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;}}
}