工作总结—基于Easyexcel的级联下拉选择实现

已于 2023-07-06 09:14:00 修改
阅读量3k 收藏 7
点赞数 6
文章标签： java 后端
于 2023-07-05 23:24:30 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_38999264/article/details/131565077
版权

业务梳理

某业务可以根据Excel模板的方式新增数据，其导入模板中包含组织机构信息，人员信息。以往的做法是用户在导入时自己填写模板。

1.弊端

  1. 大数据模板导入时，用户体验较差；
  2. 客户输入容易出错，导致导入时易产生数据校验错误。

针对如上弊端，产品提出如下需求

需求

  1. 对于已知固定数据，采用数据下拉选择填入的方式填充数据
  2. 对于具有关联关系的数据，能根据上一列填值确定下一列数据的下拉范围
  3. 某一列的数据可根据前几列是否已填入值确定该列数据的下拉范围

实现

思路

1.学习Excel如何制作下拉框
2.基于Easyexcel实现Excel下拉框选择数据

实现

1.确定下拉框实现数据结构

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.util.List;

@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class ExcelCascade {
    /**
     *下拉选项
     */
    private List<String> options;

    /**
     * 级联模板
     */
    private String parentColTpl;

    /**
     * 当前下拉列索引
     */
    private Integer curColIndex;

    /**
     * 下拉列表首行
     */
    private Integer firstRow;

    /**
     * 下拉列表末行
     */
    private Integer lastRow;

    /**
     * 下拉列表名称
     */
    private String title;
}
2.构造下拉框数据
  public static List<ExcelCascade> init() {
        List<ExcelCascade> excelCascades = new ArrayList<>();
        List<OrganizationVO> organizationVOS = new ArrayList<>() {{
            add(OrganizationVO.builder().id("1").code("1").name("公司").parentId("0").type("10").build());
            add(OrganizationVO.builder().id("2").code("2").name("处室").parentId("1").type("20").build());
            add(OrganizationVO.builder().id("3").code("3").name("科室").parentId("2").type("30").build());
            add(OrganizationVO.builder().id("4").code("4").name("班组").parentId("3").type("30").build());
        }};

        Map<String, OrganizationVO> orgIdOrgMap = organizationVOS.stream().collect(Collectors.toMap(OrganizationVO::getId, Function.identity()));
        Map<String, List<OrganizationVO>> orgGroupingByParentIdMap = organizationVOS.stream().filter(o -> !StrUtil.equals(o.getType(), "10")).collect(Collectors.groupingBy(OrganizationVO::getParentId));
        orgGroupingByParentIdMap.forEach((k, v) -> {
            OrganizationVO parentOrganizationVO = orgIdOrgMap.get(k);
            if (Objects.nonNull(parentOrganizationVO)) {
                int curColIndex = 0;
                String parentColTpl = "";
                String type = parentOrganizationVO.getType();
                if (StrUtil.equals(type, "10")) {
                    curColIndex = 1;
                }
                if (StrUtil.equals(type, "20")) {
                    curColIndex = 2;
                    parentColTpl = "INDIRECT($B${})";
                }

                if (StrUtil.equals(type, "30")) {
                    curColIndex = 3;
                    parentColTpl = "INDIRECT($C${})";
                }
                ExcelCascade tmp = ExcelCascade.builder()
                        //考虑名称中不能包含特殊字符 TODO
                        .title(parentOrganizationVO.getName() + "_" + parentOrganizationVO.getCode())
                        .options(v.stream().map(o -> o.getName() + "_" + o.getCode()).collect(Collectors.toList()))
                        .curColIndex(curColIndex)
                        .parentColTpl(parentColTpl)
                        .firstRow(1)
                        .lastRow(999)
                        .build();
                excelCascades.add(tmp);
            }

        });


        List<UserVO> userVOS = new ArrayList<>() {{
            add(UserVO.builder().id("1").name("处室的人").code("1").orgId("2").build());
            add(UserVO.builder().id("2").name("科室的人").code("2").orgId("3").build());
            add(UserVO.builder().id("3").name("班组的人").code("3").orgId("4").build());
            add(UserVO.builder().id("3").name("班组的人").code("4").orgId("4").build());
        }};
        Map<String, List<String>> userGroupByOrgIdMap = userVOS.stream().collect(Collectors.groupingBy(UserVO::getOrgId, Collectors.mapping(o -> o.getName() + "_" + o.getCode(), Collectors.toList())));
        userGroupByOrgIdMap.forEach((k, v) -> {
            OrganizationVO organizationVO = orgIdOrgMap.get(k);
            if (Objects.nonNull(organizationVO)) {
                ExcelCascade tmp = ExcelCascade.builder()
                        //考虑名称中不能包含特殊字符 TODO
                        .title(organizationVO.getName() + "_" + organizationVO.getCode() + "_成员")
                        .options(v)
                        .curColIndex(4)
                        .parentColTpl("INDIRECT((IF(ISBLANK($D${}),IF(ISBLANK($C${}),IF(ISBLANK($B${}),$B${},$B${}),$C${}),$D${}))&\"_成员\")")
                        .firstRow(1)
                        .lastRow(999)
                        .build();
                excelCascades.add(tmp);
            }

        });
        return excelCascades;
    }

