NPOI 匯出excel資料超65535自動分表

標籤：

 　　工作上遇到的問題，網上找了一些資料 整理了一個比較可行的解決方案。
　　 NPOI 大資料量分多個sheet匯出

程式碼片段  

/// <summary>
/// DataTable轉換成Excel文檔流，並輸出到用戶端
/// </summary>
/// <param name="table"></param>
/// <param name="response"></param>
/// <param name="fileName">輸出的檔案名稱</param>
public static void RenderToDataTableToExcel(DataTable table, HttpContext context, string fileName)
{
using (MemoryStream ms = ExportDataTableToExcel(table))
{
RenderToBrowser(ms, context, fileName);
}
}

 

 

/// <summary>
/// DataTable轉換成Excel文檔流(匯出資料量超出65535條,分sheet)
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
public static MemoryStream ExportDataTableToExcel(DataTable sourceTable)
{
HSSFWorkbook workbook = new HSSFWorkbook();
MemoryStream ms = new MemoryStream();
int dtRowsCount = sourceTable.Rows.Count;
int SheetCount = Convert.ToInt32(Math.Ceiling(Convert.ToDouble(dtRowsCount) / 65536));
int SheetNum = 1;
int rowIndex = 1;
int tempIndex = 1; //標示
ISheet sheet = workbook.CreateSheet("sheet1" + SheetNum);
for (int i = 0; i < dtRowsCount; i++)
{
if (i == 0 || tempIndex == 1)
{
IRow headerRow = sheet.CreateRow(0);
foreach (DataColumn column in sourceTable.Columns)
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
}
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(tempIndex);
foreach (DataColumn column in sourceTable.Columns)
{
dataRow.CreateCell(column.Ordinal).SetCellValue(sourceTable.Rows[i][column].ToString());
}
if (tempIndex == 65535)
{
SheetNum++;
sheet = workbook.CreateSheet("sheet" + SheetNum);//
tempIndex = 0;
}
rowIndex++;
tempIndex++;
//AutoSizeColumns(sheet);
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
sheet = null;
// headerRow = null;
workbook = null;
return ms;
}

 

 

/// <summary>
/// 輸出檔案到瀏覽器
/// </summary>
/// <param name="ms">Excel文檔流</param>
/// <param name="context">HTTP上下文</param>
/// <param name="fileName">檔案名稱</param>
private static void RenderToBrowser(MemoryStream ms, HttpContext context, string fileName)
{
if (context.Request.Browser.Browser == "IE")
fileName = HttpUtility.UrlEncode(fileName);
context.Response.AddHeader("Content-Disposition", "attachment;fileName=" + fileName);
context.Response.BinaryWrite(ms.ToArray());
}

NPOI 匯出excel資料超65535自動分表