NPOI 导出Sqlite 百万级数据到Excel 文件

12 篇文章 0 订阅
20 篇文章 0 订阅

一、前言,最近做项目遇到个奇怪的需求,由于每天的数据量有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();
        }

 

 

参数大神文章:

poi实现百万级数据导出

参与评论 您还未登录,请先 登录 后发表或查看评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:技术黑板 设计师:CSDN官方博客 返回首页

打赏作者

gd6321374

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值