  /**//*
 * ******************************************************************
* Project Name: aspfirst
* test.java 2006-9-18
* Copyright by Beijing Uland.com
* All right reserved.
* Authou: wangchuyun
 * ******************************************************************/
package cn.cy2;
import java.io.File;
import java.io.FileFilter;
import java.io.FileInputStream;
import java.io.FilenameFilter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import jxl.Workbook;
public class test {
private Connection conn= null;
private PreparedStatement pstmt = null;
private String sql = "insert into txl_schoolfellow " +
"(txl_name,txl_classname,txl_homephone,txl_mobile,txl_officephone,txl_work,txl_address,txl_postcode,txl_email)" +
" values (?,?,?,?,?,?,?,?,?);";
//private static final String dbSource="jdbc:mysql://127.0.0.1:3306/test?user=root&password=gsp&autoReconnect=true&useUnicode=true&characterEncoding=UTF-8";
private static final String dbSource="jdbc:datadirect:sqlserver://127.0.0.1:2433;DatabaseName=aspfirstSMS;";
  static{
try{
//Class.forName("org.gjt.mm.mysql.Driver").newInstance();
Class.forName("com.ddtek.jdbc.sqlserver.SQLServerDriver").newInstance();
} catch (Exception e){
// TODO: handle exception
e.printStackTrace();
 }
}
private int countNum=0;
public static void main(String[] arge){
test a = new test();
try{
a.openDB();
a.dumpDirectory(new File("c:\\AAAA\\"));
a.closeDB();
System.out.print(a.countNum);
} catch (Exception e){
e.printStackTrace();
// TODO: handle exception
}
}
private void openDB(){
try{
//conn = DbManager.getNewConnection("jdbc/paycard_test_cyworld");
conn = DriverManager
.getConnection(dbSource,"aspfirst","4!#&8(__)");
pstmt = conn.prepareStatement(sql);
} catch (Exception e){
e.printStackTrace();
System.exit(1);
return;
// TODO: handle exception
}
}
private void closeDB(){
try{
pstmt.close();
conn.close();
} catch (Exception e){
e.printStackTrace();
return;
// TODO: handle exception
}
}
/** *//**
* Dumps out the contents of the excel file
*/
private void display(File file) throws IOException{
FileInputStream fis= new FileInputStream(file);
jxl.Workbook rwb = null;
try{
rwb= Workbook.getWorkbook(fis);
} catch (Exception e){
e.printStackTrace();
fis.close();
return;
// TODO: handle exception
}
jxl.Sheet sh= rwb.getSheet(0);
int rowCount= sh.getRows();
countNum+=rowCount;
for (int i= 0; i < rowCount; i++)
{
jxl.Cell[] ce= sh.getRow(i);
String[] user = new String[9];
for(int j= 0; j<user.length; j++){
if(j<ce.length){
String txt = ce[j].getContents().toString().trim();
txt = txt.replaceAll(" ", "");
user[j]=txt;
}else{
user[j]="";
}
}
saveDB(user);
}
rwb.close();
fis.close();
}
private void readFiles(File dir){
File[] files= dir.listFiles(new FilenameFilter(){
public boolean accept(File dir, String name){
name = name.toLowerCase();
return (name.endsWith(".xls"));
}
});
for(int i= 0; i<files.length; i++){
if(files[i].length()>0){
//读取xls文件
System.out.println(files[i]);
try{
display(files[i]);
} catch (Exception e){
e.printStackTrace();
// TODO: handle exception
}
}
}
}
private void dumpDirectory(File dir){
readFiles(dir);
File[] files= dir.listFiles(new FileFilter(){
public boolean accept(File dir){
if(dir.isDirectory()){
///data1/2006/05/05/026/
return true;
}else{
return false;
}
}
});
for(int i= 0; i<files.length; i++){
//System.out.println(files[i]);
dumpDirectory(files[i]);
}
}
private void saveDB(String user[]){
boolean isNull = true;
for(int i= 0; i<user.length; i++){
if(!"".equals(user[i])){
isNull = false;
}
}
if(isNull){
return;
}
//报存数据
try{
for(int i= 0; i<user.length; i++){
pstmt.setString(i+1, user[i]);
}
pstmt.execute();
} catch (Exception e){
e.printStackTrace();
// TODO: handle exception
}
}
}
|
