当前位置： 首页 > article >正文

使用EasyExcel实现导出excel文件时生成多级下拉选

article 2024/11/16 15:53:38

前言

公司有个需求本来只涉及到两个下拉选项，后面就想能不能实现多个下拉选，当然我这里说的多个下拉选是联动的，比如省、地市、区县这种。

实现步骤

1、添加EasyExcel的Maven依赖

 <dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.3.2</version>
 </dependency>

2、一个具有多级关联的数据项


/**
 * excel下拉框数据项
 * @author lcy
 */
@Data
public class SelectItem {

    public SelectItem(Integer columnIndex) {
        this.columnIndex = columnIndex;
    }

    /**
     * 下拉框所在列的索引，从0开始
     */
    private Integer columnIndex;

    /**
     * 下拉框的值列表
     */
    private List<DataItem> dataItems;


    /**
     * 子级对应的下拉框数据
     */
    private SelectItem subSelect;


    public  void addDataItem(String mappingKey,List<String> values){
        if (this.dataItems == null){
            this.dataItems = new ArrayList<>();
        }
        this.dataItems.add(new DataItem(mappingKey,values));
    }
    public  void addDataItem(List<String> values){
        this.addDataItem("_"+UUID.randomUUID().toString().replaceAll("-",""),values);
    }


    @Data
    public static class DataItem{

        /**
         * 关联上级的key
         */
        private String mappingKey;

        /**
         * 当前下拉框的值
         */
        private List<String> values;

        /**
         * 当前下拉框的引用，隐藏页单元格地址
         */
        private String hiddenFormulaRef;

        public DataItem(String mappingKey, List<String> values) {
            Assert.notBlank(mappingKey,"mappingKey is not blank");
            Assert.notEmpty(values,"values is not empty");
            this.mappingKey = mappingKey;
            this.values = values;
        }
    }

3、定义一个SheetWriteHandler，这是EasyExcel提供的一个组件，允许我们在sheet页生成前后做一些干预动作。


/**
 * @author lcy
 */
public class SelectWriteHandler implements SheetWriteHandler , CellWriteHandler {

    private static final int ROW_SIZE = 10000;

    private final  WriteFont redFont;

    private final  List<SelectItem> selectItems;

    private final String HIDDEN_SHEET_NAME = "hidden_sheet";

    private final Set<Integer> selectColumns = new HashSet<>();

    private boolean isLoadSelectColumns = false;

    private int rowIndex = 0;

    public SelectWriteHandler(List<SelectItem> selectItems) {
        Assert.notEmpty(selectItems, "selectItems can not be empty");
        this.selectItems = selectItems;
        redFont = getRedFont();
    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        Workbook workbook = writeWorkbookHolder.getWorkbook();
        Sheet hiddenSheet = workbook.getSheet(HIDDEN_SHEET_NAME);
        if (hiddenSheet != null){
            return ;
        }
        hiddenSheet = workbook.createSheet(HIDDEN_SHEET_NAME);
        workbook.setSheetHidden(workbook.getSheetIndex(hiddenSheet),true);
        Sheet sheet = writeSheetHolder.getSheet();
        for (SelectItem selectItem : selectItems) {
            buildHiddenSheetSelectRef(workbook,sheet,hiddenSheet,selectItem,null);
        }
        if (!isLoadSelectColumns){
            isLoadSelectColumns = true;
        }
    }

