apache poi 实现下拉框联动校验

本贴最后更新于 260 天前,其中的信息可能已经时移世易

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

步骤如下:

  1. 创建一个隐藏 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); }
  2. 将数据放入隐藏 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++; }
  3. 在主 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);
  4. 设置联动下拉框 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);
  5. 完整代码如下:

    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>

运行截图:

image

  • POI
    23 引用 • 21 回帖

相关帖子

欢迎来到这里!

我们正在构建一个小众社区,大家在这里相互信任,以平等 • 自由 • 奔放的价值观进行分享交流。最终,希望大家能够找到与自己志同道合的伙伴,共同成长。

注册 关于
请输入回帖内容 ...

推荐标签 标签

  • Postman

    Postman 是一款简单好用的 HTTP API 调试工具。

    4 引用 • 3 回帖 • 3 关注
  • 支付宝

    支付宝是全球领先的独立第三方支付平台,致力于为广大用户提供安全快速的电子支付/网上支付/安全支付/手机支付体验,及转账收款/水电煤缴费/信用卡还款/AA 收款等生活服务应用。

    29 引用 • 347 回帖
  • Latke

    Latke 是一款以 JSON 为主的 Java Web 框架。

    71 引用 • 535 回帖 • 830 关注
  • Solidity

    Solidity 是一种智能合约高级语言,运行在 [以太坊] 虚拟机(EVM)之上。它的语法接近于 JavaScript,是一种面向对象的语言。

    3 引用 • 18 回帖 • 445 关注
  • Shell

    Shell 脚本与 Windows/Dos 下的批处理相似,也就是用各类命令预先放入到一个文件中,方便一次性执行的一个程序文件,主要是方便管理员进行设置或者管理用的。但是它比 Windows 下的批处理更强大,比用其他编程程序编辑的程序效率更高,因为它使用了 Linux/Unix 下的命令。

    125 引用 • 74 回帖 • 2 关注
  • ngrok

    ngrok 是一个反向代理,通过在公共的端点和本地运行的 Web 服务器之间建立一个安全的通道。

    7 引用 • 63 回帖 • 656 关注
  • 开源

    Open Source, Open Mind, Open Sight, Open Future!

    415 引用 • 3598 回帖 • 1 关注
  • Caddy

    Caddy 是一款默认自动启用 HTTPS 的 HTTP/2 Web 服务器。

    10 引用 • 54 回帖 • 180 关注
  • Ngui

    Ngui 是一个 GUI 的排版显示引擎和跨平台的 GUI 应用程序开发框架,基于
    Node.js / OpenGL。目标是在此基础上开发 GUI 应用程序可拥有开发 WEB 应用般简单与速度同时兼顾 Native 应用程序的性能与体验。

    7 引用 • 9 回帖 • 407 关注
  • 链滴

    链滴是一个记录生活的地方。

    记录生活,连接点滴

    183 引用 • 3887 回帖
  • Node.js

    Node.js 是一个基于 Chrome JavaScript 运行时建立的平台, 用于方便地搭建响应速度快、易于扩展的网络应用。Node.js 使用事件驱动, 非阻塞 I/O 模型而得以轻量和高效。

    139 引用 • 269 回帖
  • Office

    Office 现已更名为 Microsoft 365. Microsoft 365 将高级 Office 应用(如 Word、Excel 和 PowerPoint)与 1 TB 的 OneDrive 云存储空间、高级安全性等结合在一起,可帮助你在任何设备上完成操作。

    5 引用 • 34 回帖
  • WordPress

    WordPress 是一个使用 PHP 语言开发的博客平台,用户可以在支持 PHP 和 MySQL 数据库的服务器上架设自己的博客。也可以把 WordPress 当作一个内容管理系统(CMS)来使用。WordPress 是一个免费的开源项目,在 GNU 通用公共许可证(GPLv2)下授权发布。

    46 引用 • 114 回帖 • 166 关注
  • jsDelivr

    jsDelivr 是一个开源的 CDN 服务,可为 npm 包、GitHub 仓库提供免费、快速并且可靠的全球 CDN 加速服务。

    5 引用 • 31 回帖 • 108 关注
  • InfluxDB

    InfluxDB 是一个开源的没有外部依赖的时间序列数据库。适用于记录度量,事件及实时分析。

    2 引用 • 106 关注
  • Hexo

    Hexo 是一款快速、简洁且高效的博客框架,使用 Node.js 编写。

    22 引用 • 148 回帖 • 12 关注
  • 笔记

    好记性不如烂笔头。

    311 引用 • 794 回帖
  • CSS

    CSS(Cascading Style Sheet)“层叠样式表”是用于控制网页样式并允许将样式信息与网页内容分离的一种标记性语言。

    198 引用 • 543 回帖 • 4 关注
  • SVN

    SVN 是 Subversion 的简称,是一个开放源代码的版本控制系统,相较于 RCS、CVS,它采用了分支管理系统,它的设计目标就是取代 CVS。

    29 引用 • 98 回帖 • 698 关注
  • Ruby

    Ruby 是一种开源的面向对象程序设计的服务器端脚本语言,在 20 世纪 90 年代中期由日本的松本行弘(まつもとゆきひろ/Yukihiro Matsumoto)设计并开发。在 Ruby 社区,松本也被称为马茨(Matz)。

    7 引用 • 31 回帖 • 271 关注
  • MongoDB

    MongoDB(来自于英文单词“Humongous”,中文含义为“庞大”)是一个基于分布式文件存储的数据库,由 C++ 语言编写。旨在为应用提供可扩展的高性能数据存储解决方案。MongoDB 是一个介于关系数据库和非关系数据库之间的产品,是非关系数据库当中功能最丰富,最像关系数据库的。它支持的数据结构非常松散,是类似 JSON 的 BSON 格式,因此可以存储比较复杂的数据类型。

    91 引用 • 59 回帖 • 2 关注
  • App

    App(应用程序,Application 的缩写)一般指手机软件。

    91 引用 • 384 回帖
  • MySQL

    MySQL 是一个关系型数据库管理系统,由瑞典 MySQL AB 公司开发,目前属于 Oracle 公司。MySQL 是最流行的关系型数据库管理系统之一。

    694 引用 • 537 回帖 • 5 关注
  • 书籍

    宋真宗赵恒曾经说过:“书中自有黄金屋,书中自有颜如玉。”

    84 引用 • 414 回帖
  • API

    应用程序编程接口(Application Programming Interface)是一些预先定义的函数,目的是提供应用程序与开发人员基于某软件或硬件得以访问一组例程的能力,而又无需访问源码,或理解内部工作机制的细节。

    79 引用 • 431 回帖
  • JRebel

    JRebel 是一款 Java 虚拟机插件,它使得 Java 程序员能在不进行重部署的情况下,即时看到代码的改变对一个应用程序带来的影响。

    26 引用 • 78 回帖 • 685 关注
  • 学习

    “梦想从学习开始,事业从实践起步” —— 习近平

    172 引用 • 541 回帖