EasyExcel根据对应的实体类模板完成多个sheet的写入与读取

1.展示模板一的实体类

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import io.swagger.annotations.ApiModel;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;import javax.validation.constraints.NotBlank;@Data
@NoArgsConstructor
@AllArgsConstructor
@ApiModel(value="RegObject对象", description="不动产单元")
//表头行高
@HeadRowHeight(30)
//内容单元格行高
@ContentRowHeight(25)
public class RegObject {@ExcelProperty(value = "客体类型", index = 0)@ColumnWidth(20)@NotBlank(message = "客体类型不能为空")private String propertyType;@ExcelProperty(value = "不动产单元号", index = 1)@ColumnWidth(30)@NotBlank(message = "不动产单元号不能为空")private String regNumber;}

2.展示模板二的实体类

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import io.swagger.annotations.ApiModel;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;import javax.validation.constraints.NotBlank;
import java.util.Date;@Data
@NoArgsConstructor
@AllArgsConstructor
@ApiModel(value="IncomeInfo对象", description="受理信息")
//表头行高
@HeadRowHeight(30)
//内容单元格行高
@ContentRowHeight(25)
public class IncomeInfo {@ExcelProperty(value = "受理人", index = 0)@ColumnWidth(20)@NotBlank(message = "受理人不能为空")private String incomeMan;@ExcelProperty(value = "审批流程", index = 1)@ColumnWidth(20)@NotBlank(message = "审批流程不能为空")private String workId;@ExcelProperty(value = "坐落", index = 2)@ColumnWidth(20)@NotBlank(message = "坐落不能为空")private String seatName;//    @ExcelProperty(value = "结束时间", index = 3)
//    @ColumnWidth(20)
//    @NotBlank(message = "结束时间不能为空")
//    @DateTimeFormat(value = "yyyy年MM月dd日 HH时mm分ss秒")
//    private String endDate;@ExcelProperty(value = "结束时间", index = 3)@ColumnWidth(20)@NotBlank(message = "结束时间不能为空")@DateTimeFormat(value = "yyyy-MM-dd HH:mm:ss")private Date endDate;
}

