[Bug] 用SheetWriteHandler合并十几万行一对多的数据，addMergedRegionUnsafe要两分多钟

[laowei93]

Search before asking

• I searched in the issues and found nothing similar.

Fesod version

fastexcel1.3.0

JDK version

jdk1.8

Operating system

No response

Steps To Reproduce

需要合并的行是提前处理好的，进SheetWriteHandler只是调用了addMergedRegionUnsafe方法，再poi里调用几个方法只需要几秒

Current Behavior

1

Expected Behavior

1

Anything else?

1

Are you willing to submit a PR?

• I'm willing to submit a PR!

[delei]

Could you please provide a simple runnable sample code that contains the Excel sample file? This will help to better understand this issue.

[laowei93]

     List<CellRangeAddress> cellRangeAddressesList = getOthrCellRangeAddress(list, 4);  

WriteSheet writeSheet = EasyExcel.writerSheet().registerWriteHandler(new CustomMergeStrategyTest(ConfirmationClassRecordExportVO.class, cellRangeAddressesList))
.head(ConfirmationClassRecordExportVO.class)
.build();

        try (ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), ConfirmationClassRecordExportVO.class).build()) {
            excelWriter.write(list, writeSheet);
        }

private List getOthrCellRangeAddress(List othr, int maxCol) {
ArrayList cellRangeAddresses = new ArrayList<>();
//'护照'合并
int passportNumFirstRow = 1;
int passportNumLastRow = 1;
for (int i = 0; i < othr.size() - 1; i++) {
if (othr.get(i).getConfirmationClassRecordId().equals(othr.get(i + 1).getConfirmationClassRecordId())) {
//可以合并
passportNumLastRow++;
} else {
if (passportNumLastRow > passportNumFirstRow) {
for (int j = 0; j <= maxCol; j++) {
CellRangeAddress cellRangeAddress = new CellRangeAddress(passportNumFirstRow,
passportNumLastRow, j, j);
cellRangeAddresses.add(cellRangeAddress);
}
}
passportNumFirstRow = passportNumLastRow + 1;
passportNumLastRow = passportNumFirstRow;
}
if (i == othr.size() - 2) {
if (passportNumLastRow > passportNumFirstRow) {
for (int j = 0; j <= maxCol; j++) {
CellRangeAddress cellRangeAddress = new CellRangeAddress(passportNumFirstRow,
passportNumLastRow, j, j);
cellRangeAddresses.add(cellRangeAddress);
}
}
}
}
return cellRangeAddresses;
}
public class CustomMergeStrategyTest implements SheetWriteHandler {

/**
 * DTO数据类型
 */
private final Class<?> elementType;
// 增加自定义合并key如果自定义合并key为空按照原有策略走
private List<CellRangeAddress> cellRangeAddressesList;

public CustomMergeStrategyTest(Class<?> elementType, List<CellRangeAddress> cellRangeAddressesList) {
    this.elementType = elementType;
    this.cellRangeAddressesList = cellRangeAddressesList;
}

@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
    // 所有数据写入完毕，开始批量合并
    Sheet sheet = writeSheetHolder.getSheet();
    if (CollectionUtils.isNotEmpty(cellRangeAddressesList)) {
        long startTime = System.currentTimeMillis();
        for (CellRangeAddress cellRangeAddress : cellRangeAddressesList) {
            sheet.addMergedRegionUnsafe(cellRangeAddress);
        }
        long endTime = System.currentTimeMillis();
        System.out.println("合并完成，耗时：" + (endTime - startTime) + " 毫秒");
    }
}

}

20251203102025.xlsx

20251203102025.xlsx

[bengbengbalabalabeng]

I tested addMergedRegionUnsafe() with different dataset sizes, but I could not reproduce the reported "two minutes+" execution time. Even with large datasets (up to 250k rows), the execution time stayed within a few seconds.

Could it be that there are other variables in your environment (e.g., hardware or data distribution) that differ from mine?

• 1,000 rows → 217ms, 141ms, 30ms, 9ms, 11ms, 6ms, 6ms, 8ms, 7ms, 7ms
• 10,000 rows → 384ms, 370ms, 75ms, 104ms, 76ms, 60ms, 80ms, 70ms, 98ms, 62ms
• 50,000 rows → 1031ms, 512ms, 307ms, 307ms, 324ms, 337ms, 380ms, 294ms, 287ms, 336ms
• 100,000 rows → 1611ms, 730ms, 796ms, 660ms, 632ms, 795ms, 726ms, 779ms, 610ms, 657ms
• 150,000 rows → 2441ms, 1195ms, 1557ms, 1479ms, 1487ms, 869ms, 876ms, 1520ms, 1480ms, 1481ms
• 200,000 rows → 2998ms, 1830ms, 1319ms, 1401ms, 2704ms, 1848ms, 2132ms, 2269ms, 2158ms, 2147ms
• 250,000 rows → 3197ms, 2943ms, 2839ms, 2418ms, 2497ms, 2665ms, 2570ms, 2686ms, 2603ms, 2700ms

Environment used for testing:

• FastExcel 1.3.0
• Java 17
• 16 GB RAM
• 4-core CPU

full code view

public class AddMergedRegionUnsafePerformanceTest {


    public static void main(String[] args) {

        for (int i = 0; i < 10; i++) {
            doWriteTest(250_000);
        }

    }

