一、背景

一次工作任務是要解析excel數據，采用阿里的 easyexcel 工具進行解析，由于表格有合并單元格，但是 easyexcel 讀取合并單元格只會讀取一次，導致下面單元格數據為空，這樣會影響有層級數據的識別，所以需要將合并單元格都填充上數據。由于我的excel列數是變化的，所以無法采用對象映射，獲取出來的數據也是 List<Map<Integer, String>> 類型，廢話不多說請看下面。

二、解決

2.0 先看一下處理結果

解析到一條數據:{"0":"*應用名稱","1":"應用編碼","2":"*一級菜單","3":"菜單編碼","4":"*二級菜單","5":"菜單編碼","6":"*功能菜單","7":"菜單編碼","8":"功能角色1","9":"功能角色2","10":"功能角色3"} 解析到一條數據:{"0":"測試應用11","6":"全量","8":"√","10":"√"} 解析到一條數據:{"0":"測試應用22","2":"一級菜單1","4":"二級菜單1","6":"功能菜單1","8":"√","10":"√"} 解析到一條數據:{"6":"功能菜單2","9":"√","10":"√"} 解析到一條數據:{"6":"功能菜單3","10":"√"} 解析到一條數據:{"4":"二級菜單2","6":"功能菜單4","8":"√","10":"√"} 解析到一條數據:{"6":"功能菜單5","9":"√","10":"√"} 解析到一條數據:{"2":"一級菜單2","4":"二級菜單3","6":"功能菜單6","8":"√","10":"√"} 解析到一條數據:{"6":"功能菜單7","9":"√","10":"√"} 解析到一條數據:{"6":"功能菜單8","9":"√","10":"√"} 解析到一條數據:{"0":"測試應用33","2":"一級菜單3","6":"全量","8":"√","10":"√"} 解析到一條數據:{"2":"一級菜單4","6":"全量","10":"√"} 解析到一條數據:{"2":"一級菜單5","6":"全量","9":"√","10":"√"} 解析到一條數據:{"2":"一級菜單6","6":"全量","9":"√","10":"√"}處理后數據:{"0":"*應用名稱","1":"應用編碼","2":"*一級菜單","3":"菜單編碼","4":"*二級菜單","5":"菜單編碼","6":"*功能菜單","7":"菜單編碼","8":"功能角色1","9":"功能角色2","10":"功能角色3"} 處理后數據:{"0":"測試應用11","6":"全量","8":"√","10":"√"} 處理后數據:{"0":"測試應用22","2":"一級菜單1","4":"二級菜單1","6":"功能菜單1","8":"√","10":"√"} 處理后數據:{"0":"測試應用22","2":"一級菜單1","4":"二級菜單1","6":"功能菜單2","9":"√","10":"√"} 處理后數據:{"0":"測試應用22","2":"一級菜單1","4":"二級菜單1","6":"功能菜單3","10":"√"} 處理后數據:{"0":"測試應用22","2":"一級菜單1","4":"二級菜單2","6":"功能菜單4","8":"√","10":"√"} 處理后數據:{"0":"測試應用22","2":"一級菜單1","4":"二級菜單2","6":"功能菜單5","9":"√","10":"√"} 處理后數據:{"0":"測試應用22","2":"一級菜單2","4":"二級菜單3","6":"功能菜單6","8":"√","10":"√"} 處理后數據:{"0":"測試應用22","2":"一級菜單2","4":"二級菜單3","6":"功能菜單7","9":"√","10":"√"} 處理后數據:{"0":"測試應用22","2":"一級菜單2","4":"二級菜單3","6":"功能菜單8","9":"√","10":"√"} 處理后數據:{"0":"測試應用33","2":"一級菜單3","6":"全量","8":"√","10":"√"} 處理后數據:{"0":"測試應用33","2":"一級菜單4","6":"全量","10":"√"} 處理后數據:{"0":"測試應用33","2":"一級菜單5","6":"全量","9":"√","10":"√"} 處理后數據:{"0":"測試應用33","2":"一級菜單6","6":"全量","9":"√","10":"√"}

2.1 引入easyexcel依賴

<dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>3.0.5</version> </dependency>

2.1 創建EasyExcelListener類

