1 private static MemoryStream ExportXlsx(DataTable dt) 2         { 3             XSSFWorkbook workbook = new XSSFWorkbook(); 4             ISheet sheet = null; 5  6             int headRowIndex = 0; 7             string sheetName = "Sheet1"; 8             if (!string.IsNullOrEmpty(dt.TableName)) 9             {10                 sheetName = dt.TableName;11             }12             sheet = workbook.CreateSheet(sheetName);13             int rowIndex = 0;14 15             #region 列头及样式16             {17                 XSSFRow headerRow = (XSSFRow)sheet.CreateRow(headRowIndex);18 19                 ICellStyle headStyle = workbook.CreateCellStyle();20                 headStyle.Alignment = HorizontalAlignment.Center;21                 IFont font = workbook.CreateFont();22                 font.FontHeightInPoints = 10;23                 font.Boldweight = 700;24                 headStyle.SetFont(font);25 26                 foreach (DataColumn column in dt.Columns)27                 {28                     headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);29                     headerRow.GetCell(column.Ordinal).CellStyle = headStyle;30                 }31             }32             #endregion33 34             #region 填充内容35 36             foreach (DataRow row in dt.Rows)37             {38                 rowIndex++;39                 XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex);40                 foreach (DataColumn column in dt.Columns)41                 {42                     string drValue =http://www.mamicode.com/ row[column].ToString();43                     dataRow.CreateCell(column.Ordinal).SetCellValue(drValue);44                 }45             }46             #endregion47 48 49             MemoryStream ms = new MemoryStream();50 51             workbook.Write(ms);52             ms.Flush();53 54             return ms;55         }56 57         public static void ExportXlsxByWeb(DataTable dt, string strFileName)58         {59 60             HttpContext curContext = HttpContext.Current;61 62             MemoryStream ms = ExportXlsx(dt);63 64             curContext.Response.AppendHeader("Content-Disposition",65                 "attachment;filename=" + HttpUtility.UrlEncode(strFileName, Encoding.UTF8) + ".xlsx");66             curContext.Response.AddHeader("Content-Length", ms.ToArray().Length.ToString());67             curContext.Response.ContentEncoding = Encoding.UTF8;68 69             curContext.Response.BinaryWrite(ms.ToArray());70             ms.Close();71             ms.Dispose();72             curContext.Response.End();73 74         }

 1 /// <summary> 2         /// 使用EPPlus导出Excel(xlsx) 3         /// </summary> 4         /// <param name="sourceTable">数据源</param> 5         /// <param name="strFileName">xlsx文件名(不含后缀名)</param> 6         public static void ExportByEPPlus(DataTable sourceTable, string strFileName) 7         { 8             using (ExcelPackage pck = new ExcelPackage()) 9             {10                 //Create the worksheet11                 string sheetName = string.IsNullOrEmpty(sourceTable.TableName) ? "Sheet1" : sourceTable.TableName;12                 ExcelWorksheet ws = pck.Workbook.Worksheets.Add(sheetName);13 14                 //Load the datatable into the sheet, starting from cell A1. Print the column names on row 115                 ws.Cells["A1"].LoadFromDataTable(sourceTable, true);16 17                 //Format the row18                 ExcelBorderStyle borderStyle = ExcelBorderStyle.Thin;19                 Color borderColor = Color.FromArgb(155, 155, 155);20 21                 using (ExcelRange rng = ws.Cells[1, 1, sourceTable.Rows.Count + 1, sourceTable.Columns.Count])22                 {23                     rng.Style.Font.Name = "宋体";24                     rng.Style.Font.Size = 10;25                     rng.Style.Fill.PatternType = ExcelFillStyle.Solid;                      //Set Pattern for the background to Solid26                     rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(255, 255, 255));27 28                     rng.Style.Border.Top.Style = borderStyle;29                     rng.Style.Border.Top.Color.SetColor(borderColor);30 31                     rng.Style.Border.Bottom.Style = borderStyle;32                     rng.Style.Border.Bottom.Color.SetColor(borderColor);33 34                     rng.Style.Border.Right.Style = borderStyle;35                     rng.Style.Border.Right.Color.SetColor(borderColor);36                 }37 38                 //Format the header row39                 using (ExcelRange rng = ws.Cells[1, 1, 1, sourceTable.Columns.Count])40                 {41                     rng.Style.Font.Bold = true;42                     rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;43                     rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(234, 241, 246));  //Set color to dark blue44                     rng.Style.Font.Color.SetColor(Color.FromArgb(51, 51, 51));45                 }46 47                 //Write it back to the client48                 HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";49                 HttpContext.Current.Response.AddHeader("content-disposition", string.Format("attachment;  filename={0}.xlsx", HttpUtility.UrlEncode(strFileName, Encoding.UTF8)));50                 HttpContext.Current.Response.ContentEncoding = Encoding.UTF8;51 52                 HttpContext.Current.Response.BinaryWrite(pck.GetAsByteArray());53                 HttpContext.Current.Response.End();54             }55         }