jxl.txt

ADF读取excel中的内容,然后更新到数据库中去

    public void updateExecelToDB(ValueChangeEvent event)throws  Exception{
    
            
            Integer nameCol=null;
            
            Integer priceCol=null;
            
            String titName="名称";
            String titPrice="单价";
            
            
            UploadedFile file = (UploadedFile)event.getNewValue();
            String fileName= null;
            if (file != null && file.getLength() > 0) {
                fileName = file.getFilename();
            }else{ 
                return ;
            }

            OperationBinding operationBinding = bindings.getOperationBinding("execelToDB");
            //File tempFile=new File("d:/TEST.xls");
            InputStream is = file.getInputStream();
            
            
            jxl.Workbook rwb = Workbook.getWorkbook(is);
            
            Sheet sheet = rwb.getSheet(0); 
            
            int  columnum  =  sheet.getColumns(); 
            int  rownum  =  sheet.getRows(); 
           

            //
            for(int i=0;i<columnum;i++){
                
                Cell colId = sheet.getCell(i, 0);
                String strId = colId.getContents();
              
                if(strId!=null&&strId.equals(titName)){
                    
                    nameCol=i;
                    break;
                }
            }
            
            for(int i=0;i<columnum;i++){     
                  
                Cell colPrice = sheet.getCell(i, 0);
                String strPrice = colPrice.getContents();
                if(strPrice!=null&&strPrice.equals(titPrice)){
                    
                    priceCol=i;
                    break;
                   
                }
                
            }
            
            if(nameCol==null&&priceCol==null){
                ResourceBundle bundle = ResourceBundle.getBundle("webview.resources.UIResources");
                 FacesContext context = FacesContext.getCurrentInstance();
                 FacesMessage message = 
                  new FacesMessage(FacesMessage.SEVERITY_WARN, bundle.getString("srmain.srfileupload.error.filetype") , null);
                 context.addMessage(null, 
                                      message);  
                return ;
            }

           // Row row=iter
            double price=0.00;
            String name=null;
            
                
                 int printClo=2;
                 
                 int lenRow=rownum-1;
                
                 
                 int printPage=0;
                 int mod=lenRow%printClo;
                 if(mod==0){
                    printPage=(lenRow/printClo);
                 }else{
                     printPage=(lenRow/printClo)+1;
                 }

                for(int i=0;i<printPage;i++){
                                ArrayList sb1=null;
                                ArrayList sb2=null;
                                sb1=new ArrayList();
                                sb2=new ArrayList();
                                 //int row=0;
                                 int rowCurr=(printClo*i)+1;
                                 System.out.println("rowCurr="+rowCurr);
                                 for(int x=rowCurr;x<=printClo*i+printClo;x++){
                                    if(x>lenRow){
                                         break;
                                    }
                                
                                    Cell colId = sheet.getCell(nameCol, x);
                                    name= colId.getContents();
                                    if (name == null&&name.length()<1){  
                                           ResourceBundle bundle = ResourceBundle.getBundle("webview.resources.UIResources");
                                            FacesContext context = FacesContext.getCurrentInstance();
                                            FacesMessage message = 
                                             new FacesMessage(FacesMessage.SEVERITY_WARN, bundle.getString("srmain.srfileupload.error.halfBaked") , null);
                                            context.addMessage(null, message);  
                                           return ;
                                       }
                                    sb1.add(name);
                                    
                                    Cell colPrice = sheet.getCell(priceCol, x);
                                    String strPrice = colPrice.getContents();
                                    if (strPrice == null&&strPrice.length()<1){  
                                           ResourceBundle bundle = ResourceBundle.getBundle("webview.resources.UIResources");
                                            FacesContext context = FacesContext.getCurrentInstance();
                                            FacesMessage message = 
                                             new FacesMessage(FacesMessage.SEVERITY_WARN, bundle.getString("srmain.srfileupload.error.halfBaked") , null);
                                            context.addMessage(null, message);  
                                           return ;
                                       }
                                    
                                    price=Double.parseDouble(strPrice)*1000;
                                    sb2.add(price);
                }

                if(sb1!=null && sb2!=null){
                    
                    operationBinding.getParamsMap().put("name",sb1);
                    operationBinding.getParamsMap().put("price",sb2);
                    operationBinding.execute();
                    
                    if(operationBinding.getErrors().isEmpty()==false) {    
                        operationBinding = bindings.getOperationBinding("Rollback");
                        operationBinding.execute();    
                         return ;
                    }
                    
                }
                
                
            }
            
            operationBinding = bindings.getOperationBinding("Commit");
            operationBinding.execute();
            if(operationBinding.getErrors().isEmpty()){
                ResourceBundle bundle = ResourceBundle.getBundle("webview.resources.UIResources");
                 FacesContext context = FacesContext.getCurrentInstance();
                 FacesMessage message = 
                  new FacesMessage(FacesMessage.SEVERITY_WARN, bundle.getString("srmain.srfileupload.success") , null);
                 context.addMessage(null, message);  
                return ;
            }else{
                ResourceBundle bundle = ResourceBundle.getBundle("webview.resources.UIResources");
                 FacesContext context = FacesContext.getCurrentInstance();
                 FacesMessage message = 
                  new FacesMessage(FacesMessage.SEVERITY_WARN, bundle.getString("srmain.srfileupload.error") , null);
                 context.addMessage(null, message);  
                return ;
            }
        
        }