procedure DbGridToExcel(cx_dbgr:TDBGrid); var i,j,m:integer; ExcelApp:Variant; FHint:TFHint; begin if cx_dbgr.DataSource=nil then exit; if cx_dbgr.DataSource.DataSet=nil then exit; if not cx_dbgr.DataSource.DataSet.Active then exit; if cx_dbgr.DataSource.DataSet.RecordCount<=0 then exit; FHint:=TFhint.Create(application); FHint.Show; FHint.Update; try ExcelApp := CreateOleObject( 'Excel.Application' ); ExcelApp.WorkBooks.Add; ExcelApp.WorkSheets.Add; // ExcelApp.ActiveSheet:='Sheet1'; ExcelApp.Visible:=true; {scExcelExport1.Dataset:=cx_dbgr.DataSource.DataSet; scExcelExport1.ExportDataset; scExcelExport1.Disconnect;} j:=2; with cx_dbgr.DataSource.DataSet do begin cx_dbgr.DataSource.DataSet.First; cx_dbgr.DataSource.DataSet.DisableControls; for i:=0 to cx_dbgr.Columns.Count-1 do begin for m:=0 to cx_dbgr.Columns.Count-1 do begin if cx_dbgr.DataSource.DataSet.Fields[i].FieldName=cx_dbgr.Columns[m].FieldName then ExcelApp.WorkSheets[1].Cells[1,i+1].Value :=cx_dbgr.Columns[m].Title.Caption; end; end; ExcelApp.ActiveSheet.Rows[1].Font.Name:='隶书'; ExcelApp.ActiveSheet.Rows[1].Font.Size:=12; while not cx_dbgr.DataSource.DataSet.Eof do begin for i:=0 to FieldCount-1 do begin ExcelApp.WorkSheets[1].Cells[j,i+1].Value :=Fields[i].AsVariant; end; Next; j:=j+1; end; end; finally cx_dbgr.DataSource.DataSet.EnableControls; Fhint.Free; end; end; ///------------------------------procedure DataSetToExcel(dt:TDataSet); var i,j:integer; ExcelApp:Variant; FHint:TFHint; begin // if cx_dbgr.DataSource=nil then exit; if dt=nil then exit; if not dt.Active then exit; if dt.RecordCount<=0 then exit; FHint:=TFhint.Create(application); FHint.Show; FHint.Update; try ExcelApp := CreateOleObject( 'Excel.Application' ); ExcelApp.WorkBooks.Add; ExcelApp.WorkSheets.Add; // ExcelApp.ActiveSheet:='Sheet1'; ExcelApp.Visible:=true; {scExcelExport1.Dataset:=cx_dbgr.DataSource.DataSet; scExcelExport1.ExportDataset; scExcelExport1.Disconnect;} j:=2; with dt do begin First; DisableControls; for i:=0 to dt.FieldCount-1 do begin ExcelApp.WorkSheets[1].Cells[1,i+1].Value :=dt.Fields[i].DisplayName; end; ExcelApp.ActiveSheet.Rows[1].Font.Name:='隶书'; ExcelApp.ActiveSheet.Rows[1].Font.Size:=12; while not dt.Eof do begin for i:=0 to FieldCount-1 do begin ExcelApp.WorkSheets[1].Cells[j,i+1].Value :=Fields[i].AsVariant; end; Next; j:=j+1; end; end; finally dt.EnableControls; FHint.Free; end; end;
导出数据到Excel有好多种方法,楼上采用的是OLE方式。再提供一种给你参考,也是采用OLE方式,由于使用了Excel内置的取数据集功能,速度快一些:procedure RsToExcel(AdoQuery1: TAdoQuery; ExcelFileName: string); var ExcelApp, Book, Sheet, Range, xlQuery: OleVariant; begin if not ADOQuery1.Active then ADOQuery1.Open; ExcelApp := CreateOleObject( 'Excel.Application' ); //创建 try if ExcelApp.Workbooks.count < 1 then ExcelApp.WorkBooks.Add; Book := ExcelApp.Workbooks[1]; if Book.Worksheets.Count < 1 then Book.Worksheets.Add; Sheet := Book.Worksheets[1]; xlQuery := Sheet.QueryTables.Add(ADOQuery1.Recordset, Sheet.Range['A3']); //xlQuery.FieldNames := false; xlQuery.Refresh; Sheet.SaveAs(ExcelFileName); finally Sheet := Unassigned; Book := Unassigned; if not VarIsEmpty(ExcelApp) then ExcelApp.Quit; ExcelApp := Unassigned; if ADOQuery1.Active then ADOQuery1.Close; end; end;
var
i,j,m:integer;
ExcelApp:Variant;
FHint:TFHint;
begin
if cx_dbgr.DataSource=nil then exit;
if cx_dbgr.DataSource.DataSet=nil then exit;
if not cx_dbgr.DataSource.DataSet.Active then exit;
if cx_dbgr.DataSource.DataSet.RecordCount<=0 then exit; FHint:=TFhint.Create(application);
FHint.Show;
FHint.Update;
try
ExcelApp := CreateOleObject( 'Excel.Application' );
ExcelApp.WorkBooks.Add;
ExcelApp.WorkSheets.Add;
// ExcelApp.ActiveSheet:='Sheet1';
ExcelApp.Visible:=true;
{scExcelExport1.Dataset:=cx_dbgr.DataSource.DataSet;
scExcelExport1.ExportDataset;
scExcelExport1.Disconnect;}
j:=2;
with cx_dbgr.DataSource.DataSet do
begin
cx_dbgr.DataSource.DataSet.First;
cx_dbgr.DataSource.DataSet.DisableControls;
for i:=0 to cx_dbgr.Columns.Count-1 do
begin
for m:=0 to cx_dbgr.Columns.Count-1 do
begin
if cx_dbgr.DataSource.DataSet.Fields[i].FieldName=cx_dbgr.Columns[m].FieldName then
ExcelApp.WorkSheets[1].Cells[1,i+1].Value :=cx_dbgr.Columns[m].Title.Caption;
end;
end;
ExcelApp.ActiveSheet.Rows[1].Font.Name:='隶书';
ExcelApp.ActiveSheet.Rows[1].Font.Size:=12; while not cx_dbgr.DataSource.DataSet.Eof do
begin
for i:=0 to FieldCount-1 do
begin
ExcelApp.WorkSheets[1].Cells[j,i+1].Value :=Fields[i].AsVariant;
end;
Next;
j:=j+1;
end;
end;
finally
cx_dbgr.DataSource.DataSet.EnableControls;
Fhint.Free;
end;
end;
///------------------------------procedure DataSetToExcel(dt:TDataSet);
var
i,j:integer;
ExcelApp:Variant;
FHint:TFHint;
begin
// if cx_dbgr.DataSource=nil then exit;
if dt=nil then exit;
if not dt.Active then exit;
if dt.RecordCount<=0 then exit; FHint:=TFhint.Create(application);
FHint.Show;
FHint.Update;
try
ExcelApp := CreateOleObject( 'Excel.Application' );
ExcelApp.WorkBooks.Add;
ExcelApp.WorkSheets.Add;
// ExcelApp.ActiveSheet:='Sheet1';
ExcelApp.Visible:=true;
{scExcelExport1.Dataset:=cx_dbgr.DataSource.DataSet;
scExcelExport1.ExportDataset;
scExcelExport1.Disconnect;}
j:=2;
with dt do
begin
First;
DisableControls;
for i:=0 to dt.FieldCount-1 do
begin
ExcelApp.WorkSheets[1].Cells[1,i+1].Value :=dt.Fields[i].DisplayName;
end;
ExcelApp.ActiveSheet.Rows[1].Font.Name:='隶书';
ExcelApp.ActiveSheet.Rows[1].Font.Size:=12; while not dt.Eof do
begin
for i:=0 to FieldCount-1 do
begin
ExcelApp.WorkSheets[1].Cells[j,i+1].Value :=Fields[i].AsVariant;
end;
Next;
j:=j+1;
end;
end;
finally
dt.EnableControls;
FHint.Free;
end;
end;
如果在Delphi中,可以DBGridEh控件,它有一个功能可以把查询的结果导出多种格式,如.xls,.rtf,.txt等
var
ExcelApp, Book, Sheet, Range, xlQuery: OleVariant;
begin
if not ADOQuery1.Active then
ADOQuery1.Open; ExcelApp := CreateOleObject( 'Excel.Application' ); //创建
try
if ExcelApp.Workbooks.count < 1 then
ExcelApp.WorkBooks.Add;
Book := ExcelApp.Workbooks[1];
if Book.Worksheets.Count < 1 then
Book.Worksheets.Add;
Sheet := Book.Worksheets[1]; xlQuery := Sheet.QueryTables.Add(ADOQuery1.Recordset, Sheet.Range['A3']);
//xlQuery.FieldNames := false;
xlQuery.Refresh; Sheet.SaveAs(ExcelFileName);
finally
Sheet := Unassigned;
Book := Unassigned;
if not VarIsEmpty(ExcelApp) then
ExcelApp.Quit;
ExcelApp := Unassigned;
if ADOQuery1.Active then ADOQuery1.Close;
end;
end;
而这种用控件的方法不需要事先安装Excel你下载后看一下他自带的事例文件,里面有一个过程是专门导出其它格式的,这样你可以设置一个公共过程,只要有用到DBGridEh的地方,只要增加一个事件名,即可把列表框中的全部内部导出,也可以选择导出一部分数据。