POI设置单元格下拉列表（级联列表、自动填充）

import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;

import java.io.IOException;
import java.util.*;

/**
 * ExcelUtil
 *
 * @author maxzhao
 * @date 2021-03-24 14:51
 */
public class BootExcelUtil {

    /**
     * 给sheet页，添加下拉列表
     *
     * @param workbook    excel文件，用于添加Name
     * @param targetSheet 级联列表所在sheet页
     * @param options     级联数据 ['百度','阿里巴巴']
     * @param column      下拉列表所在列 从'A'开始
     * @param fromRow     下拉限制开始行
     * @param endRow      下拉限制结束行
     */
    public static void addValidationToSheet(Workbook workbook, Sheet targetSheet, Object[] options, char column, int fromRow, int endRow) {
        String hiddenSheetName = "sheet" + workbook.getNumberOfSheets();
        Sheet optionsSheet = workbook.createSheet(hiddenSheetName);
        String nameName = column + "_parent";
        int rowIndex = 0;
        Cell cellTemp;
        Row rowTemp;
        int columnIndex = 0;
        for (Object option : options) {
            columnIndex = 0;
            rowTemp = optionsSheet.createRow(rowIndex++);
            cellTemp = rowTemp.createCell(columnIndex++);
            cellTemp.setCellValue(option.toString());
        }
        createName(workbook, nameName, hiddenSheetName + "!$A$1:$A$" + options.length);
        DVConstraint constraint = DVConstraint.createFormulaListConstraint(nameName);
        CellRangeAddressList regions = new CellRangeAddressList(fromRow, endRow, (int) column - 'A', (int) column - 'A');
        targetSheet.addValidationData(new HSSFDataValidation(regions, constraint));
    }

    /**
     * 给sheet页  添加级联下拉列表
     *
     * @param workbook    excel
     * @param targetSheet sheet页
     * @param options     要添加的下拉列表内容  ， keys 是下拉列表1中的内容，每个Map.Entry.Value 是对应的级联下拉列表内容
     * @param keyColumn   下拉列表1位置
     * @param valueColumn 级联下拉列表位置
     * @param fromRow     级联限制开始行
     * @param endRow      级联限制结束行
     */
    public static void addValidationToSheet(Workbook workbook, Sheet targetSheet, Map<String, List<String>> options, char keyColumn, char valueColumn, int fromRow, int endRow) {
        String hiddenSheetName = "sheet" + workbook.getNumberOfSheets();
        Sheet hiddenSheet = workbook.createSheet(hiddenSheetName);
        List<String> firstLevelItems = new ArrayList<>();

        int rowIndex = 0;
        DVConstraint constraintTemp;
        Cell cellTemp;
        Row rowTemp;
        String parentTemp;
        for (Map.Entry<String, List<String>> entry : options.entrySet()) {
            parentTemp = formatNameName(entry.getKey());
            firstLevelItems.add(parentTemp);
            List<String> children = entry.getValue();

            int columnIndex = 0;
            rowTemp = hiddenSheet.createRow(rowIndex++);

            for (String child : children) {
                cellTemp = rowTemp.createCell(columnIndex++);
                cellTemp.setCellValue(child);
            }

            char lastChildrenColumn = (char) ((int) 'A' + children.size() - 1);
            createName(workbook, parentTemp, String.format(hiddenSheetName + "!$A$%s:$%s$%s", rowIndex, lastChildrenColumn, rowIndex));
            constraintTemp = DVConstraint.createFormulaListConstraint("INDIRECT($" + keyColumn + "1)");
            CellRangeAddressList regions = new CellRangeAddressList(fromRow, endRow, valueColumn - 'A', valueColumn - 'A');
            targetSheet.addValidationData(new HSSFDataValidation(regions, constraintTemp));
        }
        addValidationToSheet(workbook, targetSheet, firstLevelItems.toArray(), keyColumn, fromRow, endRow);
    }

