springboot之Excel导入导出组件：支持字段检验、字典翻译、计算公式、级联下拉

产品需求

       Excel导入需求说明

• 支持字段检验：必填、非空、长度限制、数字检验、日期检验等
• 支持字典翻译：如中文性别转数字、下拉选择中文转代码等
• 检验失败的单元格，要加批注并能导出提示Excel文件

     Excel导出需求说明

• 支持复杂表头：如跨行跨列、动态标题
• 生成单元格支持自动计算公式：如求和统计
• 自动生成下拉选择框：包括下拉单选、下拉多选、级联选择

技术方案

案例展示

1. 导入检验 Excel加批注

2. 导出Excel 自动生成公式

3. 导出Excel 生成单选下拉框

4. 导出Excel 生成级联下拉框

5. 导出Excel 生成多选下拉框

代码集成

项目地址：   https://gitee.com/cloud-coder/cloud-coder-report

1. 引入依赖，添加配置

<dependency>
  <groupId>com.geline</groupId>
  <artifactId>coder-report-easyexcel</artifactId>
  <version>1.3.1</version>
</dependency>

easy-excel:
  # Excel模板文件
  template-file: excel/template/*.xlsx
  # Excel统计报表SQL配置
  xml-file: excel/xml/*.xml
  # Excel导入导出处理接口
  excel-crud-consumer: com.geline.easyexcel.mybatisplus.config.MyExcelCrudConsumer
  # 清理过期导入Excel异常记录；表参考：sp_excel_file
  expire-delete-sql: delete from sp_excel_file where DATE(created_time) <= DATE(DATE_SUB(NOW(),INTERVAL 7 day))
  # 查询字典数据，支持树形字典；表参考：sp_data_dict
  dict-select-sql: select id, parent_id, code, name, value, level from sp_data_dict where deleted=0 and code=#{code}

2. 数据表

CREATE TABLE `sp_excel_file` (
  `id` varchar(32) NOT NULL COMMENT 'id',
  `filename` varchar(200) NOT NULL DEFAULT '' COMMENT '文件名称',
  `bytes` longblob COMMENT '文件流',
  `error_info` text COMMENT '单元格错误信息json',
  `deleted` tinyint DEFAULT '0' COMMENT '删除标识（0：正常，1：删除）',
  `create_by` varchar(120) DEFAULT NULL COMMENT '创建人',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(120) DEFAULT NULL COMMENT '修改人',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`)
) COMMENT='Excel文件表';

CREATE TABLE `sp_data_dict` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'id',
`parent_id` bigint DEFAULT NULL COMMENT '父id',
`code` varchar(80) DEFAULT NULL COMMENT '编码',
`value` varchar(1000) DEFAULT NULL COMMENT '字典项值',
`name` varchar(255) DEFAULT NULL COMMENT '名称',
`sort_num` int DEFAULT NULL COMMENT '排序',
`remark` varchar(200) DEFAULT NULL COMMENT '备注',
`deleted` tinyint DEFAULT '0' COMMENT '删除标识（0：正常，1：删除）',
`create_by` varchar(120) DEFAULT NULL COMMENT '创建人',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_by` varchar(120) DEFAULT NULL COMMENT '修改人',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
`path` varchar(1000) DEFAULT NULL,
`path_name` varchar(1000) DEFAULT NULL,
`level` int DEFAULT '0' COMMENT '层级',
PRIMARY KEY (`id`) USING BTREE
) COMMENT='数据字典（树形结构）';

3. 自定义核心实现类

集成MybatisPlus框架实现类

@Component
@Slf4j
public class MyExcelCrudConsumer extends MPExcelCrudConsumer {

    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Autowired
    private ExcelFileDao excelFileDao;
    @Autowired
    private ExcelFileService excelFileService;
    @Autowired
    private ExcelValidFunction validFunction;

    @Override
    public IService getIService(String entityName) {
        return excelFileService;
    }

    @Override
    public void transLabelToKey(Collection entityList) {
        //处理字典翻译
        easyTransService.transBatch(entityList);
    }

    @Override
    public void transKeyToLabel(Collection entityList) {
        //处理字典翻译
        easyTransService.transBatch(entityList);
    }

    @Override
    public JdbcTemplate getJdbcTemplate() {
        return jdbcTemplate;
    }

    @Override
    public ExcelFileDao getExcelFileDao() {
        return excelFileDao;
    }

    @Override
    public List listData(String entityName, PageQry qry, Class rowClass, boolean currentPage) {
        if (!currentPage) {
            qry.setPageIndex(1);
            qry.setPageSize(50000);
        }
        // 调用业务service分页方法
        IService service = SpringUtil.getBean(StrUtil.lowerFirst(entityName)+"ServiceImpl");
        Page<Object> page = new Page<>(qry.getPageIndex(), qry.getPageSize());
        QueryWrapper wrapper = QueryWrapperUtil.build(qry);
        IPage pageMaps = service.pageMaps(page, wrapper);

        List records = pageMaps.getRecords();
        List rowList = BeanUtil.copyToList(records, rowClass);
        transKeyToLabel(rowList);
        return rowList;
    }

    @Override
    public List handleReportData(List dataList) {
        //处理字典翻译、行政区划翻译
        transKeyToLabel(dataList);
        return dataList;
    }

    @Override
    public ExcelValidFunction getValidFunction() {
        //自定义检验类
        return validFunction;
    }
}

集成JPA框架实现类

@Component
@Slf4j
public class MyExcelCrudConsumer extends JPAExcelCrudConsumer {

    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Autowired
    private ExcelFileDao excelFileDao;
    @Autowired
    private ExcelFileRepository repository;
    @Resource
    private TransServiceFactory transServiceFactory;
    @Autowired
    private ExcelValidFunction validFunction;

    @Override
    public JdbcTemplate getJdbcTemplate() {
        return jdbcTemplate;
    }

    @Override
    public ExcelFileDao getExcelFileDao() {
        return excelFileDao;
    }

    @Override
    public JpaRepository getJpaRepository(String entityName) {
        return repository;
    }

    @Override
    public void transLabelToKey(Collection entityList) {
        //处理字典翻译、行政区划翻译
        entityList.stream().forEach(row -> transServiceFactory.apply(row).labelToKey());
    }

    @Override
    public void transKeyToLabel(Collection entityList) {
        //处理字典翻译、行政区划翻译
        entityList.stream().forEach(row -> transServiceFactory.apply(row).keyToLabel());
    }

    @Override
    public List listData(String entityName, PageQry qry, Class rowClass, boolean currentPage) {
        if (!currentPage) {
            qry.setPageIndex(1);
            qry.setPageSize(50000);
        }
        // 调用业务service分页方法
        Object executor = SpringUtil.getBean(StrUtil.lowerFirst(entityName) + "ServiceImpl");
        PageResponse pageResponse = ReflectUtil.invoke(executor, "findAll", qry);
        List content = pageResponse.getData();
        List rowList = BeanUtil.copyToList(content, rowClass);
        //处理字典翻译、行政区划翻译
        transKeyToLabel(rowList);
        return rowList;
    }

    @Override
    public List handleReportData(List dataList) {
        //处理字典翻译、行政区划翻译
        transKeyToLabel(dataList);
        return dataList;
    }

    @Override
    public ExcelValidFunction getValidFunction() {
        return validFunction;
    }
}

4. 导入检验 失败加批注

@ExcelImport(value = "pipeBuilt")
@Getter
@Setter
public class PipeBuiltImportVO implements Serializable {

    //检验非空且长度<=120
    @ExcelProperty(value = "管廊项目名称", index = 0)
    @ExcelValid(notBlank = true, length = 120)
    private String projectName;

    //检验非null且为整数
    @ExcelProperty(value = "总数", index = 1)
    @ExcelValid(notNull = true, regexEnum = RegexEnum.number)
    private Integer totalNum;

    //检验非null且为小数
    @ExcelProperty(value = "断面截面尺寸", index = 2)
    @ExcelValid(notNull = true, regexEnum = RegexEnum.money)
    private BigDecimal sectionSize;

    //检验非null且保留2位小数
    @ExcelProperty(value = "总长度", index = 3)
    @ExcelValid(notNull = true, regexEnum = RegexEnum.digital_2)
    private BigDecimal totalLength;

    //检验非空且为正确的手机号
    @ExcelProperty(value = "手机号", index = 4)
    @ExcelValid(notBlank = true, regexEnum = RegexEnum.mobile)
    private String phone;

    //检验非空且为正确的邮箱
    @ExcelProperty(value = "邮箱", index = 5)
    @ExcelValid(notBlank = true, regexEnum = RegexEnum.email)
    private String email;
}

核心API

API名称	接口地址
导入Excel	POST：/easyExcel/importExcel/{entity}
检验失败时下载Excel	GET： /easyExcel/downErrorExcel/{entity}?errorId=x

5. 分页查询 导出Excel

@ExcelExportPage(value = "pipeBuilt", title = "管廊", qryClass = PipeBuiltListQry.class)
@Getter
@Setter
public class PipeBuiltExportVO implements Serializable {

    @ExcelProperty(value = "管廊项目名称", index = 0)
    private String projectName;

    //字典代码
    @Schema(description = "投融资模式")
    @TransDict(dictType = "gallery_invest_type", source = "financingModeLabel")
    private String financingMode;

    //生成单选下拉框+字典标签翻译
    @ExcelProperty(value = "投融资模式", index = 1)
    @ExcelDropDown(index = 15, dictName = "投融资模式", dictCode = "gallery_invest_type", type = DropDownType.SELECT)
    @TransDict(dictType = "gallery_invest_type", source = "financingMode")
    private String financingModeLabel;

    //字典代码
    @Schema(description = "管廊类型")
    @TransDict(dictType = "gallery_type", source = "pipeTypeLabel")
    private String pipeType;

    //生成多选下拉框+字典标签翻译
    @ExcelProperty(value = "管廊类型", index = 2)
    @ExcelDropDown(index = 5, dictName = "管廊类型", dictCode = "gallery_type", type = DropDownType.CHECKBOX)
    @TransDict(dictType = "gallery_type", source = "pipeType")
    private String pipeTypeLabel;
}

核心API

API名称	接口地址
下载Excel模板	GET：/easyExcel/exportPageTemplate/{entity}
导出当前页数据	GET：/easyExcel/exportPageExcel/{entity} 参数：?pageIndex=1&pageSize=10¤tPage=true
导出所有数据	GET：/easyExcel/exportPageExcel/townHouse 参数：?pageIndex=1&pageSize=10¤tPage=false
导出当前页勾选项	GET：/easyExcel/exportPageExcel/townHouse 参数：?pageIndex=1&pageSize=10¤tPage=true&ids=11,22

6. 导出Excel (统计报表)

@ExcelExportList(value = "illegalStatistics", title = "全统计")
@Getter
@Setter
public class IllegalStatisticsVO implements Serializable {

    //所属地市
    @ExcelProperty(index = 0)
    private String xzqhdmLabel;
    
    //统计时间
    @ExcelProperty(index = 1)
    private String month;
    
    //治理量（万m²）
    @ExcelProperty(index = 2)
    private BigDecimal byzll;
    
    //其中拆除 （万m²）
    @ExcelProperty(index = 3)
    private BigDecimal byccl;
    
    //治理量（万m²）
    @ExcelProperty(index = 4)
    private BigDecimal dnzll;
    
    //其中拆除 （万m²）
    @ExcelProperty(index = 5)
    private BigDecimal dnccl;
    
    //年治理目标（万m²）
    @ExcelProperty(index = 6)
    private BigDecimal bnzlmb;
    
    //年治理进度，写入自动计算公式
    @ExcelProperty(index = 7, format = "100*E{rowIndex}/F{rowIndex}")
    private String bnzljd;
}

Excel 模板定义－IllegalStatistics.xlsx

Excel 动态查询xml定义

<?xml version="1.0" encoding="UTF-8"?>
<select>
  <!-- 查询传入参数 -->
  <conditions>year,startMonth,endMonth,xzqhdm,curXzqhdm</conditions>
  <!-- 表头动态变量 -->
  <variableMap>{"latestMonth": "{year}年{endMonth}月", "betweenMonth": "{year}年{startMonth}月-{endMonth}月", "zlmbTitle": "{year}年治理目标（万m²）", "zljdTitle": "{year}年治理进度" }</variableMap>
  <!-- 查询SQL：支持变量 ${name} #{name}；支持生成动态 where 1=1 [and name=#{name}] -->
  <dynamicSql>
    <![CDATA[
    select s.xzqhdm, s.xzqhdmLabel,
    CONCAT(${year}, '-', ${endMonth}) as month, ROUND(t2.byzll, 2) as byzll,
    ROUND(t2.xzTotal, 2) as xzTotal, ROUND(t2.clTotal, 2) as clTotal,
    from (
    select regionalization as xzqhdm, regionalization_name as xzqhdmLabel,
    trim(TRAILING '0' from regionalization) as city_code
    from sp_ad_code
    where length(trim(TRAILING '0' from regionalization))=4
    ) s
    left join jd_target_reporting t3 on t1.xzqhdm = t3.xzqhdm
    left join sp_ad_code ad on ad.deleted = 0 and ad.regionalization = t1.xzqhdm
    where 1 = 1
    [AND (#{xzqhdm} IS NULL OR t1.xzqhdm LIKE CONCAT(#{xzqhdm},'%'))]
    [AND (#{curXzqhdm} IS NOT NULL OR t1.xzqhdm LIKE CONCAT(#{curXzqhdm},'%'))]
    ]]>
  </dynamicSql>
</select>

Excel 导出数据效果图

核心API

API名称	接口地址
下载Excel模板	GET：easyExcel/exportListTemplate/{reportCode}
导出Excel数据	GET：easyExcel/exportListExcel/{reportCode}?name=xxx

7. 自定义检验单元格数据

@Component
public class MyExcelValidFunction extends ExcelValidFunction {

    @Override
    public List<ExcelError> apply(Integer rowIndex, Object data) {
        //检验属性@ExcelValid
        List<ExcelError> list = ExcelValidUtil.valid(rowIndex, data);

        //检验用户是否拥有此行政区划权限
        Field field = ReflectUtil.getField(data.getClass(), "pathName");
        if(field!=null){
            ExcelProperty property = field.getAnnotation(ExcelProperty.class);
            if(property!=null){
                int colIndex = property.index();
                //...
                list.add(new ExcelError(rowIndex, colIndex, "您无权限导入当前地市数据！"));
            }
        }
        return list;
    }
}

检验效果如下