uimportdata.pas

Delphi学籍管理程序,以Delphi7.0为前台开发工具

unit uImportData;

interface

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

type
  TdlgImport = class(TForm)
    Animate1: TAnimate;
    PB: TProgressBar;
    Label1: TLabel;
    lImporting: TLabel;
    OpenDialog: TOpenDialog;
    adcImport: TADOConnection;
    aqImport: TADOQuery;
    bbRun: TBitBtn;
    bbClose: TBitBtn;
    mFileName: TMemo;
    procedure bbRunClick(Sender: TObject);
  private
    { Private declarations }
    FClassName: string;
  public
    { Public declarations }
    FDataset: TDataset;
  end;

procedure ImportDataFromExcel(Dataset: TDataset; AClassName: string);

implementation

uses uGlobe;

{$R *.dfm}

procedure ImportDataFromExcel(Dataset: TDataset; AClassName: string);
const
  ConStr = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=%s;'
    + 'Extended Properties=Excel 8.0;Persist Security Info=False';
  SQL_ImportData = 'SELECT '+
                   '学号,'+
                   '建卡时间,'+
                   '说明,'+
                   '当前班级,'+
                   '姓名,'+
                   '性别,'+
                   '民族,'+
                   '出生年月,'+
                   '籍贯,'+
                   '是否烈属、华侨、台胞子女,'+
                   '户口性质,'+
                   '户籍所在地派出所,'+
                   '家庭住址,'+
                   '所属派出所,'+
                   '住址、户籍变更记载'+
                   ' FROM [%s$] ORDER BY 学号';
  Error_ImportOpen = '打开文件失败, 请检查Exce文件的设置!' + #13
    + #13 + '工作表: %s'
    + #13 + '数据项: 学号,建卡时间,说明,当前班级,姓名,性别,民族,出生年月,籍贯,是否烈属、华侨、台胞子女,'
    + '户口性质,户籍所在地派出所,家庭住址,所属派出所,住址、户籍变更记载';
begin
  with TdlgImport.Create(Application) do
  try
    if not OpenDialog.Execute then exit;
    mFileName.Lines.Text := OpenDialog.FileName;

    FDataset := Dataset;
    adcImport.ConnectionString := format(ConStr, [mFileName.Lines.Text]);
    aqImport.SQL.Text := format(SQL_ImportData, ['学籍卡']);

    FClassName := AClassname;
    try
      aqImport.Open;
    except
      raise Exception.Create(format(Error_ImportOpen, ['学籍卡']));
    end;

    if aqImport.RecordCount = 0 then
      raise Exception.Create('没有打到符合条件的记录!');

    PB.Max := aqImport.RecordCount;
    Showmodal;
  finally
    Free;
  end;
end;

procedure TdlgImport.bbRunClick(Sender: TObject);
const
  f_Importing = '(%d/%d)正在导入...';
  Error_Importimg = '第%d条记录导入失败, 运行中止.原因可能是因为学号出现重复, 请确认后再试.';
  f_Imported = '导入完毕, 共导入了%d条记录.';
begin
  self.Animate1.Active := True;
  self.bbRun.Enabled := False;
  self.bbClose.Enabled := False;

  try
  aqImport.First;
  while not aqImport.Eof do begin
    lImporting.Caption := format(f_Importing,
      [aqImport.RecNo, aqImport.RecordCount]);
    PB.Position := aqImport.RecNo;
    Update;
    Application.ProcessMessages;

    FDataset.Append;
    FDataset.FieldByName('XH').AsString :=
      aqImport.FieldByName('学号').AsString;
    FDataset.FieldByName('CreateDate').AsString :=
      aqImport.FieldByName('建卡时间').AsString;
    FDataset.FieldByName('Memo').AsString :=
      aqImport.FieldByName('说明').AsString;
    FDataset.FieldByName('CurClass').AsString :=
      FClassName;
    FDataset.FieldByName('Name').AsString :=
      aqImport.FieldByName('姓名').AsString;
    FDataset.FieldByName('Sex').AsString :=
      aqImport.FieldByName('性别').AsString;
    FDataset.FieldByName('People').AsString :=
      aqImport.FieldByName('民族').AsString;
    FDataset.FieldByName('Birthday').AsString :=
      aqImport.FieldByName('出生年月').AsString;
    FDataset.FieldByName('Native').AsString :=
      aqImport.FieldByName('籍贯').AsString;
    FDataset.FieldByName('MOT').AsString :=
      aqImport.FieldByName('是否烈属、华侨、台胞子女').AsString;
    FDataset.FieldByName('RegKind').AsString :=
      aqImport.FieldByName('户口性质').AsString;
    FDataset.FieldByName('RegPS').AsString :=
      aqImport.FieldByName('户籍所在地派出所').AsString;
    FDataset.FieldByName('Address').AsString :=
      aqImport.FieldByName('家庭住址').AsString;
    FDataset.FieldByName('AddPS').AsString :=
      aqImport.FieldByName('所属派出所').AsString;
    FDataset.FieldByName('RAMR').AsString :=
      aqImport.FieldByName('住址、户籍变更记载').AsString;
    FDataset.Post;

    aqImport.Next;
  end;

  except
    FDataset.Cancel;
    lImporting.Caption := format(Error_Importimg, [aqImport.RecNo]);
    self.Animate1.Active := False;
    self.bbClose.Enabled := True;
    exit;
  end;

  lImporting.Caption := format(f_Imported, [aqImport.RecordCount]);
  self.Animate1.Active := False;
  self.bbClose.Enabled := True;

end;

end.