ducttaken.java

java 读写EXCEL文件的源码

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

/**
 * <p>Title: 管孔占用类</p>
 * <p>Description: 根据新生成的“管道表”，查询PCDM的“管孔占用表”，<br>
 *                 把缆线占用信息填入中间表的“管道占用表”</p>
 */
import java.sql.*;
import java.lang.*;
import java.util.Vector;
import javax.swing.*;

public class DuctTaken {
  private Connection conn = null;
  private DuctGpEntity oDuctGpEntity = null;

  private ErrorInfoExcel oErrorInfoExcel = null;

  private String strSql = null;
  private int intCount=0;

  private String strMHCode;     //人手井编码
  private String strDuctCode;   //管孔编号
  private String strSubDuctCode;//子孔编号
  private String strCableType;  //占孔设施类型
  private String strCableCode;  //缆线简码
  private String strCableFullCode;//缆线编码
  private String strCableNo;    //缆线段序号
  private String strCableStartNo;//起始序组
  private String strCableEndNo;  //终止序组
  private String strsubCableCode;
  private String strSourceDBName = null;
  private String strTargetDBName = null;

  public DuctTaken() {
  }

  /**获取Conn连接*/
  public DuctTaken
  (
   Connection conn,
   String strInSourceDBName,
   String strInTargetDBName,
   JLabel jLabel1,
   JProgressBar jProgressBar1,
   JLabel jLabelP,
   int    rownum

   )
   {
    this.conn = conn;
    this.strSourceDBName = strInSourceDBName;
    this.strTargetDBName = strInTargetDBName;

    //实例化 写错误信息到Excel文件类
    oErrorInfoExcel = new ErrorInfoExcel();
    oErrorInfoExcel.createExcel("管孔占用","管孔占用");
    //写Title
    Vector vResult = new Vector();
    vResult.add("MSLINK");
    vResult.add("人手井编码");
    vResult.add("管孔编号");
    vResult.add("占孔设施类型");
    vResult.add("缆线简码");
    vResult.add("缆线编码");
    vResult.add("缆线段序号");
    vResult.add("子孔编号");
    vResult.add("子孔内缆线编码");
    vResult.add("起始序组");
    vResult.add("终止序组");
    vResult.add("数据录入人");
    vResult.add("录入日期");
    oErrorInfoExcel.writeExcelRow("",vResult);

Debug.print("========管孔占用开始=======");
    Vector vDuctTakens = new Vector();
    //获取管孔占用数组
    vDuctTakens = getDuctTakens();
    //把管孔占用信息填入“管道占用表”
    setDuctTakens(vDuctTakens,jLabel1,jProgressBar1,jLabelP,rownum);

    //关闭Excel文件

   oErrorInfoExcel.closeExcel();

  }

  /**获取管孔占用数组*/
  private Vector getDuctTakens(){
     Vector vDuctTakens = new Vector();
     Statement stmt = null;
     ResultSet rset = null;
     try{
       stmt = conn.createStatement();
       strSql = "Select 人手井编码,管孔编号,子孔编号," +
                "占孔设施类型,缆线简码,缆线编码,缆线段序号,子孔内缆线编码,起始序组,终止序组 " +
                "From " + strSourceDBName + ".GKZY_T ";
       //测试
Debug.print(strSql);

       rset = stmt.executeQuery(strSql);
       while (rset.next()) {
         strMHCode = rset.getString("人手井编码");
         strDuctCode = rset.getString("管孔编号");
         strSubDuctCode = rset.getString("子孔编号");

         strCableType = rset.getString("占孔设施类型");
         strCableCode = rset.getString("缆线简码");
         strCableFullCode = rset.getString("缆线编码");
         strCableNo = rset.getString("缆线段序号");
         strsubCableCode=rset.getString("子孔内缆线编码");
         strCableStartNo = rset.getString("起始序组");
         strCableEndNo = rset.getString("终止序组");

         DuctTakenEntity oDuctTakenEntity = new DuctTakenEntity(strMHCode, strDuctCode,
             strSubDuctCode, strCableType,
             strCableCode, strCableFullCode,
             strCableNo, strCableStartNo,
             strCableEndNo);
         vDuctTakens.addElement(oDuctTakenEntity);
       }
       rset.close();
       rset = null;
       stmt.close();
       stmt = null;
     }
     catch(SQLException e) {
       Debug.print("SQL error: " + e.toString());
     }
     finally {
       if (rset != null)
         try {rset.close();} catch(SQLException ignore) {}
       if (stmt != null)
         try {stmt.close();} catch(SQLException ignore) {}
     }
    return vDuctTakens;
  }

