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); } }
相关 Maven 依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.2.3</version>
</dependency>
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.11.0</version>
</dependency>
运行截图:
欢迎来到这里！
我们正在构建一个小众社区，大家在这里相互信任，以平等 • 自由 • 奔放的价值观进行分享交流。最终，希望大家能够找到与自己志同道合的伙伴，共同成长。
注册 关于