ducts.java

java 读写EXCEL文件的源码

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

/**
 * <p>Title: 管孔类</p>
 * <p>Description:根据传入的管道段编号，得到起始/终止管孔组，<br>
 *                通过比较，生成“管道表” </p>
 */
import java.sql.*;
import java.lang.*;
import java.util.Vector;
import java.util.StringTokenizer;
import javax.swing.*;

public class Ducts {
  private Connection conn = null;
  private DuctGpEntity oDuctGpEntity = null;
  private ErrorInfoExcel oErrorInfoExcel = null;
  private String strSql = null;
  private String strSourceDBName = null;
  private String strTargetDBName = null;

  public Ducts() {
  }
   /**获取Conn连接，并实例化管道段类，*/
  public Ducts(Connection conn,String strInSourceDBName,String strInTargetDBName,JLabel jLabel1){
    this.conn = conn;
    this.strSourceDBName = strInSourceDBName;
    this.strTargetDBName = strInTargetDBName;
    //实例化 写错误信息到Excel文件类
    oErrorInfoExcel = new ErrorInfoExcel();
    oErrorInfoExcel.createExcel("管孔","管孔");

Debug.print("=======开始转换管孔表======");

    int i,j; //循环计数器
    Vector vDuctGpNos = new Vector();  //管道段编号数组
    Vector vDuctCodes = new Vector();  //管孔编号数组
    //获取所有管道段记录的编码
    DuctGp oDuctGp = new DuctGp(conn,strSourceDBName);
    vDuctGpNos = oDuctGp.getDuctGpNos();
    //测试
    System.out.println("管道段数量:" + vDuctGpNos.size());

    for(i=0;i<vDuctGpNos.size();i++){
      DuctGpEntity oDuctGpEntitySet = (DuctGpEntity) vDuctGpNos.elementAt(i);
      //传入 管道段实体类
      setDuctGp(oDuctGpEntitySet);
      //得到管道段中包含 电缆 数量多的一面的管孔组
      vDuctCodes = createMaxCableDucts(jLabel1);
Debug.print(i);
/*
      //测试
      //select count(*) from I_管道
      //select count(*) from I_管道 where 所属管道 is not null
      //select count(*) from sa.管孔
      //select count(*) from sa.子管
*/
    }//end of for
    //关闭Excel文件
   oErrorInfoExcel.closeExcel();
  }

  /**传入管道段编号*/
  public void setDuctGp(DuctGpEntity oDuctGpEntity){
    this.oDuctGpEntity = oDuctGpEntity;
  }

  /**查询“管道段”表，根据“起始设施编码”字段，得到管道段起始设施类型和编码*/
  private Vector getStartDucts(){
    String strStartECode = null;       //起始设施编码
    String strBelongDuctGpCode = null; //所属管道段编码

    strStartECode = oDuctGpEntity.getStartECode();
    strBelongDuctGpCode = oDuctGpEntity.getDuctGpCode();

    Vector vStartDucts  = getDuctsByBelongCode(strStartECode,strBelongDuctGpCode);
    return vStartDucts;
  }

  /**查询“管道段”表，根据“终止设施编码”字段，得到管道段终止设施类型和编码*/
  private Vector getEndDucts(){
    String strEndECode = null;       //终止设施编码
    String strBelongDuctGpCode = null; //所属管道段编码

    strEndECode = oDuctGpEntity.getEndECode();
    strBelongDuctGpCode = oDuctGpEntity.getDuctGpCode();

    Vector vEndDucts  = getDuctsByBelongCode(strEndECode,strBelongDuctGpCode);
    return vEndDucts;
  }

  /**返回起始管孔组包含电缆数量*/
  private int getStartDuctCableCount(Vector vStartDucts){
    int intStartDuctCableCount = 0;
    int i;
    int intCableCount = 0;

    //测试
    //System.out.println("起始管孔数量:" + vStartDucts.size());
    try{
      for (i=0;i<vStartDucts.size();i++){
        //测试
        //System.out.println(vStartDucts.elementAt(i).toString());
        intCableCount = getCableCountInDuct((DuctEntity)vStartDucts.elementAt(i));
        intStartDuctCableCount = intStartDuctCableCount + intCableCount;
      }//end of for
    }catch(NullPointerException e){
      System.out.println("起始管孔组包含电缆数 method:" + e.toString());
      return intStartDuctCableCount;
    }
    //最后返回起始管孔组包含电缆数量
    return intStartDuctCableCount;
  }