  /**把管孔占用信息填入“管道占用表”*/
  private void setDuctTakens(Vector vDuctTakens,JLabel jLabel1,JProgressBar jProgressBar1,JLabel jLabelP,int  rownum)
  {
    int i;
    String strRealDuctCode = "";    //最终的管道编号
    Statement stmt = null;
     try{
       stmt = conn.createStatement();
       for(i=0;i<vDuctTakens.size();i++){
         try {
           DuctTakenEntity oDuctTakenEntity = (DuctTakenEntity)vDuctTakens.elementAt(i);
           strMHCode = oDuctTakenEntity.getMHCode();
           strDuctCode = oDuctTakenEntity.getDuctCode();
           strSubDuctCode = oDuctTakenEntity.getSubDuctCode();
           strCableType = oDuctTakenEntity.getCableType();
           strCableCode = oDuctTakenEntity.getCableCode();
           strCableFullCode = oDuctTakenEntity.getCableFullCode();
           strCableNo = oDuctTakenEntity.getCableNo();
           strCableStartNo = oDuctTakenEntity.getCableStartNo();
           strCableEndNo = oDuctTakenEntity.getCableEndNo();

           //根据是否有子管编号，判断管孔和子管的占用情况，获取最终的管道编号
           strRealDuctCode = getRealDuctCode(strMHCode,strDuctCode,
               strSubDuctCode);

           strSql ="Insert Into " + strTargetDBName + ".I_管道占用(管道编码," +
                   "占用设施类型,缆线简码,缆线编码,"+
                   "缆线段序号,起始序组,终止序组,数据录入人,录入日期) " +
                   "Values('" + strRealDuctCode +"','" +
                   strCableType + "','" + strCableCode + "','" + strCableFullCode + "','" +
                   strCableNo + "','" + strCableStartNo + "','" + strCableEndNo + "','','')";
           //测试
           //System.out.println(strSql);
            if(jLabel1!=null)
                  jLabel1.setText("生成管道占用记录："+strRealDuctCode+","+strCableType+","+strCableCode+","+strCableFullCode);
            else
               {
                  jProgressBar1.setValue(jProgressBar1.getValue()+1) ;
                  jLabelP.setText(jProgressBar1.getValue()*100/(2*rownum)+"%") ;
                 }

           stmt.execute(strSql);
Debug.print(i);
         }catch(SQLException e1){
           //System.out.println("SQL error: " + e1.toString() + "==" + strSql);
           Vector vResult = new Vector();
           intCount = intCount + 1;
           vResult.add(Integer.toString(intCount));
           vResult.add(strMHCode);
           vResult.add(strDuctCode);
           vResult.add(strCableType);
           vResult.add(strCableCode);
           vResult.add(strCableFullCode);
           vResult.add(strCableNo);
           vResult.add(strSubDuctCode);
           vResult.add(strsubCableCode);
           vResult.add(strCableStartNo);
           vResult.add(strCableEndNo);
           vResult.add("");
           vResult.add("");

           oErrorInfoExcel.writeExcelRow(e1.toString(),vResult);

//关闭Excel文件
//oErrorInfoExcel.closeExcel();
         }
       }//end of for
       stmt.close();
       stmt = null;
     }
     catch(SQLException e) {
       System.out.println("SQL error: " + e.toString() + "===" + strSql);
       //oErrorInfoExcel.writeExcelRow(e.toString(),strSql);
     }
     finally {
       if (stmt != null)
         try {stmt.close();} catch(SQLException ignore) {}
     }
  }
  /**获取 最终的管道编号*/
  private String getRealDuctCode(String strMHCodeIn,
                                 String strDuctCodeIn,
                                 String strSubDuctCodeIn){
    String strRealDuctCodeIn = "";
    //拆分管孔标号  (B7) -> (B/7)
    strDuctCodeIn = strDuctCodeIn.substring(0,1) + "/" +
                      strDuctCodeIn.substring(1,strDuctCodeIn.length());
    //根据是否有子管编号，判断管孔和子管的占用情况
    if(strSubDuctCodeIn == null){
      //若子孔编号为空，则是管孔占用
      //合并 人井和管孔
      strRealDuctCodeIn = strMHCodeIn + "/" + strDuctCodeIn;
    }else{
      //若子孔编号不为空，则是子孔占用
      strRealDuctCodeIn = strMHCodeIn + "/" + strDuctCodeIn +
                          "/" + strSubDuctCodeIn;
    }

    //测试
    //System.out.println(strSubDuctCodeIn + strRealDuctCodeIn);
    return strRealDuctCodeIn;
  }
}