subducts.java

java 读写EXCEL文件的源码

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

/**
 * <p>Title: 子管类</p>
 * <p>Description: 根据管孔编码，查询所属管孔的子管，插入“管道表” </p>
 */
import java.sql.*;
import java.lang.*;
import java.util.Vector;
import javax.swing.*;

public class SubDucts {
  private Connection conn = null;
  private String strSql = "";
  private ErrorInfoExcel oErrorInfoExcel = null;

  private String strDuctCode;//所属管孔编码
  private String strSubDuctCode;//子管号
  private String strState;//子管状态
  private double dblX;//X
  private double dblY;//Y
  private double dblScale;//SCALE
  private String strDuctGpCode;//所属管道段编码
  private String strSourceDBName = null;
  private String strTargetDBName = null;

  public SubDucts() {
  }

  /**获取Conn连接*/
  public SubDucts(Connection conn,String strInSourceDBName,String strInTargetDBName,JLabel jLabel1){
    Vector vSubDucts = new Vector();
    this.conn = conn;
    this.strSourceDBName = strInSourceDBName;
    this.strTargetDBName = strInTargetDBName;

    String subDuctTable,ductTable;
    subDuctTable = strSourceDBName + ".ZG_T";
System.out.println("=======开始转换子管表======");
/*
    测试
delete i_pcdm.I_管道 where 所属管道 is not null;
commit;
delete SA.子管;
insert into SA.子管 select * from SA.子管_T;
commit;
*/
    //实例化 写错误信息到Excel文件类
    oErrorInfoExcel = new ErrorInfoExcel();
    oErrorInfoExcel.createExcel("子管","子管");
/*
    try{
      Statement st = conn.createStatement();

      //把子管的坐标转为在 管道截面图 中的绝对坐标
      strSql = "update " + subDuctTable + " a set a.x=(select a.x+b.起始断面坐标X from  " +
                      " I_管道 b where a.所属管孔编码=b.管道编码 and rownum = 1)," +
                      "a.y=(select a.y+b.起始断面坐标Y from  I_管道  b where a.所属管孔编码=b.管道编码 and rownum = 1)";
     st.executeUpdate(strSql);
     st.close();
    }catch(SQLException e){
      System.out.println(e.toString());
    }
*/
    //把子管记录插入“管道表”
    vSubDucts = getSubDucts();
    insertSubDuctsToMiddleDucts(vSubDucts,jLabel1);

    try{
      Statement st = conn.createStatement();
      strSql = "Update " + strTargetDBName + ".I_管道 set 管径=30 where 所属管道  is not null";
      st.executeUpdate(strSql);
    }catch(SQLException e){
      System.out.println(e.toString());
    }

    //关闭Excel文件
   oErrorInfoExcel.closeExcel();
  }

  /**查询得到“子管表”所有子管记录*/
  private Vector getSubDucts(){
    Vector vSubDucts = new Vector();
    SubDuctEntity oSubDuctEntity = null;
    double dblDuctX=0,dblDuctY=0;   //管孔截面图坐标
    double dblStartMHX=0,dblStartMHY=0;   //起始人井展开图坐标
    double dblEndMHX=0,dblEndMHY=0;       //终止人井展开图坐标

    Statement stmt = null;
    Statement stmtSelect = null;
    ResultSet rset = null;
    ResultSet rsetSelect = null;
    try{
      stmt = conn.createStatement();
      stmtSelect = conn.createStatement();
      //只取 中间表的管道表中有所属管孔的子管数据
      strSql = "Select a.所属管孔编码,a.子管号,a.子管状态," +
               "a.X,a.Y,a.SCALE,a.所属管道段编码 " +
               "From " + strSourceDBName + ".ZG_T a," + strTargetDBName + ".I_管道 b " +
               "Where a.所属管孔编码 = b.管道编码";
      rset = stmt.executeQuery(strSql);
      while (rset.next()) {
        strDuctCode = rset.getString("所属管孔编码");
        strSubDuctCode = rset.getString("子管号");
        strState = rset.getString("子管状态");
        dblX = rset.getDouble("X");
        dblY = rset.getDouble("Y");
        dblScale = rset.getDouble("SCALE");
        strDuctGpCode = rset.getString("所属管道段编码");

        //把子孔坐标转换为在 管道截面图 的管孔绝对坐标
        strSql = "select b.起始断面坐标X,b.起始断面坐标Y" +
                 " from " + strSourceDBName + ".ZG_T a," + strTargetDBName + ".I_管道 b" +
                 " where a.所属管孔编码=b.管道编码 and rownum = 1 " +
                 " and a.所属管孔编码='" + strDuctCode + "'";
//System.out.println(strSql);
        rsetSelect = stmtSelect.executeQuery(strSql);
        if(rsetSelect.next()){
          dblDuctX = rsetSelect.getDouble(1);
          dblDuctY = rsetSelect.getDouble(2);
        }
        dblDuctX = dblX + dblDuctX;
        dblDuctY = dblY + dblDuctY;
        //把子孔坐标转换为在 人井展开图 的管孔绝对坐标
         strSql = "select b.起始人井展开图坐标X,b.起始人井展开图坐标Y," +
                  "b.终止人井展开图坐标X,b.终止人井展开图坐标Y" +
                  " from " + strSourceDBName + ".ZG_T a," + strTargetDBName + ".I_管道 b" +
                  " where a.所属管孔编码=b.管道编码 and rownum = 1" +
                  " and a.所属管孔编码='" + strDuctCode + "'";
         rsetSelect = stmtSelect.executeQuery(strSql);
         if(rsetSelect.next()){
           dblStartMHX = rsetSelect.getDouble(1);
           dblStartMHY = rsetSelect.getDouble(2);
           dblEndMHX = rsetSelect.getDouble(3);
           dblEndMHY = rsetSelect.getDouble(4);
         }
        dblStartMHX = dblX + dblStartMHX;
        dblStartMHY = dblY + dblStartMHY;
        dblEndMHX = dblX + dblEndMHX;
        dblEndMHY = dblY + dblEndMHY;
        oSubDuctEntity = new SubDuctEntity(strDuctCode,strSubDuctCode,
                                           strState,dblDuctX,dblDuctY,
                                           dblStartMHX,dblStartMHY,
                                           dblEndMHX,dblEndMHY,
                                           dblScale,strDuctGpCode);
        vSubDucts.addElement(oSubDuctEntity);
      }
      rset.close();
      rset = null;
      stmt.close();
      stmt = null;
    }
    catch(SQLException e) {
      System.out.println("Select SQL error: " + e.toString());
      System.out.println(strSql);
    }
    finally {
      if (rset != null)
        try {rset.close();} catch(SQLException ignore) {}
      if (stmt != null)
        try {stmt.close();} catch(SQLException ignore) {}
    }
    return vSubDucts;
  }

