easyexcel读取写入excel easyexceldemo

1.新建springboot项目

2.添加pom依赖

<name>excel</name>
<description>excelspringboot例子</description><parent>
<groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-parent</artifactId><version>2.2.1.RELEASE</version><relativePath/>
</parent><properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding><project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding><java.version>1.8</java.version><maven.compiler.source>1.8</maven.compiler.source><maven.compiler.target>1.8</maven.compiler.target><apollo.version>2.3.0</apollo.version>
</properties><dependencies>
<dependency>
<groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId>
</dependency><dependency>
<groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-aop</artifactId>
</dependency><dependency>
<groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>
<!-- 其他依赖 -->
<dependency>
<groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><version>1.18.24</version><scope>provided</scope>
</dependency><dependency>
<groupId>com.alibaba.fastjson2</groupId><artifactId>fastjson2</artifactId><version>2.0.51</version>
</dependency><dependency>
<groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-test</artifactId><version>2.7.18</version>
</dependency><!--easyexcel-->
<dependency>
<groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>3.2.1</version>
</dependency><!-- logback -->
<dependency>
<groupId>org.apache.logging.log4j</groupId><artifactId>log4j-api</artifactId><version>2.17.1</version> <!-- 确保版本 >= 2.4 -->
</dependency><dependency>
<groupId>org.apache.logging.log4j</groupId><artifactId>log4j-core</artifactId><version>2.17.1</version> <!-- 确保版本 >= 2.4 -->
</dependency></dependencies><build>
<finalName>excel-demo</finalName><plugins>
<plugin>
<groupId>org.springframework.boot</groupId><artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>

新建实体类TestData

package com.baozun.entity;import com.alibaba.excel.annotation.ExcelProperty;
import lombok.EqualsAndHashCode;
import lombok.Getter;
import lombok.Setter;/*** 基础数据类** @author Jiaju Zhuang**/
@Getter
@Setter
@EqualsAndHashCode
public class TestData {/*** 强制读取第三个 这里不建议 index 和 name 同时用，要么一个对象只用index，要么一个对象只用name去匹配*/@ExcelProperty(index = 0)private String idOne;@ExcelProperty(index = 1)private String idTwo;@ExcelProperty(index = 2)private String idThree;@ExcelProperty(index = 3)private String idFour;@ExcelProperty(index = 4)private String idFive;@ExcelProperty(index = 5)private String idSix;}

新建TestDataListenerimport com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson2.JSON;
import com.baozun.entity.TestData;
import lombok.extern.slf4j.Slf4j;import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;/*** 模板的读取类
* 
* @author Jiaju Zhuang*/@Slf4jpublic class TestDataListener extends AnalysisEventListener `<TestData>` {/*** 每隔5条存储数据库，实际使用中可以100条，然后清理list ，方便内存回收*/private static final int BATCH_COUNT = 5;Set `<String>` firstColumnData = new HashSet<>();Set `<String>` secondColumnData = new HashSet<>();Set `<String>` differences = new HashSet<>();@Overridepublic void invoke(TestData data, AnalysisContext context) {firstColumnData.add(data.getIdOne());secondColumnData.add(data.getIdTwo());}@Overridepublic void doAfterAllAnalysed(AnalysisContext context) {// 找出两个列表的差异Set `<String>` set1 = new HashSet<>(secondColumnData);set1.removeAll(firstColumnData); // 在set1中但不在set2中的元素differences.addAll(set1);String fileName = "C:\\Users\\IdeaProjects\\exceldemo\\src\\main\\resources\\test.xlsx"; //需要提前新建目录// 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭EasyExcel.write(fileName, TestData.class).sheet("模板").doWrite(data());log.info("所有数据解析完成！");}private List `<TestData>` data(){List `<TestData>` list = new ArrayList<>();differences.forEach(item -> {TestData data = new TestData();data.setIdOne(item);list.add(data);});return list;}

新建test类ApplicationTests

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.baozun.ExcelMainApplication;
import com.baozun.entity.TestData;
import com.baozun.listener.TestDataListener;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;import java.util.ArrayList;
import java.util.List;@SpringBootTest(classes = ExcelMainApplication.class)
class ApplicationTests {@Testvoid testEnrollCourse() {System.out.println("11");}/*** 写入xlsx文件测试方法*/@Testpublic void simpleWriteXlsx() {String fileName = "C:\\Users\\IdeaProjects\\exceldemo\\src\\main\\resources\\test.xlsx"; //需要提前新建目录// 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭EasyExcel.write(fileName, TestData.class).sheet("模板").doWrite(data());}/*** 写入xls文件测试方法*/@Testpublic void simpleWriteXls() {String fileName = "C:\\Users\\IdeaProjects\\exceldemo\\src\\main\\resources\\test.xls";// 如果这里想使用03 则 传入excelType参数即可EasyExcel.write(fileName, TestData.class).excelType(ExcelTypeEnum.XLS).sheet("模板").doWrite(data());}/*** 读取xlsx文件最简单的读*/@Testpublic void simpleReadXlsx() {String fileName = "C:\\Users\\IdeaProjects\\exceldemo\\src\\main\\resources\\testDifferData.xlsx";// 这里默认读取第一个sheetEasyExcel.read(fileName, TestData.class, new TestDataListener()).sheet().doRead();}/*** 读取xls文件最简单的读*/@Testpublic void simpleReadXls() {String fileName = "d:/excel/simpleWrite.xls";EasyExcel.read(fileName, TestData.class, new TestDataListener()).excelType(ExcelTypeEnum.XLS).sheet().doRead();}private List<TestData> data(){List<TestData> list = new ArrayList<>();//算上标题，做多可写65536行//超出：java.lang.IllegalArgumentException: Invalid row number (65536) outside allowable range (0..65535)for (int i = 0; i < 2; i++) {TestData data = new TestData();data.setIdOne("11");data.setIdTwo("22");list.add(data);}return list;}

项目结构