NPOI 导出百万级数据到Excel 文件
一、前言，最近做项目遇到个奇怪的需求，由于每天的数据量有10k左右，一个月就有30K 左右，一年就数据库就有两百多万条记录，几年下来就可能有上千万条记录。客户的需求要求插入和更新的速度不能大于1s，使用SqLITE 实测在千万级时，操作需要时间长，结果不满足客户需求。
于是，就采用分表储存数据，每年一个表，是实测后，发现时间符合客户需求内。这个问题解决了，还有一个问题就是报表问题，他们要求将每年的几百万数据备份存储到Excel 表格中，o(╥﹏╥)o，这个才是坑。几百万的数据库存储到表格中，这的需要多长时间啊。后面实测，两百五十万，导出至Excel 中，只能100多秒。
二、关于Excel 的HSSFWorkbook 和XSSFWorkbook 和SXSSFWorkbook
1. Excel 2003及以下的版本，其文件后缀是.xls。 HSSFWorkbook一张表最大支持65536行数据，256列，即一张表最多包含6万多条数据，因此，Excel 2003完全不可能符合百万数据导出的需求。^_^
2. Excel 2007- 更高的版本，其文件后缀是.xlsx。XSSFWorkbook一张表最大支持1048576行，16384列，即一张表最多包含104万多条数据，因此符合。但是，虽然这时导出100万数据能满足要求，但使用XSSF测试后发现偶尔还是会发生堆溢出，程序总是崩溃，抛出内存不够的异常，100多万数据就将内存榨干了，XSSFWorkbook所以也不适合百万数据的导出。
3. 后来，经过网上查找，发现在最新的NPOI 和POI中，POI3.8之后新增加了一个类，SXSSFWorkbook，在POI3.8的SXSSF包是XSSF的一个扩展版本。支持流处理，在生成大数据量的电子表格且堆空间有限时使用。SXSSF通过限制内存中可访问的记录行数来实现其低内存利用，当达到限定值时，新一行数据的加入会引起老一行的数据刷新到硬盘。当内存中限制行数为100，当行号到达101时，行号为0的记录刷新到硬盘并从内存中删除，当行号到达102时，行号为1的记录刷新到硬盘，并从内存中删除，以此类推。^_^
4. 在使用SXSSFWorkbook 导出数据过程中，C盘的 AppData\Local\Temp\poifiles （如下图）下会在产生临时文件的，而且会产生两种临时文件，一种是为每个sheet页生成一个 xml 临时文件，一种是最终导出时生成的完整.xlsx 文件，打开这个目录一看，发现有几G临时文件存在 o(╥﹏╥)o。
5. 在一片关于在Java中使用POI 导出百万级数据的文章中，提到 《poi实现百万级数据导出》，临时文件的删除可以使用
-
workbook.write(fileOut); write()方法中包含删除 .xlsx 文件的方法，在它的finally代码块里，具体可以去查看源码
-
-
workbook.dispose(); dispose()方法就是用来删除那些 xml 格式的临时文件的
6. 后面发现我使用250W 条数据测试，分5个sheet ，每个sheet 50W 条数据，发现在一些低配置的电脑或者笔记本，还是存在内存不足异常。后面查看代码，发现一开始是这样子写的，虽然不是每个表100W数据，但是5个表，加起来还是有250W多数据。
因此，需要在细节上处理一下，《poi实现百万级数据导出》提到：^_^
每创建完一个sheet页就会生成一个xml文件 但是所有的 xml 文件都是空的，只有调用workbook.write(fileOut); 方法时，才会往xml中写数据，也就是说之前构造的几百万数据都在内存中，这是很危险的行为，当达到一定量时可能就会有内存溢出的风险，所以要记得在每个sheet页构造完成之后都手动把数据刷到磁盘当中((SXSSFSheet)sheet).flushRows();
其实write()方法中也是for循环调用的flushRows()方法。
所以，每次写完50W条数据后，手动调用((SXSSFSheet)sheet).flushRows() 这个方法，将数据刷新到磁盘中，这样子就可以避免内存不够用的情况。^_^
实际使用过程如下，导出250W 数据不成问题，经过多次测试，在配置差的笔记本和台式机都是可以成功导出250W 数据
O(∩_∩)O哈哈~ ^_^
/// <summary> /// 导出数据到Execl中 /// </summary> private void ExportExeclFile() { lock (mLockObject) { mIsExport = true; } bool mIsExit = false; WriteLogcat("开始备份 ...", 0); ShowMessage(2, string.Format("{0:N3}", 0)); ISheet sheet = null; //工作表对象 IRow row = null; //行对象 ICell cell = null; //列对象 //备份整个数据库 List<String> tabList = null; DataTable db = null; int totalLines = 0, writeLine = 0; int totolRecord = 0, year = 0, singleWriteRecord = 50 * 10000, offset = 0; SXSSFWorkbookBean bean = null; SXSSFWorkbook workbook = null; CardRecordService cardRecordService = CardRecordService.GetInstance(); CheckCardRecordService checkCardRecordService = CheckCardRecordService.GetInstance(); //新建表头 -- 50万条记录一张sheet try { //1. 获取数据库中，所有表格 WriteLogcat("正在获取数据 ...", 0); if (mMode == 0) tabList = checkCardRecordService.GetRecordTables(); else tabList = cardRecordService.GetRecordTables(); if (tabList == null) { WriteLogcat("数据库为空 ...", 1); goto Finish; } lock (mLockObject) { if (mIsExport == false) { mIsExit = true; goto Finish; } } year = mYear; //2. 每个表格，分别备份到一个execl.xlsx 文件中 -- 指定年份的表 if (mMode == 0) WriteLogcat("正在备份 " + year + " 年检测卡数据...", 2); else WriteLogcat("正在备份 " + year + " 年发卡数据...", 2); if (mMode == 0) totolRecord = checkCardRecordService.GetBackupTotalRecords(year); else totolRecord = cardRecordService.GetBackupTotalRecords(year); totalLines = totolRecord; ShowMessage(2, String.Format("{0:N3}", totalLines)); lock (mLockObject) { if (mIsExport == false) { mIsExit = true; goto Finish; } } //3.开始保存 50万 一个sheet offset = 0; mWrittenLines = 0; bean = NpoiUtils.CreateSXSSFWorkbook(); //每个Execl文件对应一个对象 workbook = bean.getSxssfworkBook(); ICellStyle style = bean.getStyle(); ICellStyle dataStyle = bean.getDataStyle(); ICellStyle dataStyle2 = bean.getDataStyle2(); while (totalLines > 0) { sheet = workbook.CreateSheet("记录" + (offset + 1).ToString()); //建立新的sheet对象 WriteLogcat("正在备份 " + year + " 年 sheet " + (offset + 1) + " 数据 ...", 2); //4.创建表头 row = sheet.CreateRow(0); for (int jk = 0; jk < mSheetTitle.Length; jk++) { cell = row.CreateCell(jk); cell.SetCellValue(mSheetTitle[jk]); cell.CellStyle = style; sheet.SetColumnWidth(jk, 30 * 256); } if (totalLines >= singleWriteRecord) { totalLines -= singleWriteRecord; writeLine = singleWriteRecord; } else { writeLine = totalLines; totalLines = 0; } //5.获取数据 if (mMode == 0) db = checkCardRecordService.Report(year, offset, writeLine); else db = cardRecordService.Report(year, offset, writeLine); if (db == null || db.Rows.Count == 0) goto Finish; offset++; //6.写表格 for (int kk = 0; kk < db.Rows.Count; kk++) { row = sheet.CreateRow(kk + 1); for (int lk = 0; lk < mSheetTitle.Length; lk++) { cell = row.CreateCell(lk); if (lk == 0) cell.SetCellValue((kk + 1).ToString()); else cell.SetCellValue(db.Rows[kk][lk].ToString()); cell.CellStyle = dataStyle2; sheet.SetColumnWidth(lk, 30 * 256); } //防止点击停止 lock (mLockObject) { mWrittenLines++; if (mIsExport == false) { mIsExit = true; goto Finish; } } } //防止点击停止 lock (mLockObject) { if (mIsExport == false) goto Finish; } //在每个sheet页构造完成之后都手动把数据刷到磁盘当中,否则有可能出现内存不足异常((SXSSFSheet)sheet).flushRows(); //其实write()方法中也是for循环调用的flushRows()方法。 ((SXSSFSheet)sheet).flushRows(); } //转为字节数组 MemoryStream stream = new MemoryStream(); workbook.Write(stream); var buf = stream.ToArray(); if (mMode == 0) WriteLogcat("正在写入 " + mExeclFilePath + "\\" + year + "检测卡记录.xlsx ...", 2); else WriteLogcat("正在写入 " + mExeclFilePath + "\\" + year + "发卡记录.xlsx ...", 2); //保存为Excel文件 if (mMode == 0) { using (FileStream fs = new FileStream(mExeclFilePath + "\\" + year + "检测卡记录.xlsx", FileMode.Create, FileAccess.Write)) { fs.Write(buf, 0, buf.Length); fs.Flush(); } } else { using (FileStream fs = new FileStream(mExeclFilePath + "\\" + year + "发卡记录.xlsx", FileMode.Create, FileAccess.Write)) { fs.Write(buf, 0, buf.Length); fs.Flush(); } } if (mMode == 0) WriteLogcat("写入 " + mExeclFilePath + "\\" + year + "检测卡记录.xlsx 完毕...", 2); else WriteLogcat("写入 " + mExeclFilePath + "\\" + year + "发卡记录.xlsx 完毕...", 2); //防止点击停止 lock (mLockObject) { if (mIsExport == false) { mIsExit = true; goto Finish; } } if (stream != null) { stream.Close(); } //关闭所有流 if (workbook != null) { workbook.Close(); workbook.Dispose(); } } catch (Exception e) { mIsExit = true; LogcatService.WriteLogcat(e); ShowMessage(0, e.Message); goto Finish; } //2.清空对应年份的表，然后重新创建表格 try { if (mMode == 0) { WriteLogcat("开始清空 " + year + " 年检测卡数据 ....", 2); WriteLogcat("此操作需要时间长，请耐心等待 ^_^ ...", 2); checkCardRecordService.ClearAllRecords(year); } else { WriteLogcat("开始清空 " + year + " 年发卡数据 ....", 2); WriteLogcat("此操作需要时间长，请耐心等待 ^_^ ...", 2); cardRecordService.ClearAllRecords(year); } } catch (Exception e1) { mIsExit = true; LogcatService.WriteLogcat(e1); ShowMessage(0, e1.Message); goto Finish; } Finish: { //关闭所有流 if (workbook != null) { workbook.Close(); workbook.Dispose(); //方法就是用来删除那些 xml 格式的临时文件的 } if (mIsExit) WriteLogcat("停止备份 ...", 1); else { ShowMessage(1, string.Format("{0:N3}", totolRecord)); WriteLogcat("备份完成 ...", 0); } lock (mLockObject) { mIsExport = false; } } ProcessCloseExportThread(); }
来自https://blog.csdn.net/gd6321374/article/details/90417202