Springboot+ Vue-element-admin实现excel文件数据解析并存入到数据库中

实现效果图：
①excel文件数据图

②数据库表数据图

1.Vue前台代码

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


importData() {
	  //input选取文件的点击事件
      this.$refs.excelFile.dispatchEvent(new MouseEvent('click'))
   },
 // 导入数据
uploadFile() {
    const file = this.$refs.excelFile.files
    var fileName = file[0].name.substring(file[0].name.lastIndexOf('.')).toLowerCase()
    //判断导入文件是否为正确格式
    if (fileName === '.xlsx' || fileName === '.xls') {
    	//创建一个空对象
        var formData = new FormData()
        //通过append方法添加数据
        formData.append('file', file[0])
        //js接口
        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模板文件')
      }
    },

2.js代码，uploadExcel方法，主要与后台传送信息

export function uploadExcel(data) {
  return request({
    url: '/management/info/uploadExcel',
    method: 'post',
    data: data
  })
}

3.后台代码
①controller层代码

    @PostMapping("/uploadExcel")
    public AjaxResult uploadExcel(MultipartFile file, boolean updateSupport) throws Exception{
    	String message = excelInfoService.uploadExcel(file);
        return AjaxResult.success(message);
    }

②service代码

	@Transactional
	@Override
	public String uploadExcel(MultipartFile file) throws Exception{
	  StringBuilder message = new StringBuilder();
	  String fileName = file.getOriginalFilename();
	  //导入文件格式是否正确
	  if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
	      throw new Exception("上传文件格式不正确");
	  }
	  List<ExcelInfo> list = new ArrayList<>();
	  try{
		  //创建workbook对象和sheet对象
		  Workbook workbook = WorkbookFactory.create(file.getInputStream());
	      Sheet sheet = null;
		  //取第一个sheet和sheet名
	      sheet = workbook.getSheetAt(0);
	      //判断文件sheet是否存在
	      if (sheet == null){
	          throw new IOException("文件sheet不存在");
	      }
	      //获取导入文件的行数
	      int rows = sheet.getPhysicalNumberOfRows();
	      //构建一个实体类
	      ExcelInfo info = null;
	      //循环每行的数据，进行插入数据库中
	      for (int j = 1; j < rows; j++) {
	    	  info = new AssetInfo();
	          //获得第 j 行数据
	    	  Row row = sheet.getRow(j);
	          info.setGoodsNo(this.getCellValue(row, 0).toString());
	          info.setGoodsName(this.getCellValue(row, 1).toString());  
	          //执行插入到数据库中sql
	          excelInfoMapper.insertExcelInfo(info);
	          list.add(info);
	      }
	      message.append(list);
	      workbook.close();
	      return message.toString();
	      }catch (Exception e){
	    	  message.append(e);
	    	  return message.toString();
	   }
	}

    /**
     * 获取单元格值
     * 
     * @param row 获取的行
     * @param column 获取单元格列号
     * @return 单元格值
     */
    public Object getCellValue(Row row, int column)
    {
        if (row == null)
        {
            return row;
        }
        Object val = "";
        try
        {
            Cell cell = row.getCell(column);
            if (StringUtils.isNotNull(cell))
            {
                if (cell.getCellTypeEnum() == CellType.NUMERIC || cell.getCellTypeEnum() == CellType.FORMULA)
                {
                    val = cell.getNumericCellValue();
                    if (HSSFDateUtil.isCellDateFormatted(cell))
                    {
                        val = DateUtil.getJavaDate((Double) val); // POI Excel 日期格式转换
                    }
                    else
                    {
                        if ((Double) val % 1 > 0)
                        {
                            val = new BigDecimal(val.toString());
                        } 
                        else
                        {
                            val = new DecimalFormat("0").format(val);
                        }
                    }
                }
                else if (cell.getCellTypeEnum() == CellType.STRING)
                {
                    val = cell.getStringCellValue();
                }
                else if (cell.getCellTypeEnum() == CellType.BOOLEAN)
                {
                    val = cell.getBooleanCellValue();
                }
                else if (cell.getCellTypeEnum() == CellType.ERROR)
                {
                    val = cell.getErrorCellValue();
                }

            }
        }
        catch (Exception e)
        {
            return val;
        }
        return val;
    }