一、问题描述
无论新项目还是旧项目,都会出现数据维护、数据初始化等利用,手动录显然很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!
免责声明:如果侵犯了您的权益,请联系站长,我们会及时删除侵权内容,谢谢合作!更多信息从访问主页:qidao123.com:ToB企服之家,中国第一个企服评测及商务社交产业平台。 |