SpringBoot集成EasyExcel

Naylor11个月前更新

4060

EasyExcel介绍

一个插件或者说是工具，用到导入导出excel/csv
快、相对省内存
阿里开源
重写 Apache POI
官网： https://easyexcel.opensource.alibaba.com/
github ： https://github.com/alibaba/easyexcel

优缺点

速度快，对于大批量数据导入可忽略性能问题。实测百万级别的表，一次性导入20W数据仅需20s，而解析excel数据到javalist仅用了4s。
自带逐行导入功能，能够实现类似分页导入的功能
使用方便，例如封装了EasyExcel.write，传一个HttpServletResponse 对象进去可以直接给调用者响应一个excel
不适合用来处理花里胡哨的、复杂的excel模板，可拓展性相对ApachePoi有阉割

示例代码

功能较为简单，主要是演示实际开发中如何快速集成文件导入导出功能。

excel模板

excel-template

业务流程

客户端请求服务端，传递一个文件（excel或者csv），服务的读取文件中的数据并保存到数据库，对于文件中的错误数据或者库里已经存在重复id的数据将不会导入，错误数据会写入一个新的excel文件，并在请求响应的时候反馈给客户端。

pom

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

全量读取excel

顾名思义就是一次性将excel中的所有数据读取到java list中

case1-controller

需要全局增加异常捕获和统一响应

@Slf4j
@AllArgsConstructor
@RestController
@RequestMapping("/import")
public class ImportController {
    private ImportService importService;
    /**
     * 方式一：
     * 1：采用名称的方式映射表格列和实体
     */
    @PostMapping("/case1")
    public void importCase1(@RequestParam("file") MultipartFile file, HttpServletResponse response) throws IOException {
        FileTypeEnum fileType = FileTypeEnum.getFileType(file.getOriginalFilename());
        List<DataImportCase1Dto> data = new ArrayList<>();
        try {
            if (FileTypeEnum.CSV.equals(fileType)) {
                //CSV格式需要显示指定ExcelType
                data = EasyExcel.read(file.getInputStream()).excelType(ExcelTypeEnum.CSV).head(DataImportCase1Dto.class).sheet().doReadSync();
            } else if (FileTypeEnum.EXCEL.equals(fileType)) {
                data = EasyExcel.read(file.getInputStream()).head(DataImportCase1Dto.class).sheet().doReadSync();
            }
        } catch (Exception e) {
            log.error("importCase1-error,e={},stackTrace={}", e.getMessage(), e.getStackTrace());
            throw new RuntimeException("导入失败");
        }
        importService.importCase1(data, response);
    }
}

case1-service

@Slf4j
@Service
public class ImportService {
    /**
     *
     * @param data
     * @param response
     * @throws IOException
     */
    public void importCase1(List<DataImportCase1Dto> data, HttpServletResponse response) throws IOException {
        Assert.notEmpty(data, "参数不可为空");
        //1:校验data中的数据是否重复，例如编号是否重复
        //2：校验data中的编号是否与库里数据重复
        //3：校验业务数据是否符合要求
        //4：执行入库
        //5:收集不符合要求的数据，反馈给调用者一个excel
        List<DataImportResultDto> errorList = new ArrayList<>();
        errorList = data.stream().map(x -> {
            DataImportResultDto error = new DataImportResultDto();
            BeanUtils.copyProperties(x, error);
            error.setExplain("这里是错误描述");
            return error;
        }).collect(Collectors.toList());
        if (!CollectionUtils.isEmpty(errorList)) {
            EasyExcel.write(response.getOutputStream())
                    //.head(DataImportResultDto.head())
                    .head(DataImportResultDto.class)
                    .excelType(ExcelTypeEnum.XLSX)
                    .sheet("错误数据")
                    .doWrite(errorList);
        }
    }
}

case1-导入dto

通过 com.alibaba.excel.annotation.ExcelProperty 注解将excel列映射到dto属性
建议所有的字段都使用string类型，这种做法增强了功能的可用性。例如：如果用户将double 类型的数据填写了中文，如果dto直接定义double，那么将发生异常，并且其他数据也无法进行导入操作。虽然数据不符合业务要求，但起码在excel转list的时候程序不应报异常，对于业务数据的校验可以放到service中去做。
dto不可加链式访问注解 @Accessors(chain = true)，加了之后读取到的数据都是null，读写反射对象用到了Cglib，加了此注解后无法被Cglib读取
若excel中的列名和dto中的属性名一样，无需指定ExcelProperty，如本示例中的address属性

@Data
public class DataImportCase1Dto {
    @ExcelProperty("编号")
    private String id;
    @ExcelProperty("名称")
    private String name;
    private String address;
    @ExcelProperty("类型")
    private String type;
    @ExcelProperty("宽")
    private String width;
    @ExcelProperty("高")
    private String height;
    @ExcelProperty("来源")
    private String source;
    @ExcelProperty("备注")
    private String remark;
}

