Springboot 使用 EasyExcel 导入导出 Excel

超强辅助 Bolt.diy ⼀步搞定创意建站

一、简介

Java解析、生成Excel比较有名的框架有Apache poi、jxl。但他们都存在一个严重的问题就是非常的耗内存，poi有一套SAX模式的API可以一定程度的解决一些内存溢出的问题，但POI还是有一些缺陷，比如07版Excel解压缩以及解压后存储都是在内存中完成的，内存消耗依然很大。
easyexcel重写了poi对07版Excel的解析，一个3M的excel用POI sax解析依然需要100M左右内存，改用easyexcel可以降低到几M，并且再大的excel也不会出现内存溢出；03版依赖POI的sax模式，在上层做了模型转换的封装，让使用者更加简单方便

• 官方网站：https://easyexcel.opensource.alibaba.com/
• github地址：https://github.com/alibaba/easyexcel
• gitee地址：https://gitee.com/easyexcel/easyexcel

更多的参数理解请看这里：https://gitee.com/easyexcel/easyexcel/blob/master/docs/API.md

二、maven依赖

如果项目中没有使用过poi,且jdk版本在8-17之间，直接使用最新版本，别犹豫。

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.1.0</version>
</dependency>

如果项目中已经使用过poi或者jdk版本小于8的，请参看下面表格做出选择。

版本其他问题可以看这个：https://easyexcel.opensource.alibaba.com/qa

三、代码使用

这里我们就使用最简单的注解来实现导入导出

3.1、excel导出

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

import java.io.Serializable;
import java.util.Date;

/**
 * @author gzq
 * @date 2024/1/16
 */
@Data
public class Goods implements Serializable {

    private static final long serialVersionUID = 1L;

    @ExcelProperty("ID")
    private Integer id;

    @ExcelProperty("数量")
    private Integer num;

    @ExcelProperty("名称")
    private String name;

    /** 状态：0上架，1下架 */
    @ExcelProperty(value = "状态", converter = GoodsStatusConverter.class)
    private Integer status;

    @ColumnWidth(20)
    @ExcelProperty("时间")
    @DateTimeFormat("yyyy-MM-dd HH:mm:ss")
    private Date createDate;

}

@ExcelProperty这个注解不管加不加，Excel导入导出都会映射对象实体的所有属性。如果加上@ExcelIgnoreUnannotated注解，只有加@ExcelProperty的属性才会映射。

这里我们使用的核心注解：

• @ExcelProperty：用于匹配excel和实体类的匹配。
• @ExcelIgnore：默认所有字段都会和excel去匹配，加了这个注解会忽略该字段。
• @ExcelIgnoreUnannotated：默认不加ExcelProperty 的注解的都会参与读写，加了不会参与读写。
• @DateTimeFormat：日期转换，用String去接收excel日期格式的数据会调用这个注解。
• @NumberFormat：数字转换，用String去接收excel数字格式的数据会调用这个注解。

除了核心注解， EasyExcel 还提供单元样式注解：

这里我们还是用了转换器，用于商品状态的转换。比如我们数据库的状态存的是0、1，分别代表上架，下架。这时候就需要转换器把数据库的0、1来转换到Excel里面的上架，下架显示了。

import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.converters.ReadConverterContext;
import com.alibaba.excel.converters.WriteConverterContext;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.data.WriteCellData;

/**
 * 商品状态转换器
 * @author piao
 * @date 2024/1/16
 */
public class GoodsStatusConverter implements Converter<Integer> {

    @Override
    public Class<?> supportJavaTypeKey() {
        // 对象属性类型
        return Integer.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        // 单元格数据类型
        return CellDataTypeEnum.STRING;
    }

    @Override
    public Integer convertToJavaData(ReadConverterContext<?> context) {
        // 单元格转对象属性
        String cellStr = context.getReadCellData().getStringValue();
        if (StrUtil.isEmpty(cellStr)) {
            return null;
        }

        switch (cellStr) {
            case "上架":
                return 0;
            case "下架":
                return 1;
            default:
                return null;
        }
    }

