Apache Poi 实现Excel多级联动下拉框

news/2024/7/21 6:35:32 标签: apache, excel

由于最近做的功能，需要将接口返回的数据列表，输出到excel中，以供后续导入，且网上现有的封装，使用起来都较为麻烦，故参考已有做法封装了工具类。

使用apache poi实现excel联动下拉框思路

  1. 创建隐藏单元格，存储下拉数据
  2. 创建名称管理器
  3. 使用indirect表达式进行联动

添加依赖

<!--Java程序对Microsoft Office格式档案读和写的功能-->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>5.2.2</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.2.2</version>
</dependency>

直接上代码

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.*;

import java.util.List;
import java.util.Map;
import java.util.Set;


/**
 * excel验证工具类
 *
 * @author chenchuancheng github.com/meethigher
 * @since 2023/08/20 23:55
 */
public class ExcelValidationUtils {


    private static final int minRow = 1;

    private static final int maxRow = 100;

    private static final boolean debugHideSheet = true;


    /**
     * 创建一个xlsx
     *
     * @return {@link XSSFWorkbook}
     */
    public static XSSFWorkbook createOneXLSX() {
        return new XSSFWorkbook();
    }

    /**
     * 为xlsx添加一个sheet
     *
     * @param wb        xlsx
     * @param sheetName sheet名
     * @param headers   首行标题头
     * @return sheet
     */
    public static XSSFSheet addOneSheet(XSSFWorkbook wb, String sheetName, String[] headers) {
        XSSFSheet st = wb.createSheet(sheetName);
        //表头样式
        CellStyle style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式
        //字体样式
        Font fontStyle = wb.createFont();
        fontStyle.setFontName("微软雅黑");
        fontStyle.setFontHeightInPoints((short) 12);
        style.setFont(fontStyle);
        //单元格格式为文本
        XSSFDataFormat format = wb.createDataFormat();
        style.setDataFormat(format.getFormat("@"));
        //写标题
        XSSFRow row = st.createRow(0);
        st.createFreezePane(0, 1, 0, 1);
        for (int i = 0; i < headers.length; i++) {
            String value = headers[i];
            XSSFCell cell = row.createCell(i);
            st.setColumnWidth(i, value.length() * 1000);
            cell.setCellStyle(style);
            st.setDefaultColumnStyle(i, style);
            cell.setCellValue(value);
        }
        return st;
    }


    /**
     * 添加两层级联数据
     *
     * @param wb                  xlsx
     * @param targetSheet         目标sheet
     * @param linkageData         两层级联数据
     * @param parentCol           父列
     * @param childCol            孩子列
     * @param parentColIdentifier 父列标识符
     * @return {@link XSSFSheet}
     */
    public static XSSFSheet addLinkageDataValidation(XSSFWorkbook wb, XSSFSheet targetSheet, Map<String, List<String>> linkageData,
                                                     int parentCol, int childCol, String parentColIdentifier) {
        XSSFSheet hideSt = wb.createSheet();
        wb.setSheetHidden(wb.getSheetIndex(hideSt), !debugHideSheet);
        int rowId = 0;
        Set<String> keySet = linkageData.keySet();
        for (String parent : keySet) {
            List<String> sonList = linkageData.get(parent);
            XSSFRow row = hideSt.createRow(rowId++);
            row.createCell(0).setCellValue(parent);
            for (int i = 0; i < sonList.size(); i++) {
                XSSFCell cell = row.createCell(i + 1);
                cell.setCellValue(sonList.get(i));
            }
            // 添加名称管理器,1表示b列,从b列开始往后，都是子级
            String range = getRange(1, rowId, sonList.size());
            Name name = wb.createName();
            name.setNameName(parent);
            String formula = hideSt.getSheetName() + "!" + range;
            name.setRefersToFormula(formula);
        }
        //创建表达式校验
        XSSFDataValidationHelper helper = new XSSFDataValidationHelper(targetSheet);

//        //父级校验，如需生成更多，用户手动拖拽下拉即可。此操作会导致数组内容总长度超过255时报错
//        DataValidation parentValidation = helper.createValidation(helper.createExplicitListConstraint(keySet.toArray(new String[0])),
//                new CellRangeAddressList(minRow, maxRow, parentCol, parentCol));
//        parentValidation.createErrorBox("错误", "请选择正确的父级类型");
//        parentValidation.setShowErrorBox(true);
//        parentValidation.setSuppressDropDownArrow(true);
//        targetSheet.addValidationData(parentValidation);

        //解决长度为255的问题
        Name name = wb.createName();
        name.setNameName(hideSt.getSheetName());
        name.setRefersToFormula(hideSt.getSheetName() + "!$A$1:$A$" + keySet.size());
        DataValidation parentValidation = helper.createValidation(helper.createFormulaListConstraint(hideSt.getSheetName()), new CellRangeAddressList(minRow, maxRow, parentCol, parentCol));
        parentValidation.createErrorBox("错误", "请选择正确的父级类型");
        parentValidation.setShowErrorBox(true);
        targetSheet.addValidationData(parentValidation);

        //子级校验，如需生成更多，用户手动拖拽下拉即可
        for (int i = minRow; i < maxRow; i++) {
            DataValidation childValidation = helper.createValidation(helper.createFormulaListConstraint("INDIRECT(" + parentColIdentifier + "" + (i + 1) + ")"),
                    new CellRangeAddressList(i, i, childCol, childCol));
            childValidation.createErrorBox("错误", "请选择正确的子级类型");
            childValidation.setShowErrorBox(true);
            childValidation.setSuppressDropDownArrow(true);
            targetSheet.addValidationData(childValidation);
        }

        return hideSt;
    }

