关于SpringBoot项目利用阿里EasyExcel快捷导入Excel文件入库初始化数据的简单实现

一、问题描述

无论新项目还是旧项目，都会出现数据维护、数据初始化等操作，手动录显然很low(领导会骂你)，所以一般采用批量导入导出。这里你还在用原始读取excel逐行逐列去读取吗？2024了ok？利用工具是我们cv大师的一贯作风，so就学习一下EasyExcel吧。

二、实践步骤

2.1导入maven依赖

<!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.2.1</version>
</dependency>

你可能会问，为什么是这个版本？版本的选择根据你SpringBoot的版本，我这里是2.x

2.2代码实践

2.2.1定义实体映射，也即Excel表头一行跟实体的映射，封装ImportDataListAO

package com.juhe.digital.pojo.ao;

import com.alibaba.excel.annotation.ExcelProperty;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

/**
 * @author kiki
 * @date 2024/7/24
 * @description
 */
@Data
public class ImportDataListAO {
    @ApiModelProperty(value = "一级分类")
    @ExcelProperty(value = "一级分类",index = 0)
    private String firstClassify;

    @ApiModelProperty(value = "二级分类")
    @ExcelProperty(value = "二级分类",index = 1)
    private String secondClassify;

    @ApiModelProperty(value = "数据资源项")
    @ExcelProperty(value = "数据资源项",index = 2)
    private String dataResourceItem;

    @ApiModelProperty(value = "汇入频率")
    @ExcelProperty(value = "汇入频率",index = 3)
    private String importFrequency;

    @ApiModelProperty(value = "汇入方式")
    @ExcelProperty(value = "汇入方式",index = 4)
    private String importMode;

    @ApiModelProperty(value = "源头部门")
    @ExcelProperty(value = "源头部门",index = 5)
    private String orgName;

    @ApiModelProperty(value = "业务处室")
    @ExcelProperty(value = "业务处室",index = 6)
    private String businessOffice;

    @ApiModelProperty(value = "联系人")
    @ExcelProperty(value = "联系人",index = 7)
    private String relationUser;

    @ApiModelProperty(value = "联系方式")
    @ExcelProperty(value = "联系方式",index = 8)
    private String relationMobile;

    @ApiModelProperty(value = "表")
    @ExcelProperty(value = "表",index = 9)
    private String dataTableName;

}

2.2.2定义监听器

package com.juhe.digital.listener;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.util.ListUtils;
import com.juhe.digital.pojo.ao.ImportDataListAO;
import com.juhe.digital.service.IDataListInfoService;
import lombok.extern.slf4j.Slf4j;

import java.util.List;

/**
 * @author kiki
 * @date 2024/7/23
 * @description
 */
@Slf4j
public class DataListInfoAOListener extends AnalysisEventListener<ImportDataListAO> {

    private static final int BATCH_COUNT = 5;

    private IDataListInfoService dataListInfoService;

    private List<ImportDataListAO> cachedDataList = ListUtils.newArrayListWithCapacity(BATCH_COUNT);

    public DataListInfoAOListener(IDataListInfoService dataListInfoService) {
        this.dataListInfoService = dataListInfoService;
    }

    /**
     * 这个每一条数据解析都会来调用
     * @param ao
     * @param analysisContext
     */
    @Override
    public void invoke(ImportDataListAO ao, AnalysisContext analysisContext) {
        cachedDataList.add(ao);
        if (cachedDataList.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理 list
            cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        saveData();
    }

    private void saveData() {
        log.info("{}条数据，开始存储数据库！", cachedDataList.size());
        dataListInfoService.saveImportDataListInfo(cachedDataList);
        log.info("存储数据库成功！");
    }
}

以上准备好后，写一下简单的controller就可以了

2.2.3 FileController

	@PostMapping(value = "/import/data-resource-item")
    public CommonResult importDataResourceItem(@RequestPart("file") @NotEmpty(message = "文件不可为空") MultipartFile file) {
        if (file == null) {
            throw new BusinessException("文件不可为空");
        }
        String fileName = file.getOriginalFilename();
        if (!fileName.endsWith(".xls") && !fileName.endsWith("xlsx")) {
            throw new BusinessException("不支持的文件格式");
        }
        return fileService.importDataResourceItem(file);
    }

//IFileService 
    CommonResult importDataResourceItem(MultipartFile file);
//FileServiceImpl
	@Override
    public CommonResult importDataResourceItem(MultipartFile file) {
        try {
            EasyExcel
                    .read(file.getInputStream(), ImportDataListAO.class,new DataListInfoAOListener(dataListInfoService))
                    .sheet()
                    .doRead();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return CommonResult.success();
    }

以上就是EasyExcel在SpringBoot项目中实现Excel文件导入数据入库的基本操作了，Say Bye!