SQL Server2000 数据导出Excel(自动创建有规则的SheetName)

 

--通用的导出存储过程

/**//*+--------------------------------------
| 存储过程：SP_ExportExcel
| 功能说明：根据查询语句生成Excel文件
| 维护记录：
| 调用方式：EXEC SP_ExportExcel @SqlStr='select * from 营业执照',@Path='C:',@Fname='营业执照.XLS',@SheetName='数据',@PageSize=30000,@IsSingleTable=0,@TableName='营业执照'
| 联系方式：Spark.Zou@hotmail.com
| 创建日期：2007-05-07 12:45:24.793
| 注意事项: 
| 版权信息: 邹黎鹏
--------------------------------------+*/
ALTER  PROC SP_ExportExcel
@SqlStr     VARCHAR(8000),       --查询语句,如果查询语句中使用了order by ,请加上top 100 percent
@Path         NVARCHAR(1000),       --文件存放目录
@Fname         NVARCHAR(250),       --文件名
@SheetName     VARCHAR(250),          --要创建的工作表名,默认为文件名
@PageSize     INT=65535,        --Excel每页数据大小
@IsSingleTable     BIT=0,            --0:单表
@TableName    VARCHAR(100)=''        --单表表名
AS
BEGIN
DECLARE @PAGENUM INT,@StartPage INT,@MaxPage INT,
    @GUID VARCHAR(100),@TB_NAME VARCHAR(100),@sqls NVARCHAR(4000),@TEM_SQL  VARCHAR(4000),@SHEET_NAME VARCHAR(100),
    @FILENAME VARCHAR(200),@IDENTITYNAME VARCHAR(100),@COLUMNNAME VARCHAR(8000)
SELECT @GUID=convert(VARCHAR(100),newid()),@TEM_SQL='',@IDENTITYNAME='',@COLUMNNAME=''
SET @tb_name='##tmp_'+@GUID
SET @sqls='select @i=count(*) from ( '+@sqlstr+' ) Ta'
EXEC SP_EXECUTESQL @sqls,N'@i int output',@PAGENUM OUTPUT
IF @PageSize>65535
BEGIN
    SELECT 'Excel数据每页大小不能大于65535'
