代码改变世界

多表导出

2019-05-20 15:52  金金金金金  阅读(198)  评论(0)    收藏  举报 
 1    public void Export(DataSet ds)
 2         {
 3 
 4             using (ExcelPackage pck = new ExcelPackage())//创建文件
 5             {
 6                 foreach (DataTable sourceTable in ds.Tables)
 7                 {
 8                    
 9                     
10 
11 
12                     //Create the worksheet
13                     string sheetName = string.IsNullOrEmpty(sourceTable.TableName) ? "Sheet1" : sourceTable.TableName;
14                     //1.创建sheet
15                     ExcelWorksheet ws = pck.Workbook.Worksheets.Add(sheetName);
16                     //填充数据
17                     //Load the datatable into the sheet, starting from cell A1. Print the column names on row 1
18                     ws.Cells["A1"].LoadFromDataTable(sourceTable, true);
19 
20                     //Format the row
21                     ExcelBorderStyle borderStyle = ExcelBorderStyle.Thin;
22                     Color borderColor = Color.FromArgb(155, 155, 155);
23 
24                     using (ExcelRange rng = ws.Cells[1, 1, sourceTable.Rows.Count + 1, sourceTable.Columns.Count])
25                     {
26                         rng.Style.Font.Name = "宋体";
27                         rng.Style.Font.Size = 10;
28                         rng.Style.Fill.PatternType = ExcelFillStyle.Solid;                      //Set Pattern for the background to Solid
29                         rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(255, 255, 255));
30 
31                         rng.Style.Border.Top.Style = borderStyle;
32                         rng.Style.Border.Top.Color.SetColor(borderColor);
33 
34                         rng.Style.Border.Bottom.Style = borderStyle;
35                         rng.Style.Border.Bottom.Color.SetColor(borderColor);
36 
37                         rng.Style.Border.Right.Style = borderStyle;
38                         rng.Style.Border.Right.Color.SetColor(borderColor);
39                     }
40 
41                     //Format the header row
42                     using (ExcelRange rng = ws.Cells[1, 1, 1, sourceTable.Columns.Count])
43                     {
44                         rng.Style.Font.Bold = true;
45                         rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
46                         rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(234, 241, 246));  //Set color to dark blue
47                         rng.Style.Font.Color.SetColor(Color.FromArgb(51, 51, 51));
48                     }
49 
50 
51 
52                 }
53                 //导出Excel
54                 //Write it back to the client
55                 HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
56                 HttpContext.Current.Response.AddHeader("content-disposition", string.Format("attachment;  filename={0}.xlsx", HttpUtility.UrlEncode("事件报告分类清单", Encoding.UTF8)));
57                 HttpContext.Current.Response.ContentEncoding = Encoding.UTF8;
58 
59                 HttpContext.Current.Response.BinaryWrite(pck.GetAsByteArray());
60                 HttpContext.Current.Response.End();
61             }
62         }