struts2中jxl导入excel

0.利用jxl 包

1.action中的方法如下

2.uploadFile是jsp页面中导入空间的id or name

3.如果是多上传的话，取名都相同，加个数组处理，这里用到是单个上传

public String importExcel2() throws Exception { FileOutputStream fos=null; FileInputStream fis=null; try { String fileName = "qyxx.xls"; String realpath = ServletActionContext.getServletContext().getRealPath("\\updownload"); fos=new FileOutputStream(new File(realpath+"\\"+fileName)); fis=new FileInputStream(uploadFile); byte[] buffer=new byte[1024]; int len=0; while((len =fis.read(buffer))>0){ fos.write(buffer, 0, len); } new QyxxReadExcel().getExcel(fileName); } catch (Exception e) { e.printStackTrace(); } finally{ if(fos!=null){fos.close();} if(fis!=null){fis.close();} } return find(); } 4.QyxxReadExcel导入数据库工具类，getExcel(fileName)为此处理方法

5.其中项目中针对了一个excel文件中的8张sheet进行处理 ，由于内容过太，就给出对企业表的处理

6.BaseQyjcxxService等是service的bean

7.其中加了些过滤条件，长度过长以截取方式、数字型的以判断是否字符，有制0，、判断企业是否存在

public String getExcel(String name) { String rtn=null; String path = this.getClass().getResource("/").toString(); path = path.substring(path.indexOf("/")); String realpath=path.substring(0,path.indexOf("WEB-INF"))+"updownload/"+name; ApplicationContext ac = new ClassPathXmlApplicationContext( "classpath:applicationContext.xml"); BaseQyjcxxService impl = (BaseQyjcxxService)ac.getBean("baseQyjcxxServiceImpl"); BaseAqglService impl1 = (BaseAqglService)ac.getBean("baseAqglServiceImpl"); BaseAqpxService impl2 = (BaseAqpxService)ac.getBean("baseAqpxServiceImpl"); BaseQyhyxxKService impl3 = (BaseQyhyxxKService)ac.getBean("baseQyhyxxKServiceImpl"); BaseQyhyxxWhyhService impl4 = (BaseQyhyxxWhyhService)ac.getBean("baseQyhyxxWhyhServiceImpl"); BaseSbssService impl5 = (BaseSbssService)ac.getBean("baseSbssServiceImpl"); BaseZdsgyhService impl6 = (BaseZdsgyhService)ac.getBean("baseZdsgyhServiceImpl"); BaseZdwxyService impl7 = (BaseZdwxyService)ac.getBean("baseZdwxyServiceImpl"); TDictionaryService td = (TDictionaryService)ac.getBean("TDictionaryServiceImpl"); BaseQyjcxx qyEntity=new BaseQyjcxx(); try { // 创建对Excel工作簿文件的引用 Workbook book = Workbook.getWorkbook(new FileInputStream(realpath)); //获取sheet数 jxl.Sheet[] sheets = book.getSheets();// 获得一个sheet for(int m=0;m<sheets.length;m++){ if(StringUtils.equals(sheets[m].getName(),"列表-基础")){//标明判断 // 行循环 for (int a=1;a<sheets[m].getRows();a++) { try { //Cell cell0 = sheets[m].getCell(0, a); String qybh = cell0.getContents(); Cell cell1 = sheets[m].getCell(1, a); String qymc = cell1.getContents(); Cell cell2 = sheets[m].getCell(2, a); String qydz = cell2.getContents(); Cell cell3 = sheets[m].getCell(3, a); String fddbr = cell3.getContents(); Cell cell4 = sheets[m].getCell(4, a); String fzrlxdh = cell4.getContents(); Cell cell5 = sheets[m].getCell(5, a); String yzbm = cell5.getContents(); Cell cell6 = sheets[m].getCell(6, a); String dwcz = cell6.getContents(); Cell cell7 = sheets[m].getCell(7, a); String aqfzr = cell7.getContents(); Cell cell8 = sheets[m].getCell(8, a); String aqfzrlxdh = cell8.getContents(); Cell cell9 = sheets[m].getCell(9, a); String xzqhbm = cell9.getContents(); // Cell cell10 = sheets[m].getCell(10, a); String xzqh =cell10.getContents(); Cell cell11 = sheets[m].getCell(11, a); Long zczj =Tools2.longByStr(cell11.getContents()); Cell cell12 = sheets[m].getCell(12, a); Long nxssr =Tools2.longByStr(cell12.getContents()); Cell cell13 = sheets[m].getCell(13, a); Long nlr =Tools2.longByStr(cell13.getContents()); Cell cell14 = sheets[m].getCell(14, a); Long zdmj =Tools2.longByStr(cell14.getContents()); Cell cell15 = sheets[m].getCell(15, a); Long zgrs =Tools2.longByStr(cell15.getContents()); Cell cell16 = sheets[m].getCell(16, a); Date qyclsj =DateOperateUtils.parseStringToDate(cell16.getContents().toString(), DateConstants.DEFAULT_DATE_FORMAT); Cell cell17 = sheets[m].getCell(17, a); String zzjgdm =cell17.getContents(); Cell cell18 = sheets[m].getCell(18, a); String gxdjdm =cell18.getContents(); Cell cell19 = sheets[m].getCell(19, a); String lyzzlb =cell19.getContents(); Cell cell20 = sheets[m].getCell(20, a); String sfgmys =cell20.getContents(); Cell cell21 = sheets[m].getCell(21, a); String zycpcl =cell21.getContents(); Cell cell22 = sheets[m].getCell(22, a); String zyscgy =cell22.getContents(); Cell cell23 = sheets[m].getCell(23, a); String sshy =cell23.getContents(); Cell cell24 = sheets[m].getCell(24, a); String lsgx =cell24.getContents(); Cell cell25 = sheets[m].getCell(25, a); String jjlx =cell25.getContents(); Cell cell26 = sheets[m].getCell(26, a); String aqlb =cell26.getContents(); Cell cell27 = sheets[m].getCell(27, a); String bzhdj =cell27.getContents(); Cell cell28 = sheets[m].getCell(28, a); String sffg =cell28.getContents(); //过滤条件 if(qymc.getBytes("GBK").length>200){qymc=qymc.substring(0, 200);} if(qydz.getBytes("GBK").length>200){qydz=qydz.substring(0, 200);} if(fddbr.getBytes("GBK").length>40){fddbr=fddbr.substring(0, 40);} if(fzrlxdh.getBytes("GBK").length>20){fzrlxdh=fzrlxdh.substring(0, 20);} if(yzbm.getBytes("GBK").length>6){yzbm=yzbm.substring(0, 6);} if(dwcz.getBytes("GBK").length>20){dwcz=dwcz.substring(0, 20);} if(aqfzr.getBytes("GBK").length>40){aqfzr=aqfzr.substring(0, 40);} if(aqfzrlxdh.getBytes("GBK").length>20){aqfzrlxdh=aqfzrlxdh.substring(0, 20);} if(xzqhbm.getBytes("GBK").length>40){xzqhbm=xzqhbm.substring(0, 40);} if(zzjgdm.getBytes("GBK").length>200){zzjgdm=zzjgdm.substring(0, 200);} if(lyzzlb.getBytes("GBK").length>200){lyzzlb=lyzzlb.substring(0, 200);} if(sfgmys.getBytes("GBK").length>20){sfgmys=sfgmys.substring(0, 20);} if(zycpcl.getBytes("GBK").length>1000){zycpcl=zycpcl.substring(0, 1000);} if(zyscgy.getBytes("GBK").length>1000){zyscgy=zyscgy.substring(0, 1000);} if(sshy.getBytes("GBK").length>20){sshy=sshy.substring(0, 20);} if(lsgx.getBytes("GBK").length>20){lsgx=lsgx.substring(0, 20);} if(jjlx.getBytes("GBK").length>20){jjlx=jjlx.substring(0, 20);} if(aqlb.getBytes("GBK").length>20){aqlb=aqlb.substring(0, 20);} if(bzhdj.getBytes("GBK").length>20){bzhdj=bzhdj.substring(0, 20);} if(zczj.toString().length()>20){zczj=Long.parseLong(zczj.toString().substring(0, 20));} if(nxssr.toString().length()>20){nxssr=Long.parseLong(nxssr.toString().substring(0, 20));} if(nlr.toString().length()>20){nlr=Long.parseLong(nlr.toString().substring(0, 20));} if(zdmj.toString().length()>20){zdmj=Long.parseLong(zdmj.toString().substring(0, 20));} if(zgrs.toString().length()>20){zgrs=Long.parseLong(zgrs.toString().substring(0, 20));} if(!zzjgdm.isEmpty() && impl.isExsitQyByzzjgdm(qymc)){//判断企业是否存在 if(sffg.indexOf("是")>-1){//判断是否覆盖 BaseQyjcxx jcxx=impl.getQyjcxxByJgdm(qymc); jcxx.setQymc(qymc);jcxx.setQydz(qydz); jcxx.setFzr(fddbr);jcxx.setFzrlxdh(fzrlxdh); jcxx.setYzbm(yzbm);jcxx.setDwcz(dwcz); jcxx.setAqfzr(aqfzr);jcxx.setAqfzrlxdh(aqfzrlxdh); if(StringUtils.isBlank(xzqhbm)){ jcxx.setXzqh("3301"); }else{jcxx.setXzqh(xzqhbm);} jcxx.setZczj(zczj); jcxx.setNxssr(nxssr);jcxx.setNlr(nlr); jcxx.setZdmj(zdmj);jcxx.setZgrs(zgrs); jcxx.setQyclsj(qyclsj);jcxx.setZzjgdm(zzjgdm); if(StringUtils.isBlank(gxdjdm)){ jcxx.setYyzzlbV("其他");jcxx.setYyzzlb("3"); }else{jcxx.setYyzzlbV(gxdjdm);jcxx.setYyzzlb(lyzzlb);} jcxx.setGsdjdm(td.getValueByName(lyzzlb).toString()); jcxx.setSfgmysV(sfgmys); jcxx.setSfgmys(td.getValueByName(sfgmys).toString()); jcxx.setCpcl(zycpcl);jcxx.setScgy(zyscgy); if(StringUtils.isBlank(sshy)){ jcxx.setSshyV("其他");jcxx.setSshy("13"); }else{ jcxx.setSshyV(sshy);jcxx.setSshy(td.getValueByName(sshy).toString()); } if(StringUtils.isBlank(lsgx)){ jcxx.setLsgxV("其他");jcxx.setLsgx("90"); }else{ jcxx.setLsgxV(lsgx);jcxx.setLsgx(td.getValueByName(lsgx).toString()); } if(StringUtils.isBlank(jjlx)){ jcxx.setJjlxV("其他");jcxx.setJjlx("9"); }else{ jcxx.setJjlxV(jjlx);jcxx.setJjlx(td.getValueByName(jjlx).toString()); } if(StringUtils.isBlank(aqlb)){ jcxx.setAqlbV("未定级");jcxx.setAqlb("5"); }else{ jcxx.setAqlbV(aqlb);jcxx.setAqlb(td.getValueByName(aqlb).toString()); } if(StringUtils.isBlank(bzhdj)){ jcxx.setBzhdjV("未定级");jcxx.setBzhdj("5"); }else{ jcxx.setBzhdjV(bzhdj);jcxx.setBzhdj(td.getValueByName(bzhdj).toString()); } jcxx.setSfsc("否");jcxx.setCjsj(new Date()); qyEntity=impl.updateBaseQyjcxx(jcxx); }else{ qyEntity=impl.getQyjcxxByJgdm(qymc); } }else{ BaseQyjcxx qyxx= new BaseQyjcxx(); //qyxx.setQybm(qybh); qyxx.setQymc(qymc);qyxx.setQydz(qydz); qyxx.setFzr(fddbr);qyxx.setFzrlxdh(fzrlxdh); qyxx.setYzbm(yzbm);qyxx.setDwcz(dwcz); qyxx.setAqfzr(aqfzr);qyxx.setAqfzrlxdh(aqfzrlxdh); if(StringUtils.isBlank(xzqhbm)){ qyxx.setXzqh("3301"); }else{qyxx.setXzqh(xzqhbm);} qyxx.setZczj(zczj); qyxx.setNxssr(nxssr);qyxx.setNlr(nlr); qyxx.setZdmj(zdmj);qyxx.setZgrs(zgrs); qyxx.setQyclsj(qyclsj);qyxx.setZzjgdm(zzjgdm); if(StringUtils.isBlank(gxdjdm)){ qyxx.setYyzzlbV("其他");qyxx.setYyzzlb("3"); }else{ qyxx.setYyzzlbV(gxdjdm);qyxx.setYyzzlb(lyzzlb); } qyxx.setGsdjdm(td.getValueByName(lyzzlb).toString()); qyxx.setSfgmysV(sfgmys); qyxx.setSfgmys(td.getValueByName(sfgmys).toString()); qyxx.setCpcl(zycpcl);qyxx.setScgy(zyscgy); if(StringUtils.isBlank(sshy)){ qyxx.setSshyV("其他");qyxx.setSshy("13"); }else{ qyxx.setSshyV(sshy);qyxx.setSshy(td.getValueByName(sshy).toString()); } if(StringUtils.isBlank(lsgx)){ qyxx.setLsgxV("其他");qyxx.setLsgx("90"); }else{ qyxx.setLsgxV(lsgx);qyxx.setLsgx(td.getValueByName(lsgx).toString()); } if(StringUtils.isBlank(jjlx)){ qyxx.setJjlxV("其他");qyxx.setJjlx("9"); }else{ qyxx.setJjlxV(jjlx);qyxx.setJjlx(td.getValueByName(jjlx).toString()); } if(StringUtils.isBlank(aqlb)){ qyxx.setAqlbV("未定级");qyxx.setAqlb("5"); }else{ qyxx.setAqlbV(aqlb);qyxx.setAqlb(td.getValueByName(aqlb).toString()); } if(StringUtils.isBlank(bzhdj)){ qyxx.setBzhdjV("未定级");qyxx.setBzhdj("5"); }else{ qyxx.setBzhdjV(bzhdj);qyxx.setBzhdj(td.getValueByName(bzhdj).toString()); } qyxx.setSfsc("否");qyxx.setCjsj(new Date()); qyEntity = impl.insertBaseQyjcxx(qyxx); } } catch (Exception e) { e.printStackTrace(); } } } } } catch (Exception e) { System.out.println("ReadExcelError" + e); rtn = "导入出现异常"; } return rtn; }以此记录下