case1-错误数据dto

在导入完毕之后，将错误数据收集起来，放在一个新的excel中，响应给调用方

@Data
public class DataImportResultDto {
    @ExcelProperty("编号")
    private String id;
    @ExcelProperty("名称")
    private String name;
    private String address;
    @ExcelProperty("类型")
    private String type;
    @ExcelProperty("宽")
    private String width;
    @ExcelProperty("高")
    private String height;
    @ExcelProperty("来源")
    private String source;
    @ExcelProperty("备注")
    private String remark;
    @ExcelProperty("错误描述")
    private String explain;
    public static List<List<String>> head() {
        List<List<String>> list = new ArrayList<List<String>>();
        List<String> head0 = new ArrayList<String>();
        head0.add("编号");
        List<String> head1 = new ArrayList<String>();
        head1.add("名称");
        List<String> head2 = new ArrayList<String>();
        head2.add("地址");
        List<String> head3 = new ArrayList<String>();
        head3.add("类型");
        List<String> head4 = new ArrayList<String>();
        head4.add("宽");
        List<String> head5 = new ArrayList<String>();
        head5.add("高");
        List<String> head6 = new ArrayList<String>();
        head6.add("来源");
        List<String> head7 = new ArrayList<String>();
        head7.add("备注");
        List<String> head8 = new ArrayList<String>();
        head8.add("错误描述");
        list.add(head0);
        list.add(head1);
        list.add(head2);
        list.add(head3);
        list.add(head4);
        list.add(head5);
        list.add(head6);
        list.add(head7);
        list.add(head8);
        return list;
    }
}

FileTypeEnum

@AllArgsConstructor
@Slf4j
@Getter
public enum FileTypeEnum {
    CSV("csv", "csv"),
    EXCEL("xlsx", "excel"),
    ;
    private final String type;
    private final String describe;
    public static FileTypeEnum getFileType(String fileName) {
        if (StringUtils.hasText(fileName)) {
            String[] array = fileName.split("\\.");
            if (array.length > 0) {
                for (FileTypeEnum item : FileTypeEnum.values()) {
                    if (item.getType().equals(array[array.length - 1])) {
                        return item;
                    }
                }
            }
        }
        return null;
    }
}

逐行读取excel

一行一行的读取excel，可引入了一个缓存的概念，每读一行将结果缓存起来，当缓存数据量达到指定阀值的时候批量操作一次入库

逐行读取的优点：

尽可能避免了OOM问题
分批之后对数据库更加友好，要知道实际生产中往往不是简单的导入，还有各种数据校验，而数据的校验会涉及很多db的读写操作
能够更加精细化的监控和管理整个导入过程

case2-controller

@PostMapping("/case2")
    public void importCase2(@RequestParam("file") MultipartFile file, HttpServletResponse response) throws IOException {
        FileTypeEnum fileType = FileTypeEnum.getFileType(file.getOriginalFilename());
        if (FileTypeEnum.CSV.equals(fileType)) {
            //CSV格式需要显示指定ExcelType
            EasyExcel.read(file.getInputStream(), new DemoDataListener(importService, response)).excelType(ExcelTypeEnum.CSV).head(DataImportCase2Dto.class).sheet().doReadSync();
        } else if (FileTypeEnum.EXCEL.equals(fileType)) {
            EasyExcel.read(file.getInputStream(), new DemoDataListener(importService, response)).head(DataImportCase2Dto.class).sheet().doReadSync();
        }
    }

case2-listener

有个很重要的点 DemoDataListener 不能被spring管理，也就是说不能添加 @Service 类似的注解。要每次读取excel都要new,然后里面用到spring容器中的组件可以通过构造方法传进去。如service等可以先在controller中注入，然后在 new listener 的时候通过构造函数传递进去。

@Slf4j
public class DemoDataListener implements ReadListener<DataImportCase2Dto> {

  /**
     * 每隔 2 条存储数据库，实际使用中可以1000条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 2;

    /**
     * 缓存的数据
     */
    private List<DataImportCase2Dto> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
    private ImportService importService;
    private HttpServletResponse response;
    public DemoDataListener(ImportService service, HttpServletResponse res) {
        this.importService = service;
        this.response = res;
    }

    /**
     * 每解析一条数据都会回调到此方法
     *
     * @param data    one row value. Is is same as {@link AnalysisContext#readRowHolder()}
     * @param context analysis context
     */
    @Override
    public void invoke(DataImportCase2Dto data, AnalysisContext context) {
        log.debug("DemoDataListener-invoke-data={}", JSON.toJSONString(data));
        //数据校验
        Boolean verifyResult = importService.verifyImportData(data);
        if (verifyResult) {
            cachedDataList.add(data);
            // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
            if (cachedDataList.size() >= BATCH_COUNT) {
                importService.importCase2(cachedDataList);
                // 存储完成清理 list
                cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
            }
        } else {
            log.debug("数据校验不通过，本行数据将放到错误数据excel中响应给调用者，即将开始解析下一行");
        }
    }

