middleducts.java

java 读写EXCEL文件的源码

/********************************************************************
 *
 * $RCSfile: MiddleDucts.java,v $  $Revision: 1.1 $  $Date: 2003/09/22 08:06:24 $
 *
 * $Log: MiddleDucts.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.Double;
import java.util.Vector;
import javax.swing.*;

public class MiddleDucts {
  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 MiddleDucts() {
  }

  /**获取Conn连接*/
  public MiddleDucts(Connection conn,String strInSourceDBName,String strInTargetDBName,JLabel jLabel1) {
    this.conn = conn;
    this.strSourceDBName = strInSourceDBName;
    this.strTargetDBName = strInTargetDBName;
    int i,j; //循环计数器

    //实例化 写错误信息到Excel文件类
    oErrorInfoExcel = new ErrorInfoExcel();
    oErrorInfoExcel.createExcel("管道","管道");
System.out.println("========管孔编号开始=======");
    Vector vDuctGpNos = new Vector();  //管道段编号数组
    //获取所有管道段记录的编码
    DuctGp oDuctGp = new DuctGp(conn,strSourceDBName);
    vDuctGpNos = oDuctGp.getDuctGpNos();
    //测试
//    System.out.println("管道段数量:" + vDuctGpNos.size());
    try {
      for(i=0;i<vDuctGpNos.size();i++){
        DuctGpEntity oDuctGpEntitySet = (DuctGpEntity) vDuctGpNos.elementAt(i);
        //传入 管道段实体类
        setDuctGp(oDuctGpEntitySet);
        //对起始断面坐标进行冒泡排序，而终止断面坐标中序号与起始序号一样
        sortStartSideDuctNo(jLabel1);
      }
    }catch(NullPointerException e){
      System.out.println("MiddleDucts："+ e.toString());
    }
    finally {
      //关闭Excel文件
      oErrorInfoExcel.closeExcel();
    }
  }

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

  /**查询“管道”表，根据 “管道段编码”字段，得到管孔的起始坐标*/
  private Vector getStartDucts(JLabel jLabel1){
    String strBelongDuctGpCode = null; //所属管道段编码
    String strDuctCode;   //管道编码
    double dblStartSideX; //起始断面坐标X
    double dblStartSideY; //起始断面坐标Y
    Vector vStartSideDucts = new Vector(); //起始管孔数组

    strBelongDuctGpCode = oDuctGpEntity.getDuctGpCode();

    Statement stmt = null;
    ResultSet rset = null;
    try{
      stmt = conn.createStatement();
      //只对管孔进行排序，子孔不需要编号
      strSql = "Select 管道编码,起始断面坐标X,起始断面坐标Y " +
               "From " + strTargetDBName + ".I_管道 " +
               "where 所属管道段编码='" + strBelongDuctGpCode + "' And 所属管道 is null";
      rset = stmt.executeQuery(strSql);
      while (rset.next()) {
        strDuctCode = rset.getString("管道编码");
        dblStartSideX = rset.getDouble("起始断面坐标X");
        dblStartSideY = rset.getDouble("起始断面坐标Y");
        MiddleDuctEntity oMiddleDuctEntity = new MiddleDuctEntity(strDuctCode,
                                                                   dblStartSideX,
                                                                   dblStartSideY);
        vStartSideDucts.addElement(oMiddleDuctEntity);
      }
      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 vStartSideDucts;
  }

  /**排序起始断面管孔*/
  private void sortStartSideDuctNo(JLabel jLabel1){
    Vector vStartSideDucts = null;
    //获取起始断面管孔
    vStartSideDucts = getStartDucts(jLabel1);
    //冒泡排序
    vStartSideDucts = sortSideDuctNo(vStartSideDucts);
    //保存排序后的管孔信息到 管道表
    updateMiddelDuct(vStartSideDucts,jLabel1);
  }
  /**冒泡排序算法*/
  private Vector sortSideDuctNo(Vector vDucts){
    //以Y轴为主,所以X坐标乘0.1,Y和X增加权重后,保存在 Z轴 中
    //从左到右,从下到上
    // tempPoint.Z = tempPoint.x * dblScale + tempPoint.y
    MiddleDuctEntity oMiddleDuctEntity = null;
    MiddleDuctEntity oMDEntity_i = null;
    MiddleDuctEntity oMDEntity_j = null;

    int i,j; //计数器
    int count; //管孔数
    double dblStartSideX;   //起始断面坐标X
    double dblStartSideY;   //起始断面坐标Y
    double dblStartSideZ; //Y和X增加权重
    double dblScale = 0.01;   //权重，可以保证(100×100)的管孔以内是正确的

     count = vDucts.size();
     //从左到右，从上到下，第四象限
    //以Y轴为主,所以X坐标乘0.1,Y和X增加权重后,保存在 Temp轴 中
    for (i=0;i<count;i++){
      oMiddleDuctEntity = (MiddleDuctEntity)vDucts.elementAt(i);
      dblStartSideX = oMiddleDuctEntity.getStartSideX();
      dblStartSideY = oMiddleDuctEntity.getStartSideY();
      dblStartSideZ = dblStartSideX*dblScale + dblStartSideY;
      oMiddleDuctEntity.setStartSideTemp(dblStartSideZ);
    }//end of for
    //对Temp轴进行 冒泡排序
    for (i=0;i<count;i++){
      for (j=i+1;j<count;j++){
        oMDEntity_i = (MiddleDuctEntity)vDucts.elementAt(i);
        oMDEntity_j = (MiddleDuctEntity)vDucts.elementAt(j);
//测试fs
//System.out.println(oMDEntity_i.getStartSideZ() + "==" + oMDEntity_j.getStartSideZ());
        if (oMDEntity_i.getStartSideZ()>oMDEntity_j.getStartSideZ()){
          //交换Vector
          vDucts.remove(i);
          vDucts.insertElementAt(oMDEntity_j,i);
          vDucts.remove(j);
          if (j<(count-1)){
            vDucts.insertElementAt(oMDEntity_i,j);
          }else {
              vDucts.add(oMDEntity_i);
          }
        }//end of if
      }//end of for j
    }//end of for i
    return vDucts;
  }
  /**保存排序后的管孔信息到 管道表*/
  private void  updateMiddelDuct (Vector vDucts,JLabel jLabel1){
    //起始序号 等于 终止序号
    int i;
    int intDuctNo;      //管孔序号
    String strDuctCode; //管道编码
    double  dblStartSideX; //起始断面坐标X
    double  dblStartSideY; //起始断面坐标Y
    MiddleDuctEntity oMDEntity = null;

    Statement stmt = null;
    ResultSet rset = null;
    try{
      stmt = conn.createStatement();
      for (i=0;i<vDucts.size();i++){
        oMDEntity = (MiddleDuctEntity)vDucts.elementAt(i);
        strDuctCode = oMDEntity.getDuctCode();
        intDuctNo = i+1;
        strSql = "Update " + strTargetDBName + ".I_管道 " +
                 "Set 起始序号='" + intDuctNo + "'," +
                 "终止序号='" + intDuctNo + "' " +
                 "Where 管道编码='" + strDuctCode + "'";
        //测试
        jLabel1.setText("生成管道序号："+strSql);
        stmt.execute(strSql);
      }
      stmt.close();
      stmt = null;
    }
    catch(SQLException e) {
      System.out.println("SQL error: " + e.toString());
      oErrorInfoExcel.writeExcelRow(e.toString(),strSql);
    }
    finally {
      if (stmt != null)
        try {stmt.close();} catch(SQLException ignore) {}
     }
  }
}