poi-ooxml 設定Excel導出下拉框

導出Excel設定下拉框，代碼如下：

// 建立Excel
XSSFWorkbook workbook = new XSSFWorkbook();
workbook.createSheet("下拉清單測試");
int num = 3;
String[] data = new String[num];
for (int i = 0; i < num; i++) {
	data[i] = ("TEST1_TEST1" + i);
}
// 第1列添加下拉項
setValid(workbook, data, 0,0,true);

public static void setValid(
            Workbook workbook,
            String[] data,
            Integer firstRow,
            Integer column,
            Boolean valid,
            String headerName) {
        Sheet dataSheet = workbook.getSheetAt(0);
        CellRangeAddressList addressList = new CellRangeAddressList(firstRow, 500, column, column);
        DataValidationHelper helper = dataSheet.getDataValidationHelper();
        DataValidationConstraint constraint = buildConstraint(workbook, data);
        // 關聯上面的Name的名稱
        DataValidation validation = helper.createValidation(constraint, addressList);
        // 是否顯示下拉框箭頭
        validation.setSuppressDropDownArrow(true);
        if (valid) {
            // 單元格值和下拉項不符合時提示的值
            validation.setShowErrorBox(true);
            validation.createErrorBox("溫馨提示", "非下拉項中的值無法導入,請勿修改");
            /**
             * DataValidation.ErrorStyle.STOP 提示框為紅色,(重試,取消,幫助)選擇重試繼續編輯,選擇取消恢複為選擇的下拉項值
             * DataValidation.ErrorStyle.WARNING
             * 提示框為黃色,(是,否,取消,幫助)選擇是可儲存非下拉框值,選擇否繼續編輯,選擇取消恢複為選擇的下拉項值 DataValidation.ErrorStyle.INFO
             * 提示框為藍色,(确定,取消,幫助)選擇确定可儲存非下拉框值
             */
            validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
        }
        // 選擇單元格時候顯示的提示語
        validation.setShowPromptBox(true);
        validation.createPromptBox(headerName, "請選擇下拉項中的值");
        // 選擇下拉項後是否允許清除單元格
        validation.setEmptyCellAllowed(false);
        dataSheet.addValidationData(validation);
    }

    public static DataValidationConstraint buildConstraint(Workbook workbook, String[] data) {
        Sheet dataSheet = workbook.getSheetAt(0);
        DataValidationHelper dateSheetHelper = dataSheet.getDataValidationHelper();
        Arrays.stream(data).peek(po -> System.out.println(po.length()));
        int sum = Arrays.stream(data).mapToInt(String::length).sum();
        if (sum < 255) {
            // 下拉項總字元 < 255, 使用原生方式添加下拉框
            return dataSheet.getDataValidationHelper().createExplicitListConstraint(data);
        }
        // 通過隐藏sheet方式儲存下拉項資料
        int total = workbook.getNumberOfSheets();
        Sheet validSheet;
        if (total < 2) {
            // 預設導出的Excel隻有一個sheet,小于2表示未建立隐藏sheet,此時手動建立隐藏sheet
            validSheet = workbook.createSheet("資料驗證");
            workbook.setSheetHidden(1, false);
        } else {
            // 隐藏sheet已建立,直接擷取
            validSheet = workbook.getSheetAt(1);
        }
        // 本次在哪一列寫入下拉項資料
        Integer validIndex = 0;
        boolean notFind = true;
        for (int i = 0, length = data.length; i < length; i++) {
            Row row = validSheet.getRow(i);
            if (Objects.isNull(row)) {
                row = validSheet.createRow(i);
            }
            if (notFind) {
                notFind = false;
                while (Objects.nonNull(row.getCell(validIndex))) {
                    validIndex++;
                }
            }
            row.createCell(validIndex).setCellValue(data[i]);
        }
        String validSheetName = validSheet.getSheetName();
        String nameName = validSheetName + validIndex;
        Name name = workbook.createName();
        // 設定Name的名稱
        name.setNameName(nameName);
        // 4 $A$1:$A$N代表 以A列1行開始擷取N行下拉資料
        String reg = "!$%s$1:$%s$%d";
        char a = 'A';
        char c = (char) (a + validIndex);
        String format = String.format(reg, c, c, data.length);
        String formatName = validSheetName + format;
        // 設定引用哪個sheet的哪一列多少行的資料
        name.setRefersToFormula(formatName);
        // helper 由 dataSheet 建立, nameName 中包含了隐藏sheet的引用的下拉項資訊, 以此關聯二者
        return dateSheetHelper.createFormulaListConstraint(nameName);
    }