apache poi 实现下拉框联动校验

apache poi 提供了 DataValidation 接口让我们可以轻松实现 Excel 下拉框数据局校验。但是下拉框联动校验是无法直接通过 DataValidation 实现，所以我们可以通过其他方式间接实现。

‍

步骤如下：

创建一个隐藏 sheet

 private static void createHiddenSheet(List<String> provinceList, Map<String, String[]> regionMap, Workbook workbook) {
        String hiddenSheetName = "region";
        Sheet hiddenSheet = workbook.createSheet(hiddenSheetName);

        // 这里也可以设置 hidden 为 false 这样可以直接看到 sheet 内容
        workbook.setSheetHidden(workbook.getSheetIndex(hiddenSheet), true);
    }

将数据放入隐藏 sheet

        int rowNum = 0;
        // 第一行存放省数据
        Row row = hiddenSheet.createRow(rowNum);
        for (int i = 0; i < provinceList.size(); i++) {
            Cell cell = row.createCell(i);
            cell.setCellValue(provinceList.get(i));
        }

        rowNum++;
        for (String key : regionMap.keySet()) {
            String[] dataArray = regionMap.get(key);
            // 循环创建行，每行存放一个数组
            row = hiddenSheet.createRow(rowNum);
            // key 放在每行第一个，value 放在每行的后面
            Cell keyCell = row.createCell(0);
            keyCell.setCellValue(key);
            for (int i = 0, length = dataArray.length; i < length; i++) {
                Cell cell = row.createCell(i + 1);
                cell.setCellValue(dataArray[i]);
            }
            Name name = workbook.createName();
            // 将key 设置为下拉框的key
            name.setNameName(key);
            String formula = hiddenSheetName + "!$B$" + (rowNum + 1) + ":$" + (convertNumberToLetter(dataArray.length + 1)) + "$" + (rowNum + 1);
            name.setRefersToFormula(formula);

            // 可以将formula 放在最后一列
            Cell formulaCell = row.createCell(dataArray.length + 1);
            formulaCell.setCellValue(formula);

            rowNum++;
        }

在主 sheet 中使用 formula 来使用隐藏 sheet 的数据

    DataValidationHelper helper = mainSheet.getDataValidationHelper();

    // 设置省份下拉框
    CellRangeAddressList provRangeAddressList = new CellRangeAddressList(1, 1000, 0, 0);
    // formula 为  region!$A$1:$E$1
    DataValidationConstraint dvConstraint = helper.createFormulaListConstraint("region!$A$1:$" + (convertNumberToLetter(provinceList.size())) + "$1");
    DataValidation provinceDataValidation = helper.createValidation(dvConstraint, provRangeAddressList);

    provinceDataValidation.setSuppressDropDownArrow(true);
    mainSheet.addValidationData(provinceDataValidation);

设置联动下拉框 DataValidation

 // 设置市下拉框  firstCol lastCol 根据实际情况设置
 CellRangeAddressList cityRange = new CellRangeAddressList(1, 1000, 1, 1);
 DataValidationConstraint cityConstraint = helper.createFormulaListConstraint("INDIRECT(A2)");
 DataValidation cityValidation = helper.createValidation(cityConstraint, cityRange);
 mainSheet.addValidationData(cityValidation);


 // 设置县下拉框 firstCol lastCol 根据实际情况设置
 CellRangeAddressList districtRange = new CellRangeAddressList(1, 1000, 2, 2);
 DataValidation districtValidation = helper.createValidation(helper.createFormulaListConstraint("INDIRECT(B2)"), districtRange);
 mainSheet.addValidationData(districtValidation);

完整代码如下:

package com.shang;

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.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
* @author: shangwei
* @date: 2024/11/3 13:01
*/
public class ExcelUtil {

  public static void createExcel(String path, List<String> provinceList, Map<String, String[]> regionMap) {
      try {
          Workbook workbook = new XSSFWorkbook();
          createHiddenSheet(provinceList, regionMap, workbook);


          Sheet mainSheet = workbook.createSheet("mainSheet");
          // 主sheet 第一行数据
          String[] titles = {"省", "市", "县"};
          int rowNum = 0;
          Row row = mainSheet.createRow(rowNum);
          for (int i = 0; i < titles.length; i++) {
              Cell cell = row.createCell(i);
              cell.setCellValue(titles[i]);
          }

          DataValidationHelper helper = mainSheet.getDataValidationHelper();

          // 设置省份下拉框
          CellRangeAddressList provRangeAddressList = new CellRangeAddressList(1, 1000, 0, 0);
          // formula 为  region!$A$1:$E$1
          DataValidationConstraint dvConstraint = helper.createFormulaListConstraint("region!$A$1:$" + (convertNumberToLetter(provinceList.size())) + "$1");
          DataValidation provinceDataValidation = helper.createValidation(dvConstraint, provRangeAddressList);

          provinceDataValidation.setSuppressDropDownArrow(true);
          mainSheet.addValidationData(provinceDataValidation);

          // 设置市下拉框  firstCol lastCol 根据实际情况设置
          CellRangeAddressList cityRange = new CellRangeAddressList(1, 1000, 1, 1);
          DataValidationConstraint cityConstraint = helper.createFormulaListConstraint("INDIRECT(A2)");
          DataValidation cityValidation = helper.createValidation(cityConstraint, cityRange);
          mainSheet.addValidationData(cityValidation);


          // 设置县下拉框 firstCol lastCol 根据实际情况设置
          CellRangeAddressList districtRange = new CellRangeAddressList(1, 1000, 2, 2);
          DataValidation districtValidation = helper.createValidation(helper.createFormulaListConstraint("INDIRECT(B2)"), districtRange);
          mainSheet.addValidationData(districtValidation);


          FileOutputStream fileOutputStream = new FileOutputStream(path);
          workbook.write(fileOutputStream);

      } catch (Exception e) {
          e.printStackTrace();
      }
  }

