将Excel中的数据导入到数据库中

如果想要将Excel中的数据导入到数据库中，肯定是以一定的格式要求才可以读取并导入，因此首先项目中需要 有一个Excel的导入模板，供用户填写数据。因此，该功能的实现需要两步，第一步需要提供下载模板的接口，第二步才是将上传的Excel文件内容导入到数据库中

第一步：提供下载模板的接口

  在项目的webapp目录下存放模板文件，如下图位置

controller层提供接口：

@RequestMapping(value = "/downloadTemplate")
	public void downloadTemplate(final HttpServletRequest request, final HttpServletResponse response) {
        //获取模板在项目中的路径
		String path = request.getServletContext().getRealPath("/") + "设备导入模板.xls";
		if (log.isDebugEnabled()) {
			log.debug("DeviceController");
			log.debug("downloadTemplate");
			log.debug("path=" + path);
		}
		FileInputStream in;
		BufferedOutputStream out;
		File file = new File(path);

		try {
			in = new FileInputStream(file);
			out = new BufferedOutputStream(response.getOutputStream());
			response.reset();
			response.setHeader("Content-Disposition",
				"attachment;Filename=" + URLEncoder.encode("设备导入模板.xls", "UTF-8"));

			response.addHeader("Content-Length", "" + file.length());

			response.setContentType("application/octet-stream;charset=UTF-8");
			byte[] buff = new byte[2048];
			int bytesRead;
			while (-1 != (bytesRead = in.read(buff, 0, buff.length))) {
				out.write(buff, 0, bytesRead);
			}
			// 关闭流
			in.close();
			out.close();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
			log.error(e);
		} catch (IOException e) {
			e.printStackTrace();
			log.error(e);
		}
	}

 第二步：将上传的Excel内的内容导入到数据库中

对于excel的导入导出网上有很多资源，可以参考以下两篇文章：

https://www.jianshu.com/p/dd1e4f28757b

https://blog.csdn.net/ethan_10/article/details/80335350

这边需下载jar包，并放在工程的WEB-INF——>lib目录下

下载地址：http://poi.apache.org/download.html

Excel模板形式：

 数据库字段（我用的MySQL）：

实体类： 

 

    public void setAssType(Byte assType) {
        this.assType = assType;
    }

    public Integer getTempId() {
        return tempId;
    }

    public void setTempId(Integer tempId) {
        this.tempId = tempId;
    }

    public Integer getUnitId() {
        return unitId;
    }

    public void setUnitId(Integer unitId) {
        this.unitId = unitId;
    }

    public String getDesc() {
        return desc;
    }

    public void setDesc(String desc) {
        this.desc = desc == null ? null : desc.trim();
    }

	public String getDevName() {
		return devName;
	}

	public void setDevName(String devName) {
		this.devName = devName;
	}


	public Integer getManufacture() {
		return manufacture;
	}

	public void setManufacture(Integer manufacture) {
		this.manufacture = manufacture;
	}

	public Date getUpdateTime() {
		return updateTime;
	}

//	public void setUpdateTime(Date updateTime) {
//		this.updateTime = updateTime;
//	}

	public Integer getQuantity() {
		return quantity;
	}

	public void setQuantity(Integer quantity) {
		this.quantity = quantity;
	}

	public BigDecimal getTaxRate() {
		return taxRate;
	}

	public void setTaxRate(BigDecimal taxRate) {
		this.taxRate = taxRate;
	}

	public BigDecimal getTax() {
		return tax;
	}

	public void setTax(BigDecimal tax) {
		this.tax = tax;
	}

	public Float getPower() {
		return power;
	}

	public void setPower(Float power) {
		this.power = power;
	}

	public byte[] getImage() {
		return image;
	}

	public void setImage(byte[] image) {
		this.image = image;
	}
	

}

controller层接口：

	@RequestMapping(value = "/importData")
	@ResponseBody
	public WebResultDto upload(@RequestParam("file") CommonsMultipartFile file) {
		WebResultDto result = new WebResultDto();
		try {
			HSSFWorkbook wb = new HSSFWorkbook(file.getInputStream());
			int count = deviceService.insertFromWorkbook(wb);
			result.success(count);
			if (log.isDebugEnabled())
				log.debug(count);
		} catch (IOException e) {
			e.printStackTrace();
			log.error(e.getMessage());
			result.setInfo(e.getMessage());
		}
		return result;
	}

