产品需求
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;
}
}
检验效果如下