updatet.java

java 读写EXCEL文件的源码

/********************************************************************
 *
 * $RCSfile: UpdateT.java,v $  $Revision: 1.1 $  $Date: 2003/09/22 08:06:24 $
 *
 * $Log: UpdateT.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.*;
import jxl.write.DateFormat;

public class UpdateT{
   Connection  conn;
   String      tabName;
   String      tname;
   String      ctableName;
   String      AreaCodeStr;
   JLabel      jLabel1;
   JLabel      jLabel3;
   JProgressBar jProgressBar1;
   static int  pvalue;
   static int  successRow;
   int  rowindex;
   static int  rowindex_excel;
   int selectValue;
   int recordCount;
   Statement   QueryStatement;
   String fileName=null;
   String  acol;
   String frmErrorS="错误类型：";
   WritableWorkbook workbook =null;
   WritableSheet sheet=null;
   private int fileNum=0;
   String DatePostfix="00:00:00.0";
   DateTime dateCell = null;

   DateFormat customDateFormat = new DateFormat ("yyyy-mm-dd");
   WritableCellFormat dateFormat = new WritableCellFormat (customDateFormat);

  public UpdateT
   (
   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
     {
//       WritableWorkbook workbook =null;
 	  /**/
//       WritableSheet sheet=null;
 	  /**/
        Label cell = null;
 	  /**/
        TyResult=QueryStatement.executeQuery(FirstSql);

/*得到表头列数*/
        int cols = (TyResult.getMetaData().getColumnCount())-(Integer.valueOf(acol)).intValue();
        try
 	   {
/*
 	    if(tabName.equals("QY_T"))
 	     {
	      while(TyResult.next())
               {
                            MslinkStr=TyResult.getString("MSLINK");
                            CodeStr=TyResult.getString("区域编码");
                            SecondSql="UPDATE QY_T SET  TFLAG='1' where mslink="+MslinkStr;

                            FouthSql=InsertS.QY+MslinkStr;
                            if(CodeStr!=null)
                                 {
                                  suFlag=execuUpdate(conn,SecondSql,FouthSql,jLabel1);
                                               if(suFlag!=null)
                                                 {
                                                   WriteErrorExl(suFlag,cell,TyResult,cols);
                                                   cell=null;
                                                   rowindex++;
                                                 }//end if
                                               else
                                                  successRow++;
                                }
                               else
                                {

                                  WriteErrorExl(frmErrorS,cell,TyResult,cols);
                                 cell=null;
                                 rowindex++;
                                }//end if
	      }//end while
                              TyResult.close();
*/
/*单位*/
          if(tabName.equals("DW_T")){
	     while(TyResult.next())
              {
                            MslinkStr           = TyResult.getString("mslink");
                            CodeStr             = TyResult.getString("单位编码");
                            String Local_qycode = TyResult.getString("所属区域编码");
                            SecondSql="UPDATE DW_T SET  TFLAG='1' where mslink="+MslinkStr;

                            FouthSql=InsertS.DW+MslinkStr;
                            if(CodeStr!=null &&Local_qycode!=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(CodeStr==null)frmErrorS=frmErrorS+"单位编码为空";
                                 if(Local_qycode==null)frmErrorS=frmErrorS+",无法对应所属区域编码";

                                 WriteErrorExl(frmErrorS,cell,TyResult,cols);
                                 cell=null;
                                 rowindex++;
                                 rowindex_excel++;
                                }//end if
                              frmErrorS="错误类型：";
                              Local_qycode=null;
	      }//end while
                              TyResult.close();
 /*局站*/
             }else if(tabName.equals("JZ_T")){
                  while(TyResult.next())
                   {

                                 MslinkStr           = TyResult.getString("mslink");
                                 CodeStr             = TyResult.getString("局站编码");
                                 String Local_dwcode = TyResult.getString("所属单位编码");
                                 SecondSql="UPDATE JZ_T SET  TFLAG='1' where mslink="+MslinkStr;

                                 FouthSql=InsertS.JZ+MslinkStr;
                                 if(Local_dwcode!=null&&CodeStr!=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(CodeStr==null)frmErrorS=frmErrorS+"局站编码为空";
                                      if(Local_dwcode==null)frmErrorS=frmErrorS+",无法对应所属单位编码";

                                      WriteErrorExl(frmErrorS,cell,TyResult,cols);
                                      cell=null;
                                      rowindex++;
                                      rowindex_excel++;
                                     }//end if
                                   frmErrorS="错误类型：";
                                   Local_dwcode=null;
                   }//end while
                                   TyResult.close();
/*局外缆线*/
        }else if(tabName.equals("JWLX_T")){
	     while(TyResult.next())
              {

                               MslinkStr           = TyResult.getString("mslink");
                               CodeStr             = TyResult.getString("缆线编码");
                               SecondSql="UPDATE JWLX_T SET QY_ID='"+AreaCodeStr+"', TFLAG='1' where mslink="+MslinkStr;
                               FouthSql=InsertS.JWLX+MslinkStr;
                               if(CodeStr!=null )