某系統上線，需要大量的數據初始化，用戶提供的而是Excel文件。 期望直接插入到SQL Server數據庫的表中，所以可以按照以下步驟使用MSSM讀取Excel表格中數據，實現Excel到SQL Server的數據批量導入

---

0. 背景說明

• 某系統上線，需要大量的數據初始化，用戶提供的而是Excel文件。
  期望直接插入到SQL Server數據庫的表中，所以可以按照以下步驟使用MSSM讀取Excel表格中數據，實現Excel到SQL Server的數據批量導入

1. 安裝Access Database Engine

• 首先安裝Access Database Engine 即需要安裝Micsoft.ACE.OLEDB安裝包

  • 微軟官方下載地址：https://www.microsoft.com/zh-cn/download/details.aspx?id=13255
  • 注意安裝的SQLServer若是64位則該引擎一定也要64位的，若是安裝32位則報錯無法使用

• 因為我本機已經安裝了Office2007（32位）

  • 在該種情形下，安裝64位的Micsoft.ACE.OLEDB則會報錯：

    裝64(32)為office Access驅動的時候無法安裝64(32)位版本的Office因為在您的PC上找到了以下32(64)位程序
    

  • 而此時我并不想卸載我的32位Office，或者服務器不允許我卸載32位的程序

  • 上述情形可以使用以下安裝包安裝對應位數的版本即可

  • 百度云鏈接: 2351144/2018rupg/未在本地計算機上注冊“microsoft.ACE.oledb.12

• 2024年7月31日10:49:11 參考T-SQL——關于安裝 Mcrosoft.ACE.oledb.16.0出現的32位和64位的沖突問題

---

2. SQL腳本

說明：Excel表格是第一行默認是讀取結果集的列名

--開啟啟用 Ad Hoc Distributed Queries 高級選項，
--在SQL Server中，該選項默認是Disable的，需要顯式啟用（Enable）；
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

--允許在進程中使用ACE.OLEDB.12
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0',
                                    N'AllowInProcess',
                                    1;
--允許動態參數
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0',
                                    N'DynamicParameters',
                                    1;


--連接Excel表格的兩種方式
--注意使用OpenDataSouce函數，后使用三個點后連接需要獲取的工作簿名稱
SELECT *
FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=E:\1.xlsx')...[Sheet1$];
--注意OPENROWSET第二個參數是Excel中的工作簿名稱
SELECT *
FROM
    OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=E:\1.xlsx;hdr=yes;imex=1', Sheet1$);

--關閉第一開啟的配置
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'Ad Hoc Distributed Queries', 0;
RECONFIGURE;
GO

---

3. 使用MSSM的導入數據功能

可以通過MSSM的圖形界面的導入數據的功能，將Excel數據導入到數據庫

數據庫->右鍵->導入數據 ，進入導入向導

選擇Micsoft Excel格式的數據源，若是報錯提示：“未在本地計算機上注冊“Microsoft.ACE.OLEDB.12.0”

則還是默認32位的問題，可以從菜單欄選擇“SQL Server2019 導入導出數據（64位）”執行導入向導，則不會在報錯

---

4. .net項目中通過Micsoft.ACE.oledb讀取Excel文件

見:.net程序讀取Excel文件