---

前言

之前分享的 EasyExcel 批量導(dǎo)入并校驗(yàn)數(shù)據(jù)，僅支持規(guī)則excel，即首行表頭，下面對應(yīng)數(shù)據(jù)，無合并單元格情況。
本篇主要解決問題：

1. 模板e(cuò)xcel 表頭不在首行
2. 數(shù)據(jù)項(xiàng)有合并單元格情況

esayexcel版本2.2.7

一、需求和效果

二、難點(diǎn)和思路

1. 跳過表頭前的說明

   設(shè)置headRowNumber指定表頭位置，第三行

EasyExcel.read(inputStream, EvalTemplateReq.class,listener).extraRead(CellExtraTypeEnum.MERGE).sheet().headRowNumber(3).doRead();

3. 合并單元格數(shù)據(jù)獲取放入list
   合并單元格的數(shù)據(jù)默認(rèn)是在合并表格的左上第一個(gè)格子，其他為null，獲取到這個(gè)格子的數(shù)據(jù)，賦值給其他被合并單元格對應(yīng)的字段中

3.1 開啟合并單元格識別
extraRead(CellExtraTypeEnum.MERGE)開啟合并單元格識別
3.2 獲取合并的單元格數(shù)據(jù)
EvalExcelDataListener 重寫extra方法，獲取到被合并的數(shù)據(jù)，后續(xù)處理。extra方法在invoke方法執(zhí)行，先不要在意要導(dǎo)入的list部分字段是null，后續(xù)重新賦值即可。
extra會從excel頭讀取，所以要把前2行的數(shù)據(jù)過濾掉，這里用extra.getFirstRowIndex()>2判斷一下

    // 合并單元格private final List<CellExtra> extraMergeInfoList = new ArrayList<>();@Overridepublic void extra(CellExtra extra, AnalysisContext context) {if (extra.getType() == CellExtraTypeEnum.MERGE) {// 處理合并單元格的情況int firstRowIndex = extra.getFirstRowIndex();if(firstRowIndex>2) {extraMergeInfoList.add(extra);}}}

