SQL Server與Excel雙向資料導入導出完整方案

基礎方法：OPENROWSET (Jet OLEDB 4.0)

Excel → SQL Server

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

-- 目標表不存在（自動建表）
SELECT * INTO 目標表 FROM OPENROWSET(
  'Microsoft.Jet.OLEDB.4.0',
  'Excel 5.0;HDR=YES;DATABASE=c:\test.xls', sheet1$)

SQL Server → Excel (檔案已存在且有表頭)

INSERT INTO OPENROWSET(
  'Microsoft.Jet.OLEDB.4.0',
  'Excel 5.0;HDR=YES;DATABASE=c:\test.xls', sheet1$)
SELECT * FROM 來源表

SQL Server → 類Excel文字檔 (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"密碼"'

注意：BCP產出為**純文字檔**（CSV/Tab分隔），非真正Excel二進位格式（.xls/.xlsx）。

進階：真正Excel檔案導出存儲過程 (p_exporttb)

利用sp_OACreate建立ADODB.Connection，連接Microsoft Excel Driver (*.xls) ODBC驅動程式，動態建立Excel資料表結構（對應SQL型別→Excel型別：字串→Text(255)、整數→Int、日期→Datetime、貨幣→Money），再用OPENROWSET回寫資料。支援：

• 檔案不存在自動建立
• 表不存在自動建立（含欄位名稱列）
• 僅導出標準資料型別（排除image/text/xml/二進位等）

調用範例

EXEC p_exporttb @tbname = '地區資料', @path = 'c:\', @fname = 'aa.xls'

關鍵參數說明

• HDR=YES：首列為欄位名稱。
• sheet1$：工作表名**必須加$符號**。
• 64位元環境需安裝Microsoft Access Database Engine 2010/2016 Redistributable (64-bit)，Jet 4.0僅32位元。