@Slf4j public class EasyExcelListener extends AnalysisEventListener<Map<Integer, String>> {/*** 數據*/List<Map<Integer, String>> dataList = new ArrayList<>();/*** 正文起始行*/private Integer headRowNumber;/*** 合并單元格*/private List<CellExtra> extraMergeInfoList = new ArrayList<>();public EasyExcelListener(Integer headRowNumber) {this.headRowNumber = headRowNumber;}@Overridepublic void invoke(Map<Integer, String> data, AnalysisContext context) {log.info("解析到一條數據:{}", JSON.toJSONString(data));dataList.add(data);}/*** 讀取額外信息：合并單元格*/@Overridepublic void extra(CellExtra extra, AnalysisContext context) {log.info("讀取到了一條額外信息:{}", JSON.toJSONString(extra));switch (extra.getType()) {case MERGE: {if (extra.getRowIndex() >= headRowNumber) {extraMergeInfoList.add(extra);}break;}default: break;}}@Overridepublic void doAfterAllAnalysed(AnalysisContext analysisContext) {log.info("所有數據解析完成！");}/*** 返回解析出來的List*/public List<Map<Integer, String>> getData() {return dataList;}/*** 返回解析出來的合并單元格List*/public List<CellExtra> getExtraMergeInfoList() {return extraMergeInfoList;} }

2.2 創建EasyExcelHelper類

@Slf4j public class EasyExcelHelper<T> {/*** 返回解析后的List** @param: fileName 文件名* @param: clazz Excel對應屬性名* @param: sheetNo 要解析的sheet* @param: headRowNumber 正文起始行* @return java.util.List<T> 解析后的List*/public List<Map<Integer, String>> getList(InputStream inputStream, Integer sheetNo, Integer headRowNumber) {EasyExcelListener listener = new EasyExcelListener(headRowNumber);try {EasyExcelFactory.read(inputStream, listener).excelType(ExcelTypeEnum.XLSX).extraRead(CellExtraTypeEnum.MERGE).sheet(sheetNo).headRowNumber(headRowNumber).doRead();} catch (Exception e) {log.error(e.getMessage());}List<CellExtra> extraMergeInfoList = listener.getExtraMergeInfoList();if (CollectionUtils.isEmpty(extraMergeInfoList)) {return listener.getData();}List<Map<Integer, String>> data = explainMergeData(listener.getData(), extraMergeInfoList, headRowNumber);return data;}/*** 處理合并單元格** @param data 解析數據* @param extraMergeInfoList 合并單元格信息* @param headRowNumber 起始行* @return 填充好的解析數據*/private List<Map<Integer, String>> explainMergeData(List<Map<Integer, String>> data, List<CellExtra> extraMergeInfoList, Integer headRowNumber) {//循環所有合并單元格信息extraMergeInfoList.forEach(cellExtra -> {int firstRowIndex = cellExtra.getFirstRowIndex() - headRowNumber;int firstColumnIndex = cellExtra.getFirstColumnIndex();int lastRowIndex = cellExtra.getLastRowIndex() - headRowNumber;int lastColumnIndex = cellExtra.getLastColumnIndex();// 獲取初始值String 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(String filedValue, Integer rowIndex, Integer columnIndex, List<Map<Integer, String>> data) {Map<Integer, String> object = data.get(rowIndex);object.put(columnIndex, String.valueOf(filedValue));}/*** 獲取合并單元格的初始值* rowIndex對應list的索引* columnIndex對應實體內的字段** @param firstRowIndex 起始行* @param firstColumnIndex 起始列* @param data 列數據* @return 初始值*/private String getInitValueFromList(Integer firstRowIndex, Integer firstColumnIndex, List<Map<Integer, String>> data) {String filedValue = null;Map<Integer, String> object = data.get(firstRowIndex);for (Map.Entry<Integer, String> entry : object.entrySet()) {if (entry.getKey().equals(firstColumnIndex)) {filedValue = entry.getValue();break;}}return filedValue;} }

2.3 測試使用

@PostMapping(value = "/upload", consumes = MediaType.MULTIPART_FORM_DATA_VALUE) public Boolean uploadAuthTemplate(@RequestPart("file") MultipartFile file) {EasyExcelHelper<Map<Integer, String>> helper = new EasyExcelHelper<>();// 參數 文件流，sheet頁號，頭行號List<Map<Integer, String>> listData = helper.getList(file.getInputStream(), 0, 1);log.info(JSON.toJSONString(listData));return true; }

三、結語

萬般皆下品，唯有技術和遠方。。。愿君有所收獲。。。

總結

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

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