Java+Vue导入数据

Vue

      <el-form
        ref="uploadVarietiesForm"
        :model="uploadVarietiesForm"
        :rules="uploadVarietiesRules"
        label-width="100px"
        :disabled="uploadLoading"
      >
        <el-form-item label="Excel文件" prop="file">
          <excel-file-select v-model="uploadVarietiesForm.file" :file-type="'.xlsx'" />
        </el-form-item>
      </el-form>
      <div slot="footer" class="dialog-footer">
        <el-button :disabled="uploadLoading" @click="uploadVarietiesDlgVisible = false">取 消</el-button>
        <el-button type="primary" :loading="uploadLoading" @click="uploadFktz()">确 定</el-button>
      </div>

    async uploadFktz() {
      const valid = await new Promise(resolve => this.$refs.uploadVarietiesForm.validate((valid) => resolve(valid)))
      if (valid) {
        this.uploadLoading = true
        try {
          const formData = new FormData()
          formData.append('file', this.uploadVarietiesForm.file)
          const res = await riskStandingBookApi.uploadFktzData(formData)
          this.uploadLoading = false
          this.uploadVarietiesDlgVisible = false
          if (res.code === 200) {
            this.$message.success(res.msg)
          } else {
            this.$message.error(res.msg)
          }
          this.$emit('ok', res)
          //查询
          await this.queryFktz()
        } catch (e) {
          this.uploadVarietiesDlgVisible = false
        }
      }
    },

Java

