SQL Server 與 Excel 資料互導完整方案：OPENROWSET、BCP、自動化預存程序

前言

Excel 與 SQL Server 資料互導是常見 ETL 需求。本文提供三層次方案：

1. 簡易單行語句：適用臨時/一次性任務
2. BCP 命令列工具：適合大量資料、排程自動化
3. 完整自動化預存程序：支援建檔、建表、型別映射、欄位名保留的企業級方案

方案一：OPENROWSET 單行語句 (Ad Hoc)

前置條件

-- 啟用 Ad Hoc Distributed Queries (需 sysadmin)
EXEC sp_configure 'show advanced options', 1; RECONFIGURE
EXEC sp_configure 'Ad Hoc Distributed Queries', 1; RECONFIGURE

導入 Excel → SQL Server

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

-- 目標表不存在，自動建表
SELECT * INTO 目標表
FROM OPENROWSET(
    'Microsoft.Jet.OLEDB.4.0',
    'Excel 8.0;HDR=YES;DATABASE=C:\test.xls',
    'SELECT * FROM [Sheet1$]')

導出 SQL Server → Excel (檔案與表頭已存在)

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

方案二：BCP 命令列導出 (適合大量資料)

-- 導出整張表
EXEC master..xp_cmdshell 'bcp 資料庫名.dbo.表名 out "C:\test.xls" /c -S"伺服器名" -U"用戶名" -P"密碼"'

-- 導出查詢結果
EXEC master..xp_cmdshell 'bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout "C:\test.xls" /c -S"伺服器名" -U"用戶名" -P"密碼"'

-- 參數說明
-- /c        : 字元模式 (預設 tab 分隔)
-- /t 分隔符 : 自訂分隔符，如 /t"," 為 CSV
-- /r 換行符 : 自訂換行符
-- /w        : Unicode 模式 (支援中文)

方案三：企業級自動化預存程序 p_exporttb

功能特色：

• 自動建立真正的 .xls 檔案 (非純文字)
• 自動建立工作表、寫入欄位名
• 智慧型別映射 (varchar→text/memo, int→int, datetime→datetime, money→money 等)
• 支援導出「表」或「任意查詢結果」
• 錯誤處理與詳細回傳訊息

版本 A：導出指定表

CREATE PROC p_exporttb
    @tbname sysname,           -- 來源表名
    @path nvarchar(1000),      -- 目錄路徑
    @fname nvarchar(250) = ''  -- 檔名，預設 表名.xls
