SpringBoot整合EasyExcel

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

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

2.解决方案

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

3.EasyExcel使用步骤

创建一个简单的Maven项目

导入相关依赖

<?xml version="1.0" encoding="UTF-8"?>

    
        01_lesson_address
        com.qbb.lesson
        1.0-SNAPSHOT
        ../01_lesson_address/pom.xml
    
    4.0.0

    com.qbb
    alibaba_excel

    

        
            com.alibaba
            easyexcel
            2.1.7
        

        
            org.slf4j
            slf4j-simple
            1.7.5
        

        
            org.apache.xmlbeans
            xmlbeans
            3.1.0
        

        
            org.projectlombok
            lombok
            1.18.10
        

        
            junit
            junit
            4.12
        

    

创建一个实体类

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 {

    private static final int TEMP = 500;
    private List 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 data(){
        List 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;
    }
}

简单的excel读写,大家可以参考上面的代码,或者参考官方EsayExcel