业务实现Excel 数据导入数据库

注：应用技术:spring3.0注解(代码包括4个方法：importData、getXlsData、insert、insertDefault；依次调用)

 

@RequestMapping(value="/importData",method=RequestMethod.POST)//@ResponseBody ModelMap public void importData(@RequestParam("file") MultipartFile file,HttpServletResponse response,ModelMap modelMap) throws IOException {//,HttpServletResponse response,int num=0;try{if(!file.isEmpty()){// 获得inputstreamInputStream inputstream = file.getInputStream() ;num = getXlsData(inputstream);inputstream.close();}String msg = "导入失败";if(num>0){msg = "导入成功";}response.setContentType("text/html;charset=utf-8");response.getWriter().write("{success:"+new Boolean(num>0?true:false)+",msg:'"+msg+"'}");}catch(Exception e){modelMap.put("success", false);modelMap.put("msg", e.getMessage());e.printStackTrace();response.getWriter().write("{success:false,msg:'导入失败'}");}}

 

private int getXlsData(InputStream inputstream){int num= 0;try{List<CmsIcAccountExchgReturn> list = new ArrayList<CmsIcAccountExchgReturn>();HSSFWorkbook hssfworkbook = new HSSFWorkbook(inputstream);// 第一个工作表HSSFSheet hssfsheet = hssfworkbook.getSheetAt(0);// 创建行对象HSSFRow tempRow = null;// 获得总行数int rowCount = hssfsheet.getLastRowNum();if(rowCount > 0) {for (int i = 1; i < rowCount + 1; i++) {// 构造一个空对象CmsIcAccountExchgReturn mode = new CmsIcAccountExchgReturn();tempRow = hssfsheet.getRow(i);if (tempRow == null || tempRow.getCell(0) == null) {continue;}String batchNo = tempRow.getCell(0).toString();//if(batchNo.indexOf(".")>-1){//batchNo = batchNo.substring(0,batchNo.indexOf("."));//}if (batchNo.length() > 22) {list.clear();break;}String fee = tempRow.getCell(7).toString();String seqid = tempRow.getCell(10).toString();//if(seqid.indexOf(".")>-1){//seqid = seqid.substring(0,seqid.indexOf("."));//}if (seqid.length() > 20) {list.clear();break;}String rtnRslt = tempRow.getCell(11).toString();//if(rtnRslt.indexOf(".")>-1){//rtnRslt = rtnRslt.substring(0,rtnRslt.indexOf("."));//}if (rtnRslt.length() > 6) {list.clear();break;}mode.setBatchNo(batchNo);mode.setFee(fee);mode.setSeqId(seqid);mode.setRtnRslt(rtnRslt);list.add(mode);}if (list.size() > 0){num = this.cmsIcAccountExchgReturnService.insert(list);}}}catch(Exception e){e.printStackTrace();}return num;}

 

@Transactionalpublic int insert(List<CmsIcAccountExchgReturn> list){int number = 0;try{for(int i=0;i<list.size();i++){CmsIcAccountExchgReturn mode = list.get(i);number += insertDefault(mode);}}catch(Exception e){e.printStackTrace();}return number;}

 

public int insertDefault(CmsIcAccountExchgReturn mode){String sql = "insert into CMS_IC_ACCOUNT_EXCHG_RETURN(SEQID,BATCHNO,FEE,RTNRSLT) values(?,?,?,?)";Object[] params = new Object[]{mode.getSeqId(),mode.getBatchNo(),mode.getFee(),mode.getRtnRslt()};return this.jdbcTemplate.update(sql, params);}