exceltodb.java

java 读写EXCEL文件的源码

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

import javax.swing.JOptionPane;
import java.sql.*;
import java.io.File;
//import java.util.Hashtable;
import java.util.Vector;
//import java.util.Calendar;
import java.util.Date;
import java.awt.Frame;
import javax.swing.*;

import jxl.*;
import jxl.write.*;


public class ExceltoDB implements Runnable{

  /**将EXCEL文件中的设施信息导入到数据库
   * 入参：
   * Connection conn,//数据库连接
   * File filePath,//文件目录
   * String devCname//设施名称
   *
   * 返回值：
   * 无
   * */
  DateTime datecell = null;
  Vector pos=new Vector();
  DateFormat customDateFormat = new DateFormat ("yyyy-mm-dd");
  WritableCellFormat dateFormat = new WritableCellFormat (customDateFormat);

  JFrame frame = null;
  JProgressBar  progressbar = null;
  JLabel jLabelPercent = null;
  JButton jb9;
  Connection conn = null;
  File filePath = null;
  String devCname = null;
  String Ename = null;
  String Cname = null;
  String zcol = null;
  String areaCode = null;


  public ExceltoDB(JFrame frame,Connection conn,File filePath,String devCname,
                   JProgressBar  progressbar,JLabel jLabelPercent,
                   String Ename,String Cname,String zcol,String areaCode,JButton jb9){
    this.frame = frame;
    this.conn = conn;
    this.filePath = filePath;
    this.devCname = devCname;
    this.progressbar  = progressbar;
    this.jLabelPercent = jLabelPercent;
    this.Ename = Ename;
    this.Cname = Cname;
    this.zcol  = zcol;
    this.areaCode  = areaCode;
    this.jb9=jb9;
 Debug.print("---------------come in!----------------");
  }

  public void ImpDB(){

    /**屏幕中心位置显示*/
    frame.setLocation((BaseGui.screenSize.width - BaseGui.frameSize.width) / 2,
                            (BaseGui.screenSize.height - BaseGui.frameSize.height) / 2);
    progressbar.setSize(400,30) ;
    frame.setTitle("导入<"+Cname+">") ;
    frame.pack() ;
    frame.show() ;

    /**EXCEL设施文件*/
    Workbook workbook_read = null;

    /**设置日志计数值*/
    int failNum = 0;

    try{
      /**打开EXCEL设施文件进行读取操作*/
      workbook_read = Workbook.getWorkbook(filePath) ;
      /**得到文件抬头*/
      Vector vecTitle = getLogTitle(workbook_read,frame);
      /**读入EXCEL文件中所有的行信息集合
       * 记录的变量包括了(String recordRows)*/
      Vector vecExcelRows = getExcelRowsVec(workbook_read,vecTitle);
      workbook_read.close() ;

      /**获取EXCEL文件的行数*/
      int vecExcelRowsNum = vecExcelRows.size() ;
      int excelRowsCount=0;

      /**设置进度条的值*/
      progressbar.setMaximum(2*vecExcelRowsNum) ;
      progressbar.setMinimum(0) ;

      /**用以记录数据库插入时返回信息的变量*/
      String writeMessage = null;
      String insert_sql = null;
      int logPage = -1;
      int logRecCount = 0;


        /**insert 字段信息*/
        String insertFieldnameSting= "insert into "+devCname+" ("+StrFieldname(vecTitle)+")Values " ;

        /**处理每一行设施信息*/
        while(excelRowsCount < vecExcelRowsNum){
        progressbar.setValue(progressbar.getValue()+1) ;
        jLabelPercent.setText(progressbar.getValue()*100/(2*vecExcelRowsNum)+"%") ;

        /**得到部分的插入语句*/
        insert_sql += "(";
        logPage = failNum/20000;
        logRecCount = failNum%20000;
        /**读取一行EXCEL文件记录*/
        String str_excel_row = new String(insertFieldnameSting+vecExcelRows.elementAt(excelRowsCount).toString());
         insertoDB(str_excel_row);
//         insertoDB("COMMIT");
         excelRowsCount++;
        }
        /*文件删除方法增加 2003.6.1*/
         filePath.delete();

         /**直到所有设施行已经处理完结，则完成操作。*/
        String returnRes = new String("");

        frame.setCursor(new java.awt.Cursor(java.awt.Cursor.WAIT_CURSOR));
        if(Cname.equals("管孔占用"))
        {
             //最后实例化管孔占用类，把“管孔占用表”转换到“管道占用表”
             DuctTaken oDuctTaken = new DuctTaken(conn,"SA","I_PCDM",null,progressbar,jLabelPercent,vecExcelRowsNum);   	
         }
        else{
            /*转换2.2结构中间表*/
            new UpdateT(conn,devCname,Ename,Cname,areaCode,progressbar,null,jLabelPercent,zcol,vecExcelRowsNum);
            new Update2(conn,devCname,Ename,Cname,areaCode,progressbar,null,jLabelPercent,zcol,vecExcelRowsNum);
         }
        JOptionPane.showMessageDialog(frame,"完成导入！无法导入的错误记录在:\n"+Const.BasePath
                                                                                 +"\\doc\\无法对应excel\\\n导入记录数在:\n"+Const.BasePath
                                                                                 +"\\doc\\数据转换统计记录\\","信息",JOptionPane.INFORMATION_MESSAGE);
        frame.setCursor(new java.awt.Cursor(java.awt.Cursor.DEFAULT_CURSOR));
        jLabelPercent.setText(progressbar.getValue()*100/vecExcelRowsNum+"%") ;

        jb9.setEnabled(true);

        frame.dispose() ;

    }catch(java.io.IOException  e){
      JOptionPane.showMessageDialog(frame,"创建日志文件出错,请确认该文件处于关闭状态或未受损坏","信息",JOptionPane.INFORMATION_MESSAGE);
      jb9.setEnabled(true);
    }catch(jxl.read.biff.BiffException  e1){

      JOptionPane.showMessageDialog(frame,"jxl包 读文件出错","信息",JOptionPane.INFORMATION_MESSAGE);
      jb9.setEnabled(true);

    }
  }

