SpringBoot集成EasyExcel

EasyExcel是阿里巴巴开源poi插件之一，主要解决了poi框架使用复杂，sax解析模式不容易操作，数据量大起来容易OOM，解决了POI并发造成的报错。主要解决方式：通过解压文件的方式加载，一行一行的加载，并且抛弃样式字体等不重要的数据，降低内存的占用.

文档地址 https://alibaba-easyexcel.github.io/
GitHub：https://github.com/alibaba/easy

快速使用

总的流程分四步：

引入maven依赖
配置读取文件监听
写文件测试
读文件测试

（1）引入maven依赖

    <!--jar 包依赖管理-->
    <dependencies>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.7</version>
        </dependency>
        <!--fastjson-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.75</version>
        </dependency>
    </dependencies>

（2）配置读取文件监听

/**
 * @author ：wxw.
 * @date ： 13:35 2020/12/30
 * @description： 模板的读取类
 *  有个很重要的点 DemoDataListener 不能被spring管理，
 * 要每次读取excel都要new,然后里面用到spring可以构造方法传进去
 * @link:
 * @version: v_0.0.1
 */
public class DemoDataListener extends AnalysisEventListener {
 
    private static final Logger LOGGER = LoggerFactory.getLogger(DemoDataListener.class);
    /**
     * 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 5;
 
    List<Object> list = new ArrayList<Object>();
    /**
     * 假设这个是一个DAO，当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。
     */
    private DemoDAO demoDAO;
 
    public DemoDataListener() {
        // 这里是demo，所以随便new一个。实际使用如果到了spring,请使用下面的有参构造函数
        demoDAO = new DemoDAO();
    }
 
    /**
     * 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
     *
     * @param demoDAO
     */
    public DemoDataListener(DemoDAO demoDAO) {
        this.demoDAO = demoDAO;
    }
 
    /**
     * 这个每一条数据解析都会来调用
     *
     * @param data
     *            one row value. Is is same as {@link AnalysisContext#readRowHolder()}
     * @param context
     */
    @Override
    public void invoke(Object data, AnalysisContext context) {
        LOGGER.info("解析到一条数据:{}", JSON.toJSONString(data));
        list.add(data);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (list.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理 list
            list.clear();
        }
    }
 
 
    /**
     * 所有数据解析完成了 都会来调用
     *
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        saveData();
        LOGGER.info("所有数据解析完成！");
    }
 
    /**
     * 加上存储数据库
     */
    private void saveData() {
        LOGGER.info("{}条数据，开始存储数据库！", list.size());
        demoDAO.save(list);
        LOGGER.info("存储数据库成功！");
    }
}

（3）写文件测试

     @Test
    public void simpleWrite() {
        // 写法1
        String fileName = TestFileUtil.getPath() + "simpleWrite" + System.currentTimeMillis() + ".xlsx";
        // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        // 如果这里想使用03 则 传入excelType参数即可
        EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(data());
 
        // 写法2
        fileName = TestFileUtil.getPath() + "simpleWrite" + System.currentTimeMillis() + ".xlsx";
        // 这里 需要指定写用哪个class去写
        ExcelWriter excelWriter = null;
        try {
            excelWriter = EasyExcel.write(fileName, DemoData.class).build();
            WriteSheet writeSheet = EasyExcel.writerSheet("模板").build();
            excelWriter.write(data(), writeSheet);
        } finally {
            // 千万别忘记finish 会帮忙关闭流
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }
    }

输出路径：

注意

我们最开始操作文件，可能是这样用File file = new File("c:/test.txt")，这样就会有一个硬编码的问题，这样不利于我们日后项目迁移，项目位置变了或者windows切换部署到Linux系统的话，路径也要改，很难一次成型，所以这里我们使用 Java中getResource()的用法，

使用相对路径的形式，解决绝了硬编码问题，达到了一次成型的目标。

（3）读文件测试

     /**
     * 不创建对象的读
     */
    @Test
    public void noModelRead() {
        String fileName = TestFileUtil.getPath() + File.separator+"simpleWrite1609308032245.xlsx";
        // 这里 只要，然后读取第一个sheet 同步读取会自动finish
        EasyExcel.read(fileName, new NoModelDataListener()).sheet().doRead();
    }

基础读案例
操作的excel

实体类
@ExcelProperty注解可以使用下标和名称的方式来指定读取的列

@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class EmployeesEntity {
@ExcelProperty(index = 0)
private Integer no; //工号
@ExcelProperty(index = 1)
private String name;
@ExcelProperty(index = 2)
private Double fund;
@ExcelProperty(index = 3)
private Double postSalary;
@ExcelProperty(index = 4)
private Double performanceOf;
@ExcelProperty(index = 5)
private Double allWork;
@ExcelProperty(index = 6)
private Double violations;
@ExcelProperty(index = 7)
private Double traffic;
@ExcelProperty(index = 8)
private Double communication;
}

读取监听器
一般是异步读取,可以指定同步读取数据(看文档)

public class EmployeesListener extends AnalysisEventListener<EmployeesEntity> {

/**
* 这个每一条数据解析都会来调用
*
* @param data
* one row value. Is is same as {@link AnalysisContext#readRowHolder()}
* @param context
*/
@Override
public void invoke(EmployeesEntity data, AnalysisContext context) {
System.out.println("解析到一条数据:"+JSON.toJSONString(data));
}

/**
* 所有数据解析完成了都会来调用
*
* @param context
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {

}

}

测试
@Test
public void get(){
File file = new File("./src/main/resources/大客户部-薪酬表.xlsx");
String absolutePath = file.getAbsolutePath();
// // 这里需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
EasyExcel.read(absolutePath, EmployeesEntity.class, new EmployeesListener()).sheet().doRead();

}

基础写案例
写可以指定Sheet进行写,还可以指定列进行写,还可以写入图片,简单的合并单元格…下面教程默认写入第一个Sheet中

实体类
@ExcelProperty("字符串标题") 列的标题

@Data
public class DemoData {
@ExcelProperty("字符串标题")
private String string;
@ExcelProperty("日期标题")
private Date date;
@ExcelProperty("数字标题")
private Double doubleData;
/**
* 忽略这个字段
*/
@ExcelIgnore
private String ignore;
}

测试
//生成模拟数据
private List<DemoData> data() {
List<DemoData> list = new ArrayList<DemoData>();
for (int i = 0; i < 10; i++) {
DemoData data = new DemoData();
data.setString("字符串" + i);
data.setDate(new Date());
data.setDoubleData(0.56);
list.add(data);
}
return list;
}

/**
* 最简单的写
* <p>1. 创建excel对应的实体对象参照{@link DemoData}
* <p>2. 直接写即可
*/
@Test
public void simpleWrite() {
// 写
File file = new File("./src/main/resources/DemoData.xlsx");
// 这里需要指定写用哪个class去写，然后写到第一个sheet，名字为模板然后文件流会自动关闭
// 如果这里想使用03 则传入excelType参数即可
EasyExcel.write(file.getAbsolutePath(), DemoData.class).sheet("DemoData").doWrite(data());

}

------------------------------------------------------

2. 对读取excel内容（批量添加）

@PostMapping("plUpdate")
  public R plUpdate(@RequestParam("filename") MultipartFile file) throws IOException {
    //String Originalfilename = file.getOriginalFilename();
    // String fileName = file.getName();
   // System.out.println("orname="+Originalfilename+";"+"filename"+file.getName());
 
    // 获取文件全名
    String fileName = file.getOriginalFilename();
     //设置文件路径
    String templatePath = "G:/excel/";
    File dest0 = new File(templatePath);
    File dest = new File(dest0, fileName);
    //文件上传-覆盖
    try {
      // 检测是否存在目录
      if (!dest0.getParentFile().exists()) {
        dest0.getParentFile().mkdirs();
        //检测文件是否存在
      }
      if (!dest.exists()) {
        dest.mkdirs();
      }
      file.transferTo(dest);
    } catch (Exception e) {
      return R.error();
    }
 
    String finameUrl = templatePath+fileName;
    ExcelReader excelReader = null;
    try {
    //TeacherExcel.class对应的是和模板一样的实体类，
    //eduTeacherService对应持久层的接口
      excelReader = EasyExcel.read(finameUrl, TeacherExcel.class, new DemoDataListener(eduTeacherService)).build();
      ReadSheet readSheet = EasyExcel.readSheet(0).build();
      excelReader.read(readSheet);
    } finally {
 
      if (excelReader != null) {
        // 这里千万别忘记关闭，读的时候会创建临时文件，到时磁盘会崩的
        excelReader.finish();
      }
    }
    return R.ok();
  }

创建一个监听类：

package com.atguigu.excel;
 
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.atguigu.eduservice.entity.EduTeacher;
import com.atguigu.eduservice.entity.vo.TeacherExcel;
import com.atguigu.eduservice.service.EduTeacherService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.util.ArrayList;
import java.util.List;
 
// 有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
public class DemoDataListener extends AnalysisEventListener<TeacherExcel> {
  private static final Logger LOGGER = LoggerFactory.getLogger(DemoDataListener.class);
  //这里写持久层的类
  private EduTeacherService eduTeacherService;
   
  /**
 
   * 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
 
   *
 
   * @param eduTeacherService
 
   */
 
