SQL Server 與 Excel 雙向資料互導完整指南：OPENROWSET、BCP、ADO 自動化匯出存儲過程

核心方法對比總覽

方法一：OPENROWSET 直連 Excel (最常用)

匯入 Excel → SQL Server

-- 目標表已存在
INSERT INTO TargetTable
SELECT * FROM OPENROWSET(
    'Microsoft.Jet.OLEDB.4.0',
    'Excel 8.0;HDR=YES;DATABASE=C:\data\source.xls',
    'SELECT * FROM [Sheet1$]'
);

-- 匯入並自動建表
SELECT * INTO NewTable
FROM OPENROWSET(
    'Microsoft.Jet.OLEDB.4.0',
    'Excel 8.0;HDR=YES;DATABASE=C:\data\source.xls',
    'SELECT * FROM [Sheet1$]'
);

匯出 SQL Server → Excel (檔案須預先存在且有表頭)

INSERT INTO OPENROWSET(
    'Microsoft.Jet.OLEDB.4.0',
    'Excel 8.0;HDR=YES;DATABASE=C:\data\target.xls',
    'SELECT * FROM [Sheet1$]'
)
SELECT * FROM SourceTable;

關鍵參數說明

• HDR=YES：第一行為表頭 (預設)
• HDR=NO：無表頭，欄位名為 F1, F2...
• IMEX=1：混合型資料欄位匯入模式 (避免數值/文字衝突)
• Sheet1$：工作表名 + $ 符號 (必須)
• 64位環境：需安裝 Microsoft Access Database Engine 2010/2016 Redistributable (x64)，Provider 改為 Microsoft.ACE.OLEDB.12.0

方法二：BCP 高效匯出 (命令列/預存過程)

-- 匯出整表 (產生類 CSV 文字檔)
EXEC master..xp_cmdshell 
'bcp Database.dbo.TableName out "C:\export\data.xls" -c -T -S"ServerName"'

-- 匯出查詢結果
EXEC master..xp_cmdshell 
'bcp "SELECT col1, col2 FROM Database.dbo.Table ORDER BY col1" queryout "C:\export\query.xls" -c -T -S"ServerName"'

-- 參數說明
-c          字元模式 (Tab 分隔)
-T          信任連線 (Windows 整合驗證)
-U/P        SQL 登入帳號/密碼
-S          伺服器名稱

⚠️ BCP 產生純文字檔，副檔名 .xls 但非真正 Excel 格式，Excel 開啟會警告格式不符。

方法三：ADO 自動化匯出真正 Excel (完整存儲過程) ⭐

功能：自動建立 .xls、建立工作表、寫入表頭與資料、支援標準資料類型。

IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID('p_exporttb') AND OBJECTPROPERTY(id, 'IsProcedure') = 1)
    DROP PROCEDURE p_exporttb
GO

