如何将delphi的dbgrid导出为excel表.docx

1、如何将delphi的dbgrid导出为excel表unit unit2;interface uses forms,SysUtils,ComCtrls,DBGrids,DB,Dialogs,Messages,Windows,ComObj,Controls,ADODB,StdCtrls,Graphics;function ProgressBarform(max:integer):tProgressBar;function ExportToExcel(dbgrid:tdbgrid):boolean;implementationuses unit1;/生成一个显示进度条的窗体function Prog

2、ressBarform(max:integer):tProgressBar; var ProgressBar1:TProgressBar; form:tform; begin application.CreateForm(tform,form); form.Position:=poScreenCenter; form.BorderStyle:=bsnone; form.Height:=30; form.Width:=260; ProgressBar1:=TProgressBar.Create(form); ProgressBar1.Visible:=true; ProgressBar1.Smo

3、oth:=true; ProgressBar1.Max:=max; ProgressBar1.ParentWindow:=form.Handle; ProgressBar1.Height:=20; ProgressBar1.Width:=250; ProgressBar1.Left:=form.Left+5; ProgressBar1.Top:=form.Top+5; ProgressBar1.Step:=1; form.show; result:=ProgressBar1; end;/将DBGRID中的内容导入到EXCEL中function ExportToExcel(dbgrid:tdbg

4、rid):boolean; const xlNormal=-4143; var i,j,k:integer; str,filename:string; excel:OleVariant; SavePlace: TBookmark; savedialog:tsavedialog; ProgressBar1:TProgressBar; begin result:=false; filename:=; if dbgrid.DataSource.DataSet.RecordCount65536 then begin if application.messagebox(需要导出的数据过大，Excel最大

5、只能容纳65536行,是否还要继续？,询问,mb_yesno+mb_iconquestion)=idno then exit; end; screen.Cursor:=crHourGlass; try excel:=CreateOleObject(Excel.Application); excel.workbooks.add; except screen.cursor:=crDefault; showmessage(无法调用Excel！); exit; end; savedialog:=tsavedialog.Create(nil); savedialog.Filter:=Excel文件(*.

6、xls)|*.xls; if savedialog.Execute then begin if FileExists(savedialog.FileName) then try if application.messagebox(该文件已经存在，要覆盖吗？,询问,mb_yesno+mb_iconquestion)=idyes then DeleteFile(PChar(savedialog.FileName) else begin Excel.Quit; savedialog.free; screen.cursor:=crDefault; Exit; end; except Excel.Qui

7、t; savedialog.free; screen.cursor:=crDefault; Exit; end; filename:=savedialog.FileName; end; savedialog.free; application.ProcessMessages; if filename= then begin result:=false; Excel.Quit; screen.cursor:=crDefault; exit; end; k:=0; for i:=0 to dbgrid.Columns.count-1 do begin if dbgrid.Columns.Items

8、i.Visible then begin /Excel.Columnsk+1.ColumnWidth:=dbgrid.Columns.Itemsi.Title.Column.Width; excel.cells1,k+1:=dbgrid.Columns.Itemsi.Title.Caption; inc(k); end; end; dbgrid.DataSource.DataSet.DisableControls; saveplace:=dbgrid.DataSource.DataSet.GetBookmark; dbgrid.DataSource.dataset.First; i:=2; i

9、f dbgrid.DataSource.DataSet.recordcount65536 then ProgressBar1:=ProgressBarform(65536) else ProgressBar1:=ProgressBarform(dbgrid.DataSource.DataSet.recordcount); while not dbgrid.DataSource.dataset.Eof do begin k:=0; for j:=0 to dbgrid.Columns.count-1 do begin if dbgrid.Columns.Itemsj.Visible then b

10、egin excel.cellsi,k+1.NumberFormat:=; if not dbgrid.DataSource.dataset.fieldbyname(dbgrid.Columns.Itemsj.FieldName).isnull then begin str := dbgrid.DataSource.dataset.fieldbyname(dbgrid.Columns.Itemsj.FieldName).value; Excel.Cellsi, k + 1 := Str; end; inc(k); end else continue; end; if i=65536 then

11、break; inc(i); ProgressBar1.StepBy(1); dbgrid.DataSource.dataset.next; end; progressbar1.Owner.Free; application.ProcessMessages; dbgrid.DataSource.dataset.GotoBookmark(SavePlace); dbgrid.DataSource.dataset.EnableControls; try if copy(FileName,length(FileName)-3,4).xls then FileName:=FileName+.xls;

12、Excel.ActiveWorkbook.SaveAs(FileName,xlNormal, ,False,False); except Excel.Quit; screen.cursor:=crDefault; exit; end; /Excel.Visible := true; Excel.Quit; screen.cursor:=crDefault; Result:= true; end; end.引用是直接引用该函数就可以了ExportToExcel(dbgrid1)以下是原文含ado连接方式的函数，一般我们都用DBGRID中的内容导入到EXCEL中unit U_func;interf

13、ace uses forms,SysUtils,ComCtrls,DBGrids,DB,Dialogs,Messages,Windows,ComObj,Controls,ADODB,StdCtrls,Graphics;function ProgressBarform(max:integer):tProgressBar;function ExportToExcel(dbgrid:tdbgrid):boolean;function queryExportToExcel(queryexport:tadoquery):boolean;implementation/生成一个显示进度条的窗体functio

14、n ProgressBarform(max:integer):tProgressBar; var ProgressBar1:TProgressBar; form:tform; begin application.CreateForm(tform,form); form.Position:=poScreenCenter; form.BorderStyle:=bsnone; form.Height:=30; form.Width:=260; ProgressBar1:=TProgressBar.Create(form); ProgressBar1.Visible:=true; ProgressBa

15、r1.Smooth:=true; ProgressBar1.Max:=max; ProgressBar1.ParentWindow:=form.Handle; ProgressBar1.Height:=20; ProgressBar1.Width:=250; ProgressBar1.Left:=form.Left+5; ProgressBar1.Top:=form.Top+5; ProgressBar1.Step:=1; form.show; result:=ProgressBar1; end;/将DBGRID中的内容导入到EXCEL中function ExportToExcel(dbgri

16、d:tdbgrid):boolean; const xlNormal=-4143; var i,j,k:integer; str,filename:string; excel:OleVariant; SavePlace: TBookmark; savedialog:tsavedialog; ProgressBar1:TProgressBar; begin result:=false; filename:=; if dbgrid.DataSource.DataSet.RecordCount65536 then begin if application.messagebox(需要导出的数据过大，E

17、xcel最大只能容纳65536行,是否还要继续？,询问,mb_yesno+mb_iconquestion)=idno then exit; end; screen.Cursor:=crHourGlass; try excel:=CreateOleObject(Excel.Application); excel.workbooks.add; except screen.cursor:=crDefault; showmessage(无法调用Excel！); exit; end; savedialog:=tsavedialog.Create(nil); savedialog.Filter:=Exce

18、l文件(*.xls)|*.xls; if savedialog.Execute then begin if FileExists(savedialog.FileName) then try if application.messagebox(该文件已经存在，要覆盖吗？,询问,mb_yesno+mb_iconquestion)=idyes then DeleteFile(PChar(savedialog.FileName) else begin Excel.Quit; savedialog.free; screen.cursor:=crDefault; Exit; end; except Exc

19、el.Quit; savedialog.free; screen.cursor:=crDefault; Exit; end; filename:=savedialog.FileName; end; savedialog.free; application.ProcessMessages; if filename= then begin result:=false; Excel.Quit; screen.cursor:=crDefault; exit; end; k:=0; for i:=0 to dbgrid.Columns.count-1 do begin if dbgrid.Columns

20、.Itemsi.Visible then begin /Excel.Columnsk+1.ColumnWidth:=dbgrid.Columns.Itemsi.Title.Column.Width; excel.cells1,k+1:=dbgrid.Columns.Itemsi.Title.Caption; inc(k); end; end; dbgrid.DataSource.DataSet.DisableControls; saveplace:=dbgrid.DataSource.DataSet.GetBookmark; dbgrid.DataSource.dataset.First; i

21、:=2; if dbgrid.DataSource.DataSet.recordcount65536 then ProgressBar1:=ProgressBarform(65536) else ProgressBar1:=ProgressBarform(dbgrid.DataSource.DataSet.recordcount); while not dbgrid.DataSource.dataset.Eof do begin k:=0; for j:=0 to dbgrid.Columns.count-1 do begin if dbgrid.Columns.Itemsj.Visible

22、then begin excel.cellsi,k+1.NumberFormat:=; if not dbgrid.DataSource.dataset.fieldbyname(dbgrid.Columns.Itemsj.FieldName).isnull then begin str := dbgrid.DataSource.dataset.fieldbyname(dbgrid.Columns.Itemsj.FieldName).value; Excel.Cellsi, k + 1 := Str; end; inc(k); end else continue; end; if i=65536

23、 then break; inc(i); ProgressBar1.StepBy(1); dbgrid.DataSource.dataset.next; end; progressbar1.Owner.Free; application.ProcessMessages; dbgrid.DataSource.dataset.GotoBookmark(SavePlace); dbgrid.DataSource.dataset.EnableControls; try if copy(FileName,length(FileName)-3,4).xls then FileName:=FileName+

24、.xls; Excel.ActiveWorkbook.SaveAs(FileName,xlNormal, ,False,False); except Excel.Quit; screen.cursor:=crDefault; exit; end; /Excel.Visible := true; Excel.Quit; screen.cursor:=crDefault; Result:= true; end; /将ADOQUERY的数据集导入到EXCEL中function queryExportToExcel(queryexport:tadoquery):boolean; const xlNor

25、mal=-4143; var i,j,k:integer; str,filename:string; excel:OleVariant; savedialog:tsavedialog; ProgressBar1:TProgressBar; begin result:=false; filename:=; if queryexport.RecordCount65536 then begin if application.messagebox(需要导出的数据过大，Excel最大只能容纳65536行,是否还要继续？,询问,mb_yesno+mb_iconquestion)=idno then exi

26、t; end; screen.Cursor:=crHourGlass; try excel:=CreateOleObject(Excel.Application); excel.workbooks.add; except screen.cursor:=crDefault; showmessage(无法调用Excel！); exit; end; savedialog:=tsavedialog.Create(nil); savedialog.Filter:=Excel文件(*.xls)|*.xls; if savedialog.Execute then begin if FileExists(sa

27、vedialog.FileName) then try if application.messagebox(该文件已经存在，要覆盖吗？,询问,mb_yesno+mb_iconquestion)=idyes then DeleteFile(PChar(savedialog.FileName) else begin Excel.Quit; savedialog.free; screen.cursor:=crDefault; Exit; end; except Excel.Quit; savedialog.free; screen.cursor:=crDefault; Exit; end; file

28、name:=savedialog.FileName; end; savedialog.free; application.ProcessMessages; if filename= then begin result:=false; Excel.Quit; screen.cursor:=crDefault; exit; end; k:=0; for i:=0 to queryexport.FieldCount-1 do begin excel.cells1,k+1:=queryexport.Fieldsi.FieldName; inc(k); end; queryexport.First; i

29、:=2; if queryexport.recordcount65536 then ProgressBar1:=ProgressBarform(65536) else ProgressBar1:=ProgressBarform(queryexport.recordcount); while not queryexport.Eof do begin k:=0; for j:=0 to queryexport.FieldCount-1 do begin excel.cellsi,k+1.NumberFormat:=; if not queryexport.fieldbyname(queryexport.Fieldsj.FieldName).isnull then begin str:=queryexport.fieldbyname(queryexport.Fieldsj.FieldName).AsString; Excel.Cellsi, k + 1 := Str; end; inc(k); end; if i=65536 then break; in