方法N多 一.SQL语句 1. 建立一个按钮,及Adoconnection1,并将其驱动指向一个Excel文件! Connectionstring为 Provider=Microsoft.Jet.OLEDB.4.0;Data Source=路径+Excel文件名;Extended Properties=Excel 8.0;Persist Security Info=False 2.在按钮事件中写 ADOConnection1.Connected:=True; ADOConnection1.Execute('SELECT * into table2 FROM [T_TestTree] IN [ODBC] '+ '[ODBC;Driver=SQL Server;UID=sa;PWD=CDRSmanager;Server=127.0.0.1;DataBase=test;]'); 2.建2个ADO,一个指Excel,一个指MSSQL查询结果,循环向Excel中插入 3.用OLE实现,此种方法可灵活订制Excel格式
如果查询结果在CxGrid中或DbgridEh,可直接利用其提供的函数实现
如何将哪里的查询结果导出为excel表?
供参考,代码你稍调整一下吧. var SaveDialog: TSaveDialog; curname,Str: String; CsvFile: TextFile; begin SaveDialog := TSaveDialog.Create(nil); SaveDialog.Filter := '.csv'; try if SaveDialog.Execute then begin //开始保存文件 curname:=SaveDialog.FileName+'.csv'; //生成存储文件名 if FileExists(curname) then begin if (Application.MessageBox(PChar('文件【'+curname+'】已存在,要覆盖吗?'),'提示',MB_ICONQUESTION+MB_YESNO)=IDYES) then DeleteFile(PChar(curname)) else begin exit; end; end; //保存数据 AssignFile(CsvFile,curname); Rewrite(CsvFile); try with not eof adoquery1.eof do begin str:=''; str:='"'+filelds[0].asstring; str:=str+'"'; Writeln(CsvFile,str); adoquery1.Next; end; finally CloseFile(CsvFile); end; end; finally Savedialog.Free; end;
DBGrid就是与数据集关联的,我在上面写了一个导到csv的方法(因为导到xls行数会有65535这个限制) 导到.xls的方法如下: 表单上一个edit1,一个dbgrid,一个datasource,一个adoquery,一个adoconnection 单元中要引用 ueses ComObj这个单元 procedure TForm1.Button1Click(Sender: TObject); Var OleObjExcel,hWorkBooks,hWorkSheet :Variant; i,j,k:Integer; curname,ls_text:String; begin if trim(edit1.text)='' then begin ShowMessage('文件名不得为空'); edit1.SetFocus; exit; end; try OleObjExcel := CreateOleObject('Excel.Application'); Try hWorkBooks := OleObjExcel.Workbooks.Add; Except OleObjExcel.Quit; ShowMessage('运行Excel文件时出现异常,无法继续'); Exit; End; hWorkSheet:= hWorkBooks.Worksheets[1]; //开始写表 j:=1; adoquery1.First; while (not adoquery1.Eof) do begin k := 1; for i:=0 to adoquery1.FieldCount-1 do begin ls_text := adoquery1.Fields[i].AsString; hWorkSheet.Cells[j,k].Value:=ls_text; k := k+1; end; adoquery1.Next; if j=1 then j:=j+2 else j:=j+1; end; //开始保存文件 curname:='C:\'+trim(edit1.Text)+'.xls'; //生成存储文件名 if FileExists(curname) then begin if (Application.MessageBox('文件已存在,是否确定覆盖?','保存',MB_YESNO)=IDYES) then begin DeleteFile(curname); hWorkSheet.SaveAs(curname); end; end else hWorkSheet.SaveAs(curname); OleObjExcel.Quit; showmessage('C:\'+edit1.Text+'.xls 文件已生成'); except OleObjExcel.Quit; end; end;
一.SQL语句
1.
建立一个按钮,及Adoconnection1,并将其驱动指向一个Excel文件!
Connectionstring为
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=路径+Excel文件名;Extended Properties=Excel 8.0;Persist Security Info=False
2.在按钮事件中写
ADOConnection1.Connected:=True;
ADOConnection1.Execute('SELECT * into table2 FROM [T_TestTree] IN [ODBC] '+
'[ODBC;Driver=SQL Server;UID=sa;PWD=CDRSmanager;Server=127.0.0.1;DataBase=test;]');
2.建2个ADO,一个指Excel,一个指MSSQL查询结果,循环向Excel中插入
3.用OLE实现,此种方法可灵活订制Excel格式
var
SaveDialog: TSaveDialog;
curname,Str: String;
CsvFile: TextFile;
begin
SaveDialog := TSaveDialog.Create(nil);
SaveDialog.Filter := '.csv';
try
if SaveDialog.Execute then
begin
//开始保存文件
curname:=SaveDialog.FileName+'.csv'; //生成存储文件名
if FileExists(curname) then
begin
if (Application.MessageBox(PChar('文件【'+curname+'】已存在,要覆盖吗?'),'提示',MB_ICONQUESTION+MB_YESNO)=IDYES) then
DeleteFile(PChar(curname))
else
begin
exit;
end;
end;
//保存数据
AssignFile(CsvFile,curname);
Rewrite(CsvFile);
try
with not eof adoquery1.eof do
begin
str:='';
str:='"'+filelds[0].asstring;
str:=str+'"';
Writeln(CsvFile,str);
adoquery1.Next;
end;
finally
CloseFile(CsvFile);
end;
end;
finally
Savedialog.Free;
end;
等 级:
发表于:2008-02-22 14:31:415楼 得分:0
如何将哪里的查询结果导出为excel表?
Re:
就是用DBGrid显示查询出来的数据,然后将DBGrid中的数据导出到Excel表中
导到.xls的方法如下: 表单上一个edit1,一个dbgrid,一个datasource,一个adoquery,一个adoconnection
单元中要引用 ueses ComObj这个单元
procedure TForm1.Button1Click(Sender: TObject);
Var
OleObjExcel,hWorkBooks,hWorkSheet :Variant;
i,j,k:Integer;
curname,ls_text:String;
begin
if trim(edit1.text)='' then begin
ShowMessage('文件名不得为空');
edit1.SetFocus;
exit;
end;
try
OleObjExcel := CreateOleObject('Excel.Application');
Try
hWorkBooks := OleObjExcel.Workbooks.Add;
Except
OleObjExcel.Quit;
ShowMessage('运行Excel文件时出现异常,无法继续');
Exit;
End; hWorkSheet:= hWorkBooks.Worksheets[1];
//开始写表
j:=1;
adoquery1.First;
while (not adoquery1.Eof) do
begin
k := 1;
for i:=0 to adoquery1.FieldCount-1 do
begin
ls_text := adoquery1.Fields[i].AsString;
hWorkSheet.Cells[j,k].Value:=ls_text;
k := k+1;
end;
adoquery1.Next;
if j=1 then j:=j+2 else j:=j+1;
end; //开始保存文件
curname:='C:\'+trim(edit1.Text)+'.xls'; //生成存储文件名
if FileExists(curname) then
begin
if (Application.MessageBox('文件已存在,是否确定覆盖?','保存',MB_YESNO)=IDYES) then
begin
DeleteFile(curname);
hWorkSheet.SaveAs(curname);
end;
end
else hWorkSheet.SaveAs(curname);
OleObjExcel.Quit;
showmessage('C:\'+edit1.Text+'.xls 文件已生成');
except
OleObjExcel.Quit;
end;
end;