转：用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.