在JSP中将EXEL文件的数据传入到数据库中

在jsp中：

在script中使用函数：

$(function(){  //var lpyear = document.getElementById("lpyear").value; //var lpmonth = document.getElementById("lpmonth").value; //var factoryid = document.getElementById("factoryid").value; //var url = "&lpyear="+lpyear+"&factoryid="+factoryid+"&lpmonth="+lpmonth; $("#file").uploadify({  "height" : 19,  "buttonText" : "导入本月数据",  "buttonClass" : "btn",  "fileTypeDesc" : "Excel Files",  "fileTypeExts" : "*.xls",  "swf" : <select:link page="/Charts/uploadify.swf" />,  "uploader" : <select:link page="/Budgetimp.do?method=monthimp" />,//+url,  "onUploadStart" : function(file){   showWait("正在上传"+file.name+"，请稍候...");  },  "onUploadSuccess" : function(file, data, response) {   $('#' + file.id).find('.data').html(' 上传完毕');   hideWait();  },  "onQueueComplete" : function(queueData) {   show();  } });});

在body中写入：

<td style="text-align: center;width: 150px;padding-bottom: 1px;">               <input type="file" name="file" id="file">              </td>

在java中：

//导入本月数据    @SupPRessWarnings("unchecked") public void monthimp(ActionMapping mapping, ActionForm form,            HttpServletRequest request,            HttpServletResponse response) throws Exception{     try {            InputStream is = null;         Workbook workbook = null;//创建Workbook对象（这一个对象代表着对应的一个Excel文件）                  List<FileItem> items = new ServletFileUpload(new DiskFileItemFactory()).parseRequest(request);         for(FileItem item:items){          if (item.isFormField()){           request.setAttribute(item.getFieldName(), item.getString());          }else{           is = item.getInputStream();                    workbook = Workbook.getWorkbook(is);          }         }                  if(workbook != null){          Sheet sheet = workbook.getSheet(0);//创建Sheet对象，代表着一个工作表          int row = sheet.getRows();///获取行数          ArrayList<String> sqls = new ArrayList<String>();          for(int i=0;i<row;i++){           String year="",month="",factoryid="",feename="",monthqty="",countqty="",tQQty="";                      //year=request.getParameter("lpyear");           //month=request.getParameter("lpmonth");           //factoryid=request.getParameter("factoryid");           Cell cell = sheet.getCell(0, i);//获取第一列所有行的值           if(cell!=null && cell.getContents() != null){            year = cell.getContents().trim();           }           cell = sheet.getCell(1, i);           if(cell!=null && cell.getContents() != null){            month = cell.getContents().trim();           }           cell = sheet.getCell(2, i);           if(cell!=null && cell.getContents() != null){            factoryid = cell.getContents().trim();           }           cell = sheet.getCell(3, i);           if(cell!=null && cell.getContents() != null){             feename= cell.getContents().trim();           }           cell = sheet.getCell(4, i);           if(cell!=null && cell.getContents() != null){            monthqty = cell.getContents().trim();           }           cell = sheet.getCell(5, i);           if(cell!=null && cell.getContents() != null){            countqty = cell.getContents().trim();           }           cell = sheet.getCell(6, i);           if(cell!=null && cell.getContents() != null){            tqqty = cell.getContents().trim();           }                      if(!"".equals(year)&&!"".equals(month)&&!"".equals(factoryid)&&!"".equals(feename))             //&&!"".equals(sycomp_id)&&NumberUtils.isNumber(sycomp_id)&&!"".equals(tobasystid)&&NumberUtils.isNumber(factoryid)             //&&NumberUtils.isNumber(tobasystid)判断字符串是否是数字             //&&!"".equals(monthqty)&&NumberUtils.isNumber(monthqty)&&Double.parseDouble(monthqty)>0             //&&!"".equals(countqty)&&NumberUtils.isNumber(countqty)&&Double.parseDouble(countqty)>0             //&&!"".equals(tqqty))&&NumberUtils.isNumber(tqqty)&&Double.parseDouble(tqqty)>0)             {                        if("".equals(monthqty)){monthqty="0";}            if("".equals(countqty)){countqty="0";}            if("".equals(tqqty)){tqqty="0";}            /*            Double monthqty1=Double.parseDouble(monthqty);            Double countqty1=Double.parseDouble(countqty);            Double tqqty1=Double.parseDouble(tqqty);            */            System.out.println(year+","+month+","+factoryid+","+feename+","+monthqty+","+countqty+","+tqqty);            /*            CallHelper helper =initializeCallHelper("G_logistisfeebymonth", form, request, false);                  helper.setParam("year", year);                  helper.setParam("month", month);                  helper.setParam("factoryid", factoryid);                  helper.setParam("feename", feename);                  helper.setParam("monthqty", monthqty);                  helper.setParam("countqty", countqty);                  helper.setParam("tqqty", tqqty);                  helper.setParam("personid", getPersonId(request));                  helper.execute();                                    StringBuffer exe = new StringBuffer();            exe.append("merge into l_logistisfeebymonth a ")             .append("using ( ")             .append("select * from table( ")             .append("values('2015','01','1','物流费用合计','5640.00','28333.00','26865.00') ")             .append(") as  tab(year,month,factoryid,feename,monthqty,countqty,tqqty) ")             .append(") b on a.year = b.year and a.month = b.month and a.factoryid = b.factoryid and a.feename = b.feename")             .append("when matched then ")             .append("update set a.monthqty = b.monthqty,a.countqty=b.countqty,a.tqqty=b.tqqty ")             .append("when not matched then ")             .append("insert (year,month,factoryid,feename,monthqty,countqty,tqqty,create_time,create_personid)")             .append("values(b.year,b.month,b.factoryid,b.feename,b.monthqty,b.countqty,b.tqqty,current timestamp,"+getPersonId(request)+")");            sqls.add(exe.toString());            */            String sql = "select year,month from l_logistisfeebymonth where year='"+year+"' and month='"+month+"' and factoryid='"+factoryid+"'";            Executer ex = new Executer();      SqlRuturn sp=ex.ExecSeletSQL(sql);      ArrayList<?> list = sp.getResultSet();      System.out.println(list.size());      if(list.size()==0){//判断数据是否存在       StringBuffer exe = new StringBuffer();             exe.append("insert into l_logistisfeebymonth(year,month,factoryid,feename,monthqty,countqty,tqqty,create_time,create_personid) ")              .append("values('"+year+"','"+month+"','"+factoryid+"','"+feename+"','"+monthqty+"','"+countqty+"','"+tqqty+"',current timestamp,"+getPersonId(request)+")");             sqls.add(exe.toString());      }                        /*            StringBuffer exe1 = new StringBuffer();            exe1.append("merge into l_logistisfeebymonth a ")            .append("using ( ")            .append("select year,month,factoryid,feename,monthqty,countqty,tqqty from l_logistisfeebymonth_temp ")            .append(") b on a.year = b.year and a.month = b.month and a.factoryid = b.factoryid")            .append("when matched then ")            .append("update set a.status=to_char(int(a.status)+1) ")            .append("when not matched then ")            .append("insert (year,month,factoryid,feename,monthqty,countqty,tqqty,create_time,create_personid)")            .append("values(b.year,b.month,b.factoryid,b.feename,b.monthqty,b.countqty,b.tqqty,current timestamp,"+getPersonId(request)+")");            sqls.add(exe1.toString());             */                 }          }          //sqls.add("update l_logistisfeebymonth set month = '0'||trim(month) where length(trim(month)) < 2");          OptionUtil.exeSqls(sqls);         }     } catch (Exception e) {   e.printStackTrace();  }    }