Excel Export, Import 逻辑实现

 unit Unit2; interface uses windows, classes, variants, ComObj, sysUtils, DB, ADODB, Forms, ExtCtrls; Type TExlObj = class Timer1: TTimer; private FExlApp: OLEVariant; FExlWBook: OLEVariant; FExlWSheet: OLEVariant; FFileName: String; Procedure Timer1Handle; Public Property FileName: string Read FFileName Write FFileName; Property ExlApp: OLEVariant Read FExlApp Write FExlApp; Property ExlWBook: OLEVariant Read FExlWBook Write FExlWBook; Property EXlWSheet: OLEVariant Read FExlWSheet Write FExlWSheet; Procedure CreateExlApp; Procedure DBToExl(ADataSet: TDataSet; sRow, sColumn: Integer); Procedure ExlToDB(ADataSet: TDataSet; sRow, sColumn: Integer); Procedure DestoryExlApp; end; implementation uses unit1; procedure TExlObj.CreateExlApp;begin FExlApp := CreateOleObject('Excel.Application'); try FExlWBook := FExlApp.Application; FExlWBook.Visible := false; FExlWBook := FExlApp.WorkBooks.Add; FExlWSheet := FExlWBook.ActiveSheet; except Raise Exception.Create('Excel初始化错误.'); end;end; procedure TExlObj.DBToExl(ADataSet: TDataSet; sRow, sColumn: Integer);var i, j: Integer;begin CreateExlApp; Form1.StatusBar1.Panels[2].Text := '开始执行...'; Form1.Gauge1.MinValue := 0; Form1.Gauge1.MaxValue := Adataset.RecordCount; for i:=0 to ADataSet.FieldCount-1 do FExlApp.Cells[1, i+1] := ADataSet.Fields.Fields[i].FieldName; i := 2; ADataSet.First; while not ADataSet.Eof do begin j := 1; Application.ProcessMessages; ADataSet.DisableControls; Form1.StatusBar1.Panels[2].Text := '当前执行状态：'+ FormatFloat('0.', (i-2)/Adataset.RecordCount * 100)+'%'; Form1.Gauge1.AddProgress(1); Form1.Gauge1.Progress := i; while j <= ADataSet.FieldCount do begin FExlApp.cells[i, j] := ADataset.Fields.Fields[j-1].Value; j := j + 1; end; ADataSet.Next; i := i + 1; end; ADataSet.EnableControls; FExlWBook.Saveas(FFilename); DestoryExlApp; Form1.StatusBar1.Panels[2].Text := '';end; procedure TExlObj.DestoryExlApp;begin FExlApp.visible := false; FExlWBook.Close; FExlApp.Quit; FExlApp := unassigned;end; procedure TExlObj.ExlToDB(ADataSet: TDataSet; sRow, sColumn: Integer);var i, j, t, n: integer; tempstr: string;begin CreateExlApp; FExlApp.workBooks.Open(FFileName); t := FExlWSheet.UsedRange.Rows.Count; for i:=2 to t do begin Form1.StatusBar1.Panels[2].Text:='执行状况：'+FormatFloat('0.', (i / t) * 100)+'%'; Application.ProcessMessages; n:=0; ADataSet.Append; for j:=1 to FExlWSheet.UsedRange.Columns.Count do begin if Trim(FExlWSheet.Cells[i, j].Value) = '' then tempstr := '0' else tempstr := Trim(FExlWSheet.Cells[i, j].Value); ADataSet.Fields[n+1].Value := tempstr; n := n+1; end; if n<>0 then ADataSet.Next; end;end;end.