END
SELECT @MaxPage=CEILING(@PAGENUM/CAST(@PAGESIZE AS DECIMAL(18,2))),@StartPage=1
--判断Excel文件是否存在
set @Fname='Excel_'+@Fname
SET @FILENAME=@Path+@Fname
DECLARE @re INT
EXEC master..xp_fileexist @FILENAME,@re OUT
IF @re=1
BEGIN
    EXEC('exec master..xp_cmdshell ''del '+@FILENAME+'''') 
END

IF @IsSingleTable=0
BEGIN
    SELECT @IDENTITYNAME=COLUMN_NAME FROM INFORMATION_SCHEMA.columns
       WHERE TABLE_NAME=@TableName AND  COLUMNPROPERTY(      
          OBJECT_ID(@TableName),COLUMN_NAME,'IsIdentity')=1
    IF @IDENTITYNAME<>''
    BEGIN
        EXEC('SELECT * INTO ['+@tb_name+'] FROM ( '+@sqlstr+' ) TB')
    END
    ELSE
    BEGIN
        SELECT @COLUMNNAME=@COLUMNNAME+','+NAME FROM SYSCOLUMNS WHERE ID=OBJECT_ID(''+@TableName+'')
        ORDER BY COLORDER
        SET @COLUMNNAME=STUFF(@COLUMNNAME,1,1,'')
        EXEC('SELECT IDENTITY(INT,1,1) AS ['+@GUID+'ID],* INTO ['+@tb_name+'] FROM ( '+@sqlstr+' ) TB')
    END
    IF @MaxPage>26
    BEGIN
        SELECT '每页大小定义太小,请重新定义Excel中每页大小'
    END
    ELSE
    BEGIN
        WHILE @StartPage<=@MaxPage
        BEGIN
            SET @SHEET_NAME=LTRIM(@sheetname)+CHAR(64+@StartPage)
            IF @IDENTITYNAME<>''
            BEGIN
                SELECT @TEM_SQL='select * from ['+@tb_name+'] WHERE '+@IDENTITYNAME+' between '+LTRIM(@PAGESIZE)+'*('+LTRIM(@StartPage)+'-1)+1 and '+LTRIM(@PAGESIZE)+'*'+LTRIM(@StartPage)+''
            END
            ELSE
            BEGIN
                SELECT @TEM_SQL='select '+@COLUMNNAME+' from ['+@tb_name+'] WHERE ['+@GUID+'ID] between '+LTRIM(@PAGESIZE)+'*('+LTRIM(@StartPage)+'-1)+1 and '+LTRIM(@PAGESIZE)+'*'+LTRIM(@StartPage)+''
            END
            EXEC p_exporttb @sqlstr=@TEM_SQL,@path=@path,@fname=@fname,@sheetname=@SHEET_NAME
            SET @StartPage=@StartPage+1
            
        END
    END

END
ELSE
BEGIN
    EXEC('SELECT IDENTITY(INT,1,1) AS ['+@GUID+'ID],* INTO ['+@tb_name+'] FROM ( '+@sqlstr+' ) TB')
    IF @MaxPage>26
    BEGIN
        SELECT '每页大小定义太小,请重新定义Excel中每页大小'
    END
    ELSE
    BEGIN
        WHILE @StartPage<=@MaxPage
        BEGIN
            SET @SHEET_NAME=LTRIM(@sheetname)+CHAR(64+@StartPage)
            SELECT @TEM_SQL='select * from ['+@tb_name+'] WHERE ['+@GUID+'ID] between '+LTRIM(@PAGESIZE)+'*('+LTRIM(@StartPage)+'-1)+1 and '+LTRIM(@PAGESIZE)+'*'+LTRIM(@StartPage)+''
            EXEC p_exporttb @sqlstr=@TEM_SQL,@path=@path,@fname=@fname,@sheetname=@SHEET_NAME
            SET @StartPage=@StartPage+1
            
        END
    END

END

    EXEC( 'DROP TABLE ['+@tb_name+']' )

END







GO

--调用了邹建的导出EXCEL方法,如下:

create proc p_exporttb
@sqlstr varchar(8000),   --查询语句,如果查询语句中使用了order by ,请加上top 100 percent
@path nvarchar(1000),   --文件存放目录
@fname 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)

--参数检测
if isnull(@fname,'')='' set @fname='temp.xls'
if isnull(@sheetname,'')='' set @sheetname=replace(@fname,'.','#')

--检查文件是否已经存在
if right(@path,1)<>'' set @path=@path+''
create table #tb(a bit,b bit,c bit)
set @sql=@path+@fname
insert into #tb exec master..xp_fileexist @sql

--数据库创建语句
set @sql=@path+@fname
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=Microsoft.Jet.OLEDB.4.0;Extended Properties="Excel 8.0;HDR=YES'
    +';DATABASE='+@sql+'"'

--连接数据库
exec @err=sp_oacreate 'adodb.connection',@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())
set @sql='select * into ['+@tbname+'] from('+@sqlstr+') a'
exec(@sql)

select @sql='',@fdlist=''
select @fdlist=@fdlist+',['+a.name+']'
 ,@sql=@sql+',['+a.name+'] '
  +case 
   when b.name like '%char' 
   then case when a.length>255 then 'memo'
    else 'text('+cast(a.length as varchar)+')' 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 left join tempdb..systypes b on a.xtype=b.xusertype
where b.name not in('image','sql_variant','varbinary','binary','timestamp')
 and a.id=(select id from tempdb..sysobjects where name=@tbname)

if @@rowcount=0 return

select @sql='create table ['+@sheetname
 +']('+substring(@sql,2,8000)+')'
 ,@fdlist=substring(@fdlist,2,8000)

exec @err=sp_oamethod @obj,'execute',@out out,@sql
if @err<>0 goto lberr

exec @err=sp_oadestroy @obj

--导入数据
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+']')

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

exec(@sql)
return


lberr:
 exec sp_oageterrorinfo 0,@src out,@desc out
lbexit:
 select cast(@err as varbinary(4)) as 错误号
  ,@src as 错误源,@desc as 错误描述
 select @sql,@constr,@fdlist



GO