SQLServer导出表到EXCEL文件的存储过程

将表从SQL Server导出到EXCEL文件的存储过程:

*-数据导出到EXCEL

将表格中的数据导出到Excel，包括字段名称。该文件是一个真正的Excel文件。

，如果文件不存在，将自动创建。

，如果该表不存在，它将被自动创建。

出于通用性考虑，仅支持导出标准数据类型。

- */

/* */*-呼叫示例

P_exporttb @tbname= '区域数据'，@path='c:'，@fname='aa.xls '

- */

如果存在(select * from dbo.sysobjects其中id = object_id(N'[dbo])。[p_exporttb]')和

OBJECTPROPERTY(id，N'IsProcedure') = 1)

删除过程。[p_exporttb]

去

创建过程p_exporttb

@ tbnamesysname，-要导出的表的名称。请注意，它只能是表名/视图名。

@ pathnvarchar (1000)，-文件存储目录

@ fname nvarchar(250)= ' '-文件名，默认为表名。

如同

声明@err int、@src nvarchar(255)、@desc nvarchar(255)、@out int

声明@obj int、@ const nvarchar(1000)、@sql varchar(8000)、@fdlist varchar(8000)

-参数检测

if isnull(@fname，' ')='' set @fname=@tbname+'。xls '

-检查文件是否已经存在。

if right(@path，1)'' set @path=@path+' '

创建表#tb(a位、b位、c位)

set @sql=@path+@fname

插入#tb exec master..xp_fileexist @sql

-数据库创建语句

set @sql=@path+@fname

如果存在(从#tb中选择1，其中a=1)

set @ const = ' DRIVER = { Microsoft Excel DRIVER(*。xls)}；DSN =“”；“只读=假”

+';CREATE _ DB = " '+@ SQL+' "；DBQ='+@sql

其他

set @ constr = ' Provider = Microsoft。Jet . OLEDB.4.0扩展属性= " Excel 5.0HDR =是'

+';DATABASE='+@sql+' " '

-连接到数据库

exec @ err = sp _ oacreate ' adodb . connection '，@obj out

if @err0转到lberr

exec @err=sp_oamethod @obj，' open '，null，@ constr

if @err0转到lberr

-用于创建表的SQL

select @sql= ' '，@fdlist= ' '

select @fdlist=@fdlist+'，'+a.name

，@sql=@sql+'，['+a.name+']'

+case when b.name in('char '，' nchar '，' varchar '，' nvarchar') then

text('+cast(a.length > 255，则255，否则a . length以varchar结尾)+')

当b.name in('tynyint '，' int '，' bigint '，' tinyint') then 'int '

when b.name in('smalldatetime '，' datetime') then 'datetime '

当b.name在(' money '，' smallmoney ')中时，则为' money '

else b.name结束

从syscolumns a左连接systypes b on a.xtype=b.xusertype

其中b.name不在

(' image '，' text '，' uniqueidentifier '，' sql_variant '，' ntext '，' varbinary '，' binary '，' timestamp ')

和object_id(@tbname)=id

select @ SQL = ' create table['+@ TB name

+']('+substring(@sql，28000)+')'

，@fdlist=substring(@fdlist，28000)

exec @err=sp_oamethod @obj，' execute '，@out out，@sql

if @err0转到lberr

exec @err=sp_oadestroy @obj

-导入数据

set @ SQL = ' openrowset(' MICROSOFT。JET.OLEDB.4.0 ' '，' ' Excel 5.0HDR =是

；DATABASE='+@path+@fname+' ' '，['+@tbname+'$])'

exec(' insert into '+@ SQL+'('+@ FD list+')select '+@ FD list+' from '+@ TB name)

返回

lberr:

exec sp_oageterrorinfo 0，@src out，@desc out

lbexit:

选择(@ err asvarbinary (4))作为错误号

，@src作为错误源，@desc作为错误描述

select @sql，@ constr，@fdlist

去

*-数据导出到EXCEL

将查询中的数据导出到Excel，包括字段名，文件是真正的Excel文件。

，如果文件不存在，将自动创建。

，如果该表不存在，它将被自动创建。

出于通用性考虑，仅支持导出标准数据类型。

- */

