Java基于poi导出带下拉框的excel

直接上工具类

package com.ybg.utils;

/**
 * @author wangshiji
 * @description: TODO
 * @date 2024/1/5 10:20
 */
import cn.hutool.core.collection.CollectionUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;


import cn.hutool.core.date.DateUtil;
import cn.hutool.core.io.FileUtil;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddressList;

import java.io.File;
import java.io.FileOutputStream;
import java.util.*;

/**
 * @author zhangjianshan on 2023-04-30
 */
public class ExcelOutUtil {


    private final Workbook workbook;

    private XSSFSheet sheet;

    /**
     * 第一行
     */
    private int firstRow = 1;

    /**
     * 数据
     */
    private List<String> selectDateList;

    /**
     * 隐藏页
     */
    private String hiddenSheetName = "hidden";

    public ExcelOutUtil(Workbook book) {
        this.workbook = book;
    }

    public ExcelOutUtil createSheet(String sheetName) {
        Sheet sheet = workbook.getSheet(sheetName);
        if (Objects.nonNull(sheet)) {
            this.sheet = (XSSFSheet) sheet;
        } else {
            this.sheet = (XSSFSheet) workbook.createSheet(sheetName);
        }
        return this;
    }
    //titles 表格标题 colimnWidths 表格列宽度，为null默认，与titles每一项对应，validationRules日期校验
    public ExcelOutUtil addTitles(List<String> titles, List<Integer> columnWidths, Map<String, String> validationRules,List<String> ruleString) {
        XSSFRow titleRow = sheet.createRow(firstRow - 1);
        for (int i = 0; i < titles.size(); i++) {
            XSSFCell cell = titleRow.createCell(i);
            //避免身份证被识别为数字添加，后面想给单元格设置其它类型的，更改此段代码即可，这里想方便点，不想传值了，
            // 可使用下面方式设置每个单元格格式，接收一个Map<String, String>，如果用到请重载方法
            if(ruleString !=null && ruleString.size()>0 && ruleString.contains(titles.get(i))) {
                // 设置单元格格式为文本格式
                CellStyle style = workbook.createCellStyle();
                XSSFDataFormat format = (XSSFDataFormat)workbook.createDataFormat();
                style.setDataFormat(format.getFormat("@"));
                sheet.setDefaultColumnStyle(i, style ); //columnIndex表示第几列
            }
            // 设置单元格值类型
            //if (i < cellTypes.size() && cellTypes.get(i) != null) {
                //cell.setCellType(cellTypes.get(i));
            //}
            cell.setCellValue(titles.get(i));

            // 如果指定了列宽度，则设置列宽
            if (i < columnWidths.size() && columnWidths.get(i) != null) {
                sheet.setColumnWidth(i, columnWidths.get(i));
            }
            // 添加列的验证
            setColumnValidation(sheet, i, titles.get(i), validationRules);
        }
        return this;
    }

    public ExcelOutUtil setColumnValidation(XSSFSheet sheet, int columnIndex, String title, Map<String, String> validationRules) {
        if (validationRules.containsKey(title)) {
            String validationType = validationRules.get(title);
            switch (validationType) {
                case "date":
                    setDateFormatValidation(sheet, columnIndex);
                    break;
                // 如果有其他验证规则，可以在这里添加
                // 例如：case "custom": setCustomValidation(sheet, columnIndex); break;
            }
        }
        return this;
    }

    //日期格式校验，输入日期为yyyy-MM-dd格式
    public ExcelOutUtil setDateFormatValidation(XSSFSheet sheet, int columnIndex) {
        DataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);

        // 设置整列的数据验证，确保日期大于等于1900-01-01
        DataValidationConstraint dateConstraint = dvHelper.createDateConstraint(
                DataValidationConstraint.OperatorType.GREATER_OR_EQUAL,
                "1900-01-01",
                null,
                "yyyy-MM-dd"
        );

        CellRangeAddressList dateRange = new CellRangeAddressList(firstRow, 1048575, columnIndex, columnIndex);
        DataValidation dateValidation = dvHelper.createValidation(dateConstraint, dateRange);

        // 设置错误消息
        dateValidation.createErrorBox("日期无效", "请输入格式为 yyyy-MM-dd 且大于等于 1900-01-01 的有效日期。");

        // 显示错误提示框并抑制下拉箭头
        dateValidation.setShowErrorBox(true);
        dateValidation.setSuppressDropDownArrow(true);

        // 将验证添加到工作表
        sheet.addValidationData(dateValidation);

