EasyExcel导入导出Excel数据

本文主要是介绍EasyExcel导入导出Excel数据，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

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)：

1. 定义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;}
}

2. 定义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<>();}}
}

3. 定义Excel数据处理抽象ExcelImportDataHandler，用于处理导入的数据

package com.angel.ocean.excel;import java.util.List;public interface ExcelImportDataHandler<T> {void handler(List<T> data);
}

实例

1. Excel 导入实例
2. Excel 导出实例

这篇关于EasyExcel导入导出Excel数据的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---