本文整理自网络,侵删。
unit Unit1; interface uses Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms, Dialogs, Grids, DBGrids, DB, ADODB, StdCtrls, ExtCtrls, ComCtrls; type TForm1 = class(TForm) con1: TADOConnection; ds1: TADODataSet; ds2: TDataSource; pnl1: TPanel; btn2: TButton; btn3: TButton; btn4: TButton; Label1: TLabel; Label2: TLabel; Label3: TLabel; Label4: TLabel; edt1: TEdit; edt2: TEdit; edt3: TEdit; edt4: TEdit; Button1: TButton; dlgOpen1: TOpenDialog; stat1: TStatusBar; con2: TADOConnection; ds3: TADODataSet; ds4: TADODataSet; ds5: TADODataSet; ds6: TADODataSet; procedure btn1Click(Sender: TObject); procedure btn3Click(Sender: TObject); procedure Button1Click(Sender: TObject); procedure btn2Click(Sender: TObject); procedure btn4Click(Sender: TObject); private { Private declarations } public { Public declarations } end; var Form1: TForm1; implementation uses Unit2; {$R *.dfm} procedure TForm1.btn1Click(Sender: TObject);begin ShowMessage(ds1.FieldByName('卡号').AsString);end; procedure TForm1.btn3Click(Sender: TObject);const Constring='Provider=Microsoft.Jet.OLEDB.4.0;Data Source=%s;Mode=Read;Extended Properties=Excel 8.0;Persist Security Info=False';var i:integer; TableName:string;begin if dlgOpen1.Execute then begin ds1.Close; con1.Close; con1.ConnectionString:=Format(constring,[dlgOpen1.FileName]); con1.Open; con1.GetTableNames(Form2.lst1.Items,false); if Form2.ShowModal=mrok then begin for i:=0 to Form2.lst1.Count-1 do begin if Form2.lst1.Selected[i] then TableName:=Form2.lst1.Items.Strings[i]; end; if TableName<>'' then begin ds1.Close; ds1.CommandText:='select * from ['+TableName+']'; ds1.Open; stat1.SimpleText:='共'+inttostr(ds1.RecordCount)+'条记录!'; for i:=0 to DBGrid1.GetColCount-1 do begin DBGrid1.ColWidths[i]:=25; end; end; end; end;end; procedure TForm1.Button1Click(Sender: TObject);begin close;end; procedure TForm1.btn2Click(Sender: TObject);const constr='Provider=SQLOLEDB.1;Password=%s;Persist Security Info=True;User ID=%s;Initial Catalog=%s;Data Source=%s';begin con2.close; con2.ConnectionString:=Format(constr,[edt3.Text,edt2.Text,edt4.text,edt1.Text]); con2.Open; showmessage('连接成功!');end; procedure TForm1.btn4Click(Sender: TObject);var i:integer; msg:string;begin msg:=''; ds1.DisableControls; try //检测有效字段名 if (ds1.FindField('工号')=nil) or (ds1.FindField('部门')=nil) or (ds1.FindField('姓名')=nil) then begin ShowMessage('要导入的数据必须包含[工号]、[姓名]与[部门],此为必录字段'); Exit; end; ds3.Open; ds4.Open; ds5.Open; ds6.Open; //检查是否有对应字段 for i:=0 to ds1.FieldCount-1 do begin if not ((ds1.Fields[i].FieldName='工号') or (ds1.Fields[i].FieldName='部门') or (ds1.Fields[i].FieldName='姓名') or (ds1.Fields[i].FieldName='职务')) then begin if not ds4.Locate('ItemName',ds1.Fields[i].FieldName,[]) then begin ShowMessage('此数据库中无此字段['+ds1.Fields[i].FieldName+']'); Exit; end; end; end; //开始导入数据 con2.BeginTrans; try ds1.First; while not ds1.Eof do begin ds5.Append; if not ds3.Locate('dept_name',ds1.FieldByName('部门').Asstring,[]) then begin msg:='在此数据库中找不到对应的部门-记录号'+inttostr(ds1.RecordCount)+' '+ds1.FieldByName('部门').Asstring; raise EADOError.Create('在此数据库中找不到对应的部门-记录号'+inttostr(ds1.RecNo)+' '+ds1.FieldByName('部门').Asstring); end; ds5.FieldByName('dept_no').AsString:=TrimRight(ds3.FieldByName('dept_no').Asstring); if ds1.FindField('薪种')<>nil then if ds1.FieldByName('薪种').Asstring='件工' then ds5.FieldByName('sal_no').Asstring:='1' else ds5.FieldByName('sal_no').Asstring:='2'; ds5.FieldByName('emp_no').Asstring:=TrimRight(ds1.FieldByName('工号').Asstring); ds5.FieldByName('emp_name').Asstring:=TrimRight(ds1.FieldByName('姓名').Asstring); if ds1.FindField('职务')<>nil then ds5.FieldByName('job').Asstring:=TrimRight(ds1.FieldByName('职务').Asstring); ds5.Post; ds6.Append; ds6.FieldByName('emp_no').Asstring:=TrimRight(ds1.FieldByName('工号').Asstring); for i:=0 to ds1.FieldCount-1 do begin if ds4.Locate('ItemName',ds1.Fields[i].FieldName,[]) then ds6.FieldByName(trimright(ds4.FieldByName('FieldName').AsString)).AsString:=ds1.Fields[i].AsString; end; ds6.Post; ds1.next; end; con2.CommitTrans; except con2.RollbackTrans; for i:=0 to con2.Errors.Count-1 do msg:=msg+con2.Errors.Item[i].Description; ShowMessage(msg); end; finally ds1.EnableControls; end;end; end.
相关阅读 >>
Delphi 关于 class helper for ... 语法
Delphi tadodataset 加载数据到fdmemtable1
Delphi 工程判断内存溢出reportmemoryleaksonshutdown := true;
更多相关阅读请进入《Delphi》频道 >>