提供一個預存程序 p_exporttb，可將指定資料表或檢視表的資料匯出為真正的 Excel 檔案，自動包含欄位名稱作為標題列。若檔案不存在則自動建立，若工作表不存在亦自動建立。僅支援標準資料類型，確保相容性。
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[p_exporttb]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[p_exporttb];
GO
CREATE PROCEDURE p_exporttb
@tbname SYSNAME, -- 要匯出的資料表/檢視表名稱
@path NVARCHAR(1000), -- 檔案儲存目錄
@fname NVARCHAR(250) = '' -- 檔案名稱，預設為 資料表名.xls
AS
BEGIN
SET NOCOUNT ON;
DECLARE @err INT,
@src NVARCHAR(255),
@desc NVARCHAR(255),
@out INT,
@obj INT,
@constr NVARCHAR(1000),
@sql VARCHAR(8000),
@fdlist VARCHAR(8000);
-- 參數預設處理
IF ISNULL(@fname, '') = ''
SET @fname = @tbname + '.xls';
-- 確保路徑以反斜線結尾
IF RIGHT(@path, 1) <> '\'
SET @path = @path + '\';
SET @sql = @path + @fname;
-- 檢查檔案是否存在
CREATE TABLE #tb (a BIT, b BIT, c BIT);
INSERT INTO #tb EXEC master..xp_fileexist @sql;
-- 根據檔案是否存在建立不同連接字串
IF EXISTS (SELECT 1 FROM #tb WHERE a = 1)
begin
-- 檔案已存在：使用 Excel ODBC 驅動程式開啟現有檔案
SET @constr = 'DRIVER={Microsoft Excel Driver (*.xls)};DSN='''';READONLY=FALSE'
+ ';CREATE_DB="' + @sql + '";DBQ=' + @sql;
end
else
begin
-- 檔案不存在：使用 Jet OLEDB 建立新的 Excel 5.0 格式檔案
SET @constr = 'Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties="Excel 5.0;HDR=YES'
+ ';DATABASE=' + @sql + '"';
end
DROP TABLE #tb;
-- 建立 ADODB.Connection 物件
EXEC @err = sp_OACreate 'ADODB.Connection', @obj OUT;
IF @err <> 0 GOTO ErrorHandler;
-- 開啟連接
EXEC @err = sp_OAMethod @obj, 'Open', NULL, @constr;
IF @err <> 0 GOTO ErrorHandler;
-- 動態產生 CREATE TABLE 陳述式
SELECT @sql = '', @fdlist = '';
SELECT @fdlist = @fdlist + ',[' + a.name + ']',
@sql = @sql + ',[' + a.name + '] ' +
CASE
WHEN b.name IN ('char','nchar','varchar','nvarchar') THEN
'TEXT(' + CAST(CASE WHEN a.length > 255 THEN 255 ELSE a.length END AS VARCHAR) + ')'
WHEN b.name IN ('tinyint','int','bigint') THEN 'INT'
WHEN b.name IN ('smalldatetime','datetime') THEN 'DATETIME'
WHEN b.name IN ('money','smallmoney') THEN 'MONEY'
ELSE b.name
END
FROM syscolumns a
LEFT JOIN systypes b ON a.xtype = b.xusertype
WHERE b.name NOT IN ('image','text','uniqueidentifier','sql_variant','ntext','varbinary','binary','timestamp')
AND OBJECT_ID(@tbname) = a.id
ORDER BY a.colid;
-- 移除開頭逗號
IF LEN(@sql) > 0 SET @sql = SUBSTRING(@sql, 2, LEN(@sql));
IF LEN(@fdlist) > 0 SET @fdlist = SUBSTRING(@fdlist, 2, LEN(@fdlist));
-- 建立工作表 (若不存在)
SET @sql = 'CREATE TABLE [' + @tbname + '] (' + @sql + ')';
EXEC @err = sp_OAMethod @obj, 'Execute', NULL, @sql;
IF @err <> 0 GOTO ErrorHandler;
-- 匯入資料
SET @sql = 'INSERT INTO [' + @tbname + '] (' + @fdlist + ') SELECT ' + @fdlist + ' FROM [' + @tbname + ']';
EXEC @err = sp_OAMethod @obj, 'Execute', NULL, @sql;
IF @err <> 0 GOTO ErrorHandler;
-- 清理資源
EXEC sp_OADestroy @obj;
RETURN 0;
ErrorHandler:
EXEC sp_OAGetErrorInfo @obj, @src OUT, @desc OUT;
RAISERROR('匯出失敗: %s - %s', 16, 1, @src, @desc);
EXEC sp_OADestroy @obj;
RETURN -1;
END
GO-- 匯出「地區資料」表到 C:\aa.xls
EXEC p_exporttb @tbname = N'地區資料', @path = N'C:\', @fname = N'aa.xls';xp_fileexist 判斷目標檔案是否存在。syscolumns 系統表取得欄位定義，轉換為 Excel/ Jet 支援的資料類型 (TEXT, INT, DATETIME, MONEY 等)，排除不支援的類型 (image, text, ntext 等)。INSERT INTO ... SELECT 將資料從 SQL Server 表寫入 Excel 工作表。TEXT、NTEXT、IMAGE、VARBINARY、TIMESTAMP、SQL_VARIANT、UNIQUEIDENTIFIER 等將被忽略。Ole Automation Procedures 伺服器選項 (sp_configure 'Ole Automation Procedures', 1; RECONFIGURE;)。p_exporttb 提供一個零第三方工具、純 T-SQL 即可將資料表匯出為真實 Excel 檔案的方案，適合輕量級報表自動化需求。部署前請在測試環境驗證權限與驅動程式相容性。
