记录一次POI导出Excel由于大量单元格合并导致频繁GC问题

记录一次POI导出Excel由于大量单元格合并导致频繁GC问题

解决方法

设置合并GC的时候采用 addMergedRegionUnsafe 方法替换 addMergedRegion 。

问题排查

1. 监听GC与内存情况

2. 代码定位
   初步判定POI操作excel单元格的问题，到底是哪一步通过耗时打印进一步定位到设置合并单元格方法

org.apache.poi.xssf.usermodel.XSSFSheet#addMergedRegion(org.apache.poi.ss.util.CellRangeAddress)

查看源码得到如下图

查看 validateMergedRegions 相关源码如下，基本确认了每次设置合并的时候调用到 getMergedRegions 方法重新遍历创建了一遍对象。

    /**
     * Verify that candidate region does not intersect with an existing merged region in this sheet
     *
     * @param candidateRegion the range of cells to verify
     * @throws IllegalStateException if candidate region intersects an existing merged region in this sheet (or candidateRegion is already merged in this sheet)
     */
    private void validateMergedRegions(CellRangeAddress candidateRegion) {
        for (final CellRangeAddress existingRegion : getMergedRegions()) {
            if (existingRegion.intersects(candidateRegion)) {
                throw new IllegalStateException("Cannot add merged region " + candidateRegion.formatAsString() +
                        " to sheet because it overlaps with an existing merged region (" + existingRegion.formatAsString() + ").");
            }
        }
    }
 
        /**
     * Returns the list of merged regions. If you want multiple regions, this is
     * faster than calling {@link #getMergedRegion(int)} each time.
     *
     * @return the list of merged regions
     */
    @Override
    public List<CellRangeAddress> getMergedRegions() {
        List<CellRangeAddress> addresses = new ArrayList<>();
        CTMergeCells ctMergeCells = worksheet.getMergeCells();
        if(ctMergeCells == null) {
            return addresses;
        }
 
        for(CTMergeCell ctMergeCell : ctMergeCells.getMergeCellArray()) {
            String ref = ctMergeCell.getRef();
            addresses.add(CellRangeAddress.valueOf(ref));
        }
        return addresses;
    }