updateaccessdavid.pas

ACCESSE和Excel之间的数据互换

unit UpdateAccessDavid;

interface

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

type
  TUpdateData = class(TForm)
    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;
    ToolBar1: TToolBar;
    Button1: TButton;
    SpeedButton1: TSpeedButton;
    SpeedButton2: TSpeedButton;
    procedure BtnExcUpdateClick(Sender: TObject);
    procedure CmbDataSourceXslChange(Sender: TObject);
    procedure CbmDataTargerMdbChange(Sender: TObject);
    procedure Button1Click(Sender: TObject);
    procedure SpeedButton1Click(Sender: TObject);
    procedure SpeedButton2Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  UpdateData: TUpdateData;

implementation

{$R *.dfm}

procedure TUpdateData.BtnExcUpdateClick(Sender: TObject);
var
  I,J:Integer;
  StrItem,s,ACode,AFieldName:String;
begin
  ADOQueryDataSourceXsl.First;
  ADOQueryTargetDataMdb.First;
  ProgressBarUpdate.Max:=ADOQueryTargetDataMdb.RecordCount;
  for I :=0  to ADOQueryTargetDataMdb.RecordCount-1 do
    begin
    Application.ProcessMessages;
    ListBoxFields.ItemIndex:=0;
    ACode:=ADOQueryTargetDataMdb.FieldByName('O150_002').Value;
      if ADOQueryDataSourceXsl.Locate('O150_002',ACode,[]) then
        begin
        ADOQueryTargetDataMdb.Edit;
        for J := 0 to ListBoxFields.Items.Count-1 do
          begin
            AFieldName:=ListBoxFields.Items[J];
            if AFieldName<>'O150_001' then
              begin
              ADOQueryTargetDataMdb.FieldByName(AFieldName).Value:=ADOQueryDataSourceXsl.FieldByName(AFieldName).Value;
              end;
          end;
          ADOQueryTargetDataMdb.Post;
        end;
    ProgressBarUpdate.Position:=ProgressBarUpdate.Position+1;
    ADOQueryTargetDataMdb.Next;
    end;
  ShowMessage(inttostr(ADOQueryTargetDataMdb.RecordCount)+'条记录更新完毕！');
end;

procedure TUpdateData.CmbDataSourceXslChange(Sender: TObject);
var
  sql2,s:String;
begin
  s:=CmbDataSourceXsl.Text;
  sql2:='select * from ['+s+']';
  ADOQueryDataSourceXsl.Close;
  ADOQueryDataSourceXsl.SQL.Clear;
  ADOQueryDataSourceXsl.SQL.Add(sql2);
  ADOQueryDataSourceXsl.Open;
end;


procedure TUpdateData.CbmDataTargerMdbChange(Sender: TObject);
begin
  ADOQueryTargetDataMdb.Close;
  ADOQueryTargetDataMdb.SQL.Clear;
  ADOQueryTargetDataMdb.SQL.Add('select * from ['+CbmDataTargerMdb.Text+']');
  ADOQueryTargetDataMdb.Open;
end;


procedure TUpdateData.Button1Click(Sender: TObject);
var
  I,J:Integer;
begin
  ListBoxFields.Clear;
    for J :=0  to ADOQueryDataSourceXsl.FieldCount-1 do
    begin
      if ADOQueryTargetDataMdb.FindField(ADOQueryDataSourceXsl.Fields[J].FieldName)<>nil then
        begin
        ListBoxFields.Items.Add(ADOQueryDataSourceXsl.Fields[J].FieldName);
        end;
    end;
end;

procedure TUpdateData.SpeedButton1Click(Sender: TObject);
var
  s,ExcelTableName:string;
  ALists:TStringList;
begin
  ALists:=TStringList.Create;
  if OpenDialog.Execute then
  begin
    s:=OpenDialog.FileName;
    EdiDataSourcePathXsl.Text:=OpenDialog.FileName;
    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;
end;

procedure TUpdateData.SpeedButton2Click(Sender: TObject);
var
  DataDestination:String;
begin
  ADOConTargetDataMdb.Connected:=False;
  if OpenDialog.Execute then
    begin
      DataDestination:=OpenDialog.FileName;
      EdiTargetDataPath.Text:=OpenDialog.FileName;

      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;
      ADOConTargetDataMdb.GetTableNames(CbmDataTargerMdb.Items,false);
    end;
end;
class function DeleteRepeat(AOldString: string; ADeleteString,
  ARepeat: Char): string;
var
  I: Integer;
  tmpfind1: Boolean;
begin
  tmpfind1 := False;
  for I := Length(AOldString) downto 1 do
  begin
    if tmpfind1 then
    begin
      if AOldString[I] = ADeleteString then
        Delete(AOldString, I, 1)
      else
      begin
        if AOldString[I] = ARepeat then
        Continue;
        tmpfind1 := AOldString[I] = ADeleteString;
      end;
    end
    else
    begin
      if ADeleteString <> ARepeat then
        if AOldString[I] = ARepeat then
          Continue;
        
      tmpfind1 := AOldString[I] = ADeleteString
    end;
  end;
  Result := AOldString;
end;
procedure TUpdateData.Button2Click(Sender: TObject);
var
  I:integer;
begin
for  I:= 0 to CmbDataSourceXsl.Items.Count-1 do
begin
 end;


end;

end.