SQL Server 透過 OLE Automation 將查詢結果匯出至 Excel

本文提供一個預存程序 ExportToExcel，利用 SQL Server 的 OLE Automation 程序（sp_OA* 系列）將任意 SELECT 查詢結果直接寫入 Excel 檔案。適用於 SQL Server 7.0/2000 環境，亦可在較新版本相容模式下運作。

使用範例

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
BEGIN
    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
    IF @server IS NULL SELECT @server = @@SERVERNAME;
    IF @uname IS NULL SELECT @uname = SYSTEM_USER;
    SET NOCOUNT ON;

    -- 建立 SQLDMO 連線
    EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @SQLServer OUT;
    IF @hr <> 0 BEGIN PRINT 'error create SQLDMO.SQLServer'; RETURN; END

    IF @pwd IS NULL
        EXEC @hr = sp_OAMethod @SQLServer, 'Connect', NULL, @server, @uname;
    ELSE
        EXEC @hr = sp_OAMethod @SQLServer, 'Connect', NULL, @server, @uname, @pwd;
    IF @hr <> 0 BEGIN PRINT 'error Connect'; RETURN; 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

    -- 建立 Excel 應用程式
    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, @off_Row = 0, @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
END;
GO

注意事項

• 需啟用 Ole Automation Procedures 伺服器設定（sp_configure 'Ole Automation Procedures', 1; RECONFIGURE;）。
• 執行身分需具備 Excel 安裝與檔案系統寫入權限。
• 大量資料時效能較差，建議改用 SSIS、BCP 或 OPENROWSET 等現代化方式。

此方案雖屬經典，但在無法安裝額外工具的舊版環境中仍具參考價值。