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 }
