RSS订阅 | 匿名投稿
您的位置:网站首页 > 数据库 > 正文

Delphi 导出数据至Excel的7种方法

作者:admin 来源: 日期:2018/2/9 18:27:13 人气: 标签:

一;
delphi 快速导出excel

uses 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.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;
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处加上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;
ADOQuery1.SQL.Add('select * from jj_department');
ADOQuery1.Open;
k:=ADOQuery1.RecordCount;
Excelid.Visible := True;
Excelid.WorkBooks.Add;
Excelid.worksheets[1].range['A1:c1'].Merge(True);
Excelid.WorkSheets[1].Cells[1,1].Value :='部门编码表' ;
Excelid.worksheets[1].Range['a1:a1'].HorizontalAlignment := $FFFFEFF4;
Excelid.worksheets[1].Range['a1:a1'].Ve