  /**把子孔记录插入“管道表”*/
  private void  insertSubDuctsToMiddleDucts(Vector vDucts,JLabel jLabel1){
    int i;
    String strMater;
    SubDuctEntity oSubDuctEntity = null;
    double dblMaxX=0,dblMaxY=0;  //管孔组的最大X,Y
    double dblEndX,dblEndY;      //末端子孔的X,Y
    double dblStartMHX,dblStartMHY;   //起始人井展开图的坐标X,Y
    double dblEndMHX,dblEndMHY;       //终止人井展开图的坐标X,Y

    strMater = "塑料";   //子孔的管料 默认为 塑料
    dblScale = 0;       //子孔的管径 默认为 0

    Statement stmt = null;
    ResultSet rest = null;
    try {
      stmt = conn.createStatement();
      for (i=0;i<vDucts.size();i++){
        try {
          oSubDuctEntity = (SubDuctEntity)vDucts.elementAt(i);
          strDuctCode = oSubDuctEntity.getDuctCode();
          //获取 管孔组 最大的X 和 最大的Y
          strSql = "Select Max(b.起始断面坐标X) as X,Max(b.起始断面坐标Y) as Y" +
                   " From " + strTargetDBName + ".I_管道 a," + strTargetDBName + ".I_管道 b Where a.所属管道段编码=b.所属管道段编码 And a.管道编码 ='" + strDuctCode + "'";
          rest = stmt.executeQuery(strSql);
          if (rest.next()){
            dblMaxX = rest.getDouble(1);
            dblMaxY = rest.getDouble(2);
          }
          //加上半径
          dblMaxX = dblMaxX + 45;
          dblMaxY = dblMaxY + 45;

          strSubDuctCode = oSubDuctEntity.getSubDuctCode();
          strState = oSubDuctEntity.getState();
          dblX = oSubDuctEntity.getX();
          dblY = oSubDuctEntity.getY();
          dblEndX = dblMaxX - dblX;
          dblEndY = dblY;

          dblStartMHX = oSubDuctEntity.getStartMHX();
          dblStartMHY = oSubDuctEntity.getStartMHY();
          dblEndMHX   = oSubDuctEntity.getEndMHX();
          dblEndMHY   = oSubDuctEntity.getEndMHY();

          strDuctGpCode = oSubDuctEntity.getDuctGpCode();
          //子管的 管道编码 = 管孔编码 + "/" + 子管号
          strSubDuctCode = strDuctCode + "/" + strSubDuctCode;
          strSql = "Insert Into " + strTargetDBName + ".I_管道(" +
                     "管道编码,起始断面坐标X,起始断面坐标Y,终止断面坐标X,终止断面坐标Y," +
                     "所属管道段编码,所属管道,管径,管料,管道状态,设施状态," +
                     "起始人井展开图坐标X,起始人井展开图坐标Y,终止人井展开图坐标X,终止人井展开图坐标Y" +
                     ") Values('" + strSubDuctCode + "'," + dblX + "," + dblY +"," +
                     dblEndX + "," + dblEndY +",'" + strDuctGpCode + "','" +
                     strDuctCode + "'," + dblScale + ",'" + strMater + "','" + strState + "','竣工'," +
                     dblStartMHX + "," + dblStartMHY + "," + dblEndMHX + "," + dblEndMHY + ")";
         jLabel1.setText("生成子管管道断面坐标："+dblX+","+dblY+","+dblEndX+","+dblEndY);
            //查询 管道表 中是否转换了 子管 信息
            //select count(*) from I_管道 where 所属管道 is not null;
            stmt.execute(strSql);
        }catch (SQLException e1){
          System.out.println("Insert SQL Error:" + e1.toString());
          System.out.println(strSql);
          oErrorInfoExcel.writeExcelRow(e1.toString(),strSql);
        }
      }
      stmt.close();
      stmt = null;
    }
    catch(SQLException e) {
      System.out.println("SQL error: " + e.toString());
    }
    finally {
      if (stmt != null)
        try {stmt.close();} catch(SQLException ignore) {}
     }
  }
}