在工作表中添加大量 ValidationData 时，POI 会写入损坏的 .xlsx

Question

我想.xlsx在 JAVA 中使用 POI 编写一个大文件（50K 行）。我希望每一行都包含多个下拉单元格。当行数小于 30K 时代码工作正常，但如果行数超过 35K 则写入损坏的文件。

我试过了SXSSFWorkbook，XSSFWorkbook但没有一个对我有用。

这是代码：

        SXSSFWorkbook workbook = new SXSSFWorkbook(100);

        SXSSFSheet sheet = workbook.createSheet();

        String[] optionsArray = new String[] {"1000.00","2000.00"};

        int no_of_rows = 35000;

        for(int i=0;i<=no_of_rows;i++) {

            SXSSFRow row1 = sheet.createRow(i);

            SXSSFCell r1c1 = row1.createCell(0);

            DataValidationConstraint  constraint1 = sheet.getDataValidationHelper().createExplicitListConstraint(optionsArray);

            CellRangeAddressList addressList1 = new CellRangeAddressList(i, i, 0, 0);

            DataValidation dataValidation1 = sheet.getDataValidationHelper().createValidation(constraint1, addressList1);

            sheet.addValidationData(dataValidation1);

            r1c1.setCellValue("1000.00");   

            SXSSFCell r1c2 = row1.createCell(1);

            DataValidationConstraint  constraint2 = sheet.getDataValidationHelper().createExplicitListConstraint(optionsArray);

            CellRangeAddressList addressList2 = new CellRangeAddressList(i, i, 1, 1);

            DataValidation dataValidation2 = sheet.getDataValidationHelper().createValidation(constraint2, addressList2);

            sheet.addValidationData(dataValidation2);

            r1c2.setCellValue("2000.00");

        }

        FileOutputStream fos =new FileOutputStream(new File("c:\\data\\testout.xlsx"));

        workbook.write(fos);

        workbook.close();

        fos.close();

我希望 POI 至少能够很好地处理 50K 行此类数据。

Answer 1

不要DataValidation为每个单元格创建一个。相反，只创建两个需要的数据验证，一个A1:A50001用于B1:B50001. 为此，CellRangeAddressLists 可以包含整个列范围：CellRangeAddressList addressList1 = new CellRangeAddressList(0, no_of_rows, 0, 0);和CellRangeAddressList addressList1 = new CellRangeAddressList(0, no_of_rows, 1, 1);你的例子：import java.io.File;import java.io.FileOutputStream;import org.apache.poi.ss.util.*;import org.apache.poi.ss.usermodel.*;import org.apache.poi.xssf.streaming.*;class CreateSXSSFWorkbookDataValidations {&nbsp; &nbsp; public static void main(String[] args) throws Exception {&nbsp; &nbsp; &nbsp; &nbsp; SXSSFWorkbook workbook = new SXSSFWorkbook(100);&nbsp; &nbsp; &nbsp; &nbsp; SXSSFSheet sheet = workbook.createSheet();&nbsp; &nbsp; &nbsp; &nbsp; String[] optionsArray = new String[] {"1000.00","2000.00"};&nbsp; &nbsp; &nbsp; &nbsp; int no_of_rows = 50000;&nbsp; &nbsp; &nbsp; &nbsp; for(int i=0;i<=no_of_rows;i++) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SXSSFRow row1 = sheet.createRow(i);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SXSSFCell r1c1 = row1.createCell(0);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; r1c1.setCellValue("1000.00");&nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SXSSFCell r1c2 = row1.createCell(1);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; r1c2.setCellValue("2000.00");&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; DataValidationConstraint constraint1 = sheet.getDataValidationHelper().createExplicitListConstraint(optionsArray);&nbsp; &nbsp; &nbsp; &nbsp; CellRangeAddressList addressList1 = new CellRangeAddressList(0, no_of_rows, 0, 0);&nbsp; &nbsp; &nbsp; &nbsp; DataValidation dataValidation1 = sheet.getDataValidationHelper().createValidation(constraint1, addressList1);&nbsp; &nbsp; &nbsp; &nbsp; sheet.addValidationData(dataValidation1);&nbsp; &nbsp; &nbsp; &nbsp; DataValidationConstraint constraint2 = sheet.getDataValidationHelper().createExplicitListConstraint(optionsArray);&nbsp; &nbsp; &nbsp; &nbsp; CellRangeAddressList addressList2 = new CellRangeAddressList(0, no_of_rows, 1, 1);&nbsp; &nbsp; &nbsp; &nbsp; DataValidation dataValidation2 = sheet.getDataValidationHelper().createValidation(constraint2, addressList2);&nbsp; &nbsp; &nbsp; &nbsp; sheet.addValidationData(dataValidation2);&nbsp; &nbsp; &nbsp; &nbsp; FileOutputStream fos = new FileOutputStream(new File("testout.xlsx"));&nbsp; &nbsp; &nbsp; &nbsp; workbook.write(fos);&nbsp; &nbsp; &nbsp; &nbsp; workbook.close();&nbsp; &nbsp; &nbsp; &nbsp; fos.close();&nbsp; &nbsp; }}当不同的唯一数据验证的计数超过限制时，不可能对每个单个单元格进行不同的唯一数据验证。这个限制是一个Excel限制，而不是apache poi一个。Excel 规范和限制中没有记录这种数据验证的特殊限制。但这可能与唯一单元格格式/单元格样式 (64,000) 的限制或工作表中超链接的限制 (66,530) 有关。