EasyExcel入门
名称 | 链接 | 备注 |
---|---|---|
官方文档[写Excel | https://easyexcel.opensource.alibaba.com/docs/current/quickstart/write | |
github地址: | https://github.com/alibaba/easyexcel | |
gitee地址: | https://gitee.com/easyexcel/easyexcel |
1. 简介
1.1 入门
Java解析、生成Excel比较有名的框架有Apache poi、jxl。但他们都存在一个严重的问题就是非常的耗内存,poi有一套SAX模式的API可以一定程度的解决一些内存溢出的问题,但POI还是有一些缺陷,比如07版Excel解压缩以及解压后存储都是在内存中完成的,内存消耗依然很大。
easyexcel重写了poi对07版Excel的解析,一个3M的excel用POI sax解析依然需要100M左右内存,改用easyexcel可以降低到几M,并且再大的excel也不会出现内存溢出;03版依赖POI的sax模式,在上层做了模型转换的封装,让使用者更加简单方便
1.2 环境准备
环境准备
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>4.0.1</version>
</dependency>
数据准备
easyexcel/easyexcel-test/src/test/resources at master · alibaba/easyexcel · GitHub
从阿里巴巴的网上把excel 下载下来。
1.3 常用注解
ExcelProperty
用于匹配excel和实体类的匹配,参数如下:
名称 | 默认值 | 描述 |
---|---|---|
value | 空 | 用于匹配excel中的头,必须全匹配,如果有多行头,会匹配最后一行头 |
order | Integer.MAX_VALUE | 优先级高于value ,会根据order 的顺序来匹配实体和excel中数据的顺序 |
index | -1 | 优先级高于value 和order ,会根据index 直接指定到excel中具体的哪一列 |
converter | 自动选择 | 指定当前字段用什么转换器,默认会自动选择。读的情况下只要实现com.alibaba.excel.converters.Converter#convertToJavaData(com.alibaba.excel.converters.ReadConverterContext<?>) 方法即可 |
ExcelIgnore
默认所有字段都会和excel去匹配,加了这个注解会忽略该字段
ExcelIgnoreUnannotated
默认不加ExcelProperty
的注解的都会参与读写,加了不会参与读写
DateTimeFormat
日期转换,用String
去接收excel日期格式的数据会调用这个注解,参数如下:
名称 | 默认值 | 描述 |
---|---|---|
value | 空 | 参照java.text.SimpleDateFormat 书写即可 |
use1904windowing | 自动选择 | excel中时间是存储1900年起的一个双精度浮点数,但是有时候默认开始日期是1904,所以设置这个值改成默认1904年开始 |
NumberFormat
数字转换,用String
去接收excel数字格式的数据会调用这个注解。
名称 | 默认值 | 描述 |
---|---|---|
value | 空 | 参照java.text.DecimalFormat 书写即可 |
roundingMode | RoundingMode.HALF_UP | 格式化的时候设置舍入模式 |
2. 快速入门
2.1 读Excel
1)编写模型类并加入注解 2)监听器介绍 3)调用官方API完成写功能
首先创建实体类
package com.lkcoffee.demo;
import java.util.Date;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.Getter;
import lombok.Setter;
/**
* 基础数据类.这里的排序和excel里面的排序一致
*
* @author Jiaju Zhuang
**/
@Data
public class DemoData {
private String string;
private Date date;
private Double doubleData;
}
然后我们查看demo.xlsx的内容
写代码来读取代码
package com.lkcoffee.demo;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;
import lombok.extern.slf4j.Slf4j;
import java.io.File;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;
import java.util.Optional;
/**
* @Desciption:
* @Author: feixiang.li
* @date: 2024-07-11 14:58
**/
@Slf4j
public class Test3 {
public void read(){
System.out.println(this.getClass().getClassLoader().getResource("").getPath());
String fileName = this.getClass().getClassLoader().getResource("").getPath() + "demo" + File.separator + "demo.xlsx";
log.info(fileName);
// 这里 需要指定读用哪个class去读,然后读取第一个sheet 文件流会自动关闭
EasyExcel.read(fileName, DemoData.class, new ReadListener<DemoData>() {
@Override
public void invoke(DemoData data, AnalysisContext context) {
log.info("读取到一条数据: {}",data);
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
log.info("所有数据解析完成!");
}
}).sheet().doRead();
}
public static void main(String[] args) {
Test3 test3=new Test3();
test3.read();
}
}
下面可以看到获取的结果
下图是完成的项目结构
下面是我们完成的pom目录
<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">
<modelVersion>4.0.0</modelVersion>
<groupId>com.ucarinc.framework</groupId>
<artifactId>demo1</artifactId>
<version>1.0-SNAPSHOT</version>
<packaging>jar</packaging>
<name>demo1</name>
<url>http://maven.apache.org</url>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
</properties>
<dependencies>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
<version>2.0.13</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-log4j12</artifactId>
<version>2.0.13</version>
</dependency>
<dependency>
<groupId>org.junit.jupiter</groupId>
<artifactId>junit-jupiter</artifactId>
<version>5.10.2</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.32</version>
</dependency>
<dependency>
<groupId>org.mockito</groupId>
<artifactId>mockito-core</artifactId>
<version>5.11.0</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.mockito</groupId>
<artifactId>mockito-inline</artifactId>
<version>5.2.0</version>
<scope>test</scope>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.commons/commons-lang3 -->
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
<version>3.14.0</version>
</dependency>
<dependency>
<groupId>org.mockito</groupId>
<artifactId>mockito-junit-jupiter</artifactId>
<version>5.11.0</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>4.0.1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.alibaba.fastjson2/fastjson2 -->
<dependency>
<groupId>com.alibaba.fastjson2</groupId>
<artifactId>fastjson2</artifactId>
<version>2.0.51</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.13.2</version>
<scope>compile</scope>
</dependency>
</dependencies>
</project>
2.2 写Excel
1)编写模型类并加入注解 2)编写获取测试数据的方法 3)调用官方API完成写功能
创建实体类
package com.lkcoffee.demo.write;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
import java.util.Date;
/**
* @Desciption:
* @Author: feixiang.li
* @date: 2024-07-16 16:00
**/
@Data
public class Employee {
@ExcelProperty("员工编号")
private Long id;
@ExcelProperty("员工姓名")
private String name;
@ExcelProperty("员工入职日期")
private Date date;
@ExcelProperty("员工薪水")
private Double salary;
}
开始写入
package com.lkcoffee.demo.write;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;
import com.lkcoffee.demo.read.DemoData;
import lombok.extern.slf4j.Slf4j;
import java.io.File;
import java.util.ArrayList;
import java.util.List;
/**
* @Desciption:
* @Author: feixiang.li
* @date: 2024-07-11 14:58
**/
@Slf4j
public class Test4 {
public List<Employee> getData(int size){
List<Employee> data=new ArrayList<>();
for (int i = 0; i < size; i++) {
Employee employee=new Employee();
employee.setDate(new java.util.Date());
employee.setId((long) i);
employee.setSalary(1000.0);
employee.setName("张三"+i);
data.add(employee);
}
return data;
}
public void write(int size){
// 模拟10万条数据,然后写入excel
List<Employee> data=getData(size);
EasyExcel.write(new File("D:\\code\\java-test\\demo3\\test.xlsx"),Employee.class).sheet("员工信息").doWrite(data);
}
public static void main(String[] args) {
log.info("开始写入");
// 开始计时
long startTime = System.currentTimeMillis();
// 写10万条数据大概需要多少时间
Test4 test4=new Test4();
test4.write(100000);
// 结束计时
long endTime = System.currentTimeMillis();
// 打印运行时间,单位为秒
log.info("写入完成,耗时:{}s", (endTime - startTime)/1000);
}
}
可以看到2秒钟。就可以写入10万条数据,
编写工具类
package com.lkcoffee.demo.utils;
import java.io.File;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.collections4.CollectionUtils;
public class TestFileUtil {
public static InputStream getResourcesFileInputStream(String fileName) {
return Thread.currentThread().getContextClassLoader().getResourceAsStream("" + fileName);
}
public static String getPath() {
return TestFileUtil.class.getResource("/").getPath();
}
public static TestPathBuild pathBuild() {
return new TestPathBuild();
}
public static File createNewFile(String pathName) {
File file = new File(getPath() + pathName);
if (file.exists()) {
file.delete();
} else {
if (!file.getParentFile().exists()) {
file.getParentFile().mkdirs();
}
}
return file;
}
public static File readFile(String pathName) {
return new File(getPath() + pathName);
}
public static File readUserHomeFile(String pathName) {
return new File(System.getProperty("user.home") + File.separator + pathName);
}
/**
* build to test file path
**/
public static class TestPathBuild {
private TestPathBuild() {
subPath = new ArrayList<>();
}
private final List<String> subPath;
public TestPathBuild sub(String dirOrFile) {
subPath.add(dirOrFile);
return this;
}
public String getPath() {
if (CollectionUtils.isEmpty(subPath)) {
return TestFileUtil.class.getResource("/").getPath();
}
if (subPath.size() == 1) {
return TestFileUtil.class.getResource("/").getPath() + subPath.get(0);
}
StringBuilder path = new StringBuilder(TestFileUtil.class.getResource("/").getPath());
path.append(subPath.get(0));
for (int i = 1; i < subPath.size(); i++) {
path.append(File.separator).append(subPath.get(i));
}
return path.toString();
}
}
}
然后该成其他写法
package com.lkcoffee.demo.write;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.lkcoffee.demo.utils.TestFileUtil;
import lombok.extern.slf4j.Slf4j;
import java.io.File;
import java.util.ArrayList;
import java.util.List;
/**
* @Desciption:
* @Author: feixiang.li
* @date: 2024-07-11 14:58
**/
@Slf4j
public class Test5 {
public List<Employee> getData(int size){
List<Employee> data=new ArrayList<>();
for (int i = 0; i < size; i++) {
Employee employee=new Employee();
employee.setDate(new java.util.Date());
employee.setId((long) i);
employee.setSalary(1000.0);
employee.setName("张三"+i);
data.add(employee);
}
return data;
}
public void write(int size){
// 模拟10万条数据,然后写入excel
List<Employee> data=getData(size);
// 写法2
String fileName = TestFileUtil.getPath() + "simpleWrite" + System.currentTimeMillis() + ".xlsx";
log.info("文件名称:{}",fileName);
// 这里 需要指定写用哪个class去写,然后写到第一个sheet,名字为模板 然后文件流会自动关闭
// 如果这里想使用03 则 传入excelType参数即可
EasyExcel.write(fileName, Employee.class).sheet("模板").doWrite(data);
// 写法3
fileName = TestFileUtil.getPath() + "simpleWrite" + System.currentTimeMillis() + ".xlsx";
log.info("文件名称:{}",fileName);
// 这里 需要指定写用哪个class去写
try (ExcelWriter excelWriter = EasyExcel.write(fileName, Employee.class).build()) {
WriteSheet writeSheet = EasyExcel.writerSheet("模板").build();
excelWriter.write(data, writeSheet);
}
}
public static void main(String[] args) {
log.info("开始写入");
// 开始计时
long startTime = System.currentTimeMillis();
// 写10万条数据大概需要多少时间
Test5 test4=new Test5();
test4.write(100000);
// 结束计时
long endTime = System.currentTimeMillis();
// 打印运行时间,单位为秒
log.info("写入完成,耗时:{}s", (endTime - startTime)/1000);
}
}
2.3 导出自适应行高
注解@ColumnWidth
@Getter
@Setter
@EqualsAndHashCode
@ContentRowHeight(10)
@HeadRowHeight(20)
@ColumnWidth(25)
public class WidthAndHeightData {
@ExcelProperty("字符串标题")
private String string;
@ExcelProperty("日期标题")
private Date date;
/**
* 宽度为50
*/
@ColumnWidth(50)
@ExcelProperty("数字标题")
private Double doubleData;
}
官方提供策略
不想自己编写的话。使用官方提供的LongestMatchColumnWidthStyleStrategy()
try (ExcelWriter excelWriter = EasyExcel.write(fileName)
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
.build()) {
WriteSheet writeSheet = EasyExcel.writerSheet("模板").build();
excelWriter.write(getData(100), writeSheet);
}
自定义策略
这里我给大家详细说一下。首先是我这边定义的如果columnWidth 长度大于10,就赋值为20,否则就赋值为10,你们可以自行调整。在计算长度的那个方法中,有个switch 方法,其中对字符串,布尔,还有数值型进行区分。然后去用不同的公式计算。这里会遇到一个坑,如果你的导出的文档有很多数字,比如金额什么的。就可以把case NUMBER删掉,因为可能列宽不够导致导出来的值是”######“。
package com.lkcoffee.demo.write;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.CellData;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class CustomCellWriteWidthConfig extends AbstractColumnWidthStyleStrategy {
private final Map<Integer, Map<Integer, Integer>> CACHE = new HashMap<>();
@Override
protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer integer, Boolean isHead) {
boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
if (needSetWidth) {
Map<Integer, Integer> maxColumnWidthMap = CACHE.computeIfAbsent(writeSheetHolder.getSheetNo(), k -> new HashMap<>());
Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
if (columnWidth > 0) {
if (columnWidth > 10) {
columnWidth = 20;
}else{
columnWidth = 10;
}
Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
Sheet sheet = writeSheetHolder.getSheet();
sheet.setColumnWidth(cell.getColumnIndex(), 256 * columnWidth + 184);
}
}
}
}
/**
* 计算长度
*
* @param cellDataList
* @param cell
* @param isHead
* @return
*/
private Integer dataLength(List<WriteCellData<?>> cellDataList, Cell cell, Boolean isHead) {
if (isHead) {
return cell.getStringCellValue().getBytes().length;
} else {
CellData<?> cellData = cellDataList.get(0);
CellDataTypeEnum type = cellData.getType();
if (type == null) {
return -1;
} else {
switch (type) {
case STRING:
// 换行符(数据需要提前解析好)
int index = cellData.getStringValue().indexOf("\n");
return index != -1 ?
cellData.getStringValue().substring(0, index).getBytes().length + 1 : cellData.getStringValue().getBytes().length + 1;
case BOOLEAN:
return cellData.getBooleanValue().toString().getBytes().length;
case NUMBER:
return cellData.getNumberValue().toString().getBytes().length;
default:
return -1;
}
}
}
}
}
这里我是10个字符就插入一个换行符,你们可以根据业务场景自行调整。下面我解释一下我为什么重写afterRowDispose方法,因为在我们导出的时候除了使用一个集合进行填充,还会对单个单个单元格进行填充,也就是可能会同时使用excelWriter.fill(var1, var2, var3); 和excelWriter.fill(var1, var2); 这两个方法。因为在填充单个单元格时是没有相对行索引的概念的,所以我进行了判空处理。这个问题我排查了一下午真的裂开。特此指出。
package com.lkcoffee.demo.write;
import com.alibaba.excel.write.handler.context.RowWriteHandlerContext;
import com.alibaba.excel.write.style.row.AbstractRowHeightStyleStrategy;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import java.util.Iterator;
public class CustomCellWriteHeightConfig extends AbstractRowHeightStyleStrategy {
/**
* 默认高度
*/
private static final Integer DEFAULT_HEIGHT = 300;
@Override
protected void setHeadColumnHeight(Row row, int relativeRowIndex) {
}
@Override
protected void setContentColumnHeight(Row row, int relativeRowIndex) {
Iterator<Cell> cellIterator = row.cellIterator();
if (!cellIterator.hasNext()) {
return;
}
// 默认为 1 行高度
int maxHeight = 1;
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
if (cell.getCellType() == CellType.STRING) {
String value = cell.getStringCellValue();
for (int i = 0; i < value.length(); i += 10) {
if (i + 10 < value.length()) {
value = value.substring(0, i) + "\n" + value.substring(i, i + 10) + value.substring(i + 10);
} else {
value = value.substring(0, i) + "\n" + value.substring(i);
}
}
if (value.contains("\n")) {
int length = value.split("\n").length;
maxHeight = Math.max(maxHeight, length);
}
}
}
row.setHeight((short) (maxHeight * DEFAULT_HEIGHT));
}
@Override
public void afterRowDispose(RowWriteHandlerContext context) {
if (context.getHead() != null) {
if(ObjectUtils.isEmpty(context.getRelativeRowIndex())){
return;
}
if (Boolean.TRUE.equals(context.getHead())) {
this.setHeadColumnHeight(context.getRow(), context.getRelativeRowIndex());
} else {
this.setContentColumnHeight(context.getRow(), context.getRelativeRowIndex());
}
}
}
}
使用
package com.lkcoffee.demo.write;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.lkcoffee.demo.domain.Employee;
import com.lkcoffee.demo.domain.IndexEmployee;
import com.lkcoffee.demo.utils.TestFileUtil;
import lombok.extern.slf4j.Slf4j;
import org.junit.Test;
import java.util.ArrayList;
import java.util.List;
/**
* @Desciption:
* @Author: feixiang.li
* @date: 2024-07-11 14:58
**/
@Slf4j
public class Test7 {
public List<IndexEmployee> getData(int size){
List<IndexEmployee> data=new ArrayList<>();
for (int i = 0; i < size; i++) {
IndexEmployee employee=new IndexEmployee();
employee.setDate(new java.util.Date());
employee.setId((long) i);
employee.setSalary(1000.0);
employee.setName("张三3223432sdffffffffffffffffffffffffffffffffffffffffff" +
"ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff"+i);
data.add(employee);
}
return data;
}
@Test
public void write(){
String fileName = TestFileUtil.getPath() + "myWrite" + System.currentTimeMillis() + ".xlsx";
// 这里 需要指定写用哪个class去写,然后写到第一个sheet,名字为模板 然后文件流会自动关闭
try (ExcelWriter excelWriter = EasyExcel.write(fileName)
.registerWriteHandler(new CustomCellWriteHeightConfig())
.registerWriteHandler(new CustomCellWriteWidthConfig())
.build()) {
WriteSheet writeSheet = EasyExcel.writerSheet("模板").build();
excelWriter.write(getData(100), writeSheet);
}
}
}
另一种方案
public class ColumnWidthStyleStrategy extends AbstractColumnWidthStyleStrategy {
private static final int MAX_COLUMN_WIDTH = 255;
private Map<String, List<TableHeaderVO>> sheetHeaderMap;
public ColumnWidthStyleStrategy(Map<String, List<TableHeaderVO>> sheetHeaderMap) {
this.sheetHeaderMap = sheetHeaderMap;
}
@Override
protected void setColumnWidth(
WriteSheetHolder writeSheetHolder,
List<WriteCellData<?>> cellDataList,
Cell cell,
Head head,
Integer relativeRowIndex,
Boolean isHead) {
final int rowIndex = cell.getRowIndex();
// 只在第一行设置列宽即可
if (rowIndex != 0) {
return;
}
final List<TableHeaderVO> list = sheetHeaderMap.get(writeSheetHolder.getSheetName());
Integer columnWidth = dataLength(list, cell);
if (columnWidth < 0) {
return;
}
if (columnWidth > MAX_COLUMN_WIDTH) {
columnWidth = MAX_COLUMN_WIDTH;
}
Integer maxColumnWidth = 1;
if (columnWidth > maxColumnWidth) {
writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
}
}
// 根据 cell 中写入的内容,查找该列配置的表头列宽
private Integer dataLength(List<TableHeaderVO> list, Cell cell) {
final String cellValue = cell.getStringCellValue();
final Optional<TableHeaderVO> headerOptional =
list.stream().filter(k -> cellValue.equals(k.getLabel())).findFirst();
return headerOptional.map(tableHeaderVO -> tableHeaderVO.getWidth().intValue()).orElse(1);
}
https://gitcode.csdn.net/65e93a3a1a836825ed78e15c.html
2.4 制定写入的列
package com.lkcoffee.demo.domain;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
import java.util.Date;
/**
* @Desciption:
* @Author: feixiang.li
* @date: 2024-07-16 16:00
**/
@Data
public class IndexEmployee {
@ExcelProperty(value = "员工编号",index = 0)
private Long id;
@ExcelProperty(value = "员工姓名",index = 1)
private String name;
@ExcelProperty(value = "员工入职日期",index = 3)
private Date date;
@ExcelProperty(value = "员工薪水",index =4)
private Double salary;
}
编写测试代码
package com.lkcoffee.demo.write;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.lkcoffee.demo.domain.Employee;
import com.lkcoffee.demo.domain.IndexEmployee;
import com.lkcoffee.demo.utils.TestFileUtil;
import lombok.extern.slf4j.Slf4j;
import org.junit.Test;
import java.util.ArrayList;
import java.util.List;
/**
* @Desciption:
* @Author: feixiang.li
* @date: 2024-07-11 14:58
**/
@Slf4j
public class Test6 {
public List<IndexEmployee> getData(int size){
List<IndexEmployee> data=new ArrayList<>();
for (int i = 0; i < size; i++) {
IndexEmployee employee=new IndexEmployee();
employee.setDate(new java.util.Date());
employee.setId((long) i);
employee.setSalary(1000.0);
employee.setName("张三"+i);
data.add(employee);
}
return data;
}
public List<IndexEmployee> data(){
List<IndexEmployee> data=new ArrayList<>();
for (int i = 0; i < 10; i++) {
IndexEmployee employee=new IndexEmployee();
employee.setDate(new java.util.Date());
employee.setId((long) i);
employee.setSalary(1000.0);
employee.setName("张三"+i);
data.add(employee);
}
return data;
}
@Test
public void write(){
String fileName = TestFileUtil.getPath() + "indexWrite" + System.currentTimeMillis() + ".xlsx";
// 这里 需要指定写用哪个class去写,然后写到第一个sheet,名字为模板 然后文件流会自动关闭
EasyExcel.write(fileName, IndexEmployee.class).sheet("模板").doWrite(data());
}
}
看一下结果
2.5 批量读取
1)编写模型类并加入注解 2)自定义监听器 3)调用官方API完成写功能