EasyExcel使用

EasyExcel 简介

EasyExcel 是阿里巴巴开源的一个基于 Java 的简单、省内存的读写 Excel 工具。在处理大量数据时，它能极大地减少内存占用，提高性能。下面从依赖配置、模板使用到代码调用，进行详细介绍。

添加依赖

若要在项目里使用 EasyExcel，需在 Maven 或 Gradle 配置文件中添加相应依赖。

Maven 依赖：

<dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>3.3.2</version>
</dependency>

创建数据模型

要映射 Excel 中的数据，需创建对应的 Java 类。通过注解可指定列的索引和标题。

import com.alibaba.excel.annotation.ExcelProperty;public class DemoData {// 索引为 0，列标题是 "字符串"@ExcelProperty(index = 0, value = "字符串")private String string;// 索引为 1，列标题是 "日期"@ExcelProperty(index = 1, value = "日期")private Date date;// 索引为 2，列标题是 "数字"@ExcelProperty(index = 2, value = "数字")private Double doubleData;// 若有复杂的表头，可以使用如下方式@ExcelProperty({"主标题", "子标题"})private String complexHead;// 构造函数、Getter 和 Setter 方法public DemoData() {}public DemoData(String string, Date date, Double doubleData) {this.string = string;this.date = date;this.doubleData = doubleData;}// Getter 和 Setter 方法public String getString() {return string;}public void setString(String string) {this.string = string;}public Date getDate() {return date;}public void setDate(Date date) {this.date = date;}public Double getDoubleData() {return doubleData;}public void setDoubleData(Double doubleData) {this.doubleData = doubleData;}public String getComplexHead() {return complexHead;}public void setComplexHead(String complexHead) {this.complexHead = complexHead;}
}

读取 Excel 文件