  public DemoDataListener( EduTeacherService eduTeacherService) {
        //进行持久层的类
    this.eduTeacherService = eduTeacherService;
 
  }
   
 
  /**
   * 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
   */
  private static final int BATCH_COUNT = 5;
  List<TeacherExcel> list = new ArrayList<TeacherExcel>();
  /**
   * 假设这个是一个DAO，当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。
   */
  //private DemoDAO demoDAO;
  public DemoDataListener() {
    // 这里是demo，所以随便new一个。实际使用如果到了spring,请使用下面的有参构造函数
    //demoDAO = new DemoDAO();
  }
  /**
   * 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
   *
   * @param demoDAO
   */
  /* public DemoDataListener(DemoDAO demoDAO) {
    this.demoDAO = demoDAO;
  }*/
  /**
   * 这个每一条数据解析都会来调用
   *
   * @param data
   *      one row value. Is is same as {@link AnalysisContext#readRowHolder()}
   * @param context
   */
  @Override
  public void invoke(TeacherExcel data, AnalysisContext context) {
    LOGGER.info("解析到一条数据:{}",data.toString());
    list.add(data);
    // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
 
  }
  /**
   * 所有数据解析完成了 都会来调用
   *
   * @param context
   */
  @Override
  public void doAfterAllAnalysed(AnalysisContext context) {
    // 这里也要保存数据，确保最后遗留的数据也存储到数据库
    saveData();
    LOGGER.info("所有数据解析完成！");
  }
  /**
   * 加上存储数据库
   */
  private void saveData() {
    LOGGER.info("{}条数据，开始存储数据库！", list.size());
    //因为我excel模板的实体和插入数据库实体的类不一样，所以需要进行转化
 
    for (int i =0;i<list.size();i++ ){
      EduTeacher teacher = new EduTeacher();
      teacher.setLevel(list.get(i).getLevel());
      teacher.setCareer(list.get(i).getCareer());
      teacher.setName(list.get(i).getName());
      teacher.setSort(list.get(i).getSort());
      teacher.setIntro(list.get(i).getIntro());
      boolean save = eduTeacherService.save(teacher);
      if (save){
        System.out.println("第"+i+"添加成功");
      }
    }
  } 
}

例如我的excel模板是：

实体类：

字段上ExcelProperty的注解可以使用index声明字段在模板中的顺序，使用value声明模板各个字段的名称。
例如：

@ExcelProperty(value = “讲师简介”,index = 1)
private String intro;

在这里插入图片描述

模板的实体类要和excel字段一样对应，不然会出错

效果：

在这里插入图片描述

3. 模板下载：

创建一个工具类：

package com.atguigu.eduservice.config;
 
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
 
import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;
 
/**
 * @author linjiazeng
 * @version 1.0
 * @date 2020/12/28 22:29
 **/
public class ExcelUtil {
 
  /**
   * 导出
   * @param response
   * @param data
   * @param fileName
   * @param sheetName
   * @param clazz
   * @throws Exception
   */
  public static void writeExcel(HttpServletResponse response, List<? extends Object> data, String fileName, String sheetName, Class clazz) throws Exception {
    //表头样式
    WriteCellStyle headWriteCellStyle = new WriteCellStyle();
    //设置表头居中对齐
    headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
    //内容样式
    WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
    //设置内容靠左对齐
    contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
    HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    EasyExcel.write(getOutputStream(fileName, response), clazz).excelType(ExcelTypeEnum.XLSX).sheet(sheetName).registerWriteHandler(horizontalCellStyleStrategy).doWrite(data);
  }
  private static OutputStream getOutputStream(String fileName, HttpServletResponse response) throws Exception {
    fileName = URLEncoder.encode(fileName, "UTF-8");
    response.setContentType("application/vnd.ms-excel");
    response.setCharacterEncoding("utf8");
    response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
    response.addHeader("Access-Control-Expose-Headers", "Content-disposition");
 
    return response.getOutputStream();
  }
}

调用工具类下载模板

/*下载模板*/
 @GetMapping("/download/template")
 public void downloadTemplate(HttpServletResponse response){
   String fileName = "导入讲师模板";
   String sheetName="导入讲师模板";
   List<TeacherExcel> teacherExcelList = new ArrayList<>();
   TeacherExcel teacherExcel = new TeacherExcel();
   teacherExcel.setName("ljz");
   teacherExcel.setIntro("清华毕业，高材生");
   teacherExcel.setCareer("资深讲师");
   teacherExcel.setSort(1);
   teacherExcel.setLevel(1);
   teacherExcelList.add(teacherExcel);
   try {
     //TeacherExcel.class对应你的模板类
     //teacherExcelList模板的例子
     //也可以使用这种方式导出你查询出数据excel文件
     ExcelUtil.writeExcel(response,teacherExcelList,fileName,sheetName,TeacherExcel.class);
   } catch (Exception e) {
     System.out.println(e.getCause());
 
   }
 }

效果：

在这里插入图片描述

------------------------------------------------------

1、依赖

<!-- 阿里EasyExcel start -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.1.7</version>
</dependency>

2、写入Excel

2.1 实体

@Data
public class Student {

    //学号
    @ExcelProperty("学号")
    private Integer id;

    // 姓名
    @ExcelProperty("姓名")
    private String name;

    // 年龄
    @ExcelProperty("年龄")
    private Integer age;

    // 班级
    @ExcelProperty("班级")
    private String classRoom;

    // 性别
    @ExcelProperty("性别")
    private String sex;

    // 院校
    @ExcelProperty("院校")
    private String graduate;

    // 毕业时间
    @DateTimeFormat("yyyy年MM月dd日HH时mm分ss秒")
    @ExcelProperty("毕业时间")
    private Date graduateTime;

    // 手机号
    @ExcelProperty("手机号")
    private String phone;
    
    //@NumberFormat("#.#") //保留1位小数
    //@ExcelProperty(value = "薪资", index = 3) //设置表图和指定的列, 将工资放在 第四列
    //@ExcelIgnore  忽略字段,比如密码
    
}

2.2 实体的数据监听器

@Slf4j
public class StudentDataListener extends AnalysisEventListener<Student> {

    /**
     * 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 5; //实际操作的时候看情况修改
    List<Student> list = new ArrayList<>();

    /**
     * 这个每一条数据解析都会来调用
     *
     * @param data
     *            one row value. Is is same as {@link AnalysisContext#readRowHolder()}
     * @param context
     */
    @Override
    public void invoke(Student data, AnalysisContext context) {
        log.info("解析到一条数据:{}", data);
        list.add(data);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (list.size() >= BATCH_COUNT) {
            log.info("存数据库");

            // 批量插入
            // 存储完成清理 list
            list.clear();
        }
    }

