reader excelt to db

/*
* POIExcelReader.java
*
* Created on 2007年6月19日, 下午4:57
*
* To change this template, choose Tools | Template Manager
* and open the template in the editor.
*/
package sae.wafer.reader;

import java.text.DateFormat;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.hssf.record.*;
import org.apache.poi.hssf.model.*;
import org.apache.poi.hssf.util.*;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.record.aggregates.FormulaRecordAggregate;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.File;
import org.apache.log4j.*;
import java.util.*;
import java.util.Date;
import sae.wafer.bean.CollectionBean;
/**
*
* @author m082458
*/
public class POIExcelReader {

public static final Logger logger = Logger.getLogger("POITest");
//public static String fileToBeRead = "D:/read.xls";
private String fileToBeRead;
public ArrayList readExcelData(String str){
fileToBeRead=str;
ArrayList arrayList = new ArrayList();
try {
HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(
fileToBeRead));
HSSFSheet sheet = workbook.getSheet("Sheet1");
int rows = sheet.getPhysicalNumberOfRows();
for (int r = 0; r < rows; r++) {
HSSFRow row = sheet.getRow(r);
if (row != null) {
int cells = row.getPhysicalNumberOfCells();
String value = "";
for (short c = 0; c < cells; c++) {
Collection listRecord = new ArrayList();
HSSFCell cell = row.getCell(c);
if (cell != null) {
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_FORMULA:
//
break;
case HSSFCell.CELL_TYPE_NUMERIC:
//value += (long) cell.getNumericCellValue()
// + "\t";
value += (long) cell.getNumericCellValue()
+ "\t";
break;


case HSSFCell.CELL_TYPE_STRING:
value += cell.getRichStringCellValue() + "\t";

break;

default:
value += "\t";

}
}
}
String [] data = new String[5];
// 下面可以将查找到的行内容用SQL语句INSERT到oracle
System.out.println("the value is***********"+ value );
data = value.split("\t");

CollectionBean bean = new CollectionBean();
bean.setProject(data[0]);
bean.setProcess(data[1]);
bean.setNumber(data[2]);

bean.setCreate_time(data[3]);
arrayList.add(bean);
for(int i=0;i<data.length;i++){
System.out.println("the data array is; "+data[i]);
}
System.out.println();

// System.out.println("the values is "+ value);

//String [] data = new String[5];

}
}
} catch (Exception e) {
System.out.println(e);
}
return arrayList;
}

}


************************************************************