  /**最后返回终止管孔组包含电缆数量*/
  private int getEndDuctCableCount(Vector vEndDucts){
    int intEndDuctCableCount = 0;
    int i;
    int intCableCount = 0;

    //测试
    //System.out.println("终止管孔数量:" + vEndDucts.size());
    try{
      for (i=0;i<vEndDucts.size();i++){
        //测试
        //System.out.println(vStartDucts.elementAt(i).toString());
        intCableCount = getCableCountInDuct((DuctEntity)vEndDucts.elementAt(i));
        intEndDuctCableCount = intEndDuctCableCount + intCableCount;
      }//end of for
    }catch(NullPointerException e){
      System.out.println("终止管孔组包含电缆数 method:" + e.toString());
      return intEndDuctCableCount;
    }
    //最后返回终止管孔组包含电缆数量
    return intEndDuctCableCount;
  }

  /**查询“管孔表”，根据“所属设施编码”和“所属管道段编码”，得到管孔组*/
  private Vector getDuctsByBelongCode(String strBelongCode,
                                      String strBelongDuctGpCode){
    Vector vDucts = new Vector();
    String strDuctCode = null;
    double dblX,dblY;
    Statement stmt = null;
    ResultSet rset = null;
    try{
      stmt = conn.createStatement();
      strSql = "Select 管孔编码,X,Y " +
               "From " + strSourceDBName + ".GK_T " +
               "where 所属设施编码='" + strBelongCode + "'" +
               " and 所属管道段编码='" + strBelongDuctGpCode + "'";
      rset = stmt.executeQuery(strSql);
      while(rset.next()){
        try{
          strDuctCode = rset.getString("管孔编码");
          dblX = rset.getDouble("X");
          dblY = rset.getDouble("Y");
          DuctEntity oDuctEntity = new DuctEntity(strDuctCode,dblX,dblY);
          vDucts.addElement(oDuctEntity);
        }catch(NullPointerException e){
          System.out.println("Change  getDuctsByCode  " + e.toString());
        }
    }
      rset.close();
      rset = null;
      stmt.close();
      stmt = null;
    }
    catch(SQLException e) {
      System.out.println("SQL error: " + e.toString());
    }
    finally {
      if (rset != null)
        try {rset.close();} catch(SQLException ignore) {}
      if (stmt != null)
        try {stmt.close();} catch(SQLException ignore) {}
    }
    return vDucts;
  }
  /**根据管孔编号，获取占孔电缆数量*/
  private int getCableCountInDuct(DuctEntity oDuctEntity){
    int intCableCount = 0;

    Statement stmt = null;
    ResultSet rset = null;
    String strSToken = null; //分解的单个String
    String strNewMHCode = ""; //拆分并组合后的人手井编号
    String strNewDuctCode = ""; //拆分并组合后的管孔编号
    String strDuctCode = "";  //管孔编码
    int Count = 0; //计数器

    strDuctCode = oDuctEntity.getDuctCode();
    //拆分 管孔编号 从 "AYXTXI00/FZ#0004/B/11" 到 "B11"
    StringTokenizer oSToken = new StringTokenizer(strDuctCode,"/",false);
    try{
      strNewMHCode = oSToken.nextToken() + "/" + oSToken.nextToken();
      strNewDuctCode = oSToken.nextToken() + oSToken.nextToken();
    }catch (Exception e) {
      System.out.println(e.toString());
    }
/*
    while (oSToken.hasMoreTokens()){
      strSToken = oSToken.nextToken();
      if (Count>1){
        strNewDuctCode = strNewDuctCode + strSToken;
        //测试
        //System.out.println(strNewDuctCode);
      }
      else {
        strNewMHCode = strNewMHCode + strSToken;
      }
      Count++;
    }
*/
    try{
      stmt = conn.createStatement();
      strSql = "Select count(rowid) as cablecount " +
               "From " + strSourceDBName + ".GKZY_T " +
               "where 人手井编码='" + strNewMHCode + "'" +
               " and 管孔编号='" + strNewDuctCode + "'";
      //测试
      //System.out.println(strSql);

      rset = stmt.executeQuery(strSql);
      while (rset.next()) {
        intCableCount = rset.getInt("cablecount");
      }
      rset.close();
      rset = null;
      stmt.close();
      stmt = null;
    }
    catch(SQLException e) {
      System.out.println("SQL error: " + e.toString());
    }