导入excle数据将excle数据插入到数据库
实现功能是，用户可以直接导入对应数据，或者用户下载模板，填写数据，导入模板数据。easyui实现
前台页面
{ text : '日清导入', iconCls : 'icon-print', handler : function(){ $('#import').dialog('open'); } }
<div id = "import" title="员工信息导入" modal=true draggable=true align="center" class="easyui-dialog" closed=true style="width: 400px"> <form id="importForm" method="post" enctype="multipart/form-data"> <table id="importTable" align="center"> <tr> <td align="center" colspan="2"> 上传：<input id = "myFile" name="myFile" type="file"></input> </td> </tr> <tr> <td align ="center" colspan="2"><a id="download" href="<%=basePath %>import_template/template-dayrecruit.xls" class="easyui-linkbutton" >模板下载</a> <a id="upload" class="easyui-linkbutton">上传</a> <a id="importCancel" class="easyui-linkbutton">取消</a> </td> </tr> </table> </form> </div>
以上代码加颜色的是特别注意的点。上传文件一定加这些声明，否则不可实现。点击模板下载可以直接在我们根路径下找到模板，并下载。
具体的后台代码是：
@RequestMapping(value = "/imp", method = RequestMethod.POST, produces = "text/html;charset=UTF-8") public @ResponseBody String imp(@RequestParam MultipartFile myFile,//这里会直接以流文件形式接收上传数据 HttpServletRequest request, HttpSession session) throws Exception { Account account = this.getStaticAccount(); if (myFile == null || myFile.getSize() == 0) { return "未选择任何文件，请选择文件后上传！"; } String fileType = myFile.getOriginalFilename().substring( myFile.getOriginalFilename().lastIndexOf(".")); if (!fileType.equals(".xls") && !fileType.equals(".xlsx")) { return "文件格式错误，请上传.xls或.xlsx格式文件！"; } //String path = CommonsMethod.getProjectPath() ; String path=request.getSession() .getServletContext().getRealPath("/") + "/importReserveExcel/"; String fileattr = CommonsMethod.getNowCorrect2Millisecond() + myFile.getOriginalFilename().substring( myFile.getOriginalFilename().lastIndexOf(".")); final File targetFile = new File(path, fileattr); if (!targetFile.exists()) { targetFile.mkdirs(); } try { myFile.transferTo(targetFile); } catch (IllegalStateException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } ArrayList<Object> dataList = new ArrayList<Object>(); try { dataList = dayRecruitService.readExcel( targetFile.getAbsolutePath(), 4); } catch (Exception e) { return e.getMessage(); } try { String failImport = dayRecruitService.importDayrecruit( dataList, account); } catch (RuntimeException e) { return e.getMessage(); } catch (Exception e) { return "请仔细核对上传格式（参考备注）"; } return "上传成功"; } private static final String[] headers = new String[] {"日清日期","备注","面试人数","入职人数","招聘企业"};
readExcel代码：把数据先读到一个dayrecruit模板中 原因是当前读的数据不是直接向数据库中存的数据。
public ArrayList<Object> readExcel(String absolutePath, int i)throws Exception { // TODO Auto-generated method stub ExcelOperations oper = new ExcelOperations(); ArrayList<Object> list = oper.readExcel(absolutePath, Template_DayRecruit.class, columnHeaders,i ); return list; } public static final String[] columnHeaders = { "date", "remarks", "viewerNu", "entryNu", "customerName"};
正式读取excle数据代码为
public ArrayList<Object> readExcel(String filePath, Class entity, String[] columnHeads, int noReadSize) throws Exception { ArrayList<Method> list = new ArrayList<Method>(); ArrayList<Object> objs = new ArrayList<Object>(); try { for (int i = 0; i < columnHeads.length; i++) { char f = columnHeads[i].charAt(0); if (!Character.isUpperCase(f)) { columnHeads[i] = String.valueOf(Character.toUpperCase(f)) + columnHeads[i].substring(1); } Method methodGet = entity.getMethod("get" + columnHeads[i]); Method methodSet = entity.getMethod("set" + columnHeads[i], methodGet.getReturnType()); list.add(methodSet); } InputStream inputstream = new FileInputStream(filePath); Workbook wb = WorkbookFactory.create(inputstream); Sheet sheet1 = wb.getSheetAt(0); for (int i = 1; i <= sheet1.getLastRowNum() - noReadSize; i++) { try { Object obj = entity.newInstance(); Cell cell = null; for (int k = 0; k < list.size(); k ++) { cell = sheet1.getRow(i).getCell(k); if(cell.getCellType() == 0){ //判断是否为日期 if(HSSFDateUtil.isCellDateFormatted(cell)){ SimpleDateFormat sdf = null; if (cell.getCellStyle().getDataFormat() == HSSFDataFormat .getBuiltinFormat("h:mm")) { sdf = new SimpleDateFormat("HH:mm"); } else {// 日期 sdf = new SimpleDateFormat("yyyy-MM-dd"); } Date date = cell.getDateCellValue(); cell.setCellValue(sdf.format(date)); }else{ double value = cell.getNumericCellValue(); CellStyle style = cell.getCellStyle(); DecimalFormat format = new DecimalFormat(); String temp = style.getDataFormatString(); // 单元格设置成常规 if (temp.equals("General")) { format.applyPattern("#"); } cell.setCellValue(format.format(value)); } } list.get(k).invoke(obj, cell.toString()); } objs.add(obj); } catch (Exception e) { e.printStackTrace(); throw new Exception("Excel 文件第" + i + "行格式错误"); } } } catch (Exception e) { e.printStackTrace(); throw e; } return objs; }
然后调用import方法，里面会验证需要验证的数据。如果有一条没有通过验证那么所有的数据将会回滚操作。这主要牵扯到了事务管理。
public String importDayrecruit(List<Object> dataList, Account user) throws Exception { String failImport = ""; if (dataList != null && dataList.size() > 0) { //根据公司user找到对应公司 根据公司找到对应的 招聘企业 根据招聘企业 Organization org = dayRecruitDAO.findOrganizationById(user.getOrganization().getId()); List<EnterpriseCustomer> customers = dayRecruitDAO.findAllEnterpriseCustomerByOrg(user.getOrganization().getId()); for (int i = 0; i < dataList.size(); i++) { DayRecruit dayRec = new DayRecruit(); Template_DayRecruit t_dayRecruit = new Template_DayRecruit(); t_dayRecruit = (Template_DayRecruit) dataList.get(i); dayRec.setWriter(user.getLoginName()); dayRec.setDatatime(new Date()); dayRec.setRemarks(t_dayRecruit.getRemarks().trim()); //dayRec. //验证招聘日期 if(isValidDate(t_dayRecruit.getDate().trim()) && StringUtils.isNotBlank(t_dayRecruit.getDate().trim())){ dayRec.setDate(t_dayRecruit.getDate().toString()); }else throw new RuntimeException("第" + (i + 2) + "行填写招聘日清日期有误，请重新确定"); //验证填写人 /* if(StringUtils.isNotBlank(dayRec.getWriter()) && dayRec.getWriter().equals(user.getUserName())){ dayRec.setWriter(dayRec.getWriter().trim()); } else{ throw new RuntimeException("第" + (i + 2) + "行填写输入人有误，请填写您的登录账号，请重新确定"); }*/ //验证面试人数 if(isNumeric(t_dayRecruit.getViewerNu().trim()) && StringUtils.isNotBlank(t_dayRecruit.getViewerNu().trim())){ dayRec.setViewerNu(t_dayRecruit.getViewerNu().trim()); } else throw new RuntimeException("第" + (i + 2) + "行填写面试人数有误，请重新确定"); //验证入职人数 if(isNumeric(t_dayRecruit.getEntryNu().trim()) && StringUtils.isNotBlank(t_dayRecruit.getEntryNu().trim())){ int re = Double.valueOf(t_dayRecruit.getEntryNu().trim()).compareTo(Double.valueOf(t_dayRecruit.getViewerNu().trim())); if(re <1){ dayRec.setEntryNu(t_dayRecruit.getEntryNu().trim()); } else { throw new RuntimeException("第" + (i + 2) + "行入职人数大于面试人数，请重新确定"); } } else throw new RuntimeException("第" + (i + 2) + "行填写入职人数有误，请重新确定"); //验证招聘企业 从模板中获取招聘企业的名字 查询出 此公司所有的招聘企业 对应的话 取 招聘企业这个对象赋值给dayR // 验证所属公司 if(StringUtils.isNoneBlank(t_dayRecruit.getCustomerName().trim())){ boolean isOK = false; if(customers != null && customers.size()>0){ for(int j =0;j<customers.size();j++){ if(customers.get(j).getEnterpriseName().equals(t_dayRecruit.getCustomerName().trim())){ dayRec.setCustomer(customers.get(j)); isOK = true; break;//跳出当前循环 } } } if (!isOK) { throw new RuntimeException("第" + (i + 2) + "行招聘企业输入有误，请重新确定"); } } dayRecruitDAO.add(dayRec); } } else { throw new RuntimeException("导入数据为空"); } return failImport; }
这样数据就插入到对应数据库。完成导入操作。
