EasyExcel 操作

EasyExcel 操作

EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。在尽可能节约内存的情况下支持读写百M的Excel。 github地址:https://github.com/alibaba/easyexcel

EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。在尽可能节约内存的情况下支持读写百M的Excel。 github地址:https://github.com/alibaba/easyexcel
EasyExcel 在线文档： https://www.yuque.com/easyexcel/doc/easyexcel

版本支持

2+ 版本java7

3+ 版本 Java 8

不建议项目跨版本升级

导入依赖

<dependency>
   <groupId>com.alibaba</groupId>
   <artifactId>easyexcel</artifactId>
   <version>2.1.7</version>
</dependency>
<!-- lombok--> 
<dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.22</version>
            <scope>provided</scope>
        </dependency>

提示： easyExcel 和 poi 如果同时存在，请检查版本，否则会有版本冲突问题

lombok 需要在 idea 上下载 lombok 插件才可以使用

简单的写

@Test
public void easyExcelWrite(){
   List<Student> studentList = new ArrayList<>();
   for (int i = 0; i < 20; i++) {
      Student student = new Student();
      student.setName("二志"+i);
      student.setAge(i);
      student.setPhoneNum("134000000"+i);
      studentList.add(student);
   }
   EasyExcel.write(path+"测试.xlsx",Student.class).sheet().doWrite(studentList);
}

简单的读

public void easyExcelRead(){
      String path = this.path + "测试.xlsx";
      /**
       * 第一个参数是 路径
       * 第二个参数是 Excel 中行的实体类
       * 第三个参数 是监听器
       */
//    获得一个工作薄对象
      ExcelReaderBuilder readWorkBook = EasyExcel.read(path, Student.class, new ExcelListener());
//    获得一个工作表对象
      ExcelReaderSheetBuilder sheet = readWorkBook.sheet();
      /*开始读取，doRead 方法会自动关闭流*/
      sheet.doRead();
   }

监听器代码

public class ExcelListener extends AnalysisEventListener<Student> {
    /**
     * 读监听器，每读一行，都会调用一次该对象的 invoke,在 invoke 可以操作使用读取到的数据
     * @param student 读取到的封装类
     * @param analysisContext
     */
    @Override
    public void invoke(Student student, AnalysisContext analysisContext) {
        System.out.println(student);
    }
    /**
     * 读取完每个 sheet 调用
     * @param analysisContext
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
    }
}

实体类

@Data
public class Student {
    /*@ExcelProperty
    * value 表示的是字段对应的列名
    * index 是对应的位置 */
    @ExcelProperty("学生名字")
    private String name;
    @ExcelProperty("学生年龄")
    private int age;
    @ExcelProperty("手机号")
    private String phoneNum;
    /*可以忽略的属性*/
    @ExcelIgnore
    private String ignore;
}

常用的API 注解

1、常用类

EasyExcel 入口类，用于构建各种对象，开始各种操作

ExcelReaderBuilder 构建出一个 ReadWorkBook 对象，即一个工作薄对象，对应的是一个Excel 文件

ExcelWriterBuilder 构建出一个 WriteWorkBook 对象，即一个工作薄对象，对应的是一个 Excel 文件

ExcelReaderSheetBuilder 构建出一个 ReadSheet 对象，即一个工作表的对象，对应的Excel 中的每个sheet ，一个工作薄可以有多个工作表

ExcelWriterSheetBuilder 构建出一个WriteSheet对象，即一个工作表的对象，对应的Excel 中的每个sheet ，一个工作薄可以有多个工作表

ReadListener 在每一行读取完毕后都会调用 ReadListener 来处理数据，我们可以把调用service 的代码写在 invoke 方法内部

WriteHandler 在每一个操作包括创建单元格，创建表格等会调用 WriteHandler 来处理数据，对使用者透明不可建