    /**
     * 根据用户在keyColumn选择的key, 自动填充value到valueColumn
     *
     * @param workbook    excel
     * @param targetSheet sheet页
     * @param keyValues   匹配关系 {'百度','www.baidu.com'},{'淘宝','www.taobao.com'}
     * @param keyColumn  自动填充 要匹配的列（例如 网站中文名称）
     * @param valueColumn 匹配到的内容列（例如 网址）
     * @param fromRow     下拉限制开始行
     * @param endRow      下拉限制结束行
     */
    public static void addAutoMatchValidationToSheet(Workbook workbook, Sheet targetSheet, Map<String, String> keyValues, char keyColumn, char valueColumn, int fromRow, int endRow) {
        String hiddenSheetName = "sheet" + workbook.getNumberOfSheets();
        Sheet hiddenSheet = workbook.createSheet(hiddenSheetName);

        Cell cellTemp;
        Row rowTemp;
        // init the search region(A and B columns in hiddenSheet)
        int rowIndex = 0;
        for (Map.Entry<String, String> kv : keyValues.entrySet()) {
            rowTemp = hiddenSheet.createRow(rowIndex++);

            cellTemp = rowTemp.createCell(0);
            cellTemp.setCellValue(kv.getKey());

            cellTemp = rowTemp.createCell(1);
            cellTemp.setCellValue(kv.getValue());
        }
        String keyCellTemp;
        String formulaTemp;
        for (int i = fromRow; i <= endRow; i++) {
            Row totalSheetRow = targetSheet.getRow(i);
            if (totalSheetRow == null) {
                totalSheetRow = targetSheet.createRow(i);
            }
            cellTemp = totalSheetRow.getCell((int) valueColumn - 'A');
            if (cellTemp == null) {
                cellTemp = totalSheetRow.createCell((int) valueColumn - 'A');
            }
            keyCellTemp = String.valueOf(keyColumn) + (i + 1);
            formulaTemp = String.format("IF(ISNA(VLOOKUP(%s,%s!A:B,2,0)),\"\",VLOOKUP(%s,%s!A:B,2,0))", keyCellTemp, hiddenSheetName, keyCellTemp, hiddenSheetName);
            cellTemp.setCellFormula(formulaTemp);
        }
        // init the keyColumn as comboList
        addValidationToSheet(workbook, targetSheet, keyValues.keySet().toArray(), keyColumn, fromRow, endRow);
    }

    private static Name createName(Workbook workbook, String nameName, String formula) {
        Name name = workbook.createName();
        name.setNameName(nameName);
        name.setRefersToFormula(formula);
        return name;
    }

    /**
     * 隐藏excel中的sheet页
     *
     * @param workbook
     * @param start    需要隐藏的 sheet开始索引
     */
    private static void hideTempDataSheet(HSSFWorkbook workbook, int start) {
        for (int i = start; i < workbook.getNumberOfSheets(); i++) {
            workbook.setSheetHidden(i, true);
        }
    }

    /**
     * 不可数字开头
     *
     * @param name
     * @return
     */
    static String formatNameName(String name) {
        name = name.replaceAll(" ", "").replaceAll("-", "_").replaceAll(":", ".");
        if (Character.isDigit(name.charAt(0))) {
            name = "_" + name;
        }

        return name;
    }

    /**
     * poi
     * 测试
     *
     * @param args
     * @throws IOExceptionjava
     */
    public static void main(String[] args) throws IOException {

        //demo 单独下拉列表
        //BootExcelUtil.addValidationToSheet(workbook, sheet, new String[]{"百度", "阿里巴巴"}, 'C', 1, 200);
        //demo 级联下拉列表，一级不需要设置单独下拉
        Map<String, List<String>> data = new HashMap<>();
        data.put("百度系列", Arrays.asList("百度地图", "百度知道", "百度音乐"));
        data.put("阿里系列", Arrays.asList("淘宝", "支付宝", "钉钉"));
        //BootExcelUtil.addValidationToSheet(workbook, sheet, data, 'A', 'B', 1, 200);
        //demo 自动填充
        Map<String, String> kvs = new HashMap<>();
        kvs.put("百度", "www.baidu.com");
        kvs.put("阿里", "www.taobao.com");
        //BootExcelUtil.addAutoMatchValidationToSheet(workbook, sheet, kvs, 'D', 'E', 1, 200);
    }
}