delphi导出数据至Excel的几种方法及比较.doc

delphi导出数据至Excel的几种方法及比较 一、delphi 快速导出exceluses ComObj,clipbrd;function ToExcel(sfilename:string; ADOQuery:TADOQuery):boolean;const xlNormal=-4143;var y : integer; tsList : TStringList; s,filename :string; aSheet :Variant; excel :OleVariant; savedialog :tsavedialog;begin Result := true; try excel:=CreateOleObject(Excel.Application); excel.workbooks.add; except /screen.cursor:=crDefault; showmessage(无法调用Excel！); exit; end; savedialog:=tsavedialog.Create(nil); savedialog.FileName:=sfilename; /存入文件 savedialog.Filter:=Excel文件(*.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.Quit; savedialog.free; screen.cursor:=crDefault; Exit; end; filename:=savedialog.FileName; end; savedialog.free; if filename= then begin result:=true; Excel.Quit; /screen.cursor:=crDefault; exit; end; aSheet:=excel.Worksheets.Item1; tsList:=TStringList.Create; /tsList.Add(查询结果); /加入标题 s:=; /加入字段名 for y := 0 to adoquery.fieldCount - 1 do begin s:=s+adoQuery.Fields.Fieldsy.FieldName+#9 ; Application.ProcessMessages; end; tsList.Add(s); try try ADOQuery.First; While Not ADOQuery.Eof do begin s:=; for y:=0 to ADOQuery.FieldCount-1 do begin s:=s+ADOQuery.Fieldsy.AsString+#9; Application.ProcessMessages; end; tsList.Add(s); ADOQuery.next; end; Clipboard.AsText:=tsList.Text; except result:=false; end; finally tsList.Free; end; aSheet.Paste; MessageBox(Application.Handle,数据导出完毕！,系统提示,MB_ICONINFORMATION or MB_OK); try if copy(FileName,length(FileName)-3,4).xls then FileName:=FileName+.xls; Excel.ActiveWorkbook.SaveAs(FileName, xlNormal, , , False, False); except Excel.Quit; screen.cursor:=crDefault; exit; end; Excel.Visible := false; /true会自动打开已经保存的excel Excel.Quit; Excel := UnAssigned; end;调用: ToExcel(D:a.xsl,QueryToExcel);/路径可以自定义 -*二、delphi如何导出EXCEL，代码。非第3方控件首先在Uses处加上ComObjprocedure TForm1.Button1Click(Sender: TObject); var h,k:integer; Excelid: OleVariant; s: string;begin try Excelid := CreateOLEObject(Excel.Application); except Application.MessageBox(Excel没有安装！, 提示信息, MB_OK+MB_ICONASTERISK+MB_DEFBUTTON1+MB_APPLMODAL); Exit; end; try ADOQuery1.Close; ADOQuery1.SQL.Clear; ADOQuery1.SQL.Add(select * from jj_department); ADOQuery1.Open; k:=ADOQuery1.RecordCount; Excelid.Visible := True; Excelid.WorkBooks.Add; Excelid.worksheets1.rangeA1:c1.Merge(True); Excelid.WorkSheets1.Cells1,1.Value :=部门编码表 ; Excelid.worksheets1.Rangea1:a1.HorizontalAlignment := $FFFFEFF4; Excelid.worksheets1.Rangea1:a1.VerticalAlignment := $FFFFEFF4; Excelid.WorkSheets1.Cells2,1.Value := 组别编号; Excelid.WorkSheets1.Cells2,2.Value := 公司编号; Excelid.WorkSheets1.Cells2,3.Value := 组别名称; Excelid.worksheets1.RangeA1:c1.Font.Name := 宋体; Excelid.worksheets1.RangeA1:c1.Font.Size := 9; Excelid.worksheets1.rangeA1:c2.font.bold:=true; Excelid.worksheets1.RangeA2:c2.Font.Size := 9; Excelid.worksheets1.RangeA2:c2.HorizontalAlignment := $FFFFEFF4; Excelid.worksheets1.RangeA2:c2.VerticalAlignment := $FFFFEFF4; h:=3; ADOQuery1.First; while not ADOQuery1.Eof do begin Excelid.WorkSheets1.Cellsh,1.Value := Adoquery1.FieldByName(Fdept_id).AsString; Excelid.WorkSheets1.Cellsh,2.Value := Adoquery1.FieldByName(Ffdept_id).AsString; Excelid.WorkSheets1.Cellsh,3.Value := Adoquery1.FieldByName(Fdept_name).AsString; Inc(h); Adoquery1.Next; end; s := A2:f+ IntToStr(k+2); Excelid.worksheets1.Ranges.Font.Name := 宋体; Excelid.worksheets1.Ranges.Font.size := 9; Excelid.worksheets1.Ranges.Borders.LineStyle := 1; Excelid.Quit; except Application.MessageBox(导入数据出错！请检查文件的格式是否正确！, 提示信息, MB_OK+MB_ICONASTERISK+MB_DEFBUTTON1+MB_APPLMODAL); end; MessageBox(GetActiveWindow(), EXCEL数据导出成功!, 提示信息, MB_OK +MB_ICONWARNING);end; -*三、delphi导出EXCELuses Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms, Dialogs, ExtCtrls, Mask, ComCtrls, StdCtrls, Buttons, Grids, ValEdit, IdBaseComponent, CheckLst, excel97, ExcelXP, OleServer, ComObj, excel2000, mmsystem, ShellAPI, ADODB, DB, DBGrids, clipbrd; Var FExcel:OleVariant; /excel应用程序 FWorkBook :OleVariant; /工作表 Temsheet:OleVariant; /工作薄 FPicture:OleVariant;/图片 tmpstr:String; range:variant;/范围 i,j,TemInt:integer; TemFileName:String;begin SaveDialog1.Filter:=.xls; if SaveDialog1.Execute then begin TemFileName:=SaveDialog1.FileName+.xls; Screen.Cursor:=CrHourGlass; TemInt:=0; FExcel:= CreateoleObject(excel.Application); FWorkBook:=FExcel.WorkBooks.Add(-4167); /新的工作表 Temsheet:=FWorkBook.Worksheets.Add; Temsheet.Name:=利润统计; Temsheet.Select; Temsheet.Columns1.ColumnWidth:=4;/设置列宽度 Temsheet.Columns2.ColumnWidth:=10; Temsheet.Columns3.ColumnWidth:=16; Temsheet.Columns4.ColumnWidth:=10; Temsheet.Columns5.ColumnWidth:=10; Temsheet.Columns6.ColumnWidth:=10; Temsheet.Columns7.ColumnWidth:=10; Temsheet.Columns8.ColumnWidth:=10; Temsheet.Columns9.ColumnWidth:=20; Temsheet.Columns10.ColumnWidth:=15; range:=Temsheet.RangeTemsheet.cells1,1,Temsheet.cells5,2;/选定表格 range.select; range.merge; /合并单元格 tmpstr:=ExtractFilePath(ParamStr(0)+tem.jpg; /添加图片 FPicture:=Temsheet.Pictures.Insert(tmpstr); FPicture.Left:=20; FPicture.Top:=5; FPicture.width:=50; FPicture.height:=50; FPicture:=null; range:=Temsheet.RangeTemsheet.cells2,3,Temsheet.cells3,4;/选定表格 range.select; range.merge; Range.Characters.Font.FontStyle :=加粗; Temsheet.Cells2,3.HorizontalAlignment:=-4108; /字居中 Temsheet.Cells2,3:=ComSName; range:=Temsheet.RangeTemsheet.cells4,3,Temsheet.cells4,4;/选定表格 range.select; range.merge; Temsheet.Cells4,3.HorizontalAlignment:=-4108; /字居中 Temsheet.Cells4,3:=ComEName; range:=Temsheet.RangeTemsheet.cells2,5,Temsheet.cells2,6;/选定表格 range.select; range.merge; Temsheet.Cells2,5.HorizontalAlignment:=-4108; /字居中 Temsheet.Cells2,5:=ComName; Temsheet.Cells3,5:=联系人：; Temsheet.Cells4,5:=电话：; Temsheet.Cells4,6:=ComPhone; Temsheet.Cells5,5:=传真：; Temsheet.Cells5,6:=ComFax; range:=Temsheet.RangeTemsheet.cells6,1,Temsheet.cells6,10;/选定表格 range.select; range.merge; range:=Temsheet.RangeTemsheet.cells7,1,Temsheet.cells7,2;/选定表格 range.select; range.merge; Range.Characters.Font.FontStyle :=加粗; Temsheet.Cells7,1:=入库信息：; range:=Temsheet.RangeTemsheet.cells7,3,Temsheet.cells7,10;/选定表格 range.select; range.merge; Temsheet.Cells8,1:=序号; Temsheet.Cells8,1.HorizontalAlignment:=-4108; /字居中 Temsheet.Cells8,1.Interior.Color:=clGray; /单元格背景色 range:=Temsheet.RangeTemsheet.cells8,1,Temsheet.cells8,1;/选定表格 range.borders.linestyle:=1;/华线 for i:=0 to DBGrid1.Columns.Count - 1 do begin Temsheet.Cells8,i+2:=DBGrid1.Columnsi.Title.Caption; Temsheet.Cells8,i+2.HorizontalAlignment:=-4108; /字居中 Temsheet.Cells8,i+2.Interior.Color:=clGray; /单元格背景色 range:=Temsheet.RangeTemsheet.cells8,i+2,Temsheet.cells8,i+2;/选定表格 range.borders.linestyle:=1;/华线 end; / j:=0; DBGrid1.DataSource.DataSet.First; while not DBGrid1.DataSource.DataSet.Eof do begin Temsheet.Cells9+j,1.Value:=j+1; Temsheet.Cells9+j,1.HorizontalAlignment:=-4108; /字居中 range:=Temsheet.RangeTemsheet.cells9+j,1,Temsheet.cells9+j,1;/选定表格 range.borders.linestyle:=1;/华线 for i:=0 to DBGrid1.Columns.Count - 1 do begin Temsheet.Cells9+j,i+2.Value:=DBGrid1.Fieldsi.AsString; range:=Temsheet.RangeTemsheet.cells9+j,i+2,Temsheet.cells9+j,i+2;/选定表格 range.borders.linestyle:=1;/华线 end; DBGrid1.DataSource.DataSet.Next; j:=j+1; end; TemInt:=9+ DBGrid1.DataSource.DataSet.RecordCount; range:=Temsheet.RangeTemsheet.cellsTemInt,1,Temsheet.cellsTemInt,10;/选定表格 range.select; range.merge; TemInt:=TemInt+1; range:=Temsheet.RangeTemsheet.cellsTemInt,1,Temsheet.cellsTemInt,2;/选定表格 range.select; range.merge; Range.Characters.Font.FontStyle :=加粗; Temsheet.CellsTemInt,1:=出库信息：; range:=Temsheet.RangeTemsheet.cellsTemInt,3,Temsheet.cellsTemInt,10;/选定表格 range.select; range.merge; TemInt:=TemInt+1; Temsheet.CellsTemInt,1:=序号; Temsheet.CellsTemInt,1.HorizontalAlignment:=-4108; /字居中 Temsheet.CellsTemInt,1.Interior.Color:=clGray; /单元格背景色 range:=Temsheet.RangeTemsheet.cellsTemInt,1,Temsheet.cellsTemInt,1;/选定表格 range.borders.linestyle:=1;/华线 for i:=0 to DBGrid2.Columns.Count - 1 do begin Temsheet.CellsTemInt,i+2:=DBGrid2.Columnsi.Title.Caption; Temsheet.CellsTemInt,i+2.HorizontalAlignment:=-4108; /字居中 Temsheet.CellsTemInt,i+2.Interior.Color:=clGray; /单元格背景色 range:=Temsheet.RangeTemsheet.cellsTemInt,i+2,Temsheet.cellsTemInt,i+2;/选定表格 range.borders.linestyle:=1;/华线 end; TemInt:=TemInt+1; / j:=0; DBGrid2.DataSource.DataSet.First; while not DBGrid2.DataSource.DataSet.Eof do begin Temsheet.CellsTemInt+j,1.Value:=j+1; Temsheet.CellsTemInt+j,1.HorizontalAlignment:=-4108; /字居中 range:=Temsheet.RangeTemsheet.cellsTemInt+j,1,Temsheet.cellsTemInt+j,1;/选定表格 range.borders.linestyle:=1;/华线 for i:=0 to DBGrid2.Columns.Count - 1 do begin Temsheet.CellsTemInt+j,i+2.Value:=DBGrid2.Fieldsi.AsString; range:=Temsheet.RangeTemsheet.cellsTemInt+j,i+2,Temsheet.cellsTemInt+j,i+2;/选定表格 range.borders.linestyle:=1;/华线 end; DBGrid2.DataSource.DataSet.Next; j:=j+1; end; TemInt:=TemInt+ DBGrid2.DataSource.DataSet.RecordCount; TemInt:=TemInt+1; range:=Temsheet.RangeTemsheet.cellsTemInt,1,Temsheet.cellsTemInt,10;/选定表格 range.select; range.merge; TemInt:=TemInt+1; range:=Temsheet.RangeTemsheet.cellsTemInt,1,Temsheet.cellsTemInt,2;/选定表格 range.select; range.merge; Range.Characters.Font.FontStyle :=加粗; Temsheet.CellsTemInt,1:=入库总额：; Temsheet.CellsTemInt,3:=Trim(Edit1.Text); range:=Temsheet.RangeTemsheet.cellsTemInt,4,Temsheet.cellsTemInt,10;/选定表格 range.select; range.merge; TemInt:=TemInt+1; range:=Temsheet.RangeTemsheet.cellsTemInt,1,Temsheet.cellsTemInt,2;/选定表格 range.select; range.merge; Range.Characters.Font.FontStyle :=加粗; Temsheet.CellsTemInt,1:=出库总额：; Temsheet.CellsTemInt,3:=Trim(Edit2.Text); range:=Temsheet.RangeTemsheet.cellsTemInt,4,Temsheet.cellsTemInt,10;/选定表格 range.select; range.merge; TemInt:=TemInt+1; range:=Temsheet.RangeTemsheet.cellsTemInt,1,Temsheet.cellsTemInt,2;/选定表格 range.select; range.merge; Range.Characters.Font.FontStyle :=加粗; Temsheet.CellsTemInt,1:=总利润：; Temsheet.CellsTemInt,3:=Trim(Edit3.Text); range:=Temsheet.RangeTemsheet.cellsTemInt,4,Temsheet.cellsTemInt,10;/选定表格 range.select; range.merge; range:=Temsheet.RangeTemsheet.cells7,1,Temsheet.cellsTemInt,10;/选定表格 range.borders.linestyle:=1;/华线 Application.ProcessMessages; Screen.Cursor:=CrDefault; FExcel.WorkBooks1.saveas(TemFileName);/保存文件 FExcel.workbooks1.close; /关闭工作表 Application.ProcessMessages; MessageBox(Handle,导出成功,提示,MB_OK); /FExcel.visible:=true; FExcel.quit; /关闭Excel FExcel := unassigned; shellexecute(0,open,PChar(ExtractFileName(TemFileName),nil,PChar(ExtractFilePath(TemFileName),SW_Show); end;end;-*四、导出到Exceluses Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms, Dialogs, ExtCtrls, Mask, ComCtrls, StdCtrls, Buttons, Grids, ValEdit, IdBaseComponent, CheckLst, excel97, ExcelXP, OleServer, comobj, excel2000, mmsystem, ADODB, DB, DBGrids, clipbrd;procedure TFIND_FM.Button1Click(Sender: TObject);var i,j : integer; reportname, wpath : string; ExApp1 : TExcelApplication; ExWrbk1 : TExcelWorkbook; ExWrst1 : TExcelWorksheet; begin if Main_FM.ADOQuery_TEMP.IsEmpty then begin Showmessage(沒有可導出的資料!); Exit; end else begin Main_FM.SaveDialog1.FileName := qcreport; if Main_FM.savedialog1.Execute then begin /savedialog1.FileName := formatdatetime(YYYYMMDDHHMMSS,now()+md_orderqc_list.xls; reportname := formatdatetime(YYYYMMDDHHMMSS,now()+ExtractFileName(Main_FM.savedialog1.FileName); /reportname := formatdatetime(YYYYMMDDHHMMSS,now()+; wpath := ExtractFilePath(Main_FM.savedialog1.FileName); /showmessage(wpath); try ExApp1 := TExcelApplication.Create(application); ExWrbk1 := TExcelWorkbook.Create(application); ExWrst1 := TExcelWorksheet.Create(application); ExApp1.Connect; except Showmessage(電腦沒裝Excel!無法導出!); Abort; end; try try ExApp1.Workbooks.Add(EmptyParam,0); ExWrbk1.ConnectTo(ExApp1.Workbooks1); ExWrst1.ConnectTo(ExWrbk1.Worksheets1 as _worksheet); Main_FM.ADOQuery_TEMP.First; for j := 0 to Main_FM.ADOQuery_TEMP.FieldCount-1 do begin ExWrst1.Cells.Item1,j+1 := Main_FM.ADOQuery_TEMP.Fieldsj.DisplayName; / end; for i := 2 to Main_FM.ADOQuery_TEMP.RecordCount+1 do begin for j := 0 to Main_FM.ADOQuery_TEMP.FieldCount-1 do begin ExWrst1.Cells.Itemi,j+1 := Main_FM.ADOQuery_TEMP.Fieldsj.Value; end; Main_FM.ADOQuery_TEMP.Next; end; ExWrst1.SaveAs(wpath+reportname); /ExWrst.SaveAs(formatdatetime(YYYYMMDDHHMMSS,now()+reportname); Showmessage(數據已成功導出!); except Showmessage(導出失敗!); abort; end; finally ExApp1.Disconnect; ExApp1.Quit; ExApp1.Free; ExWrbk1.Free; ExWrst1.Free; end; end; end;end;-*delphi导出数据至Excel的三种方法及比较闲来无事，跑到网上搜集了几种导出DataSet至Excel的几种方法。另外使用GetTickcount函数计算时差，以便比较。（本来使用Timer控件，但是Timer不适合做高精度时间计算）使用TADOConnect，TADOQuery查询数据。方法五： 使用TADOQuery + Varaint方法，循环遍历数据集中数据，直接插入到Excel的WookBook单元。这是初学者最易懂和易接受的方法。在下面代码中没有仔细注意语法（比如没有使用try.finally结构体），如果需要使用，请注意：/使用ADO循环方式保存procedure TForm1.btn_WhileClick(Sender: TObject);var Eclapp:variant; n:integer; filename: string; t1,t2: Int64;begin Eclapp := CreateOleObject(Excel.Application); Eclapp.WorkBooks.Add; Eclapp.Visible:= False; filena