spring上传Excel文件批量导入数据

上传Excel文件批量导入数据

controller

@RequestMapping(value = "/importTemplate", method = RequestMethod.POST)
    @ResponseBody
    public Result importTemplate(
            ComplainSubmitBean complainBean, HttpServletRequest request, HttpSession session
    ) throws Exception {
        try {
            if (null == complainBean.getUploadFile()) {
                return Result.failure(ResultCode.FAILURE, "请上传文件!");
            }
            if ("null".equals(complainBean.getMonth())) {
                return Result.failure(ResultCode.FAILURE, "请填写日期!");
            }
            User user = CommonUtils.getUser(session);
            String content = null;
            int tCount;
            InputStream inputStream = complainBean.getUploadFile().getInputStream();
            String path = getExcelPath(request);
            tCount = 2;
            path = path + "XXXXXXXX.xlsx";
            content = readExcel(inputStream, tCount, path);
            ArrayList<XXXXXXXXXXXXXXXXBean> XXXXXXXXXXXXXXXXBeans = contentToBeanList(
                    content, tCount, user, complainBean.getMonth()
            );
            complainSatisfactionService.insertList(compSatBeans);
            return Result.success("上传成功");
        } catch (Exception e) {
            log.error(e.toString());
            return Result.failure(ResultCode.FAILURE, e.getMessage());
        }
    }

    public static String readExcel(InputStream inputStream, Integer rowTitleCount, String path) throws Exception {
        InputStream inputStreamTemplate = Files.newInputStream(Paths.get(path));
        Workbook workbook = new XSSFWorkbook(inputStream);
        Workbook workbookT = new XSSFWorkbook(inputStreamTemplate);
        Sheet sheet = workbook.getSheetAt(0);
        Sheet sheetT = workbookT.getSheetAt(0);
        StringBuilder content = new StringBuilder();
        StringBuilder contentTitle = new StringBuilder();
        StringBuilder contentTitleT = new StringBuilder();
        int rowCount = 0;
        int rowCountT = 0;
        // 判断上传的Excel和对应的模板是否对应
        for (Row row : sheetT) {
            // 如果是标题栏
            if (rowCountT <= rowTitleCount) {
                for (Cell cell : row) {
                    cell.setCellType(CellType.STRING);
                    contentTitleT.append(cell.getStringCellValue()).append("\t");
                }
                rowCountT++;
            } else {
                break;
            }
        }
        for (Row row : sheet) {
            // 如果是标题栏
            if (rowCount <= rowTitleCount) {
                for (Cell cell : row) {
                    cell.setCellType(CellType.STRING);
                    contentTitle.append(cell.getStringCellValue()).append("\t");
                }
                rowCount++;
                continue;
            }
            for (Cell cell : row) {
                cell.setCellType(CellType.STRING);
                content.append(cell.getStringCellValue()).append("\t");
            }
            content.append("\n");
        }
        workbook.close();
        if (!contentTitle.toString().contentEquals(contentTitleT)) {
            throw new Exception("该模板不匹配，请上传正确的模板！");
        }
        return content.toString();
    }

Bean

import org.springframework.web.multipart.MultipartFile;

public class XXXXXXXXBean {

    private String dataType;
    private String month;
    private MultipartFile uploadFile;

    public String getMonth() {
        return month;
    }

    public void setMonth(String month) {
        this.month = month;
    }

    public MultipartFile getUploadFile() {
        return uploadFile;
    }

    public void setUploadFile(MultipartFile uploadFile) {
        this.uploadFile = uploadFile;
    }

    public String getDataType() {
        return dataType;
    }

    public void setDataType(String dataType) {
        this.dataType = dataType;
    }

}

vue文件

uploadFiles() {
      let url = "/XXXXXXXX/importTemplate";
      if (this.formData.uploadFile === null) {
        this.$notify({title: '失败', message: "请上传文件!", type: 'error'});
      } else if (this.formData.month === null) {
        this.$notify({title: '失败', message: "请填写日期!", type: 'error'});
      }
      {
        // 重要，需要创建FormData对象存储文件并传到后端
        const formData = new FormData();
        formData.append("dataType", this.formData.dataType);
        formData.append("month", this.formData.month);
        formData.append("uploadFile", this.formData.uploadFile);
        this.dataImportLoading = true;
        postFormData(url, formData).then(res => {
          if (res.resultCode === '200') {
            this.$notify({title: '成功', message: '导入成功', type: 'success'});
            this.getCSList()
            this.dataImportDialogVisible = false
          } else {
            this.$notify({title: '失败', message: res.data, type: 'error'});
          }
          this.dataImportLoading = false;
        })
      }
    }

function postFormData(url, data) {
    return new Promise((resolve, reject) => {
        axios.post(url, data,{
            headers: {
                'Content-Type':'multipart/form-data'
            }
        }).then(res => {
             resolve(res.data)
        }).catch(err => {
             reject(err)
        })
    })
}