delphi excel导入到mysql_Delphi EXCEL表格数据导入数据库

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.