转：用Delphi实现从Excel数据Update(Insert类似)到Oracle数据库表中

 

unit Unit47;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, DB, DBClient, OleServer, Excel2000, Buttons;

type
  TForm47 = class(TForm)
    OpenDialog: TOpenDialog;
    QueryFlowExist: TClientDataSet;
    UpdateFlowPT: TClientDataSet;
    GroupBox1: TGroupBox;
    Button1: TButton;
    Memo1: TMemo;
    BitBtn1: TBitBtn;
    procedure Button1Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form47: TForm47;

implementation
uses Unit1,ComObj;
{$R *.dfm}

procedure TForm47.Button1Click(Sender: TObject);
var
MSExcel:  Variant;
i,n,m,len,alen,filelen: Integer ;
  S,word,update_date,UPLOAD_DATE,OldfileStr,NewfileStr1,flag:string;
  F: TextFile;
  PT,STAGE,STEPNO,RECIPE,PROCESS,error_message,NewfileStr:string;
begin
OpenDialog.Filter:='*.XLS|*.XLS';
OpenDialog.DefaultExt:='XLS';
if  OpenDialog.Execute  then
begin
   try
    MSExcel:=CreateOLEObject('Excel.Application');
    MSExcel.WorkBooks.Open(OpenDialog.FileName);
    except
    Memo1.Lines.Append('Failed!!!');
    end;
    MSExcel.Visible:=False;
    Screen.Cursor :=crSQLWait ;
    for  i:=2  to  MSExcel.ActiveSheet.UsedRange.Rows.Count  do
    begin 
       // Edit2.Text:=Edit2.Text+MSExcel.Cells[i,1].Value;
       PT :=MSExcel.Cells[i,1].Value;
       STAGE :=MSExcel.Cells[i,2].Value;
       STEPNO :=MSExcel.Cells[i,3].Value;
       RECIPE :=MSExcel.Cells[i,4].Value;
       PROCESS :=MSExcel.Cells[i,5].Value;
    try
    QueryFlowExist.close;
    QueryFlowExist.FetchParams ;
    QueryFlowExist.Params.ParamByName('STAGE').AsString :=STAGE ;
    QueryFlowExist.Params.ParamByName('STEPNO').AsString :=STEPNO ;
    QueryFlowExist.Params.ParamByName('RECPID').AsString :=RECIPE ;
    QueryFlowExist.Params.ParamByName('PROCESS').AsString :=PROCESS ;
    QueryFlowExist.Open;
     if  QueryFlowExist.RecordCount>0 then
     begin
     //ShowMessage('stage:'+stage+';stepno:'+stepno+';recipe:'+recipe+';process:'+process);
    UpdateFlowPT.close;
    UpdateFlowPT.FetchParams ;
    UpdateFlowPT.Params.ParamByName('PT').AsString :=PT ;
    UpdateFlowPT.Params.ParamByName('STAGE').AsString :=STAGE ;
    UpdateFlowPT.Params.ParamByName('STEPNO').AsString :=STEPNO ;
    UpdateFlowPT.Params.ParamByName('RECPID').AsString :=RECIPE ;
    UpdateFlowPT.Params.ParamByName('PROCESS').AsString :=PROCESS ;
    UpdateFlowPT.Execute;
    Memo1.Lines.Append('PT:'+PT+'; stage:'+stage+'; stepno:'+stepno+'; recipe:'+recipe+'; sprocess:'+process+';');
     end;
      except
      on E:Exception do
      begin
      //Unit1.Form1.ErrorEmail(Datetimetostr(now)+':'+e.Message+',');
      exit;
      end;
     end;
     end;
   // ShowMessage('Update OK. Record count: '+inttostr(MSExcel.ActiveSheet.UsedRange.Rows.Count-1));
    Screen.Cursor :=crDefault ;
    Memo1.Lines.Append('Update OK. Record count: '+inttostr(MSExcel.ActiveSheet.UsedRange.Rows.Count-1));
    MSExcel.ActiveWorkBook.Close;
    MSExcel.Quit;

end;

end;

end.