    @Override
    public WriteCellData<?> convertToExcelData(WriteConverterContext<Integer> context) {
        // 对象属性转单元格
        Integer cellValue = context.getValue();
        if (cellValue == null) {
            return new WriteCellData<>("");
        }

        switch (cellValue) {
            case 0:
                return new WriteCellData<>("上架");
            case 1:
                return new WriteCellData<>("下架");
            default:
                return new WriteCellData<>("");
        }
    }

}

最后我们通过访问控制器，来下载导出的Excel文档。

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.sun.deploy.net.URLEncoder;
import lombok.SneakyThrows;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Random;

/**
 * @author piao
 * @date 2024/1/16
 */
@Controller
@RequestMapping("/easyExcel")
public class ExcelController {

    @SneakyThrows(IOException.class)
    @GetMapping(value = "/export")
    public void exportMemberList(HttpServletResponse response) {
        setExcelRespProp(response, "商品列表");
        List<Goods> goodsList = getData();
        EasyExcel.write(response.getOutputStream())
                .head(Goods.class)
                .excelType(ExcelTypeEnum.XLSX)
                .sheet("商品列表")
                //设置字段宽度为自动调整
                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                .doWrite(goodsList);
    }

    /**
     * 设置excel下载响应头属性
     */
    private void setExcelRespProp(HttpServletResponse response, String rawFileName) throws UnsupportedEncodingException {
        // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileName = URLEncoder.encode(rawFileName, "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
    }

    /**
     * 获取测试数据
     * @return
     */
    private List<Goods> getData() {
        Random random = new Random();
        List<Goods> goodsList = new ArrayList<>(10);
        for (int i = 1; i <= 10; i++) {
            Goods goods = new Goods();
            goods.setId(i);
            goods.setNum(random.nextInt(100));
            goods.setName("类型" + i);
            goods.setStatus(random.nextInt(2));
            goods.setCreateDate(new Date());
            goodsList.add(goods);
        }

        return goodsList;
    }

}

访问地址：http://127.0.0.1:8208/easyExcel/export

我们打开下载的Excel，如图下：

3.2、Excel导入

这里我们在Controller，添加导入的方法

/**
 * @author piao
 * @date 2024/1/16
 */
@Controller
@RequestMapping("/easyExcel")
public class ExcelController {

    @SneakyThrows
    @PostMapping(value = "/import")
    @ResponseBody
    public List<Goods> exportMemberList(MultipartFile file) {
        List<Goods> goodsList = EasyExcel.read(file.getInputStream())
                .head(Goods.class)
                .sheet()
                .doReadSync();

        return goodsList;
    }

}

然后，我们使用PostMan来测试上传Excel。

我们看到我们导入的数据正常返回了。

3.3、复杂导出

这里来实现一个复杂的一对多导出功能，这里在Github EasyExcel 里面 issues 看到的，挺有意思的。

这里我们对Goods这个实体类改在一下。

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

import java.io.Serializable;
import java.util.Date;

/**
 * @author gzq
 * @date 2024/1/16
 */
@Data
public class Goods implements Serializable {

    private static final long serialVersionUID = 1L;

    @ExcelProperty("分类ID")
    private String id;

    @ExcelProperty("分类标题")
    private String title;

    @ExcelProperty(value = {"商品信息", "商品ID"})
    private Integer goodsId;

    @ExcelProperty(value = {"商品信息", "商品数量"})
    private Integer goodsNum;

    @ExcelProperty(value = {"商品信息", "商品名称"})
    private String goodsName;

    /** 状态：0上架，1下架 */
    @ExcelProperty(value = {"商品信息", "商品ID"}, converter = GoodsStatusConverter.class)
    private Integer goodsStatus;

