transferchang.java

java 读写EXCEL文件的源码

/********************************************************************
 *
 * $RCSfile: transferChang.java,v $  $Revision: 1.1 $  $Date: 2003/09/22 08:06:24 $
 *
 * $Log: transferChang.java,v $
 * Revision 1.1  2003/09/22 08:06:24  icestone
 * init
 *
 *
 *
 **********************************************************************/
package pcdmupgradedata;
/**
*功能：静态调用转换中间表
*算法依据：
*入：connection
*出：
**/
import java.sql.*;
import java.net.*;
import javax.swing.*;
import java.util.*;
import java.io.File;

import jxl.*;
import jxl.write.*;

  public class transferChang{
 /**
 *中间库转换
 **/
    public static void UpdateLoop
    (
     Connection conn,
     String AreaCodeStr,
     JProgressBar jProgressBar1,
     JLabel jLabel1,
     JLabel jLabel3
    )
     {
           Vector TableName=new Vector();
           Vector NewTableName= new Vector();
           Vector cTableName =new Vector();
           Vector addCol=new Vector();
           UpdateT u=null;
           Update2 u2=null;
           int pos= 0;
      try{
/*得到需调整数据表*/
         WritableWorkbook workbook =null;
         WritableSheet sheet=null;
         workbook = Workbook.createWorkbook((new File(Const.COUNT_XLS_PATH)));
         sheet = workbook.createSheet("转换统计记录",0) ;
         Label cell = null;

         String Query = "SELECT ltrim(a.TABLENAME),ltrim(b.ENAME),ltrim(b.CNAME),ltrim(a.ACOUNT) FROM ZJDY_T a,u_feature_t b where Rtrim(ltrim(a.CTABLENAME))=Rtrim(ltrim(b.CNAME)) AND a.readFlag=0 ORDER BY a.SER";
         Statement QueryTableStatement = conn.createStatement();
         ResultSet TableNameResult=QueryTableStatement.executeQuery(Query);
	 while(TableNameResult.next())
           {
                TableName.addElement(TableNameResult.getString(1));
                NewTableName.addElement(TableNameResult.getString(2));
                cTableName.addElement(TableNameResult.getString(3));
                addCol.addElement(TableNameResult.getString (4));
	   }//end while
          QueryTableStatement.close();
          TableNameResult.close();
/*拆分Vector,创建Updata实例*/
          if(TableName.size()!=0)
            {

               while(pos<TableName.size())
                     {

                 	String TmpName=(String)TableName.elementAt(pos);
          	        String TmpNewName=(String)NewTableName.elementAt(pos);
          	        String TmpcName=(String)cTableName.elementAt(pos);
          	        String TmpCol=(String)addCol.elementAt(pos);
          	        jProgressBar1.setValue(jProgressBar1.getValue()+100);
          	        jLabel3.setText(TmpcName);
         	        u=new UpdateT(conn,TmpName,TmpNewName,TmpcName,AreaCodeStr,jProgressBar1,jLabel1,jLabel3,TmpCol,0);
                        u=null;
         	        u2=new Update2(conn,TmpName,TmpNewName,TmpcName,AreaCodeStr,jProgressBar1,jLabel1,jLabel3,TmpCol,0);
                        u2=null;

/*在excel写转换统计记录数*/
                        if(pos==0)
                         {
                              cell = new Label(0,pos,"设施表");
                              sheet.addCell(cell);
                              cell = new Label(1,pos,"总记录数");
                              sheet.addCell(cell);
                              cell = new Label(2,pos,"已转换记录数");
                              sheet.addCell(cell);
                              cell = new Label(3,pos,"未能转换记录数");
                              sheet.addCell(cell);
                         }
                       	pos++;

                       cell = new Label(0,pos,TmpcName);
                       sheet.addCell(cell);
                       cell = new Label(1,pos,String.valueOf((UpdateT.successRow+UpdateT.rowindex_excel)+(Update2.successRow+Update2.rowindex_excel)));
                       sheet.addCell(cell);
                       cell = new Label(2,pos,String.valueOf(UpdateT.successRow+Update2.successRow));
                       sheet.addCell(cell);
                       cell = new Label(3,pos,String.valueOf(UpdateT.rowindex_excel+Update2.rowindex_excel));
                       sheet.addCell(cell);
                       TmpName=null;
                       TmpNewName=null;
                       TmpcName=null;
                       cell=null;
                  }//end while
               workbook.write();
               workbook.close();
               TableName=null;
               NewTableName=null;
               cTableName=null;
               addCol=null;
               RWConfig.writeControl(Const.MIDDLE_FLAG);
            }//end if
         }//end try
     catch (java.sql.SQLException se)
        {
              JOptionPane.showConfirmDialog(null, "数据库中间表读写错误！忽略错误继续转换吗\n"+se,
                                                          "错误",JOptionPane.OK_OPTION, JOptionPane.INFORMATION_MESSAGE);
               System.exit(0);
         }
     catch(jxl.write.WriteException e2){
              JOptionPane.showConfirmDialog(null, "数据库读写错误！忽略错误继续转换吗\n"+e2,
                                                          "错误",JOptionPane.OK_OPTION, JOptionPane.INFORMATION_MESSAGE);

               System.exit(0);
        }
      catch(java.io.IOException ioe){
              JOptionPane.showConfirmDialog(null, "数据库读写错误！忽略错误继续转换吗\n"+ioe,
                                                          "错误",JOptionPane.OK_OPTION, JOptionPane.INFORMATION_MESSAGE);

               System.exit(0);
            }
     }//end UpdateLoop
  }//end class