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

1)使用DoCmd.TransferSpreadsheet功能,这种方法更简单但不灵活.

{$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,ExcelFileName,True);
 LAccess.CloseCurrentDatabase;
 LAccess.Quit(1);
end;

begin
 try
    CoInitialize(nil);
    try
      ImportDataAccess('C:DataDatabase1.accdb','Sales','C:DataSales.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,ComObj;


procedure ImportDataADO(const AccessDb,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:DatosDatabase1.accdb','C:DatosSales.xlsx');
      Writeln('Done');
    finally
      CoUninitialize;
    end;
 except
    on E:EOleException do
        Writeln(Format('EOleException %s %x',E.Message);
 end;
 Writeln('Press Enter to exit');
 Readln;
end.