ductmh.java

java 读写EXCEL文件的源码

  private void  saveDuctCoord(long b_mslink,int b_entity,
                              String strDuctGPCode,String strBelongMHSide,JLabel jLabel1){
    Vector vDucts = new Vector();       //管孔数组
    int intMHSideNum = 0;               //第一端人井面数
    double dblMHSideAngle = 0;          //第一端角度
    Point oMovePoint = new Point(0,0);  //第一端偏移坐标

    int intOtherMHSideNum = 0;               //另一端人井面数
    double dblOtherMHSideAngle = 0;          //另一端角度
    Point oOtherMovePoint = new Point(0,0);  //另一端偏移坐标
    String strOtherBelongMHSide = "A";        //另一端人井面号
    long  Otherb_mslink = 0;                 //另一端b_mslink
    int Otherb_entity   = 0;                 //另一端b_entity

    String strDuctCode;                 //管孔编码
    double dblStartX,dblStartY;         //管孔坐标
    double dblEndX,dblEndY;             //管孔坐标
    boolean bStartSide;                 //管孔是否在起始端
    int i;

    Statement stmt = null;
    ResultSet rset = null;
    try {
      stmt = conn.createStatement();
    //第一端的管孔信息
    //获取 面号
    intMHSideNum = getMHSideNum(stmt,b_mslink,b_entity);
    //获取 面旋转角度
    dblMHSideAngle = getMHSideAngle(intMHSideNum,strBelongMHSide);
    //获取 面平移向量
    oMovePoint = getMovePoint(intMHSideNum,strBelongMHSide);
    //获取 管孔数组
    vDucts = getDuctsByBMslink(b_mslink,b_entity,strDuctGPCode);

      //查询 管孔所属人井信息  另一端
      strSql = "Select distinct b_mslink,b_entity,所属井面号 " +
               " From " + strSourceDBName + ".GK_T" +
               " Where 所属管道段编码='" + strDuctGPCode + "'" +
               " And b_mslink !=" + b_mslink;
//测试
//System.out.println("Other Side: " + strSql);
      rset = stmt.executeQuery(strSql);
      if (rset.next()){
        Otherb_mslink = rset.getLong(1);
        Otherb_entity = rset.getInt(2);
        strOtherBelongMHSide = rset.getString(3);
        strOtherBelongMHSide = strOtherBelongMHSide.trim();
      }
    }catch(SQLException e){
      System.out.println(e.toString());
    }
    finally {
      if (rset != null)
        try {rset.close();} catch(SQLException ignore) {}
    }
    //另一端的管孔信息
    //获取 面号
    intOtherMHSideNum = getMHSideNum(stmt,Otherb_mslink,Otherb_entity);
    //获取 面旋转角度
    dblOtherMHSideAngle = getMHSideAngle(intOtherMHSideNum,strOtherBelongMHSide);
    //获取 面平移向量
    oOtherMovePoint = getMovePoint(intOtherMHSideNum,strOtherBelongMHSide);

   try{
     for(i=0;i<vDucts.size();i++){
        DuctEntity oDuctEntity = (DuctEntity)vDucts.elementAt(i);
        strDuctCode = oDuctEntity.getDuctCode();
        //对起始管孔 进行旋转和平移
        DuctEntity oStartNewDuctEntity = getDuctNewCoord(oDuctEntity,dblMHSideAngle,oMovePoint);
        dblStartX = oStartNewDuctEntity.getX();
        dblStartY = oStartNewDuctEntity.getY();
//对终止管孔 进行旋转和平移   终点管孔 endX = 1000 - startX
        /*需要查找 终止管孔 组所在面号*/
        oDuctEntity.setX(1000-oDuctEntity.getX());

        DuctEntity oEndNewDuctEntity = getDuctNewCoord(oDuctEntity,dblOtherMHSideAngle,oOtherMovePoint);
        dblEndX = oEndNewDuctEntity.getX();
        dblEndY = oEndNewDuctEntity.getY();

        //判断管孔是否在起始端
        bStartSide = IsStartSide(strDuctCode);
        //默认人井面长1000,面宽1000毫米
        if(bStartSide == true){
          //起始端
          strSql = "Update " + strTargetDBName + ".I_管道 Set 起始人井展开图坐标X=" + dblStartX +
                   ",起始人井展开图坐标Y=" + dblStartY +
                   ",终止人井展开图坐标X=" + dblEndX +
                   ",终止人井展开图坐标Y=" + dblEndY +
                 " Where 管道编码='" + strDuctCode + "'";
        }else{
          //终止端
          strSql = "Update " + strTargetDBName + ".I_管道 Set 起始人井展开图坐标X=" + dblEndX +
                   ",起始人井展开图坐标Y=" + dblEndY +
                   ",终止人井展开图坐标X=" + dblStartX +
                   ",终止人井展开图坐标Y=" + dblStartY +
                 " Where 管道编码='" + strDuctCode + "'";
        }//end of if
        jLabel1.setText("转换人手井坐标："+dblStartX+","+dblStartY+","+dblEndX+","+dblEndY);
        stmt.executeUpdate(strSql);
      }//end of for
    }catch(SQLException e){
    //System.out.println(e.toString());
    //System.out.println("Error SQL: " + strSql);
    }
    finally {
      if (stmt != null)
        try {stmt.close();} catch(SQLException ignore) {}
    }
  }