所有配置都是继承的， Workbook的配置会被 sheet 继承，所以在用EasyExcel 设置参数的时候，在EasyExcel sheet()方法之前作用域是整个Wrokbook 的所有 sheet, 之后针对单个 sheet.

读取时的注解

@ExcelProperty

使用位置标准作用在成员变量上

可选属性

属性名	含义	说明
Index	对应Excel 表中的列数	默认-1 建议从0开始
value	对象Excel 表中的列头
converter	成员变量转换器	自定义转换器需要实现Converter接口

使用效果： Index 属性可以制定当前字段对应excel 中的哪一列，可以根据列名 value 去匹配，也可以不写。如果不使用 @ExcelProperty 注解，成员变量从上到下的顺序，对应表格中从左到右的顺序

使用建议：要么全部不写，要么全部index, 要么全部用 value

@ExcelIgnore

位置：标注在成员变量上，默认所有字段的都会和exxcel 去匹配，加了这个注解会忽略改字段

@DateTimeFormat

标注在成员变量上，日期转换，代码中用String 类型的成员变量去接受 excel 中的日期格式的数据，会调用这个注解

@NumberFormat

标注在成员变量上，数字转换，代码中用 String 类型的成员变量去接受excel 数字格式的数据，会调用和这个注解

@ExcelIgnoreUnannotated

标注在类上，默认类中所有成员变量都会参入读写，无论是否在成员变量上加了@ExcelProperty的注解

标记该注解后，类中的成员变量如果没有标注@ExcelProperty 注解将不会参入读写

读取时通用参数

ReadWorkBook,ReadSheet 都会有的参数，如果为空，默认使用上级

converter 转换器，默认加载了很多转换器，也可以自定义

readLister 监听器，在读取数据的过程中，会不断的调用监听器

headRowNumber 指定需要读表格的列头行数，默认有一行头，也就是认为第二行开始起为数据

head clazz 二选一，读取文件头对应的列表，会根据列表匹配数据，建议使用class，就是文件中每一行数据对应的代码中的实体类型

head clazz 二选一 ,读取文件的头对应的 class ，也可以使用注解，如果两个都不指定，则会读取全部数据

autoTrim 字符串，表头等数据自动 trim

password 读的时候是否需要使用密码。

读取全部的sheet

String path = this.path + "测试.xlsx";
EasyExcel.read(path,Student.class,new ExcelListener()).doReadAll();

读取部分的 sheet

    public void easyExcelReadPart(){
      ExcelReader workBook = null;
      try {
         String path = this.path+ "测试.xlsx";
//    工作薄对象
         workBook = EasyExcel.read(path).build();
//    这里为了简单 所以注册了 同样的head 和Listener 自己使用功能必须不同的Listener
         ReadSheet sheet1 = EasyExcel.readSheet(0).head(Student.class).registerReadListener(new ExcelListener()).build();
         ReadSheet sheet2 = EasyExcel.readSheet(1).head(Student.class).registerReadListener(new ExcelListener()).build();
//    一定要把sheet1 sheet2 一起传进去，不然有个问题就是03版的excel 会读取多次，浪费性能
         workBook.read(sheet1,sheet2);
      } finally {
//       这里千万别忘记关闭，读的时候会创建临时文件，到时磁盘会崩的
         workBook.finish();
      }
   }

对实体类的日期进行格式化

@DateTimeFormat("yyyy-MM-dd")
@ExcelProperty("出生日期")
private Date birthday;

自定义转换器

@ExcelProperty(value = "学生名字",converter = ExcelConverter.class)
private String name;

转换器类,可以继承他的实现类

public class ExcelConverter implements Converter<String> {
    @Override
    public Class<?> supportJavaTypeKey() {
        return String.class;
    }
    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }
    /**
     * 这里读的时候会调用这个方法
     * @param context
     * @return
     * @throws Exception
     */
    @Override
    public String convertToJavaData(ReadConverterContext<?> context) throws Exception {
        return "自定义"+context.getReadCellData().getStringValue();
    }
    @Override
    public WriteCellData<?> convertToExcelData(String value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        return null;
    }
    @Override
    public WriteCellData<?> convertToExcelData(WriteConverterContext<String> context) throws Exception {
        return null;
    }

