EasyExcel读写及填充

EasyExcel官方文档:https://www.yuque.com/easyexcel/doc/easyexcel.

初识EasyExcel

传统POI的内存消耗较大

特点

  • 功能强大
  • 代码书写冗余繁杂
  • 读写大文件耗费内存较大,容易OOM

EasyExcel

重写了POI对07版Excel的解析

  • EasyExcel重写了POI对07版Excel的解析,可以把内存消耗从100M左右降低到10M以内,并且再大的Excel不会出现内存溢出,03版仍依赖POI的SAX模式。
    下图为64M内存1分钟内读取75M(46W行25列)的Excel(当然还有急速模式能更快,但是内存占用会在100M多一点)
    EasyExcel读写及填充
  • 在上层做了模型转换的封装,让使用者更加简单方便
    特点
  • 在数据模型层面进行了封装,使用简单
  • 重写了07版本的Excel的解析代码,降低内存消耗,能有效避免OOM
  • 只能操作Excel
  • 不能读取图片

一、使用EasyExcel写入

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.2.1.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.jxsl</groupId>
    <artifactId>exceldemo</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>exceldemo</name>
    <description>Demo project for Spring Boot</description>
    <properties>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-thymeleaf</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.2.3</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.27</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.4</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.0.5</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.78</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

application.yml

server:
  port: 8887

#\u6570\u636E\u6E90
spring:
  datasource:
    druid:
      url: jdbc:mysql://localhost:3306/excel?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT
      driver-class-name: com.mysql.cj.jdbc.Driver
      username: root
      password: 123456
      initial-size: 10
      max-active: 50
      min-idle: 10
      max-wait: 60000
      pool-prepared-statements: true
      max-pool-prepared-statement-per-connection-size: 20
      # mysql \u6570\u636E\u5E93\u7684\u7279\u5F81  \u4F1A\u5173\u95ED\u5DF2\u7ECF\u8FDE\u63A5\u4E868\u4E2A\u5C0F\u65F6\u7684\u8FDE\u63A5
      validation-query: SELECT 1 FROM DUAL

# mybatis 配置
mybatis:
  # 扫描映射文件
  mapper-locations: classpath:mapper/*.xml
  configuration:
    # 开启驼峰映射配置
    map-underscore-to-camel-case: true

# log
# 整个工程只答应info 以及以上级别的日志
logging:
  level:
    root: info
    com.jsxl.mapper: debug

EasyExcel读写及填充
实体类

package com.jsxl.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.util.Date;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class DemoData {
    private Integer id;

    private String string;

    private Date date;

    private Double doubleData;
}

TestWrite

    @Test
    public void simpleWrite() {

//jdk1.8
        EasyExcel.write(path, DemoData.class)
                .sheet("模板")
                .doWrite(() -> {
                    // 分页查询数据
                    return demoDataMapper.selectByExample(null);
                });
//        // 写法2
//        // 这里 需要指定写用哪个class去写
//        ExcelWriter excelWriter = null;
//        try {
//            excelWriter = EasyExcel.write(path, DemoData.class).build();
//            WriteSheet writeSheet = EasyExcel.writerSheet("模板").build();
//            excelWriter.write(data(), writeSheet);
//        } finally {
//            // 千万别忘记finish 会帮忙关闭流
//            if (excelWriter != null) {
//                excelWriter.finish();
//            }
//        }
    /**
     * 根据参数只导出指定列
     * 1. 创建excel对应的实体对象 参照{@link DemoData}
     * 2. 根据自己或者排除自己需要的列
     * 3. 直接写即可
     */
     
    @Test
    void excludeOrIncludeWrite() {
        String fileName = path + "excludeOrIncludeWrite" + System.currentTimeMillis() + ".xlsx";
        // 这里需要注意 在使用ExcelProperty注解的使用,如果想不空列则需要加入order字段,而不是index,order会忽略空列,然后继续往后,而index,不会忽略空列,在第几列就是第几列。

        // 根据用户传入字段 假设我们要忽略 date
        Set<String> excludeColumnFiledNames = new HashSet<String>();
        excludeColumnFiledNames.add("date");
        // 这里 需要指定写用哪个class去写,然后写到第一个sheet,名字为模板 然后文件流会自动关闭
        EasyExcel.write(fileName, DemoData.class).excludeColumnFiledNames(excludeColumnFiledNames).sheet("模板")
                .doWrite(demoDataMapper.selectByExample(null));

        fileName = path + "excludeOrIncludeWrite" + System.currentTimeMillis() + ".xlsx";
        // 根据用户传入字段 假设我们只要导出 date
        Set<String> includeColumnFiledNames = new HashSet<String>();
        includeColumnFiledNames.add("date");
        // 这里 需要指定写用哪个class去写,然后写到第一个sheet,名字为模板 然后文件流会自动关闭
        EasyExcel.write(fileName, DemoData.class).includeColumnFiledNames(includeColumnFiledNames).sheet("模板")
                .doWrite(demoDataMapper.selectByExample(null));
    }

