NPOI 导出百万级数据到Excel 文件 - 龍瀧尨呀

时间:2024-02-15 19:30:27

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实现百万级数据导出》,临时文件的删除可以使用

  1.  
    workbook.write(fileOut); write()方法中包含删除 .xlsx 文件的方法,在它的finally代码块里,具体可以去查看源码
  2.  
     
  3.  
    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