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

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

一、delphi 快速导出excel

uses ComObj,clipbrd;

function ToExcel(sfilename:string; ADOQuery:TADOQuery):boolean;

const

xlNormal=-4143;

var

y : integer;

tsList : TStringList;

s,filename :string;

aSheet :V ariant;

excel :OleV ariant;

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;

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.Item[1];

tsList:=TStringList.Create;

//tsList.Add('查询结果'); //加入标题

s:=''; //加入字段名

for y := 0 to adoquery.fieldCount - 1 do

begin

s:=s+adoQuery.Fields.Fields[y].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.Fields[y].AsString+#9;

Application.ProcessMessages;

end;

tsList.Add(s);

ADOQuery.next;

end;

Clipboard.AsText:=tsList.Text;

except

result:=false;

end;

finally

tsList.Free;

aSheet.Paste;

MessageBox(Application.Handle,'数据导出完毕！','系统提示',MB_ICONINFORMA TION 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处加上ComObj

procedure 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;