生成Excel时，DataValidation数量超过999个会导致excel无法打开

[CloudShen-107]

如题，最近在做一个联动下拉框的excel功能，发现当设置的DataValidation超过999行时，虽然可以成功生成文件，但打开会报错，提示需要修复，如果点击修复，所有DataValidation都会失效。
这个问题老版的easyexcel 4.x版本就有，切换到新版fastexcel后依然存在。

核心代码如下

...
for (int i = dataStartRow; i < dataRowMax; i++) { //dataRowMax大于999就会触发这个问题
    CellRangeAddressList rangeAddressList = new CellRangeAddressList(i, i, sonColIdx, sonColIdx);
    int trueIdx = i + 1;
    String constraint = MessageFormat.format("OFFSET({0}!$C$1,MATCH(${3}${1},{0}!$B$1:$B${2},0)-1,0,COUNTIF({0}!$B$1:$B${2},${3}${1}))", hiddenSheetName, trueIdx, totalSize, fatherColumn);
    DataValidationConstraint formula = dataValidationHelper.createFormulaListConstraint(constraint);
    setValidation(sheet, dataValidationHelper, formula, rangeAddressList);
}

private void setValidation(Sheet sheet, DataValidationHelper helper, DataValidationConstraint constraint, CellRangeAddressList addressList) {
    DataValidation dataValidation = helper.createValidation(constraint, addressList);
    dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
    dataValidation.setShowErrorBox(true);
    dataValidation.setSuppressDropDownArrow(true);
    dataValidation.createErrorBox("提示", "你输入的值未在备选列表中，请下拉选择合适的值");
    sheet.addValidationData(dataValidation);
}

[CloudShen-107]

不知道有没有某个自定义参数可以调整这个上限值？还是底层代码已经写死？

[xi-mad]

我写了一个简单的代码来验证这个问题，仅依赖了Apache的poi。当我将dataRowMax设置为1000时，Microsoft Excel 无法正常打开这个文件，但wps可以正常打开。当将dataRowMax设置为999时，Microsoft Excel和wps都可以正常打开该文件。因此这个问题看起来与fastexcel无关，是更底层的问题。希望能有所帮助。

I wrote a simple piece of code to verify this issue, relying only on Apache POI. When I set dataRowMax to 1000, Microsoft Excel fails to open the file properly, but WPS can open it without any issues. When dataRowMax is set to 999, both Microsoft Excel and WPS can open the file normally. Therefore, this issue seems unrelated to FastExcel and appears to be a lower-level problem.Hope this helps.

Main.java

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileOutputStream;
import java.io.IOException;
import java.text.MessageFormat;

public class Main {

    public static void main(String[] args) {
        // 目标文件名
        String fileName = "data_validation_issue.xlsx";

        // 创建工作簿
        Workbook workbook = new XSSFWorkbook();

        // 创建主Sheet和隐藏Sheet
        Sheet mainSheet = workbook.createSheet("Main");
        Sheet hiddenSheet = workbook.createSheet("Hidden");
        workbook.setSheetHidden(workbook.getSheetIndex(hiddenSheet), true); // 隐藏辅助Sheet

        try {
            // 填充隐藏Sheet数据
            fillHiddenSheet(hiddenSheet);

            // 在主Sheet中设置数据验证规则
            setDataValidation(mainSheet, hiddenSheet);

            // 输出文件到磁盘
            try (FileOutputStream fileOut = new FileOutputStream(fileName)) {
                workbook.write(fileOut);
                System.out.println("Excel文件生成完成：" + fileName);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 填充隐藏Sheet数据，模拟父列和子列的值
     */
    private static void fillHiddenSheet(Sheet hiddenSheet) {
        // 填充示例数据：父列和子列
        for (int i = 0; i < 20; i++) {
            Row row = hiddenSheet.createRow(i);
            row.createCell(0).setCellValue("Parent" + i);  // 父列
            row.createCell(1).setCellValue("Child" + i);   // 子列
        }
    }

    /**
     * 在主Sheet上设置数据验证规则（超过999行复现问题）
     */
    private static void setDataValidation(Sheet mainSheet, Sheet hiddenSheet) {
        DataValidationHelper dataValidationHelper = mainSheet.getDataValidationHelper();

        // 开始设置联动下拉框数据验证
        int dataStartRow = 0;   // 数据验证起始行
        int dataRowMax = 1000;  // 设置1000行，触发Excel报错
//        int dataRowMax = 999;  // 设置999行，不会触发Excel报错

        for (int i = dataStartRow; i < dataRowMax; i++) {
            // 数据验证的单元格范围：第i行，第1列
            CellRangeAddressList rangeAddressList = new CellRangeAddressList(i, i, 1, 1);

            // 构建OFFSET公式，引用隐藏Sheet数据
            String constraint = MessageFormat.format(
                    "OFFSET(Hidden!$A$1,MATCH(${0}${1},Hidden!$A$1:$A$20,0)-1,1,1)",
                    "B", i + 1
            );

            // 创建数据验证约束
            DataValidationConstraint formulaConstraint = dataValidationHelper.createFormulaListConstraint(constraint);

            // 将数据验证添加到Sheet中
            DataValidation dataValidation = dataValidationHelper.createValidation(formulaConstraint, rangeAddressList);
            dataValidation.setSuppressDropDownArrow(true);
            dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
            dataValidation.createErrorBox("错误", "无效的输入，请从下拉列表中选择");

            // 添加数据验证到主Sheet
            mainSheet.addValidationData(dataValidation);
        }
    }
}

pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>org.example</groupId>
  <artifactId>testValidation</artifactId>
  <version>1.0</version>

  <dependencies>
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>5.2.3</version>
    </dependency>
  </dependencies>
</project>