EasyExcel 是阿里巴巴开源的一个用于读写Excel文件的Java库。它提供了简单的API，可以方便地操作Excel文件，特别适合处理大数据量的Excel文件。
EasyExcel的主要特点:
- 高性能：设计用于高效处理大数据量。
- 内存管理：采用流模式读写Excel文件，避免内存溢出。
- 注解支持：通过注解将Java对象映射到Excel行和列。
- 灵活性：支持简单和复杂的Excel文件结构。
基本用法:
将EasyExcel添加到项目中，如果你使用Maven，可以在pom.xml中添加依赖
<dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>3.3.2</version>
</dependency>
Excel导入导出SDK封装(ocean-easyexcel)：
- 定义Excel工具类EasyExcelUtil，封装Excel导入导出功能
package com.angel.ocean.excel;import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.handler.WriteHandler;
import lombok.extern.slf4j.Slf4j;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.net.HttpURLConnection;
import java.net.URL;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.List;/*** Excel工具类*/
@Slf4j
public class EasyExcelUtil {private EasyExcelUtil() {}/*** 导出excel** @param response HttpServletResponse* @param fileName 文件名称* @param sheetName 单元薄名称* @param list 导出数据* @param classType 导出对象* @param <T> 结果*/public static <T> void writeExcel(HttpServletResponse response, String fileName, String sheetName, List<T> list, Class<T> classType, WriteHandler... writeHandler) {// 此处ContentType不要变，前端会根据此类型手动创建Blob对象进行下载response.setContentType("application/vnd.ms-excel;charset=UTF-8");response.addHeader("charset", "utf-8");String encodeName = null;try {encodeName = URLEncoder.encode(fileName, StandardCharsets.UTF_8.toString());response.setHeader("Content-disposition", "attachment;filename=" + encodeName);ExcelWriterBuilder writerBuilder = EasyExcel.write(response.getOutputStream(), classType);for (WriteHandler handler : writeHandler) {writerBuilder.registerWriteHandler(handler);}writerBuilder.sheet(sheetName).doWrite(list);} catch (UnsupportedEncodingException e) {log.error("UnsupportedEncodingException", e);} catch (IOException e) {log.error("IOException", e);}}/*** 导出excel模板** @param response HttpServletResponse* @param fileName 文件名称* @param sheetName 单元薄名称* @param head 表头* @param writeHandler 自定义处理器*/public static void writeExcelTemplate(HttpServletResponse response, String fileName, String sheetName, List<List<String>> head, WriteHandler... writeHandler) {response.setContentType("application/vnd.ms-excel;charset=UTF-8");response.addHeader("charset", "utf-8");String encodeName = null;try {encodeName = URLEncoder.encode(fileName, StandardCharsets.UTF_8.toString());response.setHeader("Content-disposition", "attachment;filename=" + encodeName);ExcelWriterBuilder writerBuilder = EasyExcel.write(response.getOutputStream()).inMemory(true);for (WriteHandler handler : writeHandler) {writerBuilder.registerWriteHandler(handler);}writerBuilder.sheet(sheetName).head(head);} catch (UnsupportedEncodingException e) {log.error("UnsupportedEncodingException", e);} catch (IOException e) {log.error("IOException", e);}}/*** 数据解析处理* @param excelImportDataHandler* @param inputStream* @param pojoClass* @param sheetNo* @param headRowNumber* @param <T>*/public static <T> void readExcel(ExcelImportDataHandler excelImportDataHandler, InputStream inputStream, Class<T> pojoClass, Integer sheetNo, Integer headRowNumber) {// 读的excelEasyExcelImportListener easyExcelImportListener = new EasyExcelImportListener(excelImportDataHandler);// 先读取第一个sheet页ExcelReader excelReader = EasyExcel.read(inputStream, pojoClass, easyExcelImportListener).build();ReadSheet readSheet = EasyExcel.readSheet(sheetNo).headRowNumber(headRowNumber).build();excelReader.read(readSheet);// 这里千万别忘记关闭，读的时候会创建临时文件，到时磁盘会崩的excelReader.finish();}/*** 根据文件地址获取输入流*/public static InputStream getInputStream(String fileUrl) {InputStream inputStream = null;try {HttpURLConnection conn = (HttpURLConnection) new URL(fileUrl).openConnection();conn.setDoInput(true);conn.connect();inputStream = conn.getInputStream();} catch (IOException e) {log.error("IOException", e);}return inputStream;}
}- 定义Excel数据导入监听EasyExcelImportListener（继承AnalysisEventListener）
package com.angel.ocean.excel;import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import lombok.extern.slf4j.Slf4j;
import java.util.ArrayList;
import java.util.List;@Slf4j
@SuppressWarnings({"all"})
public class EasyExcelImportListener extends AnalysisEventListener<Object> {private ExcelImportDataHandler excelImportDataHandler;private List<Object> data = new ArrayList<>();public EasyExcelImportListener(ExcelImportDataHandler excelImportDataHandler) {this.excelImportDataHandler = excelImportDataHandler;}@Overridepublic void doAfterAllAnalysed(AnalysisContext arg) {excelImportDataHandler.handler(data);}@Overridepublic void invoke(Object object, AnalysisContext context) {data.add(object);if(data.size() > 10) {// 根据自己的业务去处理excelImportDataHandler.handler(data);data = new ArrayList<>();}}
}
- 定义Excel数据处理抽象ExcelImportDataHandler，用于处理导入的数据
package com.angel.ocean.excel;import java.util.List;public interface ExcelImportDataHandler<T> {void handler(List<T> data);
}
实例
1. Excel 导入实例
2. Excel 导出实例