    @ColumnWidth(20)
    @DateTimeFormat("yyyy-MM-dd HH:mm:ss")
    @ExcelProperty(value = {"商品信息", "商品时间"})
    private Date goodsCreateDate;

}

在修改一下Controller的初始化测试数据方法。

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.sun.deploy.net.URLEncoder;
import lombok.SneakyThrows;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;

import javax.servlet.http.HttpServletResponse;
import java.io.UnsupportedEncodingException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Random;

/**
 * @author gzq
 * @date 2024/1/16
 */
@Controller
@RequestMapping("/easyExcel")
public class ExcelController {

    @SneakyThrows
    @GetMapping(value = "/export")
    public void exportMemberList(HttpServletResponse response) {
        setExcelRespProp(response, "商品列表");
        List<Goods> goodsList = getData();
        EasyExcel.write(response.getOutputStream())
                .head(Goods.class)
                .excelType(ExcelTypeEnum.XLSX)
                .sheet("商品列表")
                .doWrite(goodsList);
    }

    /**
     * 设置excel下载响应头属性
     */
    private void setExcelRespProp(HttpServletResponse response, String rawFileName) throws UnsupportedEncodingException {
        // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileName = URLEncoder.encode(rawFileName, "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
    }

    /**
     * 获取测试数据
     * @return
     */
    private List<Goods> getData() {
        Random random = new Random();
        List<Goods> goodsList = new ArrayList<>(10);
        for (int i = 1; i <= 10; i++) {
            Goods goods = new Goods();
            int typeId = random.nextInt(3);
            goods.setId(typeId + "");
            goods.setTitle("分类标题" + typeId);
            goods.setGoodsId(i);
            goods.setGoodsNum(random.nextInt(100));
            goods.setGoodsName("类型" + i);
            goods.setGoodsStatus(random.nextInt(2));
            goods.setGoodsCreateDate(new Date());
            goodsList.add(goods);
        }

        return goodsList;
    }

}

最后我们导出来的是如下图：

我们可以看到这种展示效果并不好，我们想要的应该相同的分类应该进行合并处理，右边去对应合并后的分类信息。具体什么样呢？我们来改下代码看效果。

这里我们需要增加一个合并注解，和一个合并策略代码。

合并注解：

import java.lang.annotation.*;

/**
 * @author piao
 * @date 2024/1/16
 */
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Inherited
public @interface CustomMerge {

    /**
     * 是否需要合并单元格
     */
    boolean needMerge() default false;

    /**
     * 如果该字段相同的行合并
     */
    boolean isTak() default false;

}

合并策略：

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.write.handler.RowWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;

import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;

/**
 * @author piao
 * @date 2024/1/16
 */
public class CustomMergeStrategy implements RowWriteHandler {

    /** 主键下标 */
    private Integer pkIndex;

    /** 需要合并的列的下标集合 */
    private List<Integer> needMergeColumnIndex = new ArrayList<>();

    /**
     * DTO数据类型
     */
    private Class<?> elementType;

    public CustomMergeStrategy(Class<?> elementType) {
        this.elementType = elementType;
    }

    @Override
    public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {
        // 如果是标题,则直接返回
        if (isHead) {
            return;
        }

        // 获取当前sheet
        Sheet sheet = writeSheetHolder.getSheet();
        if (null == pkIndex) {
            this.lazyInit(writeSheetHolder);
        }

        // 判断是否需要和上一行进行合并
        // 不能和标题合并，只能数据行之间合并
        if (row.getRowNum() <= 1) {
            return;
        }
        // 获取上一行数据
        Row lastRow = sheet.getRow(row.getRowNum() - 1);
        // 将本行和上一行是同一类型的数据(通过主键字段进行判断)，则需要合并
        if (lastRow.getCell(pkIndex).getStringCellValue().equalsIgnoreCase(row.getCell(pkIndex).getStringCellValue())) {
            for (Integer needMerIndex : needMergeColumnIndex) {
                CellRangeAddress cellRangeAddress = new CellRangeAddress(row.getRowNum() - 1, row.getRowNum(),
                        needMerIndex, needMerIndex);
                sheet.addMergedRegionUnsafe(cellRangeAddress);
            }
        }
    }