/* */*-呼叫示例

p _ exporttb @ sqlstr = ' select * from regional data '

，@ path =' c:'，@ fname =' aa.xls '，@ sheetname =' regional data '

- */

如果存在(select * from dbo.sysobjects其中id = object_id(N'[dbo])。[p_exporttb]')和

OBJECTPROPERTY(id，N'IsProcedure') = 1)

删除过程。[p_exporttb]

去

创建过程p_exporttb

@ sqlstrsysname，-query语句。如果查询语句中使用了order by，请添加top 100。请注意，如果您领导

在表/视图之外，使用上面的存储过程。

@ pathnvarchar (1000)，-文件存储目录

@ fname nvarchar (250)，-文件名

@ sheet name varchar(250)= ' '-要创建的工作表的名称，默认为文件名。

如同

声明@err int、@src nvarchar(255)、@desc nvarchar(255)、@out int

声明@obj int、@ const nvarchar(1000)、@sql varchar(8000)、@fdlist varchar(8000)

-参数检测

if isnull(@fname，' ')='' set @fname='temp.xls '

' if isnull(@sheetname，' ')= ' ' set @ sheet name = replace(@ fname，'.','#')

-检查文件是否已经存在。

if right(@path，1)'' set @path=@path+' '

创建表#tb(a位、b位、c位)

set @sql=@path+@fname

插入#tb exec master..xp_fileexist @sql

-数据库创建语句

set @sql=@path+@fname

如果存在(从#tb中选择1，其中a=1)

set @ const = ' DRIVER = { Microsoft Excel DRIVER(*。xls)}；DSN =“”；“只读=假”

+';CREATE _ DB = " '+@ SQL+' "；DBQ='+@sql

其他

set @ constr = ' Provider = Microsoft。Jet . OLEDB.4.0扩展属性= " Excel 5.0HDR =是'

+';DATABASE='+@sql+' " '

-连接到数据库

exec @ err = sp _ oacreate ' adodb . connection '，@obj out

if @err0转到lberr

exec @err=sp_oamethod @obj，' open '，null，@ constr

if @err0转到lberr

-用于创建表的SQL

声明@tbname sysname

set @ tbname = ' # # tmp _ '+convert(varchar(38)，newid())

set @ SQL = ' select * into['+@ TB name+']from('+@ sqlstr+')a '

exec(@sql)

select @sql= ' '，@fdlist= ' '

select @fdlist=@fdlist+'，'+a.name

，@sql=@sql+'，['+a.name+']'

+case when b.name in('char '，' nchar '，' varchar '，' nvarchar') then

text('+cast(a.length > 255，则255，否则a . length以varchar结尾)+')

当b.name in('tynyint '，' int '，' bigint '，' tinyint') then 'int '

when b.name in('smalldatetime '，' datetime') then 'datetime '

当b.name在(' money '，' smallmoney ')中时，则为' money '

else b.name结束

来自tempdb..syscolumns a左连接临时数据库..a.xtype=b.xusertype上的systypes b

其中b.name不在

(' image '，' text '，' uniqueidentifier '，' sql_variant '，' ntext '，' varbinary '，' binary '，' timestamp ')

和a.id=(从tempdb中选择id..sysobjects，其中name=@tbname)

select @ SQL = ' create table['+@ sheet name

+']('+substring(@sql，28000)+')'

，@fdlist=substring(@fdlist，28000)

exec @err=sp_oamethod @obj，' execute '，@out out，@sql

if @err0转到lberr

exec @err=sp_oadestroy @obj

-导入数据

set @ SQL = ' openrowset(' MICROSOFT。JET.OLEDB.4.0 ' '，' ' Excel 5.0HDR =是

；DATABASE='+@path+@fname+' ' '，['+@sheetname+'$])'

exec(' insert into '+@ SQL+'('+@ FD list+')select '+@ FD list+' from['+@ TB name+']')

set @ SQL = ' drop table['+@ TB name+']'

exec(@sql)

返回

lberr:

exec sp_oageterrorinfo 0，@src out，@desc out

lbexit:

选择(@ err asvarbinary (4))作为错误号

，@src作为错误源，@desc作为错误描述

select @sql，@ constr，@fdlist

去