文章标题

                                                      springmvc框架项目将execl格式的数据导入数据库

1. 导入步骤
   首先将execl文件上传至服务器端，然后通过Jxl解析execl数据并通过java类组织数据，最后将数据存入数据库。
   2.具体代码实现
   页面显示代码：
   将文件标签伪装成button 当选择好上传文件后将文件提交至服务器端

---

     `<form id="execlform" action="<plansToDB.form" enctype="multipart/form-data" method="post" > <table><tr> <td style="width:70px;" nowrap>                            <input type="file" name="file" id="file" onchange="javascript:$('form').submit();"/>                            <button style="font:bolder;background-color: transparent;width: 100px;">导入</button>  </td> </tr></table></from>`

`
/css/  
 #file {
position:absolute;
margin:0px;
opacity:0;
filter:alpha(opacity:0);
z-index:999;
width:100px;
height:20px;
}

服务器端处理  

---

public ModelAndView plansToDBController (HttpServletRequest request,            HttpServletResponse response) throws Exception{        Workbook rwb = null;        String flag="";        boolean success=false;        int n=0;//记录导入记录条数        VOPlanout vop=new VOPlanout();        Dict dict=null;        PlanoutService planoutService=null;        ServletContext ctx = null;        BeanFactory factory = null;        Logger log = Logger.getLogger(this.getClass());        try {            ctx = this.getServletContext();            factory = (BeanFactory) WebApplicationContextUtils                    .getWebApplicationContext(ctx);            planoutService = (PlanoutService) factory.getBean("planoutService");            dict=(Dict)factory.getBean("dict");        } catch (Exception e) {            log.error("取得 spring PlanoutService 类时失败", e);        }        // 上传文件        MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;        for (Iterator<String> it = multipartRequest.getFileNames(); it                .hasNext();) {            String key = it.next();            MultipartFile multipartFile = multipartRequest.getFile(key);            if ((multipartFile != null) && !multipartFile.isEmpty()) {                InputStream input = multipartFile.getInputStream();                rwb = Workbook.getWorkbook(input);                // 获取第一张Sheet表                 Sheet rs = rwb.getSheet(0);                 String year=rs.getCell(0,0).getContents()==null?"":rs.getCell(0, 0).getContents().substring(0,4);                 String dwfullnames=rs.getCell(1, 1).getContents()==null?"":rs.getCell(1, 1).getContents();                 String dwid=dict.getdeptID(dwfullnames);//根据单位全称得到单位id                 if("单位名称有误!".equals(dwid)){                     flag="execl表单，申报单位输入不正确！";                 }else{                     //System.out.println("----------开始执行2----------------------");                    for (int k = 3; k <rs.getRows(); k++){                      String name=rs.getCell(0, k).getContents()==null?"":rs.getCell(0, k).getContents();                      String taskstyle=rs.getCell(1, k).getContents()==null?"":rs.getCell(1, k).getContents();                      String tasktype=rs.getCell(2, k).getContents()==null?"":rs.getCell(2, k).getContents();                      String is2team=rs.getCell(3, k).getContents()==null?"":rs.getCell(3, k).getContents();                      String danwei=rs.getCell(4, k).getContents()==null?"":rs.getCell(4, k).getContents();                      String attendteam=rs.getCell(5, k).getContents()==null?"":rs.getCell(5, k).getContents();                      String teamnum=rs.getCell(6, k).getContents()==null?"":rs.getCell(6, k).getContents();                      String dest=rs.getCell(7, k).getContents()==null?"":rs.getCell(7, k).getContents();                      String tasktime=rs.getCell(8, k).getContents()==null?"":rs.getCell(8, k).getContents();                      String daynum=rs.getCell(9, k).getContents()==null?"":rs.getCell(9, k).getContents();                      String funding=rs.getCell(10, k).getContents()==null?"":rs.getCell(10, k).getContents();                      String remarks=rs.getCell(11, k).getContents()==null?"":rs.getCell(11, k).getContents();                      /*System.out.println("---"+                              " -name-"+name+"-taskstyle:-"+taskstyle+"-tasktype:-"+tasktype+"-is2team-"+is2team+"-danwei:-"+danwei+"---"+                              " -attendteam:-"+attendteam+"-teamnum:-"+teamnum+"-dest:-"+dest+"-tasktime:"+tasktime+"-daynum:"+daynum+"--"+                              "-funding:"+funding+"-dwfullnames:"+dwfullnames+"-remarks:"+remarks+"---------");                        System.out.println("-------tasktime:-------"+tasktime+"--------------------");*/                      vop=new VOPlanout();                      vop.setName(name);//任务名称                      vop.setTaskstyle(taskstyle);//任务类型                      vop.setTasktype(tasktype);//任务分类                      vop.setIs2team(is2team);//是否双跨团                      vop.setDanwei(danwei);//组团单位                      vop.setAttendteam(attendteam);//参团单位及人员                     // vop.setTeamnum(Integer.valueOf(teamnum));//人数                      if(NumberUtils.isNumber(teamnum)){                          vop.setTeamnum(Integer.valueOf(teamnum));                      }else{                          vop.setTeamnum(0);                      }                      vop.setDest(dest);//出访国家及地区                      vop.setTasktime(tasktime);//时间                      vop.setDaynum(daynum);//天数                      if(NumberUtils.isNumber(funding)){                          vop.setFunding(new BigDecimal(funding));//经费预算                      }else{                          vop.setFunding(new BigDecimal(0));                      }                      vop.setDwfullname(dwfullnames);//申报单位                      vop.setRemarks(remarks);//备注                      vop.setDwid(dwid);//单位编码                      vop.setYear(year);//年份                      success=planoutService.save(vop);//执行数据库操作                      planoutService.report(vop.getPlancode(),true);                      if(success){                            ++n;                           }                         }                         rwb.close();                 }            }        }    return new ModelAndView("redirect:/plans/new/list.jsp?execlitems="+            n);                 }