AS
BEGIN
    -- 1. 參數檢核與路徑正規化
    IF ISNULL(@fname, '') = '' SET @fname = @tbname + '.xls'
    IF RIGHT(@path, 1) <> '\' SET @path = @path + '\'
    
    -- 2. 檢查檔案是否存在，決定連接字串 (新建用 Jet OLEDB，既有用 Excel Driver)
    CREATE TABLE #tb(a bit, b bit, c bit)
    DECLARE @sql nvarchar(1000) = @path + @fname
    INSERT INTO #tb EXEC master..xp_fileexist @sql
    
    DECLARE @constr nvarchar(1000)
    IF EXISTS(SELECT 1 FROM #tb WHERE a = 1)
        SET @constr = 'DRIVER={Microsoft Excel Driver (*.xls)};DSN='''';READONLY=FALSE;CREATE_DB="' + @sql + '";DBQ=' + @sql
    ELSE
        SET @constr = 'Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties="Excel 8.0;HDR=YES;DATABASE=' + @sql + '"'
    
    -- 3. 建立 ADO 連接
    DECLARE @obj int, @err int, @out int
    EXEC @err = sp_OACreate 'ADODB.Connection', @obj OUT
    IF @err <> 0 GOTO lberr
    EXEC @err = sp_OAMethod @obj, 'Open', NULL, @constr
    IF @err <> 0 GOTO lberr
    
    -- 4. 動態產生 CREATE TABLE 陳述式 (依 syscolumns/systypes 映射型別)
    DECLARE @fdlist varchar(8000) = '', @createSql varchar(8000) = ''
    SELECT 
        @fdlist = @fdlist + ',[' + a.name + ']',
        @createSql = @createSql + ',[' + a.name + '] ' +
            CASE
                WHEN b.name LIKE '%char' THEN CASE WHEN a.length > 255 THEN 'memo' ELSE 'text(' + CAST(a.length AS varchar) + ')' END
                WHEN b.name LIKE '%int' OR b.name = 'bit' THEN 'int'
                WHEN b.name LIKE '%datetime' THEN 'datetime'
                WHEN b.name LIKE '%money' THEN 'money'
                WHEN b.name LIKE '%text' THEN 'memo'
                ELSE b.name
            END
    FROM syscolumns a LEFT JOIN systypes b ON a.xtype = b.xusertype
    WHERE b.name NOT IN ('image','uniqueidentifier','sql_variant','varbinary','binary','timestamp')
      AND OBJECT_ID(@tbname) = a.id
    
    SET @createSql = 'CREATE TABLE [' + @tbname + '](' + SUBSTRING(@createSql, 2, 8000) + ')'
    SET @fdlist = SUBSTRING(@fdlist, 2, 8000)
    
    EXEC @err = sp_OAMethod @obj, 'Execute', @out OUT, @createSql
    IF @err <> 0 GOTO lberr
    EXEC @err = sp_OADestroy @obj
    IF @err <> 0 GOTO lberr
    
    -- 5. 寫入資料
    SET @sql = 'OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',''Excel 8.0;HDR=YES;IMEX=1;DATABASE=' + @path + @fname + ''',[' + @tbname + '$])'
    EXEC('INSERT INTO ' + @sql + '(' + @fdlist + ') SELECT ' + @fdlist + ' FROM ' + @tbname)
    RETURN
    
lberr:
    EXEC sp_OAGetErrorInfo 0, @src OUT, @desc OUT
    SELECT CAST(@err AS varbinary(4)) AS 錯誤碼, @src AS 錯誤源, @desc AS 錯誤描述
END
GO

版本 B：導出任意查詢結果 (支援複雜 JOIN/彙總)

CREATE PROC p_exporttb
    @sqlstr varchar(8000),     -- 查詢語句，含 ORDER BY 時需加 TOP 100 PERCENT
    @path nvarchar(1000),
    @fname nvarchar(250),
    @sheetname varchar(250) = ''  -- 工作表名，預設檔名
AS
BEGIN
    -- 參數檢核
    IF ISNULL(@fname, '') = '' SET @fname = 'temp.xls'
    IF ISNULL(@sheetname, '') = '' SET @sheetname = REPLACE(@fname, '.', '#')
    
    -- 建立全域暫表暫存查詢結果
    DECLARE @tbname sysname = '##tmp_' + CONVERT(varchar(38), NEWID())
    EXEC('SELECT * INTO [' + @tbname + '] FROM (' + @sqlstr + ') a')
    
    -- 後續步驟同版本 A：建立 Excel 連接 → 依 tempdb 系統表映射型別 → CREATE TABLE → INSERT OPENROWSET
    -- 最後清理暫表
    EXEC('DROP TABLE [' + @tbname + ']')
END
GO

呼叫範例

-- 導出表
EXEC p_exporttb @tbname = '薪資報表', @path = 'E:\', @fname = 'salary.xls'

-- 導出查詢
EXEC p_exporttb @sqlstr = 'SELECT Dept, SUM(Salary) AS Total FROM Salary GROUP BY Dept', 
                @path = 'E:\', @fname = 'dept_sum.xls', @sheetname = '部門彙總'

關鍵注意事項

現代替代方案

• SQL Server Integration Services (SSIS)：企業級 ETL 平台，視覺化設計、錯誤處理、排程、日誌完善
• OPENROWSET + ACE OLEDB：支援 .xlsx，無 65535 列限制
• CLR / Python / R 腳本：使用 openpyxl、pandas 靈活處理複雜格式