其次便是service层的实现逻辑：

	@Override
	public int insertFromWorkbook(HSSFWorkbook wb) {
		// DataFormatter formatter = new DataFormatter();
		int count = 0;
		Sheet sheet1 = wb.getSheetAt(0);
		int i = 0;
		for (Row row : sheet1) {
			// 跳过第一行
			if (i++ == 0)
				continue;
			int j = 0;
			DeviceInfo entity = new DeviceInfo();
			for (Cell cell : row) {
				switch (j++) {
				// 设备编号
				case 0:
					if (cell.getCellTypeEnum().equals(CellType.STRING)
							|| cell.getCellTypeEnum().equals(CellType.FORMULA)) {
						entity.setDevCode(cell.getStringCellValue());
					}
					break;
				// 设备名称
				case 1:
					if (cell.getCellTypeEnum().equals(CellType.STRING)
							|| cell.getCellTypeEnum().equals(CellType.FORMULA)) {
						entity.setDevName(cell.getStringCellValue());
					}
					break;
				// 设备型号
				case 2:
					if (cell.getCellTypeEnum().equals(CellType.STRING)
							|| cell.getCellTypeEnum().equals(CellType.FORMULA)) {
						entity.setDevModel(cell.getStringCellValue());
					}
					break;
				// 设备类型
				case 3:
					if (cell.getCellTypeEnum().equals(CellType.STRING)
							|| cell.getCellTypeEnum().equals(CellType.FORMULA)) {
						String devTypeName = cell.getStringCellValue();
						if (ConstantUtils.getDeviceTypeMap().containsValue(devTypeName)) {
							for (Byte key : ConstantUtils.getDeviceTypeMap().keySet()) {
								if (ConstantUtils.getDeviceTypeName(key).equals(devTypeName)) {
									entity.setDevType(key);
									break;
								}
							}
						}
					}
					break;
				// 启用日期
				case 4:
					if (cell.getCellTypeEnum().equals(CellType.STRING)
							|| cell.getCellTypeEnum().equals(CellType.FORMULA)) {
						try {
							entity.setUseDate(format.parse(cell.getStringCellValue()));
						} catch (ParseException e) {
							// TODO Auto-generated catch block
							e.printStackTrace();
						}
					}
					break;
				// 设备状态
				case 5:
					if (cell.getCellTypeEnum().equals(CellType.STRING)
							|| cell.getCellTypeEnum().equals(CellType.FORMULA)) {
						String devStatusName = cell.getStringCellValue();
						if (ConstantUtils.getDeviceStatusMap().containsValue(devStatusName)) {
							for (Byte key : ConstantUtils.getDeviceStatusMap().keySet()) {
								if (ConstantUtils.getDeviceStatusName(key).equals(devStatusName)) {
									entity.setDevStatus(key);
									break;
								}
							}
						}
					}
					break;
				// 数量
				case 6:
					if (cell.getCellTypeEnum().equals(CellType.NUMERIC)
							|| cell.getCellTypeEnum().equals(CellType.FORMULA)) {
						entity.setQuantity(((Double) cell.getNumericCellValue()).intValue());
					}
					break;
				// 单价
				case 7:
					if (cell.getCellTypeEnum().equals(CellType.NUMERIC)
							|| cell.getCellTypeEnum().equals(CellType.FORMULA)) {
						entity.setDevPrice(BigDecimal.valueOf(cell.getNumericCellValue()));
					}
					break;
				// 税率
				case 8:
					if (cell.getCellTypeEnum().equals(CellType.NUMERIC)
							|| cell.getCellTypeEnum().equals(CellType.FORMULA)) {
						entity.setTaxRate(BigDecimal.valueOf(cell.getNumericCellValue()));
					}
					break;
				// 税
				case 9:
					if (cell.getCellTypeEnum().equals(CellType.NUMERIC)
							|| cell.getCellTypeEnum().equals(CellType.FORMULA)) {
						entity.setTax(BigDecimal.valueOf(cell.getNumericCellValue()));
					}
					break;
				// 重要级别
				case 10:
					if (cell.getCellTypeEnum().equals(CellType.STRING)
							|| cell.getCellTypeEnum().equals(CellType.FORMULA)) {
						String impLevelName = cell.getStringCellValue();
						if (ConstantUtils.getImportanceLevelMap().containsValue(impLevelName)) {
							for (Byte key : ConstantUtils.getImportanceLevelMap().keySet()) {
								if (ConstantUtils.getImportanceLevelMap().get(key).equals(impLevelName)) {
									entity.setImpLevel(key);
									break;
								}
							}
						}
					}
					break;
				case 11:
					// 资产类型
					if (cell.getCellTypeEnum().equals(CellType.STRING)
							|| cell.getCellTypeEnum().equals(CellType.FORMULA)) {
						String assTypeName = cell.getStringCellValue();
						if (ConstantUtils.getAssetsTypeMap().containsValue(assTypeName)) {
							for (Byte key : ConstantUtils.getAssetsTypeMap().keySet()) {
								if (ConstantUtils.getAssetsTypeMap().get(key).equals(assTypeName)) {
									entity.setAssType(key);
									break;
								}
							}
						}
					}
					break;
				// 功率
				case 12:
					if (cell.getCellTypeEnum().equals(CellType.NUMERIC)
							|| cell.getCellTypeEnum().equals(CellType.FORMULA)) {
						entity.setPower(((Double) cell.getNumericCellValue()).floatValue());
					}
					break;
				case 13:
					// 单位
					if (cell.getCellTypeEnum().equals(CellType.STRING)
							|| cell.getCellTypeEnum().equals(CellType.FORMULA)) {
						String unitName = cell.getStringCellValue();
						Integer unitId = this.unitInfoMapper.getUnitIdByName(unitName);
						if (unitId != null)
							entity.setUnitId(unitId);
					}
					break;
				}

			}
			try {
				count += this.deviceInfoDao.insert(entity);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return count;
	}

这样就可以将Excel中的内容一一转化为数据库中的数据了