update2.java

java 读写EXCEL文件的源码

/********************************************************************
 *
 * $RCSfile: Update2.java,v $  $Revision: 1.1 $  $Date: 2003/09/22 08:06:24 $
 *
 * $Log: Update2.java,v $
 * Revision 1.1  2003/09/22 08:06:24  icestone
 * init
 *
 *
 *
 **********************************************************************/
package pcdmupgradedata;

/**
*功能：修改中间表记录的类
*in:连接串,中间表名,新表名
*out:
**/
import java.sql.*;
import java.net.*;
import java.util.*;
import javax.swing.*;
import java.io.File;
import java.util.*;

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

public class Update2{
   Connection  conn;
   String      tabName;
   String      tname;
   String      ctableName;
   String      AreaCodeStr;
   JLabel      jLabel1;
   JLabel      jLabel3;
   JProgressBar jProgressBar1;
   int recordCount;
   static int  pvalue;
   static int  successRow;
   int  rowindex;
   static int  rowindex_excel;
   int selectValue;
   Statement   QueryStatement;
   WritableWorkbook workbook =null;
   WritableSheet sheet=null;
   String fileName=null;
   String acol;
   String frmErrorS="错误类型：";
   private int fileNum=0;
   private JButton jButton1 = new JButton();

   String DatePostfix="00:00:00.0";
   DateTime dateCell = null;
   DateFormat customDateFormat = new DateFormat ("yyyy-mm-dd");
   WritableCellFormat dateFormat = new WritableCellFormat (customDateFormat);

  public Update2
   (
   Connection conn,
   String tabName,               //中间表名
   String tname,                 //新结构表名
   String ctableName,            //中文表名
   String AreaCodeStr,
   JProgressBar jProgressBar1,
   JLabel jLabel1,
   JLabel jLabel3,
   String acol,
   int recordCount
   )
   {
   this.conn=conn;
   this.tabName=tabName;
   this.tname=tname;
   this.AreaCodeStr=AreaCodeStr;
   this.jLabel1=jLabel1;
   this.jLabel3=jLabel3;
   this.ctableName=ctableName;
   this.jProgressBar1=jProgressBar1;
   this.acol=acol;
   this.recordCount=recordCount;

   fileName=Const.BasePath+"//doc//无法对应excel//"+ctableName+".xls";

    try{
        QueryStatement = conn.prepareStatement("");

    }
    catch (java.sql.SQLException e)
    {
    }
   runUpdate();
   }

