C# NPOI导出Excel和EPPlus导出Excel比较

private static MemoryStream ExportXlsx(DataTable dt)
{
XSSFWorkbook workbook = new XSSFWorkbook();
ISheet sheet = null; int headRowIndex = ;
string sheetName = "Sheet1";
if (!string.IsNullOrEmpty(dt.TableName))
{
sheetName = dt.TableName;
}
sheet = workbook.CreateSheet(sheetName);
int rowIndex = ; #region 列头及样式
{
XSSFRow headerRow = (XSSFRow)sheet.CreateRow(headRowIndex); ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = ;
font.Boldweight = ;
headStyle.SetFont(font); foreach (DataColumn column in dt.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
}
}
#endregion #region 填充内容 foreach (DataRow row in dt.Rows)
{
rowIndex++;
XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex);
foreach (DataColumn column in dt.Columns)
{
string drValue = row[column].ToString();
dataRow.CreateCell(column.Ordinal).SetCellValue(drValue);
}
}
#endregion MemoryStream ms = new MemoryStream(); workbook.Write(ms);
ms.Flush(); return ms;
} public static void ExportXlsxByWeb(DataTable dt, string strFileName)
{ HttpContext curContext = HttpContext.Current; MemoryStream ms = ExportXlsx(dt); curContext.Response.AppendHeader("Content-Disposition",
"attachment;filename=" + HttpUtility.UrlEncode(strFileName, Encoding.UTF8) + ".xlsx");
curContext.Response.AddHeader("Content-Length", ms.ToArray().Length.ToString());
curContext.Response.ContentEncoding = Encoding.UTF8; curContext.Response.BinaryWrite(ms.ToArray());
ms.Close();
ms.Dispose();
curContext.Response.End(); }

/// <summary>
/// 使用EPPlus导出Excel(xlsx)
/// </summary>
/// <param name="sourceTable">数据源</param>
/// <param name="strFileName">xlsx文件名(不含后缀名)</param>
public static void ExportByEPPlus(DataTable sourceTable, string strFileName)
{
using (ExcelPackage pck = new ExcelPackage())
{
//Create the worksheet
string sheetName = string.IsNullOrEmpty(sourceTable.TableName) ? "Sheet1" : sourceTable.TableName;
ExcelWorksheet ws = pck.Workbook.Worksheets.Add(sheetName); //Load the datatable into the sheet, starting from cell A1. Print the column names on row 1
ws.Cells["A1"].LoadFromDataTable(sourceTable, true); //Format the row
ExcelBorderStyle borderStyle = ExcelBorderStyle.Thin;
Color borderColor = Color.FromArgb(, , ); using (ExcelRange rng = ws.Cells[, , sourceTable.Rows.Count + , sourceTable.Columns.Count])
{
rng.Style.Font.Name = "宋体";
rng.Style.Font.Size = ;
rng.Style.Fill.PatternType = ExcelFillStyle.Solid; //Set Pattern for the background to Solid
rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(, , )); rng.Style.Border.Top.Style = borderStyle;
rng.Style.Border.Top.Color.SetColor(borderColor); rng.Style.Border.Bottom.Style = borderStyle;
rng.Style.Border.Bottom.Color.SetColor(borderColor); rng.Style.Border.Right.Style = borderStyle;
rng.Style.Border.Right.Color.SetColor(borderColor);
} //Format the header row
using (ExcelRange rng = ws.Cells[, , , sourceTable.Columns.Count])
{
rng.Style.Font.Bold = true;
rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(, , )); //Set color to dark blue
rng.Style.Font.Color.SetColor(Color.FromArgb(, , ));
} //Write it back to the client
HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
HttpContext.Current.Response.AddHeader("content-disposition", string.Format("attachment; filename={0}.xlsx", HttpUtility.UrlEncode(strFileName, Encoding.UTF8)));
HttpContext.Current.Response.ContentEncoding = Encoding.UTF8; HttpContext.Current.Response.BinaryWrite(pck.GetAsByteArray());
HttpContext.Current.Response.End();
}
}