excel导入oracle数据库

一、jar包准备

   导入excel需要使用apache的POI提供的jar包：

二、JSP页面文件选择导入

  

<form action="<%=basePath%>deviceman/processDeviceBatchImport.shtml" method="post" enctype="multipart/form-data">
			<tr>
				<td>
					<label>
						<!--  <input type="text" style="width:350px" readonly="readonly" name="file_name" id="file_name"/>
	        			<input type="file" name="btn_file" style="display:none" onchange="file_name.value=this.value"/>
	        			<input type="button" value="浏 览" onclick="btn_file.click();" name="get_file"/>
	        			<input type="submit" value="提 交" name="sub"/>-->
	        			<input id="filePath" type="file" name="file" /> <input type="submit" value="确定" />
					</label>
				</td>				
			</tr>
			
			</form>

三、后台处理上传文件请求

 (1)接收页面excel上传请求

/**
	 * @Title: processDeviceBatchImport
	 * @Description: 处理上传
	 * @param file
	 * @return ModelAndView 返回类型
	 * @throws
	 */
	@RequestMapping(value = "processDeviceBatchImport.shtml", method = RequestMethod.POST)
	public ModelAndView processDeviceBatchImport(@RequestParam(value = "file", required = false) MultipartFile file) {
		logger.debug("enter DeviceManageAction.processDeviceBatchImport(@RequestParam MultipartFile file)");
		
		ModelAndView model = new ModelAndView("/thingsConnMan/deviceMan/deviceBatchImportList");
		
		try {
			
			List<CdmDeviceListBean> deviceInfoList = deviceManageService.transExcelToObj(file.getInputStream());
			int count = deviceManageService.saveImportDeviceInfo(deviceInfoList);
			
			model.addObject("deviceInfoList", deviceList);
			
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		logger.debug("exit DeviceManageAction.processDeviceBatchImport(@RequestParam MultipartFile file)");
		return model;
	}

(2)将excel数据转换为数据库表对应的实体对象

/**
	 * @Title: transExcelToObj
	 * @Description: excel数据转换为实体对象
	 * @param in
	 * @return List<CdmDeviceListBean> 返回类型
	 * @throws
	 */
	public List<CdmDeviceListBean> transExcelToObj(InputStream in) {
		logger.debug("enter DeviceManageService.transExcelToObj(InputStream in)");
		List<CdmDeviceListBean> academyList = new ArrayList<CdmDeviceListBean>();
		try {
			String cellStr = null;
			Workbook wb = new XSSFWorkbook(in);
			Sheet sheet = wb.getSheetAt(0);
			int firstRow = sheet.getFirstRowNum() + 1;
			int lastRow = sheet.getLastRowNum();
			for (int i = firstRow; i <= lastRow; i++) {
				CdmDeviceListBean bean = new CdmDeviceListBean();
				Row row = sheet.getRow(i);
				if (row == null) {
					continue;
				}
				for (int j = 0; j < row.getLastCellNum(); j++) {
					Cell cell = row.getCell(j);
					cellStr = ConvertCellStr(cell, cellStr);
					bean = addingCdmDevice(j, bean, cellStr);
					bean.setIsvalidate("0");
					logger.debug("-----" + bean.getBatchName() + "--------------");
				}
				academyList.add(bean);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (in != null) {
				try {
					in.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			} else {
				logger.info("没有数据流");
			}
		}
		logger.debug("exit DeviceManageService.transExcelToObj(InputStream in)");
		return academyList;
	}

 注意点：

        1.将excel文件流转换为workbook对象：

         Workbook wb = new XSSFWorkbook(in);

         2.获取excel数据行，以便进行循环处理：

         int firstRow = sheet.getFirstRowNum() + 1;
         int lastRow = sheet.getLastRowNum();

         3.对excel中cell的值进行转化，其中涉及到excel科学计数法的转换以及日期的转换：

        

/**
	 * @Title: ConvertCellStr
	 * @Description: excel数据转换
	 * @param cell
	 * @param cellStr
	 * @return String 返回类型
	 * @throws
	 */
	private String ConvertCellStr(Cell cell, String cellStr) {
		switch (cell.getCellType()) {
			case Cell.CELL_TYPE_STRING:
				// 读取String
				cellStr = cell.getStringCellValue().toString();
				break;
			case Cell.CELL_TYPE_BOOLEAN:
				// 得到Boolean对象的方法
				cellStr = String.valueOf(cell.getBooleanCellValue());
				break;
			case Cell.CELL_TYPE_NUMERIC:
				// 先看是否是日期格式
				if (DateUtil.isCellDateFormatted(cell)) {
					// 读取日期格式
					cellStr = cell.getDateCellValue().toString();
				} else {
					// 读取数字
					DecimalFormat df = new DecimalFormat("0");
					cellStr = String.valueOf(df.format(cell.getNumericCellValue()));
				}
				break;
			case Cell.CELL_TYPE_FORMULA:
				// 读取公式
				cellStr = cell.getCellFormula().toString();
				break;
		}
		return cellStr;
	}

             4.设置实体属性值，通过switch语句，很方便的将各个cell中的值放入实体对应的字段中：

/**
	 * @Title: addingCdmDevice
	 * @Description: 设置实体属性值
	 * @param j
	 * @param bean
	 * @param cellStr
	 * @return CdmDeviceListBean 返回类型
	 * @throws
	 */
	private CdmDeviceListBean addingCdmDevice(int j, CdmDeviceListBean bean, String cellStr) {
		switch (j) {
			case 0:
				bean.setDeviceSn(cellStr);
				break;
			case 1:
				bean.setDeviceModel(cellStr);
				break;
			case 2:
				bean.setDeviceName(cellStr);
				break;
			case 3:
				bean.setBatchName(cellStr);
				break;
			case 4:
				bean.setSimNo(cellStr);
				break;
			case 5:
				SimpleDateFormat sdf = new SimpleDateFormat("EEE MMM dd HH:mm:ss zzz yyyy", Locale.US);
				// java.util.Date对象
				try {
					Date date = (Date) sdf.parse(cellStr);
					Date d = DateFormat.formatDate(date);
					bean.setProductTime(d);
				} catch (ParseException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				break;
		}
		return bean;
	}

 (3)将转化后的实体对象列表直接入库：

  

/* (非 Javadoc) 
	* <p>Title: batchSaveDeviceInfo</p> 
	* <p>Description: 批量保存设备信息 </p> 
	* @param cdmDeviceList
	* @return 
	* @see com.spring3.DAO.things.DeviceManageDao#batchSaveDeviceInfo(java.util.List) 
	*/
	@Override
	@Transactional
	public int batchSaveDeviceInfo(final List<CdmDeviceListBean> cdmDeviceList) {
		int affectedRow = (Integer) getHibernateTemplate().execute(new HibernateCallback<Object>() {

			public Object doInHibernate(Session session) throws HibernateException, SQLException {
				for (int i = 0; i < cdmDeviceList.size(); ++i) {
					session.save(cdmDeviceList.get(i));
					if (i % BATCH_MAX_ROW == 0) {
						session.flush();
						session.clear();
					}
				}
				session.flush();
				session.clear();
				return cdmDeviceList.size();
			}
		});
		return affectedRow;
	}

 如上变完成了excel数据导入oracle的操作