通过DBGRidEh直接导出到Excel: Procedure TurnToExcel(TmpDBGrid:TDBGridEh); var MyExcel: Variant; WorkBook: OleVariant; WorkSheet: OleVariant; i,j:integer; xlsfilename :string; Savedialog1 :TSaveDialog; begin if Application.MessageBox('确认导出到Excel?',App_caption,MB_ICONQUESTION+MB_YESNO)=mrno then Abort; SaveDialog1 :=TSaveDialog.create(Application); SaveDialog1.Filter := 'Excel文件(*.xls)|*.XLS'; if savedialog1.Execute then if savedialog1.FileName <>'' then begin xlsfilename :=savedialog1.FileName; try MyExcel:=CreateOleObject('Excel.Application'); MyExcel.Application.WorkBooks.Add; MyExcel.Caption:='将数据导入到EXCEL表中'; MyExcel.Application.Visible:=false; WorkBook:=MyExcel.Application.workbooks[1]; worksheet:=workbook.worksheets.item[1]; except Application.MessageBox('EXCEL不存在!',App_caption,MB_ICONERROR+MB_OK); Savedialog1.Free; workBook.Saved := True; WorkBook.close; MyExcel.Quit;//释放VARIANT变量 MyExcel:=Unassigned; end; i:=1; Frm_system_progress :=TFrm_system_progress.create(Application); Try with TmpDBGrid.DataSource.DataSet do begin Open; DisableControls; with Frm_system_progress.ProgressBar_temp do begin min :=0; max :=TmpDBGrid.Columns.Count*recordcount; Position :=0; end; Frm_system_progress.label_progress.caption :='正在导出到Excel...'; Frm_system_progress.Show; Frm_system_progress.update; for j:=0 to TmpDBGrid.Columns.Count-1 do begin if TmpDBGrid.Columns[j].Visible=true then worksheet.cells[1,j+1]:=TmpDBGrid.Columns[j].Title.Caption; end; First; while not Eof do begin inc(i); for j:=0 to TmpDBGrid.Columns.Count-1 do begin if TmpDBGrid.Columns[j].Visible=true then begin worksheet.cells[i,j+1].NumberFormatLocal :='@'; worksheet.cells[i,j+1]:=TmpDBGrid.Columns[j].Field.AsString ; Frm_system_progress.ProgressBar_temp.StepIt; end; end; next; end; EnableControls; end; WorkBook.saveas(XlsFileName); Frm_system_progress.ProgressBar_temp.position :=TmpDBGrid.Columns.Count*TmpDBGrid.DataSource.DataSet.RecordCount; Application.MessageBox('导出到Excel成功!',App_caption,MB_ICONINFORMATION+MB_OK); Frm_system_progress.Free; MyExcel.Quit; MyExcel := Unassigned; Savedialog1.Free; except Application.MessageBox('导出到Excel失败!',App_caption,MB_ICONWARNING+MB_OK); workBook.Saved := True; WorkBook.close; MyExcel.Quit;//释放VARIANT变量 MyExcel:=Unassigned; Frm_system_progress.Free; Savedialog1.Free; end; end;end;
如果不能,那就要自己操作了,
遍历DbGrid,然后一行一行地写入Excel文件。
...
SaveDBGridEhToExportFile(TDBGridEhExportAsXLS,DBGridEh,ls_string,true);
Procedure TurnToExcel(TmpDBGrid:TDBGridEh);
var
MyExcel: Variant;
WorkBook: OleVariant;
WorkSheet: OleVariant;
i,j:integer;
xlsfilename :string;
Savedialog1 :TSaveDialog;
begin
if Application.MessageBox('确认导出到Excel?',App_caption,MB_ICONQUESTION+MB_YESNO)=mrno then
Abort;
SaveDialog1 :=TSaveDialog.create(Application);
SaveDialog1.Filter := 'Excel文件(*.xls)|*.XLS';
if savedialog1.Execute then
if savedialog1.FileName <>'' then
begin
xlsfilename :=savedialog1.FileName;
try
MyExcel:=CreateOleObject('Excel.Application');
MyExcel.Application.WorkBooks.Add;
MyExcel.Caption:='将数据导入到EXCEL表中';
MyExcel.Application.Visible:=false;
WorkBook:=MyExcel.Application.workbooks[1];
worksheet:=workbook.worksheets.item[1];
except
Application.MessageBox('EXCEL不存在!',App_caption,MB_ICONERROR+MB_OK);
Savedialog1.Free;
workBook.Saved := True;
WorkBook.close;
MyExcel.Quit;//释放VARIANT变量
MyExcel:=Unassigned;
end;
i:=1;
Frm_system_progress :=TFrm_system_progress.create(Application);
Try
with TmpDBGrid.DataSource.DataSet do
begin
Open;
DisableControls;
with Frm_system_progress.ProgressBar_temp do
begin
min :=0;
max :=TmpDBGrid.Columns.Count*recordcount;
Position :=0;
end;
Frm_system_progress.label_progress.caption :='正在导出到Excel...';
Frm_system_progress.Show;
Frm_system_progress.update;
for j:=0 to TmpDBGrid.Columns.Count-1 do
begin
if TmpDBGrid.Columns[j].Visible=true then
worksheet.cells[1,j+1]:=TmpDBGrid.Columns[j].Title.Caption;
end;
First;
while not Eof do
begin
inc(i);
for j:=0 to TmpDBGrid.Columns.Count-1 do
begin
if TmpDBGrid.Columns[j].Visible=true then
begin
worksheet.cells[i,j+1].NumberFormatLocal :='@';
worksheet.cells[i,j+1]:=TmpDBGrid.Columns[j].Field.AsString ;
Frm_system_progress.ProgressBar_temp.StepIt;
end;
end;
next;
end;
EnableControls;
end;
WorkBook.saveas(XlsFileName);
Frm_system_progress.ProgressBar_temp.position :=TmpDBGrid.Columns.Count*TmpDBGrid.DataSource.DataSet.RecordCount;
Application.MessageBox('导出到Excel成功!',App_caption,MB_ICONINFORMATION+MB_OK);
Frm_system_progress.Free;
MyExcel.Quit;
MyExcel := Unassigned;
Savedialog1.Free;
except
Application.MessageBox('导出到Excel失败!',App_caption,MB_ICONWARNING+MB_OK);
workBook.Saved := True;
WorkBook.close;
MyExcel.Quit;//释放VARIANT变量
MyExcel:=Unassigned;
Frm_system_progress.Free;
Savedialog1.Free;
end;
end;end;
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, DB, DBTables, StdCtrls, ExtCtrls, Grids, DBGrids, Excel2000,
OleServer, Mask, DBCtrls, ComCtrls;////////////////////////////////////////////////////////////////////////////////
Procedure DBToExcel
( ModuleName : String ; // Excel 模块文件名 *.Xlt
FileName: String ; // Excel 目标文件名 *.Xls
DBGrid : TDBGrid ; // TDBGrid 控件名
Row : Integer ; // 起始行
Col : Integer // 起始列
) ;
////////////////////////////////////////////////////////////////////////////////implementation////////////////////////////////////////////////////////////////////////////////
//函数名 : DBToExcel
//
// 功能 : 将TDBGrid 的数据导入到从某模板文件建立的 Excel 文档中,
// 举例 : DBToExcel( 'C:\ReportTable.Xlt', 'C:\ReportTable.Xls',
// DBGrid1, 5, 1 ) ; //
// 注意 :所指定的模板文件*.xlt, 必须已存在
////////////////////////////////////////////////////////////////////////////////
Procedure DBToExcel
( ModuleName : String ; // Excel 模块文件名 *.Xlt
FileName: String ; // Excel 目标文件名 *.Xls
DBGrid : TDBGrid ; // TDBGrid 控件名
Row : Integer ; // 起始行
Col : Integer // 起始列
) ;
Var
ExlApp : TExcelApplication ;
ExlBook : TExcelWorkBook ;
ExlSheet : TExcelWorkSheet ;
i, j : Integer ;
temp1, temp2 : OleVariant ;
Begin
// 判断指定的模板文件是否存在
ModuleName := Trim(ModuleName) ;
If FileExists(ModuleName)= False Then
begin
ShowMessage( '模板文件: '+ ModuleName + ' 不存在 !' ) ;
Exit ;
end ; // 创建 Excel 服务器控件
ExlApp := TExcelApplication.Create(nil);
ExlBook := TExcelWorkBook.Create (nil);
ExlSheet := TExcelWorkSheet.Create (nil) ; // 连接 Excel 服务器
Try
ExlApp.Connect ;
Except
ShowMessage ('连接失败,可能没装 Excle') ;
Abort ;
End ; // 从指定的模板文件*.xlt 新建一 Excel 文档
temp1 := ModuleName ; temp2 := 1 ;
ExlApp.Workbooks.Add( temp1, temp2) ;
ExlBook.ConnectTo(ExlApp.Workbooks[1] );
ExlSheet.ConnectTo(ExlBook.Worksheets[1] As _WorkSheet ); // 把 TDBGrid 的数据导入 Excel 中
DBGrid.DataSource.DataSet.First ;
i := Row ;
While Not(DBGrid.DataSource.DataSet.Eof) Do
Begin
For j:=Col To DBGrid.FieldCount+ Col-1 Do
ExlSheet.Cells.Item[i,j]:= DBGrid.Fields[j-col].AsString ;
DBGrid.DataSource.DataSet.Next ;
i := i+1 ;
End ; // 保存到 指定的文件
FileName := Trim(FileName) ;
IF FileExists(FileName) Then DeleteFile(FileName) ;
ExlSheet.SaveAs(FileName);
ExlSheet.PrintPreview ; // 打印预览
// 关闭 Excel 服务器
ExlApp.Disconnect ;
ExlApp.Quit ;
ExlApp.Free; ExlBook.Free; ExlSheet.Free;End; // End of The Procedure DbToExcel_2() /////////////////////////////////////
end.