    /**
     * 添加简单下拉列表验证-下拉列表总内容不超过255字符
     *
     * @param st           sheet
     * @param dropDownList 下拉列表数据
     * @param firstCol     开始列，从0开始
     * @param lastCol      结束列，从0开始
     */
    public static void addSimpleDropDownListValidation(XSSFSheet st, String[] dropDownList, int firstCol, int lastCol) {
        XSSFDataValidationHelper helper = new XSSFDataValidationHelper(st);
        XSSFDataValidationConstraint constraint = (XSSFDataValidationConstraint) helper.createExplicitListConstraint(dropDownList);
        CellRangeAddressList addressList = new CellRangeAddressList(minRow, maxRow, firstCol, lastCol);
        XSSFDataValidation validation = (XSSFDataValidation) helper.createValidation(constraint, addressList);
        validation.setSuppressDropDownArrow(true);
        validation.setShowErrorBox(true);
        st.addValidationData(validation);
    }


    /**
     * 添加复杂下拉列表验证-下拉列表总内容允许超过255字符
     *
     * @param wb           xlsx
     * @param dropDownList 下拉列表数据
     * @param firstCol     开始列，从0开始
     * @param lastCol      结束列，从0开始
     */
    public static void addComplexDropDownListValidation(XSSFWorkbook wb, XSSFSheet st, String[] dropDownList, int firstCol, int lastCol) {
        XSSFSheet hideSt = wb.createSheet();
        wb.setSheetHidden(wb.getSheetIndex(hideSt), !debugHideSheet);
        XSSFDataValidationHelper helper = new XSSFDataValidationHelper(st);
        for (int i = 0, length = dropDownList.length; i < length; i++) {
            String value = dropDownList[i];
            XSSFRow row = hideSt.createRow(i);
            XSSFCell cell = row.createCell(0);
            cell.setCellValue(value);
        }
        //解决长度为255的问题
        Name name = wb.createName();
        name.setNameName(hideSt.getSheetName());
        name.setRefersToFormula(hideSt.getSheetName() + "!$A$1:$A$" + dropDownList.length);
        DataValidation parentValidation = helper.createValidation(helper.createFormulaListConstraint(hideSt.getSheetName()), new CellRangeAddressList(minRow, maxRow, firstCol, lastCol));
        parentValidation.createErrorBox("错误", "请选择正确的类型");
        parentValidation.setShowErrorBox(true);
        st.addValidationData(parentValidation);
    }


    /**
     * 计算formula
     *
     * @param offset   偏移量，如果给0，表示从A列开始，1，就是从B列
     * @param rowId    第几行
     * @param colCount 一共多少列
     * @return 如果给入参 1,1,10. 表示从B1-K1。最终返回 $B$1:$K$1
     */
    private static String getRange(int offset, int rowId, int colCount) {
        char start = (char) ('A' + offset);
        if (colCount <= 25) {
            char end = (char) (start + colCount - 1);
            return "$" + start + "$" + rowId + ":$" + end + "$" + rowId;
        } else {
            char endPrefix = 'A', endSuffix;
            if ((colCount - 25) / 26 == 0 || colCount == 51) {// 26-51之间，包括边界（仅两次字母表计算）
                if ((colCount - 25) % 26 == 0) {// 边界值
                    endSuffix = (char) ('A' + 25);
                } else {
                    endSuffix = (char) ('A' + (colCount - 25) % 26 - 1);
                }
            } else {// 51以上
                if ((colCount - 25) % 26 == 0) {
                    endSuffix = (char) ('A' + 25);
                    endPrefix = (char) (endPrefix + (colCount - 25) / 26 - 1);
                } else {
                    endSuffix = (char) ('A' + (colCount - 25) % 26 - 1);
                    endPrefix = (char) (endPrefix + (colCount - 25) / 26);
                }
            }
            return "$" + start + "$" + rowId + ":$" + endPrefix + endSuffix + "$" + rowId;
        }
    }
}

使用示例

public class TestExportExcelWithValidation {


    private final static String[] headers = new String[]{
            "性别",
            "省",
            "市",
            "区",
    };


    private static Map<String, List<String>> 省级() {
        Map<String, List<String>> map = new HashMap<>();
        map.put("湖北省", Arrays.asList("武汉市", "襄阳市"));
        map.put("吉林省", Arrays.asList("长春市", "吉林市"));
        return map;
    }

