JAVA Excel导入导出

最新推荐文章于 2024-07-03 14:13:18 发布

huang714

最新推荐文章于 2024-07-03 14:13:18 发布

阅读量1k

点赞数 23

文章标签： java excel python

本文链接：https://blog.csdn.net/huang714/article/details/136292169

版权

产品需求

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

检验效果如下

huang714

关注

23
点赞
踩
7

收藏

觉得还不错? 一键收藏
0
评论
JAVA Excel导入导出

Component@Slf4j@Autowired@Autowired@Autowired@Autowired@Override@Override//处理字典翻译@Override//处理字典翻译@Override@Override@Overrideif (!// 调用业务service分页方法@Override//处理字典翻译、行政区划翻译@Override//自定义检验类@Component@Slf4j。
复制链接

扫一扫