  private String  StrFieldname(Vector vecs){
  	String s="";
  	for(int i=0;i<vecs.size();i++)
  	 {
  	    if(i!=0)
  	      s+=","+vecs.elementAt(i).toString();
  	    else
  	      s+=vecs.elementAt(i).toString();
  	 }//end for
  	return s;

  	}


  /**得到日志文件的抬头*/
  public  Vector getLogTitle(Workbook workbook,JFrame frame){
    Vector vec = new Vector();
    Sheet sheet = null;
    sheet = workbook.getSheet(0) ;
    Cell cell = null;
    String str =null;
    int cellcol = sheet.getColumns() ;
    for(int i=0;i<cellcol;i++){
      cell = sheet.getCell(i,0) ;
      str = cell.getContents().trim() ;
      if(str=="")break;
      if(str.equals("安装时间")||str.equals("竣工日期")||str.equals("开通日期")
                           ||str.equals("录入日期")||str.equals("启用时间")||str.equals("申请日期")
                                                     ||str.equals("数据采集日期")||str.equals("完工日期"))
          {
           pos.addElement(String.valueOf(i));
          }
      vec.addElement(str) ;
    }
    return vec;
  }

  /**得到EXCEL文件的所有的行记录*/
 private Vector getExcelRowsVec(Workbook workbook,Vector vecTitle){
    Vector vecExcelRows = new Vector();
    int sheetnum = workbook.getNumberOfSheets() ;
    int cellrow= 0,cellcol=0;
    Sheet sheet = null;
    Cell cell = null;
    String str = new String("");
    DateCell dc = null;
    Date tempDate = null;
    for (int i=0;i<sheetnum;i++){
      sheet = workbook.getSheet(i) ;
      cellcol = vecTitle.size() ;

      cellrow = sheet.getRows();
      /**跳过抬头*/
      for(int j= 1;j<cellrow;j++){
        str = "";
        /**跳过ID字段*/

        for(int  k=0;k<cellcol;k++){
           cell = sheet.getCell(k,j);

             if(compareV(k)==true)
              str += "to_date(SUBSTR('"+cell.getContents().toUpperCase()+"',1,INSTR('"
                                          +cell.getContents().toUpperCase()+"','.')-1),'YYYY-MM-DDhh24:mi:ss')"+(char)(int)(',');
             else
              str += "'"+cell.getContents().toUpperCase()+"'"+(char)(int)(',');
        }

        str = "("+str.substring(0,str.length() -1)+")" ;
        vecExcelRows.addElement(str) ;
      }
    }
    if(sheet!=null)
      sheet = null;
    return vecExcelRows;
  }

  public void run(){
    ImpDB();
  }
  private boolean compareV(int v){
       boolean rv=false;
         for(int i=0;i<pos.size();i++){
       	   if(String.valueOf(v).equals((String)pos.elementAt(i)))
       	      {
       	        return true;
       	      }
       	  }
       return rv;
  }
  void insertoDB(String sql)
  {
              try{
                     Statement  InsertStatement = conn.createStatement();
                      try
                         {
                           if(sql!=null)
                            InsertStatement.executeUpdate(sql);

	                  }
                          catch(java.sql.SQLException e){
                          	System.out.println(e.toString());
      	                  }
                  finally{
                          InsertStatement.close();
                    }
	          }
              catch(java.sql.SQLException e){
      	     }
  }

}