easyexcel处理合并单元格数据
easyexcel处理excel合并单元格,将所有合并单元格填充对应数据。
·
一、背景
一次工作任务是要解析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;
}
@Override
public void invoke(Map<Integer, String> data, AnalysisContext context) {
log.info("解析到一条数据:{}", JSON.toJSONString(data));
dataList.add(data);
}
/**
* 读取额外信息:合并单元格
*/
@Override
public 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;
}
}
@Override
public 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;
}
三、结语
万般皆下品,唯有技术和远方。。。愿君有所收获。。。
更多推荐
已为社区贡献1条内容
所有评论(0)