    /**
     * 所有数据解析完成会回调此方法
     *
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        importService.importCase2(cachedDataList);
        //响应错误数据excel
        importService.responseErrorData(response);
        log.debug("DemoDataListener-所有数据解析完成");
    }
    @Override
    public void onException(Exception exception, AnalysisContext context) {
        log.error("DemoDataListener-error,本行解析发生异常，但是可以继续解析下一行,msg={},stackTrace={}", exception.getMessage(), exception.getStackTrace());
        //如果是某一个单元格的转换异常 能获取到具体行号
        if (exception instanceof ExcelDataConvertException) {
            ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException) exception;
            log.error("第{}行，第{}列解析异常，数据为:{}", excelDataConvertException.getRowIndex(), excelDataConvertException.getColumnIndex(), excelDataConvertException.getCellData());
        }
    }
}

case2-service

@Slf4j
@AllArgsConstructor
@Service
public class ImportService {
    private UserService userService;

    /**
     * 错误数据集合
     */
    private List<DataImportResultDto> errorList = new CopyOnWriteArrayList<>();

    public void importCase1(List<DataImportCase1Dto> data, HttpServletResponse response) throws IOException {
        Assert.notEmpty(data, "参数不可为空");
        //1:校验data中的数据是否重复，例如编号是否重复
        //2：校验data中的编号是否与库里数据重复
        //3：校验业务数据是否符合要求
        //4：执行入库
        //5:收集不符合要求的数据，反馈给调用者一个excel
        List<DataImportResultDto> errorList = new ArrayList<>();
        errorList = data.stream().map(x -> {
            DataImportResultDto error = new DataImportResultDto();
            BeanUtils.copyProperties(x, error);
            error.setExplain("这里是错误描述");
            return error;
        }).collect(Collectors.toList());
        if (!CollectionUtils.isEmpty(errorList)) {
            EasyExcel.write(response.getOutputStream())
                    //.head(DataImportResultDto.head())
                    .head(DataImportResultDto.class)
                    .excelType(ExcelTypeEnum.XLSX)
                    .sheet("错误数据")
                    .doWrite(errorList);
        }
    }

    public void importCase2(List<DataImportCase2Dto> data) {
        log.debug("importCase2-分批导入，本次导入数据量={}", data.size());
    }

    public Boolean verifyImportData(DataImportCase2Dto data) {
        //1：校验data中的编号是否与库里数据重复
        //2：校验业务数据是否符合要求
        DataImportResultDto error = new DataImportResultDto();
        BeanUtils.copyProperties(data, error);
        error.setExplain("这里是错误描述");
        errorList.add(error);
        return false;
    }

    /**
     * 响应错误数据
     */
    public void responseErrorData(HttpServletResponse response) {
        if (errorList.size() > 0) {
            //生成一个新的excel，返回给调用者
            try {
                EasyExcel.write(response.getOutputStream())
                        //.head(DataImportResultDto.head())
                        .head(DataImportResultDto.class)
                        .excelType(ExcelTypeEnum.XLSX)
                        .sheet("错误数据")
                        .doWrite(errorList);
            } catch (IOException e) {
                log.error("responseErrorData-error,msg={},stackTrace={}", e.getMessage(), e.getStackTrace());
            }
        }
    }
}

case2导入dto

@Data
public class DataImportCase2Dto {
    @ExcelProperty("编号")
    private String id;
    @ExcelProperty("名称")
    private String name;
    private String address;
    @ExcelProperty("类型")
    private String type;
    @ExcelProperty("宽")
    private String width;
    @ExcelProperty("高")
    private String height;
    @ExcelProperty("来源")
    private String source;
    @ExcelProperty("备注")
    private String remark;
}

工作流程

解析Excel文件：EasyExcel 首先需要解析Excel文件，获取文件中的所有工作表和单元格。可以使用 POI 提供的 API 来解析 Excel 文件。
格式化数据：一旦文件被解析，EasyExcel 可以将数据格式化为所需的格式。这可以通过使用 POI 提供的各种方法来完成，如设置单元格格式、填充数据等。
生成 Java 对象：一旦数据被格式化，EasyExcel 可以将其转换为 Java 对象。这些对象可以包含数据、公式、图表等。
写入 Excel 文件：最后，EasyExcel 可以将生成的 Java 对象写入到 Excel 文件中。这可以通过使用 POI 提供的 API 来完成，如创建工作簿、创建工作表、写入数据等。

代码

https://gitee.com/naylor_personal/ramble-spring-boot/tree/master/easyexcel

4人已评分