easyexcel读取excel合并单元格数据 |
您所在的位置：网站首页 › wxcel合并表格 › easyexcel读取excel合并单元格数据 |
easyexcel读取excel合并单元格数据
|
普通的excel列表，easyexcel读取是没有什么问题的。但是，如果有合并单元格，那么它读取的时候，能获取数据，但是数据是不完整的。如下所示的单元格数据：
我们通过简单的异步读取，最后查看数据内容： ExcelData.java package com.example.model; import com.alibaba.excel.annotation.ExcelProperty; import lombok.AllArgsConstructor; import lombok.Data; import lombok.NoArgsConstructor; @Data @AllArgsConstructor @NoArgsConstructor public class ExcelData { @ExcelProperty("学生姓名") private String name; @ExcelProperty("年龄") private int age; @ExcelProperty("性别") private String gender; @ExcelProperty({"课程", "课程名称"}) private String courseName; @ExcelProperty({"课程", "分数"}) private double score; }ExcelRead.java package com.example.service; import com.alibaba.excel.EasyExcel; import com.alibaba.excel.context.AnalysisContext; import com.alibaba.excel.event.AnalysisEventListener; import com.example.model.ExcelData; import lombok.extern.slf4j.Slf4j; import java.util.ArrayList; import java.util.List; @Slf4j public class ExcelRead { private static final String FILEPATH = "e:\\test\\student.xlsx"; public List list() { List excelDataList = new ArrayList(); EasyExcel.read(FILEPATH, ExcelData.class, new AnalysisEventListener() { @Override public void invoke(ExcelData excelData, AnalysisContext analysisContext) { log.info("read data {}", excelData); excelDataList.add(excelData); } @Override public void doAfterAllAnalysed(AnalysisContext analysisContext) { } }).sheet().doRead(); return excelDataList; } }ExcelTest.java package com.example.service; import com.example.model.ExcelData; import java.util.List; public class ExcelTest { public static void main(String[] args) { ExcelRead excelRead = new ExcelRead(); List list = excelRead.list(); System.out.println(list.size()); } }运行程序，打印日志信息如下：
获取了6个数据没错，但是每一个合并单元格记录里面都有一个数据获取是空的。 解决办法就是需要在异步读取数据监听器里面读取合并单元格的额外数据，并把这部分数据给补充上。 需要修改的地方： 1、实体需要增加注解索引值： @Data @AllArgsConstructor @NoArgsConstructor public class ExcelData { @ExcelProperty(value = "学生姓名", index = 0) private String name; @ExcelProperty(value = "年龄", index = 1) private int age; @ExcelProperty(value = "性别", index = 2) private String gender; @ExcelProperty(value = {"课程", "课程名称"}, index = 3) private String courseName; @ExcelProperty(value = {"课程", "分数"}, index = 4) private double score; }2、自定义监听器，读取合并单元格数据： package com.example.service; import com.alibaba.excel.context.AnalysisContext; import com.alibaba.excel.enums.CellExtraTypeEnum; import com.alibaba.excel.event.AnalysisEventListener; import com.alibaba.excel.metadata.CellExtra; import com.example.model.ExcelData; import lombok.extern.slf4j.Slf4j; import java.util.ArrayList; import java.util.List; @Slf4j public class CustomAnalysisEventListener extends AnalysisEventListener { private int headRowNum; public CustomAnalysisEventListener(int headRowNum) { this.headRowNum = headRowNum; } private List list = new ArrayList(); private List cellExtraList = new ArrayList(); @Override public void invoke(ExcelData excelData, AnalysisContext analysisContext) { log.info(" data -> {}", excelData); list.add(excelData); } @Override public void extra(CellExtra extra, AnalysisContext context) { CellExtraTypeEnum type = extra.getType(); switch (type) { case MERGE: { if (extra.getRowIndex() >= headRowNum) { cellExtraList.add(extra); } break; } default:{ } } } @Override public void doAfterAllAnalysed(AnalysisContext analysisContext) { } public List getList() { return list; } public List getCellExtraList() { return cellExtraList; } }3、通过监听器读取数据，通过监听器获取数据和合并单元格数据，然后设置单元格数据。 package com.example.service; import com.alibaba.excel.EasyExcel; import com.alibaba.excel.annotation.ExcelProperty; import com.alibaba.excel.enums.CellExtraTypeEnum; import com.alibaba.excel.metadata.CellExtra; import com.example.model.ExcelData; import lombok.extern.slf4j.Slf4j; import java.lang.reflect.Field; import java.util.List; @Slf4j public class ExcelRead { private static final int HEAD_ROW_NUM = 2; private static final String FILEPATH = "e:\\test\\student.xlsx"; public List list() { List excelDataList; CustomAnalysisEventListener listener = new CustomAnalysisEventListener(HEAD_ROW_NUM); EasyExcel.read(FILEPATH, ExcelData.class, listener).extraRead(CellExtraTypeEnum.MERGE).sheet().doRead(); excelDataList = listener.getList(); List cellExtraList = listener.getCellExtraList(); if (cellExtraList != null && cellExtraList.size() > 0) { mergeExcelData(excelDataList, cellExtraList, HEAD_ROW_NUM); } return excelDataList; } private void mergeExcelData(List excelDataList, List cellExtraList, int headRowNum) { cellExtraList.forEach(cellExtra -> { int firstRowIndex = cellExtra.getFirstRowIndex() - headRowNum; int lastRowIndex = cellExtra.getLastRowIndex() - headRowNum; int firstColumnIndex = cellExtra.getFirstColumnIndex(); int lastColumnIndex = cellExtra.getLastColumnIndex(); //获取初始值 Object initValue = getInitValueFromList(firstRowIndex, firstColumnIndex, excelDataList); //设置值 for (int i = firstRowIndex; i |
【本文地址】
今日新闻 |
推荐新闻 |