    private void buildHiddenSheetSelectRef(Workbook workbook,Sheet sheet,Sheet hiddenSheet, SelectItem selectItem,String formulaRef ) {
        if (!isLoadSelectColumns){
            selectColumns.add(selectItem.getColumnIndex());
        }
        List<SelectItem.DataItem> dataItems = selectItem.getDataItems();
        for (SelectItem.DataItem dataItem : dataItems) {
            setDataAndName(workbook, hiddenSheet, dataItem);
        }
        // 单元格地址引用
        if (formulaRef == null || formulaRef.isEmpty()){
            formulaRef = dataItems.get(0).getHiddenFormulaRef();
        }
        // 创建检验器
        DataValidation dataValidation = getDataValidation(sheet, selectItem, formulaRef);
        sheet.addValidationData(dataValidation);
        SelectItem subSelect = selectItem.getSubSelect();
        if (subSelect != null){
            buildHiddenSheetSelectRef(workbook,sheet,hiddenSheet,subSelect,getInDirectFormulaRef(selectItem.getColumnIndex()));
        }
    }

    private  DataValidation getDataValidation(Sheet sheet, SelectItem selectItem, String formulaRef) {
        DataValidationHelper helper = sheet.getDataValidationHelper();
        DataValidationConstraint constraint = helper.createFormulaListConstraint(formulaRef);
        CellRangeAddressList rangeAddressList = new CellRangeAddressList(1,ROW_SIZE, selectItem.getColumnIndex(), selectItem.getColumnIndex());
        DataValidation dataValidation = helper.createValidation(constraint, rangeAddressList);
        dataValidation.setShowErrorBox(true);
        return dataValidation;
    }

    private void setDataAndName(Workbook workbook, Sheet hiddenSheet, SelectItem.DataItem dataItem) {
        // 构建隐藏数据
        Row row = hiddenSheet.createRow(rowIndex);
        List<String> values = dataItem.getValues();
        for (int i = 0; i < values.size(); i++) {
            row.createCell(i).setCellValue(values.get(i));
        }
        // 创建名称命名器
        Name name = workbook.createName();
        name.setNameName(dataItem.getMappingKey());
        name.setRefersToFormula(getFormulaRef(row));
        dataItem.setHiddenFormulaRef(name.getRefersToFormula());
        rowIndex++;
    }

    private String getInDirectFormulaRef(Integer columnIndex){
        CellReference slectCellReference = new CellReference(1, columnIndex);
        return  "INDIRECT("+joinFormulaRef(slectCellReference, false)+")";
    }

    @Override
    public void afterCellDispose(CellWriteHandlerContext context) {
        if (!context.getHead()){
            Integer columnIndex = context.getColumnIndex();
            if (selectColumns.contains(columnIndex)){
                // 设置红色字体
                context.getFirstCellData().getOrCreateStyle().setWriteFont(redFont);
            }
        }
        CellWriteHandler.super.afterCellDispose(context);
    }

    private   String getFormulaRef(Row prvRow) {
        Cell startCell = prvRow.getCell(prvRow.getFirstCellNum());
        Cell endCell = prvRow.getCell(prvRow.getLastCellNum() - 1);
        return HIDDEN_SHEET_NAME + "!" + joinFormulaRef(new CellReference(startCell),true) + ":" + joinFormulaRef(new CellReference(endCell),true);
    }

    public  String joinFormulaRef(CellReference cellReference,boolean isAbsolute){
        StringBuilder sb = new StringBuilder();
        String[] refs = cellReference.getCellRefParts();
        for (int i = refs.length -1 ; i >= 1; i--) {
            if (isAbsolute){
                sb.append("$");
            }
            sb.append(refs[i]);
        }
        return sb.toString();
    }

    /**
     * 返回一个红色字体
     * @return
     */
    private WriteFont getRedFont() {
        WriteFont redFont =  new WriteFont();
        redFont.setColor(IndexedColors.RED.getIndex());
        return redFont;
    }
}

4、准备数据

       // 准备数据
        SelectItem selectItem = new SelectItem(0);
        selectItem.addDataItem(List.of("浙江省","河南省"));

        SelectItem subSelectItem = new SelectItem(1);
        subSelectItem.addDataItem("浙江省",List.of("杭州市","宁波市"));
        subSelectItem.addDataItem("河南省",List.of("郑州市","洛阳市","开封市"));
        selectItem.setSubSelect(subSelectItem);

