SQL导出为Excel表

IF OBJECT_ID('ExportToExcel') IS NOT NULL DROP PROC ExportToExcel 

GO 

  

CREATE PROCEDURE ExportToExcel ( 

  @server sysname = null, 

  @uname sysname = null, 

  @pwd sysname = null, 

  @QueryText varchar(200) = null, 

  @filename varchar(200) = 'c:/ImportToExcel.xls'

) 

AS

DECLARE @SQLServer int, 

        @QueryResults int, 

        @CurrentResultSet int, 

        @object int, 

        @WorkBooks int, 

        @WorkBook int, 

        @Range int, 

        @hr int, 

        @Columns int, 

        @Rows int, 

        @indColumn int, 

        @indRow int, 

        @off_Column int, 

        @off_Row int, 

        @code_str varchar(100), 

        @result_str varchar(255) 

  

IF @QueryText IS NULL 

  BEGIN

    PRINT 'Set the query string'

    RETURN

  END

  

-- Sets the server to the local server 

IF @server IS NULL SELECT @server = @@servername 

  

-- Sets the username to the current user name 

IF @uname IS NULL SELECT @uname = SYSTEM_USER

  

SET NOCOUNT ON

  

EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @SQLServer OUT

IF @hr <> 0 

BEGIN

    PRINT 'error create SQLDMO.SQLServer'

    RETURN

END

  

--  Connect to the SQL Server 

IF @pwd IS NULL

  BEGIN

    EXEC @hr = sp_OAMethod @SQLServer, 'Connect', null, @server, @uname 

    IF @hr <> 0 

       BEGIN

         PRINT 'error Connect'

         RETURN

       END

  END

ELSE

  BEGIN

    EXEC @hr = sp_OAMethod @SQLServer, 'Connect', null, @server, @uname, @pwd 

    IF @hr <> 0 

      BEGIN

        PRINT 'error Connect'

        RETURN

      END

  END

  

SELECT @result_str = 'ExecuteWithResults("' + @QueryText + '")'

EXEC @hr = sp_OAMethod @SQLServer, @result_str, @QueryResults OUT

IF @hr <> 0 

BEGIN

    PRINT 'error with method ExecuteWithResults'

    RETURN

END

  

EXEC @hr = sp_OAMethod @QueryResults, 'CurrentResultSet', @CurrentResultSet OUT

IF @hr <> 0 

BEGIN

    PRINT 'error get CurrentResultSet'

    RETURN

END

  

EXEC @hr = sp_OAMethod @QueryResults, 'Columns', @Columns OUT

IF @hr <> 0 

BEGIN

    PRINT 'error get Columns'

    RETURN

END

  

EXEC @hr = sp_OAMethod @QueryResults, 'Rows', @Rows OUT

IF @hr <> 0 

BEGIN

    PRINT 'error get Rows'

    RETURN

END

  

EXEC @hr = sp_OACreate 'Excel.Application', @object OUT

IF @hr <> 0 

BEGIN

    PRINT 'error create Excel.Application'

    RETURN

END

  

EXEC @hr = sp_OAGetProperty @object, 'WorkBooks', @WorkBooks OUT

IF @hr <> 0 

BEGIN

    PRINT 'error create WorkBooks'

    RETURN

END

  

EXEC @hr = sp_OAGetProperty @WorkBooks, 'Add', @WorkBook OUT

IF @hr <> 0 

BEGIN

    PRINT 'error with method Add'

    RETURN

END

  

EXEC @hr = sp_OAGetProperty @object, 'Range("A1")', @Range OUT

IF @hr <> 0 

BEGIN

    PRINT 'error create Range'

    RETURN

END

  

SELECT @indRow = 1 

SELECT @off_Row = 0 

SELECT @off_Column = 1 

  

WHILE (@indRow <= @Rows) 

BEGIN

SELECT @indColumn = 1 

  

WHILE (@indColumn <= @Columns) 

BEGIN

  

EXEC @hr = sp_OAMethod @QueryResults, 'GetColumnString', @result_str OUT, @indRow, @indColumn 

IF @hr <> 0 

BEGIN

    PRINT 'error get GetColumnString'

    RETURN

END

  

EXEC @hr = sp_OASetProperty @Range, 'value', @result_str 

IF @hr <> 0 

BEGIN

    PRINT 'error set value'

    RETURN

END

  

EXEC @hr = sp_OAGetProperty @Range, 'Offset', @Range OUT, @off_Row, @off_Column 

IF @hr <> 0 

BEGIN

    PRINT 'error get Offset'

    RETURN

END

  

SELECT @indColumn = @indColumn + 1 

  

END

  

SELECT @indRow = @indRow + 1 

SELECT @code_str = 'Range("A' + LTRIM(str(@indRow)) + '")'

EXEC @hr = sp_OAGetProperty @object, @code_str, @Range OUT

IF @hr <> 0 

BEGIN

    PRINT 'error create Range'

    RETURN

END

  

END

  

SELECT @result_str = 'exec master..xp_cmdshell ''del ' + @filename + ''', no_output'

EXEC(@result_str) 

SELECT @result_str = 'SaveAs("' + @filename + '")'

EXEC @hr = sp_OAMethod @WorkBook, @result_str 

IF @hr <> 0 

BEGIN

    PRINT 'error with method SaveAs'

    RETURN

END

  

EXEC @hr = sp_OAMethod @WorkBook, 'Close'

IF @hr <> 0 

BEGIN

    PRINT 'error with method Close'

    RETURN

END

  

EXEC @hr = sp_OADestroy @object 

IF @hr <> 0 

BEGIN

    PRINT 'error destroy Excel.Application'

    RETURN

END

  

EXEC @hr = sp_OADestroy @SQLServer 

IF @hr <> 0 

BEGIN

    PRINT 'error destroy SQLDMO.SQLServer'

    RETURN

END

GO

调用方法：
EXEC ExportToExcel @server = '.', 

                   @uname = 'sa', 

                   @QueryText = 'SELECT au_fname FROM pubs..authors', 

                   @filename = 'c:/ImportToExcel.xls'

 

转自：http://www.oschina.net/code/snippet_59519_1800