poi生成excel大数据量的合并单元格操作优化

原创
2017/07/12 19:39
阅读数 2.4W

之前根据官网给的api和example,对excel单元格的合并操作使用下方的代码(poi版本 3.12)

public class MergedCells {
   public static void main(String[] args) throws IOException {
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("new sheet");

        HSSFRow row = sheet.createRow(1);
        HSSFCell cell = row.createCell(1);
        cell.setCellValue("This is a test of merging");

        sheet.addMergedRegion(new CellRangeAddress(1, 1, 1, 2));

        // Write the output to a file
        FileOutputStream fileOut = new FileOutputStream("workbook.xls");
        wb.write(fileOut);
        fileOut.close();
        wb.close();
    }
}

当使用支持07以上的XSSFWorkbook和SXSSFWorkbook的时候同样可以这样,代码如下:

private static void mergeWithXSSF() throws  IOException{
       XSSFWorkbook wb = new XSSFWorkbook();
       XSSFSheet sheet = wb.createSheet("new sheet");

       XSSFRow row = sheet.createRow(1);
       XSSFCell cell = row.createCell(1);
       cell.setCellValue("This is a test of merging");
       sheet.addMergedRegion(new CellRangeAddress(1, 1, 1, 2));
        
       // Write the output to a file
       FileOutputStream fileOut = new FileOutputStream("d:/temp/workbook1.xlsx");
       wb.write(fileOut);
       fileOut.close();
       wb.close();
 }

但是当mergeCell的次数非常大(数万~数十万)时候,对cpu和内存消耗不仅大大增加,而且耗时也非常大,本地测试的时候,循环次数6W次,大约需要20-30分钟;耗时核心代码如下:

for(int i=0;i<100000;i++) {
      XSSFRow row = sheet.createRow(i);
      XSSFCell cell = row.createCell(1);
      cell.setCellValue("This is a test of merging");
      sheet.addMergedRegion(new CellRangeAddress(i, i, 1, 2));
  }

找遍百度和stackoverflow都没有找到合适的答案,可能这种应用场景不多(当时我遇到的需求是对20W条数据插入excel时候,前两列需要合并,因为前两列字符长度略长,后来觉得可以通过设置前两列的列宽解决)

我看了看官网,最新版本是3.16,我更新后发现 sheet 多了一个合并单元格的方法 addMergedRegionUnsafe,比addMergedRegion少了一些检测异常的过程

源码如下:

public int addMergedRegion(CellRangeAddress region) {
        return this.addMergedRegion(region, true);
    }

    public int addMergedRegionUnsafe(CellRangeAddress region) {
        return this.addMergedRegion(region, false);
    }

    private int addMergedRegion(CellRangeAddress region, boolean validate) {
        if(region.getNumberOfCells() < 2) {
            throw new IllegalArgumentException("Merged region " + region.formatAsString() + " must contain 2 or more cells");
        } else {
            region.validate(SpreadsheetVersion.EXCEL2007);
            if(validate) {
                this.validateArrayFormulas(region);
                this.validateMergedRegions(region);
            }

            CTMergeCells ctMergeCells = this.worksheet.isSetMergeCells()?this.worksheet.getMergeCells():this.worksheet.addNewMergeCells();
            CTMergeCell ctMergeCell = ctMergeCells.addNewMergeCell();
            ctMergeCell.setRef(region.formatAsString());
            return ctMergeCells.sizeOfMergeCellArray();
        }
    }

使用这个方法之后,对于十万条数据合并单元格的本地测试就降低到了30多秒,感觉真的是质的飞跃,非常高兴,但是这只是开始,我想到既然通过减少了一些异常检测就有如此神威,是否合并单元格的方法还可以继续缩减呢?

合并单元格的核心代码在这:

CTMergeCells ctMergeCells = this.worksheet.isSetMergeCells()?this.worksheet.getMergeCells():this.worksheet.addNewMergeCells();
CTMergeCell ctMergeCell = ctMergeCells.addNewMergeCell();
ctMergeCell.setRef(region.formatAsString());
return ctMergeCells.sizeOfMergeCellArray();

第一行通过方法名了解到,判断sheet是否已经有过合并单元格的经历,如果有就getMergeCells得到ctMergeCells 对象,否则就从addNewMergeCells获取对象(因为isSetMergeCells中使用了锁,还有一些复杂的操作,感觉会比较耗时)

这个自己可以控制嘛~,设置一个本地变量 int mergeCellsCount = 0;如果合并了单元格 mergeCellsCount ++; 刚才核心的代码可以改成

