springboot+vue利用POI技术实现对Excel的导入功能并插入到数据库

第一次接触Excel导出功能，写的有点费劲，把代码贴出来希望能帮到大家

后端：springboot+springcloud+mybatis
前端：vue+iview
数据库：oracle

先上vue

<Upload
        ref="upload"
        action="此处填写你访问的后台接口地址"
        :on-format-error="handleFormatError"
        :on-success="handleSuccess"
        :on-error="handleError"
        enctype="multipart/form-data"
        :format ="['xlsx','xls']">
        <Button type="primary" icon="ios-cloud-upload-outline">批量导入</Button>
</Upload>

uploadLoading: false,
file: null,

    //导入
  handleFormatError(file){
    this.$Notice.warning({
      title: '文件格式不正确',
      desc: '文件 ' + file.name + ' 格式不正确，请上传.xls,.xlsx文件。'
    })
  },
  handleSuccess(res,file){
      this.$Message.success("数据导入成功！")
      //提示完渲染页面
      this.getTreeData(1)
  },
  handleError(error,file){
    this.$Message.error("数据导入失败！")
  },

接下来我们写后台
导入jar包（不知道是否都能用到，引就vans了，版本可能对代码有影响）

<!--POI-->
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>3.9</version>
    </dependency>
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi</artifactId>
      <version>3.9</version>
    </dependency>
    <dependency>
      <groupId>org.dom4j</groupId>
      <artifactId>dom4j</artifactId>
      <version>2.1.1</version>
    </dependency>
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml-schemas</artifactId>
      <version>3.9</version>
    </dependency>
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-scratchpad</artifactId>
      <version>3.9</version>
    </dependency>
    <dependency>
      <groupId>commons-fileupload</groupId>
      <artifactId>commons-fileupload</artifactId>
      <version>1.3.1</version>
    </dependency>
    <dependency>
      <groupId>commons-io</groupId>
      <artifactId>commons-io</artifactId>
      <version>2.4</version>
    </dependency>
    <dependency>
      <groupId>org.apache.xmlbeans</groupId>
      <artifactId>xmlbeans</artifactId>
      <version>2.3.0</version>
    </dependency>
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-examples</artifactId>
      <version>3.9</version>
    </dependency>
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-excelant</artifactId>
      <version>3.9</version>
    </dependency>

controller

/**
	 * 导入Excel
	 * @param file
	 * @throws Exception
	 */
	@ApiOperation(value="Excel导入", notes="上传Excel表格插入数据库")
	@RequestMapping(value = "/upload",method = RequestMethod.POST)
	public void upload(@RequestParam("file") MultipartFile file) throws Exception {
		stationService.batchImport(file);
	}

service

/**
	 * 导入Excel
	 * @param file
	 * @throws Exception
	 */
	void batchImport(MultipartFile file) throws Exception;

serviceimpl

/**
	 * 导入Excel
	 * @param file
	 * @throws Exception
	 */
	@Transactional(readOnly = false,rollbackFor = Exception.class)
	@Override
	public void batchImport(MultipartFile file)throws Exception  {
		List<Station> ss = new ArrayList<>();
		InputStream is = file.getInputStream();
		Workbook workbook = WorkbookFactory.create(is);
		//确定版本
		boolean isExcel2003 = file.getOriginalFilename().endsWith("xls")?true:false;
		List<Map<String, Object>> returnMap;
		if(isExcel2003) {
			//有多少个sheet
			int sheets = workbook.getNumberOfSheets();
			for (int i = 0; i < sheets; i++) {
				HSSFSheet sheet = (HSSFSheet) workbook.getSheetAt(i);
				//获取多少行
				int rows = sheet.getPhysicalNumberOfRows();
				Station s = null;
				//遍历每一行，注意：第 0 行为标题
				for (int j = 1; j < rows; j++) {
					s = new Station();
					//获得第 j 行
					HSSFRow row = sheet.getRow(j);
					//转换格式 row.getCell(0).setCellType(Cell.CELL_TYPE_STRING)
					s.setStationName(row.getCell(0).getStringCellValue());
					s.setContacts(row.getCell(1).getStringCellValue());
					s.setAddress(row.getCell(2).getStringCellValue());
					s.setIntoUseTime(row.getCell(3).getStringCellValue());
					ss.add(s);
					if(StringUtils.isBlank(s.getStationId())){
						s.setStationId(UUIDGenerate.getUUID());
					}
					Map<String, Object>  returnMap1=new HashMap<>();
					returnMap1.put("station",s);

					stationDao.addStationExcel(s);

				}

			}
		}else{
			//有多少个sheet
			int sheets = workbook.getNumberOfSheets();
			for (int i = 0; i < sheets; i++) {
				XSSFSheet sheet = (XSSFSheet) workbook.getSheetAt(i);
				//获取多少行
				int rows = sheet.getPhysicalNumberOfRows();
				Station s = null;
				//遍历每一行，注意：第 0 行为标题
				for (int j = 1; j < rows; j++) {
					s = new Station();
					//获得第 j 行
					XSSFRow row = sheet.getRow(j);
					//设置参数与Excel列对应
					s.setStationName(row.getCell(0).getStringCellValue());
					s.setContacts(row.getCell(1).getStringCellValue());
					s.setAddress(row.getCell(2).getStringCellValue());
					s.setIntoUseTime(row.getCell(3).getStringCellValue());
					ss.add(s);
					//设置id为随机数
					if(StringUtils.isBlank(s.getStationId())){
						s.setStationId(UUIDGenerate.getUUID());
					}
					stationDao.addStationExcel(s);
				}
			}
		}
	}

dao

/**
	 * Excel导入
	 * @param s
	 */
	void addStationExcel(Station s);

**.xml

<!-- excel导入 -->
	<insert id="addStationExcel" parameterType="实体类" >
		insert into 表名
		<trim prefix="(" suffix=")" suffixOverrides=",">
			<if test="stationId != null and stationId != ''">
				station_id,
			</if>
			<if test="stationName != null and stationName != ''">
				station_name,
			</if>
			<if test="contacts != null and contacts != ''">
				contacts,
			</if>
			<if test="intoUseTime != null and intoUseTime != ''">
				into_use_time,
			</if>
			<if test="address != null and address != ''">
				address,
			</if>		
		</trim>
		<trim prefix="values (" suffix=")" suffixOverrides=",">
			<if test="stationId != null and stationId != ''">
				#{stationId,jdbcType=VARCHAR},
			</if>
			<if test="stationName != null and stationName != ''">
				#{stationName,jdbcType=VARCHAR},
			</if>
			<if test="contacts != null and contacts != ''">
				#{contacts,jdbcType=VARCHAR},
			</if>
			<if test="intoUseTime != null and intoUseTime != ''">
				to_date(#{intoUseTime,jdbcType=DATE},'yyyy-MM-dd'),
			</if>
			<if test="address != null and address != ''">
				#{address,jdbcType=VARCHAR},
			</if>
		</trim>
	</insert>