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

        // 读的excel
        EasyExcelImportListener 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;
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext arg) {
        excelImportDataHandler.handler(data);
    }

    @Override
    public 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 导出实例