记录一次POI导出Excel由于大量单元格合并导致频繁GC问题
记录一次POI导出Excel由于大量单元格合并导致频繁GC问题
解决方法
设置合并GC的时候采用
addMergedRegionUnsafe
方法替换addMergedRegion
。
问题排查
- 监听GC与内存情况
- 代码定位
初步判定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;
}