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