输出实例

Student(name=自定义二志0, age=0, phoneNum=1340000000, ignore=null, birthday=null)
Student(name=自定义二志1, age=1, phoneNum=1340000001, ignore=null, birthday=null)

第二种方式：

    public void easyExcelReadConverter(){
        String path = this.path + "测试.xlsx";
        EasyExcel.read(path,Student.class,new ExcelListener())
//                也可以这里进行注册转换器,全局进行读取，所有的String 类型都会进行转换，测试出现问题，不建议这样
                .registerConverter(new ExcelConverter())
                .sheet().doRead();
    }

多行头读

  public void ExcelReader(){
        String path = "D:\\CloudMusic\\测试写1.xlsx";
        EasyExcel.read(path, Student.class,new ExcelListener()).sheet()
//                表头多行，一定要注意.headRowNumber设置表头的行数，否则读不出来数据
                .headRowNumber(2).doRead();
    }

同步的读

 public void ExcelReader2(){
        String path = "D:\\CloudMusic\\测试写1.xlsx";
        EasyExcel.read(path, Student.class,new ExcelListener()).sheet()
//                表头多行，一定要注意.headRowNumber设置表头的行数，否则读不出来数据
//               doReadSync(); 表示同步去读
//        同步的返回，不推荐使用，如果数据量大会把数据放到内存里面
                .headRowNumber(2).doReadSync();
    }

数据转换等异常处理

需要重写监听器中的onException方法

子类可以不抛出或者只能抛出比父类小的异常

测试了下，转换异常会抛出，不会继续向下读

 /**
     *  在转换异常 获取其他异常下会调用本接口。抛出异常则停止读取。如果这里不抛出异常则 继续读取下一行。
     * @param exception
     * @param context
     * @throws Exception
     */
    @Override
    public void onException(Exception exception, AnalysisContext context)  {
//        如果是 excel类型转换错误
        if(exception instanceof  ExcelDataConvertException){
            exception.printStackTrace();
            ExcelDataConvertException exception1 = (ExcelDataConvertException) exception;
            Integer rowIndex = exception1.getRowIndex();
            Integer columnIndex = exception1.getColumnIndex();
            CellData<?> cellData = exception1.getCellData();
            System.out.println(rowIndex+","+columnIndex);
            System.out.println("数据为"+cellData);
        }
    }

web中的读

@PostMapping("/files")
//MultipartFile  这个类是 spring
public void files(MultipartFile file) throws IOException {
    EasyExcel.read(file.getInputStream(),Student.class,new ExcelListener()).doReadAll();
}

写

简单的写

第一种方式

 public void EasyExcelSimpleWriter(){
        String path = "D:\\CloudMusic\\测试写.xlsx";
//        注意 simpleWrite在数据量不大的情况下可以使用（5000以内，具体也要看实际情况）
        EasyExcel.write(path, Student.class).sheet("模板")
                .doWrite(() -> {return data();});
    }
// 两种方式区别只是将方法放进去了
 public void EasyEcelSimpleWriter2(){
        String path = "D:\\CloudMusic\\测试写.xlsx";
//        注意 simpleWrite在数据量不大的情况下可以使用（5000以内，具体也要看实际情况）
        EasyExcel.write(path, Student.class).sheet("模板")
                .doWrite(data());
    }

初始化数据

private Collection<?> data() {
    List<Student> studentList = new ArrayList<>();
    for (int i = 0; i < 10; i++) {
        Student student = new Student();
        student.setName("二志"+i);
        student.setAge(i);
        student.setBirthday(new Date());
        student.setPhoneNum("11111"+i);
        studentList.add(student);
    }
    return studentList;
}