注意

  1. 下拉数据中只能包含有下划线“_”这一特殊字符，且不能是数字开头
  2. 保证名称唯一
3.制作下拉框

import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.util.StringUtils;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import com.example.demo.entity.ExcelCascade;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;

import java.util.List;
import java.util.concurrent.CopyOnWriteArrayList;
import java.util.concurrent.atomic.AtomicInteger;

public class CascadeWriteHandler implements SheetWriteHandler {

    /**
     * 下拉框信息
     */
    private List<ExcelCascade> selectData;

    public CascadeWriteHandler(List<ExcelCascade> selectData) {
        this.selectData = selectData;
    }

    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        //获取工作簿
        Sheet sheet = writeSheetHolder.getSheet();
        DataValidationHelper dvHelper = sheet.getDataValidationHelper();

        Workbook book = writeWorkbookHolder.getWorkbook();
        //创建一个专门用来存放地区信息的隐藏sheet页
        //因此不能在现实页之前创建，否则无法隐藏。
        String hideSheetName = "hideSheet";
        Sheet hideSheet = book.createSheet(hideSheetName);
        //设置隐藏 建议在构建模板时注释掉改行
        //book.setSheetHidden(book.getSheetIndex(hideSheet), true);
        // 将具体的数据写入到每一行中，行开头为父级区域即公式名称，后面是子区域，即下拉选项。
        AtomicInteger rowId = new AtomicInteger(1);
        List<Integer> hasSet = new CopyOnWriteArrayList<>();