    private static void doWriteTest(Integer maxSize) {
        // 复现 "[20251203102025.xlsx](https://github.com/user-attachments/files/23894289/20251203102025.xlsx)" 格式数据
        List<ConfirmationClassRecordExportVO> list = IntStream.rangeClosed(1, maxSize)
                .mapMulti((index, downstream) -> {
                    downstream.accept(index);
                    downstream.accept(index);
                })
                .mapToObj(ConfirmationClassRecordExportVO::new)
                .toList();


        List<CellRangeAddress> cellRangeAddressesList = getOthrCellRangeAddress(list, 4);
        WriteSheet writeSheet = FastExcel.writerSheet()
                .registerWriteHandler(new CustomMergeStrategyTest(ConfirmationClassRecordExportVO.class, cellRangeAddressesList))
                .head(ConfirmationClassRecordExportVO.class)
                .build();

        try (ExcelWriter excelWriter = FastExcel.write(new File("/path/file.xlsx"), ConfirmationClassRecordExportVO.class).build()) {
            excelWriter.write(list, writeSheet);
        }
        System.out.println("Done...");
    }


    private static List<CellRangeAddress> getOthrCellRangeAddress(List<ConfirmationClassRecordExportVO> othr, int maxCol) {
        List<CellRangeAddress> cellRangeAddresses = new ArrayList<>();
        //'护照'合并
        int passportNumFirstRow = 1;
        int passportNumLastRow = 1;
        for (int i = 0; i < othr.size() - 1; i++) {
            if (othr.get(i).getConfirmationClassRecordId().equals(othr.get(i + 1).getConfirmationClassRecordId())) {
                //可以合并
                passportNumLastRow++;
            } else {
                if (passportNumLastRow > passportNumFirstRow) {
                    for (int j = 0; j <= maxCol; j++) {
                        CellRangeAddress cellRangeAddress = new CellRangeAddress(passportNumFirstRow,
                                passportNumLastRow, j, j);
                        cellRangeAddresses.add(cellRangeAddress);
                    }
                }
                passportNumFirstRow = passportNumLastRow + 1;
                passportNumLastRow = passportNumFirstRow;
            }
            if (i == othr.size() - 2) {
                if (passportNumLastRow > passportNumFirstRow) {
                    for (int j = 0; j <= maxCol; j++) {
                        CellRangeAddress cellRangeAddress = new CellRangeAddress(passportNumFirstRow,
                                passportNumLastRow, j, j);
                        cellRangeAddresses.add(cellRangeAddress);
                    }
                }
            }
        }
        return cellRangeAddresses;
    }

    public static class CustomMergeStrategyTest implements SheetWriteHandler {

        /**
         * DTO数据类型
         */
        private final Class<?> elementType;
        // 增加自定义合并key如果自定义合并key为空按照原有策略走
        private List<CellRangeAddress> cellRangeAddressesList;

        public CustomMergeStrategyTest(Class<?> elementType, List<CellRangeAddress> cellRangeAddressesList) {
            this.elementType = elementType;
            this.cellRangeAddressesList = cellRangeAddressesList;
        }

        @Override
        public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
            // 所有数据写入完毕，开始批量合并
            Sheet sheet = writeSheetHolder.getSheet();
            if (CollectionUtils.isNotEmpty(cellRangeAddressesList)) {
                long startTime = System.currentTimeMillis();
                for (CellRangeAddress cellRangeAddress : cellRangeAddressesList) {
                    sheet.addMergedRegionUnsafe(cellRangeAddress);
                }
                long endTime = System.currentTimeMillis();
                System.out.println("合并完成，耗时：" + (endTime - startTime) + " 毫秒");
            }
        }
    }

    @Data
    public static class ConfirmationClassRecordExportVO {

        @ExcelProperty("序号")
        private Integer index;

        @ExcelProperty("员工号")
        private String employeeId;

        @ExcelProperty("姓名")
        private String name;

        @ExcelProperty("查班时间")
        private String checkTime;

        @ExcelProperty("部门名称")
        private String departmentName;

        @ExcelProperty("航班号")
        private String flightNumber;

        @ExcelProperty("航班时间")
        private String flightTime;

        @ExcelProperty("开始时间")
        private String startTime;

        @ExcelProperty("结束时间")
        private String endTime;

        @ExcelProperty("签到时间")
        private String signInTime;

        @ExcelProperty("任务类型")
        private String taskType;

        @ExcelProperty("出发站")
        private String departureStation;

        @ExcelProperty("到达站")
        private String arrivalStation;

        @ExcelProperty("机型")
        private String aircraftType;

        @ExcelProperty("机尾号")
        private String tailNumber;

        @ExcelProperty("岗位")
        private String position;

        @ExcelProperty("航班序列号")
        private Integer confirmationClassRecordId;

        @ExcelProperty("base")
        private String base;

        public ConfirmationClassRecordExportVO(Integer index) {
            this.index = index;
            this.employeeId = "******";
            this.name = "***";
            this.checkTime = "******";
            this.departmentName = "******";
            this.flightNumber = "****";
            this.flightTime = "";
            this.startTime = "2025-01-01 00:00:00";
            this.endTime = "2025-01-01 23:00:00";
            this.signInTime = ""; // 空值
            this.taskType = "****";
            this.departureStation = "";
            this.arrivalStation = "";
            this.aircraftType = "aaa";
            this.tailNumber = "bbbb";
            this.position = "乘务员";
            this.confirmationClassRecordId = index;
            this.base = "";
        }
    }

}