apache poi 实现下拉框联动校验

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

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 回帖

相关帖子

欢迎来到这里!

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

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

推荐标签 标签

  • RYMCU

    RYMCU 致力于打造一个即严谨又活泼、专业又不失有趣,为数百万人服务的开源嵌入式知识学习交流平台。

    4 引用 • 6 回帖 • 60 关注
  • 小说

    小说是以刻画人物形象为中心,通过完整的故事情节和环境描写来反映社会生活的文学体裁。

    32 引用 • 108 回帖
  • JRebel

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

    26 引用 • 78 回帖 • 675 关注
  • 阿里巴巴

    阿里巴巴网络技术有限公司(简称:阿里巴巴集团)是以曾担任英语教师的马云为首的 18 人,于 1999 年在中国杭州创立,他们相信互联网能够创造公平的竞争环境,让小企业通过创新与科技扩展业务,并在参与国内或全球市场竞争时处于更有利的位置。

    43 引用 • 221 回帖 • 59 关注
  • JWT

    JWT(JSON Web Token)是一种用于双方之间传递信息的简洁的、安全的表述性声明规范。JWT 作为一个开放的标准(RFC 7519),定义了一种简洁的,自包含的方法用于通信双方之间以 JSON 的形式安全的传递信息。

    20 引用 • 15 回帖 • 27 关注
  • Facebook

    Facebook 是一个联系朋友的社交工具。大家可以通过它和朋友、同事、同学以及周围的人保持互动交流,分享无限上传的图片,发布链接和视频,更可以增进对朋友的了解。

    4 引用 • 15 回帖 • 450 关注
  • Kotlin

    Kotlin 是一种在 Java 虚拟机上运行的静态类型编程语言,由 JetBrains 设计开发并开源。Kotlin 可以编译成 Java 字节码,也可以编译成 JavaScript,方便在没有 JVM 的设备上运行。在 Google I/O 2017 中,Google 宣布 Kotlin 成为 Android 官方开发语言。

    19 引用 • 33 回帖 • 78 关注
  • 又拍云

    又拍云是国内领先的 CDN 服务提供商,国家工信部认证通过的“可信云”,乌云众测平台认证的“安全云”,为移动时代的创业者提供新一代的 CDN 加速服务。

    20 引用 • 37 回帖 • 579 关注
  • Office

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

    5 引用 • 34 回帖
  • 资讯

    资讯是用户因为及时地获得它并利用它而能够在相对短的时间内给自己带来价值的信息,资讯有时效性和地域性。

    56 引用 • 85 回帖
  • frp

    frp 是一个可用于内网穿透的高性能的反向代理应用,支持 TCP、UDP、 HTTP 和 HTTPS 协议。

    17 引用 • 7 回帖
  • 正则表达式

    正则表达式(Regular Expression)使用单个字符串来描述、匹配一系列遵循某个句法规则的字符串。

    31 引用 • 94 回帖
  • Flume

    Flume 是一套分布式的、可靠的,可用于有效地收集、聚合和搬运大量日志数据的服务架构。

    9 引用 • 6 回帖 • 661 关注
  • 旅游

    希望你我能在旅途中找到人生的下一站。

    98 引用 • 903 回帖 • 1 关注
  • Bug

    Bug 本意是指臭虫、缺陷、损坏、犯贫、窃听器、小虫等。现在人们把在程序中一些缺陷或问题统称为 bug(漏洞)。

    76 引用 • 1742 回帖 • 2 关注
  • Swagger

    Swagger 是一款非常流行的 API 开发工具,它遵循 OpenAPI Specification(这是一种通用的、和编程语言无关的 API 描述规范)。Swagger 贯穿整个 API 生命周期,如 API 的设计、编写文档、测试和部署。

    26 引用 • 35 回帖 • 2 关注
  • LaTeX

    LaTeX(音译“拉泰赫”)是一种基于 ΤΕΧ 的排版系统,由美国计算机学家莱斯利·兰伯特(Leslie Lamport)在 20 世纪 80 年代初期开发,利用这种格式,即使使用者没有排版和程序设计的知识也可以充分发挥由 TeX 所提供的强大功能,能在几天,甚至几小时内生成很多具有书籍质量的印刷品。对于生成复杂表格和数学公式,这一点表现得尤为突出。因此它非常适用于生成高印刷质量的科技和数学类文档。

    12 引用 • 59 回帖 • 3 关注
  • Latke

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

    71 引用 • 535 回帖 • 828 关注
  • 一些有用的避坑指南。

    69 引用 • 93 回帖
  • Hexo

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

    22 引用 • 148 回帖 • 5 关注
  • 996
    13 引用 • 200 回帖 • 4 关注
  • Oracle

    Oracle(甲骨文)公司,全称甲骨文股份有限公司(甲骨文软件系统有限公司),是全球最大的企业级软件公司,总部位于美国加利福尼亚州的红木滩。1989 年正式进入中国市场。2013 年,甲骨文已超越 IBM,成为继 Microsoft 后全球第二大软件公司。

    107 引用 • 127 回帖 • 341 关注
  • gRpc
    11 引用 • 9 回帖 • 98 关注
  • 链滴

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

    记录生活,连接点滴

    180 引用 • 3878 回帖
  • 倾城之链
    23 引用 • 66 回帖 • 169 关注
  • Ngui

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

    7 引用 • 9 回帖 • 403 关注
  • 生活

    生活是指人类生存过程中的各项活动的总和,范畴较广,一般指为幸福的意义而存在。生活实际上是对人生的一种诠释。生活包括人类在社会中与自己息息相关的日常活动和心理影射。

    230 引用 • 1432 回帖