    /**
     * 所有数据解析完成了 都会来调用
     *
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        log.info("所有数据解析完成！");
    }

    //为了方便获取读取的数据
    public List<Student> getList() {
        return list;
    }
}

2.3 03版本的Excel写入

@Test
public void testExcel03(){

    //要写入的文件的路径
    String fileName = "D:\\KEGONGCHANG\\DaiMa\\IDEA\\KH96\\SpringBoot\\SpringBoot\\springboot-03-asyztimer\\excel\\excel03_student.xls";

    // 如果这里想使用03 则 传入excelType参数即可
    //获取学生信息，实际应用中从数据库中查询出来即可，list集合

    Student student =   Student.builder().id(19).name("huayu").age(19)
        .classRoom("KH96").sex("男").graduate("金科")
        .graduateTime(new Date()).phone("13501020304").build();
    List<Student> studentList = new ArrayList<>();
    studentList.add(student);

    //将数据写入文件
    EasyExcel.write(fileName, Student.class)
        	 .excelType(ExcelTypeEnum.XLS)
        	 .sheet("学生信息")
        	 .doWrite(studentList);

    log.info("学生信息写入完成！！！{}",student);

}

测试结果：

2.4 07版本的Excel 写入

@Test
public void testExcel07(){

   //要写入的文件的路径
   String fileName = "D:\\KEGONGCHANG\\DaiMa\\IDEA\\KH96\\SpringBoot\\SpringBoot\\springboot-03-asyztimer\\excel\\excel07_student.xls";


   //获取学生信息
    studentList.add(student);

     //07版写入文件
    EasyExcel.write(fileName, Student.class)
        	 .sheet("学生信息")
        	 .doWrite(studentList);

    log.info("学生信息写入完成！！！{}",student);

}

测试结果：

2.5 写入多个sheet

@Test
public void testWriteSheets(){

    String fileName = "D:\\KEGONGCHANG\\DaiMa\\IDEA\\KH96\\SpringBoot\\SpringBoot\\springboot-03-asyztimer\\excel\\excel07_student_sheets.xlsx";

    //获取学生信息
    studentList.add(student);

    // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
    ExcelWriter excelWriter = EasyExcel.write(fileName, Student.class).build();

    WriteSheet sheet;

    for (int i = 0; i < 2; i++) {
        sheet = EasyExcel.writerSheet(i, "学生信息"+(i+1)).build();
        excelWriter.write(studentList,sheet);
    }

    excelWriter.finish();

    log.info("学生信息写入完成！！！{}",student);

}

测试结果：

3、读出Excel

3.1 doRead()

3.1.1 sheet1中的数据

sheet1:

3.1.2 doRead() 方法读取

//读取Excel中的学生信息
@Test
public void testReadExcel(){

    String fileName = "D:\\KEGONGCHANG\\DaiMa\\IDEA\\KH96\\SpringBoot\\SpringBoot\\springboot-03-asyztimer\\excel\\excel07_student_sheets.xlsx";

    // 实例化一个数据监听器
    StudentDataListener studentDataListener =  new StudentDataListener();

    //第一种
    //读取后的数据放进list中，所以可以从list中获取数据，(这里的数据是我们处理过的，最多返回5条的数据，可以根据实际境况修改)
    EasyExcel.read(fileName, Student.class,studentDataListener)
        	 .sheet()  //默认读取第一个sheet
        	 .doRead();
    List<Student> list = studentDataListener.getList();
    log.info("------ 读取后的数据放进list中，所以可以从list中获取数据,最多返回5条的数据 ------");
    list.forEach(ersonData->{
        log.info(ersonData.toString());
    });

}

3.1.3 测试结果：

3.1.4 分析 doRead() 方法

//没有任何数据返回
public void doRead() {
    if (this.excelReader == null) {
        throw new ExcelGenerateException("Must use 'EasyExcelFactory.read().sheet()' to call this method");
    } else {
        this.excelReader.read(new ReadSheet[]{this.build()});
        this.excelReader.finish();
    }
}

3.2 doReadSync()

3.2.1 sheet2中的数据

sheet2:

3.2.2 doReadSync() 方法读取

//读取Excel中的学生信息
@Test
public void testReadExcel(){

    String fileName = "D:\\KEGONGCHANG\\DaiMa\\IDEA\\KH96\\SpringBoot\\SpringBoot\\springboot-03-asyztimer\\excel\\excel07_student_sheets.xlsx";

    // 实例化一个数据监听器
    StudentDataListener studentDataListener =  new StudentDataListener();

    //第二种
    log.info("------ 同步获取数据，读取到的所有数据 ------");
    //同步获取数据，读取到的所有数据
    List<Student> list2 =  EasyExcel.read(fileName, Student.class,studentDataListener)
        							.sheet(1) //读取第二个sheet
        							.doReadSync();
    list2.forEach(ersonData->{
        log.info(ersonData.toString());
    });
}

3.2.3 测试结果：

3.2.4 分析 doReadSync() 方法

//会将读取到的数据返回
public <T> List<T> doReadSync() {
    if (this.excelReader == null) {
        throw new ExcelAnalysisException("Must use 'EasyExcelFactory.read().sheet()' to call this method");
    } else {
        SyncReadListener syncReadListener = new SyncReadListener();
        this.registerReadListener(syncReadListener);
        this.excelReader.read(new ReadSheet[]{this.build()});
        this.excelReader.finish();
        return syncReadListener.getList();
    }
}

------------------------------------------------------

EasyExcel 专题(二) 深度解析读流程核心源码

EasyExcel将excel文件转换为xml文件，以SAX的形式按行解析，占用内存代价极小，能轻松解析百万条记录。

一、官网地址和版本
地址: https://github.com/alibaba/easyexcel

版本: 3.1.0

克隆下来后，使用git tag命令查看所有版本

git tag

切换到指定版本 : v3.1.0
git checkout v3.1.0
编译源码:
mvn clean install -Dmaven.test.skip=true
编译成功后，进入到easyexcel-test模块。

二、读核心源码解读
首先我们看怎么将excel sheet页里的内容读到 java的DemoData对象里。

1. 材料准备
用最新版的excel文件做演示，创建一个demo.xlsx文件，excel里有2个sheet页，分别为sheet1和sheet2, 内容相同，如下:

对应的model对象DemoData，对应excel表格里的3列。

@Getter
@Setter
@EqualsAndHashCode
public class DemoData {
private String string;
private Date date;
private Double doubleData;
}
用DemoData按行接收数据,例如用PageListener监听器，每次通过consumer回调100行数据出来。

2. EasyExcelFactory
EasyExcelFactory是EasyExcel的父类，它提供了开发者读和写的入口，我们可以借助EasyExcelFactory调用读、写操作。

fileName = TestFileUtil.getPath() + "demo" + File.separator + "demo.xlsx";
// 这里需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead();
读取一个excel只需要三个参数: 带绝对路径的文件名，POJO的Class和一个监听器。

跟着EasyExcel.read()方法去解析读操作, 进入到EasyExcelFactory类，下面我将从初始化---->解析---> 收尾这三个流程做解析。

3. 读excel---初始化
1) read()方法初始化ExcelReaderBuilder
在read()方法里初始化一个ExcelReaderBuilder, 通过readBuilder设置文件路径，excel标题和注册监听器。

public static ExcelReaderBuilder read(String pathName, Class head, ReadListener readListener) {
ExcelReaderBuilder excelReaderBuilder = new ExcelReaderBuilder();
// 设置文件路径
excelReaderBuilder.file(pathName);
//设置excel标题,也支持无标题
if (head != null) {
excelReaderBuilder.head(head);
}
// 注册监听器
if (readListener != null) {
excelReaderBuilder.registerReadListener(readListener);
}
return excelReaderBuilder;
}
重要点: 根据ExcelReaderBuilder初始化一个ReadWorkBook类, ReadWorkBoook类包含了读取Excel的内容，用流的形式进行保存。

private final ReadWorkbook readWorkbook;

public ExcelReaderBuilder() {
this.readWorkbook = new ReadWorkbook();
}
2) .sheet()方法初始化ExcelReaderSheetBuilder
通过sheet()方法，通过build()方法初始化ExcelReaderSheetBuilder, 并设置表格名称、表格序号。

public ExcelReaderSheetBuilder sheet(Integer sheetNo, String sheetName) {
// 初始化表格建造器
ExcelReaderSheetBuilder excelReaderSheetBuilder = new ExcelReaderSheetBuilder(build());
if (sheetNo != null) {
// 设置表格序号
excelReaderSheetBuilder.sheetNo(sheetNo);
}
if (sheetName != null) {
//设置表格名称
excelReaderSheetBuilder.sheetName(sheetName);
}
return excelReaderSheetBuilder;
}
主要点，在build()方法里初始化了excelReader和readSheet对象, 为后续读取工作做准备。

public ExcelReader build() {
return new ExcelReader(readWorkbook);
}
同时在ExcelReader的构造方法里初始化ExcelAnalyser的实现类ExcelAnalyserImpl， ExcelAnalyserImpl是分析excel的核心实现类。

/**
* Analyser
*/
private final ExcelAnalyser excelAnalyser;

public ExcelReader(ReadWorkbook readWorkbook) {
excelAnalyser = new ExcelAnalyserImpl(readWorkbook);
}
然后用初始化好了的excelReader去初始化ExcelReaderSheetBuilder，同时初始readSheet对象后续会做为ExcelReader.read()方法的参数。

private ExcelReader excelReader;
/**
* Sheet
*/
private final ReadSheet readSheet;

public ExcelReaderSheetBuilder() {
this.readSheet = new ReadSheet();
}

public ExcelReaderSheetBuilder(ExcelReader excelReader) {
this.readSheet = new ReadSheet();
this.excelReader = excelReader;
}
3) 根据excel类型选择对应的执行器executor的实现类XlsxAnalyser
在ExcelAnalyserImpl的构造方法里实现了初始化ExcelReaderExecutor的操作，根据不同的文件类型，选择使用不同的Executor, 比如后缀名为xlsx类型的excel使用XlsxAnalyser解析器。

public ExcelAnalyserImpl(ReadWorkbook readWorkbook) {
try {
// 根据excel的类型选择对应的executor
choiceExcelExecutor(readWorkbook);
} catch (RuntimeException e) {
finish();
throw e;
} catch (Throwable e) {
finish();
throw new ExcelAnalysisException(e);
}
}

private void choiceExcelExecutor(ReadWorkbook readWorkbook) throws Exception {
ExcelTypeEnum excelType = ExcelTypeEnum.valueOf(readWorkbook);
switch (excelType) {
case XLS:
POIFSFileSystem poifsFileSystem;
if (readWorkbook.getFile() != null) {
poifsFileSystem = new POIFSFileSystem(readWorkbook.getFile());
} else {
poifsFileSystem = new POIFSFileSystem(readWorkbook.getInputStream());
}
// So in encrypted excel, it looks like XLS but it's actually XLSX
if (poifsFileSystem.getRoot().hasEntry(Decryptor.DEFAULT_POIFS_ENTRY)) {
InputStream decryptedStream = null;
try {
decryptedStream = DocumentFactoryHelper
.getDecryptedStream(poifsFileSystem.getRoot().getFileSystem(), readWorkbook.getPassword());
XlsxReadContext xlsxReadContext = new DefaultXlsxReadContext(readWorkbook, ExcelTypeEnum.XLSX);
analysisContext = xlsxReadContext;
excelReadExecutor = new XlsxSaxAnalyser(xlsxReadContext, decryptedStream);
return;
} finally {
IOUtils.closeQuietly(decryptedStream);
// as we processed the full stream already, we can close the filesystem here
// otherwise file handles are leaked
poifsFileSystem.close();
}
}
if (readWorkbook.getPassword() != null) {
Biff8EncryptionKey.setCurrentUserPassword(readWorkbook.getPassword());
}
XlsReadContext xlsReadContext = new DefaultXlsReadContext(readWorkbook, ExcelTypeEnum.XLS);
xlsReadContext.xlsReadWorkbookHolder().setPoifsFileSystem(poifsFileSystem);
analysisContext = xlsReadContext;
excelReadExecutor = new XlsSaxAnalyser(xlsReadContext);
break;
case XLSX:
XlsxReadContext xlsxReadContext = new DefaultXlsxReadContext(readWorkbook, ExcelTypeEnum.XLSX);
analysisContext = xlsxReadContext;
// 同时通过openxml4j将excel文件转换为xml文件
excelReadExecutor = new XlsxSaxAnalyser(xlsxReadContext, null);
break;
case CSV:
CsvReadContext csvReadContext = new DefaultCsvReadContext(readWorkbook, ExcelTypeEnum.CSV);
analysisContext = csvReadContext;
excelReadExecutor = new CsvExcelReadExecutor(csvReadContext);
break;
default:
break;
}
}

4) 使用OPCPackage将Excel文件转换为Xml
接着上一步，进入到XlsxAnalyser的构造方法里，通过OPCPackage将excel文件转换为一个zip文件

OPCPackage.open(xlsxReadWorkbookHolder.getFile());
里面包含了excel所有相关内容的xml形式的展示， OPCPackage的实现通过ZipPackage

5) 查看excel转换后的xml形式
我们可以写一个小demo,使用opcPackage.save(path)方法将zip存入到磁盘上。