EasyExcel读写及填充
指定写入的列

    @ExcelProperty(value = "数字标题", index = 3)
    private Double doubleData;

复杂头写入
EasyExcel读写及填充

@Getter
@Setter
@EqualsAndHashCode
public class ComplexHeadData {
    @ExcelProperty({"主标题", "字符串标题"})
    private String string;
    @ExcelProperty({"主标题", "日期标题"})
    private Date date;
    @ExcelProperty({"主标题", "数字标题"})
    private Double doubleData;
}

注解ColumnWidth()

/**
 * Set the width of the table
 *
 * @author Jiaju Zhuang
 */
@Target({ElementType.FIELD, ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
@Inherited//@Inherited修饰的注解的@Retention是RetentionPolicy.RUNTIME,则增强了继承性,在反射中可以获取得到
public @interface ColumnWidth {

    /**
     * Column width
     * 默认返回-1
     * -1 means the default co
     */
     // 返回列的宽度
    int value() default -1;
}

注解ExcelProperty

@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
@Inherited
public @interface ExcelProperty {

    /**
     * 工作表标题的名称。
     * write: 当你有多个头时,它会自动合并
     * read: 当你有多个头时,选最后一个
     * @return The name of the sheet header
     */
    String[] value() default {""};

    /**
     * 返回当前列的索引
     *读取或写入列的索引,如果它等于-1,它是按Java类排序。
     * priority: index &gt; order &gt; default sort
     *
     * @return Index of column
     */
    int index() default -1;

    /**
     * 定义列的排序顺序。
     * priority: index &gt; order &gt; default sort
     * @return Order of column
     */
    int order() default Integer.MAX_VALUE;

    /**
     * 强制当前字段使用这个转换器
     * @return Converter
     */
    Class<? extends Converter<?>> converter() default AutoConverter.class;

    /**
     * 如果默认格式不满足,可以设置格式
     * @return Format string
     * @deprecated please use {@link com.alibaba.excel.annotation.format.DateTimeFormat}
     */
    @Deprecated
    //若某类或某方法加上该注解之后,表示此方法或类不再建议使用,调用时也会出现删除线,但并不代表不能用,只是说,不推荐使用,因为还有更好的方法可以调用。
    String format() default "";
}

注解ExcelIgnore

/**
 * 忽略将该字段
 */
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
@Inherited
public @interface ExcelIgnore {}

二、使用EasyExcel读出

新增一个接口

int save(@Param("list") List<DemoData> list);

Mapper.xml

  <insert id="save" parameterType="java.util.List">
    insert into excel.demo_data (string,date,double_data)
    values
    <foreach collection="list" item="item" separator=",">
      (#{item.string}, #{item.date},#{item.doubleData})
    </foreach>
  </insert>

这里需要一个监听器,注解这个监听器不能被spring管理

package com.jsxl.read;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.util.ListUtils;
import com.jsxl.mapper.DemoDataMapper;
import com.jsxl.pojo.DemoData;
import lombok.extern.slf4j.Slf4j;

import java.util.List;


@Slf4j
// 有个很重要的点 DemoDataListener 不能被spring管理,要每次读取excel都要new,然后里面用到spring可以构造方法传进去
public class DemoDataListener implements ReadListener<DemoData> {
    
    /**
     *  引入接口
     */
    DemoDataMapper demoDataMapper;

    /**
     *  我们通过构造函数从外部传入接口
     * @param demoDataMapper
     */
    public DemoDataListener(DemoDataMapper demoDataMapper){
        this.demoDataMapper = demoDataMapper;
    }
    
    /**
     * 每隔5条存储数据库,实际使用中可以100条,然后清理list ,方便内存回收
     */
    private static final int BATCH_COUNT = 100;
    /**
     * 缓存的数据
     */
    private List<DemoData> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
    /**
     * 假设这个是一个DAO,当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。
     */

    /**
     * 这个每一条数据解析都会来调用
     */
    @Override
    public void invoke(DemoData data, AnalysisContext context) {
        // 达到BATCH_COUNT了,需要去存储一次数据库,防止数据几万条数据在内存,容易OOM
        cachedDataList.add(data);
        System.out.println(cachedDataList.size());
        if (cachedDataList.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理 list
            cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
        }
    }

    /**
     * 所有数据解析完成了 都会来调用
     *
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 这里也要保存数据,确保最后遗留的数据也存储到数据库
        saveData();
        log.info("所有数据解析完成!");
    }

    /**
     * 加上存储数据库
     */
    private void saveData() {
        log.info("{}条数据,开始存储数据库!", cachedDataList.size());
        demoDataMapper.save(cachedDataList);
        log.info("存储数据库成功!");
    }
}

三、填充

​ Excel表格中用{} 来表示包裹要填充的变量,如果单元格文本中本来就有{,}左右大括号,需要在括号前面使用斜杠转义{,}。

​ 代码中被填充数据的实体对象的成员变量名或被填充map集合的key需要和Excel中被{}包裹的变量名称一致。

3.1 简单填充

EasyExcel读写及填充
实体类

@Getter
@Setter
@EqualsAndHashCode
public class FillData {
    private String name;
    private double number;
    private Date date;
}

测试

  // 模板注意 用{} 来表示你要用的变量 如果本来就有"{","}" 特殊字符 用"\{","\}"代替
    private static final String templateFileName1 = "D:\\github\\exceldemo\\模板1.xlsx";
    private static final String fileName1="D:\\github\\exceldemo\\simpleFill1.xlsx";

    /**
     * 最简单的填充
     */
    @Test
    public void simpleFill() {

        // 方案1 根据对象填充
        // 这里 会填充到第一个sheet, 然后文件流会自动关闭
        FillData fillData = new FillData();
        fillData.setName("张三");
        fillData.setNumber(5.2);
        EasyExcel.write(fileName1).withTemplate(templateFileName1).sheet().doFill(fillData);

        // 方案2 根据Map填充
        // 这里 会填充到第一个sheet, 然后文件流会自动关闭
//        Map<String, Object> map = new HashMap<String, Object>();
//        map.put("name", "张三");
//        map.put("number", 5.2);
//        EasyExcel.write(fileName1).withTemplate(templateFileName1).sheet().doFill(map);
    }

3.2 填充列表

EasyExcel读写及填充
测试

    @Test
    public void listFill() {
        // 填充list 的时候还要注意 模板中{.} 多了个点 表示list

        // 方案1 一下子全部放到内存里面 并填充
        // 这里 会填充到第一个sheet, 然后文件流会自动关闭
        EasyExcel.write(fileName2).withTemplate(templateFileName2).sheet().doFill(data());

        // 方案2 分多次 填充 会使用文件缓存(省内存) jdk8
        // since: 3.0.0-beta1
//        EasyExcel.write(fileName2)
//                .withTemplate(templateFileName2)
//                .sheet()
//                .doFill(() -> {
//                    // 分页查询数据
//                    return data();
//                });
//
//        // 方案3 分多次 填充 会使用文件缓存(省内存)
//        ExcelWriter excelWriter = EasyExcel.write(fileName2).withTemplate(templateFileName2).build();
//        WriteSheet writeSheet = EasyExcel.writerSheet().build();
//        excelWriter.fill(data(), writeSheet);
//        excelWriter.fill(data(), writeSheet);
//        // 千万别忘记关闭流
//        excelWriter.finish();
    }

    public List<FillData> data(){
        List<FillData> list = Lists.newArrayList();
        for (int i = 0; i < 10 ; i++){
            FillData fillData = new FillData();
            fillData.setName("name"+i);
            fillData.setNumber(Math.random()*100);
            fillData.setDate(new Date());
            list.add(fillData);
        }
        return list;
    }

3.3 数据量大的复杂填充

EasyExcel读写及填充

 private static final String templateFileName3 = "D:\\github\\exceldemo\\模板3.xlsx";
    private static final String fileName3="D:\\github\\exceldemo\\simpleFill3.xlsx";

    /**
     * 数据量大的复杂填充
     * 这里的解决方案是 确保模板list为最后一行,然后再拼接table.还有03版没救,只能刚正面加内存。
     * @since 2.1.1
     */
    @Test
    public void complexFillWithTable() {
        // 模板注意 用{} 来表示你要用的变量 如果本来就有"{","}" 特殊字符 用"\{","\}"代替
        // {} 代表普通变量 {.} 代表是list的变量
        // 这里模板 删除了list以后的数据,也就是统计的这一行
        ExcelWriter excelWriter = EasyExcel.write(fileName3).withTemplate(templateFileName3).build();
        WriteSheet writeSheet = EasyExcel.writerSheet().build();
        // 直接写入数据
        excelWriter.fill(data(), writeSheet);
        excelWriter.fill(data(), writeSheet);

        // 写入list之前的数据
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("date", new SimpleDateFormat("yyyy年MM月dd日HH:mm:ss").format(new Date()));
        excelWriter.fill(map, writeSheet);

        // list 后面还有个统计 想办法手动写入
        // 这里偷懒直接用list 也可以用对象
        List<List<String>> totalListList = new ArrayList<List<String>>();
        List<String> totalList = new ArrayList<String>();
        totalListList.add(totalList);
        totalList.add(null);
        totalList.add(null);
        totalList.add(null);
        // 第四列
        totalList.add("统计:1000");
        // 这里是write 别和fill 搞错了
        excelWriter.write(totalListList, writeSheet);
        excelWriter.finish();
        // 总体上写法比较复杂 但是也没有想到好的版本 异步的去写入excel 不支持行的删除和移动,也不支持备注这种的写入,所以也排除了可以
        // 新建一个 然后一点点复制过来的方案,最后导致list需要新增行的时候,后面的列的数据没法后移,后续会继续想想解决方案
    }

3.4 横向的填充

EasyExcel读写及填充

private static final String templateFileName4 = "D:\\github\\exceldemo\\模板4.xlsx";
    private static final String fileName4="D:\\github\\exceldemo\\simpleFill4.xlsx";
    /**
     * 横向的填充
     *
     * @since 2.1.1
     */
    @Test
    public void horizontalFill() {
        // 模板注意 用{} 来表示你要用的变量 如果本来就有"{","}" 特殊字符 用"\{","\}"代替
        // {} 代表普通变量 {.} 代表是list的变量
        ExcelWriter excelWriter = EasyExcel.write(fileName4).withTemplate(templateFileName4).build();
        WriteSheet writeSheet = EasyExcel.writerSheet().build();
        FillConfig fillConfig = FillConfig.builder().direction(WriteDirectionEnum.HORIZONTAL).build();
        excelWriter.fill(data(), fillConfig, writeSheet);
        excelWriter.fill(data(), fillConfig, writeSheet);

        Map<String, Object> map = new HashMap<String, Object>();
        map.put("date",  new SimpleDateFormat("yyyy年MM月dd日HH:mm:ss").format(new Date()));
        excelWriter.fill(map, writeSheet);

        // 别忘记关闭流
        excelWriter.finish();
    }

3.5 多列表组合填充填充

EasyExcel读写及填充

    private static final String templateFileName5 = "D:\\github\\exceldemo\\模板5.xlsx";
    private static final String fileName5="D:\\github\\exceldemo\\simpleFill5.xlsx";
    /**
     * 多列表组合填充填充
     *
     * @since 2.2.0-beta1
     */
    @Test
    public void compositeFill() {
        // 模板注意 用{} 来表示你要用的变量 如果本来就有"{","}" 特殊字符 用"\{","\}"代替
        // {} 代表普通变量 {.} 代表是list的变量 {前缀.} 前缀可以区分不同的list
        ExcelWriter excelWriter = EasyExcel.write(fileName5).withTemplate(templateFileName5).build();
        WriteSheet writeSheet = EasyExcel.writerSheet().build();
        FillConfig fillConfig = FillConfig.builder().direction(WriteDirectionEnum.HORIZONTAL).build();
        // 如果有多个list 模板上必须有{前缀.} 这里的前缀就是 data1,然后多个list必须用 FillWrapper包裹
        excelWriter.fill(new FillWrapper("data1", data()), fillConfig, writeSheet);
        excelWriter.fill(new FillWrapper("data2", data()), writeSheet);
        excelWriter.fill(new FillWrapper("data3", data()), writeSheet);

        Map<String, Object> map = new HashMap<String, Object>();
        map.put("date", new SimpleDateFormat("yyyy年MM月dd日HH:mm:ss").format(new Date()));
        excelWriter.fill(map, writeSheet);

        // 别忘记关闭流
        excelWriter.finish();
    }

FillConfig类

@Getter
@Setter
@EqualsAndHashCode
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class FillConfig {
    private WriteDirectionEnum direction;
    /**
     * 每次使用list参数时创建一个新行。如果需要,默认创建。
     * <p>
     * Warnning:If you use <code>forceNewRow</code> set true, will not be able to use asynchronous write file, simply
     * say the whole file will be stored in memory.
     */
    private Boolean forceNewRow;

    /**
     * Automatically inherit style
     * default true.
     */
    private Boolean autoStyle;

    private boolean hasInit;

    public void init() {
        if (hasInit) {
            return;
        }
        if (direction == null) {
            direction = WriteDirectionEnum.VERTICAL;
        }
        if (forceNewRow == null) {
            forceNewRow = Boolean.FALSE;
        }
        if (autoStyle == null) {
            autoStyle = Boolean.TRUE;
        }
        hasInit = true;
    }
}
上一篇:Spring Boot + EasyExcel 导入导出,好用到爆,含泪狂刷Java基础面试118题


下一篇:easyExcel的使用小结