unit_frm_rszlfromexcel.pas

Excel导入导出:This TscExcelExport component is an advanced, powerfull but easy component to export all r

unit Unit_frm_rszlFromExcel;

interface

uses
  //OLE
  ComObj,
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, Gauges, RzButton, DB, ADODB, StdCtrls,
  RzLabel, StrUtils, LMDCustomControl, LMDCustomPanel, LMDCustomBevelPanel,
  LMDBaseEdit, LMDCustomEdit, LMDCustomBrowseEdit, LMDCustomFileEdit,
  LMDFileOpenEdit, Mask, RzEdit, DateUtils, ExtCtrls;

type
  Tfrm_rszlFromExcel = class(TForm)
    gg1: TGauge;
    bbtn_excel: TRzBitBtn;
    bbtn_Close: TRzBitBtn;
    qry1: TADOQuery;
    lbl1: TRzLabel;
    lbl2: TRzLabel;
    qry2: TADOQuery;
    lbl3: TLabel;
    fileedt1: TLMDFileOpenEdit;
    lbl5: TLabel;
    bvl1: TBevel;
    qry3: TADOQuery;
    qry4: TADOQuery;
    procedure bbtn_excelClick(Sender: TObject);
    procedure bbtn_CloseClick(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure FormCreate(Sender: TObject);
    procedure FormCloseQuery(Sender: TObject; var CanClose: Boolean);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  frm_rszlFromExcel: Tfrm_rszlFromExcel;
  ist, isYes, kqisYes: Boolean;

implementation
uses
  Unit_dm1, frm_mealsystem_ic, Unit_Function, Unit_frm_rszl;
{$R *.dfm}

function GG(s:string):string ;
var
  i: Integer;
begin
	if (Length(s)<4) and (s<>'') then
	begin
	  for i := 1 to 4-Length(s) do
  	begin
    	s:='0'+s;
	  end;
  end;
  Result := s;
end;

procedure MyExcel;
var
  MSExcel:Variant;//定义一个变量
  r, rcount: Integer;
  Excelstr, ExcelZggh, ExcelName, Excelbm: string;
begin
  try
    MSExcel := CreateOleObject('Excel.Application'); //创建一个OleObject
    
     MSExcel.WorkBooks.Open(frm_rszlFromExcel.fileedt1.Text);

      Excelstr := MSExcel.Cells.Item[1,1];
      Excelstr := LeftStr(Excelstr,2);
      if Excelstr = '@@' then
      begin
        rcount := 0;
        repeat
          rcount := rcount+1;
          excelstr := MSExcel.Cells.Item[rcount,1];
        until (excelstr='');
        rcount := rcount-1;
        frm_rszlFromExcel.gg1.MinValue := 0;
        frm_rszlFromExcel.gg1.MaxValue := rcount-2;
        for r := 2 to rcount do
        begin
          ExcelZggh := MSExcel.Cells.Item[r,1];
          ExcelName := MSExcel.Cells.Item[r,2];
          Excelbm := MSExcel.Cells.Item[r,3];
          frm_rszlFromExcel.lbl2.Caption := ExcelZggh+'/'+ExcelName;
          if not IsValidInt(Excelbm) then
          begin
            Excelbm := '22';
          end;
          if ExcelZggh<>'' then
          begin
            with frm_rszlFromExcel.qry2 do
            begin  
              Close;
              SQl.Clear;
              SQl.Add('SELECT * FROM 人事资料');
              SQL.Add('WHERE 工号='''+ExcelZggh+'''');
              Open;
              if not IsEmpty then
              begin
                isYes := True;
              end else
              begin
                isYes := False;
              end;
            end;
            with frm_rszlFromExcel.qry3 do
            begin    
              Close;
              SQl.Clear;
              SQl.Add('SELECT * FROM grsbbz');
              SQL.Add('WHERE gzbh='''+GG(ExcelZggh)+'''');
              Open;
              if not IsEmpty then
              begin
                kqisYes := True;
              end else
              begin
                kqisYes := False;
              end;
            end;
            with frm_rszlFromExcel.qry1 do
            begin
              if not isYes then
              begin
                Close;
                SQL.Clear;
                SQL.Add('INSERT INTO 人事资料');
                SQL.Add('(工号,姓名,学历编号,籍贯编号,民族,部门编号,职位编号,档案类型)');
                SQL.Add('VALUES ('''+ExcelZggh+''','''+ExcelName+''',10,18,''汉族'',');
                SQL.Add(Excelbm+',8,''临时工'')');
                ExecSQL
              end else
              begin
                if (ExcelName<>frm_rszlFromExcel.qry2.FieldByName('姓名').AsString) or (Excelbm<>frm_rszlFromExcel.qry2.FieldByName('部门编号').AsString) then
                begin
                  Close;
                  SQL.Clear;
                  SQL.Add('UPDATE 人事资料');
                  SQL.Add('SET 部门编号='+Excelbm);
                  SQL.Add(',姓名='''+ExcelName+'''');
                  SQL.Add('WHERE 工号='''+ExcelZggh+'''');
                  ExecSQL;
                end;
              end;
            end;
            //kqxt
            with frm_rszlFromExcel.qry4 do
            begin
              if not kqisYes then
              begin
                Close;
                SQL.Clear;
                SQL.Add('INSERT INTO grsbbz');
                SQL.Add('(gzbh,xm,bmbh,fk,gs,bh)');
                SQL.Add('VALUES ('''+GG(ExcelZggh)+''','''+ExcelName+''',');
                SQL.Add(''''+Excelbm+''',0,0,''01,02,03,04,  ,  ,'')');
                ExecSQL
              end else
              begin
                if (ExcelName<>frm_rszlFromExcel.qry3.FieldByName('xm').AsString) or (Excelbm<>frm_rszlFromExcel.qry3.FieldByName('bmbh').AsString) then
                begin
                  Close;
                  SQL.Clear;
                  SQL.Add('UPDATE grsbbz');
                  SQL.Add('SET bmbh='''+Excelbm+'''');
                  SQL.Add(',xm='''+ExcelName+'''');
                  SQL.Add('WHERE gzbh='''+GG(ExcelZggh)+'''');
                  ExecSQL;
                end;
              end; 
              frm_rszlFromExcel.gg1.Progress := frm_rszlFromExcel.gg1.Progress + 1;
            end;
          end;
        end;
        frm_rszlFromExcel.lbl2.Caption := '                        ';
        frm_rszl.btn_rel.Click;
        frm_rszlFromExcel.bbtn_excel.Enabled := True;
        ist := False;
        MSExcel.Quit;
        Application.MessageBox('人事资料导入完毕！', '提示', MB_OK +
          MB_ICONINFORMATION);
        frm_rszlFromExcel.Close;
      end else
      begin
        frm_rszlFromExcel.bbtn_excel.Enabled := True;
        ist := False;    
        MSExcel.Quit;
        Application.MessageBox('此 Excel 文件不是人事资料数据格式！', '提示', MB_OK +
          MB_ICONINFORMATION);
      end; 
  except
    frm_rszlFromExcel.bbtn_excel.Enabled := True;
    frm_rszlFromExcel.bbtn_Close.Enabled := True;
    ist := False;
    MSExcel.Quit;
    Application.MessageBox('导入失败，请检查 Excel 文件格式及数据是否正确。', 
      '提示', MB_OK + MB_ICONINFORMATION);
  end;
end;

procedure Tfrm_rszlFromExcel.bbtn_excelClick(Sender: TObject);
var
  hThread:Thandle;//定义一个句柄
  ThreadID:DWord;
begin
  if fileedt1.Text='' then
  begin
    Application.MessageBox('请选择需要导入的 Excel 文件。', '提示', MB_OK + 
      MB_ICONINFORMATION);
    Exit;  
  end;
  ist := True;
  bbtn_excel.Enabled := False;
  bbtn_Close.Enabled := False;
  try
    //创建线程，同时线程函数被调用
    hthread:=CreateThread(nil,0,@MyExcel,nil,0,ThreadID);
    if hThread=0 then
    begin
      Application.MessageBox('线程创建失败！', '提示', MB_OK +
        MB_ICONINFORMATION);
    end;
  except
    bbtn_excel.Enabled := True;
    bbtn_Close.Enabled := True;
    ist := False;
    Exit;
  end;
end;

procedure Tfrm_rszlFromExcel.bbtn_CloseClick(Sender: TObject);
begin
  Close;
end;

procedure Tfrm_rszlFromExcel.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin  
  qry1.Close;
  qry2.Close;
  qry3.Close;
end;

procedure Tfrm_rszlFromExcel.FormCreate(Sender: TObject);
begin
  ist := False;
  lbl2.Caption := '                        ';
end;

procedure Tfrm_rszlFromExcel.FormCloseQuery(Sender: TObject;
  var CanClose: Boolean);
begin
  if ist then
  begin
    CanClose := False;
  end else
  begin
    CanClose := True;
  end;
end;

end.