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


}