【POI-EXCEL-下拉框】POI导出excel下拉框数据太多导致下拉框不显示BUG修复

RT

最近在线上遇到一个很难受的BUG，我一度以为是我代码逻辑出了问题，用了Arthas定位分析之后，开始坚定了信心：大概率是POI的API有问题，比如写入数据过多。

PS：上图为正常的下拉框。但是，当下拉选项过多时（跟多少无关，而是跟字节数有关），会导致下拉框内容显示失败

解决办法

功夫不负有心人，根据百度找到了这篇文章《ava POI 利用隐藏sheet实现导出下拉数据太多为空》。

解决思路是：

使用隐藏sheet来实现下拉框，抛弃旧有的方法

翻译成我这边的代码之后，如下：

创建下拉框单例：

import cn.hutool.core.collection.CollectionUtil;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
import org.apache.poi.xssf.usermodel.XSSFDataValidationConstraint;
import org.apache.poi.xssf.usermodel.XSSFDataValidationHelper;
import org.apache.poi.xssf.usermodel.XSSFSheet;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

/**
 * @author feng.zhang
 * @since 2023-08-17 12:28
 */
public class CommonExcelUtil {

    /**
     * 给列加下拉选项
     *
     * @param sheet            sheet页
     * @param hiddentSheetName 隐藏sheet名
     * @param colName          需要绑定的列名
     * @param textList         具体下拉框内容，如   String[] textList = {"男","女};
     * @param firstRow         起始行（0起算第一行）
     * @param endRow           结束行（0起算第一行）
     * @param firstCol         起始列（0起算第一列）
     * @param endCol           结束列（0起算第一列）
     **/
    public static void createBox(Sheet sheet, String hiddentSheetName, String colName, String[] textList, int firstRow, int endRow, int firstCol, int endCol) {
        List<String> phaseNames = new ArrayList<>(Arrays.asList(textList));
        int row = CollectionUtil.isEmpty(phaseNames) ? 1 : phaseNames.size();
        String strFormula = hiddentSheetName + "!$" + colName + "$1:$" + colName + "$" + row;
        XSSFDataValidationConstraint hiddentConstraint = new XSSFDataValidationConstraint(DataValidationConstraint.ValidationType.LIST, strFormula);

        // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);

        // 数据有效性对象
        DataValidationHelper help = new XSSFDataValidationHelper((XSSFSheet) sheet);
        DataValidation validation = help.createValidation(hiddentConstraint, regions);
        sheet.addValidationData(validation);
    }
}

调用处代码：

private void setTopLevel(Workbook workbook) {
        String[] array = getDataArray();

        // 创建隐藏sheet
        final String hiddenSheetName = "hiddenSheetA";
        final String colName = "A";
        if (array.length > 0) {
            workbook.createSheet(hiddenSheetName);
            workbook.setSheetHidden(workbook.getSheetIndex(workbook.getSheet(hiddenSheetName)), true);
            Sheet sheet = workbook.getSheet(hiddenSheetName);

            //sheet.getLastRowNum无法区分 有一行和没有 所以这里先建一行
            sheet.createRow(0);

            int colNum = Integer.valueOf(colName.charAt(0)) - 65;
            Row row; //创建数据行
            sheet.setColumnWidth(colNum, 4000); //设置每列的列宽
            for (int j = 0; j < array.length; j++) {
                if (sheet.getLastRowNum() < j) {
                    row = sheet.createRow(j); //创建数据行
                } else {
                    row = sheet.getRow(j);
                }
                //设置对应单元格的值
                row.createCell(colNum).setCellValue(array[j]);
            }
        }

        CommonExcelUtil.createBox(workbook.getSheetAt(0), hiddenSheetName, colName, array, 2, topLevelArr.size() + 1000, 6, 6);
    }