功能实现:
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
测试时发现前两行是空白行,不知为什么。
谢谢
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
测试时发现前两行是空白行,不知为什么。
谢谢
从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 我网上找的,好复杂啊
文件类型选XXX.CSV
1。将数据库中查询的数据导出到Excel中
2。如果在导出时发现Excel文件已存在,则删除重建,或删除其中的内容,然后将新的数据倒入
3。导出时,指定每一列的中文名字,-------------------
在VBA 中,你這三個功能很好實現的
EXEC master..xp_cmdshell 'del d:\book1.xls'