delphi excel导入到mysql_如何使用delphi将Excel文件导入Access数据库

你有几个选择,尝试其中之一

{$APPTYPE CONSOLE}

{$R *.res}

uses

SysUtils,

ActiveX,

ComObj;

procedure ImportDataAccess(const AccessDb, TableName, ExcelFileName:String);

Const

acQuitSaveAll = $00000001;

acImport = $00000000;

acSpreadsheetTypeExcel9 = $00000008;

acSpreadsheetTypeExcel12 = $00000009;

var

LAccess : OleVariant;

begin

//create the COM Object

LAccess := CreateOleObject('Access.Application');

//open the access database

LAccess.OpenCurrentDatabase(AccessDb);//if the access database doesn't exist use the NewCurrentDatabase method instead.

//import the data

LAccess.DoCmd.TransferSpreadsheet( acImport, acSpreadsheetTypeExcel9, TableName, ExcelFileName, True);

LAccess.CloseCurrentDatabase;

LAccess.Quit(1);

end;

begin

try

CoInitialize(nil);

try

ImportDataAccess('C:\Data\Database1.accdb','Sales','C:\Data\Sales.xlsx');

Writeln('Done');

finally

CoUninitialize;

end;

except

on E:EOleException do

Writeln(Format('EOleException %s %x', [E.Message,E.ErrorCode]));

on E:Exception do

Writeln(E.Classname, ':', E.Message);

end;

Writeln('Press Enter to exit');

Readln;

end.

2)使用ado组件,更灵活的方式.

{$APPTYPE CONSOLE}

{$R *.res}

uses

Data.DB,

Data.Win.ADODB,

SysUtils,

ActiveX,

ComObj;

procedure ImportDataADO(const AccessDb, TableName, ExcelFileName:String);

var

LAdoQueryExcel : TADOQuery;

LADOAccesCmd : TADOCommand;

begin

LAdoQueryExcel:=TADOQuery.Create(nil);

LADOAccesCmd:=TADOCommand.Create(nil);

try

//set the connection string for access

LADOAccesCmd.ConnectionString:=Format('Provider=Microsoft.ACE.OLEDB.12.0;Data Source=%s;',[AccessDb]);

LADOAccesCmd.Parameters.Clear;

LADOAccesCmd.CommandText:='INSERT INTO Sales (id,name) VALUES (:id,:name)';

LADOAccesCmd.ParamCheck:=False;

//set the connection string for excel

LAdoQueryExcel.ConnectionString:=Format('Provider=Microsoft.ACE.OLEDB.12.0;Data Source=%s;Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1"',[ExcelFileName]);

LAdoQueryExcel.SQL.Add('SELECT * FROM [Sheet1$]');

LAdoQueryExcel.Open;

while not LAdoQueryExcel.eof do

begin

LADOAccesCmd.Parameters.ParamByName('id').Value := LAdoQueryExcel.FieldByname('id').AsInteger;

LADOAccesCmd.Parameters.ParamByName('name').Value := LAdoQueryExcel.FieldByname('name').AsString;

LADOAccesCmd.Execute;

LAdoQueryExcel.Next;

end;

finally

LAdoQueryExcel.Free;

LADOAccesCmd.Free;

end;

end;

begin

try

CoInitialize(nil);

try

ImportDataADO('C:\Datos\Database1.accdb','Sales','C:\Datos\Sales.xlsx');

Writeln('Done');

finally

CoUninitialize;

end;

except

on E:EOleException do

Writeln(Format('EOleException %s %x', [E.Message,E.ErrorCode]));

on E:Exception do

Writeln(E.Classname, ':', E.Message);

end;

Writeln('Press Enter to exit');

Readln;

end.