@PostMapping("/uploadFktzData")
	public RestResponse<String> uploadFktzData(@RequestParam("file") MultipartFile file){
		try {
			InputStream inp = file.getInputStream();
			Workbook wb = null;
			if(file.getOriginalFilename().endsWith(".xlsx")){
				wb = new XSSFWorkbook(inp);
			}else{
				return new RestResponse<String>(RestResponse.FAILURE_CODE,"不支持的文件" + file.getName(),null);
			}
			trsRiskStandingBookService.uploadFktzData(wb);
			return new RestResponse<String>(RestResponse.SUCCESS_CODE,"操作成功", "文件成功上传");
		}catch (Exception e){
			e.printStackTrace();
			return new RestResponse<String>(RestResponse.FAILURE_CODE,"程序异常:" + e.getMessage(),null);
		}
	}

    @Override
    @Transactional
    public void uploadFktzData(Workbook wb) {
        for (int a = 0; a < wb.getNumberOfSheets(); a++) {
            Sheet sheet = wb.getSheetAt(a);
            String sheetName = sheet.getSheetName();
            //查询所有项目
            List<TrsBasicsProjectModel> projectList = trsBasicsProjectMapper.selectList(null);

            //查询所有策略
            List<TrsBasicsStrategyModel> clList = trsBasicsStrategyMapper.selectList(null);

            //查询所有品种
            List<TrsBasicsVarietiesModel> pzList = trsBasicsVarietiesMapper.selectList(null);

            List<TrsRiskStandingBookModel> list = new ArrayList<>();
            //从第二行还是读数据
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row != null) {
                    //日期数据
                    String date = ExcelUtil.getValue(row.getCell(0));
                    if (StringUtils.isNotBlank(date)) {
                        TrsRiskStandingBookModel model = new TrsRiskStandingBookModel();
                        model.setDate(date);
                        //项目数据
                        String projectName = ExcelUtil.getValue(row.getCell(1));
                        if (StringUtils.isNotBlank(projectName)) {
                            for (TrsBasicsProjectModel projectModel : projectList) {
                                if (projectModel.getProjectName().equals(projectName)) {
                                    model.setProjectId(projectModel.getId());
                                    break;
                                }
                            }
                        } else {
                            throw new RuntimeException("第" + i + "行项目数据为空");
                        }

                        //策略类型
                        String strategyType = ExcelUtil.getValue(row.getCell(2));
                        //策略名称
                        String strategyName = ExcelUtil.getValue(row.getCell(3));
                        //设置策略id
                        if (StringUtils.isNotBlank(strategyType)) {
                            if ("单边".equals(strategyType)) {
                                for (TrsBasicsStrategyModel strategyModel : clList) {
                                    if (strategyType.equals(strategyModel.getStrategyType())) {
                                        //设置策略id
                                        model.setStrategyId(strategyModel.getId());
                                    }
                                }
                            } else {
                                if (StringUtils.isNotBlank(strategyName)) {
                                    for (TrsBasicsStrategyModel strategyModel : clList) {
                                        if (strategyType.equals(strategyModel.getStrategyType())
                                                && strategyName.equals(strategyModel.getStrategyName())) {
                                            //设置策略id
                                            model.setStrategyId(strategyModel.getId());
                                        }
                                    }
                                } else {
                                    throw new RuntimeException("第" + i + "行策略名称为空");
                                }
                            }
                        }else {
                            throw new RuntimeException("第" + i + "行策略类型为空");
                        }

                        //品种编码
                        String pzbm = ExcelUtil.getValue(row.getCell(4));
                        if(StringUtils.isBlank(pzbm)){
                            throw new RuntimeException("第" + i + "行品种编码为空");
                        }

                        //内外盘
                        String nwpName = ExcelUtil.getValue(row.getCell(5));
                        if(StringUtils.isBlank(nwpName)){
                            throw new RuntimeException("第" + i + "行内外盘为空");
                        }

                        //设置品种id
                        for (TrsBasicsVarietiesModel varietiesModel : pzList) {
                            if (varietiesModel.getVarietiesCode().equals(pzbm)
                                    && varietiesModel.getVarietiesType().equals(nwpName)) {
                                //设置品种id
                                model.setVarieteId(varietiesModel.getId());
                            }
                        }

                        //持仓量
                        String ccl = ExcelUtil.getValue(row.getCell(6));
                        if (StringUtils.isNotBlank(ccl)) {
                            model.setPosition(new BigDecimal(ccl));
                        }


                        //盈亏趋势
                        String clyk = ExcelUtil.getValue(row.getCell(7));
                        if (StringUtils.isNotBlank(clyk)) {
                            model.setClyk(new BigDecimal(clyk));
                        }

                        //盈亏趋势当日新增
                        String clykdrzl = ExcelUtil.getValue(row.getCell(8));
                        if (StringUtils.isNotBlank(clykdrzl)) {
                            model.setClykdrzl(new BigDecimal(clykdrzl));
                        }

                        //敞口
                        String ck = ExcelUtil.getValue(row.getCell(9));
                        if (StringUtils.isNotBlank(ck)) {
                            model.setExposure(new BigDecimal(ck));
                        }


                        //套保比例
                        String tbbl = ExcelUtil.getValue(row.getCell(10));
                        if (StringUtils.isNotBlank(tbbl)) {
                            model.setHedgingRate(new BigDecimal(tbbl));
                        }

                        //备注
                        String bz = ExcelUtil.getValue(row.getCell(11));
                        if (StringUtils.isNotBlank(bz)) {
                            model.setRemark(bz);
                        }
                        list.add(model);
                    }
                }
            }

            List<Long> idList = new ArrayList<>();
            List<TrsRiskStandingBookModel> oldModelList = trsRiskStandingBookMapper.selectList(null);
            for (TrsRiskStandingBookModel oldModel : oldModelList) {
                for (TrsRiskStandingBookModel bookModel : list) {
                    if (oldModel.getDate().equals(bookModel.getDate())
                            && oldModel.getProjectId().equals(bookModel.getProjectId())
                            && oldModel.getStrategyId().equals(bookModel.getStrategyId())) {
                        idList.add(oldModel.getId());
                    }
                }
            }
            if (!CollectionUtils.isEmpty(idList)) {
                trsRiskStandingBookMapper.deleteBatchIds(idList);
            }
            //一次存1000条
            int size = 1000;
            for (int i = 0; i <= list.size() / size; i++) {
                if (list.size() >= (i + 1) * size) {
                    trsRiskStandingBookMapper.insertAll(list.subList(i * size, (i + 1) * size));
                    System.out.println("已保存" + (i + 1) * size + "条数据");
                } else {
                    if (list.subList(i * size, list.size()) != null && list.subList(i * size, list.size()).size() > 0) {
                        trsRiskStandingBookMapper.insertAll(list.subList(i * size, list.size()));
                        System.out.println("已保存" + list.size() + "条数据");
                    }
                }
            }
        }
    }