public void testExcelTransferToXml() throws InvalidFormatException, IOException {
// 使用OPCPackage打开一个压缩包
String path = TestFileUtil.getPath() + "demo/";

File file = new File(path + "demo.xlsx");
OPCPackage opcPackage = OPCPackage.open(file);
List<PackagePart> parts = opcPackage.getParts();
File targetFile = new File(path + "result.zip");
opcPackage.save(targetFile);
System.out.println();
}
打开zip文件后，长这样, sheet表单里的内容存放在worksheets目录下:

到此，一个Analyser的完整初始化流程就结束了，同时在executor的实现类里将EXCEL转换为XML，这些动作就是为了读而做准备。

4. 读excel---解析
1) 进入ExcelReaderSheetBuilder.doRead()方法
public void doRead() {
if (excelReader == null) {
throw new ExcelGenerateException("Must use 'EasyExcelFactory.read().sheet()' to call this method");
}
// build()方法初始化ExcelReaderBuilder,
excelReader.read(build());
excelReader.finish();
}
2) 转交给ExcelReader.read(ReadSheet... readSheet)
用初始化好的excelAnalyser去分析表格列表，一个ReadSheet就是一个sheet页。

public ExcelReader read(List<ReadSheet> readSheetList) {
excelAnalyser.analysis(readSheetList, Boolean.FALSE);
return this;
}
3) 读取excel的入口 excelReaderExecutor.execute()
ExcelAnalyserImpl里的anlysis方法里的execelReadExecutor.execute()方法是读取excel的入口， readAll参数默认为false。

public void analysis(List<ReadSheet> readSheetList, Boolean readAll) {
try {
if (!readAll && CollectionUtils.isEmpty(readSheetList)) {
throw new IllegalArgumentException("Specify at least one read sheet.");
}
analysisContext.readWorkbookHolder().setParameterSheetDataList(readSheetList);
analysisContext.readWorkbookHolder().setReadAll(readAll);
try {
//执行excel读取的入口
excelReadExecutor.execute();
} catch (ExcelAnalysisStopException e) {
if (LOGGER.isDebugEnabled()) {
LOGGER.debug("Custom stop!");
}
}
} catch (RuntimeException e) {
finish();
throw e;
} catch (Throwable e) {
finish();
throw new ExcelAnalysisException(e);
}
}

在analysis方法执行前，我们可以发现在AnalysisImpl的构造方法里根据Excel类型创建了不同类型的执行器choiceExcelExecutor(readWorkBook)，其中CsvExcelReaderExecutor为解析CSV用的执行器、XlsSaxAnalyser为解析03版的excel的执行器(后缀为.xls的excel) 、XlsxSaxAnalyser 为解析07版excel的执行器。

parseXmlSource方法继续xml文件里的内容。

4) 如何解析xml?
SAX提供了一个ContentHandler接口给开发者，我们可以实现ContentHandler接口来自定义内容处理器，重写ContentHandler里的startElement()方法即可，XlsxRowHandler为定义的实现类

parseXmlSource(sheetMap.get(readSheet.getSheetNo()), new XlsxRowHandler(xlsxReadContext));
因此解析的时候会在XlsxRowHandler里的startElement方法里进行

我们可以发现这里用到了策略模式，根据name获取到对应的XlsxTagHandler, 根据excel里的标签去选择对应的handler。

然后进入到RowTagHandler的startElement方法，按照xml的标签去解析。

5) 使用ModelBuildEventListener监听器映射成用户定义对象
当满足while条件时，进入到xlsxReadContext.analysisEventProcessor().endRow(xlsxReadContext);

然后进入到dealData(analysisContext)方法，根据rowIndex当前行判断是否为数据，如果是数据，那么就执行监听器的invoke方法，如果是标题，那么就执行监听器的invokeHead()方法，analysisContext里的ReadHolder提供了一个默认的Listener-----ModelBuildEventListener。

ModelBuildEventListener的用处是将cellDataMap在builderUserModel转换为一个开发者指定的对象，并用ReadRowHolder的currentRowAnalysisResult属性接收。

返回用户对象后，会在for循环里执行下一个监听器，该监听器为我们指定的PageReaderListener。

进入到PageReaderListener的invoke方法获取到用户对象。

@Override
public void invoke(T data, AnalysisContext context) {
cachedDataList.add(data);
if (cachedDataList.size() >= BATCH_COUNT) {
consumer.accept(cachedDataList);
cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
}
}

然后每次读完BATCH_COUNT条数据，执行一次回调consumer.accept(cachedDataList)。

6) 执行收尾工作
再次进入到XlsxSaxAnalyser的execute()方法，解析完后，执行了收尾工作

xlsxReadContext.analysisEventProcessor().endSheet(xlsxReadContext);

进入到PageReaderListener接口里的doAfterAllAnalysed, 如果cachedDataList不为空，继续回调。

@Override
public void doAfterAllAnalysed(AnalysisContext context) {
if (CollectionUtils.isNotEmpty(cachedDataList)) {
consumer.accept(cachedDataList);
}
}
比如我定义的BACTH_COUNT=100, 每次满100条我才回调，我excel里只有10条记录，那么就不能在invoke方法里回调了，所以这里一定要进行收尾。

------------------------------------------------------

POI与easyExcel的区别：

POI是通过WorkBook来解析表格文件的，虽然内部有使用到Sax模式，能后大大的提高效率，但是要解析大文件（10w，100w行）的话很容易出现OOM（内存溢出）。
相比之下，
1、easyExcel解析实在磁盘上进行的，几乎可以将几mb级别的内存压缩到kb级别，几乎不用担心OOM；
2、用Java模型进行关系映射，项目中最常用的就Java模型映射，通过 @ExcelProperty注解就可以完成行与列的映射；
3、easyExcel中有一个类AnalysisEventListener，里面有一个方法invoke实现了一行一行返回，另外还可以重写该类的doAfterAllAnalysed方法，用来做事后处理之类的操作，相当的灵活。

准备阶段

第一步：引入easyExcel依赖

<dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>easyexcel</artifactId>
      <version>1.1.2-beta5</version>
    </dependency>

    <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>easyexcel</artifactId>
      <version>1.1.2-beta5</version>
    </dependency>

第二步：自定义监听器ExcelListener继承于AnalysisEventListener，重写invoke()方法（可以读取到excel每一行的数据）和doAfterAllAnalysed()方法（用于后置处理），其中datas用于存取读取到的数据，importHeads为导入表头，modelHeads

package com.cloud.data.utils;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.metadata.ExcelHeadProperty;
import org.springframework.util.StringUtils;

import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
* EasyExcell导入监听类
*/
public class ExcelListener extends AnalysisEventListener {
// 自定义用于暂时存储数据
private List<Object> datas = new ArrayList<>();
// 导入表头
private String importHeads = "";
// 模版表头
private String modelHeads = "";

/**
* 通过 AnalysisContext对象获取当前sheet，当前行等数据
*/
@Override
public void invoke(Object o, AnalysisContext analysisContext) {
Integer currentRowNum = analysisContext.getCurrentRowNum();
// 获取导入表头，默认第一行为表头
if(currentRowNum == 0){
try {
Map<String,Object> m = objToMap(o);
for (Object v : m.values()) {
if(!StringUtils.isEmpty(v)){
importHeads += String.valueOf(v).trim() + ",";
}
}
} catch (Exception e) {
e.printStackTrace();
}
}else{
datas.add(o);
}
}

/**
* 监听器获取模板表头
*/
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
// 获取模版表头
ExcelHeadProperty ehp = analysisContext.getExcelHeadProperty();
for(List<String> s : ehp.getHead()){
modelHeads += s.get(0) + ",";
}
}

// Object转换为Map
private Map<String,Object> objToMap(Object obj) throws Exception{
Map<String,Object> map = new LinkedHashMap<String, Object>();
Field[] fields = obj.getClass().getDeclaredFields();
for(Field field : fields){
field.setAccessible(true);
map.put(field.getName(), field.get(obj));
}
return map;
}

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

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

public String getImportHeads() {
return importHeads;
}

public void setImportHeads(String importHeads) {
this.importHeads = importHeads;
}

public String getModelHeads() {
return modelHeads;
}

public void setModelHeads(String modelHeads) {
this.modelHeads = modelHeads;
}
}

package com.cloud.data.utils;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.metadata.ExcelHeadProperty;
import org.springframework.util.StringUtils;

import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * EasyExcell导入监听类
 */
public class ExcelListener extends AnalysisEventListener {
    // 自定义用于暂时存储数据
    private List<Object> datas = new ArrayList<>();
    // 导入表头
    private String importHeads = "";
    // 模版表头
    private String modelHeads = "";