  private static void createHiddenSheet(List<String> provinceList, Map<String, String[]> regionMap, Workbook workbook) {
      String hiddenSheetName = "region";
      Sheet hiddenSheet = workbook.createSheet(hiddenSheetName);
      int rowNum = 0;
      // 第一行存放省数据
      Row row = hiddenSheet.createRow(rowNum);
      for (int i = 0; i < provinceList.size(); i++) {
          Cell cell = row.createCell(i);
          cell.setCellValue(provinceList.get(i));
      }

      rowNum++;
      for (String key : regionMap.keySet()) {
          String[] dataArray = regionMap.get(key);
          // 循环创建行，每行存放一个数组
          row = hiddenSheet.createRow(rowNum);
          // key 放在每行第一个，value 放在每行的后面
          Cell keyCell = row.createCell(0);
          keyCell.setCellValue(key);
          for (int i = 0, length = dataArray.length; i < length; i++) {
              Cell cell = row.createCell(i + 1);
              cell.setCellValue(dataArray[i]);
          }
          Name name = workbook.createName();
          // 将key 设置为下拉框的key
          name.setNameName(key);
          String formula = hiddenSheetName + "!$B$" + (rowNum + 1) + ":$" + (convertNumberToLetter(dataArray.length + 1)) + "$" + (rowNum + 1);
          name.setRefersToFormula(formula);

          // 可以将formula 放在最后一列
          Cell formulaCell = row.createCell(dataArray.length + 1);
          formulaCell.setCellValue(formula);

          rowNum++;
      }
      // 这里也可以设置 hidden 为 false 这样可以直接看到 sheet 内容
      workbook.setSheetHidden(workbook.getSheetIndex(hiddenSheet), true);
  }

  /**
   * 将数字 1 到 26 转换为对应的字母 A 到 Z。
   *
   * @param number 要转换的数字，范围是 1 到 26。
   * @return 对应的字母。
   */
  public static String convertNumberToLetter(int number) {
      if (number < 1 || number > 26) {
          throw new IllegalArgumentException("Number must be between 1 and 26");
      }
      return String.valueOf((char) ('A' + number - 1));
  }


  public static void main(String[] args) {
      Map<String, String[]> regionMap = new HashMap<>();
      List<String> provinceList = Arrays.asList("湖北省", "湖南省", "广东省", "江苏省", "浙江省");

      regionMap.put("湖北省", new String[]{"武汉市", "黄石市", "十堰市", "宜昌市", "襄樊市", "鄂州市", "荆门市", "孝感市", "荆州市", "黄冈市", "咸宁市", "随州市"});
      regionMap.put("湖南省", new String[]{"长沙市", "株洲市", "湘潭市", "衡阳市", "邵阳市", "岳阳市", "常德市", "张家界市", "益阳市", "郴州市", "永州市", "怀化市"});
      regionMap.put("广东省", new String[]{"广州市", "韶关市", "深圳市", "珠海市", "汕头市", "佛山市", "江门市", "湛江市", "茂名市", "肇庆市", "惠州市", "梅州市", "汕尾市", "河源市", "阳江市", "清远市"});
      regionMap.put("江苏省", new String[]{"南京市", "无锡市", "徐州市", "常州市", "苏州市", "南通市", "连云港市", "淮安市", "盐城市", "扬州市", "镇江市", "泰州市", "宿迁市"});
      regionMap.put("浙江省", new String[]{"杭州市", "宁波市", "温州市", "嘉兴市", "湖州市", "绍兴市", "金华市", "衢州市", "舟山市", "台州市", "丽水市"});
      regionMap.put("武汉市", new String[]{"江岸镇", "江汉镇", "江夏镇", "硚口镇", "武昌镇", "江夏镇"});
      regionMap.put("黄石市", new String[]{"黄石港镇", "西塞山镇", "下陆镇", "大冶镇", "大冶镇"});

      String path = "/Users/shangwei/Desktop/example" + System.currentTimeMillis() + ".xlsx";
      createExcel(path, provinceList, regionMap);

  }


}