tojava
BlogJava 首页 新随笔 联系 聚合 管理
  17 Posts :: 6 Stories :: 55 Comments :: 0 Trackbacks
read excle with jexcle Api
package  excel;
 import  java.io.File;
 import  java.io.FileInputStream;
 import  java.io.FileOutputStream;
 import  java.io.FileWriter;
 import  java.io.InputStream;
 import  java.io.OutputStreamWriter;
 import  java.sql.Connection;
 import  java.sql.DriverManager;
 import  java.sql.Statement;
 import  org.apache.log4j. * ;

 import  jxl.Cell;
 import  jxl.Sheet;
 import  jxl.Workbook;
 public   class  ImportItems  {
    ImportItems() {
        String sqlStr2  =   "" ;
        
        Sheet rs = null ;
         // Cell c10=null;
         try {
            InputStream is  =   new  FileInputStream(excel.Constrants.items_filename); //
             jxl.Workbook rwb  =  Workbook.getWorkbook(is);
            rs  =  rwb.getSheet( 3 ); // 0,1,2,3
            } catch  (Exception e) {
                e.printStackTrace();
                }
            
             int  rsRows  =  rs.getRows();
             int  a = 0 ;
             try {    
                Class.forName( " com.microsoft.jdbc.sqlserver.SQLServerDriver " );
                Connection con  =  DriverManager.getConnection( excel.Constrants.url,  excel.Constrants.user,  excel.Constrants.password );
                StringBuffer strB =   new  StringBuffer();
                Statement st  =  con.createStatement();
                java.sql.ResultSet rset = null ;
                 for ( int  i = 1 ;i < rsRows;i ++ ) { // 文具54-56
                    
                    sqlStr2 = "   Insert into standarditem values(' " +
                    rs.getCell( 2 , i).getContents().trim() + " ',' " +
                    rs.getCell( 3 , i).getContents() + " ',' " +
                    rs.getCell( 7 , i).getContents() + " ',' " +
                    rs.getCell( 4 , i).getContents().replace(excel.Constrants.dot,  '   ' )
                    .replace( ' * ' ,  '   ' ).replace( ' - ' ,  '   ' ).replace( ' / ' ,  '   ' ).replace( ' = ' ,  '   ' )
                    .replace( ' # ' ,  '   ' ).replace( ' " ' ,  '   ' ).trim() + " ',' " +
                    rs.getCell( 5 , i).getContents() + " ',' " +
                    rs.getCell( 6 , i).getContents() + " ', " +
                    rs.getCell( 9 , i).getContents() + " , " +
                    getCategoreID(rs.getCell( 0 , i).getContents().trim()) + " ,0,' " +
                    rs.getCell( 12 , i).getContents() + " ',1,' " +
                    rs.getCell( 1 , i).getContents() + " ',' " +
                     " 729',' " +
                    rs.getCell( 8 , i).getContents() + " ') " ;
                    System.out.println(i + sqlStr2);
                     // strB.append(sqlStr2).append("\n");
                     // rset=st.executeQuery("select * from standarditem "); // where 編號='"+rs.getCell(0, i).getContents().trim()+"'");
                     // if(rset.next()){
                         // st.executeUpdate("delete standarditem where 編號='"+rs.getCell(0, i).getContents().trim()+"'");
                        
                         // st.executeUpdate("update standarditem set 品名='"+rset.getString("品名").replace(excel.Constrants.dot, ' ')+"'");
                     //     a++;
                         // System.out.println("delete standarditem where 編號='"+rs.getCell(0, i).getContents());
                     // }
                    st.executeUpdate(sqlStr2);
                    }
                 // System.out.println("update standarditem set 品名= replace(品名,''"+excel.Constrants.dot+"'',' ')");
                 // st.executeUpdate("update standarditem set 品名= replace(品名,'"+excel.Constrants.dot+"',' ')");
                 // File fl= new File("c:\\a.txt");
                 // FileWriter fw =new FileWriter(fl);
                 // fw.write(strB.toString());
                 // fw.close();
                System.out.println(a);
                st.close();
                con.close();
             } catch (Exception err) {
                   err.printStackTrace(System.out);}
             }

     int  getCategoreID(String name) {
         int  i = 6 ;
         if ( " 紙品 " .equals(name))  i = 1 ; 
         else  
             if ( " 藥品 " .equals(name)) i = 2 ;
             else  
                 if ( " 清潔用品 " .equals(name))i = 3 ;
                 else  
                     if ( " 文具 " .equals(name)) i = 4 ; 
                     else  
                         if ( " 印刷品 " .equals(name))i = 5 ;  
                              
                                
         return  i;
        
    }
     public   static   void  main(String[] args)  {
             ImportItems t =   new  ImportItems();
    }

}
posted on 2006-07-29 15:00 l like life i like java 阅读(170) 评论(0)  编辑  收藏 所属分类: web dev

只有注册用户登录后才能发表评论。
网站导航: