在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中写入：

在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();
  }
    }