unit Unit1;

interface

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

type
  TForm1 = class(TForm)
    ADOConnection1: TADOConnection;
    ADOConnection2: TADOConnection;
    Button1: TButton;
    ADODataSet1: TADODataSet;
    ADOQuery1: TADOQuery;
    Label1: TLabel;
    SpinEdit1: TSpinEdit;
    procedure FormCreate(Sender: TObject);
    procedure Button1Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.FormCreate(Sender: TObject);
begin
  ADOConnection1.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;Password="";Data Source=' + ExtractFilePath(Application.ExeName) + '单位用户.mdb;Persist Security Info=True';
end;

procedure TForm1.Button1Click(Sender: TObject);
var
  iLoop: Integer;
  Sex,
  LoginName,
  TrueName,
  DisplayName,
  Duty,
  Tel,
  Mobile,
  ShortMobile,
  OfficeID,
  Email: String;
begin
  ADOConnection1.Connected := True;
  ADOConnection2.Connected := True;
  ADODataSet1.CommandText := 'SELECT * FROM Sheet1';
  ADODataSet1.Active := True;


  iLoop := 0;
  while not ADODataSet1.Eof do
  begin
    ADOQuery1.SQL.Text := 'delete Users where LoginName=''' + ADODataSet1.FindField('用户名').Value + '''';
    ADOQuery1.ExecSQL;

    Sex := '0';
    LoginName := ADODataSet1.FindField('用户名').Value;
    TrueName := ADODataSet1.FindField('单位名称').Value;
    DisplayName := ADODataSet1.FindField('单位名称').Value;
    Duty := '';
    Tel := '';
    Mobile := '';
    ShortMobile := '';
    OfficeID := '';
    Email := '';

    ADOQuery1.SQL.Text := 'insert into Users ' +
      '(LoginName, ' +
      'Password, ' +
      'Sex, ' +
      'TrueName, ' +
      'DisplayName, ' +
      'Duty, ' +
      'Tel, ' +
      'Mobile, ' +
      'Email, ' +
      'BranchID,' + 
      '[Position],' +
      'Version)' +
      'Values ' +
      '(''' + LoginName + ''',' +
      '''' + MD5.MD5En('12345678') + ''',' +
      '''' + Sex + ''',' +
      '''' + TrueName + ''',' +
      '''' + DisplayName + ''',' +
      '''' + Duty + ''',' +
      '''' + Tel + ''',' +
      '''' + Mobile + ''',' +
      '''' + Email + ''',' +
      '' + IntToStr(SpinEdit1.Value) + ',' +
      '' + IntToStr(100 + iLoop) + ',' +
      '1)';
    ADOQuery1.ExecSQL;

    Inc(iLoop);
    Caption := '已导入 ' + IntToStr(iLoop) + ' 条记录';
    Application.ProcessMessages;

    ADODataSet1.Next;
  end;


  {
  iLoop := 0;
  while not ADODataSet1.Eof do
  begin
    ADOQuery1.SQL.Text := 'delete Users where LoginName=''' + ADODataSet1.FindField('F1').Value + '''';
    ADOQuery1.ExecSQL;

    if AnsiSameText(ADODataSet1.FindField('F3').Value, '男') then
      Sex := '0'
    else
      Sex := '1';
    LoginName := ADODataSet1.FindField('F1').Value; 
    TrueName := ADODataSet1.FindField('F2').Value;
    DisplayName := ADODataSet1.FindField('F2').Value;
    try
      Duty := ADODataSet1.FindField('F4').Value;
    except
      Duty := '';
    end;

    try
      Tel := ADODataSet1.FindField('F5').Value;
    except
      Tel := '';
    end;

    try
      Mobile := ADODataSet1.FindField('F6').Value;
    except
      Mobile := '';
    end;

    try
      ShortMobile := ADODataSet1.FindField('F7').Value;
    except
      ShortMobile := '';
    end;

    try
      OfficeID := ADODataSet1.FindField('F8').Value;
    except
      OfficeID := '';
    end;

    try
      Email := ADODataSet1.FindField('F9').Value;
    except
      Email := '';
    end;

    ADOQuery1.SQL.Text := 'insert into Users ' +
      '(LoginName, ' +
      'Password, ' +
      'Sex, ' +
      'TrueName, ' +
      'DisplayName, ' +
      'Duty, ' +
      'Tel, ' +
      'Mobile, ' +
      'Email, ' +
      'BranchID,' + 
      '[Position],' +
      'Version)' +
      'Values ' +
      '(''' + LoginName + ''',' +
      '''96e79218965eb72c92a549dd5a330112'',' +
      '''' + Sex + ''',' +
      '''' + TrueName + ''',' +
      '''' + DisplayName + ''',' +
      '''' + Duty + ''',' +
      '''' + Tel + ''',' +
      '''' + Mobile + ''',' +
      '''' + Email + ''',' +
      '' + IntToStr(SpinEdit1.Value) + ',' +
      '' + IntToStr(iLoop) + ',' +
      '1)';
    ADOQuery1.ExecSQL;

    Inc(iLoop);
    Caption := '已导入 ' + IntToStr(iLoop) + ' 条记录';
    Application.ProcessMessages;

    ADODataSet1.Next;
  end;
  }
end;

end.