easyexcel读取合并单元格
一、设置读取额外信息
二、重写Listener中的extra()方法，获取合并单元格的信息
三、遍历合并单元格的信息
- 合并单元格只有第一个（firstRowIndex，firstColumnIndex）有值，所以要取到这个值。
- 通过获取到的合并单元格信息（firstRowIndex,lastRowIndex,firstColumnIndex,lastColumnIndex），遍历此区域的每一个单元格，并给每一个单元格都赋上该值
- 此方法的重点在于利用反射找到实体对应的属性，对应关系是@ExcelProperty(index = 0)->columnIndex
index对应了columnIndex（也就是字段在excel所在的位置）；rowindex对应了解析出来的List data的索引值
四、代码清单
1. UploadDataListener.java
importcom.alibaba.excel.context.AnalysisContext; importcom.alibaba.excel.event.AnalysisEventListener; importcom.alibaba.excel.metadata.CellExtra; importcom.alibaba.fastjson.JSON; importcom.baomidou.mybatisplus.extension.api.Assert; importorg.slf4j.Logger; importorg.slf4j.LoggerFactory; importjava.util.ArrayList; importjava.util.List; /*** 模板的读取类** @author wangwei*/publicclassUploadDataListener<T>extendsAnalysisEventListener<T> { privatestaticfinalLoggerLOGGER=LoggerFactory.getLogger(UploadDataListener.class); /*** 解析的数据*/List<T>list=newArrayList<>(); /*** 正文起始行*/privateIntegerheadRowNumber; /*** 合并单元格*/privateList<CellExtra>extraMergeInfoList=newArrayList<>(); publicUploadDataListener(IntegerheadRowNumber) { this.headRowNumber=headRowNumber; } /*** 这个每一条数据解析都会来调用** @param data one row value. Is is same as {@link AnalysisContext#readRowHolder()}* @param context context*/publicvoidinvoke(Tdata, AnalysisContextcontext) { LOGGER.info("解析到一条数据:{}", JSON.toJSONString(data)); list.add(data); } /*** 所有数据解析完成了 都会来调用** @param context context*/publicvoiddoAfterAllAnalysed(AnalysisContextcontext) { LOGGER.info("所有数据解析完成！"); } /*** 加上存储数据库*/publicList<T>getData() { returnlist; } publicvoidextra(CellExtraextra, AnalysisContextcontext) { LOGGER.info("读取到了一条额外信息:{}", JSON.toJSONString(extra)); switch (extra.getType()) { caseCOMMENT: { LOGGER.info("额外信息是批注,在rowIndex:{},columnIndex;{},内容是:{}", extra.getRowIndex(), extra.getColumnIndex(), extra.getText()); break; } caseHYPERLINK: { if ("Sheet1!A1".equals(extra.getText())) { LOGGER.info("额外信息是超链接,在rowIndex:{},columnIndex;{},内容是:{}", extra.getRowIndex(), extra.getColumnIndex(), extra.getText()); } elseif ("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; } caseMERGE: { LOGGER.info( "额外信息是合并单元格,而且覆盖了一个区间,在firstRowIndex:{},firstColumnIndex;{},lastRowIndex:{},lastColumnIndex:{}", extra.getFirstRowIndex(), extra.getFirstColumnIndex(), extra.getLastRowIndex(), extra.getLastColumnIndex()); if (extra.getRowIndex() >=headRowNumber) { extraMergeInfoList.add(extra); } break; } default: { } } } publicList<CellExtra>getExtraMergeInfoList() { returnextraMergeInfoList; } }
2. ExcelAnalysisHelper.java
importcn.xxx.UploadDataListener; importcn.xxx.BizException; importcn.xxx.ResultCode; importcom.alibaba.excel.EasyExcel; importcom.alibaba.excel.annotation.ExcelProperty; importcom.alibaba.excel.enums.CellExtraTypeEnum; importcom.alibaba.excel.metadata.CellExtra; importorg.slf4j.Logger; importorg.slf4j.LoggerFactory; importorg.springframework.util.CollectionUtils; importorg.springframework.web.multipart.MultipartFile; importjava.io.IOException; importjava.lang.reflect.Field; importjava.util.List; /*** @author wangwei* @date 2020-12-01 13:34**/publicclassExcelAnalysisHelper<T> { privatestaticfinalLoggerLOGGER=LoggerFactory.getLogger(ExcelAnalysisHelper.class); publicList<T>getList(MultipartFilefile, Class<T>clazz) { returngetList(file, clazz, 0, 1); } publicList<T>getList(MultipartFilefile, Class<T>clazz, IntegersheetNo, IntegerheadRowNumber) { UploadDataListener<T>listener=newUploadDataListener<>(headRowNumber); try { EasyExcel.read(file.getInputStream(), clazz, listener).extraRead(CellExtraTypeEnum.MERGE).sheet(sheetNo).headRowNumber(headRowNumber).doRead(); } catch (IOExceptione) { LOGGER.error(e.getMessage()); } List<CellExtra>extraMergeInfoList=listener.getExtraMergeInfoList(); if (CollectionUtils.isEmpty(extraMergeInfoList)) { returnlistener.getData(); } List<T>data=explainMergeData(listener.getData(), extraMergeInfoList, headRowNumber); returndata; } /*** 处理合并单元格** @param data 解析数据* @param extraMergeInfoList 合并单元格信息* @param headRowNumber 起始行* @return 填充好的解析数据*/privateList<T>explainMergeData(List<T>data, List<CellExtra>extraMergeInfoList, IntegerheadRowNumber) { // 循环所有合并单元格信息extraMergeInfoList.forEach(cellExtra-> { intfirstRowIndex=cellExtra.getFirstRowIndex() -headRowNumber; intlastRowIndex=cellExtra.getLastRowIndex() -headRowNumber; intfirstColumnIndex=cellExtra.getFirstColumnIndex(); intlastColumnIndex=cellExtra.getLastColumnIndex(); // 获取初始值ObjectinitValue=getInitValueFromList(firstRowIndex, firstColumnIndex, data); // 设置值for (inti=firstRowIndex; i<=lastRowIndex; i++) { for (intj=firstColumnIndex; j<=lastColumnIndex; j++) { setInitValueToList(initValue, i, j, data); } } }); returndata; } /*** 设置合并单元格的值** @param filedValue 值* @param rowIndex 行* @param columnIndex 列* @param data 解析数据*/publicvoidsetInitValueToList(ObjectfiledValue, IntegerrowIndex, IntegercolumnIndex, List<T>data) { Tobject=data.get(rowIndex); for (Fieldfield : object.getClass().getDeclaredFields()) { //提升反射性能，关闭安全检查field.setAccessible(true); ExcelPropertyannotation=field.getAnnotation(ExcelProperty.class); if (annotation!=null) { if (annotation.index() ==columnIndex) { try { field.set(object, filedValue); break; } catch (IllegalAccessExceptione) { thrownewBizException(ResultCode.FAILURE, "解析数据时发生异常!"); } } } } } /*** 获取合并单元格的初始值* rowIndex对应list的索引* columnIndex对应实体内的字段** @param firstRowIndex 起始行* @param firstColumnIndex 起始列* @param data 列数据* @return 初始值*/privateObjectgetInitValueFromList(IntegerfirstRowIndex, IntegerfirstColumnIndex, List<T>data) { ObjectfiledValue=null; Tobject=data.get(firstRowIndex); for (Fieldfield : object.getClass().getDeclaredFields()) { //提升反射性能，关闭安全检查field.setAccessible(true); ExcelPropertyannotation=field.getAnnotation(ExcelProperty.class); if (annotation!=null) { if (annotation.index() ==firstColumnIndex) { try { filedValue=field.get(object); break; } catch (IllegalAccessExceptione) { thrownewBizException(ResultCode.FAILURE, "解析数据时发生异常!"); } } } } returnfiledValue; } }
注：easyexcel版本为2.2.6
<!--========================EasyExcel 配置============================--><dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>2.2.6</version></dependency><!--========================EasyExcel 配置结束========================-->
