EasyExcel解析包含单元格合并的表格

EasyExcel解析包含单元格合并的表格

1，引入依赖

com.alibaba easyexcel 2.2.7

2，实现代码
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.enums.CellExtraTypeEnum;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.exception.ExcelDataConvertException;
import com.alibaba.excel.metadata.CellExtra;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.FileNotFoundException;
import java.util.*;

/**

• @Description 注意：空白的单元格不会被读取

• @Author dsf

• @Date 2020/12/14 15:30

• @Version V1.0
  **/
  public class NoModelDataListener extends AnalysisEventListener<Map<Integer, String>> {

  private static final Logger log = LoggerFactory.getLogger(UploadDataListener.class);
  /**

  • 所有列到字段名的映射
  • /
    private Map<Integer,String> index2FieldMap ;
    /*
  • 解析出的数据
    /
    private LinkedHashMap<Integer,Map<Integer, String>> row2DataMap = new LinkedHashMap<>();
    /*
  • 合并单元格
    */
    private List extraMergeInfoList = new ArrayList<>();

  public NoModelDataListener(){}

  public NoModelDataListener(Map<Integer,String> index2FieldMap){
  this.index2FieldMap = index2FieldMap ;
  }

  @Override
  public void invoke(Map<Integer, String> rowData, AnalysisContext analysisContext) {
  //读取到的每行数据,其key是以0开始的索引
  row2DataMap.put(analysisContext.readRowHolder().getRowIndex(),rowData) ;
  }

  @Override
  public void doAfterAllAnalysed(AnalysisContext analysisContext) {
  //所有行都解析完成
  this.explainMergeData(row2DataMap, extraMergeInfoList) ;
  }

  /**

  • 某行的数据解析失败
  • */
    @Override
    public void onException(Exception exception, AnalysisContext context) {
    System.err.println("解析失败，但是继续解析下一行: " + exception.getMessage());
    // 如果是某一个单元格的转换异常 能获取到具体行号
    if (exception instanceof ExcelDataConvertException) {
    ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException)exception;
    System.err.println(“第{}行，第{}列解析异常” + excelDataConvertException.getRowIndex() +
    excelDataConvertException.getColumnIndex());
    }
    }

  @Override
  public void extra(CellExtra extra, AnalysisContext context) {
  switch (extra.getType()){
  case MERGE:{
  extraMergeInfoList.add(extra);
  break;
  }
  case HYPERLINK:{
  break;
  }
  case COMMENT:{
  }
  default: {
  }
  }
  }

  /**

  • 处理合并单元格

  • @param data 解析数据

  • @param extraMergeInfoList 合并单元格信息

  • @return 填充好的解析数据
    */
    private void explainMergeData(Map<Integer,Map<Integer, String>> data, List extraMergeInfoList) {
    //循环所有合并单元格信息
    extraMergeInfoList.forEach(cellExtra -> {
    int firstRowIndex = cellExtra.getFirstRowIndex();
    int lastRowIndex = cellExtra.getLastRowIndex();
    int firstColumnIndex = cellExtra.getFirstColumnIndex();
    int lastColumnIndex = cellExtra.getLastColumnIndex();

     Map<Integer,String> rdata = data.get(cellExtra.getRowIndex()) ;
     String val = null ;
     if(rdata != null){
         val = rdata.get(cellExtra.getColumnIndex()) ;
     }
     //遍历每行
     Map<Integer, String> rowData = null ;
     for(int i=firstRowIndex;i<=lastRowIndex;i++){
         rowData = data.get(i) ;
         if(rowData == null){
             continue;
         }
         for(int c = firstColumnIndex;c<=lastColumnIndex;c++){
             rowData.put(c,val) ;
         }
     }
    

    });
    }

  public static void main(String[] args) throws FileNotFoundException {
  //读取的表格名
  String fileName = “C:\Users\dsf\Desktop\解析的\2020年2季度电子渠道新业务开发需求-评估数据（数据导入模板）.xlsx”;
  String fileName2 = “C:\Users\dsf\Desktop\解析的\非COSMIC评估工作量汇总模板.xlsx” ;
  String fileName3 = “C:\Users\dsf\Desktop\解析的\test.xlsx” ;
  EasyExcel.read(fileName3,new NoModelDataListener())
  .extraRead(CellExtraTypeEnum.MERGE) // 需要读取合并单元格信息 默认不读取
  //.registerConverter(new EmptyConverter()) //默认：DefaultConverterLoader#loadDefaultReadConverter()
  .ignoreEmptyRow(true)
  .autoTrim(true)
  .headRowNumber(1)
  .autoCloseStream(true)
  //.sheet(“2、功能点拆分表”)
  //.sheet(“4、结果计算”)
  .sheet()
  .doRead();

  }
  }

参考链接：https://blog.51cto.com/dengshuangfu/2566775