        selectData.forEach(sd -> {
            int rowIdNumber = rowId.getAndIncrement();
            Row row = hideSheet.createRow(rowIdNumber);
            String title = sd.getTitle();
            row.createCell(0).setCellValue(title);
            List<String> options = sd.getOptions();
            for (int i = 0; i < options.size(); i++) {
                Cell cell = row.createCell(i + 1);
                cell.setCellValue(options.get(i));
            }
            // 添加名称管理器
            String range = getRange(1, rowIdNumber + 1, options.size());
            Name name = book.createName();
            name.setNameName(title);
            String formula = hideSheetName + "!" + range;
            name.setRefersToFormula(formula);
            //设置下拉框
            if (StringUtils.isBlank(sd.getParentColTpl()) && !hasSet.contains(sd.getCurColIndex())) {
                hasSet.add(sd.getCurColIndex());
                DataValidationConstraint expConstraint = dvHelper.createFormulaListConstraint(title);
                CellRangeAddressList expRangeAddressList = new CellRangeAddressList(sd.getFirstRow(), sd.getLastRow(), sd.getCurColIndex(), sd.getCurColIndex());
                setValidation(sheet, dvHelper, expConstraint, expRangeAddressList, "提示", "你输入的值未在备选列表中，请下拉选择合适的值");
            } else {
                if (!hasSet.contains(sd.getCurColIndex())) {
                    hasSet.add(sd.getCurColIndex());
                    for (int i = sd.getFirstRow() + 1; i < sd.getLastRow(); i++) {
                        DataValidationConstraint expConstraint = dvHelper.createFormulaListConstraint(StrUtil.format(sd.getParentColTpl(), i, i, i, i, i, i, i));
                        CellRangeAddressList expRangeAddressList = new CellRangeAddressList(i - 1, i - 1, sd.getCurColIndex(), sd.getCurColIndex());
                        setValidation(sheet, dvHelper, expConstraint, expRangeAddressList, "提示", "你输入的值未在备选列表中，请下拉选择合适的值");
                    }
                }
            }
        });
    }

    /**
     * 设置验证规则
     *
     * @param sheet       sheet对象
     * @param helper      验证助手
     * @param constraint  createExplicitListConstraint
     * @param addressList 验证位置对象
     * @param msgHead     错误提示头
     * @param msgContext  错误提示内容
     */
    private void setValidation(Sheet sheet, DataValidationHelper helper, DataValidationConstraint constraint, CellRangeAddressList addressList, String msgHead, String msgContext) {
        DataValidation dataValidation = helper.createValidation(constraint, addressList);
        dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
        dataValidation.setShowErrorBox(true);
        dataValidation.setSuppressDropDownArrow(true);
        dataValidation.createErrorBox(msgHead, msgContext);
        sheet.addValidationData(dataValidation);
    }

    /** 获取数据范围
     * @param offset   偏移量，如果给0，表示从A列开始，1，就是从B列
     * @param rowId    第几行
     * @param colCount 一共多少列
     * @return 数据范围
     */
    public String getRange(int offset, int rowId, int colCount) {
        char start = (char) ('A' + offset);
        return "$" + start + "$" + rowId + ":$" + numberToColumn(colCount+offset) + "$" + rowId;
    }


    /**
     * 10 进制转 26进制
     * @param number
     * @return
     */
    String numberToColumn(int number) {
        StringBuilder column = new StringBuilder();
        while (number > 0) {
            int remainder = (number - 1) % 26;
            column.insert(0, (char) ('A' + remainder));
            number = (number - 1) / 26;
        }
        return column.toString();
    }
}

4、导出模板

  1. 模板数据结构

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class ExportExcel {

    @ExcelProperty(value = "业务名称", index = 0)
    private String name;

    @ExcelProperty(value = "责任部门", index = 1)
    private String org;

    @ExcelProperty(value = "责任科室", index = 2)
    private String dept;

    @ExcelProperty(value = "责任班组", index = 3)
    private String team;

    @ExcelProperty(value = "责任人", index = 4)
    private String rspUserName;

    @ExcelProperty(value = "业务开始时间", index = 5)
    private String startTime;

    @ExcelProperty(value = "业务完成时间", index = 6)
    private String endTime;

    @ExcelProperty(value = "备注", index = 7)
    private String goal;
}
  1. 小试牛刀
 public static void main(String[] args) {
        List<ExcelCascade> excelCascades = init();

        // 写出数据
        EasyExcel.write(new File("业务导入模板.xlsx"), ExportExcel.class)
                .sheet("sheet1")
                .registerWriteHandler(new CascadeWriteHandler(excelCascades))
                .doWrite(new ArrayList<>());
    }
  1. 其他类

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class OrganizationVO {
    /**
     * 部门Id
     */
    private String id;

    /**
     * 部门名称
     */
    private String name;

    /**
     * 部门代码
     */
    private String code;

    /**
     * 父级部门Id
     */
    private String parentId;

    /**
     * 部门类型
     */
    private String type;

}




