Read Excel

package com.embraiz.web;
import java.io.*; 
import java.sql.SQLException;

import com.embraiz.common.sql.SQLBridge;
import java.sql.SQLException;
import jxl.*; 
  
/**
#  * @author Ken
#  *
#  * To change the template for this generated type comment go to
#  * Window>Preferences>Java>Code Generation>Code and Comments
#  */ 
public class ExcelRead { 
  
     public static void main(String[] args) { 
         try { 
        SQLBridge sqlBridge = new SQLBridge();
        sqlBridge.setDriverName("com.mysql.jdbc.Driver");
             sqlBridge.setJdbcURL("jdbc:mysql://192.168.0.35/poadd");
             sqlBridge.setPassword("");
             sqlBridge.setUserName("");
             sqlBridge.setConnectionSwitch("TEST");
        Workbook book = Workbook.getWorkbook(new File("f:/siteMast.xls")); 
             //get a Sheet object.  
             Sheet sheet = book.getSheet(0); 
             //get 1st-Column,1st-Row content.
             int rowStart=0;
             int rowEnd=498;
             int colStart=0;
             int colEnd=7;
             Range[] range = sheet.getMergedCells();
             ExcelRead read=new ExcelRead();
             for(int j=rowStart;j<=rowEnd;j++){
            String siteId="-1";
            String size="";
            Double rateCard=0.0;
            Double standrad=0.0;
            for(int i=colStart;i<=colEnd;i++){
            if(i>=2){
            if(i==6){
                continue;
                 }
            Cell cell = sheet.getCell(i, j); 
                 String result = cell.getContents();
                 result=ExcelRead.removeBlank(result);
                
                 if(i==2&&result.equals(""))
                break;
                 if(i==2&&!result.equals("")){                                
                siteId=read.searchBySiteCode(sqlBridge,result);
               
                 }
                 if(siteId.equals("0"))
                break;
                 if(i==4){
                size=result;
                 }
                
                 if(result.equals("")){                
                for (int x = 0; x < range.length; x++) {
                         int topLeftCol=range[x].getTopLeft().getColumn();
                         int topLeftRow=range[x].getTopLeft().getRow();
                         int bottomRightCol=range[x].getBottomRight().getColumn();
                         int bottomRightRow=range[x].getBottomRight().getRow();
                         if(i>=topLeftCol&&j>=topLeftRow&&i<=bottomRightCol&&j<=bottomRightRow){
                        cell = sheet.getCell(topLeftCol, topLeftRow);
                          result = cell.getContents();
                                               
                          break;
                         }
                      }
                 }
                 if(i==5){
                String rateCardStr=result;
                if(ExcelRead.isNumber(rateCardStr)){
                rateCard=Double.valueOf(rateCardStr);
                }
                 }
                 if(i==7){
                String standradStr=result;
                if(ExcelRead.isNumber(standradStr)){
                standrad=Double.valueOf(standradStr);
                }
                 }
                 if(i==7){                
                read.updataBySiteId(sqlBridge, siteId, size, rateCard, standrad);                
                 }
            } 
            }
             }
             book.close(); 
         } catch (Exception e) { 
             e.printStackTrace(); 
         } 
  
     } 
     public  String  searchBySiteCode(SQLBridge sqlBridge,String siteCode)throws SQLException{   
    String siteId="0";
    try{
    StringBuffer selectString = new StringBuffer();
         selectString.append("Select obj_site.siteId,obj.OBJ_TITLE ");
         selectString.append(" From obj_site inner join obj  on ");
         selectString.append(" obj_site.siteId=obj.OBJ_ID where obj.OBJ_TITLE=? ");
         sqlBridge.prepareSQL(selectString.toString());
         sqlBridge.setPreValue(1, siteCode);
         sqlBridge.execPrepare();
         while(sqlBridge.nextRow()){
        siteId=sqlBridge.getFieldString("obj_site.siteId");
         }
    }catch(Exception e){
   
    }
    return siteId;
     }
    
     public void updataBySiteId(SQLBridge sqlBridge,String siteId,String size,Double rental,Double stadardpi)throws SQLException{
   
    try
         {
             sqlBridge.setAutoCommit(false);   
           
              String sqlUpdate="update obj_site set visualSize=?,rateCard=?,standardPICost=? where siteId=?";
              sqlBridge.prepareSQL(sqlUpdate);
              sqlBridge.setPreValue(1,size);
              sqlBridge.setPreValue(2,rental);
              sqlBridge.setPreValue(3,stadardpi);
              sqlBridge.setPreValue(4,siteId);
             
              sqlBridge.execUpdate();
              sqlBridge.commitTrans();
            
         }
         catch(Exception e)
         {
             e.printStackTrace();
             sqlBridge.rollbackTrans();
            
             throw new SQLException(e.getMessage());
         }
   
     }
    
     public static String removeBlank(String str){
    StringBuilder sb = new StringBuilder();
    char c =' ';
    for(int i = 0 ; i < str.length() ; i++){
    char ch = str.charAt(i);
    if(ch != c){
    sb.append(ch);
    }
    }
    return sb.toString();
     }
    
   
//////////////////////////////////////////////////////////////   
     public static boolean isInteger(String value) {
    try {
    Integer.parseInt(value);
    return true;
    } catch (NumberFormatException e) {
    return false;
    }
    }

   
    public static boolean isDouble(String value) {
    try {
    Double.parseDouble(value);
    if (value.contains("."))
    return true;
    return false;
    } catch (NumberFormatException e) {
    return false;
    }
    }

   
    public static boolean isNumber(String value) {
    return isInteger(value) || isDouble(value);
    }


}