1 #region NPOI 导出excel数据超65535自动分表
2 /// <summary>
3 /// DataTable转换成Excel文档流，并输出到客户端
4 /// </summary>
5 /// <param name="table"></param>
6 /// <param name="response"></param>
7 /// <param name="fileName">输出的文件名</param>
8 public static void RenderToDataTableToExcel(DataSet ds, string fileName)
9 {
10 for (int i = 0; i < ds.Tables.Count; i++)
11 {
12 using (MemoryStream ms = ExportDataTableToExcel(ds.Tables[i]))
13 {
14 RenderToBrowser(ms, HttpContext.Current, fileName);
15 }
16 }
17 }
18
19 /// <summary>
20 /// DataTable转换成Excel文档流(导出数据量超出65535条,分sheet)
21 /// </summary>
22 /// <param name="table"></param>
23 /// <returns></returns>
24 public static MemoryStream ExportDataTableToExcel(DataTable sourceTable)
25 {
26 HSSFWorkbook workbook = new HSSFWorkbook();
27 MemoryStream ms = new MemoryStream();
28 int dtRowsCount = sourceTable.Rows.Count;
29 int SheetCount = Convert.ToInt32(Math.Ceiling(Convert.ToDouble(dtRowsCount) / 65536));
30 int SheetNum = 1;
31 int rowIndex = 1;
32 int tempIndex = 1; //标示
33 ISheet sheet = workbook.CreateSheet("sheet1" + SheetNum);
34 for (int i = 0; i < dtRowsCount; i++)
35 {
36 if (i == 0 || tempIndex == 1)
37 {
38 IRow headerRow = sheet.CreateRow(0);
39 foreach (DataColumn column in sourceTable.Columns)
40 headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
41 }
42 HSSFRow dataRow = (HSSFRow)sheet.CreateRow(tempIndex);
43 foreach (DataColumn column in sourceTable.Columns)
44 {
45 dataRow.CreateCell(column.Ordinal).SetCellValue(sourceTable.Rows[i][column].ToString());
46 }
47 if (tempIndex == 65535)
48 {
49 SheetNum++;
50 sheet = workbook.CreateSheet("sheet" + SheetNum);//
51 tempIndex = 0;
52 }
53 rowIndex++;
54 tempIndex++;
55 //AutoSizeColumns(sheet);
56 }
57 workbook.Write(ms);
58 ms.Flush();
59 ms.Position = 0;
60 sheet = null;
61 // headerRow = null;
62 workbook = null;
63 return ms;
64 }
65
66 /// <summary>
67 /// 输出文件到浏览器
68 /// </summary>
69 /// <param name="ms">Excel文档流</param>
70 /// <param name="context">HTTP上下文</param>
71 /// <param name="fileName">文件名</param>
72 private static void RenderToBrowser(MemoryStream ms, HttpContext context, string fileName)
73 {
74 if (context.Request.Browser.Browser == "IE")
75 fileName = HttpUtility.UrlEncode(fileName);
76 context.Response.AddHeader("Content-Disposition", "attachment;fileName=" + fileName);
77 context.Response.BinaryWrite(ms.ToArray());
78 }
79 #endregion
