目录
我遇到的场景：
业务上需要读取表格中的所有数据，接着入库保存。
思路：
一、观察表格整体结构
值得注意的两个细节：
“合并单元格实际上就是多个单元格都是同一个值”
“左上原则：拆开合并单元格后，仅最左或最上单元格保留原有值，剩余单元格均为空”
二、设计数据结构
目标：设计一个围绕指标、综保区、的数据表，并且保证指标和综保区可扩展。
这里将指标和综保区统统编码化，便于扩展。
CREATE TABLE `t_cba_dispatch_summary` ( `id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '主键', `sequence_no` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '序号', `first_indicator` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '一级指标 | \n1 一线进出口值（亿元）\n2 一线进出口值增长率（%）\n3 一线进出口值全国排名（全国155家）\n4 企业情况（家）\n5 招商引资（个）\n6 实际利用外资（亿美元）\n7 聚焦生产要素\n', `second_indicator` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '二级指标 | \n1_1 一线进出口值（亿元）\r\n2_1 一线进出口值增长率（%）\r\n3_1 一线进出口值全国排名（全国155家）\r\n3_2 一线进口值（亿元）\r\n3_3 一线进口值增长率（%）\r\n3_4 一线出口值（亿元）\r\n3_5 一线出口值增长率（%）\r\n4_1 期末注册企业数\r\n4_2 有进出口业务企业\r\n4_3 加工贸易企业数\r\n5_1 签约项目金额（亿元）\r\n5_2 外向型经济项目（个）\r\n5_3 100亿元以上项目（个）\r\n5_4 50亿元以上项目（个）\r\n5_5 10亿元以上项目（个）\r\n5_6 1亿元以上项目（个）\r\n5_7 3000万美元以上外资项目（个）\r\n5_8 在谈项目（个）\r\n6_1 实际利用外资（亿美元）\r\n7_1 引进各类人才（人）\r\n7_2 引进研发创新机构（个）\r\n', `cba_name` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '综保区名称 | \n武汉东湖综保区 wh_dh_cba\r\n武汉新港空港综保区 wh_xgkg_cba\r\n武汉经开综保区 wh_jk_cba\r\n宜昌综保区 yc_cba\r\n襄阳综保区（未运营 xy_cba\r\n黄石棋盘洲综保区（在建） hsqpz_cba\r\n', `cba_code` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '综保区编码|\n武汉东湖综保区 wh_dh_cba\n武汉新港空港综保区 wh_xgkg_cba\n武汉经开综保区 wh_jk_cba\n宜昌综保区 yc_cba\n襄阳综保区（未运营） xy_cba\n黄石棋盘洲综保区（在建） hsqpz_cba\n', `amount_to` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '合计', `create_user` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '创建者', `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `create_dept` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '创建部门code', `create_dept_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '创建部门name', `update_user` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '更新者', `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', `is_deleted` int DEFAULT '0' COMMENT '删除状态 | 0（默认） 未删除 1 已删除', PRIMARY KEY (`id`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='综保区年月调度汇总表(COMPREHENSIVE_BONDED_AREA)';
三、引入依赖
<!--easy-excel最新依赖-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.2.0</version>
</dependency>四、建立实体
分析excel表头，建立实体。多级表头时，设置各表头路径：
//多级表头在设置value时，每行表头都需要记录下来，可以理解成告诉程序基层表头的路径。 @ExcelProperty(value = {"综保区2022年1-6月份调度汇总表", "一级指标"}, index = 1)
@AllArgsConstructor
@NoArgsConstructor
@Data
public class ComprehensiveBondedAreaDispatchVO {
@ExcelProperty(value = {"综保区2022年1-6月份调度汇总表", "序号"}, index = 0)
private String sequenceNo;
@ExcelProperty(value = {"综保区2022年1-6月份调度汇总表", "一级指标"}, index = 1)
private String firstIndicator;
@ExcelProperty(value = {"综保区2022年1-6月份调度汇总表", "二级指标"}, index = 2)
private String secondIndicator;
@ExcelProperty(value = {"综保区2022年1-6月份调度汇总表", "武汉东湖综保区"}, index = 3)
private String wuhanDongHuCba;
@ExcelProperty(value = {"综保区2022年1-6月份调度汇总表", "武汉新港空港综保区"}, index = 4)
private String wuhanXinGangKongGangCba;
@ExcelProperty(value = {"综保区2022年1-6月份调度汇总表", "武汉经开综保区"}, index = 5)
private String wuhanJingKaiJkCba;
@ExcelProperty(value = {"综保区2022年1-6月份调度汇总表", "宜昌综保区"}, index = 6)
private String yiChangCba;
@ExcelProperty(value = {"综保区2022年1-6月份调度汇总表", "襄阳综保区（未运营）"}, index = 7)
private String xiangYangCba;
@ExcelProperty(value = {"综保区2022年1-6月份调度汇总表", "黄石棋盘洲综保区（在建）"}, index = 8)
private String huangShiQpzCba;
@ExcelProperty(value = {"综保区2022年1-6月份调度汇总表", "合计"}, index = 9)
private String amount;
}五、实现自定义解析逻辑
通常这里只需要继承一下抽象类，然后根据自身业务覆写一些方法实现解析逻辑即可。
extends AnalysisEventListener<M> 以下面我的代码为例，由于我需要解析多个类型不同的sheet，扩展需要，我这里是定义了一个抽象类去继承的 AnalysisEventListener<M> 。接着用具体的子类去继承它，并在其中实现了我需要的通用解析业务。代码如下：
@Getter
@Slf4j
public abstract class AbstractDataIntegrationListener<M> extends AnalysisEventListener<M> {
/**
* The constant TOTAL_KEY.
*/
public static final String TOTAL_KEY = "总计";
/**
* The constant AMOUNT_TO_KEY.
*/
public static final String AMOUNT_TO_KEY = "合计";
/**
* The Head row number.
*/
public int headRowNumber;
/**
* The Start row number.
*/
public int startRowNumber;
/**
* The constant HEAD_OFFSET.
*/
public static final AtomicInteger HEAD_OFFSET = new AtomicInteger();
/**
* The Model class.
*/
public Class<M> modelClass;
/**
* The constant BATCH_COUNT.
*
* @Description 每100条存库, 然后清理list, 方便GC。
* @Author chentl
* @Create: 2023 /2/8 10:30 上午
*/
protected static final int BATCH_COUNT = 100;
/**
* The Cached model data.
*
* @Description 缓存的excel数据
* @Author chentl
* @Create: 2023 /2/8 10:34 上午
*/
public List<M> cachedModelData = Lists.newArrayListWithExpectedSize(BATCH_COUNT);
@Override
public void invoke(M m, AnalysisContext analysisContext) {
if (toJumpHeadRow(analysisContext)) {
return;
}
cachedModelData.add(m);
}
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
if (HEAD_OFFSET.incrementAndGet() == headRowNumber) {
super.invokeHeadMap(headMap, context);
checkExcelHeader(headMap, modelClass);
}
super.invokeHeadMap(headMap, context);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
saveData();
log.info("[easy-excel]解析完成。");
}
/**
* 获取监听器，由子类实现
*
* @param enums 枚举入参
* @return {@link AbstractDataIntegrationListener<M>}
* @author chentl
* @version v1.0.0
* @since 9 :45 上午 2023/2/9
*/
public abstract AbstractDataIntegrationListener<M> getListener(DataIntegrationReadEnum enums);
/**
* 保存数据业务
*
* @author chentl
* @version v1.0.0
* @since 2 :18 下午 2023/2/9
*/
protected void saveData() {
log.info("[easy-excel]{}条数据，开始处理。", cachedModelData.size());
log.info("[easy-excel]处理结束,未见异常。");
}
/**
* 分批操作
*
* @author chentl
* @version v1.0.0
* @since 11 :13 上午 2023/2/8
*/
protected void saveInBatches() {
if (cachedModelData.size() >= BATCH_COUNT) {
saveData();
// 存储完成清理 list
cachedModelData = Lists.newArrayListWithExpectedSize(BATCH_COUNT);
}
}
/**
* 是否跳过表头
*
* @param analysisContext the analysis context
* @return {@link boolean}
* @author chentl
* @version v1.0.0
* @since 3 :04 下午 2023/2/9
*/
protected boolean toJumpHeadRow(AnalysisContext analysisContext) {
return analysisContext.readRowHolder().getRowIndex() < startRowNumber - 1;
}
/**
* 传递合并"列"单元格的值
*
* @param m 表格泛型对象
* @param targetFieldNameArr 目标列索引名称
* @author chentl
* @version v1.0.0
* @since 2 :35 下午 2023/2/9
*/
protected void passMergedColValue(M m, String... targetFieldNameArr) {
try {
List<String> fieldList = Arrays.stream(m.getClass().getDeclaredFields()).map(Field::getName).collect(Collectors.toList());
for (String currentField : targetFieldNameArr) {
//上一列
String previousFiled = fieldList.get(fieldList.indexOf(currentField) - 1);
Method curFieldGetMethod = m.getClass().getMethod("get" + StringUtils.capitalize(currentField));
Method curFiledSetMethod = m.getClass().getMethod("set" + StringUtils.capitalize(currentField), String.class);
Method preFieldGetMethod = m.getClass().getMethod("get" + StringUtils.capitalize(previousFiled));
val currentFieldVal = ValueUtils.parseString(curFieldGetMethod.invoke(m, null));
if (StringUtils.isBlank(currentFieldVal)) {
val previousFieldVal = ValueUtils.parseString(preFieldGetMethod.invoke(m, null));
curFiledSetMethod.invoke(m, previousFieldVal);
}
}
} catch (NoSuchMethodException | IllegalAccessException | InvocationTargetException e) {
log.error("[easy-excel]传递合并横向单元格的值时发生异常", e);
}
}
/**
* 传递合并"行"单元格的值
*
* @param m 表格泛型对象
* @param targetFiledNameArr 目标字段名称
* @author chentl
* @version v1.0.0
* @since 1 :20 下午 2023/2/9
*/
protected void passMergedRowValue(M m, String... targetFiledNameArr) {
try {
for (String fn : targetFiledNameArr) {
Method setMethod = m.getClass().getMethod("set" + StringUtils.capitalize(fn), String.class);
Method getMethod = m.getClass().getMethod("get" + StringUtils.capitalize(fn));
M lastField = cachedModelData.stream().reduce((f, s) -> s).orElse(null);
if (CollectionUtil.isEmpty(cachedModelData) || Objects.isNull(lastField)) {
return;
}
val currentFieldVal = ValueUtils.parseString(getMethod.invoke(m, null));
if (StringUtils.isBlank(currentFieldVal)) {
val lastFieldVal = ValueUtils.parseString(getMethod.invoke(lastField, null));
setMethod.invoke(m, lastFieldVal);
}
}
} catch (NoSuchMethodException | IllegalAccessException | InvocationTargetException e) {
log.error("[easy-excel]传递合并竖向单元格的值时发生异常", e);
}
}
}定义具体的解析器 ，处理略微复杂的表格内容。重点在覆写 invoke(...) 方法：
这里有三段扩展的业务：
1、跳过表头，目的是为了直接读取数据
2、传递横向被合并单元格的数据。（因为 |A|B| 合并为 |AB|后，读取时只有A那一列有数据，这个方法 passMergedColValue 就是将A的数据传递给B。）
3、传递纵向被合并单元格的数据。( 因为
—— —— ——
A B A
—— 和 —— ==> B
——
被合并后，读取时只有B那一行有数据，这个方法 passMergedRowValue 就是将A的值传递给B。
)
@Getter
@NoArgsConstructor
@Slf4j
public class ComprehensiveBondedAreaDispatchDiListener extends AbstractDataIntegrationListener<ComprehensiveBondedAreaDispatchVO> {
public ComprehensiveBondedAreaDispatchDiListener(int startRowNumber, int headRowNumber, Class<ComprehensiveBondedAreaDispatchVO> clazz) {
super();
super.startRowNumber = startRowNumber;
super.headRowNumber = headRowNumber;
super.modelClass = clazz;
}
@Override
public void invoke(ComprehensiveBondedAreaDispatchVO cba, AnalysisContext analysisContext) {
List<Integer> skipRow = Arrays.asList(9, 13, 23);
//跳过指定行号
boolean toJump = skipRow.contains(analysisContext.readRowHolder().getRowIndex());
if (toJumpHeadRow(analysisContext) || toJump) {
return;
}
passMergedColValue(cba, "secondIndicator");
passMergedRowValue(cba, "sequenceNo", "firstIndicator", "secondIndicator");
cachedModelData.add(cba);
saveInBatches();
}
/**
* 获取监听器，由子类实现
*
* @param enums 监听器枚举
* @return {@link AbstractDataIntegrationListener< ComprehensiveBondedAreaDispatchVO >}
* @author chentl
* @version v1.0.0
* @since 3:48 下午 2023/2/8
*/
@Override
public AbstractDataIntegrationListener<ComprehensiveBondedAreaDispatchVO> getListener(DataIntegrationReadEnum enums) {
return new ComprehensiveBondedAreaDispatchDiListener(enums.startRowNumber, enums.headRowNumber, ComprehensiveBondedAreaDispatchVO.class);
}
}六、业务调用
下面是controller以及service下的业务实现：
@PostMapping("/read-test-4")
@ApiOperationSupport(order = 4)
@ApiOperation(value = "上传4", notes = "传入file")
public R<Object> upload4(@RequestParam MultipartFile file) {
Object result = dataIntegrationService.readExcel(file, DataIntegrationReadEnum.COMPREHENSIVE_BONDED_AREA_DISPATCH);
return R.data(result);
}public class DataIntegrationService {
public Object readExcel(MultipartFile file, DataIntegrationReadEnum diReadEnum) {
Map<String, Object> sheetResultMap = Maps.newLinkedHashMapWithExpectedSize(5);
AbstractDataIntegrationListener<?> diListener;
Class<?> excelModel;
try {
diListener = ((AbstractDataIntegrationListener) Class.forName(diReadEnum.listenerClassName).newInstance()).getListener(diReadEnum);
excelModel = diListener.modelClass;
if (Objects.isNull(excelModel)) {
return null;
}
String sheetName = diReadEnum.sheetName;
ExcelReader excelReader = EasyExcel.read(file.getInputStream()).build();
ReadSheet sheet = EasyExcel.readSheet(sheetName).head(excelModel).registerReadListener(diListener).build();
excelReader.read(sheet);
sheetResultMap.put(sheetName, diListener.getCachedModelData());
} catch (InstantiationException | IllegalAccessException | ClassNotFoundException e) {
log.error("[easy-excel]读取模板解析类时发生异常:", e);
} catch (IOException e) {
log.error("[easy-excel]读取excel时发生IO异常:", e);
}
return R.data(sheetResultMap);
}
}七、其他代码段
上述代码中涉及的枚举类 DataIntegrationReadEnum
@Getter
@NoArgsConstructor
@AllArgsConstructor
public enum DataIntegrationReadEnum {
//数据统纳EXCEL待读取表格枚举
VOLUME_OF_FREIGHT_BY_DISTRICT(0, "进出口货运量分市州汇总表", 4, 5, "com.dsj.message.excel.listener.VofByDistrictDiListener"),
VOLUME_OF_FREIGHT_BY_TRANSPORTATION_MODE(1, "进出口货运量按运输方式汇总表", 4, 5, "com.dsj.message.excel.listener.VofByTransportationModeDiListener"),
VOLUME_OF_FREIGHT_BY_CUSTOMS_STATISTICS(2, "海关货运量业务统计表", 4, 5, "com.dsj.message.excel.listener.VofByCustomStatisticsDiListener"),
COMPREHENSIVE_BONDED_AREA_DISPATCH(3, "综保区2022年01-06月份调度汇总表", 2, 3, "com.dsj.message.excel.listener.ComprehensiveBondedAreaDispatchDiListener"),
BONDED_LOGISTICS_CENTER_DISPATCH(4, "保税物流中心（B型）2022年1-6月份调度汇总表", 2, 3, "com.dsj.message.excel.listener.BondedLogisticsCenterDispatchDiListener"),
;
public int sheetIndex;
public String sheetName;
public int headRowNumber;
public int startRowNumber;
public String listenerClassName;
}
620
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
