C# NPOI大数据量导出多个sheet的Excel
程序员文章站
2022-07-13 14:33:07
...
NPOI早期版本导出Excel版本是 2003 excel,一个sheet最多存储65536条记录,超过报错,后期版本可以导出2007 excel,但为了兼容2003,一般我们选择导出2003 excel。解决办法是将数据分多个sheet导出,以下为核心代码。
我这里用的是NPOI_1.2.4.0,大家可以去网上下载。
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
/// <summary>
/// DataTable转换成Excel文档流
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
public static MemoryStream DataTableToExcelStream(DataTable table)
{
MemoryStream ms = new MemoryStream();
using (table)
{
using (IWorkbook workbook = new HSSFWorkbook()) //HSSFWorkbook
{
// handling value.
int rowIndex = 1;
ISheet sheet=null;
IRow headerRow=null;
int sheetCount = 0;
int sheetRowCount = 65536; //每个sheet最大数据行数
if (table.Rows.Count == 0) //如果没有数据,创建一个只有title的sheet
{
sheet = workbook.CreateSheet("sheet" + sheetCount.ToString());
//创建列头
headerRow = sheet.CreateRow(0);
// handling header.
foreach (DataColumn column in table.Columns)
headerRow.CreateCell(column.Ordinal).SetCellValue(column.Caption);//If Caption not set, returns the ColumnName value
}
else
{
for (int i = 0; i < table.Rows.Count; i++)
{
if (i % (sheetRowCount - 1) == 0) //因单个sheet最多存储65536条记录,故分sheet存储数据 -1的原因是代码创建的列头占据了一行
{
sheetCount++;
rowIndex = 1;
sheet = workbook.CreateSheet("sheet" + sheetCount.ToString());
headerRow = sheet.CreateRow(0);
// handling header.
foreach (DataColumn column in table.Columns)
headerRow.CreateCell(column.Ordinal).SetCellValue(column.Caption);//If Caption not set, returns the ColumnName value
}
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in table.Columns)
{
dataRow.CreateCell(column.Ordinal).SetCellValue(table.Rows[i][column].ToString());
}
rowIndex++;
//每个sheet最后一条数据调整列宽
if ((i % (sheetRowCount -2) == 0 && i / (sheetRowCount - 2) >= 1)||i==table.Rows.Count-1)
{
AutoSizeColumns(sheet);
}
}
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
}
}
return ms;
}