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

按照指定模板上传Excel

springboot、vue、antd、easyexcel

easyexcel依赖

	com.alibaba
	easyexcel
	2.2.6

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 {

    
    private static final Integer BATCH_COUNT = 1000;

    
    private List 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 list) {
        List 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);
    }

前端

  导入数据

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
      }
    },