架构师撸轮子～Excel导入导出

架构师撸轮子 07-05 1861

前言：

今天我们对“bigdecimal非空”大概比较看重，兄弟们都想要分析一些“bigdecimal非空”的相关知识。那么小编在网络上网罗了一些有关“bigdecimal非空””的相关内容，希望小伙伴们能喜欢，我们一起来学习一下吧！

产品需求

Excel导入需求说明

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

Excel导出需求说明

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

技术方案

Excel导入流程图

Excel导出流程图

案例展示1. 导入检验 Excel加批注2. 导出Excel 自动生成公式3. 导出Excel 生成单选下拉框4. 导出Excel 生成级联下拉框5. 导出Excel 生成多选下拉框代码集成1. 添加配置

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@Slf4jpublic 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@Slf4jpublic 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@Setterpublic 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@Setterpublic 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@Setterpublic 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. 自定义检验单元格数据

@Componentpublic 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;    }}

检验效果如下

源码获取

关注作者后私信回复关键词【excel】

本文地址：http://www.longkongtuishu.com/ca5aeA2sCB1oPC1c.html

标签： #bigdecimal非空

架构师撸轮子～Excel导入导出

单例模式，看起来简单也有学问

架构师撸轮子～Excel导入导出