import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class UserVO {
    /**
     * 用户Id
     */
    private String id;

    /**
     * 用户名称
     */
    private String name;

    /**
     * 用户代码
     */
    private String code;


    /**
     * 归属部门Id
     */
    private String orgId;

}

总结

  1. 书到用时方恨少，Java 基础，Excel 使用
  2. 敢于思考，敢于尝试，例如，在根据选择部门级别的不同，做到选择责任人下拉框下拉数据范围的确定？；下拉数据是以列的方式写入还是以行的方式写入？
  3. 优化，比如使用注解的方式，模板的替换？

参考文档

使用EasyExcel导出模板并设置级联下拉及其原理分析

致谢

十分感谢参考文档提供的示例，以及对Excel设置下拉框步骤的讲解。同时也感谢十分强大的Excel处理工具Easyexcel。

qq_38999264
关注
  • 6
    点赞
  • 踩
  • 7
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
java web下开发二级级联下拉菜单（数据库实现的）
05-23
网上级联菜单确有不少，单大多是直接写死到jsp页面中的，本代码是用mysql的数据库实现的，希望对大家有帮助！！！
二级联动 下拉列表
10-24
二级联动 下拉列表 可以进行2级联动下拉等功效 完全的代码 用的时候只需要改改数据库就行了
使用EasyExcel导出下拉框
最新发布
jerry764的博客
04-30 1372
formula：公式，用于指定自定义的数据验证条件。通过使用，您可以为 Excel 中的单元格设置数据验证的约束条件，以确保输入的数据符合指定的规则。
EasyExcel实现动态生成包含下拉框、级联下拉框的excel表格
maziaotong的博客
09-04 3138
但是这个表格里面包含了下拉框，耗时两天才算是把这个excel表格搞完，现在记录一下，方便后续回顾。普通下拉数据，就是如同性别、是否之类的数据；Map中的key就是要关联的列，注意：excel列从0开始，value就是下拉数据的数组。其中，子类返回的map中，key就是父类返回数组的值，value就是子类列的下拉列表。根据D列的检查类型,E列弹出不同的下拉选项，F列弹出不同的问题属性；这个拦截器是在service层使用的，就是最上面的代码。SheetWriteHandler接口。直接上代码，代码中有注释。
EasyExcel导出带下拉选数据的Excel数据导入模板（以及下拉选数据量多不展示的问题）
weixin_42174911的博客
10-08 4408
*** 标注导出的列为下拉框类型，并为下拉框设置内容*//*** 固定下拉内容*//*** 动态下拉内容*/Class
EasyExcel多列单元格设置下拉框
CD523的博客
02-09 2975
EasyExcel官网文档地址：https://easyexcel.opensource.alibaba.com/docs/current/项目场景：实际开发中，需要用到很多下拉框去供使用者选择特定得值，但是easyExcel官方文档中提供得示例很简单，只有1个列有下拉框。在此分享一下多列下拉框得心得体会。
EasyExcel增加下拉选择框
吃黄瓜不蘸酱的博客
12-19 2539
EasyExcel增加下拉选择框
使用easyExcel导出多级表头及下拉选框选择数据
weixin_52664593的博客
04-20 5258
Excel多级表头下拉框选取数据模板
EasyExcel生成带下拉列表或多级级联列表的Excel模版+自定义校验导入数据(修订)
weixin_43558927的博客
11-01 6380
生成带下拉菜单的Excel文件, 限制用户输入, 以配合服务端的数据模型; 新版本主要变化: 优化部分设计和实现逻辑, 清晰命名, 改用SpEL替换指定接口等. 并更新本文的生成下拉部分内容.
【Java】easyexcel 使用拦截器设置单元格下拉选项，并解决下拉选项个数受限问题。
konw_的博客
12-25 1192
excel的下拉选项会限制最大选择个数，我们这里采用引用单元格的形式实现，同时使用easyexcel 的拦截器，方便简洁。
java excel 生成6级级联
12-07
java excel 生成6级级联。加一个poi.jar就行。
基于EasyExcel的excel解析工具类。
02-25
基于EasyExcel框架的excel解析工具类。EasyExcel是阿里的excel解析工具，使用快捷方便，轻量。封装了常用的方法。使用前需要了解easyExcel的基本用法。
阿里的EasyExcel+Mysql方式实现数据库数据导出生成exce
03-01
总结来说，阿里的EasyExcel结合SpringBoot和Mysql数据库，提供了一个完整的解决方案，实现了从数据库数据导出生成Excel文件，以及将Excel数据保存回数据库的功能。这种解决方案在处理大数据量时表现出色，具有低内存...
easyExcel实现大数据导出
11-13
总结来说，`easyExcel`是Java开发中处理大数据Excel读写的好帮手，它的内存优化策略和简洁的API使得即使面对海量数据，也能轻松应对。结合适当的处理策略，如分批写入，可以进一步提高系统的稳定性和性能。
EasyExcel导出Excel，单元格添加下拉列表
我的小幸运呢
07-05 3846
本文章可解决EasyExcel导出Excel时，添加的下拉列表无法展示数据问题
Java 导出Excel表格生成下拉框-EasyExcel
weixin_53335648的博客
05-04 7118
*** 标注导出的列为下拉框类型，并为下拉框设置内容*//*** 固定下拉内容*//*** 动态下拉内容*/Class
导出带下拉选项的Excel基于EasyExcel实现
weixin_54345563的博客
10-20 1390
导出带下拉选项的Excel基于EasyExcel实现
easyexcel导出动态模板(动态导出下拉框)
qq_47605710的博客
04-22 4848
使用easyexcel下载模板
easyexcel 构建 下拉选择
05-30
在 easyexcel 中，可以通过实现 Converter 接口来进行下拉选择的构建。 1. 首先，需要定义下拉选择的选项，可以使用枚举类型或者自定义类来进行定义。例如，定义一个颜色选择的枚举类型： public enum ColorEnum { RED("红色"), GREEN("绿色"), BLUE("蓝色"); private String desc; ColorEnum(String desc) { this.desc = desc; } public String getDesc() { return desc; } public static List<String> getValues() { List<String> values = new ArrayList<>(); for (ColorEnum color : ColorEnum.values()) { values.add(color.getDesc()); } return values; } } 2. 实现 Converter 接口，定义下拉选择的转换规则。例如，定义一个将枚举类型转换为字符串类型的转换器： public class ColorEnumConverter implements Converter<ColorEnum> { @Override public Class<ColorEnum> supportJavaTypeKey() { return ColorEnum.class; } @Override public CellData<String> convertToExcelData(ColorEnum value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) { return new CellData<>(value.getDesc()); } @Override public ColorEnum convertToJavaData(CellData cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) { String desc = cellData.getStringValue(); for (ColorEnum color : ColorEnum.values()) { if (color.getDesc().equals(desc)) { return color; } } return null; } } 3. 在导出 Excel 时，使用 @ExcelProperty 注解指定字段需要进行下拉选择，并使用 @ContentStyle 注解指定下拉选择的样式： @ExcelProperty(value = "颜色", converter = ColorEnumConverter.class) @ContentStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND, fillForegroundColor = 10) private ColorEnum color; 4. 在导入 Excel 时，使用 @ExcelProperty 注解指定字段需要进行下拉选择，并使用 @HeadStyle 注解指定下拉选择的样式： @ExcelProperty(value = "颜色", converter = ColorEnumConverter.class) @HeadStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND, fillForegroundColor = 10) private ColorEnum color; 以上就是在 easyexcel 中构建下拉选择的步骤。需要注意的是，在进行下拉选择时，需要将字段类型设置为支持下拉选择的类型，例如枚举类型或者自定义类。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值