2023年7月13日发(作者：)

USE [DBNAME]

GO

/****** Object: StoredProcedure [dbo].[ExportToExcel] Script Date: 03/18/2011 13:16:39

******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

/*********************************************************************************************************************

导出数据到EXCEL中且每页为65535条数据，包含字段名，文件为真正的Excel文件格式。

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

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

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

--Creator by zoujian 2003.10

--增加分页功能，每页65000条。

--Add by xiexiaoman

--修改分页功能，每页65535条。

--Add by liujunxiong 2011.03.18

--1、修改在SQL2005/2008下配置项没有打开，解决权限的问题；

--2、修改表带自增列导出报错的问题。

--调用示例

exec ExportToExcel @ExcSql='select *

[TABLENAME]',@SavePath='D:',@FileName='',@SheetName='[TABLENAME]'

from

--存在的问题：

--1、文件只能导出到服务器进行保存。

***********************************************************************************************************************/

CREATE proc [dbo].[ExportToExcel]

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

percent

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

@FileName nvarchar(250), --文件名

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

as

declare @err int,@src nvarchar(255),@desc nvarchar(255),@out int

declare @obj int,@constr nvarchar(1000),@sql varchar(8000),@fdlist varchar(8000),@tmpsql varchar(8000)

declare @sheetcount int,@sheetnow int, @recordcount int, @recordnow int

declare @sheetsql varchar(8000)--创建页的sql

declare @pagesize int

set @pagesize = 65535 --Sheet分页的大小

--参数检测

if isnull(@FileName,'')='' set @FileName=''

if isnull(@SheetName,'')='' set @SheetName=replace(@FileName,'.','#')

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

if right(@SavePath,1)<>'' set @SavePath=@SavePath+''

create table #tb(a bit,b bit,c bit)

set @sql=@SavePath+@FileName

insert into #tb p_fileexist @sql

--数据库创建语句

set @sql=@SavePath+@FileName

if exists(select 1 from #tb where a=1)

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

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

else

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

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

--打开配置项

exec sp_configure 'show advanced options',1;

reconfigure;

exec sp_configure 'Ole Automation Procedures',1;

reconfigure;

exec sp_configure 'Ad Hoc Distributed Queries',1;

reconfigure;

--连接数据库

exec @err=sp_oacreate 'tion',@obj out

if @err<>0 goto lberr

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

if @err<>0 goto lberr

--创建表的SQL

declare @tbname sysname

set @tbname='##tmp_'+convert(varchar(38),newid()) declare @tbtmpid nvarchar(50)

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

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

--2011.03.18

--From "Identity(int,1,1)" to "newid()"

set @sql='select newid() as ['+@tbtmpid+'], a.* into ['+@tbname+'] from ( select top 100 percent

b.* from ( '+@ExcSql+') b) a'

exec(@sql)

--print(@sql)

--取得记录总数

set @recordcount= @@rowcount

if @recordcount=0 goto lrtn

--print @recordcount

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

select @fdlist=@fdlist+',['++']'

,@sql=@sql+',['++'] '

+case

when like '%char'

then case when >255 then 'memo'

else 'text('+cast( as varchar)+')' end

when like '%int' or ='bit' then 'int'

when like '%datetime' then 'datetime'

when like '%money' then 'money'

when like '%text' then 'memo'

else end

FROM tempdb..syscolumns a left join tempdb..systypes b on =ype

where not in('image','uniqueidentifier','sql_variant','varbinary','binary','timestamp')

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

and <> @tbtmpid

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

--print @fdlist

--列数为零

if @@rowcount=0 goto lrtn

set @sheetsql = @sql

--print @sheetsql

--导入数据，页数

set @sheetcount = CEILING(@recordcount/CAST(@pagesize as float)) --print @sheetcount

--只是一个页而已

IF @sheetcount = 1 BEGIN

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

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

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

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

if @err<>0 goto lberr

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

;DATABASE='+@SavePath+@FileName+''',['+@SheetName+'$])'

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

END

--多个页

set @sheetnow = @sheetcount

set @recordnow= 0

IF @sheetcount > 1 BEGIN

--print '多个页'

WHILE @sheetnow > 0 BEGIN

--创建页

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<>0 goto lberr

--print @sql

--创建页end

IF @sheetnow = @sheetcount BEGIN

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

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

;DATABASE='+@SavePath+@FileName+''',['+@SheetName+'_'+convert(nvarchar(80),@sheetcount - @sheetnow + 1)+'$])'

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

IF @sheetnow < @sheetcount BEGIN

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

['+@tbtmpid

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

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

;DATABASE='+@SavePath+@FileName+''',['+@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

--结束返回

goto lrtn;

--处理返回

lrtn:

exec sp_configure 'Ole Automation Procedures',0;

exec sp_configure 'Ad Hoc Distributed Queries',0;

exec sp_configure 'show advanced options',0;

reconfigure;

return;

--产生错误了

lberr:

exec sp_oageterrorinfo 0,@src out,@desc out

select cast(@err as varbinary(4)) as 错误号, @src as 错误源, @desc as 错误描述

select @sql, @constr, @fdlist exec sp_configure 'Ole Automation Procedures',0;

exec sp_configure 'Ad Hoc Distributed Queries',0;

exec sp_configure 'show advanced options',0;

reconfigure;