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