EasyExcel 提供了多种读取 Excel 的方式，下面是一个基本示例。

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.read.builder.ExcelReaderBuilder;
import com.alibaba.excel.read.builder.ExcelReaderSheetBuilder;import java.io.File;
import java.util.ArrayList;
import java.util.List;public class ExcelReaderExample {public static void main(String[] args) {// 要读取的 Excel 文件路径String fileName = "D:\\test.xlsx";// 方法一：读取数据并处理EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead();// 方法二：读取数据并返回列表List<DemoData> dataList = EasyExcel.read(fileName).head(DemoData.class).sheet().doReadSync();System.out.println("读取的数据量: " + dataList.size());// 方法三：链式调用读取ExcelReaderBuilder readerBuilder = EasyExcel.read(fileName, DemoData.class, new DemoDataListener());ExcelReaderSheetBuilder sheetBuilder = readerBuilder.sheet();sheetBuilder.doRead();}// 监听器类，用于处理读取到的数据public static class DemoDataListener extends AnalysisEventListener<DemoData> {private static final int BATCH_COUNT = 100;private List<DemoData> list = new ArrayList<>(BATCH_COUNT);// 每读取一行数据时调用@Overridepublic void invoke(DemoData data, AnalysisContext context) {System.out.println("读取到数据: " + data);list.add(data);if (list.size() >= BATCH_COUNT) {saveData();list.clear();}}// 读取完成后调用@Overridepublic void doAfterAllAnalysed(AnalysisContext context) {saveData();System.out.println("所有数据读取完成！");}// 保存数据的方法private void saveData() {System.out.println("当前批次保存 " + list.size() + " 条数据");// 这里可以添加保存数据到数据库等操作}}
}

写入 Excel 文件

下面是使用 EasyExcel 将数据写入 Excel 文件的几种常见方法。

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;import java.io.File;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;public class ExcelWriterExample {public static void main(String[] args) {// 写入文件的路径String fileName = "D:\\write.xlsx";// 方法一：直接写入数据writeExcelDirectly(fileName);// 方法二：使用构建器写入writeExcelWithBuilder(fileName);// 方法三：写入多个 sheetwriteMultipleSheets(fileName);}// 方法一：直接写入数据private static void writeExcelDirectly(String fileName) {// 创建测试数据List<DemoData> dataList = createTestData();// 直接写入数据EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(dataList);}// 方法二：使用构建器写入private static void writeExcelWithBuilder(String fileName) {// 创建测试数据List<DemoData> dataList = createTestData();// 使用构建器写入ExcelWriterBuilder writerBuilder = EasyExcel.write(fileName, DemoData.class);ExcelWriterSheetBuilder sheetBuilder = writerBuilder.sheet("模板");sheetBuilder.doWrite(dataList);}// 方法三：写入多个 sheetprivate static void writeMultipleSheets(String fileName) {// 创建测试数据List<DemoData> dataList1 = createTestData();List<DemoData> dataList2 = createTestData();// 写入多个 sheettry (ExcelWriterBuilder writerBuilder = EasyExcel.write(fileName, DemoData.class)) {// 第一个 sheetwriterBuilder.sheet("第一个 sheet").doWrite(dataList1);// 第二个 sheetwriterBuilder.sheet("第二个 sheet").doWrite(dataList2);}}// 创建测试数据private static List<DemoData> createTestData() {List<DemoData> list = new ArrayList<>();for (int i = 0; i < 10; i++) {DemoData data = new DemoData();data.setString("字符串" + i);data.setDate(new Date());data.setDoubleData(0.56 + i);list.add(data);}return list;}
}

使用模板导出 Excel

若需要使用模板导出 Excel，可按以下方式操作。

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
import com.alibaba.excel.write.metadata.WriteSheet;import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;public class ExcelTemplateExample {public static void main(String[] args) {// 模板文件路径String templateFileName = "D:\\template.xlsx";// 输出文件路径String outputFileName = "D:\\template_output.xlsx";// 填充数据并导出fillTemplateAndExport(templateFileName, outputFileName);}private static void fillTemplateAndExport(String templateFileName, String outputFileName) {// 创建填充数据Map<String, Object> data = new HashMap<>();data.put("name", "张三");data.put("date", "2023-01-01");data.put("total", 1000.00);// 创建列表数据List<Map<String, Object>> list = new ArrayList<>();for (int i = 0; i < 5; i++) {Map<String, Object> item = new HashMap<>();item.put("line", i + 1);item.put("description", "项目" + i);item.put("amount", 100.00 + i);list.add(item);}data.put("list", list);// 使用模板填充数据EasyExcel.write(outputFileName).withTemplate(templateFileName).sheet().doFill(data);}
}

处理复杂表头和合并单元格

对于复杂表头和合并单元格的情况，EasyExcel 也能很好地处理。

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.write.merge.LoopMergeStrategy;import java.util.ArrayList;
import java.util.List;public class ComplexHeadExample {public static void main(String[] args) {// 输出文件路径String fileName = "D:\\complex_head.xlsx";// 创建测试数据List<ComplexHeadData> dataList = createComplexHeadData();// 写入复杂表头的 Excel// 每 2 行合并一次LoopMergeStrategy mergeStrategy = new LoopMergeStrategy(2, 0);EasyExcel.write(fileName, ComplexHeadData.class).registerWriteHandler(mergeStrategy).sheet("复杂表头").doWrite(dataList);}private static List<ComplexHeadData> createComplexHeadData() {List<ComplexHeadData> list = new ArrayList<>();for (int i = 0; i < 4; i++) {ComplexHeadData data = new ComplexHeadData();data.setHead1("头部1-" + i);data.setHead2("头部2-" + i);data.setHead3("头部3-" + i);data.setHead4("头部4-" + i);list.add(data);}return list;}// 复杂表头的数据模型public static class ComplexHeadData {@ExcelProperty({"主标题", "一级标题1", "二级标题1"})private String head1;@ExcelProperty({"主标题", "一级标题1", "二级标题2"})private String head2;@ExcelProperty({"主标题", "一级标题2", "二级标题3"})private String head3;@ExcelProperty({"主标题", "一级标题2", "二级标题4"})private String head4;// Getter 和 Setter 方法public String getHead1() {return head1;}public void setHead1(String head1) {this.head1 = head1;}public String getHead2() {return head2;}public void setHead2(String head2) {this.head2 = head2;}public String getHead3() {return head3;}public void setHead3(String head3) {this.head3 = head3;}public String getHead4() {return head4;}public void setHead4(String head4) {this.head4 = head4;}}
}