CTMergeCells ctMergeCells = mergeCellsCount >0 ?this.worksheet.getMergeCells():this.worksheet.addNewMergeCells();
CTMergeCell ctMergeCell = ctMergeCells.addNewMergeCell();
ctMergeCell.setRef(region.formatAsString());
mergeCellsCount  ++
return ctMergeCells.sizeOfMergeCellArray();

咦,这个return是干嘛的?读读源码发现 他每次合并单元格的时候还要返回 已经 合并的单元格 的数目(我们上方定义的mergeCellsCount )

我们只要合并的过程,这个计数对我们没用,所以,自己重写了以下addMergedRegion方法

private void addMergeRegion(CellRangeAddress cra) {
		XSSFWorkbook workbook = new XSSFWorkbook();
		XSSFSheet sheet = workbook.getSheetAt(0);
		CTWorksheet ctWorksheet = sheet.getCTWorksheet();

		CTMergeCells ctMergeCells = mergeCellsCount > 0 ?ctWorksheet.getMergeCells():ctWorksheet.addNewMergeCells();
		CTMergeCell ctMergeCell = ctMergeCells.addNewMergeCell();

		ctMergeCell.setRef(cra.formatAsString());
		mergeCellsCount ++;
	}

经过修改,再次测试,50W条数据只需要不到10秒。(10W条数据大概需要2,3秒),已经不是生成报表的时间瓶颈了,到此,收工。

总结一下:主要是CellRangeAddress 是自己定义的,自己会控制合并区域的单元格的合法性,所以去掉验证合法性的代码; 去掉返回count的代码

如果有发现更优的方法,请在下方留言联系我,谢谢!

转载请注明出处:https://my.oschina.net/u/1417838/blog/edit

展开阅读全文
打赏
2
1 收藏
分享
加载中
这个重写我是引入的jar,jar怎么重写啊
2020/12/08 15:58
回复
举报
大佬求问,私有方法怎么重写呢?
2020/09/22 16:31
回复
举报
chro008博主
文中已经贴了
2020/09/29 10:21
回复
举报
2020/06/19 18:55
回复
举报
你好,想跟你请教个问题:为什么我重写这个方法会报错,能提供下代码参考下吗。
2019/09/14 09:13
回复
举报
已明白了,谢谢。
2019/09/14 10:50
回复
举报
前辈,XSSFSheet sheet = workbook.getSheetAt(0);
这里报错,请问是怎么回事呢?
2020/12/02 09:54
回复
举报
chro008博主

引用来自“吐槽星人”的评论

请问 mergeCellsCount 在哪里重置呢?
每次生成新的报表或者新的sheet页的时候吧
2019/05/22 10:32
回复
举报
请问 mergeCellsCount 在哪里重置呢?
2019/05/22 10:19
回复
举报
chro008博主

引用来自“发呆丶”的评论

为啥用这种方法做了之后,用office打开会报错,office2013版本的

引用来自“发呆丶”的评论

用wps打开倒是正常,导出速度确实非常快

引用来自“chro008”的评论

考虑是否是office的原因,我查询office2013打不开xlsx文件,搜索出一些原因,因本机装的office2010且无法重现你说的问题,所以没有测试

引用来自“发呆丶”的评论

找到原因了,感谢楼主分享
👍
2018/12/21 16:50
回复
举报

引用来自“发呆丶”的评论

为啥用这种方法做了之后,用office打开会报错,office2013版本的

引用来自“发呆丶”的评论

用wps打开倒是正常,导出速度确实非常快

引用来自“chro008”的评论

考虑是否是office的原因,我查询office2013打不开xlsx文件,搜索出一些原因,因本机装的office2010且无法重现你说的问题,所以没有测试
找到原因了,感谢楼主分享
2018/12/21 16:36
回复
举报
为啥用这种方法做了之后,用office打开会报错,office2013版本的,wps没问题,您是怎么解决的,分享下可以吗?文章地址:https://my.oschina.net/u/1417838/blog/1358616#comments
2019/12/11 21:31
回复
举报
chro008博主

引用来自“发呆丶”的评论

为啥用这种方法做了之后,用office打开会报错,office2013版本的

引用来自“发呆丶”的评论

用wps打开倒是正常,导出速度确实非常快
考虑是否是office的原因,我查询office2013打不开xlsx文件,搜索出一些原因,因本机装的office2010且无法重现你说的问题,所以没有测试
2018/12/21 15:57
回复
举报

引用来自“发呆丶”的评论

为啥用这种方法做了之后,用office打开会报错,office2013版本的
用wps打开倒是正常,导出速度确实非常快
2018/12/21 14:44
回复
举报
更多评论
打赏
16 评论
1 收藏
2
分享
返回顶部
顶部