/**判断管孔是否在起始端*/
private boolean  IsStartSide(String strDuctGPCode,long b_mslink,int b_entity){
  boolean bStartSide = false;
  String strDuctCode = "";
  Statement stmt = null;
  ResultSet rset = null;
  try{
    stmt = conn.createStatement();
    strSql = "Select 管孔编码" +
             " From " + strSourceDBName + ".GK_T " +
             " Where 所属管道段编码='" + strDuctGPCode +
             "' And b_mslink=" + b_mslink +
             " And b_entity=" + b_entity +
             " And rownum=1";
//测试
//System.out.println(strSql);
    rset = stmt.executeQuery(strSql);
    //查询是否起始端 若有记录,则是起始端,否则是终止端
    if(rset.next()){
      strDuctCode = rset.getString(1);
      bStartSide = IsStartSide(strDuctCode);
    }
  }catch(SQLException e){
    System.out.println(e.toString());
  }
  finally {
    if (rset != null)
      try {rset.close();} catch(SQLException ignore) {}
    if (stmt != null)
      try {stmt.close();} catch(SQLException ignore) {}
  }
  return bStartSide;
  }
  /**判断管孔是否在起始端*/
  private boolean  IsStartSide(String strDuctCode){
    boolean bStartSide = false;
    Statement stmt = null;
    ResultSet rset = null;
    try{
      stmt = conn.createStatement();
      strSql = "Select a.管孔编码" +
               " From " + strSourceDBName + ".GK_T a," + strSourceDBName + ".管道段 b" +
               " Where a.所属设施编码=b.起始设施编码" +
               " And a.所属管道段编码=b.管道段编码" +
               " And a.管孔编码='" + strDuctCode + "'";
//System.out.println(strSql);
      rset = stmt.executeQuery(strSql);
      //查询是否起始端 若有记录,则是起始端,否则是终止端
      if(rset.next()){
        bStartSide = true;
      }
    }catch(SQLException e){
      System.out.println(e.toString());
    }
    finally {
      if (rset != null)
        try {rset.close();} catch(SQLException ignore) {}
      if (stmt != null)
        try {stmt.close();} catch(SQLException ignore) {}
    }
    return bStartSide;
  }
  /**对一个管孔 进行旋转和平移*/
  private DuctEntity getDuctNewCoord(DuctEntity oDuctEntity,double dblMHSideAngle,
                                     Point oMovePoint){
    DuctEntity oReturnDuctEntity = null;
    String strDuctCode;
    double dblX=0,dblY=0;
    Point startPoint,endPoint;  //旋转的起始点和终止点
    Point newPoint;             //旋转后的点

    strDuctCode = oDuctEntity.getDuctCode();
    dblX = oDuctEntity.getX() ;
    dblY = oDuctEntity.getY();
    //旋转
    startPoint = new Point(0,0);
    endPoint   = new Point(dblX,dblY);
    newPoint = getRotatedPoint(startPoint,endPoint,dblMHSideAngle);

    dblX = newPoint.X;
    dblY = newPoint.Y;
    //平移
    dblX = dblX + oMovePoint.X;
    dblY = dblY + oMovePoint.Y;
    //构造新的 DuctEntity
    oReturnDuctEntity = new DuctEntity(strDuctCode,dblX,dblY);
    return oReturnDuctEntity;
  }
  /**向量的旋转 围绕 startPoint旋转angle角度*/
  private Point getRotatedPoint(Point startPoint,Point endPoint,double angle){
    Point oReturnPoint = new Point(0,0);
    double x_vec,y_vec,cosa,sina;

    x_vec=endPoint.X - startPoint.X;
    y_vec=endPoint.Y - startPoint.Y;

    cosa=java.lang.Math.cos(-angle);
    sina=java.lang.Math.sin(-angle);

    oReturnPoint.X = x_vec*cosa-y_vec*sina;
    oReturnPoint.Y = x_vec*sina+y_vec*cosa;

    oReturnPoint.X += startPoint.X;
    oReturnPoint.Y += startPoint.Y;
    return oReturnPoint;
  }
}