        SelectItem selectItem3 = new SelectItem(2);
        selectItem3.addDataItem("杭州市",List.of("滨江区","西湖区"));
        selectItem3.addDataItem("宁波市",List.of("宁波市1","宁波市2"));
        selectItem3.addDataItem("郑州市",List.of("金水区","二七区"));
        selectItem3.addDataItem("洛阳市",List.of("洛阳市1","洛阳市2"));
        selectItem3.addDataItem("开封市",List.of("开封市1","开封市2"));
        subSelectItem.setSubSelect(selectItem3);

5、测试

EasyExcel.write("d:\\5555.xlsx")
                .registerWriteHandler(new SelectWriteHandler(List.of(selectItem)))
                .sheet()
                .doWrite(Collections.emptyList());

完整的测试代码

public class SelectExcelTest {


    public static void main(String[] args) {

        // 准备数据
        SelectItem selectItem = new SelectItem(0);
        selectItem.addDataItem(List.of("浙江省","河南省"));

        SelectItem subSelectItem = new SelectItem(1);
        subSelectItem.addDataItem("浙江省",List.of("杭州市","宁波市"));
        subSelectItem.addDataItem("河南省",List.of("郑州市","洛阳市","开封市"));
        selectItem.setSubSelect(subSelectItem);

        SelectItem selectItem3 = new SelectItem(2);
        selectItem3.addDataItem("杭州市",List.of("滨江区","西湖区"));
        selectItem3.addDataItem("宁波市",List.of("宁波市1","宁波市2"));
        selectItem3.addDataItem("郑州市",List.of("金水区","二七区"));
        selectItem3.addDataItem("洛阳市",List.of("洛阳市1","洛阳市2"));
        selectItem3.addDataItem("开封市",List.of("开封市1","开封市2"));
        subSelectItem.setSubSelect(selectItem3);



        EasyExcel.write("d:\\5555.xlsx")
                .registerWriteHandler(new SelectWriteHandler(List.of(selectItem)))
                .sheet()
                .doWrite(Collections.emptyList());
    }

}

6、结果


 


http://www.kler.cn/a/386132.html

相关文章：

  • ElementUI的日期组件中禁止选择小时、分钟、秒
  • git命令提交项目
  • RabbitMQ 篇-深入了解延迟消息、MQ 可靠性（生产者可靠性、MQ 可靠性、消费者可靠性）
  • 2024 年（第 7 届）“泰迪杯”数据分析技能赛B 题 特殊医学用途配方食品数据分析 完整代码 结果 可视化分享
  • 重卡穿越商都，ROG DAY 2024郑州站高燃来袭
  • 【C++学习（37）】并发性模式：如生产者-消费者、读写锁等。 架构模式：如MVC、MVVM等。属于23 种设计模式吗？ RAII 的关系？
  • 梧桐数据库浅谈查询优化技巧
  • UE5 metahuman 头发物理模拟
  • Meta 上周宣布正式开源小型语言模型 MobileLLM 系列
  • 怎样使用pycharm的服务？
  • FFmpeg - 音视频文件编码
  • lua入门教程：ipairs
  • DevExpress JS ASP.NET Core v24.1亮点 - 支持DateOnly/TimeOnly类型
  • linux强制修改mysql的root账号密码
  • Elasticsearch的数据类型
  • Zookeeper运维秘籍：四字命令基础、详解及业务应用全解析
  • 机器学习—sigmoid的替代品
  • 开发中使用UML的流程_01概述
  • Go：接口和反射
  • 机器学习-倒数5个项目（05）
  • 文件上传和下载
  • 带宽与下载速度的对应关系
  • c#使用COM接口设置excel单元格宽高匹配图片，如何计算？
  • 关于stm32中IO映射的一些问题
  • 想买开放式耳机如何挑选？5款高人气开放式耳机分享
  • OSPF动态路由配置实验：实现高效网络自动化