excel动态字段导入导出
1. 背景介绍
遇到的业务需求为: 有主要填写内容及扩展的填写信息(扩展的填写信息又根据类型区分出不同的字段),
简单理解就是我需要填写我的个人信息，身份证,手机号,姓名, 然后根据职业不同, 例如我是程序员需要填写入行年限, 会那些语言, 英语四六级, 如果是医生就填写 职位, 主刀病例, 考过那些证件等来组成每个人的信息表, 表的设计我是主表+职业子表(一对多关系,职业字段用扩展字段json存储)
导入的模板一般是有多个的(模板有对应程序员模板及医生模板)这个模板可以是客户提供或我们提前生成好传哪种类型调哪种模板
2.设计思路
如果是正常的一张表那么直接用hutool的导入导出功能即可,但涉及到每个类型都对应着不同的扩展字段(扩展字段可能有很多字段)
这时候就有两种做法
1> 固定读取法: 导入excel后根据表头一个个读取,但这种适合于固定模板,一旦我的某一列换了顺序那么后端读取数据也会造成错乱
程序员导入模板
先用一张表(就先理解为一对一)进行测试写代码
mysql个人信息表(test_excel)
后端逻辑代码(简单示例代码)
package com.chinaoly.api; import cn.hutool.poi.excel.ExcelReader; import cn.hutool.poi.excel.ExcelUtil; import com.alibaba.fastjson.JSONObject; import com.chinaoly.dao.mapper.ExcelTestMapper; import com.chinaoly.model.TestExcelDO; import com.chinaoly.util.EmptyUtils; import com.chinaoly.util.config.BizException; import io.swagger.annotations.Api; import io.swagger.annotations.ApiOperation; import lombok.extern.slf4j.Slf4j; import org.springframework.web.bind.annotation.*; import org.springframework.web.multipart.MultipartFile; import javax.annotation.Resource; import java.io.InputStream; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; /** * @author admin * @date 2024/6/25 **/ @Slf4j @RestController @RequestMapping("/excelTest") @Api(value = "excel测试", tags = {"excel测试"}) public class excelTest { @Resource private ExcelTestMapper excelTestMapper; @PostMapping("/excelTestImport") @ApiOperation(value = "excelTestImport",notes = "测试excel导入") public void excelTestImport(@RequestParam(name = "file") MultipartFile file) { if (file.isEmpty()) { BizException.throwException("文件不能为空"); } try { //获取文件信息 InputStream inputStream = file.getInputStream(); ExcelReader reader = ExcelUtil.getReader(inputStream); List<List<Object>> list = reader.read(1); List<TestExcelDO> excelDOList = new ArrayList<>(); TestExcelDO testExcelDO = new TestExcelDO(); for (List<Object> row : list) { //个人信息基本字段 if (EmptyUtils.isNotEmpty(row.get(0))) { testExcelDO.setSfz(row.get(0).toString()); } if (EmptyUtils.isNotEmpty(row.get(1))) { testExcelDO.setPhone(row.get(1).toString()); } if (EmptyUtils.isNotEmpty(row.get(2))) { testExcelDO.setXm(row.get(2).toString()); } Map<String, Object> map = new HashMap<>(); //扩展字段获取 if (EmptyUtils.isNotEmpty(row.get(3)) || EmptyUtils.isNotEmpty(row.get(4))) { if (EmptyUtils.isNotEmpty(row.get(3))) { map.put("rhNx", row.get(3).toString()); } if (EmptyUtils.isNotEmpty(row.get(4))) { map.put("yunYa", row.get(4).toString()); } } //扩展字段 if (EmptyUtils.isNotEmpty(map)) { //组装进json String json = JSONObject.toJSONString(map); testExcelDO.setExtend(json); } excelDOList.add(testExcelDO); } //数据批量插入 excelTestMapper.insertMysqlAndOthersList(excelDOList); System.out.println(excelDOList); } catch (Exception e) { log.error("报错: error:{}", e.getMessage()); } } }
执行完后数据也被导进去了
这种缺陷是不够灵活,一旦增加或减少字段或将原有字段打乱顺序
将手机号与身份证调换位置
执行结果-数据也错乱了
2>动态读取法: 动态读取肯定是要解决第一种方式的痛点,可以随意在excel增加删除或修改顺序字段
那也意味着我不清楚excel具体有那些字段,顺序咋样我也不清楚,那就存入字典吧,创建一个字典表,将excel的所有字段都存入对应字典
如果excel增加或删除的字段,对应字典表也增加删除(不需要修改后端逻辑),顺序可以忽略,毕竟本身就是依靠表头名称进行读取的(字典的名称一定要跟excel表头一致不然读取不到)
字典配置表
动态获取后端逻辑
@PostMapping("/excelTestDtImport")
@ApiOperation(value = "excelTestDtImport",notes = "测试excel动态导入")
public void excelTestDtImport(@RequestParam(name = "file") MultipartFile file) {
if (file.isEmpty()) {
BizException.throwException("文件不能为空");
}
//查询全部字典(将医生字段部分先删除掉了)
List<TestDictDO> dictList = testDictMapper.selectAll();
try {
//获取文件信息
InputStream inputStream = file.getInputStream();
//生成Workbook
Workbook workbook = WorkbookFactory.create(inputStream);
Sheet sheet = workbook.getSheetAt(0);
//获取第一行表头(一般是中文)
Row headerRow = sheet.getRow(0);
//获取表头信息
Map<Integer, String> columnMap = new HashMap<>();
for (Cell cell : headerRow) {
//将表头存进map
columnMap.put(cell.getColumnIndex(), cell.getStringCellValue());
}
List<TestExcelDO> testExcelList = new ArrayList<>();
//获取每行的数据从第一行开始
for (int i = 1; i<sheet.getLastRowNum()+1; i++) {
Row currentRow = sheet.getRow(i);
//如果该行为空则跳出
if (currentRow == null) {
continue;
}
Class<TestExcelDO> testExcelClass = TestExcelDO.class;
TestExcelDO testExcelDO = new TestExcelDO();
Map<String, Object> map = new HashMap<>();
//每一列数据遍历
for (Cell cell : currentRow) {
//每个表头获取自己那一列的数据
if (!columnMap.containsKey(cell.getColumnIndex())) {
continue;
}
//根据excel每列数据类型获取对应数据
Object cellValue = DataUtils.getCellValue(cell);
//字典列表
for (TestDictDO dict : dictList) {
if(!dict.getFieldName().equals(columnMap.get(cell.getColumnIndex()))) {
continue;
}
if ("基本信息".equals(dict.getType())) {
//反射并设置字段值
PropertyDescriptor propertyDescriptor = new PropertyDescriptor(dict.getField(), testExcelClass);
Method writeMethod = propertyDescriptor.getWriteMethod();
//设置值(默认字符串类型,可能是number类型那么这里就不能写toString())
writeMethod.invoke(testExcelDO, cellValue.toString());
} else {
map.put(dict.getField(), cellValue.toString());
}
}
//扩展字段
if (EmptyUtils.isNotEmpty(map)) {
//组装进json
String json = JSONObject.toJSONString(map);
testExcelDO.setExtend(json);
}
}
testExcelList.add(testExcelDO);
}
//数据批量插入
excelTestMapper.insertMysqlAndOthersList(testExcelList);
System.out.println(testExcelList);
} catch (Exception e) {
log.error("报错: error:{}", e.getMessage());
}
}
日志也成功打印
[TestExcelDO(id=null, sfz=36XXXXXX, phone=13XXXXXX, xm=张三, extend={"rhNx":"2","yunYa":"java"})]
把其中用到的一些方法也发出来
excel获取各类型数据方法
/** * 根据excel每列数据类型获取对应数据 */ public static Object getCellValue(Cell cell) { if (cell.getCellType().equals(CellType.NUMERIC)) { DecimalFormat df =new DecimalFormat("0"); return df.format(cell.getNumericCellValue()); } else if (cell.getCellType().equals(CellType.BLANK)) { return ""; } else if (cell.getCellType().equals(CellType.ERROR)) { return cell.getErrorCellValue(); } else { return cell.getStringCellValue(); } }
统一判空校验类
package com.chinaoly.util; import java.util.Collection; import java.util.Map; /** * 统一空校验工具类 * * @author admin * @date 2024/6/25 */ public class EmptyUtils { /** * 判断目标值为空 */ public static boolean isEmpty(Object obj) { if (obj == null || "".equals(obj.toString())) { return true; } if (obj instanceof String) { return ((String) obj).trim().length() == 0; } if (obj instanceof Collection) { return ((Collection) obj).isEmpty(); } if (obj instanceof Map) { return ((Map) obj).isEmpty(); } if (obj instanceof Object[]) { return ((Object[]) obj).length == 0; } return false; } public static boolean isNotEmpty(Object obj) { return !isEmpty(obj); } /** * 判断目标值为空或为0 */ public static boolean isEmptyOrZero(Object num) { if (EmptyUtils.isEmpty(num)) { return true; } if (num instanceof String) { return num.equals("0"); } if (num instanceof Integer) { return num.equals(0); } if (num instanceof Long) { return num.equals(0L); } if (num instanceof Double) { return num.equals(0D); } if (num instanceof Short) { return (Short) num == 0; } if (num instanceof Float) { return (Float) num == 0; } if (num instanceof Byte) { return (Byte) num == 0; } return false; } public static boolean isNotEmptyOrZero(Object num) { return !isEmptyOrZero(num); } }
主动异常抛出
package com.chinaoly.util.config; import com.chinaoly.frm.core.entity.ResultCode; import lombok.Data; /** * 主动异常抛出 * * @author admin * @date 2024/6/25 */ @Data public class BizException extends RuntimeException { private ResultCode resultCode = ResultCode.ERROR; private BizException(String message) { super(message); } private BizException(ResultCode resultCode, String message) { super(message); this.resultCode = resultCode; } /** * 抛异常 */ public static void throwException(String message) { throw new BizException(message); } public static void throwException(ResultCode resultCode, String message) { throw new BizException(resultCode, message); } }
核心jar包
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.5.8</version>
</dependency>
可以看到动态读取的核心就是配置字典+类反射
excel动态导出原理差不多,也是用hutool类导出
动态表头(动态字典)+列表数据(用字典洗一遍),这里就不展示了
