SpringBoot 集成 EasyExcel 3.x 实现 Excel 导出

没有昵称的打工仔

已于 2023-07-07 18:06:51 修改

阅读量1.7k

点赞数 3

文章标签： excel

于 2023-07-07 17:45:42 首次发布

本文链接：https://blog.csdn.net/weixin_43522117/article/details/131596342

版权

EasyExcel官方文档

EasyExcel是什么？

EasyExcel注解

springboot集成EasyExcel

EasyExcel官方文档

EasyExcel官方文档 - 基于Java的Excel处理工具 | Easy Excel

EasyExcel是什么？

EasyExcel是一个基于Java的、快速、简洁、解决大文件内存溢出的Excel处理工具.他能让你在不用考虑性能、内存的等因素的情况下，快速完成Excel的读、写等功能。

EasyExcel注解

@ExcelProperty: 核心注解，value属性可用来设置表头名称，converter属性可以用来设置类型转换器
@ColumnWidth: 用于设置表格列的宽度
@DateTimeFormat: 用于设置日期转换格式
@NumberFormat: 用于设置数字转换格式
@ExcelIgnore：默认所有字段都会和excel去匹配，加了这个注解会忽略该字段
@ExcelIgnoreUnannotated：默认不加 ExcelProperty 的注解的都会参与读写，加了不会参与读写

具体使用请参考：

读Excel：读Excel | Easy Excel

写Excel：写Excel | Easy Excel

springboot集成EasyExcel

springboot集成EasyExcel3.x非常简单，只需要引入以下依赖即可。

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

简单入门导出：

接下来使用EasyExcel实现简单导出功能。

实体类

导入数据还是导出数据都可以想象成具体某个对象的集合。

@Data
public class StudentDo {
    @ExcelProperty("用户编号")
    @ColumnWidth(20)
    private Long id;

    @ExcelProperty("用户名")
    @ColumnWidth(20)
    private String studentName;

    @ExcelIgnore
    private String password;

    @ExcelProperty("生日")
    @ColumnWidth(20)
    @DateTimeFormat("yyyy-MM-dd")
    private Date birthday;

    @ExcelProperty("身高（米）")
    @NumberFormat("#.##")
    @ColumnWidth(20)
    private Double height;

    @ExcelProperty(value = "性别", converter = GenderConverter.class)
    @ColumnWidth(10)
    private Integer gender;
}

自定义转换类

导出excel数据时，可能会遇到XXX状态、XXX类型和性别等需要转换的字段，例如：0->男，1->女。需实现Converter接口来自定义转换器。

orElse(null)与orElseGet(null)区别：

orElse(null)表示如果一个都没找到返回null。【orElse()中可以塞默认值。如果找不到就会返回orElse中你自己设置的默认值。】

orElseGet(null)表示如果一个都没找到返回null。【orElseGet()中可以塞默认值。如果找不到就会返回orElseGet中你自己设置的默认值。】

区别就是在使用方法时，即时时没有值也会执行 orElse 内的方法，而 orElseGet则不会。

Enum：

@Getter
@AllArgsConstructor
public enum GenderEnum {

    /**
     * 男性
     */
    MALE(0, "男性"),

    /**
     * 女性
     */
    FEMALE(1, "女性"),

    /**
     * 未知
     */
    UNKNOWN(2, "未知");

    private final Integer value;

    @JsonFormat
    private final String description;

    public static GenderEnum convert(Integer value) {
        return Stream.of(values())
                .filter(bean -> bean.value.equals(value))
                .findAny()
                .orElse(UNKNOWN);
    }

    public static GenderEnum convert(String description) {
        return Stream.of(values())
                .filter(bean -> bean.description.equals(description))
                .findAny()
                .orElse(UNKNOWN);
    }
}

Converter：

public class GenderConverter implements Converter<Integer> {
    @Override
    public Class<?> supportJavaTypeKey() {
        return Integer.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }

    @Override
    public Integer convertToJavaData(ReadConverterContext<?> context) {
        return GenderEnum.convert(context.getReadCellData().getStringValue()).getValue();
    }

    @Override
    public WriteCellData<?> convertToExcelData(WriteConverterContext<Integer> context) {
        return new WriteCellData<>(GenderEnum.convert(context.getValue()).getDescription());
    }
}

测试一下

File.separator等价于“\”

package com.springwork.high.easyexcel.controller;

/**
 * @author gj
 * @version 1.0.0
 * @date 2023/7/7 15:36
 */

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.fasterxml.jackson.core.type.TypeReference;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.springwork.high.easyexcel.vo.StudentDo;
import com.sun.deploy.net.URLEncoder;
import org.springframework.core.io.ClassPathResource;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.util.List;

/**
 * EasyExcel导入导出
 *
 * @author william@StarImmortal
 */
@RestController
@RequestMapping("/excel")
public class ExcelController {