    /**
     * 通过 AnalysisContext对象获取当前sheet，当前行等数据
     */
    @Override
    public void invoke(Object o, AnalysisContext analysisContext) {
        Integer currentRowNum = analysisContext.getCurrentRowNum();
        // 获取导入表头，默认第一行为表头
        if(currentRowNum == 0){
            try {
                Map<String,Object> m = objToMap(o);
                for (Object v : m.values()) {
                    if(!StringUtils.isEmpty(v)){
                        importHeads += String.valueOf(v).trim() + ",";
                    }
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }else{
            datas.add(o);
        }
    }

    /**
     * 监听器获取模板表头
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        // 获取模版表头
        ExcelHeadProperty ehp = analysisContext.getExcelHeadProperty();
        for(List<String> s : ehp.getHead()){
            modelHeads += s.get(0) + ",";
        }
    }

    // Object转换为Map
    private Map<String,Object> objToMap(Object obj) throws Exception{
        Map<String,Object> map = new LinkedHashMap<String, Object>();
        Field[] fields = obj.getClass().getDeclaredFields();
        for(Field field : fields){
            field.setAccessible(true);
            map.put(field.getName(), field.get(obj));
        }
        return map;
    }

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

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

    public String getImportHeads() {
        return importHeads;
    }

    public void setImportHeads(String importHeads) {
        this.importHeads = importHeads;
    }

    public String getModelHeads() {
        return modelHeads;
    }

    public void setModelHeads(String modelHeads) {
        this.modelHeads = modelHeads;
    }
}

第三步：添加接收excel导入的实体类OutDbillDto，继承BaseRowModel，其中@ExcelProperty(value = "机组调度名称（必填）", index = 0)对应excel表头的（value为表头名称，index为索引位置）

package com.cloud.data.entity.dto;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
import lombok.Data;

import java.io.Serializable;

/**
* 日结算账单导入类
*/
@Data
public class OutDbillDto extends BaseRowModel implements Serializable {
@ExcelProperty(value = "机组调度名称（必填）", index = 0)
private String unitDispatchName;
@ExcelProperty(value = "日期（必填）", index = 1)
private String billTime;
@ExcelProperty(value = "收费项目", index = 2)
private String project;
@ExcelProperty(value = "本期电量（万kWh）", index = 3)
private String quan;
@ExcelProperty(value = "单价（厘/kWh）", index = 4)
private String avgPrice;
@ExcelProperty(value = "本期电费（元）",index = 5)
private String price;
/**标记*/
private String mark;
/**错误信息*/
private String errMsg;
}

package com.cloud.data.entity.dto;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
import lombok.Data;

import java.io.Serializable;

/**
 * 日结算账单导入类
 */
@Data
public class OutDbillDto extends BaseRowModel implements Serializable {
    @ExcelProperty(value = "机组调度名称（必填）", index = 0)
    private String unitDispatchName;
    @ExcelProperty(value = "日期（必填）", index = 1)
    private String billTime;
    @ExcelProperty(value = "收费项目", index = 2)
    private String project;
    @ExcelProperty(value = "本期电量（万kWh）", index = 3)
    private String quan;
    @ExcelProperty(value = "单价（厘/kWh）", index = 4)
    private String avgPrice;
    @ExcelProperty(value = "本期电费（元）",index = 5)
    private String price;
    /**标记*/
    private String mark;
    /**错误信息*/
    private String errMsg;
}

第四步，定义EasyExcelUtil工具类，用于读取excel

package com.cloud.data.utils;

import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.cloud.frame.util.DateUtil;
import com.google.common.collect.Lists;
import org.apache.commons.beanutils.BeanUtils;

import org.apache.poi.ss.formula.functions.T;
import org.springframework.web.multipart.MultipartFile;

import java.io.*;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.util.*;

public class EasyExcelUtil {
/**
* 读取某个 sheet 的 Excel
*
* @param excel 文件
* @param rowModel 实体类映射，继承 BaseRowModel 类
* @return Excel 数据 list
*/
public static List<Object> readExcel(MultipartFile excel, BaseRowModel rowModel) throws IOException {
return readExcel(excel, rowModel, 1, 1);
}

/**
* 读取某个 sheet 的 Excel
* @param excel 文件
* @param rowModel 实体类映射，继承 BaseRowModel 类
* @param sheetNo sheet 的序号从1开始
* @return Excel 数据 list
*/
public static Map<String,Object> readExcel(MultipartFile excel, BaseRowModel rowModel, int sheetNo) throws IOException {
Map<String,Object> result = new HashMap<>();
ExcelListener excelListener = new ExcelListener();
ExcelReader reader = getReader(excel, excelListener);
if (Objects.isNull(reader)) {
return null;
}
reader.read(new Sheet(sheetNo, 0, rowModel.getClass()));
//校验表头
Boolean flag = false;
if(excelListener.getImportHeads().equals(excelListener.getModelHeads())){
flag = true;
}
result.put("flag", flag);
result.put("datas", excelListener.getDatas());
return result;
}

/**
* 读取某个 sheet 的 Excel
* @param excel 文件
* @param rowModel 实体类映射，继承 BaseRowModel 类
* @param sheetNo sheet 的序号从1开始
* @param headLineNum 表头行数，默认为1
* @return Excel 数据 list
*/
public static List<Object> readExcel(MultipartFile excel, BaseRowModel rowModel, int sheetNo, int headLineNum) throws IOException {
ExcelListener excelListener = new ExcelListener();
ExcelReader reader = getReader(excel, excelListener);
if (reader == null) {
return null;
}
reader.read(new Sheet(sheetNo, headLineNum, rowModel.getClass()));
return excelListener.getDatas();
}

/**
* 读取指定sheetName的Excel(多个 sheet)
* @param excel 文件
* @param rowModel 实体类映射，继承 BaseRowModel 类
* @return Excel 数据 list
* @throws IOException
*/
public static List<Object> readExcel(MultipartFile excel, BaseRowModel rowModel,String sheetName) throws IOException {
ExcelListener excelListener = new ExcelListener();
ExcelReader reader = getReader(excel, excelListener);
if (reader == null) {
return null;
}
for (Sheet sheet : reader.getSheets()) {
if (rowModel != null) {
sheet.setClazz(rowModel.getClass());
}
//读取指定名称的sheet
if(sheet.getSheetName().contains(sheetName)){
reader.read(sheet);
break;
}
}
return excelListener.getDatas();
}

/**
* 返回 ExcelReader
* @param excel 需要解析的 Excel 文件
* @param excelListener new ExcelListener()
* @throws IOException
*/
private static ExcelReader getReader(MultipartFile excel,ExcelListener excelListener) throws IOException {
String filename = excel.getOriginalFilename();
if(Objects.nonNull(filename) && (filename.toLowerCase().endsWith(".xls") || filename.toLowerCase().endsWith(".xlsx"))){
InputStream is = new BufferedInputStream(excel.getInputStream());
return new ExcelReader(is, null,null, excelListener, false);
}else{
return null;
}
}

/**
* 将导入失败的数据写到系统指定路径
* @param failDatas 数据
* @param name 文件名
* @param dir 写入的路径
* @param header Excel表头
* @param columns 需要写入Excel的对象属性集合
* @param index 合并单元格索引
* @return
* @throws Exception
*/
public static String saveExcel2Loacl(List<?> failDatas, String name, String dir, String[] header,String[] columns,Integer index) throws Exception {
// 1.唯一文件名
String fileName = name + "_" + DateUtil.getNowStr(DateUtil.TIME_FORMAT)+".xls";
Path path= Paths.get(ExcelUtil.FILE_UPLOAD_ROOT, dir).toAbsolutePath();
File file = new File(path.toString());
if (!file.exists()){
file.mkdirs();
}
// 2.添加Sheet名
Sheet sheet = new Sheet(1,0);
sheet.setSheetName(name);
// 3.动态添加Excel表头
List<List<String>> head = new ArrayList<>();
for (String h : header) {
head.add(Lists.newArrayList(h));
}
sheet.setHead(head);
// 4.写入数据
List<T> datas = new ArrayList(failDatas);
List<List<Object>> data = new ArrayList<>();
for (Object var : datas) {
List<Object> objects = Lists.newArrayList();
Arrays.stream(columns).forEach(e ->{
try {
String property = BeanUtils.getProperty(var, e);
objects.add(property);
} catch (Exception err) {
err.printStackTrace();
}
});
data.add(objects);
}

FileOutputStream fileOutputStream = new FileOutputStream(Paths.get(ExcelUtil.FILE_UPLOAD_ROOT, dir, fileName).toFile());
ExcelWriter writer = EasyExcelFactory.getWriter(fileOutputStream, ExcelTypeEnum.XLS, true);
writer.write1(data,sheet);
// 5.合并单元格
for (int i = 1; i <= failDatas.size(); i = i + index) {
writer.merge(i,i + index - 1,0,0);
writer.merge(i,i + index - 1,1,1);
writer.merge(i,i + index - 1,2,2);
}
writer.finish();
return fileName;
}
}

package com.cloud.data.utils;

import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.cloud.frame.util.DateUtil;
import com.google.common.collect.Lists;
import org.apache.commons.beanutils.BeanUtils;

import org.apache.poi.ss.formula.functions.T;
import org.springframework.web.multipart.MultipartFile;

import java.io.*;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.util.*;

public class EasyExcelUtil {
    /**
     * 读取某个 sheet 的 Excel
     *
     * @param excel    文件
     * @param rowModel 实体类映射，继承 BaseRowModel 类
     * @return Excel 数据 list
     */
    public static List<Object> readExcel(MultipartFile excel, BaseRowModel rowModel) throws IOException {
        return readExcel(excel, rowModel, 1, 1);
    }

    /**
     * 读取某个 sheet 的 Excel
     * @param excel       文件
     * @param rowModel    实体类映射，继承 BaseRowModel 类
     * @param sheetNo     sheet 的序号 从1开始
     * @return Excel 数据 list
     */
    public static Map<String,Object> readExcel(MultipartFile excel, BaseRowModel rowModel, int sheetNo) throws IOException {
        Map<String,Object> result = new HashMap<>();
        ExcelListener excelListener = new ExcelListener();
        ExcelReader reader = getReader(excel, excelListener);
        if (Objects.isNull(reader)) {
            return null;
        }
        reader.read(new Sheet(sheetNo, 0, rowModel.getClass()));
        //校验表头
        Boolean flag = false;
        if(excelListener.getImportHeads().equals(excelListener.getModelHeads())){
            flag = true;
        }
        result.put("flag", flag);
        result.put("datas", excelListener.getDatas());
        return result;
    }

    /**
     * 读取某个 sheet 的 Excel
     * @param excel       文件
     * @param rowModel    实体类映射，继承 BaseRowModel 类
     * @param sheetNo     sheet 的序号 从1开始
     * @param headLineNum 表头行数，默认为1
     * @return Excel 数据 list
     */
    public static List<Object> readExcel(MultipartFile excel, BaseRowModel rowModel, int sheetNo, int headLineNum) throws IOException {
        ExcelListener excelListener = new ExcelListener();
        ExcelReader reader = getReader(excel, excelListener);
        if (reader == null) {
            return null;
        }
        reader.read(new Sheet(sheetNo, headLineNum, rowModel.getClass()));
        return excelListener.getDatas();
    }

    /**
     * 读取指定sheetName的Excel(多个 sheet)
     * @param excel    文件
     * @param rowModel 实体类映射，继承 BaseRowModel 类
     * @return Excel 数据 list
     * @throws IOException
     */
    public static List<Object> readExcel(MultipartFile excel, BaseRowModel rowModel,String sheetName) throws IOException {
        ExcelListener excelListener = new ExcelListener();
        ExcelReader reader = getReader(excel, excelListener);
        if (reader == null) {
            return null;
        }
        for (Sheet sheet : reader.getSheets()) {
            if (rowModel != null) {
                sheet.setClazz(rowModel.getClass());
            }
            //读取指定名称的sheet
            if(sheet.getSheetName().contains(sheetName)){
                reader.read(sheet);
                break;
            }
        }
        return excelListener.getDatas();
    }

    /**
     * 返回 ExcelReader
     * @param excel 需要解析的 Excel 文件
     * @param excelListener new ExcelListener()
     * @throws IOException
     */
    private static ExcelReader getReader(MultipartFile excel,ExcelListener excelListener) throws IOException {
        String filename = excel.getOriginalFilename();
        if(Objects.nonNull(filename) && (filename.toLowerCase().endsWith(".xls") || filename.toLowerCase().endsWith(".xlsx"))){
            InputStream is = new BufferedInputStream(excel.getInputStream());
            return new ExcelReader(is, null,null, excelListener, false);
        }else{
            return null;
        }
    }

    /**
     * 将导入失败的数据写到系统指定路径
     * @param failDatas 数据
     * @param name 文件名
     * @param dir 写入的路径
     * @param header Excel表头
     * @param columns 需要写入Excel的对象属性集合
     * @param index 合并单元格索引
     * @return
     * @throws Exception
     */
    public static String saveExcel2Loacl(List<?> failDatas, String name, String dir, String[] header,String[] columns,Integer index) throws Exception {
        // 1.唯一文件名
        String fileName = name + "_" + DateUtil.getNowStr(DateUtil.TIME_FORMAT)+".xls";
        Path path= Paths.get(ExcelUtil.FILE_UPLOAD_ROOT, dir).toAbsolutePath();
        File file = new File(path.toString());
        if (!file.exists()){
            file.mkdirs();
        }
        // 2.添加Sheet名
        Sheet sheet = new Sheet(1,0);
        sheet.setSheetName(name);
        // 3.动态添加Excel表头
        List<List<String>> head = new ArrayList<>();
        for (String h : header) {
            head.add(Lists.newArrayList(h));
        }
        sheet.setHead(head);
        // 4.写入数据
        List<T> datas = new ArrayList(failDatas);
        List<List<Object>> data = new ArrayList<>();
        for (Object var : datas) {
            List<Object> objects = Lists.newArrayList();
            Arrays.stream(columns).forEach(e ->{
                try {
                    String property = BeanUtils.getProperty(var, e);
                    objects.add(property);
                } catch (Exception err) {
                    err.printStackTrace();
                }
            });
            data.add(objects);
        }

        FileOutputStream fileOutputStream = new FileOutputStream(Paths.get(ExcelUtil.FILE_UPLOAD_ROOT, dir, fileName).toFile());
        ExcelWriter writer = EasyExcelFactory.getWriter(fileOutputStream, ExcelTypeEnum.XLS, true);
        writer.write1(data,sheet);
        // 5.合并单元格
        for (int i = 1; i <= failDatas.size(); i = i + index) {
            writer.merge(i,i + index - 1,0,0);
            writer.merge(i,i + index - 1,1,1);
            writer.merge(i,i + index - 1,2,2);
        }
        writer.finish();
        return fileName;
    }
}

执行原理：

ExcelAnalyserImpl是解析器的真正实现，整合了v07好人v03，解析的时候会根据getSaxAnalyser来选择使用哪种版本的解析器。

通过ExcelAnalyserImpl()构造方法，将inputstream（也就是file文件）和自定义的监听器eventListener（继承于AnalysisEventListener）存入ExcelAnalyserImpl类的AnalysisContext属性

package com.alibaba.excel.analysis;

import com.alibaba.excel.analysis.v03.XlsSaxAnalyser;
import com.alibaba.excel.analysis.v07.XlsxSaxAnalyser;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.context.AnalysisContextImpl;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.exception.ExcelAnalysisException;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.modelbuild.ModelBuildEventListener;
import com.alibaba.excel.support.ExcelTypeEnum;

import java.io.InputStream;
import java.util.List;

/**
* @author jipengfei
*/
public class ExcelAnalyserImpl implements ExcelAnalyser {

private AnalysisContext analysisContext;

private BaseSaxAnalyser saxAnalyser;

public ExcelAnalyserImpl(InputStream inputStream, ExcelTypeEnum excelTypeEnum, Object custom,
AnalysisEventListener eventListener, boolean trim) {
analysisContext = new AnalysisContextImpl(inputStream, excelTypeEnum, custom,
eventListener, trim);
}

private BaseSaxAnalyser getSaxAnalyser() {
if (saxAnalyser != null) {
return this.saxAnalyser;
}
try {
if (analysisContext.getExcelType() != null) {
switch (analysisContext.getExcelType()) {
case XLS:
this.saxAnalyser = new XlsSaxAnalyser(analysisContext);
break;
case XLSX:
this.saxAnalyser = new XlsxSaxAnalyser(analysisContext);
break;
}
} else {
try {
this.saxAnalyser = new XlsxSaxAnalyser(analysisContext);
} catch (Exception e) {
if (!analysisContext.getInputStream().markSupported()) {
throw new ExcelAnalysisException(
"Xls must be available markSupported,you can do like this <code> new "
+ "BufferedInputStream(new FileInputStream(\"/xxxx\"))</code> ");
}
this.saxAnalyser = new XlsSaxAnalyser(analysisContext);
}
}
} catch (Exception e) {
throw new ExcelAnalysisException("File type error，io must be available markSupported,you can do like "
+ "this <code> new BufferedInputStream(new FileInputStream(\\\"/xxxx\\\"))</code> \"", e);
}
return this.saxAnalyser;
}

@Override
public void analysis(Sheet sheetParam) {
analysisContext.setCurrentSheet(sheetParam);
analysis();
}

@Override
public void analysis() {
BaseSaxAnalyser saxAnalyser = getSaxAnalyser();
appendListeners(saxAnalyser);
saxAnalyser.execute();
analysisContext.getEventListener().doAfterAllAnalysed(analysisContext);
}

@Override
public List<Sheet> getSheets() {
BaseSaxAnalyser saxAnalyser = getSaxAnalyser();
saxAnalyser.cleanAllListeners();
return saxAnalyser.getSheets();
}

private void appendListeners(BaseSaxAnalyser saxAnalyser) {
saxAnalyser.cleanAllListeners();
if (analysisContext.getCurrentSheet() != null && analysisContext.getCurrentSheet().getClazz() != null) {
saxAnalyser.appendLister("model_build_listener", new ModelBuildEventListener());
}
if (analysisContext.getEventListener() != null) {
saxAnalyser.appendLister("user_define_listener", analysisContext.getEventListener());
}
}

}

package com.alibaba.excel.analysis;

import com.alibaba.excel.analysis.v03.XlsSaxAnalyser;
import com.alibaba.excel.analysis.v07.XlsxSaxAnalyser;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.context.AnalysisContextImpl;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.exception.ExcelAnalysisException;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.modelbuild.ModelBuildEventListener;
import com.alibaba.excel.support.ExcelTypeEnum;

import java.io.InputStream;
import java.util.List;

/**
 * @author jipengfei
 */
public class ExcelAnalyserImpl implements ExcelAnalyser {

    private AnalysisContext analysisContext;

    private BaseSaxAnalyser saxAnalyser;

    public ExcelAnalyserImpl(InputStream inputStream, ExcelTypeEnum excelTypeEnum, Object custom,
                             AnalysisEventListener eventListener, boolean trim) {
        analysisContext = new AnalysisContextImpl(inputStream, excelTypeEnum, custom,
            eventListener, trim);
    }

    private BaseSaxAnalyser getSaxAnalyser() {
        if (saxAnalyser != null) {
            return this.saxAnalyser;
        }
        try {
            if (analysisContext.getExcelType() != null) {
                switch (analysisContext.getExcelType()) {
                    case XLS:
                        this.saxAnalyser = new XlsSaxAnalyser(analysisContext);
                        break;
                    case XLSX:
                        this.saxAnalyser = new XlsxSaxAnalyser(analysisContext);
                        break;
                }
            } else {
                try {
                    this.saxAnalyser = new XlsxSaxAnalyser(analysisContext);
                } catch (Exception e) {
                    if (!analysisContext.getInputStream().markSupported()) {
                        throw new ExcelAnalysisException(
                            "Xls must be available markSupported,you can do like this <code> new "
                                + "BufferedInputStream(new FileInputStream(\"/xxxx\"))</code> ");
                    }
                    this.saxAnalyser = new XlsSaxAnalyser(analysisContext);
                }
            }
        } catch (Exception e) {
            throw new ExcelAnalysisException("File type error，io must be available markSupported,you can do like "
                + "this <code> new BufferedInputStream(new FileInputStream(\\\"/xxxx\\\"))</code> \"", e);
        }
        return this.saxAnalyser;
    }

    @Override
    public void analysis(Sheet sheetParam) {
        analysisContext.setCurrentSheet(sheetParam);
        analysis();
    }

    @Override
    public void analysis() {
        BaseSaxAnalyser saxAnalyser = getSaxAnalyser();
        appendListeners(saxAnalyser);
        saxAnalyser.execute();
        analysisContext.getEventListener().doAfterAllAnalysed(analysisContext);
    }

    @Override
    public List<Sheet> getSheets() {
        BaseSaxAnalyser saxAnalyser = getSaxAnalyser();
        saxAnalyser.cleanAllListeners();
        return saxAnalyser.getSheets();
    }

    private void appendListeners(BaseSaxAnalyser saxAnalyser) {
        saxAnalyser.cleanAllListeners();
        if (analysisContext.getCurrentSheet() != null && analysisContext.getCurrentSheet().getClazz() != null) {
            saxAnalyser.appendLister("model_build_listener", new ModelBuildEventListener());
        }
        if (analysisContext.getEventListener() != null) {
            saxAnalyser.appendLister("user_define_listener", analysisContext.getEventListener());
        }
    }

}

package com.alibaba.excel.analysis;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.event.AnalysisEventRegisterCenter;
import com.alibaba.excel.event.OneRowAnalysisFinishEvent;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.util.TypeUtil;

import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
* @author jipengfei
*/
public abstract class BaseSaxAnalyser implements AnalysisEventRegisterCenter, ExcelAnalyser {

protected AnalysisContext analysisContext;

private LinkedHashMap<String, AnalysisEventListener> listeners = new LinkedHashMap<String, AnalysisEventListener>();

/**
* execute method
*/
protected abstract void execute();

@Override
public void appendLister(String name, AnalysisEventListener listener) {
if (!listeners.containsKey(name)) {
listeners.put(name, listener);
}
}

@Override
public void analysis(Sheet sheetParam) {
execute();
}

@Override
public void analysis() {
execute();
}

/**
*/
@Override
public void cleanAllListeners() {
listeners = new LinkedHashMap<String, AnalysisEventListener>();
}

@Override
public void notifyListeners(OneRowAnalysisFinishEvent event) {
analysisContext.setCurrentRowAnalysisResult(event.getData());
/** Parsing header content **/
if (analysisContext.getCurrentRowNum() < analysisContext.getCurrentSheet().getHeadLineMun()) {
if (analysisContext.getCurrentRowNum() <= analysisContext.getCurrentSheet().getHeadLineMun() - 1) {
analysisContext.buildExcelHeadProperty(null,
(List<String>)analysisContext.getCurrentRowAnalysisResult());
}
} else {
List<String> content = converter((List<String>)event.getData());
/** Parsing Analyze the body content **/
analysisContext.setCurrentRowAnalysisResult(content);
if (listeners.size() == 1) {
analysisContext.setCurrentRowAnalysisResult(content);
}
/** notify all event listeners **/
for (Map.Entry<String, AnalysisEventListener> entry : listeners.entrySet()) {
entry.getValue().invoke(analysisContext.getCurrentRowAnalysisResult(), analysisContext);
}
}
}

private List<String> converter(List<String> data) {
List<String> list = new ArrayList<String>();
if (data != null) {
for (String str : data) {
list.add(TypeUtil.formatFloat(str));
}
}
return list;
}

}

package com.alibaba.excel.analysis;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.event.AnalysisEventRegisterCenter;
import com.alibaba.excel.event.OneRowAnalysisFinishEvent;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.util.TypeUtil;

import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * @author jipengfei
 */
public abstract class BaseSaxAnalyser implements AnalysisEventRegisterCenter, ExcelAnalyser {

    protected AnalysisContext analysisContext;

    private LinkedHashMap<String, AnalysisEventListener> listeners = new LinkedHashMap<String, AnalysisEventListener>();

    /**
     * execute method
     */
    protected abstract void execute();


    @Override
    public void appendLister(String name, AnalysisEventListener listener) {
        if (!listeners.containsKey(name)) {
            listeners.put(name, listener);
        }
    }

    @Override
    public void analysis(Sheet sheetParam) {
        execute();
    }

    @Override
    public void analysis() {
        execute();
    }

    /**
     */
    @Override
    public void cleanAllListeners() {
        listeners = new LinkedHashMap<String, AnalysisEventListener>();
    }

    @Override
    public void notifyListeners(OneRowAnalysisFinishEvent event) {
        analysisContext.setCurrentRowAnalysisResult(event.getData());
        /** Parsing header content **/
        if (analysisContext.getCurrentRowNum() < analysisContext.getCurrentSheet().getHeadLineMun()) {
            if (analysisContext.getCurrentRowNum() <= analysisContext.getCurrentSheet().getHeadLineMun() - 1) {
                analysisContext.buildExcelHeadProperty(null,
                    (List<String>)analysisContext.getCurrentRowAnalysisResult());
            }
        } else {
            List<String> content = converter((List<String>)event.getData());
            /** Parsing Analyze the body content **/
            analysisContext.setCurrentRowAnalysisResult(content);
            if (listeners.size() == 1) {
                analysisContext.setCurrentRowAnalysisResult(content);
            }
            /**  notify all event listeners **/
            for (Map.Entry<String, AnalysisEventListener> entry : listeners.entrySet()) {
                entry.getValue().invoke(analysisContext.getCurrentRowAnalysisResult(), analysisContext);
            }
        }
    }

    private List<String> converter(List<String> data) {
        List<String> list = new ArrayList<String>();
        if (data != null) {
            for (String str : data) {
                list.add(TypeUtil.formatFloat(str));
            }
        }
        return list;
    }

}

通过ExcelAnalyserImpl类的getSaxAnalyser()方法获取Excel是.xls还是.xlsx类型后，将ExcelAnalyserImpl的analysisContext属性赋给BaseSaxAnalyser类的analysisContext属性，再用appendListeners()方法封装默认的监听器和自定义的监听器到BaseSaxAnalyser类的listeners属性中，到此为止，BaseSaxAnalyser的两个属性：analysisContext、listeners已经分别存有file文件、excel枚举类型和监听器等值

接下来讲一个DefaultHandler类（核心类），该类是SAX事件解析器，底层在磁盘中解析文件并一行一行的读取，并且会依次执行startDocument()、startElement()、characters()、endElement()、endDocument()方法

package org.xml.sax.helpers;

import java.io.IOException;

import org.xml.sax.InputSource;
import org.xml.sax.Locator;
import org.xml.sax.Attributes;
import org.xml.sax.EntityResolver;
import org.xml.sax.DTDHandler;
import org.xml.sax.ContentHandler;
import org.xml.sax.ErrorHandler;
import org.xml.sax.SAXException;
import org.xml.sax.SAXParseException;

public class DefaultHandler
implements EntityResolver, DTDHandler, ContentHandler, ErrorHandler
{

public InputSource resolveEntity (String publicId, String systemId)
throws IOException, SAXException
{
return null;
}

public void notationDecl (String name, String publicId, String systemId)
throws SAXException
{
// no op
}

public void unparsedEntityDecl (String name, String publicId,
String systemId, String notationName)
throws SAXException
{
// no op
}

public void setDocumentLocator (Locator locator)
{
// no op
}

public void startDocument ()
throws SAXException
{
// no op
}

public void endDocument ()
throws SAXException
{
// no op
}

public void startPrefixMapping (String prefix, String uri)
throws SAXException
{
// no op
}

public void endPrefixMapping (String prefix)
throws SAXException
{
// no op
}

public void startElement (String uri, String localName,
String qName, Attributes attributes)
throws SAXException
{
// no op
}

public void endElement (String uri, String localName, String qName)
throws SAXException
{
// no op
}

public void characters (char ch[], int start, int length)
throws SAXException
{
// no op
}

public void ignorableWhitespace (char ch[], int start, int length)
throws SAXException
{
// no op
}

public void processingInstruction (String target, String data)
throws SAXException
{
// no op
}

public void skippedEntity (String name)
throws SAXException
{
// no op
}

public void warning (SAXParseException e)
throws SAXException
{
// no op
}

public void error (SAXParseException e)
throws SAXException
{
// no op
}

public void fatalError (SAXParseException e)
throws SAXException
{
throw e;
}

}

// end of DefaultHandler.java

package org.xml.sax.helpers;

import java.io.IOException;

import org.xml.sax.InputSource;
import org.xml.sax.Locator;
import org.xml.sax.Attributes;
import org.xml.sax.EntityResolver;
import org.xml.sax.DTDHandler;
import org.xml.sax.ContentHandler;
import org.xml.sax.ErrorHandler;
import org.xml.sax.SAXException;
import org.xml.sax.SAXParseException;



public class DefaultHandler
    implements EntityResolver, DTDHandler, ContentHandler, ErrorHandler
{

    public InputSource resolveEntity (String publicId, String systemId)
        throws IOException, SAXException
    {
        return null;
    }

    public void notationDecl (String name, String publicId, String systemId)
        throws SAXException
    {
        // no op
    }


    public void unparsedEntityDecl (String name, String publicId,
                                    String systemId, String notationName)
        throws SAXException
    {
        // no op
    }

    public void setDocumentLocator (Locator locator)
    {
        // no op
    }

    public void startDocument ()
        throws SAXException
    {
        // no op
    }

    public void endDocument ()
        throws SAXException
    {
        // no op
    }


    public void startPrefixMapping (String prefix, String uri)
        throws SAXException
    {
        // no op
    }

    public void endPrefixMapping (String prefix)
        throws SAXException
    {
        // no op
    }


    public void startElement (String uri, String localName,
                              String qName, Attributes attributes)
        throws SAXException
    {
        // no op
    }


    public void endElement (String uri, String localName, String qName)
        throws SAXException
    {
        // no op
    }


    public void characters (char ch[], int start, int length)
        throws SAXException
    {
        // no op
    }

    public void ignorableWhitespace (char ch[], int start, int length)
        throws SAXException
    {
        // no op
    }

    
    public void processingInstruction (String target, String data)
        throws SAXException
    {
        // no op
    }


    public void skippedEntity (String name)
        throws SAXException
    {
        // no op
    }

    public void warning (SAXParseException e)
        throws SAXException
    {
        // no op
    }

    public void error (SAXParseException e)
        throws SAXException
    {
        // no op
    }

    public void fatalError (SAXParseException e)
        throws SAXException
    {
        throw e;
    }

}

// end of DefaultHandler.java

通过XlsxRowHandler类继承DefaultHandler类，将每一行读取的行数和数据分别存入curCol和curRowContent属性中

读取完之后，通过后置方法endElement()方法，唤醒最初自己定义的监听器ExcelListener，并将读取到的行数、数据等赋给该监听器

package com.alibaba.excel.analysis.v07;

import com.alibaba.excel.annotation.FieldType;
import com.alibaba.excel.constant.ExcelXmlConstants;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventRegisterCenter;
import com.alibaba.excel.event.OneRowAnalysisFinishEvent;
import com.alibaba.excel.util.PositionUtils;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.xml.sax.Attributes;
import org.xml.sax.SAXException;
import org.xml.sax.helpers.DefaultHandler;

import java.util.Arrays;

import static com.alibaba.excel.constant.ExcelXmlConstants.*;

/**
*
* @author jipengfei
*/
public class XlsxRowHandler extends DefaultHandler {

private String currentCellIndex;

private FieldType currentCellType;

private int curRow;

private int curCol;

private String[] curRowContent = new String[20];

private String currentCellValue;

private SharedStringsTable sst;

private AnalysisContext analysisContext;

private AnalysisEventRegisterCenter registerCenter;

public XlsxRowHandler(AnalysisEventRegisterCenter registerCenter, SharedStringsTable sst,
AnalysisContext analysisContext) {
this.registerCenter = registerCenter;
this.analysisContext = analysisContext;
this.sst = sst;

}

@Override
public void startElement(String uri, String localName, String name, Attributes attributes) throws SAXException {

setTotalRowCount(name, attributes);

startCell(name, attributes);

startCellValue(name);

}

private void startCellValue(String name) {
if (name.equals(CELL_VALUE_TAG) || name.equals(CELL_VALUE_TAG_1)) {
// initialize current cell value
currentCellValue = "";
}
}

private void startCell(String name, Attributes attributes) {
if (ExcelXmlConstants.CELL_TAG.equals(name)) {
currentCellIndex = attributes.getValue(ExcelXmlConstants.POSITION);
int nextRow = PositionUtils.getRow(currentCellIndex);
if (nextRow > curRow) {
curRow = nextRow;
// endRow(ROW_TAG);
}
analysisContext.setCurrentRowNum(curRow);
curCol = PositionUtils.getCol(currentCellIndex);

String cellType = attributes.getValue("t");
currentCellType = FieldType.EMPTY;
if (cellType != null && cellType.equals("s")) {
currentCellType = FieldType.STRING;
}
}
}

private void endCellValue(String name) throws SAXException {
// ensure size
if (curCol >= curRowContent.length) {
curRowContent = Arrays.copyOf(curRowContent, (int)(curCol * 1.5));
}
if (CELL_VALUE_TAG.equals(name)) {

switch (currentCellType) {
case STRING:
int idx = Integer.parseInt(currentCellValue);
currentCellValue = new XSSFRichTextString(sst.getEntryAt(idx)).toString();
currentCellType = FieldType.EMPTY;
break;
}
curRowContent[curCol] = currentCellValue;
} else if (CELL_VALUE_TAG_1.equals(name)) {
curRowContent[curCol] = currentCellValue;
}
}

@Override
public void endElement(String uri, String localName, String name) throws SAXException {
endRow(name);
endCellValue(name);
}

@Override
public void characters(char[] ch, int start, int length) throws SAXException {
currentCellValue += new String(ch, start, length);
}

private void setTotalRowCount(String name, Attributes attributes) {
if (DIMENSION.equals(name)) {
String d = attributes.getValue(DIMENSION_REF);
String totalStr = d.substring(d.indexOf(":") + 1, d.length());
String c = totalStr.toUpperCase().replaceAll("[A-Z]", "");
analysisContext.setTotalCount(Integer.parseInt(c));
}

}

private void endRow(String name) {
if (name.equals(ROW_TAG)) {
registerCenter.notifyListeners(new OneRowAnalysisFinishEvent(curRowContent,curCol));
curRowContent = new String[20];
}
}

}

package com.alibaba.excel.analysis.v07;

import com.alibaba.excel.annotation.FieldType;
import com.alibaba.excel.constant.ExcelXmlConstants;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventRegisterCenter;
import com.alibaba.excel.event.OneRowAnalysisFinishEvent;
import com.alibaba.excel.util.PositionUtils;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.xml.sax.Attributes;
import org.xml.sax.SAXException;
import org.xml.sax.helpers.DefaultHandler;

import java.util.Arrays;

import static com.alibaba.excel.constant.ExcelXmlConstants.*;

/**
 *
 * @author jipengfei
 */
public class XlsxRowHandler extends DefaultHandler {

    private String currentCellIndex;

    private FieldType currentCellType;

    private int curRow;

    private int curCol;

    private String[] curRowContent = new String[20];

    private String currentCellValue;

    private SharedStringsTable sst;

    private AnalysisContext analysisContext;

    private AnalysisEventRegisterCenter registerCenter;

    public XlsxRowHandler(AnalysisEventRegisterCenter registerCenter, SharedStringsTable sst,
                          AnalysisContext analysisContext) {
        this.registerCenter = registerCenter;
        this.analysisContext = analysisContext;
        this.sst = sst;

    }

    @Override
    public void startElement(String uri, String localName, String name, Attributes attributes) throws SAXException {

        setTotalRowCount(name, attributes);

        startCell(name, attributes);

        startCellValue(name);

    }

    private void startCellValue(String name) {
        if (name.equals(CELL_VALUE_TAG) || name.equals(CELL_VALUE_TAG_1)) {
            // initialize current cell value
            currentCellValue = "";
        }
    }

    private void startCell(String name, Attributes attributes) {
        if (ExcelXmlConstants.CELL_TAG.equals(name)) {
            currentCellIndex = attributes.getValue(ExcelXmlConstants.POSITION);
            int nextRow = PositionUtils.getRow(currentCellIndex);
            if (nextRow > curRow) {
                curRow = nextRow;
                // endRow(ROW_TAG);
            }
            analysisContext.setCurrentRowNum(curRow);
            curCol = PositionUtils.getCol(currentCellIndex);

            String cellType = attributes.getValue("t");
            currentCellType = FieldType.EMPTY;
            if (cellType != null && cellType.equals("s")) {
                currentCellType = FieldType.STRING;
            }
        }
    }

    private void endCellValue(String name) throws SAXException {
        // ensure size
        if (curCol >= curRowContent.length) {
            curRowContent = Arrays.copyOf(curRowContent, (int)(curCol * 1.5));
        }
        if (CELL_VALUE_TAG.equals(name)) {

            switch (currentCellType) {
                case STRING:
                    int idx = Integer.parseInt(currentCellValue);
                    currentCellValue = new XSSFRichTextString(sst.getEntryAt(idx)).toString();
                    currentCellType = FieldType.EMPTY;
                    break;
            }
            curRowContent[curCol] = currentCellValue;
        } else if (CELL_VALUE_TAG_1.equals(name)) {
            curRowContent[curCol] = currentCellValue;
        }
    }

    @Override
    public void endElement(String uri, String localName, String name) throws SAXException {
        endRow(name);
        endCellValue(name);
    }

    @Override
    public void characters(char[] ch, int start, int length) throws SAXException {
        currentCellValue += new String(ch, start, length);
    }


    private void setTotalRowCount(String name, Attributes attributes) {
        if (DIMENSION.equals(name)) {
            String d = attributes.getValue(DIMENSION_REF);
            String totalStr = d.substring(d.indexOf(":") + 1, d.length());
            String c = totalStr.toUpperCase().replaceAll("[A-Z]", "");
            analysisContext.setTotalCount(Integer.parseInt(c));
        }

    }

    private void endRow(String name) {
        if (name.equals(ROW_TAG)) {
            registerCenter.notifyListeners(new OneRowAnalysisFinishEvent(curRowContent,curCol));
            curRowContent = new String[20];
        }
    }

}

此时回到ExcelListener监听器，便可以去取到每一行的数据存入datas属性中，并且对比导入的表头和模板表头是否一致等

到此，可以读到excel的每一行数据，用于业务处理。

------------------------------------------------------

posted @ 2022-07-07 21:49 hanease 阅读(720) 评论(0) 编辑收藏举报

刷新页面返回顶部

hanease

SpringBoot集成EasyExcel

快速使用

2. 对读取excel内容（批量添加）

3. 模板下载：

1、依赖

2、写入Excel

2.1 实体

2.2 实体的数据监听器

2.3 03版本的Excel写入

2.4 07版本的Excel 写入

2.5 写入多个sheet

3、读出Excel

3.1 doRead()

3.1.1 sheet1中的数据

3.1.2 doRead() 方法读取

3.1.3 测试结果：

3.1.4 分析 doRead() 方法

3.2 doReadSync()

3.2.1 sheet2中的数据

3.2.2 doReadSync() 方法读取

3.2.3 测试结果：

3.2.4 分析 doReadSync() 方法

EasyExcel 专题(二) 深度解析读流程核心源码

公告