第三种写

 public void EasyEcelSimpleWriter3(){
        String path = "D:\\CloudMusic\\测试写.xlsx";
//        注意 simpleWrite在数据量不大的情况下可以使用（5000以内，具体也要看实际情况）
//        工作薄
        ExcelWriter workbook = null;
        try {
            workbook = EasyExcel.write(path, Student.class).build();
//        工作表
            WriteSheet sheet = EasyExcel.writerSheet("模板").build();
            workbook.write(data(), sheet);
        } finally {
            if (workbook != null) {
                workbook.finish();
            }
        }
    }

写指定的列

   public void EasyExcelExclude(){
        String path = "D:\\CloudMusic\\测试写1.xlsx";
        Set<String> excludeFiled = new HashSet<>();
//       TODO 注意 填写的必须是类中的成员变量名称
        excludeFiled.add("birthday");
        /*excludeColumnFiledNames  这个是忽略*/
        /*includeColumnFiledNames()  这个是指定*/
        EasyExcel.write(path,Student.class).sheet().excludeColumnFiledNames(excludeFiled).doWrite(data());
    }

复杂的头写入

@ExcelProperty({"主标题","姓名"})
private String name;
@ExcelProperty({"主标题","年龄"})
private int age;
@ExcelProperty({"主标题","手机"})
private String phoneNum;
/*可以忽略的属性*/
@ExcelIgnore
private String ignore;
@DateTimeFormat("yyyy-MM-dd")
@ExcelProperty({"主标题","出生日期"})
private Date birthday;

@HeadStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 10) 表头背景设置为红色
@HeadFontStyle(fontHeightInPoints = 20) 表头字体设置为 20

@ContentStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 17) 内容背景设置为绿色

@ContentFontStyle(fontHeightInPoints = 20) 内容字体设置为 20

可以在实体类上，全局使用，也可以成员变量上。

@HeadRowHeight(20) 行高

@ColumnWidth(25) 列宽

@ContentRowHeight(10) 内容行高

不创建对象的写

标题放到List中

private List<List<String>> head(){
    List<List<String>> headList = new ArrayList<>();
    List<String> head1 = new ArrayList<>();
    head1.add("行么");
    List<String> head2 = new ArrayList<>();
    head2.add("年龄");
    List<String> head3 = new ArrayList<>();
    head3.add("手机");
    List<String> head4 = new ArrayList<>();
    head4.add("日期");
    Collections.addAll(headList,head1,head2,head3,head4);
    return headList;
}

public void EasyExcelNotObject(){
    String path = "D:\\CloudMusic\\测试写3.xlsx";
    EasyExcel.write(path).head(head()).sheet().doWrite(data());
}

Web 中的写

@GetMapping("/download")
public void doenload(HttpServletResponse response) throws IOException {
    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    response.setCharacterEncoding("UTF-8");
    String fileName = URLEncoder.encode("测试1","UTF-8").replaceAll("\\+","%20");
    response.setHeader("Content-disposition", "attachment;filename*=utf-8" + fileName + ".xlsx");
    EasyExcel.write(response.getOutputStream(), Student.class).sheet("模板").doWrite(data());
}

下载出错返回json 响应码

   public void doenload(HttpServletResponse response) throws IOException {
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("UTF-8");
        try {
            String fileName = URLEncoder.encode("测试1","UTF-8").replaceAll("\\+","%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8" + fileName + ".xlsx");
            EasyExcel.write(response.getOutputStream(), Student.class).
                    //设置不需要自动关闭流
                    autoCloseStream(Boolean.FALSE).
                    sheet("模板").doWrite(data());
        } catch (IOException e) {
            e.printStackTrace();
//            重置    response
            response.reset();
            response.setContentType("application/json");
            response.setCharacterEncoding("UTF-8");
            Map<String,String> map = new HashMap<>();
            map.put("status","failure");
            map.put("message","文件下载失败"+e.getMessage());
            response.getWriter().write(new ObjectMapper().writeValueAsString(map));
        }