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的每一行数据，用于业务处理。

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

相关阅读:
angularjs加载html
git 使用
 图片压缩原理讲解很通透
 angularjs 实现多个图片上传及预览
 HTML 空格转义符的用法
 docker-volumes
docker-管理数据
 docker-代理服务器
 docker-none
docker-macvlan
原文地址：https://www.cnblogs.com/hanease/p/16456273.html