EasyExcel的常用方法封装

约 1666 字大约 6 分钟

2024-08-29

由于工作中时不时会遇到使用EasyExcel导出的场景，而每次遇到这类场景都需要重新复习一遍EasyExcel去处理，因此本文特地记录下自己对EasyExcel常用方法的一些封装，以方便日后提升工作效率。

为什么使用EasyExcel

总结下来就是一句话：性能好，功能强！

easyexcel重写了poi对07版Excel的解析，一个3M的excel用POI sax解析依然需要100M左右内存，改用easyexcel可以降低到几M，并且再大的excel也不会出现内存溢出；03版依赖POI的sax模式，在上层做了模型转换的封装，让使用者更加简单方便。

官网地址：easyexcel.opensource.alibaba.com/docs/curren…

依赖导入

首先导入maven依赖,下边我还引入了一些其他依赖，方便测试使用使用。

<dependencies>
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>easyexcel</artifactId>
        <version>3.3.1</version>
    </dependency>
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
    </dependency>
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <scope>test</scope>
    </dependency>
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>fastjson</artifactId>
        <version>1.2.73</version>
        <scope>test</scope>
    </dependency>
</dependencies>

功能设计

然后是excel的工具类封装，工具内部封装了常用的导出功能，不过这里我全部都是采用了同步导出，代码逻辑处理起来其实并不复杂，只是api长期不用容易忘记，所以就整理了一些常用的功能给大家。

excel全页导入
excel指定页，指定头部导入
excel单页导出
excel指定页，指定头部导出

package qiyu.framework.excel.service;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.read.builder.ExcelReaderBuilder;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.WriteSheet;
import qiyu.framework.excel.config.ExportSheetInfo;
import qiyu.framework.excel.config.ImportSheetInfo;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Author idea
 * @Date: Created in 22:01 2024/6/22
 * @Description
 */
public class ExcelExportUtils {