    /**
     * 初始化主键下标和需要合并字段的下标
     */
    private void lazyInit(WriteSheetHolder writeSheetHolder) {
        // 获取当前sheet
        Sheet sheet = writeSheetHolder.getSheet();
        // 获取标题行
        Row titleRow = sheet.getRow(0);
        // 获取DTO的类型
        Class<?> eleType = this.elementType;

        // 获取DTO所有的属性
        Field[] fields = eleType.getDeclaredFields();
        // 遍历所有的字段，因为是基于DTO的字段来构建excel，所以字段数 >= excel的列数
        for (Field theField : fields) {
            // 获取@ExcelProperty注解，用于获取该字段对应在excel中的列的下标
            ExcelProperty easyExcelAnno = theField.getAnnotation(ExcelProperty.class);
            // 为空,则表示该字段不需要导入到excel,直接处理下一个字段
            if (null == easyExcelAnno) {
                continue;
            }
            // 获取自定义的注解，用于合并单元格
            CustomMerge customMerge = theField.getAnnotation(CustomMerge.class);
            // 没有@CustomMerge注解的默认不合并
            if (null == customMerge) {
                continue;
            }

            for (int index = 0; index < fields.length; index++) {
                Cell theCell = titleRow.getCell(index);
                // 当配置为不需要导出时，返回的为null，这里作一下判断，防止NPE
                if (null == theCell) {
                    continue;
                }
                // 将字段和excel的表头匹配上
                if (easyExcelAnno.value()[0].equalsIgnoreCase(theCell.getStringCellValue())) {
                    if (customMerge.isTak()) {
                        pkIndex = index;
                    }

                    if (customMerge.needMerge()) {
                        needMergeColumnIndex.add(index);
                    }
                }
            }
        }

        // 没有指定主键，则异常
        if (null == this.pkIndex) {
            throw new IllegalStateException("使用@CustomMerge注解必须指定主键");
        }
    }

}

我们还要修改Goods实体类，使用我们自定义的合并注解

@Data
public class Goods implements Serializable {

    private static final long serialVersionUID = 1L;

    @CustomMerge(needMerge = true, isTak = true)
    @ExcelProperty("分类ID")
    private String id;

    @CustomMerge(needMerge = true)
    @ExcelProperty("分类标题")
    private String title;

    @ExcelProperty(value = {"商品信息", "商品ID"})
    private Integer goodsId;

    @ExcelProperty(value = {"商品信息", "商品数量"})
    private Integer goodsNum;

    @ExcelProperty(value = {"商品信息", "商品名称"})
    private String goodsName;

    /** 状态：0上架，1下架 */
    @ExcelProperty(value = {"商品信息", "商品ID"}, converter = GoodsStatusConverter.class)
    private Integer goodsStatus;

    @ColumnWidth(20)
    @DateTimeFormat("yyyy-MM-dd HH:mm:ss")
    @ExcelProperty(value = {"商品信息", "商品时间"})
    private Date goodsCreateDate;

}

最后我们在修改一下Controller，使用我们新建的策略。

    @SneakyThrows
    @GetMapping(value = "/export")
    public void exportMemberList(HttpServletResponse response) {
        setExcelRespProp(response, "商品列表");
        List<Goods> goodsList = getData();
        goodsList = goodsList.stream().sorted(Comparator.comparing(Goods::getId)).collect(Collectors.toList());
        EasyExcel.write(response.getOutputStream())
                .head(Goods.class)
                .excelType(ExcelTypeEnum.XLSX)
                .registerWriteHandler(new CustomMergeStrategy(Goods.class))
                .sheet("商品列表")
                .doWrite(goodsList);
    }

现在我们访问导出接口，效果如下：