unit1.pas

将Excel导入Access(菜鸟版) 将Excel导入Access(菜鸟版) 将Excel导入Access(菜鸟版)

unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, DB, ADODB, StdCtrls, Grids, DBGrids;

type
  TForm1 = class(TForm)
    ADOConnection1: TADOConnection;
    ADOConnection2: TADOConnection;
    Button1: TButton;
    OpenDialog2: TOpenDialog;
    ADOQuery1: TADOQuery;
    ADOQuery2: TADOQuery;
    DBGrid1: TDBGrid;
    DataSource1: TDataSource;
    Button2: TButton;
    Label1: TLabel;
    SaveDialog1: TSaveDialog;
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.Button1Click(Sender: TObject);
var
  sFileName, sTableName: string;
  sl: TStringList;
  i: integer;
begin

  if not OpenDialog2.Execute then Exit;

  if not ADOConnection1.Connected then  ADOConnection1.Connected:=True;  //判断是否连接

 try
  sFileName := OpenDialog2.FileName;
  ADOConnection2.Connected := False;    //注意这里 连接 EXCEL 的参数                                                                //关键是这里
  ADOConnection2.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;Password="";Data Source=' + sFileName + ';Extended Properties="Excel 8.0;IMEX=1";Persist Security Info=False';
  ADOConnection2.Connected := True;
  //
  sl := TStringList.Create;
  ADOConnection2.GetTableNames(sl); // 记住 Excel 文件的 底部 必须是 sheet1   否则不能导入
  sTableName := sl[0];
  if Pos('$', sTableName) > 0 then sTableName := '[' + sTableName + ']';

  ADOquery2.Close;
  ADOquery2.SQL.Clear;
  ADOquery2.SQL.Text := 'SELECT * FROM ' + sTableName;
  ADOquery2.Open;
  sl.Free;



  ADOquery1.Close;
  ADOquery1.Sql.Text := 'SELECT top 1 * FROM sp where 1=0';      //打开 空记录表....这里可以用 insert,不用append
  ADOquery1.Open;                                                           //根据自己习惯 做吧...

  if ADOquery2.RecordCount > 0 then
  begin

    ADOquery2.First;
    for i := 1 to ADOquery2.RecordCount do      //
    begin
        ADOquery1.Append;
        ADOquery1.FieldValues['编号']:=ADOquery2.FieldValues['编号'];
        ADOquery1.FieldValues['名称']:=ADOquery2.FieldValues['名称'];
        ADOquery1.FieldValues['价格']:=ADOquery2.FieldValues['价格'];
        ADOquery1.Post;

      ADOquery2.Next;
    end;

  end;



 MessageBox(Handle,'导入完成！','完成！',MB_OK+MB_ICONINFORMATION);
 except
 MessageBox(Handle,'失败！','信息！',MB_OK+MB_ICONINFORMATION);

 end;

end;

procedure TForm1.Button2Click(Sender: TObject);
begin
  if not ADOConnection1.Connected then  ADOConnection1.Connected:=True;  //判断是否连接

  ADOquery1.Close;
  ADOquery1.Sql.Text := 'SELECT  * FROM sp';
  ADOquery1.Open;
end;

end.