GridView中的数据导出到Excel.并分工作薄显示

 

DataContext db = new DataContext();
            XlsApplication xlApp = new XlsApplication();
            xlApp.DisplayAlerts = false;
            XlsWookbook xlWb = xlApp.Wookbooks.Add();
            string fileName = Path.GetTempFileName();
            try
            {
                File.Delete(fileName);
            }
            catch { }

 

            IList<object> ids = Gv_Pur.GetSelectedFieldValues("Purchase_id");

        

           //实例化函数 返回条码,货号,颜色,尺码
            var results = new List<CreateBarCodeFromPurchaseResult>();
            foreach (string id in ids)
            {
                results.AddRange(db.p_CreateBarCodeFromPurchase(id));
            }

           

           //根据货号分组

            var bns = (
                from r in results
                group r by r.bn.Substring(0, r.bn.Length - 3) into g
                select g.Key
                ).ToArray();

 

       

            foreach (string bn in bns)
            {
                var result = results.Where(r => r.bn.StartsWith(bn)).ToArray();

                XlsWooksheet xlWs = xlWb.Wooksheets.Add();
                xlWs.Name = bn;

                xlWs.Cells(1, 1).Text = "条码";
                xlWs.Cells(1, 2).Text = "货号";
                xlWs.Cells(1, 3).Text = "颜色";
                xlWs.Cells(1, 4).Text = "尺码";

                xlWs.Rows(1).Font.Bold = true;

                int rowIndex = 2;
                foreach (var item in result)
                {
                    xlWs.Cells(rowIndex, 1).Text = item.barcode;
                    xlWs.Cells(rowIndex, 2).Text = item.bn;
                    xlWs.Cells(rowIndex, 3).Text = item.color;
                    xlWs.Cells(rowIndex, 4).Text = item.size;

                    rowIndex++;
                }

                xlWs.Columns().AutoFit();
                xlWs.Dispose();
            }

            int sheetCount = xlWb.Wooksheets.Count;

 

        //删除Excel自带的三个sheet
            for (int i = 0; i < sheetCount - bns.Length; i++)
            {
                xlWb.Wooksheets[xlWb.Wooksheets.Count].Delete();
            }

            xlWb.Close(fileName);
            xlApp.Dispose();

            byte[] fileBytes = File.ReadAllBytes(fileName);
            File.Delete(fileName);

            HttpContext.Current.Response.Charset = "UTF-8";
            HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
            HttpContext.Current.Response.ContentType = "application/ms-excel";
            HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode("barcode.xls", Encoding.UTF8).ToString());
            HttpContext.Current.Response.BinaryWrite(fileBytes);
            HttpContext.Current.Response.End();