updateaccessdavid.~pas

ACCESSE和Excel之间的数据互换

unit UpdateAccessDavid;

interface

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

type
  TUpdateData = class(TForm)
    btnOpenDataSourceXsl: TButton;
    btnOpenTargetBataSourceMdb: TButton;
    EdiDataSourcePathXsl: TEdit;
    EdiTargetDataPath: TEdit;
    CmbDataSourceXsl: TComboBox;
    CbmDataTargerMdb: TComboBox;
    Label1: TLabel;
    ListBoxFields: TListBox;
    BtnExcUpdate: TButton;
    ProgressBarUpdate: TProgressBar;
    ADOConDataSourceXsl: TADOConnection;
    ADOConTargetDataMdb: TADOConnection;
    ADOQueryDataSourceXsl: TADOQuery;
    ADOQueryTargetDataMdb: TADOQuery;
    OpenDialog: TOpenDialog;
    procedure btnOpenDataSourceXslClick(Sender: TObject);
    procedure btnOpenTargetBataSourceMdbClick(Sender: TObject);
    procedure BtnExcUpdateClick(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  UpdateData: TUpdateData;

implementation

{$R *.dfm}

procedure TUpdateData.btnOpenDataSourceXslClick(Sender: TObject);
var
  s,ExcelTableName:string;
  ALists:TStringList;
begin
  ALists:=TStringList.Create;
  if OpenDialog.Execute then
  begin
    s:=OpenDialog.FileName;
  end;
  CmbDataSourceXsl.Items.Clear;
  
  ADOConDataSourceXsl.Connected:=False;
  ADOConDataSourceXsl.ConnectionString:='Provider=Microsoft.Jet.OLEDB.4.0;Data Source='+s+';Extended Properties=Excel 8.0;Persist Security Info=False';
  ADOConDataSourceXsl.Connected:=True;
  ADOConDataSourceXsl.GetTableNames(ALists,false);

  ADOQueryDataSourceXsl.Connection:=ADOConDataSourceXsl;
  ADOQueryDataSourceXsl.SQL.Clear;

  CmbDataSourceXsl.Items.Clear;
  CmbDataSourceXsl.Items.AddStrings(ALists);
  CmbDataSourceXsl.ItemIndex:=0;
  ExcelTableName:=CmbDataSourceXsl.Text;
end;
procedure TUpdateData.btnOpenTargetBataSourceMdbClick(Sender: TObject);
var
  DataDestination:String;
begin
  ADOConTargetDataMdb.Connected:=False;
  if OpenDialog.Execute then
    begin
    DataDestination:=OpenDialog.FileName;
    EdiTargetDataPath.Text:=OpenDialog.FileName;
    end;
  CbmDataTargerMdb.Items.Clear;
  ADOConTargetDataMdb.ConnectionString :=
    'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' +  DataDestination+
    ';Persist Security' + ' Info=False';
  ADOConTargetDataMdb.Provider:= 'Microsoft.Jet.OLEDB.4.0'  ;
  ADOConTargetDataMdb.Connected:=True;
  CbmDataTargerMdb.Items.Clear;
  ADOConTargetDataMdb.GetTableNames(CbmDataTargerMdb.Items,false);
end;

procedure TUpdateData.BtnExcUpdateClick(Sender: TObject);
var
  I,J:Integer;
  StrItem,s,ACode,AFieldName:String;
begin
  ListBoxFields.Clear;
  for I :=0  to ADOQueryTargetDataMdb.FieldCount-1 do
  begin
    ListBoxFields.Items.Add(ADOQueryTargetDataMdb.Fields[I].FieldName);
  end;

  ADOQueryDataSourceXsl.First;
  ProgressBarUpdate.Max:=ADOQueryTargetDataMdb.RecordCount;
  ADOQueryTargetDataMdb.First;
  while not ADOQueryTargetDataMdb.eof do
  begin
    Application.ProcessMessages;
    ACode:=ADOQueryTargetDataMdb.FieldByName('O150_002').Value;
    if ADOQueryDataSourceXsl.Locate('O150_002',ACode,[]) then
    begin
      ADOQueryTargetDataMdb.Edit;
      ListBoxFields.ItemIndex:=0;
      for J := 0 to ListBoxFields.Items.Count-1 do
      begin
        AFieldName:=ListBoxFields.Items[J];
        if AFieldName<>'O150_001' then
          ADOQueryTargetDataMdb.FieldByName(AFieldName).Value:=ADOQueryDataSourceXsl.FieldByName(AFieldName).Value;
      end;
      ADOQueryTargetDataMdb.Post;
    end;
    ProgressBarUpdate.Position:=ProgressBarUpdate.Position+1;
    ADOQueryTargetDataMdb.Next;
  end;
  ShowMessage('OK');

end;

end.