3.展示easyExcel实现写入功能的代码

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.guan.study.easyExcelDemo.IncomeInfo;
import com.guan.study.easyExcelDemo.RegObject;import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;public class WriteExcelDemo03 {private static final String PATH = "D:\\WorkDirTest2\\测试.xls";private static List<String> list = new ArrayList<>();public static void main(String[] args) {list.add("不动产单元信息");list.add("受理信息");//如果文件存在就删除Path path = Paths.get(PATH);if (Files.exists(path)) {try {Files.delete(path);} catch (IOException e) {System.out.println("无法删除文件：" + e.getMessage());}}ExcelWriterBuilder write = EasyExcel.write(PATH);// 创建ExcelWriter对象ExcelWriter excelWriter = write.build();// 向Excel的同一个Sheet重复写入数据for (int i = 0; i < list.size(); i++) {// 创建Sheet对象WriteSheet writeSheet = EasyExcel.writerSheet(list.get(i)).build();// 设置excel模板寻求首航标题的类writeSheet.setClazz(getTargetClass(list.get(i)));// 设置数据到该sheet中excelWriter.write(data(list.get(i)), writeSheet);}// 关闭流excelWriter.finish();}/*** 根据传入的字符串返回对应的目标类*/private static Class<?> getTargetClass(String str) {switch (str) {case "不动产单元信息":return RegObject.class;case "受理信息":return IncomeInfo.class;default:return null;}}/*** 模拟前端传过来的数据 不动产单元信息*/private static List<Object> data(String str) {SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");List<Object> list = new ArrayList<>();switch (str) {case "不动产单元信息":RegObject regObject1 = new RegObject();regObject1.setPropertyType("0008");regObject1.setRegNumber("130000000001GB00001F00010001");list.add(regObject1);RegObject regObject2 = new RegObject();regObject2.setPropertyType("0008");regObject2.setRegNumber("130000000001GB00001F00010002");list.add(regObject2);break;case "受理信息":IncomeInfo info1 = new IncomeInfo();info1.setIncomeMan("00000001");info1.setWorkId("0001");info1.setSeatName("130208");//info1.setEndDate(dateFormat.format(new Date()));info1.setEndDate(new Date());list.add(info1);IncomeInfo info2 = new IncomeInfo();info2.setIncomeMan("00000001");info2.setWorkId("0002");info2.setSeatName("130229");//info2.setEndDate(dateFormat.format(new Date()));info2.setEndDate(new Date());list.add(info2);break;}return list;}
}

4.展示D:\WorkDirTest2路径下测试.xls中的效果

5.读取excel代码

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.guan.study.easyExcelDemo.IncomeInfo;
import com.guan.study.easyExcelDemo.RegObject;import java.util.ArrayList;
import java.util.List;public class ReadExcelDemo03 {private static List<String> list = new ArrayList<>();public static void main(String[] args) {list.add("不动产单元信息");list.add("受理信息");// 读取的excel文件路径String filename = "D:\\WorkDirTest2\\测试.xls";for (int i = 0; i < list.size(); i++) {readExcel(filename, i, getTargetClass(list.get(i)));}//readExcel(filename, 0, RegObject.class);//readExcel(filename, 1, IncomeInfo.class);}private static <T> void readExcel(String filename, int sheetNo, Class<T> clazz) {// 构建ExcelReader对象ExcelReader excelReader = EasyExcel.read(filename).build();// 构建sheet对象ReadSheet sheet = EasyExcel.readSheet(sheetNo).head(clazz) // 指定数据模板.registerReadListener(new AnalysisEventListener<T>() {// 每解析一行数据,该方法会被调用一次@Overridepublic void invoke(T data, AnalysisContext context) {System.out.println("解析数据为:" + data.toString());}// 全部解析完成被调用@Overridepublic void doAfterAllAnalysed(AnalysisContext context) {//System.out.println("解析完成...");}}).build();// 读取sheetexcelReader.read(sheet);// 需要自己关闭流操作,在读取文件时会创建临时文件,如果不关闭,会损耗磁盘,严重的磁盘爆掉excelReader.finish();}/*** 根据传入的字符串返回对应的目标类*/private static Class<?> getTargetClass(String str) {switch (str) {case "不动产单元信息":return RegObject.class;case "受理信息":return IncomeInfo.class;default:return null;}}}

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.7.13</version><relativePath/> <!-- lookup parent from repository --></parent><groupId>com.guan</groupId><artifactId>EasyExcel-study</artifactId><version>0.0.1-SNAPSHOT</version><name>EasyExcel-study</name><description>EasyExcel-study</description><properties><java.version>1.8</java.version></properties><dependencies><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-test</artifactId><scope>test</scope></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-validation</artifactId></dependency><!-- https://mvnrepository.com/artifact/org.hibernate/hibernate-validator --><dependency><groupId>org.hibernate</groupId><artifactId>hibernate-validator</artifactId><version>5.4.3.Final</version></dependency><!-- https://mvnrepository.com/artifact/javax.validation/validation-api --><dependency><groupId>javax.validation</groupId><artifactId>validation-api</artifactId><version>2.0.1.Final</version></dependency><dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>3.1.3</version><exclusions><exclusion><groupId>javax.servlet</groupId><artifactId>servlet-api</artifactId></exclusion><exclusion><groupId>org.apache.poi</groupId><artifactId>poi</artifactId></exclusion><exclusion><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId></exclusion><exclusion><groupId>org.apache.poi</groupId><artifactId>poi-ooxml-schemas</artifactId></exclusion></exclusions></dependency><!-- https://mvnrepository.com/artifact/org.apache.poi/poi --><dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>5.2.3</version></dependency><!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml --><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>5.2.3</version></dependency><!-- https://mvnrepository.com/artifact/joda-time/joda-time --><dependency><groupId>joda-time</groupId><artifactId>joda-time</artifactId><version>2.12.5</version></dependency><!-- https://mvnrepository.com/artifact/junit/junit --><dependency><groupId>junit</groupId><artifactId>junit</artifactId><version>4.13.2</version><scope>test</scope></dependency><!--swagger--><dependency><groupId>io.springfox</groupId><artifactId>springfox-swagger2</artifactId><version>2.9.2</version></dependency><dependency><groupId>io.springfox</groupId><artifactId>springfox-swagger-ui</artifactId><version>2.9.2</version></dependency><!--lombok用来简化实体类：需要安装lombok插件--><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><version>1.18.20</version></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId></dependency><dependency><groupId>org.apache.commons</groupId><artifactId>commons-lang3</artifactId></dependency><!-- https://mvnrepository.com/artifact/org.jsoup/jsoup --><dependency><groupId>org.jsoup</groupId><artifactId>jsoup</artifactId><version>1.15.3</version></dependency></dependencies><build><plugins><!-- Add Lombok Plugin --><plugin><groupId>org.apache.maven.plugins</groupId><artifactId>maven-compiler-plugin</artifactId><version>3.8.1</version><configuration><source>1.8</source><target>1.8</target><annotationProcessorPaths><path><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><version>1.18.24</version></path></annotationProcessorPaths></configuration></plugin></plugins></build></project>