dataimport.java

java 读写EXCEL文件的源码

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

import java.awt.*;
import java.awt.event.*;
import com.borland.jbcl.layout.*;
import javax.swing.*;
import javax.swing.border.*;
import java.sql.*;
import java.io.*;
import java.net.*;
import java.util.*;
import jxl.*;
import jxl.write.*;

public class DataImport extends JFrame {
  Connection conn=null;

  Frame frame=null;
  String title = null;
  boolean modal;
  private JPanel jPanelProgressBar = new JPanel();
  private JProgressBar progressbar = new JProgressBar(JProgressBar.HORIZONTAL,1,1500);
  private JLabel jLabelPercent = new JLabel();
  private BorderLayout borderLayout1 = new BorderLayout();
  private BorderLayout borderLayout2 = new BorderLayout();
  private JLabel jLabelProcess = new JLabel();
  private JButton jb9;
  String areaCode;

  public DataImport(Frame frame,String title,boolean modal,Connection conn,String areaCode,JButton jb9){
    /**接受输入的数据库连接字，与传入的结点值*/
    this.conn =conn;
    this.frame = frame;
    this.title = title;
    this.modal = modal;
    this.areaCode = areaCode;
    this.jb9=jb9;


    /**判断传入参数的合法性*/
    try{
      if(conn==null){
        JOptionPane.showMessageDialog(null,"传送的数据库连接有错,数据库连接为null","信息",JOptionPane.INFORMATION_MESSAGE);
        jb9.setEnabled(true);

        return;
      }
    }catch(Exception e){
          e.printStackTrace();
    }
    jbInit();
    getInputFile();
  }

  void jbInit(){
    this.setIconImage(Toolkit.getDefaultToolkit().getImage(System.getProperty("user.dir")+"//images//logo.gif"));
    this.getContentPane().setLayout(borderLayout1);
    jLabelProcess.setText("进度");
    jPanelProgressBar.setLayout(borderLayout2);
    jPanelProgressBar.add(jLabelProcess,  BorderLayout.WEST);
    jPanelProgressBar.add(progressbar,  BorderLayout.CENTER);
    jPanelProgressBar.add(jLabelPercent,  BorderLayout.EAST);
    this.getContentPane().add(jPanelProgressBar, BorderLayout.CENTER);

  }

  /**得到需要导出的目录位置，如果未进行选择则返回null值*/
  void getInputFile(){
    JFileChooser chooser=new JFileChooser(System.getProperty("user.dir")+"\\doc\\无法对应excel");

    /**设置只能选择'.xls'结尾的Excel文件*/
    ExampleFileFilter filter = new ExampleFileFilter();
    filter.addExtension("xls");
    filter.setDescription("Excel 文件");
    chooser.setAcceptAllFileFilterUsed(false) ;
    chooser.setFileFilter(filter);
    chooser.setFileSelectionMode(JFileChooser.FILES_ONLY);

    int state = chooser.showDialog(this.frame ,"数据导入(请选择Excel文件)") ;
    if(state == JFileChooser.APPROVE_OPTION){

      /**得到文件位置*/
      File filePath=chooser.getSelectedFile().getAbsoluteFile();
      /**得到文件名称*/
      String devCname = filePath.getName();
      /**得到设施名称*/
      devCname = devCname.substring(0,devCname.indexOf((int)('.'),0) ) ;

      String devEname = DevName.getEname(conn,devCname) ;
      if(devCname.equals("管孔占用"))  
         {
          
           deleteGKZY();
           Thread p = new Thread(new ExceltoDB(this,conn,filePath,devCname,this.progressbar ,
                                                    this.jLabelPercent,devEname,devCname,"0",areaCode,jb9));
           p.start() ;
        
         }    
        /*删除中间表记录*/
        /*zTableName--中间表名*/
        /*zcol--剔除的临时的列数*/
      else{
           String zTableName=deleteMidRecord(devCname,0);
           String zcol=deleteMidRecord(devCname,1);

        /*读excel加入中间表记录*/
        
           Thread p = new Thread(new ExceltoDB(this,conn,filePath,zTableName,this.progressbar ,
                                                    this.jLabelPercent,devEname,devCname,zcol,areaCode,jb9));
           p.start() ;
        }
 
    }//end if
     else
       jb9.setEnabled(true);

  }//end getInputFile
   private void  deleteGKZY(){
     try{
         Statement deleteTableStatement = conn.createStatement();
         String dsql = "delete from 管孔占用";
 	 deleteTableStatement.executeUpdate(dsql);
         deleteTableStatement.close();
         dsql=null;
        }
     catch (java.sql.SQLException se)
       {
              JOptionPane.showConfirmDialog(this, "管孔占用数据不存在！\n"+se,
                                                          "错误",JOptionPane.OK_OPTION, JOptionPane.INFORMATION_MESSAGE);
              System.exit(0);
       }
   }//END deleteGKZY

   private String  deleteMidRecord(String Tcname,int f){
   	 String mtable=null;
         String Query=null;
     try{
         if(Tcname.equals("管孔")||Tcname.equals("子管"))
          {
              JOptionPane.showConfirmDialog(this, "管孔数据无法导入！\n",
                                                          "提示",JOptionPane.OK_OPTION, JOptionPane.INFORMATION_MESSAGE);
              System.exit(0);
          }

         if(f==0)
           Query = "select TABLENAME from zjdy_t where ctablename='"+Tcname+"'";
         else
           Query = "select ACOUNT from zjdy_t where ctablename='"+Tcname+"'";

         Statement QueryTableStatement = conn.createStatement();
         Statement deleteTableStatement = conn.createStatement();
         ResultSet TableNameResult=QueryTableStatement.executeQuery(Query);
	 while(TableNameResult.next())
           {
	    mtable=TableNameResult.getString(1);
	   }//end while
          if(f==0)
          {
           String dsql = "delete from "+mtable;
 	   deleteTableStatement.executeUpdate(dsql);
           deleteTableStatement.close();
           dsql=null;
          }
          QueryTableStatement.close();
          TableNameResult.close();
          Query=null;

         }
   	catch (java.sql.SQLException se)
        {
              JOptionPane.showMessageDialog(this, "数据表不存在！\n"+se,
                                                          "错误",JOptionPane.INFORMATION_MESSAGE);
              System.exit(0);
         }
   	return mtable;
   	}//end deleteMidRecord


  protected void processWindowEvent(WindowEvent e) {
    super.processWindowEvent(e);
    if (e.getID() == WindowEvent.WINDOW_CLOSING) {
         System.exit(0);
    }
  }


}