导出 DBGrid 内容到 Excel 示例中,dbgrid(DBGrid1)具有一个弹出菜单,它给出两个选项:"Send to Excel" 和 "Copy". 下面给出用到的方法: //注意:下面的方法必须包含 ComObj, Excel97 单元 //----------------------------------------------------------- // if toExcel = false, export dbgrid contents to the Clipboard // if toExcel = true, export dbgrid to Microsoft Excel procedure ExportDBGrid(toExcel: Boolean); var bm: TBook; col, row: Integer; sline: String; mem: TMemo; ExcelApp: Variant; begin Screen.Cursor := crHourglass; DBGrid1.DataSource.DataSet.DisableControls; bm := DBGrid1.DataSource.DataSet.GetBook; DBGrid1.DataSource.DataSet.First;
// create the Excel object if toExcel then begin ExcelApp := CreateOleObject('Excel.Application'); ExcelApp.WorkBooks.Add(xlWBatWorkSheet); ExcelApp.WorkBooks[1].WorkSheets[1].Name := 'Grid Data'; end;
// First we send the data to a memo // works faster than doing it directly to Excel mem := TMemo.Create(Self); mem.Visible := false; mem.Parent := MainForm; mem.Clear; sline := '';
// add the info for the column names for col := 0 to DBGrid1.FieldCount-1 do sline := sline + DBGrid1.Fields[col].DisplayLabel + #9; mem.Lines.Add(sline);
// get the data into the memo for row := 0 to DBGrid1.DataSource.DataSet.RecordCount-1 do begin sline := ''; for col := 0 to DBGrid1.FieldCount-1 do sline := sline + DBGrid1.Fields[col].AsString + #9; mem.Lines.Add(sline); DBGrid1.DataSource.DataSet.Next; end;
// we copy the data to the clipboard mem.SelectAll; mem.CopyToClipboard;
// if needed, send it to Excel // if not, we already have it in the clipboard if toExcel then begin ExcelApp.Workbooks[1].WorkSheets['Grid Data'].Paste; ExcelApp.Visible := true; end;
示例中,dbgrid(DBGrid1)具有一个弹出菜单,它给出两个选项:"Send to Excel" 和 "Copy".
下面给出用到的方法:
//注意:下面的方法必须包含 ComObj, Excel97 单元
//-----------------------------------------------------------
// if toExcel = false, export dbgrid contents to the Clipboard
// if toExcel = true, export dbgrid to Microsoft Excel
procedure ExportDBGrid(toExcel: Boolean);
var
bm: TBook;
col, row: Integer;
sline: String;
mem: TMemo;
ExcelApp: Variant;
begin
Screen.Cursor := crHourglass;
DBGrid1.DataSource.DataSet.DisableControls;
bm := DBGrid1.DataSource.DataSet.GetBook;
DBGrid1.DataSource.DataSet.First;
// create the Excel object
if toExcel then
begin
ExcelApp := CreateOleObject('Excel.Application');
ExcelApp.WorkBooks.Add(xlWBatWorkSheet);
ExcelApp.WorkBooks[1].WorkSheets[1].Name := 'Grid Data';
end;
// First we send the data to a memo
// works faster than doing it directly to Excel
mem := TMemo.Create(Self);
mem.Visible := false;
mem.Parent := MainForm;
mem.Clear;
sline := '';
// add the info for the column names
for col := 0 to DBGrid1.FieldCount-1 do
sline := sline + DBGrid1.Fields[col].DisplayLabel + #9;
mem.Lines.Add(sline);
// get the data into the memo
for row := 0 to DBGrid1.DataSource.DataSet.RecordCount-1 do
begin
sline := '';
for col := 0 to DBGrid1.FieldCount-1 do
sline := sline + DBGrid1.Fields[col].AsString + #9;
mem.Lines.Add(sline);
DBGrid1.DataSource.DataSet.Next;
end;
// we copy the data to the clipboard
mem.SelectAll;
mem.CopyToClipboard;
// if needed, send it to Excel
// if not, we already have it in the clipboard
if toExcel then
begin
ExcelApp.Workbooks[1].WorkSheets['Grid Data'].Paste;
ExcelApp.Visible := true;
end;
FreeAndNil(mem);
// FreeAndNil(ExcelApp);
DBGrid1.DataSource.DataSet.GotoBook(bm);
DBGrid1.DataSource.DataSet.FreeBook(bm);
DBGrid1.DataSource.DataSet.EnableControls;
Screen.Cursor := crDefault;
end;
Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
StdCtrls, Grids, DBGrids, Db, DBTables, Excel97, OleServer;type
TForm1 = class(TForm)
Button1: TButton;
Button2: TButton;
Button3: TButton;
Button4: TButton;
Button5: TButton;
Edit1: TEdit;
DataSource1: TDataSource;
Query1: TQuery;
DBGrid1: TDBGrid;
ExcelWorksheet1: TExcelWorksheet;
ExcelWorkbook1: TExcelWorkbook;
ExcelApplication1: TExcelApplication;
Label1: TLabel;
procedure Button5Click(Sender: TObject);
procedure Button1Click(Sender: TObject);
procedure Button2Click(Sender: TObject);
procedure Button3Click(Sender: TObject);
procedure Button4Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;var
Form1: TForm1;implementation{$R *.DFM}procedure TForm1.Button5Click(Sender: TObject);
begin
Query1.Close;
Query1.SQL.Clear;
Query1.SQL.Text :=Edit1.Text ;
Query1.Open;
end;procedure TForm1.Button1Click(Sender: TObject);
var
i,row,column:integer;
begin
Try
ExcelApplication1.Connect;
Except
MessageDlg('Excel may not be installed',
mtError, [mbOk], 0);
Abort;
End;
ExcelApplication1.Visible[0]:=True;
ExcelApplication1.Caption:='Excel Application';
ExcelApplication1.Workbooks.Add(Null,0);
ExcelWorkbook1.ConnectTo
(ExcelApplication1.Workbooks[1]);
ExcelWorksheet1.ConnectTo
(ExcelWorkbook1.Worksheets[1] as _Worksheet);
Query1.Open;
row:=1;
column:=1;
for i:=0 to query1.FieldCount -1 do //列标题
begin
ExcelWorksheet1.Cells.Item[row,column]:=dbgrid1.Fields[i].DisplayLabel ;
column:=column+1;
end;row:=row+1;
While Not(Query1.Eof) do //表内容
begin
column:=1;
for i:=1 to Query1.FieldCount do
begin
ExcelWorksheet1.Cells.Item[row,column]:=Query1.fields[i-1].AsString;
column:=column+1;
end;
Query1.Next;
row:=row+1;
end;end;procedure TForm1.Button2Click(Sender: TObject);
begin
ExcelWorksheet1.PrintPreview;
end;procedure TForm1.Button3Click(Sender: TObject);
begin
ExcelWorksheet1.PrintOut;
end;procedure TForm1.Button4Click(Sender: TObject);
begin
ExcelApplication1.Disconnect;
ExcelApplication1.Quit;
end;end.