多表导出
2019-05-20 15:52 金金金金金 阅读(196) 评论(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 }