使用多个SheetHandler 对行进行初始化 Attempting to write a row[0] in the range [0,1901] that is already written to disk.

2023-06-30 10:04:07.817|1402|ERROR|[nio-8080-exec-1]|[TID: N/A]| c.a.p.f.i.ResponseExceptionHandler |系统异常 java.lang.IllegalArgumentException: Attempting to write a row[0] in the range [0,1901] that is already written to disk. org.apache.poi.xssf.streaming.SXSSFSheet.createRow(SXSSFSheet.java:131) org.apache.poi.xssf.streaming.SXSSFSheet.createRow(SXSSFSheet.java:65) com.alibaba.excel.util.WorkBookUtil.createRow(WorkBookUtil.java:88) com.alibaba.excel.context.WriteContextImpl.initHead(WriteContextImpl.java:245) com.alibaba.excel.context.WriteContextImpl.initSheet(WriteContextImpl.java:209) com.alibaba.excel.context.WriteContextImpl.currentSheet(WriteContextImpl.java:135) com.alibaba.excel.write.ExcelBuilderImpl.addContent(ExcelBuilderImpl.java:54) com.alibaba.excel.ExcelWriter.write(ExcelWriter.java:73) com.alibaba.excel.ExcelWriter.write(ExcelWriter.java:50)

代码 ： WriteSheet writeSheet = EasyExcel.writerSheet(sheetName) .head(ProductExcelDataConvert.buildHeader(excelExportModel)) .registerWriteHandler(new ProductImportTemplateCellHandler(excelExportModel)) .registerWriteHandler(new ProductImportTemplateSheetHandler(excelExportModel)) .registerWriteHandler(new ProductImportTemplateFormulaHandler(excelExportModel,sheetName)) .build(); excelWriter.write(Lists.newArrayList(), writeSheet);

ProductImportTemplateFormulaHandler ：

public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) { Sheet sheet = writeSheetHolder.getSheet(); if (sheet.getSheetName().equals(sheetName)){ setFormula(sheet); }

}
/**
 * excel设置公式
 */
public void setFormula(Sheet sheet) {


    // 设置公式
    Integer styleNoIndex  = null;
    Integer merStyleNoIndex = null;
    Integer mainPropertyNum = null;
    for (ExcelIndexModel excelIndexModel : excelExportModel.getExcelIndexModelList()) {
        if (ProductExcelHeaderMappingEnum.STYLE_NO.getFiledName().equals(excelIndexModel.getFieldName())) {
            styleNoIndex = excelIndexModel.getIndex();
        }
        if (ProductExcelHeaderMappingEnum.MER_STYLE_NO.getFiledName().equals(excelIndexModel.getFieldName())) {
            merStyleNoIndex = excelIndexModel.getIndex();
        }
        if (ProductExcelHeaderMappingEnum.MERCHANT_MAIN_PROPERTY_NO.getFiledName().equals(excelIndexModel.getFieldName())) {
            mainPropertyNum = excelIndexModel.getIndex();
        }
    }
    if (styleNoIndex  == null || merStyleNoIndex == null || mainPropertyNum == null) {
        return;
    }
    for (int i = 1; i <= 2001; i++) {
        Row row =  sheet.getRow(i);
        if (row == null) {
            row = sheet.createRow(i);
        }
        Cell cell = row.getCell(merStyleNoIndex);
        if (cell == null) {
            cell = row.createCell(merStyleNoIndex);
        }
        cell.setCellFormula("IFERROR(CONCAT(A"+ merStyleNoIndex +",B"+mainPropertyNum + "),\"\")");
    }
    sheet.setForceFormulaRecalculation(true);
}

ProductImportTemplateSheetHandler:

    for (int i = 0; i <= 2000; i++) {
        // "B"是指父类所在的列，i+1初始值为1代表从第2行开始，2要与“B”对应，为B的列号加1，假如第一个参数为“C”，那么最后一个参数就3
        char offset = (char) ('A' + excelExportModel.getCascadeIndex());
        int colNum = excelExportModel.getCascadeIndex() + 1;
        setDataValidation(offset, sheet, i + 1 , colNum);

        // 设置公式

// setFormula(sheet, styleNoIndex, merStyleNoIndex, mainPropertyNum,i+1); } sheet.setForceFormulaRecalculation(true);

private void setDataValidation(char offset, Sheet sheet, int rowNum, int colNum) { DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper(); String validationFormula1 = "INDIRECT($" + offset + (rowNum) + ")"; // String validationFormula2 = "INDIRECT($" + (char) (offset + 1) + (rowNum) + ")"; DataValidation dataValidation1 = getDataValidationByFormula(validationFormula1, rowNum, colNum, dataValidationHelper); // DataValidation dataValidation2 = getDataValidationByFormula(validationFormula2, rowNum, colNum + 1, dataValidationHelper); sheet.addValidationData(dataValidation1); // sheet.addValidationData(dataValidation2); }

private static DataValidation getDataValidationByFormula(String formulaString, int naturalRowIndex, int naturalColumnIndex, DataValidationHelper dataValidationHelper) {
    DataValidationConstraint dvConstraint = dataValidationHelper.createFormulaListConstraint(formulaString);
    CellRangeAddressList regions = new CellRangeAddressList(naturalRowIndex -1, 2001, naturalColumnIndex, naturalColumnIndex);
    DataValidation dataValidation = dataValidationHelper.createValidation(dvConstraint, regions);
    dataValidation.setEmptyCellAllowed(false);
    dataValidation.setShowErrorBox(true);
    // 设置输入信息提示信息
    dataValidation.createPromptBox("下拉选择提示", "请使用下拉方式选择合适的值！");
    return dataValidation;
}