Sql Server数据导出EXCEL

admin管理员组

文章数量:1441543

Sql Server数据导出EXCEL

*--数据导出EXCEL

 导出查询中的数据到Excel,包含字段名,文件为真正的Excel文件

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

 如果表不存在,将自动创建表

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

--邹建 2003.10(引用请保留此信息)--

增加分页功能

6.5w条一页

--Add by 谢小漫--

*/

/*--调用示例

 p_exporttb @sqlstr='select * from 地区资料'

  ,@path='c:\',@fname='aa.xls',@sheetname='地区资料'

--*/

CREATEproc p_exporttb

@sqlstrvarchar(8000),   --查询语句,如果查询语句中使用了order by ,请加上top 100 percent

@pathnvarchar(1000),   --文件存放目录

@fnamenvarchar(250),   --文件名

@sheetnamevarchar(250)=''--要创建的工作表名,默认为文件名

as

declare@errint,@srcnvarchar(255),@descnvarchar(255),@outint

declare@objint,@constrnvarchar(1000),@sqlvarchar(8000),@fdlistvarchar(8000),@tmpsqlvarchar(8000)

declare@sheetcountint,@sheetnowint, @recordcountint, @recordnowint

declare@sheetsqlvarchar(8000)--创建页的sql

declare@pagesizeint

set@pagesize=65000--sheet分页的大小

--set @pagesize = 1000

--参数检测

ifisnull(@fname,'')=''set@fname='temp.xls'

ifisnull(@sheetname,'')=''set@sheetname=replace(@fname,'.','#')

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

ifright(@path,1)<>'\'set@path=@path+'\'

createtable #tb(a bit,b bit,c bit)

set@sql=@path+@fname

insertinto #tb exec master..xp_fileexist @sql

--数据库创建语句

set@sql=@path+@fname

ifexists(select1from #tb where a=1)

set@constr='DRIVER={Microsoft Excel Driver (*.xls)};DSN='''';READONLY=FALSE'

+';CREATE_DB="'+@sql+'";DBQ='+@sql

else

set@constr='Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties="Excel 8.0;HDR=YES'

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

--连接数据库

exec@err=sp_oacreate 'adodb.connection',@obj out

if@err<>0goto lberr

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

if@err<>0goto lberr

--创建表的SQL

declare@tbname sysname

set@tbname='##tmp_'+convert(varchar(38),newid())

declare@tbtmpidnvarchar(50)

set@tbtmpid='tmp_'+convert(varchar(38),newid())+''

--有序列ID @tbtmpid的临时表

set@sql='select Identity(int,1,1) as ['+@tbtmpid+'], a.* into ['+@tbname+'] from ( select top 100 percent b.* from ( '+@sqlstr+') b) a'

exec(@sql)

--print(@sql)

--取得记录总数

set@recordcount=@@rowcount

if@recordcount=0return

--print @recordcount

select@sql='',@fdlist=''

select@fdlist=@fdlist+',['+a.name+']'

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

+case

when b.name like'%char'

thencasewhen a.length>255then'memo'

else'text('+cast(a.length asvarchar)+')'end

when b.name like'%int'or b.name='bit'then'int'

when b.name like'%datetime'then'datetime'

when b.name like'%money'then'money'

when b.name like'%text'then'memo'

else b.name end

FROM tempdb..syscolumns a leftjoin tempdb..systypes b on a.xtype=b.xusertype

where b.name notin('image','uniqueidentifier','sql_variant','varbinary','binary','timestamp')

and a.id=(select id from tempdb..sysobjects where name=@tbname)

and a.name <>@tbtmpid

set@fdlist=substring(@fdlist,2,8000)

--print @fdlist

--列数为零

if@@rowcount=0return

set@sheetsql=@sql

--print @sheetsql

--导入数据

--页数

set@sheetcount=CEILING(@recordcount/CAST(@pagesizeasfloat))

--print @sheetcount

--只是一个页而已

IF@sheetcount=1BEGIN

--print '只是一个页而已'

set@sql='create table ['+@sheetname

+']('+substring(@sheetsql,2,8000)+')'

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

if@err<>0goto lberr

set@sql='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''Excel 8.0;HDR=YES

       ;DATABASE='+@path+@fname+''',['+@sheetname+'$])'

exec('insert into '+@sql+'('+@fdlist+') select '+@fdlist+' from ['+@tbname+']')

END

--多个页

set@sheetnow=@sheetcount

set@recordnow=0

IF@sheetcount>1BEGIN

--print '多个页'

WHILE@sheetnow>0BEGIN

--创建页

set@sql='create table ['+@sheetname+'_'+convert(nvarchar(80),@sheetcount-@sheetnow+1)

+']('+substring(@sheetsql,2,8000)+')'

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

if@err<>0goto lberr

--print @sql

--创建页end

IF@sheetnow=@sheetcountBEGIN

set@tmpsql='select top '+str(@pagesize)+''+@fdlist+' from ['+@tbname+']'

set@sql='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''Excel 8.0;HDR=YES

    ;DATABASE='+@path+@fname+''',['+@sheetname+'_'+convert(nvarchar(80),@sheetcount-@sheetnow+1)+'$])'

exec('insert into '+@sql+'('+@fdlist+') '+@tmpsql)

END

IF@sheetnow<@sheetcountBEGIN

set@tmpsql='select top '+str(@pagesize)+''+@fdlist+' from ['+@tbname+'] where ['+@tbtmpid

+'] not in ( select top '+str(@recordnow-@pagesize)+' ['+@tbtmpid+'] from ['+@tbname+'])'

set@sql='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''Excel 8.0;HDR=YES

    ;DATABASE='+@path+@fname+''',['+@sheetname+'_'+convert(nvarchar(80),@sheetcount-@sheetnow+1)+'$])'

exec('insert into '+@sql+'('+@fdlist+') '+@tmpsql)

--print (@tmpsql)    

--exec(@tmpsql)

END

--print (@tmpsql)    

--exec (@tmpsql)    

set@recordnow=@pagesize*(@sheetcount-@sheetnow+2)

set@sheetnow=@sheetnow-1

END

END

set@sql='drop table ['+@tbname+']'

exec(@sql)

exec@err=sp_oadestroy @obj

--结束返回

return

lberr:

exec sp_oageterrorinfo 0,@src out,@desc out

lbexit:

selectcast(@errasvarbinary(4)) as 错误号

  ,@srcas 错误源,@descas 错误描述

select@sql,@constr,@fdlist

本文参与 腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。 原始发表：2021-08-17，如有侵权请联系 cloudcommunity@tencent 删除前往查看数据sqlexcelserverset

本文标签： Sql Server数据导出EXCEL