change.java

java 读写EXCEL文件的源码

/********************************************************************
 *
 * $RCSfile: Change.java,v $  $Revision: 1.1 $  $Date: 2003/09/22 08:06:23 $
 *
 * $Log: Change.java,v $
 * Revision 1.1  2003/09/22 08:06:23  icestone
 * init
 *
 *
 *
 **********************************************************************/
package pcdmupgradedata;
/**
功能：    管孔/子孔坐标转换
       从PCDM2.2转到以人井面的左下角为原点的坐标系
*/
import java.sql.*;
import java.util.*;
import javax.swing.*;

public class 	Change{
  private static  String strSourceDBName;
  private static  String strSql;
//  private static  DuctEntity oDuctEntity = null;  //管孔实体类

  private static void  changeCoordinate(Connection conn,String ductTable,
                                     long   mslink,int  entity){
  int i;
  DPoint ductPos[] =null;
  String ductMslinkArray[]=null;

  try{
    int pointCount = 0;
    Statement statement = conn.createStatement();
    ResultSet result;

    //选择内点的个数
    String queryCount = new String("select count(*)+1 from " +
                                   strSourceDBName + ".deviceframe " +
                                   "where DEVICEMSLINK = " + mslink+
                                   " and DEVICEENTITY = " + entity);
    //System.out.println(queryCount);

    result=statement.executeQuery (queryCount);
    while(result.next ()){
      pointCount = result.getInt(1);
    }
    result.close();
//System.out.println(pointCount);
    if(pointCount < 2)
      statement.close();

    //初始化构造内点数组
    DPoint[] framePoint = new DPoint[pointCount];
    for(i = 0 ; i < pointCount ; i++)
      framePoint[i] = new DPoint();

    //pointCount-1为边数
    //内点用逆时针方向由(0->(pointCount-1)),而面号则顺时针方向(A->E)
    //与边的对应关系为：(1-0)->A，(5-4)->B，(4-3)->C,(3-2)->D,(2-1)->E
    //算法：简化面号(A为0，B为1，C为2，D为3,E为4),边总数为5
    //		对应关系为： ((边总数-side号)%边总数+1,(边总数-side号)%边总数)->side号

    String query = new String("select X,Y from " + strSourceDBName + ".deviceframe where DEVICEMSLINK = " +
                              mslink+ " and DEVICEENTITY = " + entity+ " order by serial");

    //System.out.println(query);

    result=statement.executeQuery (query);

    i = 0;
    while(result.next ()){
      framePoint[i].x = result.getDouble("X");
      framePoint[i++].y = result.getDouble("Y");
    }

    framePoint[i] = framePoint[0];
    result.close();

    int len = pointCount - 1;
    //System.out.println("len::"+len);

    int ductNum;

    for(i = 0; i < len;i++){
      ductNum=0;

      //得到面上的点数
      query = "select count(*) from "+ductTable+" where b_mslink="+mslink+" and b_entity="+entity+
              " and 所属井面号='"+(char)(65+i)+"'";
      //System.out.println(query);
      result=statement.executeQuery (query);
      if(result.next()){
        ductNum = result.getInt(1);
        result.close();

        if(ductNum > 0){
          ductPos = new DPoint[ductNum];
          ductMslinkArray = new String[ductNum];

          int k=0;
          query = "select mslink,x,y from  "+ductTable+"  where b_mslink="+mslink+" and b_entity="+entity+
                  " and 所属井面号='"+(char)(65+i)+"'";
          //System.out.println(query);
          result=statement.executeQuery (query);
          while(result.next ()){
            ductMslinkArray[k]=result.getString(1);
            ductPos[k++] =new DPoint(result.getDouble(2), result.getDouble(3) );
          }

          int orgIndex = (len-i)%len;
          translateIntoNewCoordinate(conn,ductMslinkArray,ductPos,framePoint[orgIndex+1],framePoint[orgIndex],ductNum);

          //更新坐标
          for(k=0;k<ductNum;k++){
            query = "update  "+ductTable+"  set x = "+ductPos[k].x+",y="+ductPos[k].y+" where mslink = "+ductMslinkArray[k];
            //System.out.println(query);
            statement.executeUpdate(query);
          }
        }
      }
    }
    //System.out.println("len::"+len);
    statement.close();
  }catch(java.sql.SQLException e){
    //System.out.println("changeCoordinate " + e.toString());
 }
}

private static void  translateIntoNewCoordinate(Connection conn,String ductMslinkArray[],
                                                DPoint ductPos[],DPoint org,
                                                DPoint sec,int pointNum){
  double sinaa, cosaa, tempx, tempy,tempdist;
  DPoint tempdpoint = new DPoint(0.0,0.0);
  String ductMslink = new String("");
  int i,j,k,numi,numj;
  int flag =0;

  //计算新的坐标系的旋转角度的正弦和余弦值
  //System.out.println("rotate org:" + org);
  //System.out.println("rotate sec:" + sec);

  tempx = ( sec.x - org.x ) * (sec.x - org.x ) ;
  tempy = ( sec.y - org.y ) * (sec.y - org.y ) ;
  tempdist = java.lang.Math.sqrt(tempx + tempy);
  sinaa = (sec.y - org.y)/tempdist;
  cosaa = (sec.x - org.x )/tempdist;

  //System.out.println("rotate sina:" + sinaa);
  //System.out.println("rotate cosaa:" + cosaa);

  for(i = 0 ; i < pointNum ; i++){
    //System.out.print(ductMslinkArray[i]+":"+ductPos[i]);

    //得到新的系统坐标值
    tempx = (ductPos[i].x - org.x ) *cosaa  + (ductPos[i].y - org.y) * sinaa;
    tempy = -(ductPos[i].x - org.x)*sinaa + (ductPos[i].y - org.y) * cosaa;

    ductPos[i].x = tempx;
    //保证纵坐标值大于零
    flag = (tempy>0)?1:-1;
    ductPos[i].y = flag*tempy;
    //System.out.println(":new:"+ductPos[i]);
  }
}
/**转换主函数*/
public static void  translateAll(Connection conn,String ductTable,
                                 String subDuctTable,String strInSourceDBName,JLabel jLabel1){

  String strSql = null;
  String strDuctGPCode = null;  //管孔编码
  String strMHCode  = null;  //所属设施编码
  int i=0;     //计数器

  //添加 所属用户 如：SA.ductTable
  strSourceDBName = strInSourceDBName;
  ductTable = strSourceDBName + "." + ductTable;
  subDuctTable = strSourceDBName + "." + subDuctTable;
Debug.print("=======转换管孔坐标Change开始=======");
  jLabel1.setText("转换管孔坐标开始");

  try{
    Statement st = conn.createStatement();
    ResultSet res = null;

    //清除管孔错误数据 （管孔编码 is null）
   
    strSql = "delete " + strSourceDBName + ".GK_T Where 管孔编码 is null";
    st.executeUpdate(strSql);
    //清除管孔错误数据 （所属管道段编码 = 'null'）
    strSql = "delete " + strSourceDBName + ".GK_T Where 所属管道段编码 = 'null'";
    st.executeUpdate(strSql);
   
//   jLabel1.setText("删除管孔记录");

    //把子管坐标转换成相对于管孔的坐标
    strSql = "update " + subDuctTable + " a set a.x=(select a.x-b.x from  "+
                    ductTable+"  b where a.b_dmslink=b.mslink and rownum = 1),"+
                    "a.y=(select a.y-b.y from  "+ductTable+"  b where a.b_dmslink=b.mslink and rownum = 1)";
    st.executeUpdate(strSql);
     jLabel1.setText("正在转换子管相对坐标："+strSql);
    //根据 管孔表 的 所属管道mslink,entity进行转换
    strSql = " select distinct b_mslink,b_entity from "+ductTable+" order by b_mslink,b_entity ";
    res = st.executeQuery(strSql);
    long b_mslink = 0L;
    int b_entity = 0;
    while (res.next()){
      b_mslink = res.getLong(1);
      b_entity = res.getInt(2);
      //转换管孔坐标
      changeCoordinate(conn,ductTable,b_mslink,b_entity);
      jLabel1.setText("正在转换管孔所属关系："+i);

     i = i + 1;
    }

    //偏移管孔圆心
    strSql = "update " + ductTable + " set x=x*90/0.75,y=y*90/0.75";
    st.executeUpdate(strSql);
    //偏移子管圆心
    strSql = "update " + subDuctTable + " set x=x*90/0.75,y=y*90/0.75";
    st.executeUpdate(strSql);
    //管孔的管径初始化为 9
    strSql = "update " + ductTable + " set 管径=90";
    st.executeUpdate(strSql);

/*
    //转换前，清理数据库
    delete i_pcdm.I_管道占用;
    commit;
    delete i_pcdm.I_管道;
    commit;
    delete sa.管孔;
    insert into sa.管孔 select * from sa.管孔_BAK;
    commit;
    delete sa.子管;
    insert into sa.子管 select * from sa.子管_BAK;
    COMMIT;
    */
    /*
    临时测试
    delete TEST.I_管道占用;
    commit;
    delete TEST.I_管道;
    commit;
    delete TEST_SA.管孔;
    insert into TEST_SA.管孔 select * from TEST_SA.管孔_BAK;
    commit;
    delete TEST_SA.子管;
    insert into TEST_SA.子管 select * from TEST_SA.子管_BAK;
    COMMIT;
    */
    res.close();
    st.close();
  }catch(java.sql.SQLException e){
    Debug.print("Change:" + e.toString());
    Debug.print(strSql);
  }
}
}