function ExportDBGridToExcel(Args: array of const): Boolean; var App: Variant; Sheet: Variant; I: Integer; passCol: Integer; bm: TBook; Col, Row: Integer; begin Result := False; try App := CreateOleObject('Excel.Application'); App.WorkBooks.Add; App.SheetsInNewWorkbook := High(Args) + 1; except Exit; end; Screen.Cursor := crSQLWait; try for I := Low(Args) to High(Args) do begin Sheet := App.Workbooks[1].WorkSheets[I+1]; with TDBGrid(Args[I].VObject), DataSource do begin bm := DataSet.GetBook(); DataSet.DisableControls; DataSet.first; try passCol := 0; for Col := 0 to Columns.Count - 1 do begin if Columns.Items[Col].Visible then Sheet.Cells[1, Col + 1 - passCol] := Columns.Items[Col].Title.Caption else Inc(passCol); end; Row := 1;{从第1行} while not DataSet.Eof do begin passCol := 0; for Col := 0 to Columns.Count - 1 do begin if Columns.Items[Col].Visible then Sheet.Cells[Row + 1, Col + 1 - passCol] := Columns.Items[Col].Field.AsString else Inc(passCol); end; Inc(Row); DataSet.Next; SendMessage(Application.MainForm.Handle, WM_USER + $F5, Row, DataSet.RecordCount); end; finally DataSet.GotoBook(bm); DataSet.FreeBook(bm); DataSet.EnableControls; end; end; end; finally App.Visible := True; Screen.Cursor := crDefault; end; end;
procedure TFrm_Main.ExportDBGrid(FdbGrid: TDBgrideh; toExcel: Boolean); var bm: TBook; col, row: Integer; sline, rownum: string; mem: TMemo; ExcelApp: Variant; reccount, RowCount: Integer; MyDBSumList: TDBSumList; begin reccount := 0; MyDBSumList:=TDBSumList.Create(nil); MyDBSumList.DataSet := FDBGrid.DataSource.DataSet; MyDBSumList.SumCollection.Add; MyDBSumList.SumCollection.Items[0].GroupOperation := Gocount; MyDBSumList.SumCollection.Items[0].FieldName := FDBGrid.Fields[0].FullName; MyDBSumList.Active := True; RowCount := trunc(MyDBSumList.SumCollection.Items[0].SumValue); MyDBSumList.Free; Screen.Cursor := crHourglass; FDBGrid.DataSource.DataSet.DisableControls; bm := FDBGrid.DataSource.DataSet.GetBook; FDBGrid.DataSource.DataSet.First; if toExcel then begin ExcelApp := CreateOleObject('Excel.Application'); ExcelApp.WorkBooks.Add(xlWBatWorkSheet); end; mem := TMemo.Create(nil); mem.Parent := Fdbgrid.Parent; mem.Visible := False; mem.Clear; sline := ''; for col := 0 to FDBGrid.Columns.Count - 1 do sline := sline + FDBGrid.Columns[col].Title.Caption + #9; mem.Lines.Add(sline); for row := 1 to RowCount do begin sline := ''; for col := 0 to FDBGrid.FieldCount - 1 do begin sline := sline + FDBGrid.Columns[col].DisplayText + #9; end; mem.Lines.Add(sline); if row mod 150 = 0 then begin mem.SelectAll; mem.CopyToClipboard; if toExcel then begin rownum := 'A' + inttostr(reccount + 1); ExcelApp.Workbooks[1].WorkSheets[1].Range[rownum].PasteSpecial; mem.Clear; Clipboard.Clear; reccount := row + 1; end; end; FDBGrid.DataSource.DataSet.Next; end; mem.SelectAll; mem.CopyToClipboard; if toExcel then begin rownum := 'A' + inttostr(reccount + 1); ExcelApp.Workbooks[1].WorkSheets[1].Range[rownum].PasteSpecial; ExcelApp.Visible := True; end; FreeAndNil(mem); FDBGrid.DataSource.DataSet.GotoBook(bm); FDBGrid.DataSource.DataSet.FreeBook(bm); FDBGrid.DataSource.DataSet.EnableControls; Screen.Cursor := crDefault; end;以上代码在D7下运行正确!
var
App: Variant;
Sheet: Variant;
I: Integer;
passCol: Integer;
bm: TBook;
Col, Row: Integer;
begin
Result := False; try
App := CreateOleObject('Excel.Application');
App.WorkBooks.Add;
App.SheetsInNewWorkbook := High(Args) + 1;
except
Exit;
end; Screen.Cursor := crSQLWait;
try
for I := Low(Args) to High(Args) do
begin
Sheet := App.Workbooks[1].WorkSheets[I+1]; with TDBGrid(Args[I].VObject), DataSource do
begin
bm := DataSet.GetBook();
DataSet.DisableControls;
DataSet.first;
try
passCol := 0;
for Col := 0 to Columns.Count - 1 do
begin
if Columns.Items[Col].Visible then
Sheet.Cells[1, Col + 1 - passCol] := Columns.Items[Col].Title.Caption
else Inc(passCol);
end; Row := 1;{从第1行}
while not DataSet.Eof do
begin
passCol := 0;
for Col := 0 to Columns.Count - 1 do
begin
if Columns.Items[Col].Visible then
Sheet.Cells[Row + 1, Col + 1 - passCol] := Columns.Items[Col].Field.AsString
else Inc(passCol);
end; Inc(Row);
DataSet.Next;
SendMessage(Application.MainForm.Handle, WM_USER + $F5, Row, DataSet.RecordCount);
end;
finally
DataSet.GotoBook(bm);
DataSet.FreeBook(bm);
DataSet.EnableControls;
end;
end;
end; finally
App.Visible := True;
Screen.Cursor := crDefault;
end;
end;
var
bm: TBook;
col, row: Integer;
sline, rownum: string;
mem: TMemo;
ExcelApp: Variant;
reccount, RowCount: Integer;
MyDBSumList: TDBSumList;
begin
reccount := 0;
MyDBSumList:=TDBSumList.Create(nil);
MyDBSumList.DataSet := FDBGrid.DataSource.DataSet;
MyDBSumList.SumCollection.Add;
MyDBSumList.SumCollection.Items[0].GroupOperation := Gocount;
MyDBSumList.SumCollection.Items[0].FieldName := FDBGrid.Fields[0].FullName;
MyDBSumList.Active := True;
RowCount := trunc(MyDBSumList.SumCollection.Items[0].SumValue);
MyDBSumList.Free;
Screen.Cursor := crHourglass;
FDBGrid.DataSource.DataSet.DisableControls;
bm := FDBGrid.DataSource.DataSet.GetBook;
FDBGrid.DataSource.DataSet.First;
if toExcel then
begin
ExcelApp := CreateOleObject('Excel.Application');
ExcelApp.WorkBooks.Add(xlWBatWorkSheet);
end;
mem := TMemo.Create(nil);
mem.Parent := Fdbgrid.Parent;
mem.Visible := False;
mem.Clear;
sline := '';
for col := 0 to FDBGrid.Columns.Count - 1 do
sline := sline + FDBGrid.Columns[col].Title.Caption + #9;
mem.Lines.Add(sline);
for row := 1 to RowCount do
begin
sline := '';
for col := 0 to FDBGrid.FieldCount - 1 do
begin
sline := sline + FDBGrid.Columns[col].DisplayText + #9;
end;
mem.Lines.Add(sline);
if row mod 150 = 0 then
begin
mem.SelectAll;
mem.CopyToClipboard;
if toExcel then
begin
rownum := 'A' + inttostr(reccount + 1);
ExcelApp.Workbooks[1].WorkSheets[1].Range[rownum].PasteSpecial;
mem.Clear;
Clipboard.Clear;
reccount := row + 1;
end;
end;
FDBGrid.DataSource.DataSet.Next;
end;
mem.SelectAll;
mem.CopyToClipboard;
if toExcel then
begin
rownum := 'A' + inttostr(reccount + 1);
ExcelApp.Workbooks[1].WorkSheets[1].Range[rownum].PasteSpecial;
ExcelApp.Visible := True;
end;
FreeAndNil(mem);
FDBGrid.DataSource.DataSet.GotoBook(bm);
FDBGrid.DataSource.DataSet.FreeBook(bm);
FDBGrid.DataSource.DataSet.EnableControls;
Screen.Cursor := crDefault;
end;以上代码在D7下运行正确!