Export table to Excel file

USE [WorkSpace]

GO

/****** Object:  StoredProcedure [dbo].[usp_export_table]    Script. Date: 12/02/200911:43:36 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:    Larry Song

-- Create date: 2009-12-02

-- Description:   Export data from table to excel file

-- =============================================

 

/*-- EXAMPLE:

 

 EXEC [dbo].[usp_export_table]

    @sys_tb_name='tb_name',

    @nvc_folder='c:\',

    @nvc_file_name='test.xls',

    @nvc_sheet_name='sheet_name'

--*/

ALTER PROCEDURE [dbo].[usp_export_table]

    @sys_tb_name      SYSNAME,          -- table name which needs to be exported

    @nvc_folder          NVARCHAR(4000),      -- the folder of the exported excel file

    @nvc_file_name       NVARCHAR(255)='', -- excel file name, default is table name

    @nvc_sheet_name      NVARCHAR(255)=''  -- sheet name, default is file name

AS

BEGIN

    DECLARE @nvc_err INT,@nvc_src NVARCHAR(255),@nvc_desc NVARCHAR(255),@OUT INT,@nvc_del NVARCHAR(255),@int_flag INT

    DECLARE @nvc_obj INT,@nvc_constr NVARCHAR(4000),@nvc_sql NVARCHAR(4000),@nvc_field_list NVARCHAR(4000)

   

    BEGIN TRY

       --BEGIN TRANSACTION

 

           -- Check Parameters

           IF RIGHT(@nvc_folder,   1) <> '\' 

           BEGIN

                SET @nvc_folder = @nvc_folder + '\' 

           END

           IF ISNULL (@nvc_file_name,'')=''

              SET @nvc_file_name=@sys_tb_name+'.xls'

           IF ISNULL (@nvc_sheet_name,'')=''

              SET @nvc_sheet_name=REPLACE (@nvc_file_name,'.','#')

      

      

           -- Whether the file exists or not?

      

           CREATE TABLE #tb(a BIT,b BIT,c BIT)

           SET @nvc_sql=@nvc_folder+@nvc_file_name

           --  del the file

       /*  EXECUTE   master..xp_fileexist   @nvc_sql,   @int_flag   OUTPUT  

           IF @int_flag <> 0

           BEGIN

              SET @nvc_del = 'del ' + @nvc_sql

              EXEC xp_cmdshell @nvc_del

           END

       */  INSERT INTO #tb EXEC master..xp_fileexist @nvc_sql

      

           -- Create the DB

           SET @nvc_sql=@nvc_folder+@nvc_file_name

           IF EXISTS(SELECT 1 FROM #tb WHERE a=1)

           BEGIN

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

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

           END

           ELSE

           BEGIN

            SET @nvc_constr='Provider=Microsoft.Jet.OLEDB.4.0;EXTENDED Properties="Excel 5.0;HDR=YES'

              +';DATABASE='+@nvc_sql+'"'

           END

      

           -- Connect to DB

           EXEC @nvc_err=sp_OACREATE 'adodb.connection',@nvc_obj OUT

           IF @nvc_err<>0

              GOTO err_section

      

           EXEC @nvc_err=sp_OAMethod @nvc_obj,'open',null,@nvc_constr

           IF @nvc_err<>0

              GOTO err_section

      

           -- Create the table in EXCEL file

           SELECT @nvc_sql='',@nvc_field_list=''

      

           SELECT @nvc_field_list=@nvc_field_list+',['+a.name+']'

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

             +CASE

              WHEN b.name LIKE '%CHAR'

              THEN CASE WHEN a.length>255 OR a.length < 0 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 syscolumns a LEFT JOIN systypes b

           ON a.xtype=b.xusertype

           WHERE b.name NOT IN('image','uniqueidentIFier','sql_variant','varbinary','binary','timestamp')

            AND OBJECT_ID(@sys_tb_name)=id

      

           SELECT @nvc_sql='CREATE TABLE ['+@nvc_sheet_name

            +']('+SUBSTRING(@nvc_sql,2,8000)+')'

            ,@nvc_field_list=SUBSTRING(@nvc_field_list,2,8000)

      

           EXEC @nvc_err=sp_oamethod @nvc_obj,'EXECUTE',@OUT OUT,@nvc_sql

           IF @nvc_err<>0

              GOTO err_section

             

           EXEC @nvc_err=sp_oamethod @nvc_obj,'Close'

           IF @nvc_err<>0

              GOTO err_section

      

           EXEC @nvc_err=sp_oadestroy @nvc_obj

      

           -- Export the data to EXCEL file

           SET @nvc_sql='OPENROWSET(''MICROSOFT.JET.OLEDB.4.0'',''Excel 5.0;HDR=YES;

              ;DATABASE='+@nvc_folder+@nvc_file_name+''',['+@nvc_sheet_name+'$])'

      

           EXEC('INSERT INTO '+@nvc_sql+'('+@nvc_field_list+') SELECT '+@nvc_field_list+' FROM '+@sys_tb_name)

      

           return

      

           err_section:

            EXEC sp_oageterrorinfo 0,@nvc_src OUT,@nvc_desc OUT

           exit_section:

            SELECT CAST(@nvc_err AS varbinary(4)) AS 'ErrorNum'

             ,@nvc_src AS 'ErrorSource',@nvc_desc AS 'ErrorDesc'

            SELECT @nvc_sql,@nvc_constr,@nvc_field_list

       --COMMIT TRANSACTION

    END TRY

 

    BEGIN CATCH

       EXEC usp_error_handler '[usp_export_table]'

    END CATCH

 

END

 

需要注意的几点：

1 ''MICROSOFT.JET.OLEDB.4.0''不支持事务，因此不能在事务中使用！

2  sp_oamethod 的使用，在调用sp_oadestroy 之前，先需要将 sp_oamethod  Close，否则资源将不会释放，造成操作失败！

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/518079/viewspace-660423/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/518079/viewspace-660423/