        return this;
    }

    public ExcelOutUtil createSelectDateList(List<String> selectDateList) {
        this.selectDateList = selectDateList;
        return this;
    }

    public ExcelOutUtil createHiddenName(String hiddenSheetName) {
        this.hiddenSheetName = hiddenSheetName;
        return this;
    }

    public ExcelOutUtil createFirstRow(int firstRow) {
        this.firstRow = firstRow;
        return this;
    }

    /**
     * 基于数据有效性序列设置下拉(字符数有限制)
     *
     * @param columnIndex 第几列从0开始
     */
    public ExcelOutUtil effectivenessSelectData(Integer columnIndex) {
        XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
        DataValidationConstraint provConstraint = dvHelper.createExplicitListConstraint(selectDateList.toArray(new String[0]));
        setSelectParameter(sheet, dvHelper, provConstraint, columnIndex, columnIndex);
        return this;
    }


    /**
     * 基于数据有效性序列设置下拉
     *
     * @param columnIndex 第几列从0开始
     */
    public ExcelOutUtil sheetSelectData(Integer columnIndex) {
        //创建隐藏sheet
        this.createHiddenSheet();
        XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
        DataValidationConstraint provConstraint = dvHelper.createFormulaListConstraint(hiddenSheetName + "!$A:$A");
        setSelectParameter(sheet, dvHelper, provConstraint, columnIndex, columnIndex);
        return this;
    }

    public void setSelectParameter(XSSFSheet sheet, XSSFDataValidationHelper dvHelper, DataValidationConstraint provConstraint, int firstCol, int lastCol) {
        //四个参数分别是起始行、终止行、起始列、终止列
        CellRangeAddressList proRangeAddressList = new CellRangeAddressList(firstRow, 65535, firstCol, lastCol);
        DataValidation provinceDataValidation = dvHelper.createValidation(provConstraint, proRangeAddressList);
        //验证
        provinceDataValidation.createErrorBox("error", "请选择正确的类型");
        provinceDataValidation.setShowErrorBox(true);
        provinceDataValidation.setSuppressDropDownArrow(true);
        sheet.addValidationData(provinceDataValidation);
    }

    public void createHiddenSheet() {
        XSSFSheet hiddenSheet = (XSSFSheet) workbook.getSheet(hiddenSheetName);
        if (Objects.isNull(hiddenSheet)) {
            hiddenSheet = (XSSFSheet) workbook.createSheet(hiddenSheetName);
        }
        //填充数据前设置隐藏列
        workbook.setSheetHidden(workbook.getSheetIndex(hiddenSheetName), true);
        for (int i = 0; i < selectDateList.size(); i++) {
            XSSFRow row = hiddenSheet.createRow(i);
            XSSFCell cell = row.createCell(0);
            cell.setCellValue(selectDateList.get(i));
        }
    }

    public void writeFile() {
        writeFile(workbook);
    }


    public static void writeFile(Workbook book) {
        try {
            String storeName = System.currentTimeMillis() + ".xlsx";
            String folder = "template/" + DateUtil.format(DateUtil.date(), "yyMMdd") + "/";
            String attachmentFolder = "E://" + File.separator;
            String address = folder + storeName;
            FileUtil.mkdir(attachmentFolder + folder);
            FileOutputStream fileOut = new FileOutputStream(attachmentFolder + address);
            book.write(fileOut);
            fileOut.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    public static void main(String[] args) {
        // Create an Excel workbook
        ExcelOutUtil excelUtil = new ExcelOutUtil(new XSSFWorkbook());

        // Create a sheet named "SampleSheet"
        excelUtil.createSheet("SampleSheet")
                .createFirstRow(1);

        // Define titles for the Excel template
        List<String> titles = Arrays.asList("姓名", "性别", "政治面貌","入职时间","毕业时间");

        // Add titles to the Excel sheet
        List<Integer> columnWidths = Arrays.asList(null, null, 4000);  // 为 "政治面貌" 列设置宽度

        Map<String, String> validationRules = new HashMap<>();
        validationRules.put("入职时间", "date");
        validationRules.put("毕业时间", "date");
        List<String> list = Arrays.asList("身份证", "部门编号", "工号");
        //创建表格
        excelUtil.addTitles(titles, columnWidths, validationRules,list);

        // Define data for the "性别" (Gender) dropdown list
        List<String> genderList = Arrays.asList("男", "女", "未知");

        // Create a hidden sheet with the "性别" (Gender) dropdown list
        excelUtil.createSelectDateList(genderList)
                .effectivenessSelectData(titles.indexOf("性别"));

        // Define data for the "政治面貌" (Political Affiliation) dropdown list
        List<String> politicalAffiliationList = Arrays.asList("团员", "党员");

        // Create a hidden sheet with the "政治面貌" (Political Affiliation) dropdown list
        excelUtil.createSelectDateList(politicalAffiliationList)
                .sheetSelectData(titles.indexOf("政治面貌"));

        // Write the Excel file
        excelUtil.writeFile();
    }
}

在这个工具类里因为有身份证的存在，防止身份证被表格转换，给这列设置为了文本格式，然后时间格式添加了日期校验。

如果有多个下拉框，只需要设置隐藏表单后加以验证规则即可。（下拉框值少的情况下可使用下列代码中性别创建方式，校验值比较多的情况下建议使用政治面貌及户籍类型方式创建下拉框，否则极有可能导出文件出现校验问题）

    //"性别" (Gender)
        List<String> genderList = Arrays.asList("男", "女", "未知");
        excelUtil.createSelectDateList(genderList)
                .effectivenessSelectData(titles.indexOf("性别"));
        List<String> politicalAffiliationList = PoliticalOutlook.getDescriptionsWithoutDefault();
        excelUtil.createSelectDateList(politicalAffiliationList)
                .createHiddenName("hidden_political")  // 不再使用 "hidden_select_data"
                .sheetSelectData(titles.indexOf("政治面貌"));

        List<String> hjList = HjlxEnum.getDescriptionsWithoutDefault();
        excelUtil.createSelectDateList(hjList)
                .createHiddenName("hidden_hj")
                .sheetSelectData(titles.indexOf("户籍类型"));