    /**
     * 导入数据到excel第一页中
     *
     * @param fileName
     * @param path
     * @param sheetInfo
     * @throws IOException
     */
    public static <T> void exportExcel(String fileName, String path, ExportSheetInfo sheetInfo) {
        File exportFile = new File(path + "/" + fileName);
        try (ExcelWriter excelWriter = EasyExcel.write(exportFile).build()) {
            WriteSheet writeSheet;
            writeSheet = EasyExcel.writerSheet(sheetInfo.getSheetName()).head(sheetInfo.getHeadClass()).build();
            excelWriter.write(sheetInfo.getDataList(), writeSheet);
            excelWriter.finish();
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 分页导出excel
     *
     * @param fileName
     * @param path
     * @param sheetDataList
     * @throws IOException
     */
    public static <T> void exportExcel(String fileName, String path, List<ExportSheetInfo> sheetDataList) {
        File exportFile = new File(path + "/" + fileName);
        try (ExcelWriter excelWriter = EasyExcel.write(exportFile).build()) {
            WriteSheet writeSheet;
            for (ExportSheetInfo sheetInfo : sheetDataList) {
                writeSheet = EasyExcel.writerSheet(sheetInfo.getSheetName()).head(sheetInfo.getHeadClass()).build();
                excelWriter.write(sheetInfo.getDataList(), writeSheet);
            }
            excelWriter.finish();
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 默认读取第一页excel内容
     *
     * @param file
     * @param headClass
     * @param <T>
     * @return 第一页的数据内容
     * @throws IOException
     */
    public static <T> List<T> read(File file, Class<T> headClass) throws IOException {
        ExcelReaderBuilder excelReaderBuilder = EasyExcel.read(new FileInputStream(file), headClass, null);
        excelReaderBuilder.excelType(ExcelTypeEnum.XLSX);
        excelReaderBuilder.autoCloseStream(true);
        return excelReaderBuilder.doReadAllSync();
    }

    /**
     * 读取指定页excel的数据内容
     *
     * @param file
     * @param importSheetInfoList
     * @return 每一页数据内容，map格式
     */
    public static Map<String, List> read(File file, List<ImportSheetInfo> importSheetInfoList) {
        try {
            Map<String, List> excelDataMap = new HashMap<>();
            ExcelReaderBuilder excelReaderBuilder = EasyExcel.read(file);
            for (ImportSheetInfo importSheetInfo : importSheetInfoList) {
                List currentSheetDataList = excelReaderBuilder.sheet(importSheetInfo.getSheetName()).head(importSheetInfo.getHeadClass()).doReadSync();
                excelDataMap.put(importSheetInfo.getSheetName(), currentSheetDataList);
            }
            return excelDataMap;
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

}

上边工具针对多Sheet的导入导出封装了特定的对象，代码如下：

package qiyu.framework.excel.config;


/**
 * @Author idea
 * @Date: Created in 10:19 2024/6/23
 * @Description 导入数据使用的参数
 */
public class ImportSheetInfo<T> {

    private String sheetName;
    private Class<T> headClass;

    public String getSheetName() {
        return sheetName;
    }

    public void setSheetName(String sheetName) {
        this.sheetName = sheetName;
    }

    public Class<T> getHeadClass() {
        return headClass;
    }

    public void setHeadClass(Class<T> headClass) {
        this.headClass = headClass;
    }
}

package qiyu.framework.excel.config;

import java.util.List;

/**
 * @Author idea
 * @Date: Created in 10:14 2024/6/23
 * @Description 导出数据使用参数
 */
public class ExportSheetInfo<T> {

    private String sheetName;
    private List<T> dataList;
    private Class<T> headClass;

    public String getSheetName() {
        return sheetName;
    }

    public void setSheetName(String sheetName) {
        this.sheetName = sheetName;
    }

    public List<T> getDataList() {
        return dataList;
    }

    public void setDataList(List<T> dataList) {
        this.dataList = dataList;
    }

    public Class<T> getHeadClass() {
        return headClass;
    }

    public void setHeadClass(Class<T> headClass) {
        this.headClass = headClass;
    }
}

代码测试

接下来我们可以定义一些实体对象，用于测试导出导入的逻辑。下边是我的两个实体对象，分别代表了订单数据和用户数据的含义。实体对象里面的ExcelProperty注解就是导出的excel头部文案，ColumnWidth是该字段的宽度，这两个属性经常会使用到。

package qiyu.framework.excel.model;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import lombok.Data;

import java.math.BigDecimal;
import java.util.Date;

/**
 * @Author idea
 * @Date: Created in 21:57 2024/6/22
 * @Description 订单实体对象
 */
@Data
public class OrderModel {

    @ExcelProperty("订单金额")
    private BigDecimal num;

    @ExcelProperty("订单id")
    private String orderId;

    @ExcelProperty("备注")
    @ColumnWidth(50)
    private String remark;

    @ExcelProperty("创建时间")
    @ColumnWidth(50)
    private Date createTime;

    @ExcelProperty("更新时间")
    @ColumnWidth(50)
    private Date updateTime;
}

package qiyu.framework.excel.model;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import lombok.Data;

import java.math.BigDecimal;
import java.util.Date;

/**
 * @Author idea
 * @Date: Created in 21:57 2024/6/22
 * @Description 用户实体对象
 */
@Data
public class UserModel {

    @ExcelProperty("用户姓名")
    @ColumnWidth(50)
    private String name;

    @ExcelProperty("性别")
    private Integer sex;

    @ExcelProperty("创建时间")
    @ColumnWidth(50)
    private Date createTime;

    @ExcelProperty("更新时间")
    @ColumnWidth(50)
    private Date updateTime;
}

最后是基于junit4的测试用例代码，大家可以修改导出导入的文件地址方便测试：

package qiyu.framework.excel.test;

import com.alibaba.fastjson.JSON;
import org.junit.Before;
import org.junit.Test;
import qiyu.framework.excel.config.ExportSheetInfo;
import qiyu.framework.excel.config.ImportSheetInfo;
import qiyu.framework.excel.model.OrderModel;
import qiyu.framework.excel.model.UserModel;
import qiyu.framework.excel.service.ExcelExportUtils;

import java.io.File;
import java.io.IOException;
import java.math.BigDecimal;
import java.util.*;

/**
 * @Author idea
 * @Date: Created in 22:08 2024/6/22
 * @Description
 */
public class TestExcelExportUtilsSuite {


    private List<OrderModel> orderModelList = new ArrayList<>();
    private List<UserModel> userModelList = new ArrayList<>();

    //excel文件的所在目录
    private String exportFilePath = "/Users/tester/IdeaProjects/qiyu-framework-gitee/qiyu-framework/qiyu-framework-excel/src/main/resources";
    //excel文件名
    private String exportFileName = "测试excel导出文件1.xlsx";

    @Before
    public void setUp() {
        for (int i = 0; i < 100; i++) {
            OrderModel orderModel = new OrderModel();
            orderModel.setOrderId(UUID.randomUUID().toString());
            orderModel.setNum(new BigDecimal(i));
            orderModel.setRemark("这是一条备注");
            orderModel.setCreateTime(new Date());
            orderModel.setUpdateTime(new Date());
            orderModelList.add(orderModel);
        }
        for (int i = 0; i < 100; i++) {
            UserModel userModel = new UserModel();
            userModel.setSex(1);
            userModel.setName(UUID.randomUUID().toString());
            userModel.setCreateTime(new Date());
            userModel.setUpdateTime(new Date());
            userModelList.add(userModel);
        }
    }

    @Test
    public void testExport() throws IOException {
        ExportSheetInfo exportSheetInfo = new ExportSheetInfo();
        exportSheetInfo.setSheetName("sheet-1");
        exportSheetInfo.setDataList(orderModelList);
        exportSheetInfo.setHeadClass(OrderModel.class);
        ExcelExportUtils.exportExcel(exportFileName, exportFilePath, exportSheetInfo);
        System.out.println("导出结束");
    }

    @Test
    public void testExcelExport() throws IOException {
        List<ExportSheetInfo> exportSheetInfoList = new ArrayList<>();
        ExportSheetInfo orderSheetInfo = new ExportSheetInfo();
        orderSheetInfo.setSheetName("sheet-1");
        orderSheetInfo.setDataList(orderModelList);
        orderSheetInfo.setHeadClass(OrderModel.class);

        ExportSheetInfo userSheetInfo = new ExportSheetInfo();
        userSheetInfo.setSheetName("sheet-2");
        userSheetInfo.setDataList(userModelList);
        userSheetInfo.setHeadClass(UserModel.class);
        exportSheetInfoList.add(orderSheetInfo);
        exportSheetInfoList.add(userSheetInfo);
        ExcelExportUtils.exportExcel(exportFileName, exportFilePath, exportSheetInfoList);
    }

    @Test
    public void testReadExcel() throws IOException {
        List<OrderModel> orderModels = ExcelExportUtils.read(new File(exportFilePath + "/" + exportFileName), OrderModel.class);
        System.out.println(JSON.toJSONString(orderModels));
    }

    @Test
    public void testMultiSheetReadExcel() {
        List<ImportSheetInfo> importSheetInfoList = new ArrayList<>();
        ImportSheetInfo orderModelSheet = new ImportSheetInfo();
        orderModelSheet.setHeadClass(OrderModel.class);
        orderModelSheet.setSheetName("sheet-1");
        ImportSheetInfo userModelSheet = new ImportSheetInfo();
        userModelSheet.setHeadClass(UserModel.class);
        userModelSheet.setSheetName("sheet-2");

        importSheetInfoList.add(orderModelSheet);
        importSheetInfoList.add(userModelSheet);

        Map<String,List> excelDataResultMap = ExcelExportUtils.read(new File(exportFilePath + "/" + exportFileName), importSheetInfoList);
        System.out.println(JSON.toJSONString(excelDataResultMap));
    }
}

导出文件测试效果：

多sheet导出正常

指定sheet导入加载正常

小节

easyexcel还有很多丰富的api，你可以结合复杂的逻辑对excel的样式进行各种调整，这里我只整理了最常用的几种api，以帮助大家提升工作效率。像异步导出，监听器，极速模式这些大家可以到官网再去查询下。

easyexcel官网：easyexcel.opensource.alibaba.com/qa/

原文链接: https://juejin.cn/post/7382979793740431400

文章标签

后端开发程序员