public class POBean {
private String PartNo;
private String WorkOrder;
private String PackNo;
private String Code;
private String ShortDesc;
private String LongDesc;

/** Creates a new instance of POBean */
public POBean() {
}

public String getCode() {
return Code;
}


public void setCode(String code) {
Code = code;
}


public String getLongDesc() {
return LongDesc;
}


public void setLongDesc(String longDesc) {
LongDesc = longDesc;
}


public String getPackNo() {
return PackNo;
}


public void setPackNo(String packNo) {
PackNo = packNo;
}


public String getPartNo() {
return PartNo;
}


public void setPartNo(String partNo) {
PartNo = partNo;
}


public String getShortDesc() {
return ShortDesc;
}


public void setShortDesc(String shortDesc) {
ShortDesc = shortDesc;
}

**********************************************************
import sae.wafer.db.DBHelper;
import java.sql.*;
import javax.sql.*;
import java.util.*;
public class POBusiness implements PO{
public ArrayList getPO(){
ArrayList alist = new ArrayList();
Connection conn = null;
Statement stmt=null;
ResultSet rst= null;
DBHelper db = new DBHelper();
conn=db.getConnection();

String sqlStr ="select *from PO where STATUS=0 ";
try{
stmt = conn.createStatement();
rst=stmt.executeQuery(sqlStr);
while(rst.next()){

HashMap hashMap = new HashMap();
hashMap.put("PartNo",rst.getString("PART_NO"));
hashMap.put("Work_Order",rst.getString("Work_Order"));
hashMap.put("Pack_No",rst.getString("Pack_No"));
hashMap.put("Code",rst.getString("Code"));
hashMap.put("Short_Desc",rst.getString("Short_Desc"));
hashMap.put("Long_Desc",rst.getString("Long_Desc"));
hashMap.put("Create_User",rst.getString("Create_User"));
hashMap.put("UPDATE_USER",rst.getString("UPDATE_USER"));
hashMap.put("UPDATE_TIME",rst.getString("UPDATE_TIME"));
hashMap.put("CREATE_TIME",rst.getString("CREATE_TIME"));

// System.out.println("the Create time is "+ rst.getString("Create_User"));
alist.add(hashMap);

}

} catch(Exception e){
e.printStackTrace();
} finally{
if (rst!= null){
try{
rst.close();
}catch(Exception e){
e.printStackTrace();
}
}
if (stmt!= null){
try{
stmt.close();
}catch(Exception e){
e.printStackTrace();
}
}
if (conn!= null){
try{
conn.close();
}catch(Exception e){
e.printStackTrace();
}
}
}

return alist;

}
public java.lang.String doDelete(HashMap deleteMap){
String retStr=null;
Connection conn =null;
PreparedStatement pstm = null;

DBHelper db = new DBHelper();
conn=db.getConnection();
String PartNo = (String)deleteMap.get("Part_No");


try{

String deleteSql = "DELETE FROM PO WHERE Part_No ='"+ PartNo+"'";
pstm= conn.prepareStatement(deleteSql);
pstm.executeUpdate();

retStr="Successful";
}catch(Exception e) {
retStr="Fail";
e.printStackTrace();

} finally{
if(pstm!=null){
try{
pstm.close();
}catch(Exception e){
e.printStackTrace();
}
}
if (conn!= null){
try{
conn.close();
}catch(Exception e){

}
}
}

return retStr.trim();

}

public java.lang.String doEdit(java.util.HashMap editMap){
String retStr=null;
Connection conn =null;
PreparedStatement pstm = null;

DBHelper db = new DBHelper();
conn=db.getConnection();

String PartNo = (String)editMap.get("Part_No");
String WorkOrder =(String)editMap.get("Work_Order");
String PackNo = (String)editMap.get("Pack_No");
System.out.print(" The PackNo is"+ PackNo);
String Code = (String)editMap.get("Code");
String ShortDesc =(String)editMap.get("Short_Desc");
String LongDesc=(String)editMap.get("Long_Desc");
String sqlEdit;
PartNo = PartNo.replaceAll("'"," ''");
WorkOrder=WorkOrder.replaceAll("'","''");
//................................
try {
// *****************************************************************
// PART_NO,WORK_ORDER,PACK_NO,CODE,SHORT_DESC,LONG_DESC,CREATE_TIME

sqlEdit = "UPDATE PO SET WORK_ORDER = '" +WorkOrder +"',PACK_NO='"+PackNo+ "',CODE='"+Code+"',SHORT_DESC='"+ShortDesc+"',LONG_DESC='"+LongDesc +"',UPDATE_TIME = CURRENT_TIMESTAMP" + " WHERE PART_NO ='" + PartNo+"'";
System.out.println("The sqlEdit is:"+ sqlEdit);
pstm = conn.prepareStatement(sqlEdit);
pstm.executeUpdate();
retStr="Successful";

} catch (Exception err) {
// logger.error(err.toString());
// rtnstr = err.toString();
err.printStackTrace();
} finally {
if(pstm!=null){
try{
pstm.close();
}catch(Exception e){
e.printStackTrace();
}
}
if (conn!= null){
try{
conn.close();
}catch(Exception e){

}
}

}
return retStr;

}

public java.lang.String doAdd(java.util.HashMap addMap){
String retStr=null;
Connection conn =null;
PreparedStatement pstm = null;

DBHelper db = new DBHelper();
conn=db.getConnection();

String PartNo = (String)addMap.get("Part_No");
String WorkOrder =(String)addMap.get("Work_Order");
String PackNo = (String)addMap.get("Pack_No");
String Code = (String)addMap.get("Code");
String ShortDesc =(String)addMap.get("Short_Desc");
String LongDesc=(String)addMap.get("Long_Desc");
System.out.println("the test is ************ Code is"+Code);

try{

String insertSql = "insert into PO(PART_NO,WORK_ORDER,PACK_NO,CODE,SHORT_DESC,LONG_DESC,CREATE_TIME) values('"+ PartNo+ "','"+ WorkOrder+ "','"+ PackNo + "','"+Code + "','"+ShortDesc + "','"+ LongDesc +"', CURRENT_TIMESTAMP"+")";
pstm= conn.prepareStatement(insertSql);
pstm.executeUpdate();

retStr="Successful";
}catch(Exception e) {
retStr="Fail";
e.printStackTrace();

} finally{
if(pstm!=null){
try{
pstm.close();
}catch(Exception e){
e.printStackTrace();
}
}
if (conn!= null){
try{
conn.close();
}catch(Exception e){

}
}
}

return retStr.trim();

}

}
********************************************************

import java.sql.*;
import java.util.*;
import org.apache.log4j.*;
import sae.wafer.bean.*;

public class WriteData {
String project;
String process;
String number;

Connection conn =null;
PreparedStatement pstm = null;
String sql;

public static final Logger logger = Logger.getLogger("writeData");

public String writeData (ArrayList arraylist){

String rtnStr = null;
ArrayList list =null;
CollectionBean bean = new CollectionBean();
Iterator iterator = arraylist.iterator();
int CountRecord=0;
for(int i=0;i<3;i++){
logger.info(" "+"\n") ;
}

this.makeConnection();

try{
sql = "insert into test(project,process,number,creat_time)values(?,?,?,?)";
pstm = conn.prepareStatement(sql);

while(iterator.hasNext()) {

bean= (CollectionBean)iterator.next();
pstm.setString(1,bean.getProject());
pstm.setString(2,bean.getProcess());
pstm.setString(3,bean.getNumber());
pstm.setString(4,bean.getCreate_time());
pstm.executeUpdate();
logger.info(sql);
CountRecord++;
}


}catch(Exception e){
e.printStackTrace();
}

finally{
if (conn!= null){
try{
conn.close();
}catch(Exception e){

}
}

}
rtnStr="Total "+ CountRecord + " Record Operate Successful! ";
return rtnStr;
}


private Connection makeConnection() {

try {
logger.debug(this.getClass().getName() + " is invoked.");
logger.debug("Connect mysql Database......");

Class.forName("com.mysql.jdbc.Driver");
//logger.info("go on......");

conn= DriverManager.getConnection("jdbc:mysql://10.10.74.82:3306/datacollect","test","12345678");


} catch (Exception ex) {
ex.printStackTrace();
logger.info(ex);
}
logger.info("get the connection");
return conn;
}


}