SQL Server 與 Excel 雙向匯入匯出完整方案：OPENROWSET、BCP 與自動化預存程序

技術選型對照

OPENROWSET 核心範本 (需 Microsoft.ACE.OLEDB.12.0/16.0)

-- 1. 匯入既有表 (工作表名 = Sheet1$，HDR=YES 表頭為欄位名)
INSERT INTO dbo.TargetTable (Col1, Col2, Col3)
SELECT Col1, Col2, Col3
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0;HDR=YES;Database=C:\Data\Source.xlsx;',
    'SELECT * FROM [Sheet1$]');

-- 2. 匯入並建新表 (結構推導)
SELECT * INTO dbo.NewTable
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0;HDR=YES;Database=C:\Data\Source.xlsx;',
    'SELECT * FROM [Sheet1$]');

-- 3. 匯出至既有 Excel (需預建工作表與表頭)
INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0;HDR=YES;Database=C:\Data\Target.xlsx;',
    'SELECT * FROM [Sheet1$]')
SELECT Col1, Col2, Col3 FROM dbo.SourceTable;

-- 4. 關鍵連接字串參數
-- HDR=YES|NO          表頭處理
-- IMEX=1              混合型別欄位強制文字 (避免數值截斷)
-- TypeGuessRows=0     掃描所有列決定型別 (預設 8 列)
-- ReadOnly=False      允許寫入

BCP 匯出真 Excel 替代方案：CSV + 後處理

REM 匯出 CSV (含表頭需 UNION ALL 技巧或外部工具)
bcp "SELECT 'Col1','Col2','Col3' UNION ALL SELECT CAST(Col1 AS VARCHAR), CAST(Col2 AS VARCHAR), CAST(Col3 AS VARCHAR) FROM Db.dbo.Table" queryout "C:\Exp\data.csv" -c -t, -T -S.

REM 後續用 PowerShell/Excel 自動化轉 .xlsx
powershell -Command "Import-Csv C:\Exp\data.csv | Export-Excel -Path C:\Exp\data.xlsx -AutoSize"

企業級自動化預存程序：產出真 .xls (相容舊版) / .xlsx

原理：利用 sp_OACreate 建立 ADODB.Connection → Jet/ACE Provider 建立 Excel 檔與工作表 → INSERT INTO OPENROWSET 寫入資料。注意：需啟用 Ole Automation Procedures，生產環境建議改用 SSIS/CLR 替代。

