SQL导出为Excel表

WBOY
WBOY원래의
2016-06-07 14:54:441445검색

SQL导出为Excel表 Excel Version: SQL Server 7.0/2000Created by: Alexander Chigrikhttp://www.MSSQLCity.com/ - all about MS SQL(SQL Server Articles, FAQ, Scripts, Tips and Test Exams). This stored procedure can be used to insert the result set

SQL导出为Excel表 Excel
Version: SQL Server 7.0/2000
Created by: Alexander Chigrik
http://www.MSSQLCity.com/ - all about MS SQL
(SQL Server Articles, FAQ, Scripts, Tips and Test Exams). 

This stored procedure can be used to insert the result set of the
particular select statement into Excel file (c:\ImportToExcel.xls,
by default).
You can pass the server name, user name, user password, the select
statement to execute, and the file name to store the results set,
as in the example below:

EXEC ExportToExcel @server = '.',
                   @uname = 'sa',
                   @QueryText = 'SELECT au_fname FROM pubs..authors',
                   @filename = 'c:\ImportToExcel.xls'

/*
Version: SQL Server 7.0/2000
Created by: Alexander Chigrik
http://www.MSSQLCity.com/ - all about MS SQL
(SQL Server Articles, FAQ, Scripts, Tips and Test Exams).

This stored procedure can be used to insert the result set of the
particular select statement into Excel file (c:\ImportToExcel.xls,
by default).
You can pass the server name, user name, user password, the select
statement to execute, and the file name to store the results set,
as in the example below:

EXEC ExportToExcel @server = '.',
                   @uname = 'sa',
                   @QueryText = 'SELECT au_fname FROM pubs..authors',
                   @filename = 'c:\ImportToExcel.xls'
*/

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
성명:
본 글의 내용은 네티즌들의 자발적인 기여로 작성되었으며, 저작권은 원저작자에게 있습니다. 본 사이트는 이에 상응하는 법적 책임을 지지 않습니다. 표절이나 침해가 의심되는 콘텐츠를 발견한 경우 admin@php.cn으로 문의하세요.