导入execl文件

/**//*
 * ******************************************************************
 * 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
        }
    }
}