SpringBoot整合EasyExcel

1.Excel导入导出的应用场景

在做项目中很多时候都会用到Excel的导入和导出

2.解决方案

POI:操作比较繁琐

EasyExcel:正如其名,'Easy'Excel相对于POI使用起来还是比较简单的

3.EasyExcel使用步骤

创建一个简单的Maven项目

导入相关依赖

<?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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <parent>
        <artifactId>01_lesson_address</artifactId>
        <groupId>com.qbb.lesson</groupId>
        <version>1.0-SNAPSHOT</version>
        <relativePath>../01_lesson_address/pom.xml</relativePath>
    </parent>
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.qbb</groupId>
    <artifactId>alibaba_excel</artifactId>

    <dependencies>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.1.7</version>
        </dependency>

        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-simple</artifactId>
            <version>1.7.5</version>
        </dependency>

        <dependency>
            <groupId>org.apache.xmlbeans</groupId>
            <artifactId>xmlbeans</artifactId>
            <version>3.1.0</version>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.10</version>
        </dependency>

        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>

    </dependencies>

</project>

创建一个实体类

package com.qbb.alibaba.excel.entity;

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.alibaba.excel.annotation.format.NumberFormat;
import lombok.Data;

import java.util.Date;

/**
 * @author QiuQiu&LL
 * @create 2021-07-07  23:27
 * @Description:
 */
@Data
public class ExcelStudentData {

    @ExcelProperty(value = "姓名",index = 1)
    private String name;

    @DateTimeFormat("yyyy年MM月dd日 HH时mm分ss秒")
    @ExcelProperty(value = "生日",index = 2)
    private Date birthday;

    @NumberFormat("#.##%")
    @ExcelProperty(value = "薪资",index = 3)
    private Double salary;

    /**
     * 忽略这个字段
     */
    @ExcelIgnore
    private String password;
}

穿件一个监听器listener

package com.qbb.alibaba.excel.listener;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.qbb.alibaba.excel.entity.ExcelStudentData;
import lombok.extern.slf4j.Slf4j;

import java.util.ArrayList;
import java.util.List;

/**
 * @author QiuQiu&LL
 * @create 2021-07-08  0:15
 * @Description:
 */
@Slf4j
public class ExcelStudentDataListener extends AnalysisEventListener<ExcelStudentData> {

    private static final int TEMP = 500;
    private List<ExcelStudentData> list = new ArrayList<>();

    @Override
    public void invoke(ExcelStudentData excelStudentData, AnalysisContext analysisContext) {
        log.info("解析到每一条数据" + excelStudentData);
        list.add(excelStudentData);
        if (list.size() >= TEMP) {
            // TODO 存入数据库
            list.clear();
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        log.info("收尾工作" + analysisContext);
    }

}

读excel

package com.qbb.alibaba.excel;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.qbb.alibaba.excel.entity.ExcelStudentData;
import com.qbb.alibaba.excel.listener.ExcelStudentDataListener;
import org.junit.Test;

/**
 * @author QiuQiu&LL
 * @create 2021-07-08  0:16
 * @Description:
 */
public class ExcelReadTest {

    @Test
    public void simpleRead07() {
        String filename = "G:/excel/01-simpleWrite-07.xlsx";
        EasyExcel.read(filename, ExcelStudentData.class, new ExcelStudentDataListener()).excelType(ExcelTypeEnum.XLSX)
                .sheet().doRead();
    }
}

写excel

package com.qbb.alibaba.excel;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.qbb.alibaba.excel.entity.ExcelStudentData;
import org.junit.Test;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @author QiuQiu&LL
 * @create 2021-07-07  23:29
 * @Description:
 */
public class ExcelWriteTest {

    /**
     * 最简单的写
     */
    @Test
    public void simpleWrite07() {

        String fileName = "G:/excel/01-simpleWrite-07.xlsx";
        // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        EasyExcel.write(fileName, ExcelStudentData.class).sheet("模板").doWrite(data());
    }

    @Test
    public void simpleWrite03() {

        String fileName = "G:/excel/01-simpleWrite-03.xls";
        // 如果这里想使用03 则 传入excelType参数即可
        EasyExcel.write(fileName, ExcelStudentData.class).excelType(ExcelTypeEnum.XLS).sheet("模板").doWrite(data());
    }

    private List<ExcelStudentData> data(){
        List<ExcelStudentData> list = new ArrayList<>();

        //算上标题，做多可写65536行
        //超出：java.lang.IllegalArgumentException: Invalid row number (65536) outside allowable range (0..65535)
        for (int i = 0; i < 655; i++) {
            ExcelStudentData data = new ExcelStudentData();
            data.setName("QiuQiu&LL" + i);
            data.setBirthday(new Date());
            data.setSalary(0.56);
            data.setPassword("123"); //即使设置也不会被导出
            list.add(data);
        }

        return list;
    }
}