 private void runUpdate(){
          ResultSet TyResult=null;
 	  /**/
 	  String SecondSql=null;
 	  /**/
          String MslinkStr=null;
 	  /**/
          String CodeStr=null;
 	  /**/
          String JmStr=null;
 	  /**/
          String IdStr=null;
 	  /**/
 	  successRow=0;
 	  /**/
          rowindex=0;
          rowindex_excel=0;
 	  /**/
 	  String suFlag=null;
 	  /**/
   	  String FirstSql="SELECT * from "+tabName+" order by mslink";
          /*表转换成功标志*/
   	  String ThirdSql="UPDATE ZJDY_T SET READFLAG=1 WHERE TABLENAME='"+tabName+"'";
   	  String FouthSql=null;

   	  String FifthSql="delete from "+tname;

  try
    {
 	  /**/
        Label cell = null;
 	  /**/
        TyResult=QueryStatement.executeQuery(FirstSql);
/*得到表头列数*/
        int cols = (TyResult.getMetaData().getColumnCount())-(Integer.valueOf(acol)).intValue();
/*直线DP端子信息表*/
      try{
 	  if(tabName.equals("ZDPD_T")){
	     while(TyResult.next())
              {
                            MslinkStr=TyResult.getString("mslink");
                            String Local_zxdp_code=TyResult.getString("直线DP编码");
                            SecondSql="UPDATE ZDPD_T SET  TFLAG='1' where mslink="+MslinkStr;
                            FouthSql=InsertS.ZDPD+MslinkStr;
                            if(Local_zxdp_code!=null)
                                 {
                                  suFlag=execuUpdate(conn,SecondSql,FouthSql,jLabel1);
                                               if(suFlag!=null)
                                                 {
                                                   WriteErrorExl(suFlag,cell,TyResult,cols);
                                                   cell=null;
                                                   rowindex++;
                                                   rowindex_excel++;
                                                 }//end if
                                               else
                                                  successRow++;
                                }
                               else
                                {
                                 if(Local_zxdp_code==null)frmErrorS=frmErrorS+",无法对应直线DP编码";

                                  WriteErrorExl(frmErrorS,cell,TyResult,cols);
                                 cell=null;
                                 rowindex++;
                                 rowindex_excel++;
                                }//end if
                                frmErrorS="错误类型：";
                                Local_zxdp_code=null;
	       }//end while
                                TyResult.close();
 /*直线DP线缆关联表*/
 	}else if(tabName.equals("ZDPX_T")){
	     while(TyResult.next())
              {
                            MslinkStr=TyResult.getString("mslink");
                            String Local_code=TyResult.getString("直线DP编码");
                            String Local_dl_code=TyResult.getString("电缆编码");
                            SecondSql="UPDATE ZDPX_T SET  TFLAG='1' where mslink="+MslinkStr;
                            FouthSql=InsertS.ZDPX+MslinkStr;
                            if(Local_code!=null&&Local_dl_code!=null)
                                 {
                                  suFlag=execuUpdate(conn,SecondSql,FouthSql,jLabel1);
                                               if(suFlag!=null)
                                                 {
                                                   WriteErrorExl(suFlag,cell,TyResult,cols);
                                                   cell=null;
                                                   rowindex++;
                                                   rowindex_excel++;
                                                 }//end if
                                               else
                                                  successRow++;
                                }
                               else
                                {
                                 if(Local_code==null)frmErrorS=frmErrorS+",无法对应直线DP编码";
                                 if(Local_dl_code==null)frmErrorS=frmErrorS+",无法对应电缆编码";

                                  WriteErrorExl(frmErrorS,cell,TyResult,cols);
                                 cell=null;
                                 rowindex++;
                                 rowindex_excel++;
                                }//end if
                                frmErrorS="错误类型：";
                                Local_code=null;
                                Local_dl_code=null;
	      }//end while
                              TyResult.close();
/*配线架直列端子表*/
 	}else if(tabName.equals("PXDZ_T")){
	     while(TyResult.next())
              {
                            MslinkStr=TyResult.getString("mslink");
                            String Local_mdf_code=TyResult.getString("MDF编码");
                            SecondSql="UPDATE PXDZ_T SET  TFLAG='1' where mslink="+MslinkStr;
                            FouthSql=InsertS.PXDZ+MslinkStr;
                            if(Local_mdf_code!=null)
                                 {
                                  suFlag=execuUpdate(conn,SecondSql,FouthSql,jLabel1);
                                               if(suFlag!=null)
                                                 {
                                                   WriteErrorExl(suFlag,cell,TyResult,cols);
                                                   cell=null;
                                                   rowindex++;
                                                   rowindex_excel++;
                                                 }//end if
                                               else
                                                  successRow++;
                                }
                               else
                                {
                                 if(Local_mdf_code==null)frmErrorS=frmErrorS+",无法对应MDF编码";

                                  WriteErrorExl(frmErrorS,cell,TyResult,cols);
                                 cell=null;
                                 rowindex++;
                                 rowindex_excel++;
                                }//end if
                                frmErrorS="错误类型：";
                                Local_mdf_code=null;
	      }//end while
                              TyResult.close();
/*配线架跳线信息表*/
 	}else if(tabName.equals("PXTX_T")){
	     while(TyResult.next())
              {
                            MslinkStr=TyResult.getString("mslink");
                            String Local_mdf_code=TyResult.getString("MDF编码");
                            SecondSql="UPDATE PXTX_T SET  TFLAG='1' where mslink="+MslinkStr;
                            FouthSql=InsertS.PXTX+MslinkStr;