Npoi 导出Excel分多个工作簿

现在有代码可以实现导出数据了，但是数据实在是多十几万 超过excel 2003 的一个工作簿的存储容量 现在想分成 大于15000就分到另一个工作簿中。

先贴上我 现在的代码，NPOI使用的版本是1.2.3.0

/// <summary>
        /// 
        /// </summary>
        /// <param name="SourceTable">数据源</param>
        /// <param name="FileName">文件名</param>
        public static void RenderDataTableToExcel(DataTable SourceTable, string FileName)
        {
            try
            {
                HttpContext curContext = HttpContext.Current;
                // 设置编码和附件格式
                curContext.Response.ContentType = "application/vnd.ms-excel";
                curContext.Response.ContentEncoding = Encoding.UTF8;
                curContext.Response.Charset = "";
                curContext.Response.AppendHeader("Content-Disposition",
                    "attachment;filename=" + HttpUtility.UrlEncode(FileName, Encoding.UTF8));
                MemoryStream ms = RenderDataTableToExcel(SourceTable,FileName,1) as MemoryStream;
                curContext.Response.BinaryWrite(ms.GetBuffer());
                curContext.Response.End();
            }
            catch (Exception e)
            {
                WebMessageBox.Show("执行出错！错误原因是：" + e.Message.ToString());
            }
        }


/// <summary>
        /// NPOI导出excel 
        /// </summary>
        /// <param name="SourceTable">数据源</param>
        /// <returns>生成的数据流</returns>
        public static Stream RenderDataTableToExcel(DataTable SourceTable,string filename,int type)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFCellStyle dateStyle = workbook.CreateCellStyle() as HSSFCellStyle;
            MemoryStream ms = new MemoryStream();
            HSSFSheet sheet = workbook.CreateSheet(filename.Substring(0,filename.LastIndexOf('.'))) as HSSFSheet;
            HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow;
            sheet.DefaultColumnWidth =13;
            HSSFDataFormat format = workbook.CreateDataFormat() as HSSFDataFormat;
            dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
            // handling header.
            foreach (DataColumn column in SourceTable.Columns)
                headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
            // handling value.
            int rowIndex = 1;
            foreach (DataRow row in SourceTable.Rows)
            {
                HSSFRow dataRow = sheet.CreateRow(rowIndex) as HSSFRow;
                foreach (DataColumn column in SourceTable.Columns)
                {
                    // dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
                    HSSFCell newCell = dataRow.CreateCell(column.Ordinal) as HSSFCell;
                    string drValue = row[column].ToString();
                    switch (column.DataType.ToString())
                    {                        
                        case "System.String"://字符串类型   
                            newCell.SetCellValue(drValue);
                            break;
                        case "System.DateTime"://日期类型   
                            if (drValue == null || drValue == "")
                            {
                                newCell.SetCellValue("");
                            }
                            else
                            {
                                DateTime dateV;
                                DateTime.TryParse(drValue, out dateV);
                                newCell.SetCellValue(dateV);
                                newCell.CellStyle = dateStyle;//格式化显示   
                            } 
                            break;
                        case "System.Boolean"://布尔型   
                            bool boolV = false;
                            bool.TryParse(drValue, out boolV);
                            newCell.SetCellValue(boolV);
                            break;
                        case "System.Int16"://整型   
                        case "System.Int32":
                        case "System.Int64":
                        case "System.Byte":
                            int intV = 0;
                            int.TryParse(drValue, out intV);
                            newCell.SetCellValue(intV);
                            break;
                        case "System.Decimal"://浮点型   
                        case "System.Double":
                            double doubV = 0;
                            double.TryParse(drValue, out doubV);
                            newCell.SetCellValue(doubV);
                            break;
                        case "System.DBNull"://空值处理   
                            newCell.SetCellValue("");
                            break;
                        default:
                            newCell.SetCellValue("");
                            break;
                    }
                }
                rowIndex++;
            }
            workbook.Write(ms);
            ms.Flush();
            ms.Position = 0;
            sheet = null;
            headerRow = null;
            workbook = null;
            return ms;
        }

2 个解决方案

#1

我想着就是 在RenderDataTableToExcel(DataTable SourceTable,string filename,int type) 这个方法里面进行处理一下。因为有好多地方那个用到这个方法 

我传进一个DataTable就可以自行处理 最好

#2

怎么就没有人回帖呢。。。郁闷啊