CREATE PROCEDURE p_exporttb
    @tbname SYSNAME,           -- 要匯出的表名
    @path NVARCHAR(1000),      -- 檔案目錄
    @fname NVARCHAR(250) = ''  -- 檔案名 (預設表名.xls)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @err INT, @obj INT, @constr NVARCHAR(1000), @sql VARCHAR(8000), @fdlist VARCHAR(8000);
    DECLARE @out INT, @src NVARCHAR(255), @desc NVARCHAR(255);

    -- 參數預設
    IF ISNULL(@fname, '') = '' SET @fname = @tbname + '.xls';
    IF RIGHT(@path, 1) <> '\' SET @path = @path + '\';
    DECLARE @fullpath NVARCHAR(1100) = @path + @fname;

    -- 檢查檔案是否存在
    CREATE TABLE #tmp (a BIT, b BIT, c BIT);
    INSERT INTO #tmp EXEC master..xp_fileexist @fullpath;

    -- 建立 ADO 連線字串
    IF EXISTS (SELECT 1 FROM #tmp WHERE a = 1)
        SET @constr = 'DRIVER={Microsoft Excel Driver (*.xls)};DSN="";READONLY=FALSE;CREATE_DB="' + @fullpath + '";DBQ=' + @fullpath;
    ELSE
        SET @constr = 'DRIVER={Microsoft Excel Driver (*.xls)};DSN="";READONLY=FALSE;CREATE_DB="' + @fullpath + '";DBQ=' + @fullpath;

    -- 建立 ADO Connection 物件
    EXEC @err = sp_OACreate 'ADODB.Connection', @obj OUT;
    IF @err <> 0 GOTO LBL_ERR;
    EXEC @err = sp_OAMethod @obj, 'Open', NULL, @constr;
    IF @err <> 0 GOTO LBL_ERR;

    -- 產生建表 SQL (排除不支援類型: image, text, ntext, binary, timestamp 等)
    SELECT @sql = '', @fdlist = '';
    SELECT 
        @fdlist = @fdlist + ',[' + c.name + ']',
        @sql = @sql + ',[' + c.name + '] ' + 
            CASE 
                WHEN t.name IN ('char','nchar','varchar','nvarchar') THEN 'TEXT(' + CAST(CASE WHEN c.max_length > 255 THEN 255 ELSE c.max_length END AS VARCHAR) + ')'
                WHEN t.name IN ('tinyint','smallint','int','bigint') THEN 'INTEGER'
                WHEN t.name IN ('smalldatetime','datetime') THEN 'DATETIME'
                WHEN t.name IN ('money','smallmoney') THEN 'CURRENCY'
                ELSE t.name
            END
    FROM sys.columns c
    JOIN sys.types t ON c.system_type_id = t.system_type_id
    WHERE c.object_id = OBJECT_ID(@tbname)
      AND t.name NOT IN ('image','text','ntext','uniqueidentifier','sql_variant','varbinary','binary','timestamp','geography','geometry','hierarchyid');

    SELECT @sql = 'CREATE TABLE [' + @tbname + '] (' + SUBSTRING(@sql, 2, 8000) + ')';
    SELECT @fdlist = SUBSTRING(@fdlist, 2, 8000);

    -- 執行建表
    EXEC @err = sp_OAMethod @obj, 'Execute', @out OUT, @sql;
    IF @err <> 0 GOTO LBL_ERR;

    -- 匯入資料 (使用 OPENROWSET 回寫)
    SET @sql = 'INSERT INTO OPENROWSET(''Microsoft.Jet.OLEDB.4.0'', ''Excel 8.0;HDR=YES;DATABASE=' + @fullpath + ''', [' + @tbname + '$]) (' + @fdlist + ') SELECT ' + @fdlist + ' FROM ' + @tbname;
    EXEC(@sql);

    -- 清理
    EXEC sp_OADestroy @obj;
    DROP TABLE #tmp;
    RETURN;

LBL_ERR:
    EXEC sp_OAGetErrorInfo @obj, @src OUT, @desc OUT;
    SELECT @err AS ErrorCode, @src AS ErrorSource, @desc AS ErrorDescription;
    EXEC sp_OADestroy @obj;
    DROP TABLE #tmp;
END
GO

呼叫範例

-- 匯出指定表到指定路徑
EXEC p_exporttb @tbname = '客戶資料', @path = 'C:\Export', @fname = 'Customers.xls';

前置需求檢查清單

1. sp_configure 'Ole Automation Procedures', 1; RECONFIGURE; (啟用 OLE 自動化)
2. sp_configure 'Ad Hoc Distributed Queries', 1; RECONFIGURE; (啟用 OPENROWSET)
3. 安裝 Access Database Engine (32/64位匹配 SQL Server 版本)
4. 服務帳號對目標資料夾具備讀寫權限
5. Excel 檔案未被其他程式佔用 (獨佔鎖定)

現代替代方案建議

• SSIS (SQL Server Integration Services)：企業級 ETL、視覺化設計、排程部署
• PolyBase / OPENROWSET (Parquet/ORC)：大數據格式、雲端儲存整合
• Python/pandas + pyodbc：彈性腳本化、資料科學整合
• Power Query / Power BI：商業智慧自助式資料準備