萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

SQL Server 與 Excel 數據互導完全指南:OPENROWSET、BCP、ADO 自動建表導出四大方案

方案一:OPENROWSET 直接查詢 Excel (最簡單,適合臨時導入導出)

導入 Excel → SQL Server


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

-- 導入並自動建表
SELECT * INTO 目標表
FROM OPENROWSET(
    'MICROSOFT.JET.OLEDB.4.0',
    'Excel 5.0;HDR=YES;DATABASE=C:\test.xls',
    'SELECT * FROM [Sheet1$]'
);

注意: HDR=YES 表示首行為列名;工作表名需加 $ 並用方括號包裹;64 位環境需安裝 Microsoft.ACE.OLEDB.12.0 驅動並啟用 Ad Hoc Distributed Queries。

導出 SQL Server → Excel (文件已存在、表頭已建)


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

方案二:BCP 命令行工具 (高性能、無驅動依賴、產出類 CSV/文本)


-- 導出表
EXEC master..xp_cmdshell 'bcp 數據庫.dbo.表名 out "C:\test.xls" /c /S"服務器" /U"用戶" -P"密碼"'

-- 導出查詢結果
EXEC master..xp_cmdshell 'bcp "SELECT col1, col2 FROM 數據庫.dbo.表名 ORDER BY col1" queryout "C:\test.xls" /c /S"服務器" /U"用戶" -P"密碼"'

缺點: 輸出實為文本文件 (制表符分隔),非真正 .xls 格式,無法直接雙擊用 Excel 正常打開複雜格式。

方案三:ADO + COM 自動化 (生成真正 Excel .xls,支持自動建表、字段類型映射)


CREATE PROCEDURE p_exporttb
    @tbname sysname,
    @path nvarchar(1000),
    @fname nvarchar(250) = ''
AS
BEGIN
    DECLARE @obj int, @hr int, @constr nvarchar(1000), @sql varchar(8000), @fdlist varchar(8000)
    IF ISNULL(@fname,'') = '' SET @fname = @tbname + '.xls'
    IF RIGHT(@path,1) <> '\' SET @path = @path + '\'

    -- 構建連接字符串 (Excel Driver)
    SET @constr = 'DRIVER={Microsoft Excel Driver (*.xls)};DSN='';READONLY=FALSE;CREATE_DB="' + @path + @fname + '";DBQ=' + @path + @fname

    -- 創建 ADODB.Connection
    EXEC @hr = sp_OACreate 'ADODB.Connection', @obj OUT
    IF @hr <> 0 GOTO LBERR
    EXEC @hr = sp_OAMethod @obj, 'Open', NULL, @constr
    IF @hr <> 0 GOTO LBERR

    -- 可選:覆蓋模式先刪表
    -- EXEC @hr = sp_OAMethod @obj, 'Execute', @out OUT, 'DROP TABLE [' + @tbname + ']'

    -- 動態構建 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

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

    EXEC @hr = sp_OAMethod @obj, 'Execute', @out OUT, @sql
    IF @hr <> 0 GOTO LBERR

    -- 寫入數據
    SET @sql = 'OPENROWSET(''MICROSOFT.JET.OLEDB.4.0'', ''Excel 5.0;HDR=YES;DATABASE=' + @path + @fname + ''', [' + @tbname + '$])'
    EXEC('INSERT INTO ' + @sql + '(' + @fdlist + ') SELECT ' + @fdlist + ' FROM ' + @tbname)

    EXEC @hr = sp_OADestroy @obj
    RETURN

LBERR:
    EXEC sp_OAGetErrorInfo 0, @src OUT, @desc OUT
    SELECT CAST(@hr AS varbinary(4)) AS 錯誤號, @src AS 錯誤源, @desc AS 錯誤描述
END

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

方案四:SQL Server 2005+ 現代替代 — SSIS / OPENROWSET(ACE) / PolyBase

  • SSIS 包:可視化 ETL,支持 Excel/CSV/平面文件、數據轉換、錯誤處理、部署調度。
  • OPENROWSET('Microsoft.ACE.OLEDB.12.0', ...) 支持 .xlsx (Office 2007+)。
  • PolyBase (2016+):外部表查詢 Parquet/ORC/CSV/Delta Lake,不直接支持 Excel 但可先轉 CSV。

避坑指南

  1. 64 位環境必裝 Access Database Engine (ACE) 64 位版,並執行:
    EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
    EXEC sp_configure 'Ad Hoc Distributed Queries', 1; RECONFIGURE;
  2. Excel 文件**不能被獨佔打開** (被 Excel 打開時導入/導出會失敗)。
  3. 大數據量優先 BCP 或 SSIS,避免 OPENROWSET 內存壓力。
  4. 字段類型映射:Excel 只有數值、文本、日期、邏輯四類,長文本映射 TEXT(255) 可能截斷。
  5. 生產環境禁用 xp_cmdshell (安全風險),改用 SQL Agent 代理作業調用 BCP/SSIS。
copyright © 萬盛學電腦網 all rights reserved