    @GetMapping("/export/student")
    public void exportUserExcel(HttpServletResponse response) {
        try {
            this.setExcelResponseProp(response, "学生列表");
            List<StudentDo> studentList = this.getStudentList();
            EasyExcel.write(response.getOutputStream())
                    .head(StudentDo.class)
                    .excelType(ExcelTypeEnum.XLSX)
                    .sheet("学生列表")
                    .doWrite(studentList);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 设置响应结果
     *
     * @param response    响应结果对象
     * @param rawFileName 文件名
     * @throws UnsupportedEncodingException 不支持编码异常
     */
    private void setExcelResponseProp(HttpServletResponse response, String rawFileName) throws UnsupportedEncodingException {
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        String fileName = URLEncoder.encode(rawFileName, "UTF-8").replaceAll("%20", File.separator+"+");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
    }

    /**
     * 读取学生列表数据
     *
     * @return 学生数据
     * @throws IOException IO异常
     */
    private List<StudentDo> getStudentList() throws IOException {
        ObjectMapper objectMapper = new ObjectMapper();
        ClassPathResource classPathResource = new ClassPathResource("data/students.json");
        InputStream inputStream = classPathResource.getInputStream();
        return objectMapper.readValue(inputStream, new TypeReference<List<StudentDo>>() {
        });
    }
}

json:将JSON数据放在resource/data下即可

[
  {"id": 1,"studentName": "张三","password": "1234","birthday": "2000-01-01","height": "170.5","gender": 0},
  {"id": 2,"studentName": "李四","password": "1234","birthday": "2000-01-02","height": "175.5","gender": 1},
  {"id": 3,"studentName": "王五","password": "1234","birthday": "2000-01-03","height": "180.5","gender": 1},
  {"id": 4,"studentName": "赵六","password": "1234","birthday": "2000-01-04","height": "176.5","gender": 0}
]

测试：

使用postman进行测试，选择“Send and Download”选项。

复杂表头一对多导出：

由于 EasyPoi 支持嵌套对象导出，直接使用内置 @ExcelCollection 注解即可实现，但是 EasyExcel 不支持一对多导出，只能自行实现，可以通过自定义合并策略方式来实现一对多导出。

自定义注解

@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface ExcelMerge {
    /**
     * 是否合并单元格
     *
     * @return true || false
     */
    boolean merge() default true;

    /**
     * 是否为主键（即该字段相同的行合并）
     *
     * @return true || false
     */
    boolean isPrimaryKey() default false;
}

定义实体类

需要合并的字段上标明@ExcelMerge

二级表头通过@ExcelProperty(value = {"学生信息","学生编号"})设置

@Data
public class SchoolDo {

    @ExcelProperty("学校编号")
    @ColumnWidth(20)
    @ExcelMerge(merge = true,isPrimaryKey = true)
    private String schoolId;

    @ExcelProperty("学校名")
    @ColumnWidth(20)
    @ExcelMerge(merge = true)
    private String schoolName;

    @ExcelProperty(value = {"学生信息","学生编号"})
    @ColumnWidth(20)
    private Long studentId;

    @ExcelProperty(value = {"学生信息","学生名"})
    @ColumnWidth(20)
    private String studentName;

    @ExcelIgnore
    private String password;

    @ExcelProperty(value = {"学生信息","生日"})
    @ColumnWidth(20)
    @DateTimeFormat("yyyy-MM-dd")
    private Date birthday;

    @ExcelProperty(value = {"学生信息","身高（米）"})
    @NumberFormat("#.##")
    @ColumnWidth(20)
    private Double height;

    @ExcelProperty(value = {"学生信息","性别"},converter = GenderConverter.class)
    @ColumnWidth(10)
    private Integer gender;
}

自定义单元格合并策略

当 Excel 中两列主键相同时，合并被标记需要合并的列，将自定义合并策略 ExcelMergeStrategy 通过 registerWriteHandler 注册上去：


public class ExcelMergeStrategy implements RowWriteHandler {

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

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

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

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

    @Override
    public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {
        // 判断是否为标题
        if (isHead) {
            return;
        }
        // 获取当前工作表
        Sheet sheet = writeSheetHolder.getSheet();
        // 初始化主键下标和需要合并字段的下标
        if (primaryKeyIndex == null) {
            this.initPrimaryIndexAndMergeIndex(writeSheetHolder);
        }
        // 判断是否需要和上一行进行合并
        // 不能和标题合并，只能数据行之间合并
        if (row.getRowNum() <= 1) {
            return;
        }
        // 获取上一行数据
        Row lastRow = sheet.getRow(row.getRowNum() - 1);
        // 将本行和上一行是同一类型的数据（通过主键字段进行判断），则需要合并
        if (lastRow.getCell(primaryKeyIndex).getStringCellValue().equalsIgnoreCase(row.getCell(primaryKeyIndex).getStringCellValue())) {
            for (Integer mergeIndex : mergeColumnIndexList) {
                CellRangeAddress cellRangeAddress = new CellRangeAddress(row.getRowNum() - 1, row.getRowNum(), mergeIndex, mergeIndex);
                sheet.addMergedRegionUnsafe(cellRangeAddress);
            }
        }
    }

    /**
     * 初始化主键下标和需要合并字段的下标
     *
     * @param writeSheetHolder WriteSheetHolder
     */
    private void initPrimaryIndexAndMergeIndex(WriteSheetHolder writeSheetHolder) {
        // 获取当前工作表
        Sheet sheet = writeSheetHolder.getSheet();
        // 获取标题行
        Row titleRow = sheet.getRow(0);
        // 获取所有属性字段
        Field[] fields = this.elementType.getDeclaredFields();
        // 遍历所有字段
        for (Field field : fields) {
            // 获取@ExcelProperty注解，用于获取该字段对应列的下标
            ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class);
            // 判断是否为空
            if (null == excelProperty) {
                continue;
            }
            // 获取自定义注解，用于合并单元格
            ExcelMerge excelMerge = field.getAnnotation(ExcelMerge.class);
            // 判断是否需要合并
            if (null == excelMerge) {
                continue;
            }
            for (int i = 0; i < fields.length; i++) {
                Cell cell = titleRow.getCell(i);
                if (null == cell) {
                    continue;
                }
                // 将字段和表头匹配上
                if (excelProperty.value()[0].equalsIgnoreCase(cell.getStringCellValue())) {
                    if (excelMerge.isPrimaryKey()) {
                        primaryKeyIndex = i;
                    }
                    if (excelMerge.merge()) {
                        mergeColumnIndexList.add(i);
                    }
                }
            }
        }

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

测试一下

 @GetMapping("/export/school")
    public void exportOrderExcel(HttpServletResponse response) {
        try {
            this.setExcelResponseProp(response, "学校列表");
            List<SchoolDo> exportData = this.getSchoolList();
            EasyExcel.write(response.getOutputStream())
                    .head(SchoolDo.class)
                    .registerWriteHandler(new ExcelMergeStrategy(SchoolDo.class))
                    .excelType(ExcelTypeEnum.XLSX)
                    .sheet("学校列表")
                    .doWrite(exportData);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }
/**
     * 读取学校列表数据
     *
     * @return 列表数据
     * @throws IOException IO异常
     */
    private List<SchoolDo> getSchoolList() throws IOException {
        ObjectMapper objectMapper = new ObjectMapper();
        ClassPathResource classPathResource = new ClassPathResource("data/schools.json");
        InputStream inputStream = classPathResource.getInputStream();
        return objectMapper.readValue(inputStream, new TypeReference<List<SchoolDo>>() {
        });
    }

json数据：

[
  {"schoolId":"1","schoolName":"北京大学","studentId": 1,"studentName": "张三","password": "1234","birthday": "2000-01-01","height": "170.5","gender": 0},
  {"schoolId":"1","schoolName":"北京大学","studentId": 2,"studentName": "李四","password": "1234","birthday": "2000-01-02","height": "175.5","gender": 1},
  {"schoolId":"1","schoolName":"北京大学","studentId": 3,"studentName": "王五","password": "1234","birthday": "2000-01-03","height": "180.5","gender": 1},
  {"schoolId":"1","schoolName":"北京大学","studentId": 4,"studentName": "赵六","password": "1234","birthday": "2000-01-04","height": "176.5","gender": 0},
  {"schoolId":"2","schoolName":"河北大学","studentId": 1,"studentName": "张三","password": "1234","birthday": "2000-01-01","height": "170.5","gender": 0},
  {"schoolId":"2","schoolName":"河北大学","studentId": 2,"studentName": "李四","password": "1234","birthday": "2000-01-02","height": "175.5","gender": 1},
  {"schoolId":"2","schoolName":"河北大学","studentId": 3,"studentName": "王五","password": "1234","birthday": "2000-01-03","height": "180.5","gender": 1},
  {"schoolId":"2","schoolName":"河北大学","studentId": 4,"studentName": "赵六","password": "1234","birthday": "2000-01-04","height": "176.5","gender": 0}
]

测试：

使用postman进行测试，选择“Send and Download”选项。

参考文献

官方文档：

https://www.yuque.com/easyexcel/doc/easyexcel

一对多导出优雅方案：

https://github.com/alibaba/easyexcel/issues

没有昵称的打工仔

关注

3
点赞
踩
8

收藏

觉得还不错? 一键收藏
3
评论
SpringBoot 集成 EasyExcel 3.x 实现 Excel 导出

导出excel数据时，可能会遇到XXX状态、XXX类型和性别等需要转换的字段，例如：0->男，1->女。需实现Converter接口来自定义转换器。orElse(null)与orElseGet(null)区别：orElse(null)表示如果一个都没找到返回null。【orElse()中可以塞默认值。如果找不到就会返回orElse中你自己设置的默认值。orElseGet(null)表示如果一个都没找到返回null。【orElseGet()中可以塞默认值。
复制链接

扫一扫