springboot+vue+antd下载模板上传Excel

一、功能需求

按照指定模板上传Excel

二、使用技术

springboot、vue、antd、easyexcel

三、功能实现

easyexcel依赖

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

1. 模板下载

public void downloadTemplate(HttpServletRequest request,HttpServletResponse response) throws Exception {
        InputStream inputStream = null;
        ServletOutputStream servletOutputStream = null;
        try {
            Resource resource = new DefaultResourceLoader().getResource("文件存放地址");
            response.setContentType("application/vnd.ms-excel");//文件类型
            response.setHeader("Content-Disposition", "attachment;fileName=" + new String("信息数据模板".getBytes(), StandardCharsets.ISO_8859_1)
                    + ".xlsx");
            inputStream = resource.getInputStream();
            servletOutputStream = response.getOutputStream();
            IOUtils.copy(inputStream, servletOutputStream);
            response.flushBuffer();
        } catch (Exception e) {
            response.setContentType("");
            response.setHeader("", "");
        } finally {
            if (servletOutputStream != null) {
                servletOutputStream.close();
            }
            if (inputStream != null) {
                inputStream.close();
            }
        }

前端

downloadTemplate() {
  uploadTemplate().then((res) => {//uploadTemplate()调用后端downloadTemplate()
    downloadExcel(res.data, '数据模板.xlsx')
  })
},

export function downloadExcel (blobPart, filename) {
  const blob = new Blob([blobPart], { type: 'application/vnd.ms-excel' })
  console.log('downloadExcel', blob.size)
  // 创建一个超链接，将文件流赋进去，然后实现这个超链接的单击事件
  const elink = document.createElement('a')
  elink.download = decodeURIComponent(filename)
  elink.style.display = 'none'
  elink.href = URL.createObjectURL(blob)
  document.body.appendChild(elink)
  elink.click()
  URL.revokeObjectURL(elink.href) // 释放URL 对象
  document.body.removeChild(elink)
}

2. excel文件上传
   -创建实体类存放Excel文件对应数据

@Data
public class ImportParam {

    @ExcelProperty(value = "小区名称")//使用该注解对应excel表头
    private String name;
    
    @ExcelProperty(value = "小区代码")
    private String code;

    @ExcelProperty(value = "街道代码")
    private String streetCode;
    ...................
}

• 创建监听

public class ImportListener extends AnalysisEventListener<ImportParam> {

    /**
     * 每隔1000条存储数据库，然后清理list,方便内存回收
     */
    private static final Integer BATCH_COUNT = 1000;

    /**
     * 缓存的数据
     */
    private List<ImportParam> list = new ArrayList<>(BATCH_COUNT);

    @Autowired
    private CommunityInfoService communityInfoService;

    public ImportListener(CommunityInfoService communityInfoService) {
        this.communityInfoService = communityInfoService;
    }

    @Override
    public void invoke(ImportParam param, AnalysisContext analysisContext) {
        list.add(param);
        if (list.size() >= BATCH_COUNT) {
            saveData();
            list = new ArrayList<>(BATCH_COUNT); //保证doAfterAllAnalysed方法执行数据为不重复数据
        }
    }
    //这里是数据全部读完之后走  为保证数据不够批量最小值也能存进数据库
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        saveData();
    }
    private void saveData() {
        communityInfoService.saveImportList(list);
    }
}

• 新增方法存储数据

@Transactional
    public void saveImportList(List<ImportParam> list) {
        List<RecoveryPoint> recoveryPoints = new ArrayList<>();
        list.forEach(item -> {
            RecoveryPoint recoveryPoint = new RecoveryPoint();
            recoveryPoint.setCommunityId(info.getId());
            recoveryPoint.setName(item.getRecoveryPointName());
            recoveryPoint.setAddr(item.getRecoveryPointAddress());
            recoveryPoints.add(recoveryPoint);
        });
        recoveryPointService.saveBatch(recoveryPoints);
    }

前端

<a-upload
  accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, application/vnd.ms-excel"
  :customRequest="customRequest"
  @change="customChange"
  :disabled="uploadDisabled"
>
  <a-button :icon="uploadIcon" :disabled="uploadDisabled" style="margin-left: 8px">导入数据</a-button>
</a-upload>

customRequest(data) {
      const file = data.file 
      const formData = new FormData()
      formData.append('file', file)
      data.onProgress()
      areaApi
        .importCommunity(formData)
        .then((res) => {
          if (res.code == 200) {
            this.$message.success('导入成功')
            data.onSuccess(res, data.file);
            this.handleSearch()
          } else {
            this.$message.error('导入失败')
          }
        })
        .finally(() => {
          this.switchIconAndStatus(false)
        })
    },
    customChange(data) {
      if (data.file.status === 'uploading') {
        this.switchIconAndStatus(true)
      } else if (data.file.status === 'done') {
        this.switchIconAndStatus(false)
      } else if (data.file.status === 'error') {
        this.switchIconAndStatus(false)
      }
    },
    switchIconAndStatus(flag) {
      if (flag) {
        this.uploadIcon = 'loading'
        this.uploadDisabled = true
      } else {
        this.uploadIcon = 'to-top'
        this.uploadDisabled = false
      }
    },