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 blocke.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:// 读取StringcellStr = 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 blocke.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@Transactionalpublic 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的操作