****将查询结果导出到Excel问题****

[gsk99]

功能实现：
1。将数据库中查询的数据导出到Excel中
2。如果在导出时发现Excel文件已存在，则删除重建，或删除其中的内容，然后将新的数据倒入
3。导出时，指定每一列的中文名字，

---------------------------------
目前只知道下面这点导出方法
insert opendatasource('Microsoft.Jet.OLEDB.4.0','Data Source="d:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...sheet1$(SlotName)
select A,B,C,D from T
测试时发现前两行是空白行，不知为什么。


谢谢

[gsk99]

从SQLServer中导入/导出Excel的基本方法

发布日期：2007-1-8 21:41:02 作者：辉煌盛世 出处：辉煌盛世


/*===================导入/导出Excel的基本方法===================*/
从Excel文件中,导入数据到SQL数据库中,很简单,直接用下面的语句:

/*===================================================================*/
--如果接受数据导入的表已经存在
insertinto表select*from
OPENROWSET(MICROSOFT.JET.OLEDB.4.0
,Excel5.0;HDR=YES;DATABASE=c:test.xls,sheet1$)

--如果导入数据并生成表
select*into表from
OPENROWSET(MICROSOFT.JET.OLEDB.4.0
,Excel5.0;HDR=YES;DATABASE=c:test.xls,sheet1$)


/*===================================================================*/
--如果从SQL数据库中,导出数据到Excel,如果Excel文件已经存在,而且已经按照要接收的数据创建好表头,就可以简单的用:
insertintoOPENROWSET(MICROSOFT.JET.OLEDB.4.0
,Excel5.0;HDR=YES;DATABASE=c:test.xls,sheet1$)
select*from表


--如果Excel文件不存在,也可以用BCP来导成类Excel的文件,注意大小写:
--导出表的情况
EXECmaster..xp_cmdshellbcp数据库名.dbo.表名out"c:test.xls"/c-/S"服务器名"/U"用户名"-P"密码"

--导出查询的情况
EXECmaster..xp_cmdshellbcp"SELECTau_fname,au_lnameFROMpubs..authorsORDERBYau_lname"queryout"c:test.xls"/c-/S"服务器名"/U"用户名"-P"密码"


/*--说明:
c:test.xls为导入/导出的Excel文件名.
sheet1$为Excel文件的工作表名,一般要加上$才能正常使用.
--*/
--上面已经说过,用BCP导出的是类Excel文件,其实质为文本文件,

--要导出真正的Excel文件.就用下面的方法


/*--数据导出EXCEL

导出表中的数据到Excel,包含字段名,文件为真正的Excel文件
,如果文件不存在,将自动创建文件
,如果表不存在,将自动创建表
基于通用性考虑,仅支持导出标准数据类型
--邹建2003.10--*/

/*--调用示例

p_exporttb@tbname=地区资料,@path=c:,@fname=aa.xls
--*/
ifexists(select*fromdbo.sysobjectswhereid=object_id(N[dbo].[p_exporttb])andOBJECTPROPERTY(id,NIsProcedure)=1)
dropprocedure[dbo].[p_exporttb]
GO

createprocp_exporttb
@tbnamesysname,--要导出的表名
@pathnvarchar(1000),--文件存放目录
@fnamenvarchar(250)=--文件名,默认为表名
as
declare@errint,@srcnvarchar(255),@descnvarchar(255),@outint
declare@objint,@constrnvarchar(1000),@sqlvarchar(8000),@fdlistvarchar(8000)

--参数检测
ifisnull(@fname,)=set@fname=@tbname+.xls

--检查文件是否已经存在
ifright(@path,1)<>set@path=@path+
createtable#tb(abit,bbit,cbit)
set@sql=@path+@fname
insertinto#tbexecmaster..xp_fileexist@sql

--数据库创建语句
set@sql=@path+@fname
ifexists(select1from#tbwherea=1)
set@constr=DRIVER={MicrosoftExcelDriver(*.xls)};DSN=;READONLY=FALSE
+;CREATE_DB="+;DATABASE=+@sql+"


--连接数据库
exec@err=sp_oacreateadodb.connection,@objout
if@err<>0gotolberr

exec@err=sp_oamethod@obj,open,null,@constr
if@err<>0gotolberr

/*--如果覆盖已经存在的表,就加上下面的语句
--创建之前先删除表/如果存在的话
select@sql=droptable[+@tbname+]
exec@err=sp_oamethod@obj,execute,@outout,@sql
--*/

--创建表的SQL
select@sql=,@fdlist=
select@fdlist=@fdlist+,[+a.name+]
,@sql=@sql+,[+a.name+]
+casewhenb.namein(char,nchar,varchar,nvarchar)then
text(+cast(casewhena.length>255then255elsea.lengthendasvarchar)+)
whenb.namein(tynyint,int,bigint,tinyint)thenint
whenb.namein(smalldatetime,datetime)thendatetime
whenb.namein(money,smallmoney)thenmoney
elseb.nameend
FROMsyscolumnsaleftjoinsystypesbona.xtype=b.xusertype
whereb.namenotin(image,text,uniqueidentifier,sql_variant,ntext,varbinary,binary,timestamp)
andobject_id(@tbname)=id
select@sql=createtable[+@tbname
+](+substring(@sql,2,8000)+)
,@fdlist=substring(@fdlist,2,8000)
exec@err=sp_oamethod@obj,execute,@outout,@sql
if@err<>0gotolberr

exec@err=sp_oadestroy@obj

--导入数据
set@sql=openrowset(MICROSOFT.JET.OLEDB.4.0,Excel5.0;HDR=YES
;DATABASE=+@path+@fname+,[+@tbname+$])

exec(insertinto+@sql+(+@fdlist+)select+@fdlist+from+@tbname)

return

lberr:
execsp_oageterrorinfo0,@srcout,@descout
lbexit:
selectcast(@errasvarbinary(4))as错误号
,@srcas错误源,@descas错误描述
select@sql,@constr,@fdlist
go
--上面是导表的,下面是导查询语句的.

/*--数据导出EXCEL

导出查询中的数据到Excel,包含字段名,文件为真正的Excel文件
,如果文件不存在,将自动创建文件
,如果表不存在,将自动创建表
基于通用性考虑,仅支持导出标准数据类型
--邹建2003.10--*/

/*--调用示例

p_exporttb@sqlstr=select*from地区资料
,@path=c:,@fname=aa.xls,@sheetname=地区资料
--*/
ifexists(select*fromdbo.sysobjectswhereid=object_id(N[dbo].[p_exporttb])andOBJECTPROPERTY(id,NIsProcedure)=1)
dropprocedure[dbo].[p_exporttb]
GO

createprocp_exporttb
@sqlstrvarchar(8000),--查询语句,如果查询语句中使用了orderby,请加上top100percent
@pathnvarchar(1000),--文件存放目录
@fnamenvarchar(250),--文件名
@sheetnamevarchar(250)=--要创建的工作表名,默认为文件名
as
declare@errint,@srcnvarchar(255),@descnvarchar(255),@outint
declare@objint,@constrnvarchar(1000),@sqlvarchar(8000),@fdlistvarchar(8000)

--参数检测
ifisnull(@fname,)=set@fname=temp.xls
ifisnull(@sheetname,)=set@sheetname=replace(@fname,.,#)

--检查文件是否已经存在
ifright(@path,1)<>set@path=@path+
createtable#tb(abit,bbit,cbit)
set@sql=@path+@fname
insertinto#tbexecmaster..xp_fileexist@sql

--数据库创建语句
set@sql=@path+@fname
ifexists(select1from#tbwherea=1)
set@constr=DRIVER={MicrosoftExcelDriver(*.xls)};DSN=;READONLY=FALSE
+;CREATE_DB="+;DATABASE=+@sql+"

--连接数据库
exec@err=sp_oacreateadodb.connection,@objout
if@err<>0gotolberr

exec@err=sp_oamethod@obj,open,null,@constr
if@err<>0gotolberr

--创建表的SQL
declare@tbnamesysname
set@tbname=##tmp_+convert(varchar(38),newid())
set@sql=select*into[+@tbname+]from(+@sqlstr+)a
exec(@sql)

select@sql=,@fdlist=
select@fdlist=@fdlist+,[+a.name+]
,@sql=@sql+,[+a.name+]
+casewhenb.namein(char,nchar,varchar,nvarchar)then
text(+cast(casewhena.length>255then255elsea.lengthendasvarchar)+)
whenb.namein(tynyint,int,bigint,tinyint)thenint
whenb.namein(smalldatetime,datetime)thendatetime
whenb.namein(money,smallmoney)thenmoney
elseb.nameend
FROMtempdb..syscolumnsaleftjointempdb..systypesbona.xtype=b.xusertype
whereb.namenotin(image,text,uniqueidentifier,sql_variant,ntext,varbinary,binary,timestamp)
anda.id=(selectidfromtempdb..sysobjectswherename=@tbname)
select@sql=createtable[+@sheetname
+](+substring(@sql,2,8000)+)
,@fdlist=substring(@fdlist,2,8000)

exec@err=sp_oamethod@obj,execute,@outout,@sql
if@err<>0gotolberr

exec@err=sp_oadestroy@obj

--导入数据
set@sql=openrowset(MICROSOFT.JET.OLEDB.4.0,Excel5.0;HDR=YES
;DATABASE=+@path+@fname+,[+@sheetname+$])

exec(insertinto+@sql+(+@fdlist+)select+@fdlist+from[+@tbname+])

set@sql=droptable[+@tbname+]
exec(@sql)
return

lberr:
execsp_oageterrorinfo0,@srcout,@descout
lbexit:
selectcast(@errasvarbinary(4))as错误号
,@srcas错误源,@descas错误描述
select@sql,@constr,@fdlist
go

我网上找的，好复杂啊

[gsk99]

up

[Andy__Huang]

在查询分析器查询有结果后,点击查询结果区域,然后点"保存",
文件类型选XXX.CSV

[fa_ge]

功能实现：
1。将数据库中查询的数据导出到Excel中
2。如果在导出时发现Excel文件已存在，则删除重建，或删除其中的内容，然后将新的数据倒入
3。导出时，指定每一列的中文名字，

-------------------
在VBA 中，你這三個功能很好實現的

[fa_ge]

樓主不如這樣做

把查詢的結果保存在一個表裡，然後用DTS把它導出EXCEL 中，這樣不也簡單.

[gsk99]

谢谢各位

我的意思是用sql语句导出Excel。然后我用DTS定期执行，这样，可以保持这个Excel文件的数据时最新的。

这个我从没有做过，所以不知从何下手。

导出时，如果Excel文件不存在，则自己创建，如果存在，则删除其中的内容，然后重新导入新的数据。

不知sql能否删除文件或者是删除Excel中的内容。

[wgzaaa]

sql能否删除文件----
EXEC master..xp_cmdshell 'del d:\book1.xls'

[dovelee]

xuexi

[gsk99]

请问我导出Excel时，我想给每个列加列名，不知如和实现？

我已实现导出功能，但没有列名

[viva369]

学习