EasyExcel 的使用

一、使用EasyExcel 读取excel数据并转换成对象

1、使用的maven依赖及版本：

<dependency>
   <groupId>com.alibaba</groupId>
   <artifactId>easyexcel</artifactId>
   <version>2.1.4</version>
</dependency>
<dependency>
   <groupId>org.apache.poi</groupId>
   <artifactId>poi</artifactId>
   <version>3.17</version>
</dependency>
<dependency>
   <groupId>org.apache.poi</groupId>
   <artifactId>poi-ooxml</artifactId>
   <version>3.17</version>
</dependency>
<dependency>
   <groupId>org.apache.poi</groupId>
   <artifactId>poi-scratchpad</artifactId>
   <version>3.17</version>
</dependency>

2、代码如下：将用到三个类，MyExcelListener.java，StudentInfo.java，ExcelUtil.java

StudentInfo.java

@Getter
@Setter 
public class StudentInfo implements Serializable {
  private static final long serialVersionUID = 7566235228027597205L;

  private String studnetName;

  private String gender;

  private String classes;

  private double score;
}

   ExcelListener.java

ExcelListener.java

  public static class ExcelListener extends AnalysisEventListener<Object> {

    private int headSize;     //头部大小，一般是指对象的总属性

    private List<Object> datas = new ArrayList<>();   // excel文档的内容存储
    /**
     * 逐行解析
     * object : 当前行的数据
     */
    @Override
    public void invoke(Object object, AnalysisContext context) {
      if (object != null) {
        datas.add(object);
      }
    }

    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
      super.invokeHeadMap(headMap, context);
      headSize = headMap.size();
    }

    public int getHeadSize() {
      return headSize;
    }

    public void setHeadSize(int headSize) {
      this.headSize = headSize;
    }

    /**
     * 解析完所有数据后会调用该方法
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
      //销毁不用的资源
    }

    public List<Object> getDatas() {
      return datas;
    }

    public void setDatas(List<Object> datas) {
      this.datas = datas;
    }
  }

3、ExcelUtil.java 包含两个主要方法 [1]读取excel的数据、[2]解析成对象

package com.violetg.conform.office.easyexcel;

import com.alibaba.excel.EasyExcelFactory;

import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import java.io.*;
import java.lang.reflect.Field;
import java.util.*;


public class ExcelUtil {

  private static ReadSheet initReadSheet;

  private static WriteSheet initWriteSheet;

  static {
    initReadSheet = new ReadSheet(1,"sheet");
    initWriteSheet = new WriteSheet();
    initWriteSheet.setSheetName("sheet");
    initWriteSheet.setSheetNo(1);
  }
 
  /*
   * 1、 读取excel
   * @param filePath
   * @param sheet
   * @return
   */

  public static List<Object> readRow(String filePath, ReadSheet sheet){
     if(!StringUtils.hasText(filePath)){
       return null;
     }
    sheet =  sheet != null ?sheet :initReadSheet;
    InputStream fileStream = null;
    ExcelListener excelListener = new ExcelListener();
    ExcelReader excelReader = null;
    try{
      fileStream = new FileInputStream(filePath);
      excelReader = EasyExcelFactory.read(fileStream, excelListener).autoTrim(true).doReadAll();
      return excelListener.getDatas();
    }catch (FileNotFoundException e) {
      e.printStackTrace();
    }finally {
      try{
        if(fileStream != null){
          fileStream.close();
        }
      } catch (IOException e) {
        e.printStackTrace();
      }
    }
    return null;
  }

/**
   * 2、转换成对象
   * @param classPath  映射模型的类的全路径
   * @param objectList excel内容
   * @return
   */
  private static JSONArray dataReflet(String classPath, List<Object> objectList) {
    JSONArray jsonArr = new JSONArray();
    try {
      Class<?> clazz = Class.forName(classPath);
      Field[] importField = clazz.getDeclaredFields();
      for (Object obj : objectList) {
        Map<Integer,String>  map = (LinkedHashMap)obj;
        String[] values = map.values().toArray(new String[0]);
        JSONObject jsonObj = new JSONObject();
        for (int i = 0; i <= values.length; i++) {
          Field field = importField[i];
          // 字段为 serialVersionUID时不读取
          if ("serialVersionUID".equals(field.getName())) {
            continue;
          }else{
            jsonObj.put(field.getName(),values[i-1]);
          }
        }
        jsonArr.add(jsonObj);
      }
    } catch (ClassNotFoundException e) {
      e.printStackTrace();
    }
    return jsonArr;
  }

 /**
   * 3) 测试
   * 
   */
  public static void main(String[] args) {
    // 数据导入
    String filePath = "D:\\software\\study\\data\\ExcelImport.xlsx";
    readRow(filePath,null);
    List<Object> objectList = ExcelUtil.readRow(filePath, null);
    String classPath = "com.violetg.conform.office.easyexcel.StudentInfo";
    JSONArray jsonArr = dataReflet(classPath, objectList);
    List<StudentInfo> dtoList = new ArrayList<>();
    dtoList = JSONArray.parseArray(jsonArr.toJSONString(), StudentInfo.class);
    System.out.println(dtoList.toString());
  }

测试结果如下所示：

[{"classes":"1","gender":"男","score":0.0,"studnetName":"张三"}, {"classes":"2","score":89.12,"studnetName":"李四"}, {"classes":"1","gender":"男","score":96.14,"studnetName":"王麻子"}, {"classes":"1","gender":"女","score":94.15,"studnetName":"孙小"}]

本文地址：https://blog.csdn.net/seragxx/article/details/107683851