-- 建立匯出表預存程序 (支援 .xls)
CREATE PROCEDURE dbo.usp_ExportTableToExcel
    @TableName    sysname,        -- 來源表
    @FilePath     nvarchar(1000), -- 目錄 (需以 \ 結尾)
    @FileName     nvarchar(250) = '' -- 檔名，預設 TableName.xls
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @FullPath nvarchar(1200) = @FilePath + ISNULL(NULLIF(@FileName,''), @TableName + '.xls');
    DECLARE @ConnStr  nvarchar(1000), @SQL nvarchar(4000), @Err int, @Obj int, @Out int;

    -- 1. 建立 Excel 檔與工作表 (Jet 4.0 僅支援 .xls)
    IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'tempdb..#FileCheck'))
        DROP TABLE #FileCheck;
    CREATE TABLE #FileCheck (Exists bit, Dir bit, Parent bit);
    INSERT #FileCheck EXEC master..xp_fileexist @FullPath;

    IF (SELECT Exists FROM #FileCheck) = 1
        SET @ConnStr = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' + @FullPath + ';Extended Properties="Excel 8.0;HDR=YES;READONLY=FALSE"';
    ELSE
        SET @ConnStr = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' + @FullPath + ';Extended Properties="Excel 8.0;HDR=YES;READONLY=FALSE"';

    EXEC @Err = sp_OACreate 'ADODB.Connection', @Obj OUT;
    IF @Err <> 0 GOTO ErrHandler;
    EXEC @Err = sp_OAMethod @Obj, 'Open', NULL, @ConnStr;
    IF @Err <> 0 GOTO ErrHandler;

    -- 2. 動態建立 CREATE TABLE 陳述式 (排除不支援型別: image, text, xml, udt, binary, timestamp)
    SELECT @SQL = 'CREATE TABLE [' + @TableName + '] ('
        + STUFF((SELECT ', [' + c.name + '] ' +
            CASE t.name
                WHEN 'varchar' THEN 'VARCHAR(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length AS varchar) END + ')'
                WHEN 'nvarchar' THEN 'NVARCHAR(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length/2 AS varchar) END + ')'
                WHEN 'char' THEN 'CHAR(' + CAST(c.max_length AS varchar) + ')'
                WHEN 'nchar' THEN 'NCHAR(' + CAST(c.max_length/2 AS varchar) + ')'
                WHEN 'int' THEN 'INTEGER'
                WHEN 'bigint' THEN 'LONG'
                WHEN 'smallint' THEN 'SHORT'
                WHEN 'tinyint' THEN 'BYTE'
                WHEN 'datetime' THEN 'DATETIME'
                WHEN 'smalldatetime' THEN 'DATETIME'
                WHEN 'date' THEN 'DATE'
                WHEN 'time' THEN 'TIME'
                WHEN 'money' THEN 'CURRENCY'
                WHEN 'decimal' THEN 'DECIMAL(' + CAST(c.precision AS varchar) + ',' + CAST(c.scale AS varchar) + ')'
                WHEN 'numeric' THEN 'DECIMAL(' + CAST(c.precision AS varchar) + ',' + CAST(c.scale AS varchar) + ')'
                WHEN 'bit' THEN 'YESNO'
                WHEN 'float' THEN 'DOUBLE'
                WHEN 'real' THEN 'SINGLE'
                ELSE 'VARCHAR(255)'
            END
        FROM sys.columns c JOIN sys.types t ON c.user_type_id = t.user_type_id
        WHERE c.object_id = OBJECT_ID(@TableName) AND t.name NOT IN ('image','text','ntext','xml','varbinary','binary','timestamp','uniqueidentifier','sql_variant','hierarchyid','geometry','geography')
        FOR XML PATH(''), TYPE).value('.','nvarchar(max)'), 1, 2, '') + ')';

    EXEC @Err = sp_OAMethod @Obj, 'Execute', @Out OUT, @SQL;
    IF @Err <> 0 GOTO ErrHandler;

    -- 3. 寫入資料
    DECLARE @ColList nvarchar(max) = STUFF((SELECT ', [' + name + ']' FROM sys.columns WHERE object_id = OBJECT_ID(@TableName) FOR XML PATH(''), TYPE).value('.','nvarchar(max)'), 1, 2, '');
    SET @SQL = 'INSERT INTO OPENROWSET(''Microsoft.Jet.OLEDB.4.0'', ''Excel 8.0;HDR=YES;Database=' + @FullPath + ''', [' + @TableName + '$]) (' + @ColList + ') SELECT ' + @ColList + ' FROM ' + QUOTENAME(@TableName);
    EXEC (@SQL);

    EXEC sp_OADestroy @Obj;
    RETURN 0;

ErrHandler:
    DECLARE @Src nvarchar(255), @Desc nvarchar(255);
    EXEC sp_OAGetErrorInfo @Obj, @Src OUT, @Desc OUT;
    EXEC sp_OADestroy @Obj;
    RAISERROR('Excel Export Failed: %s - %s', 16, 1, @Src, @Desc);
    RETURN -1;
END

64 位元環境部署關鍵

1. 安裝 64 位元 Microsoft Access Database Engine (2010: ACE 12.0 / 2016: ACE 16.0)，不可**安裝 32 位元版本** (會衝突)。
2. 連接字串 Provider 統一使用 Microsoft.ACE.OLEDB.12.0 或 16.0。
3. SSIS 專案屬性 Run64BitRuntime = True (預設)。
4. 若必須用 32 位元 ACE (協力廠商元件相依)，需在 SSIS 設定 Run64BitRuntime = False，或建立 32 位元 SQL Agent Proxy 執行。

安全性與穩定性建議

• 停用 sp_OA*：EXEC sp_configure 'Ole Automation Procedures', 0; RECONFIGURE; 生產環境預設關閉，降低攻擊面。
• 檔案路徑白名單**：預存程序驗證 @FilePath 僅允許指定匯出目錄，防止目錄遍歷。
• 服務帳號權限最小化**：僅授予匯出目錄 Write、匯入目錄 Read。
• 大量資料改用 SSIS/BCP**：COM 自動化單執行緒、效能差、易記憶體洩漏，僅用於小量報表匯出 (≤ 5 萬列)。