郑州市多商家网站制作公司,房地产图文制作网站,做国外搞笑网站,婚纱摄影网站设计模板apache poi 提供了 DataValidation 接口 让我们可以轻松实现 Excel 下拉框数据局校验。但是下拉框联动校验是无法直接通过 DataValidation 实现#xff0c;所以我们可以通过其他方式间接实现。
步骤如下#xff1a;
创建一个隐藏 sheet private static void create…apache poi 提供了 DataValidation 接口 让我们可以轻松实现 Excel 下拉框数据局校验。但是下拉框联动校验是无法直接通过 DataValidation 实现所以我们可以通过其他方式间接实现。
步骤如下
创建一个隐藏 sheet private static void createHiddenSheet(ListString provinceList, MapString, 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 设置为下拉框的keyname.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$1DataValidationConstraint 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, ListString provinceList, MapString, 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$1DataValidationConstraint 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(ListString provinceList, MapString, 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 设置为下拉框的keyname.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) {MapString, String[] regionMap new HashMap();ListString 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 依赖
dependencygroupIdorg.apache.poi/groupIdartifactIdpoi-ooxml/artifactIdversion5.2.3/version/dependencydependencygroupIdcommons-io/groupIdartifactIdcommons-io/artifactIdversion2.11.0/version/dependency运行截图:
