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
去
位律师回复
0条评论