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:*/@Datapublic 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;/*** 忽略这个字段*/@ExcelIgnoreprivate 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:*/@Slf4jpublic class ExcelStudentDataListener extends AnalysisEventListener<ExcelStudentData> {private static final int TEMP = 500;private List<ExcelStudentData> list = new ArrayList<>();@Overridepublic void invoke(ExcelStudentData excelStudentData, AnalysisContext analysisContext) {log.info("解析到每一条数据" + excelStudentData);list.add(excelStudentData);if (list.size() >= TEMP) {// TODO 存入数据库list.clear();}}@Overridepublic 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 {@Testpublic 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 {/*** 最简单的写*/@Testpublic void simpleWrite07() {String fileName = "G:/excel/01-simpleWrite-07.xlsx";// 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭EasyExcel.write(fileName, ExcelStudentData.class).sheet("模板").doWrite(data());}@Testpublic 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;}}

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