springboot+element-ui+vue 导入excel并保存数据库

前端

<input ref="file" type="file" accept=".xlsx,.xls" style="display: none;" @change="uploadFile">
<el-button type="primary" icon="el-icon-download" @click="clickFile">导入数据</el-button>

clickFile() {
      this.$refs.file.dispatchEvent(new MouseEvent('click'))
    },
    // 导入数据
    uploadFile() {
      const file = this.$refs.file.files
      var extName = file[0].name.substring(file[0].name.lastIndexOf('.')).toLowerCase()
      var username = this.$store.getters.name
      if (extName === '.xlsx' || extName === '.xls') {
        var formData = new FormData()
        formData.append('file', file[0])
        formData.append('username', username)
        uploadExcel(formData).then((res) => {
          if (res.data.status !== 200) {
            this.$message.error('数据导入失败，请检擦数据格式!')
          } else {
            this.$message({
              type: 'success',
              message: '数据导入成功!'
            })
          }
        }).catch((err) => {
          this.$message.error('数据导入失败!ERR:' + err)
        })
      } else {
        this.$message.error('数据导入失败，请选择正确的xlsx模板文件')
      }
    },

后端

controller层

@PostMapping("/uploadExcel")
@ApiOperation("导入excel")
public String taskUploadExcel(@RequestParam(value = "file") MultipartFile file,@RequestParam(value = "username") String username) throws Exception{
   return taskServiceImpl.taskUploadExcel(file, username);
}

service层

@Transactional
@Override
public String taskUploadExcel(MultipartFile file, String username) throws Exception{
  SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
  String fileName = file.getOriginalFilename();
  if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
      throw new Exception("上传文件格式不正确");
  }
  List<TaskData> list = new ArrayList<>();
  try{
      InputStream is = file.getInputStream();
      HSSFWorkbook workbook = new HSSFWorkbook(new POIFSFileSystem(is));
      HSSFSheet sheet = workbook.getSheetAt(0);
      //获取多少行
      int rows = sheet.getPhysicalNumberOfRows();
      TaskData task = null;
      for (int j = 2; j < rows; j++) {
          task = new TaskData();
          //获得第 j 行
          HSSFRow row = sheet.getRow(j);
          System.out.println("row="+row+"++++++++++++++++++++++++++++++++++++++++++");

          task.setName(row.getCell(0).getStringCellValue());
          System.out.println("name="+ task.getName());

          task.setDescription(row.getCell(1).getStringCellValue());
          System.out.println("description="+ task.getDescription());

          task.setTypeid((int)row.getCell(2).getNumericCellValue());
          System.out.println("Typeid="+ task.getTypeid());

          task.setAttrid((int)row.getCell(3).getNumericCellValue());
          System.out.println("getAttrid="+ task.getAttrid());

          task.setSource(row.getCell(4).getStringCellValue());
          System.out.println("getSource="+ task.getSource());

          task.setPlantime(df.format(row.getCell(5).getDateCellValue()));
          System.out.println("getPlantime="+ task.getPlantime());

          task.setDeadlinetime(df.format(row.getCell(6).getDateCellValue()));
          System.out.println("getDeadlinetime="+ task.getDeadlinetime());

          list.add(task);
          System.out.println("总条数="+list.size());
          System.out.println("结束");
          System.out.println("退出循环");
		  taskDataMapper.uploadExcel(task);
          System.out.println("----------------");
      }
      		return gson.toJson(ok("成功",list));
      }catch (Exception e){
      		return gson.toJson(error("导入失败"+e));
   }
}