easyexcel讀取合并單元格

文章目錄

• easyexcel讀取合并單元格
• • 一、設置讀取額外信息
  • 二、重寫Listener中的extra()方法，獲取合并單元格的信息
  • 三、遍歷合并單元格的信息
  • 四、代碼清單
  • • 1. UploadDataListener.java
    • 2. ExcelAnalysisHelper.java

一、設置讀取額外信息

二、重寫Listener中的extra()方法，獲取合并單元格的信息

三、遍歷合并單元格的信息

合并單元格只有第一個（firstRowIndex，firstColumnIndex）有值，所以要取到這個值。

通過獲取到的合并單元格信息（firstRowIndex,lastRowIndex,firstColumnIndex,lastColumnIndex），遍歷此區域的每一個單元格，并給每一個單元格都賦上該值

此方法的重點在于利用反射找到實體對應的屬性，對應關系是@ExcelProperty(index = 0)->columnIndex
index對應了columnIndex（也就是字段在excel所在的位置）；rowindex對應了解析出來的List<T> data的索引值

四、代碼清單

1. UploadDataListener.java

import com.alibaba.excel.context.AnalysisContext; import com.alibaba.excel.event.AnalysisEventListener; import com.alibaba.excel.metadata.CellExtra; import com.alibaba.fastjson.JSON; import com.baomidou.mybatisplus.extension.api.Assert; import org.slf4j.Logger; import org.slf4j.LoggerFactory;import java.util.ArrayList; import java.util.List;/*** 模板的讀取類** @author wangwei*/ public class UploadDataListener<T> extends AnalysisEventListener<T> {private static final Logger LOGGER = LoggerFactory.getLogger(UploadDataListener.class);/*** 解析的數據*/List<T> list = new ArrayList<>();/*** 正文起始行*/private Integer headRowNumber;/*** 合并單元格*/private List<CellExtra> extraMergeInfoList = new ArrayList<>();public UploadDataListener(Integer headRowNumber) {this.headRowNumber = headRowNumber;}/*** 這個每一條數據解析都會來調用** @param data one row value. Is is same as {@link AnalysisContext#readRowHolder()}* @param context context*/@Overridepublic void invoke(T data, AnalysisContext context) {LOGGER.info("解析到一條數據:{}", JSON.toJSONString(data));list.add(data);}/*** 所有數據解析完成了 都會來調用** @param context context*/@Overridepublic void doAfterAllAnalysed(AnalysisContext context) {LOGGER.info("所有數據解析完成！");}/*** 加上存儲數據庫*/public List<T> getData() {return list;}@Overridepublic void extra(CellExtra extra, AnalysisContext context) {LOGGER.info("讀取到了一條額外信息:{}", JSON.toJSONString(extra));switch (extra.getType()) {case COMMENT: {LOGGER.info("額外信息是批注,在rowIndex:{},columnIndex;{},內容是:{}", extra.getRowIndex(), extra.getColumnIndex(),extra.getText());break;}case HYPERLINK: {if ("Sheet1!A1".equals(extra.getText())) {LOGGER.info("額外信息是超鏈接,在rowIndex:{},columnIndex;{},內容是:{}", extra.getRowIndex(),extra.getColumnIndex(), extra.getText());} else if ("Sheet2!A1".equals(extra.getText())) {LOGGER.info("額外信息是超鏈接,而且覆蓋了一個區間,在firstRowIndex:{},firstColumnIndex;{},lastRowIndex:{},lastColumnIndex:{},"+ "內容是:{}",extra.getFirstRowIndex(), extra.getFirstColumnIndex(), extra.getLastRowIndex(),extra.getLastColumnIndex(), extra.getText());} else {Assert.fail("Unknown hyperlink!");}break;}case MERGE: {LOGGER.info("額外信息是合并單元格,而且覆蓋了一個區間,在firstRowIndex:{},firstColumnIndex;{},lastRowIndex:{},lastColumnIndex:{}",extra.getFirstRowIndex(), extra.getFirstColumnIndex(), extra.getLastRowIndex(),extra.getLastColumnIndex());if (extra.getRowIndex() >= headRowNumber) {extraMergeInfoList.add(extra);}break;}default: {}}}public List<CellExtra> getExtraMergeInfoList() {return extraMergeInfoList;} }

2. ExcelAnalysisHelper.java

import cn.xxx.UploadDataListener; import cn.xxx.BizException; import cn.xxx.ResultCode; import com.alibaba.excel.EasyExcel; import com.alibaba.excel.annotation.ExcelProperty; import com.alibaba.excel.enums.CellExtraTypeEnum; import com.alibaba.excel.metadata.CellExtra; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.util.CollectionUtils; import org.springframework.web.multipart.MultipartFile;import java.io.IOException; import java.lang.reflect.Field; import java.util.List;/*** @author wangwei* @date 2020-12-01 13:34**/ public class ExcelAnalysisHelper<T> {private static final Logger LOGGER = LoggerFactory.getLogger(ExcelAnalysisHelper.class);public List<T> getList(MultipartFile file, Class<T> clazz) {return getList(file, clazz, 0, 1);}public List<T> getList(MultipartFile file, Class<T> clazz, Integer sheetNo, Integer headRowNumber) {UploadDataListener<T> listener = new UploadDataListener<>(headRowNumber);try {EasyExcel.read(file.getInputStream(), clazz, listener).extraRead(CellExtraTypeEnum.MERGE).sheet(sheetNo).headRowNumber(headRowNumber).doRead();} catch (IOException e) {LOGGER.error(e.getMessage());}List<CellExtra> extraMergeInfoList = listener.getExtraMergeInfoList();if (CollectionUtils.isEmpty(extraMergeInfoList)) {return listener.getData();}List<T> data = explainMergeData(listener.getData(), extraMergeInfoList, headRowNumber);return data;}/*** 處理合并單元格** @param data 解析數據* @param extraMergeInfoList 合并單元格信息* @param headRowNumber 起始行* @return 填充好的解析數據*/private List<T> explainMergeData(List<T> data, List<CellExtra> extraMergeInfoList, Integer headRowNumber) { // 循環所有合并單元格信息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); // 設置值for (int i = firstRowIndex; i <= lastRowIndex; i++) {for (int j = firstColumnIndex; j <= lastColumnIndex; j++) {setInitValueToList(initValue, i, j, data);}}});return data;}/*** 設置合并單元格的值** @param filedValue 值* @param rowIndex 行* @param columnIndex 列* @param data 解析數據*/public void setInitValueToList(Object filedValue, Integer rowIndex, Integer columnIndex, List<T> data) {T object = data.get(rowIndex);for (Field field : object.getClass().getDeclaredFields()) {//提升反射性能，關閉安全檢查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) {throw new BizException(ResultCode.FAILURE, "解析數據時發生異常!");}}}}}/*** 獲取合并單元格的初始值* rowIndex對應list的索引* columnIndex對應實體內的字段** @param firstRowIndex 起始行* @param firstColumnIndex 起始列* @param data 列數據* @return 初始值*/private Object getInitValueFromList(Integer firstRowIndex, Integer firstColumnIndex, List<T> data) {Object filedValue = null;T object = data.get(firstRowIndex);for (Field field : object.getClass().getDeclaredFields()) {//提升反射性能，關閉安全檢查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) {throw new BizException(ResultCode.FAILURE, "解析數據時發生異常!");}}}}return filedValue;} }

注：easyexcel版本為2.2.6

<!--========================EasyExcel 配置============================--><dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>2.2.6</version></dependency><!--========================EasyExcel 配置結束========================-->

總結

以上是生活随笔為你收集整理的easyexcel读取合并单元格的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。