3.3 doAfterAllAnalysed中補(bǔ)全list字段值

    @Overridepublic void doAfterAllAnalysed(AnalysisContext context) {//所有數(shù)據(jù)解析完畢執(zhí)行該方法// 防止導(dǎo)入空的Excelif (context.readRowHolder().getRowIndex() <= 0) {throw new ExcelAnalysisException("當(dāng)前excel無數(shù)據(jù)!");}//處理合并單元格list = EasyExcelMergeUtil.explainMergeData(list, extraMergeInfoList, 3);saveData();}

3.4 explainMergeData 補(bǔ)全字段值
根據(jù)單元格左上角標(biāo)通過反射獲取字段并賦值給其他被合并的單元格，角標(biāo)對應(yīng)實(shí)體類index屬性

三、全部代碼

1. 接收數(shù)據(jù)實(shí)體

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.fasterxml.jackson.annotation.JsonIgnore;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;import javax.validation.constraints.NotBlank;
import java.util.List;/*** 評估標(biāo)準(zhǔn)*/
@Builder
@Data
@AllArgsConstructor
@NoArgsConstructor
public class EvalTemplateReq {@ExcelIgnoreprivate String id;/*** 評估項(xiàng)*/@ExcelProperty(value = "評估名稱",index = 1)@NotBlank(message = "評估名稱不能為空")private String item;/*** 指標(biāo)簡稱*/@ExcelProperty(value = "指標(biāo)簡稱",index =2)@NotBlank(message = "指標(biāo)簡稱不能為空")private String itemShortName;/*** 指標(biāo)編碼*/@ExcelProperty(value = "指標(biāo)編碼",index = 3)@NotBlank(message = "指標(biāo)編碼不能為空")private String itemCode;/*** 指標(biāo)釋義*/@ExcelProperty(value = "指標(biāo)釋義",index = 4)@NotBlank(message = "指標(biāo)釋義不能為空")private String itemExplain;/*** 評價(jià)要求/分?jǐn)?shù) json*/@ExcelIgnoreprivate List<Requirements> requirementList;/*** 系統(tǒng)監(jiān)測類/參演部門上報(bào)類*/@ExcelProperty(value = "分類",index = 0)@NotBlank(message = "分類不能為空")private String type;@ExcelProperty(value = "評價(jià)要求",index = 5)@JsonIgnoreprivate String require;@ExcelProperty(value = "分?jǐn)?shù)",index = 6)@JsonIgnoreprivate Integer score;@Builder@Data@AllArgsConstructor@NoArgsConstructorpublic static class Requirements {@ExcelIgnoreprivate String id;/*** 評價(jià)內(nèi)容*/@NotBlank(message = "評價(jià)要求不能為空")private String require;/*** 分?jǐn)?shù)*/@NotBlank(message = "分?jǐn)?shù)不能為空")private Integer score;}
}

2. service調(diào)用

    /*** 導(dǎo)入評估標(biāo)準(zhǔn)*/@Overridepublic String importEval(MultipartFile file) {EvalExcelDataListener listener = new EvalExcelDataListener(evalTemplateDao);InputStream inputStream;try {inputStream = file.getInputStream();EasyExcel.read(inputStream, EvalTemplateReq.class,listener).extraRead(CellExtraTypeEnum.MERGE).sheet().headRowNumber(3).doRead();return "全部導(dǎo)入成功！";} catch (IOException e) {throw new BusinessCheckException("Excel 文件流讀取失敗");} catch (ExcelAnalysisException e) {return e.getMessage();} catch (Exception e) {throw new BusinessException("數(shù)據(jù)導(dǎo)入失敗", e);}}

3. 合并單元格數(shù)據(jù)處理util

package com.gsafety.bg.pd.service.excel;import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.CellExtra;
import lombok.extern.slf4j.Slf4j;import java.lang.reflect.Field;
import java.util.List;@Slf4j
public class EasyExcelMergeUtil {/*** 處理合并單元格* @param data               解析數(shù)據(jù)* @param extraMergeInfoList 合并單元格信息* @param headRowNumber      起始行* @return 填充好的解析數(shù)據(jù)*/public static <T> List<T> explainMergeData(List<T> data, List<CellExtra> extraMergeInfoList, Integer headRowNumber) {// 循環(huán)所有合并單元格信息extraMergeInfoList.forEach(cellExtra -> {int firstRowIndex = cellExtra.getFirstRowIndex() - headRowNumber;int lastRowIndex = cellExtra.getLastRowIndex() - headRowNumber;int firstColumnIndex = cellExtra.getFirstColumnIndex();int lastColumnIndex = cellExtra.getLastColumnIndex();// 獲取初始值Object initValue = getInitValueFromList(firstRowIndex, firstColumnIndex, data);// 設(shè)置值for (int i = firstRowIndex; i <= lastRowIndex; i++) {for (int j = firstColumnIndex; j <= lastColumnIndex; j++) {setInitValueToList(initValue, i, j, data);}}});return data;}/*** 設(shè)置合并單元格的值** @param filedValue  值* @param rowIndex    行* @param columnIndex 列* @param data        解析數(shù)據(jù)*/private static <T> void setInitValueToList(Object filedValue, Integer rowIndex, Integer columnIndex, List<T> data) {if (rowIndex >= data.size()) return;T object = data.get(rowIndex);for (Field field : object.getClass().getDeclaredFields()) {// 提升反射性能，關(guān)閉安全檢查field.setAccessible(true);ExcelProperty annotation = field.getAnnotation(ExcelProperty.class);if (annotation != null) {if (annotation.index() == columnIndex) {try {field.set(object, filedValue);break;} catch (IllegalAccessException e) {log.error("設(shè)置合并單元格的值異常：{}", e.getMessage());}}}}}/*** 獲取合并單元格的初始值* rowIndex對應(yīng)list的索引* columnIndex對應(yīng)實(shí)體內(nèi)的字段** @param firstRowIndex    起始行* @param firstColumnIndex 起始列* @param data             列數(shù)據(jù)* @return 初始值*/private static <T> Object getInitValueFromList(Integer firstRowIndex, Integer firstColumnIndex, List<T> data) {Object filedValue = null;T object = data.get(firstRowIndex);for (Field field : object.getClass().getDeclaredFields()) {// 提升反射性能，關(guān)閉安全檢查field.setAccessible(true);ExcelProperty annotation = field.getAnnotation(ExcelProperty.class);if (annotation != null) {if (annotation.index() == firstColumnIndex) {try {filedValue = field.get(object);break;} catch (IllegalAccessException e) {log.error("設(shè)置合并單元格的初始值異常：{}", e.getMessage());}}}}return filedValue;}}

4. easyexcel數(shù)據(jù)監(jiān)聽類

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.enums.CellExtraTypeEnum;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.exception.ExcelAnalysisException;
import com.alibaba.excel.metadata.CellExtra;
import com.gsafety.bg.gsdss.common.utils.json.JsonUtil;
import com.gsafety.bg.pd.dao.EvalTemplateDao;
import com.gsafety.bg.pd.model.dto.req.EvalTemplateReq;
import com.gsafety.bg.pd.model.po.EvalTemplatePO;import java.util.*;
import java.util.stream.Collectors;public class EvalExcelDataListener extends AnalysisEventListener<EvalTemplateReq> {private final Integer LIST_COUNT = 19;List<EvalTemplateReq> list = new ArrayList<>(LIST_COUNT);// 合并單元格private final List<CellExtra> extraMergeInfoList = new ArrayList<>();// 由于監(jiān)聽器只能通過new的方式創(chuàng)建，所以可以通過構(gòu)造器傳入dao層對象private final EvalTemplateDao dao;public EvalExcelDataListener(EvalTemplateDao dao) {this.dao = dao;}@Overridepublic void invoke(EvalTemplateReq req, AnalysisContext context) {list.add(req);if (list.size() > LIST_COUNT) {throw new ExcelAnalysisException("當(dāng)前excel數(shù)據(jù)量不得大于" + LIST_COUNT + "條！");}}@Overridepublic void extra(CellExtra extra, AnalysisContext context) {if (extra.getType() == CellExtraTypeEnum.MERGE) {// 處理合并單元格的情況int firstRowIndex = extra.getFirstRowIndex();if(firstRowIndex>2) {extraMergeInfoList.add(extra);}}}@Overridepublic void doAfterAllAnalysed(AnalysisContext context) {//所有數(shù)據(jù)解析完畢執(zhí)行該方法// 防止導(dǎo)入空的Excelif (context.readRowHolder().getRowIndex() <= 0) {throw new ExcelAnalysisException("當(dāng)前excel無數(shù)據(jù)!");}//處理合并單元格list = EasyExcelMergeUtil.explainMergeData(list, extraMergeInfoList, 3);saveData();}protected void saveData() {Map<String, List<EvalTemplateReq>> collect = list.stream().collect(Collectors.groupingBy(EvalTemplateReq::getItem));List<EvalTemplatePO> pos = new ArrayList<>();collect.forEach((k, v) -> {List<EvalTemplateReq.Requirements> requirements = v.stream().map(l -> EvalTemplateReq.Requirements.builder().id(v.indexOf(l) + "").require(l.getRequire()).score(l.getScore()).build()).collect(Collectors.toList());pos.add(EvalTemplatePO.builder().item(k).itemCode(collect.get(k).get(0).getItemCode()).itemExplain(collect.get(k).get(0).getItemExplain()).itemShortName(collect.get(k).get(0).getItemShortName()).type(collect.get(k).get(0).getType()).requirements(JsonUtil.of(requirements)).build());});dao.deleteAll();dao.saveAll(pos);}
}

踩坑

1. extra方法不生效
   extraRead(CellExtraTypeEnum.MERGE)顯式指定識別合并單元格數(shù)據(jù)
   invoke中原校驗(yàn)數(shù)據(jù)，因?yàn)闉閚ull導(dǎo)致攔截，不走extra方法。
   執(zhí)行順序：invoke->extra->doAfterAllAnalysed
2. 指定表頭位置后extra獲取的第一個(gè)數(shù)據(jù)還是首行的合并單元格數(shù)據(jù)
   headRowNumber(3)只是指定了讀取表頭的位置，extra是獲取整表的所有合并單元格數(shù)據(jù)，根據(jù)excel模板，要跳過2行，獲取從第三行后的合并單元格數(shù)據(jù)。if(firstRowIndex>2)

參考：https://blog.csdn.net/xhmico/article/details/136905419

---