当记录数超出65536时，有两种方式处理：
一是调用WriteToDownLoad65536方法建立多个Excel。
二是调用WriteToDownLoad方法在同一个Excel中建多个Sheet。
若在同一Excel中建多个Sheet，若记录数达数十万，会导致字节流溢出的问题，解决办法是先获取MemoryStream，然后分块读取写入文件流。
需要注意的是在读取内存流的时候，一定要将内存流的位置设为0，因为在从HssfWorkBook中获取内存流时，位置已经置于最后了！若不重Position重新置为0则读取不到任何数据。
代码
using System; using System.Collections.Generic; using LuCeServiceWinForm.Common; using NPOI.HSSF.UserModel; using NPOI.HPSF; using System.Web; using System.IO; using System.Data; using NPOI.SS.UserModel; using System.Reflection; namespace LuCeServiceWinForm.Helper { public class NPOIHelper { static HSSFWorkbook hssfworkbook; /// <summary> /// 初始化 /// </summary> static void InitializeWorkbook() { hssfworkbook = new HSSFWorkbook(); DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = ""; hssfworkbook.DocumentSummaryInformation = dsi; SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); si.Subject = ""; hssfworkbook.SummaryInformation = si; } /// <summary> /// DataTable写入Excel /// </summary> /// <param name="FileName">要保存的文件名称 eg:test.xls</param> /// <param name="SheetName">工作薄名称</param> /// <param name="dt">要写入的DataTable </param> public static void WriteToDownLoad(string FileName, string SheetName, DataTable dt) { string filename = FileName; HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"; HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", filename)); HttpContext.Current.Response.Clear(); //初始化Excel信息 InitializeWorkbook(); //填充数据 DTExcel(SheetName, dt, null); HttpContext.Current.Response.BinaryWrite(WriteToStream().GetBuffer()); HttpContext.Current.Response.End(); } /// <summary> /// 当大于65536条记录时，表格中建多个Sheet /// </summary> /// <typeparam name="T">实体</typeparam> /// <param name="FileName">要保存的文件名称 eg:test.xls</param> /// <param name="SheetName">工作薄名称</param> /// <param name="lst">要写入的List</param> public static void WriteToDownLoad<T>(string FileName, string SheetName, List<T> lst, List<string> listTitle) { //初始化Excel信息 InitializeWorkbook(); //填充数据 //ListExcel<T>(SheetName, lst, listTitle); //填充大于65536的数据 Fill65536(SheetName, lst, listTitle); MemoryStream memoryStream = WriteToStream(); FileStream fstr = new FileStream(FileName, FileMode.OpenOrCreate, FileAccess.Write); WriteMemoryStream(memoryStream, fstr); } public static void WriteToDownLoad<T>(string dir, string FileName, string SheetName, List<T> lst, List<string> listTitle) { try { if (!Directory.Exists(dir)) { Directory.CreateDirectory(dir); } //初始化Excel信息 InitializeWorkbook(); //填充数据 ListExcel<T>(SheetName, lst, listTitle); MemoryStream memoryStream = WriteToStream(); FileStream fstr = new FileStream(dir + "\\" + FileName + DateTime.Now.ToString("yyMMddHHmmss") + ".xls", FileMode.OpenOrCreate, FileAccess.Write); WriteMemoryStream(memoryStream, fstr); } catch (Exception ex) { LogHelper.CreateLog(ex); } } /// <summary> /// 将源内存流写入目标内存流 /// </summary> /// <param name="memoryStream">源内存流</param> /// <param name="fileStream">目标文件流</param> private static void WriteMemoryStream(MemoryStream memoryStream, FileStream fileStream) { try { using (memoryStream) { using (fileStream) { //流的位置一定要归零，否则啥也读不到！ memoryStream.Position = 0; long len = memoryStream.Length; byte[] buffer = new byte[1024 * 1024];//1MB=1024 * 1024 while (true) { int r = memoryStream.Read(buffer, 0, buffer.Length); if (r <= 0)//表示读取到了文件的末尾 { break; } else { fileStream.Write(buffer, 0, r); double proc = (double)fileStream.Position / len; LogHelper.WriteToLog("拷贝进度：" + proc * 100 + "%"); } } } } } catch (Exception ex) { LogHelper.CreateLog(ex); } } /// <summary> /// 从HssfWorkBook中获取内存流 /// </summary> /// <returns></returns> static MemoryStream WriteToStream() { MemoryStream file = new MemoryStream(); try { hssfworkbook.Write(file); } catch (Exception ex) { LogHelper.CreateLog(ex); } return file; } #region 数据填充部分 /// <summary> /// 将DataTable数据写入到Excel /// </summary> /// <param name="SheetName"></param> /// <param name="dt"></param> /// <param name="lstTitle"></param> static void DTExcel(string SheetName, DataTable dt, List<string> lstTitle) { ISheet sheet1 = hssfworkbook.CreateSheet(SheetName); int y = dt.Columns.Count; int x = dt.Rows.Count; //给定的标题为空,赋值datatable默认的列名 if (lstTitle == null) { lstTitle = new List<string>(); for (int ycount = 0; ycount < y; ycount++) { lstTitle.Add(dt.Columns[ycount].ColumnName); } } IRow hsTitleRow = sheet1.CreateRow(0); //标题赋值 for (int yt = 0; yt < lstTitle.Count; yt++) { hsTitleRow.CreateCell(yt).SetCellValue(lstTitle[yt]); } //填充数据项 for (int xcount = 1; xcount < x; xcount++) { IRow hsBodyRow = sheet1.CreateRow(xcount); for (int ycBody = 0; ycBody < y; ycBody++) { hsBodyRow.CreateCell(ycBody).SetCellValue(dt.DefaultView[xcount - 1][ycBody].ToString()); } } } private static int index = 0; static void Fill65536<T>(string SheetName, List<T> lst, List<string> lstTitle) { ++index; if (lst.Count > 10) { ListExcel<T>(SheetName + index, lst.GetRange(0, 10), lstTitle); lst.RemoveRange(0, 10); Fill65536(SheetName, lst, lstTitle); } else { ListExcel<T>(SheetName + index, lst, lstTitle); index = 0; } } static void ListExcel<T>(string SheetName, List<T> lst, List<string> lstTitle) { ISheet sheet1 = hssfworkbook.CreateSheet(SheetName); T _t = (T)Activator.CreateInstance(typeof(T)); PropertyInfo[] propertys = _t.GetType().GetProperties(); //给定的标题为空,赋值T默认的列名 if (lstTitle == null) { lstTitle = new List<string>(); for (int ycount = 0; ycount < propertys.Length; ycount++) { lstTitle.Add(((System.Reflection.MemberInfo)(propertys[ycount])).Name);//获取实体中列名称,去掉列类型 } } IRow hsTitleRow = sheet1.CreateRow(0); //标题赋值 for (int yt = 0; yt < lstTitle.Count; yt++) { hsTitleRow.CreateCell(yt).SetCellValue(lstTitle[yt]); } //填充数据项 for (int xcount = 1; xcount <= lst.Count; xcount++) { IRow hsBodyRow = sheet1.CreateRow(xcount); for (int ycBody = 0; ycBody < propertys.Length; ycBody++) { PropertyInfo pi = propertys[ycBody]; object obj = pi.GetValue(lst[xcount - 1], null); if (obj != null) { hsBodyRow.CreateCell(ycBody).SetCellValue(obj.ToString()); } else { hsBodyRow.CreateCell(ycBody).SetCellValue(""); } } } } #endregion /// <summary> /// 当大于65536条记录时，建多个Excel /// </summary> /// <typeparam name="T"></typeparam> /// <param name="folder"></param> /// <param name="fileName"></param> /// <param name="sheetName"></param> /// <param name="list"></param> /// <param name="listTitle"></param> public static void WriteToDownLoad65536<T>(string folder, string fileName, string sheetName, List<T> list, List<string> listTitle) { if (list.Count > 65535) { //填充 WriteToDownLoad<T>(folder, fileName, sheetName, list.GetRange(0, 65535), listTitle); list.RemoveRange(0, 65535); //递归 WriteToDownLoad65536<T>(folder, fileName, sheetName, list, listTitle); } else { //填充 WriteToDownLoad<T>(folder, fileName, sheetName, list, listTitle); } } } }