    private static Map<String, List<String>> 市级() {
        Map<String, List<String>> map = new HashMap<>();
        map.put("武汉市", Arrays.asList("洪山区", "江夏区"));
        map.put("长春市", Arrays.asList("宽城区", "南关区"));
        return map;
    }

    public static void main(String[] args) throws Exception {
        XSSFWorkbook wb = createOneXLSX();
        XSSFSheet st = addOneSheet(wb, "data", headers);
        addSimpleDropDownListValidation(st, new String[]{"男", "女"}, 0, 0);
        addLinkageDataValidation(wb, st, 省级(), 1, 2, "B");
        addLinkageDataValidation(wb, st, 市级(), 2, 3, "C");


        wb.write(new FileOutputStream("aaa.xlsx"));
    }
}

最终结果展示如图


idation(st, new String[]{“男”, “女”}, 0, 0);
addLinkageDataValidation(wb, st, 省级(), 1, 2, “B”);
addLinkageDataValidation(wb, st, 市级(), 2, 3, “C”);

    wb.write(new FileOutputStream("aaa.xlsx"));
}

}


最终结果展示如图

[外链图片转存中...(img-zQCvRcAm-1693070160923)]

http://www.niftyadmin.cn/n/4972995.html

相关文章

PyCharm软件安装包分享（附安装教程）

PyCharm软件安装包分享（附安装教程）

目录 一、软件简介 二、软件下载 一、软件简介 PyCharm是一种集成开发环境&#xff08;IDE&#xff09;&#xff0c;专门为Python开发者设计。它是由捷克软件公司JetBrains开发的&#xff0c;为Python开发人员提供了高效、易用和功能丰富的工具集。 以下是PyCharm软件的主要…
阅读更多...
计网第四章（网络层）（四）

计网第四章（网络层）（四）

目录 一、IP数据报的发送和转发过程 发送&#xff1a; 1.直接交付和间接交付 如果判断源主机和目的主机是否在同一个网络中&#xff1f; 2.默认网关&#xff1a; 转发&#xff1a; 路由表&#xff1a; 一、IP数据报的发送和转发过程 发送&#xff1a; 由主机发送IP数据…
阅读更多...
京东算法分析

京东算法分析

# 1.首先charles抓包发现每个请求Url后都接了一个sign的参数且每次都不一样。也没有其他的一些别的特别参数,那么关键问题就是分析sign参数的生成了 # 2.jadx反编译,寻找sign的生成的位置 > 直接搜索sign参数匹配的出来的结果太多了,一时间不好区分哪个是真的。于是使用get…
阅读更多...
开源软件的崛起：历史与未来

开源软件的崛起：历史与未来

&#x1f337;&#x1f341; 博主猫头虎 带您 Go to New World.✨&#x1f341; &#x1f984; 博客首页——猫头虎的博客&#x1f390; &#x1f433;《面试题大全专栏》 文章图文并茂&#x1f995;生动形象&#x1f996;简单易学&#xff01;欢迎大家来踩踩~&#x1f33a; &a…
阅读更多...
【C++】—— C++11之可变参数模板

【C++】—— C++11之可变参数模板

前言&#xff1a; 在C语言中&#xff0c;我们谈论了有关可变参数的相关知识。在C11中引入了一个新特性---即可变参数模板。本期&#xff0c;我们将要介绍的就是有关可变参数模板的相关知识&#xff01;&#xff01;&#xff01; 目录 序言 &#xff08;一&#xff09;可变参…
阅读更多...
ElasticSearch - 海量数据索引拆分的一些思考

ElasticSearch - 海量数据索引拆分的一些思考

文章目录 困难解决方案初始方案及存在的问题segment merge引入预排序 拆分方案设计考量点如何去除冗余数据按什么维度拆分&#xff0c;拆多少个最终的索引拆分模型演进历程整体迁移流程全量迁移流程流量回放比对验证异步转同步多索引联查优化效果 总结与思考参考 困难 索引数据…
阅读更多...
＜MySon car=“宝马“ :money=“money“＞＜/MySon＞有没有冒号

＜MySon car=“宝马“ :money=“money“＞＜/MySon＞有没有冒号

为什么car"宝马"没有&#xff1a; 但是 :money"money"就有&#xff1a; <script setup> import {ref} from vue import MySon from /components/MySon.vueconst money ref(100) </script><template><h3>father</h3><My…
阅读更多...
02.sqlite3学习——嵌入式数据库的基本要求和SQLite3的安装

02.sqlite3学习——嵌入式数据库的基本要求和SQLite3的安装

目录 嵌入式数据库的基本要求和SQLite3的安装 嵌入式数据库的基本要求 常见嵌入式数据库 sqlite3简介 SQLite3编程接口模型 ubuntu 22.04下的SQLite安装 嵌入式数据库的基本要求和SQLite3的安装 嵌入式数据库的基本要求 常见嵌入式数据库 sqlite3简介 SQLite3编程接口模…
阅读更多...
最新文章

Copyright @ 2022~2023