[C#] 利用 OleDB 來存取 Excel

學習在 C# 上透過 OleDB 存取 Excel

Sample Excel Data

Sample Code

// 必須加入該命名空間
using System.Data.OleDb;

namespace XLSByOleDB
{
    class Program
    {
        static void Main(string[] args)
        {
            string FileFullName = @"D:\XlsDemo.xlsx";

            string ExcelConnectionString = GetExcelConnectionString(FileFullName);

            DataTable sheetsInfo = GetExcelSheetNames(ExcelConnectionString);

            string sheetName;
            foreach (DataRow sheet in sheetsInfo.Rows)
            {
                sheetName = sheet["Table_Name"].ToString();
                // OR sheetName = sheet[2].ToString();
                Console.WriteLine($"SheetName：{sheetName}　相關資料");

                DataTable data = GetExcelData(ExcelConnectionString, sheetName);
                foreach (DataRow row in data.Rows)
                {
                    // 欄位1 為該 Sheet 第一筆資料，也就是欄位名稱
                    Console.WriteLine(row["欄位1"].ToString());
                }
            }
        }

        /// <summary>
        /// 產生 Excel 檔案連線字串
        /// </summary>
        /// <param name="FileFullName">Excel 檔案完整路徑</param>
        /// <returns>Excel 檔案連線字串</returns>
        private static string GetExcelConnectionString(string FileFullName)
        {
            OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder
            {
                Provider = "Microsoft.ACE.OLEDB.12.0",
                DataSource = FileFullName
            };

            string Extension = Path.GetExtension(FileFullName);
            string ExcelVersion;
            string XML;
            if (Extension == ".xlsx")
            {
                ExcelVersion = "12.0";
                XML = nameof(XML);
            }
            else
            {
                ExcelVersion = "8.0";
                XML = string.Empty;
            }
            builder["Extended Properties"] = $"Excel {ExcelVersion} {XML};HDR=YES;IMEX=1";

            return builder.ConnectionString;
        }

        /// <summary>
        /// 抓取 Excel 內全部 Sheet 
        /// </summary>
        /// <param name="connectionString">Excel 檔案連線字串</param>
        /// <returns>Excel 內全部 Sheet</returns>
        private static DataTable GetExcelSheetNames(string connectionString)
        {
            using (OleDbConnection conn = new OleDbConnection(connectionString))
            {
                conn.Open();
                DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                // OR
                // DataTable dt = conn.GetSchema("Tables");
                return dt;
            }
        }

        /// <summary>
        /// 取得指定 Sheet 內資料
        /// </summary>
        /// <param name="connectionString">Excel 檔案連線字串</param>
        /// <param name="sheetName">指定 Sheet 名稱</param>
        /// <returns>指定 Sheet 內資料</returns>
        private static DataTable GetExcelData(string connectionString, string sheetName)
        {
            using (OleDbConnection conn = new OleDbConnection(connectionString))
            {
                conn.Open();
                // Sheet名稱，必需用中括號 [] 包起來
                string TSQL = $"SELECT * FROM [{sheetName}]";
                OleDbDataAdapter da = new OleDbDataAdapter(TSQL, connectionString);
                DataTable dt = new DataTable();
                da.Fill(dt);
                return dt;
            }
        }

    }
}

結果

重點紀錄

• 在 Microsoft Access Database Engine 2010 可轉散發套件 和 Microsoft Access Database Engine 2016 Redistributable 都有下列說明

• 在 Excel Connection String 內提到，可以利用 [Microsoft.ACE.OLEDB.12.0] 來存取 [97 - 2003] 格式

• 下面兩篇列為重點精華文章，主要是說明 Extended Properties 內的 IMEX 參數、TypeGuessRows 和 ImportMixedTypes 兩個機碼的重要性

1. 透過 OleDb 精準讀入 Excel 檔的方法
2. Excel 有資料，但匯入到資料庫後卻是 NULL；設定登錄機碼 TypeGuessRows、連線字串 IMEX

• 參考資料
• C#讀取Excel的三種方式以及比較

[SQL] 資料表提示-ReadPast

討論 NOLOCK 常常會一併討論 READPAST，相較於 NOLOCK 會產生 Dirty Read 問題，READPAST 沒有 Dirty Read 困惱，但 READPAST 會略過被封鎖資料

官方文件說明如下

指定 Database Engine 不讀取其他交易已鎖定的資料列。 當指定 READPAST 時，系統會略過資料列層級鎖定，但不會略過頁面層級的鎖定。 亦即，Database Engine 會略過資料列，而不會封鎖目前的交易，直到釋放鎖定為止。 例如，假設資料表 T1 包含了值為 1、2、3、4、5 的單一整數資料行。 如果交易 A 將 3 的值變更為 8，但是尚未認可，則 SELECT * FROM T1 (READPAST) 會產生 1、2、4、5 的值。 READPAST 主要是在實作使用 SQL Server 資料表的工作佇列時用來減少鎖定競爭。 使用 READPAST 的佇列讀取器會略過其他交易已鎖定的佇列項目，直接到下一個可用的佇列項目，不需要等待其他交易釋放鎖定。

依上述範例來驗證

USE AdventureWorks
GO

DROP TABLE IF EXISTS tbReadPastDemo

CREATE TABLE tbReadPastDemo
(
  ID int identity(1,1) Primary key,
  Col1 int
)
 
INSERT INTO tbReadPastDemo (Col1) 
VALUES(1) , (2) , (3) , (4) , (5)
 
-- 顯示建立資料
SELECT * FROM tbReadPastDemo

----- ReadPast Demo
----- Session A
-- Step1：開啟交易並更新 ID = 3 資料為 8，此時交易還在
begin transaction
UPDATE tbReadPastDemo SET Col1 = 8 WHERE ID = 3

-- Step3：結束交易
ROLLBACK

----- Session B
-- Step2：利用 ReadPast 查詢 ID = 2 資料，此時 Session1 還在交易內
SELECT * FROM tbReadPastDemo WITH (ReadPast)

從 Session2 可以發現，只有 4 筆資料，ID = 3 的資料被忽略了

UPDATE、DELETE 官方文件說明

UPDATE 或 DELETE 陳述式所參考的任何資料表，以及 FROM 子句所參考的任何資料表，都可以指定 READPAST。 當在 UPDATE 陳述式中指定 READPAST 時，只有在讀取資料來識別要更新的記錄時，才會套用 READPAST，不論是在陳述式的哪個位置指定，都是如此。 INSERT 陳述式 INTO 子句中的資料表不能指定 READPAST。 當讀取外部索引鍵或索引檢視表時，或當修改次要索引時，使用 READPAST 的更新或刪除作業可能會進行封鎖。

參考資料
• 資料表提示
• SQL with(nolock)與with(readpast)

[SQL] DDL 封鎖 NOLOCK

延續 [SQL] 資料表提示-NOLOCK 該篇筆記，原以為使用 NOLOCK 就不會被鎖定，在 資料表提示 上看見這段文字

READUNCOMMITTED 和 NOLOCK 提示只適用於資料鎖定。 所有的查詢 (包括具有 READUNCOMMITTED 和 NOLOCK 提示的查詢)，都會在編譯和執行期間取得 Sch-S (結構描述穩定性) 鎖定。 因此，當並行交易在資料表上保有 Sch-M (結構描述修改) 鎖定時，查詢將會遭到封鎖。 例如，資料定義語言 (DDL) 作業會在修改資料表的結構描述資訊之前先取得 Sch-M 鎖定。 任何並行查詢，包括以 READUNCOMMITTED 或 NOLOCK 提示執行的查詢，會在嘗試取得 Sch-S 鎖定時遭到封鎖。 相反地，保有 Sch-S 鎖定的查詢將會封鎖嘗試取得 Sch-M 鎖定的並行交易。

原來操作 DDL 時，即使有使用 NOLOCK 也會被鎖定

Sample Code

USE AdventureWorks
GO 

DROP TABLE IF EXISTS tbDDLBlockDemo

CREATE TABLE tbDDLBlockDemo
(
  ID int identity(1,1) Primary key,
  Col1 int
)

----- DDL Block NOLOCK Demo
----- Session1
-- Step1：開啟交易並更改 Col1 資料型態，此時交易還在
begin transaction 
ALTER TABLE tbDDLBlockDemo ALTER COLUMN Col1 bigint

-- Step4：把交易 commit 後，Session2 就會馬上返回結果
COMMIT

----- Session2
-- Step2：利用 NOLOCK 查詢資料，此時 Session1 還在交易內
SELECT * FROM tbDDLBlockDemo WITH (NOLOCK)

----- Session3：Step3：利用 DMV 來觀察 Lock 情況
set transaction isolation level read uncommitted
SELECT *
FROM sys.dm_tran_locks 
WHERE resource_database_id IN (SELECT database_id FROM sys.databases WHERE [Name] = 'AdventureWorks')
ORDER BY request_session_id

觀察下圖右方 Session2 可以發現，查詢被 Block 住啦，故意讓它跑久一些

利用 DMV 可以觀察到 Session1 有 Sch-M 存在

[SQL] 資料表提示-NOLOCK

利用資料表提示-NOLOCK 來進行資料查詢是實務上常見方式，但該方式會造成 Dirty Read，驗證一下該情況

NOLOCK 相當於 READ UNCOMMITTED 隔離層級，Dirty Read 官方文件解釋如下

指定陳述式可以讀取其他交易已修改，但尚未認可的資料列。

在 READ UNCOMMITTED 層級執行的交易不會發出共用鎖定來防止其他交易修改目前交易所讀取的資料。 防止目前交易讀取其他交易已修改而尚未認可之資料列的獨佔鎖定，也不會封鎖 READ UNCOMMITTED 交易。 當設定這個選項時，可能會讀取到尚未認可的修改項目，這稱為中途讀取 (Dirty Read)。 在交易結束之前，資料中的值可以變更，資料列也可以在資料集中出現或消失。 這個選項的效果，與在交易中將所有 SELECT 陳述式之所有資料表設為 NOLOCK 相同。 這是隔離等級中限制最少的一種。

Sample Code

USE AdventureWorks
GO 

DROP TABLE IF EXISTS tbNoLockDemo

CREATE TABLE tbNoLockDemo
(
  ID int identity(1,1) Primary key,
  Col1 int
)

INSERT INTO tbNoLockDemo (Col1) 
VALUES(1) , (2) , (3) , (4) , (5)

-- 顯示建立資料
SELECT * FROM tbNoLockDemo 

----- NOLCOK Demo
----- Session1
-- Step1：開啟交易並更新 ID = 2 資料，此時交易還在
begin transaction 
UPDATE tbNoLockDemo SET Col1 = 999 WHERE ID = 2

-- Step3：把交易 Rollback 模擬交易失敗並查詢 ID = 2 資料 
ROLLBACK
SELECT * FROM tbNoLockDemo WITH (NOLOCK) WHERE ID = 2

----- Session2
-- Step2：利用 NOLOCK 查詢 ID = 2 資料，此時 Session1 還在交易內
SELECT * FROM tbNoLockDemo WITH (NOLOCK) WHERE ID = 2

依 Script 操作就可以產生下述 Dirty Read 結果，Session1 資料為 2，但 Session2 資料為 999

UPDATE、DELETE 官方文件說明

無法針對插入、更新或刪除作業修改的資料表指定 READUNCOMMITTED 和 NOLOCK。 SQL Server 查詢最佳化工具會忽略套用在 UPDATE 或 DELETE 陳述式目標資料表的 FROM 子句中的 READUNCOMMITTED 和 NOLOCK 提示。

計算欄位官方文件說明

如果資料表包含計算資料行，且計算資料行是由存取其他資料表之資料行的運算式或函數來計算，資料表提示就不會在這些資料表上使用，也不會傳播。 例如，在查詢中指定資料表的 NOLOCK 資料表提示。 這份資料表擁有多個計算資料行，這些計算資料行會利用存取另一資料表中之資料行的運算式和函數的組合來進行計算。 當存取運算式和函數所參考的資料表時，它們不會使用 NOLOCK 資料表提示。

參考資料
• 資料表提示
• SET TRANSACTION ISOLATION LEVEL
• 論壇討論
• Using NOLOCK? Here’s How You’ll Get the Wrong Query Results.

[VFP] Report Form - NoConsole 參數

開發一份直接輸出的標籤報表時，使用者反應，畫面除了動畫部分，其他區域都變成標籤內容

後來發現輸出參數忘記加上 NoConsole 參數，才會造成該現象

REPORT FORM ReportName.frx TO PRINTER NOCONSOLE

Help 內說明的參數說明

在列印報表或將它傳送給一個檔案時，不在 Visual FoxPro 桌面視窗或使用者自定視窗中顯示它的內容。

注意：
當您使用 Visual FoxPro 9.0 的物件輔助的輸出模式時，REPORT FORM 命令不會在目前輸出視窗中顯示您的報表內容，因此 NOCONSOLE 關鍵字和 OFF 關鍵字對原始的行為沒有影響。不過，該關鍵字可用於報表偵聽器的 CommandClauses 物件中。您可以在您的報表偵聽器衍生類別中評估它們，並選擇是要取消一個報表內容的顯示或是其他基於這些內容的使用者回應。相關詳細資訊請參閱「CommandClauses 屬性」。

[VFP] GoFish - 完整檔案路徑

在 GoFish 內使用關鍵字來搜尋時，結果常常跨不同的 Project，因此需要知道該檔案所在完整路徑

在畫面紅框之處有完整檔案路徑

跟同事分享後，他也在 Option 內找到相關設定，也是另外一種可以知道檔案完整路徑的方式

參考資料
• GoFish GitHub

[VFP] Error accessing printer spooler

VFP 在指定列印標籤機時，拋出下圖錯誤訊息 - 離線列印時發生錯誤

把離線列印翻成英文去 Google，找到的都是確認印表機、標籤機狀態的討論，確定標籤機當下是正常，不應該需要確認狀態，後來在測試環境中指定標籤機，總算是拋出正確錯誤訊息，馬上就找到答案，Orz

之前指定標籤機是下列寫法，利用 laPrinters(li, 1) 來進行設定

APRINTERS(laPrinters)
IF VARTYPE(laPrinters) <> "U"
  FOR li = 1 TO ALEN(laPrinters, 1)
    IF ATC(TargetPrinter , laPrinters(li, 1)) <> 0  
      SET PRINTER TO NAME laPrinters(li, 1)
    ENDIF
  ENDFOR
ENDIF

因為有些因素，所以改寫為下述寫法，先把印表機名稱塞進一個變數，再利用該變數來指定印表機，然後就是一上線就掛的悲劇，QQ

APRINTERS(laPrinters)
IF VARTYPE(laPrinters) <> "U"
  FOR li = 1 TO ALEN(laPrinters, 1)

    lcPrintName = laPrinters(li, 1)
  
    IF ATC(TargetPrinter , lcPrintName) <> 0  
      SET PRINTER TO NAME lcPrintName
    ENDIF
  ENDFOR
ENDIF

必須利用括號 [name expression ()] 包住變數，才能正常指定

SET PRINTER TO NAME (lcPrintName)

延伸閱讀
• [VFP] 載入印表機驅動程式時發生錯誤

參考資料
• 如何列印到 Visual FoxPro 中指定的印表機使用印表機設定為名稱指令

[SQL] 無聯結述詞

修改一隻報表 AP 時，看一下該 TSQL 語法執行計畫，一打開就發現 Operator Warming - 無聯結述詞

該語法沒有效能問題，7 個 Table Logical Read 都沒有超過 10，且都用 Seek 抓出 1 筆資料運算而已，在測試環境上可以重現，就研究並筆記一下，整理網路文章提到重點

• 統計資訊過期：確認統計資訊是正常的，但保險起見，這 7 個 Table 相關 Index，即使執行計畫裡沒有用上的，通通都 rebuild，WA 開頭統計資訊通通刪除，沒有幫助
• 資料內容重複性過高：因為是廠內勾稽用判斷條件，基本上是不會有該情況
• 返回過多不必要資料：只有回傳一筆資料，排除該情況

搞了一整天後，想說就不要理會，因為也沒轍，QQ

繼續改報表 AP 時發現，原來 TSQL 內有 2 個 Table 並不是報表所需內容，單純是順道 JOIN 一併抓出來，在 AP 內要做防呆判斷用，其中 1 個 Table 是產生 [無聯結述詞 operator] 關鍵，移除這 2 個 Table 並把防呆判斷另外寫，結案

就商業邏輯上來說，這 7 個 Table JOIN 在一起，在實務情況上是合理的，完全不知道產生原因，>.<

參考資料
• [SQL Server]No Join Predicate
• SQL執行計畫Join運算子顯示 [無聯結述詞]。 Sql Server No Join Predicate Warning
  

[SQL] Job Owner 權限

在 Client 端測試 Job 時，執行失敗，錯誤訊息如下

確認是 Owner 沒有適當權限造成，且該 Login 是在更名 Windows 電腦名稱前使用，把 Owner 換成其他 Login 就 OK

• 在 SSMS 內變更 Owner

• 利用 dbo.sp_manage_jobs_by_login 變更該 Owner 所屬的 Job

USE msdb ;  
GO  

EXEC dbo.sp_manage_jobs_by_login  
    @action = N'REASSIGN',  
    @current_owner_login_name = N'CurrentOwner',  
    @new_owner_login_name = N'NewOwner' ;  
GO  

延伸閱讀
• [SQL] 錯誤的伺服器名稱

參考資料
• 論壇討論
• sp_manage_jobs_by_login (Transact-SQL)

[SQL] 執行 SQL Agent 權限

論壇問題

要開放 SQL Server Agent 給使用者管理

要執行 SQL Server Agent 要具備下列 msdb 資料庫固定資料庫角色，分別為

• SQLAgentUserRole
• SQLAgentReaderRole
• SQLAgentOperatorRole

權限細節參考 - SQL Server Agent 固定資料庫角色，以下為簡易操作練習

建立 SQL 登入-AgentDemo

把 AgentDemo 加入 msdn SQLAgentUserRole 內

利用 SSMS 登錄時就可以看見 SQL Agent 啦

SysAdamin 和 SQLAgentUserRole 的 Agent 權限對比圖

假如要把現有 Job 移轉給使用者管理，只要變更 Job 擁有者就行

參考資料
• Security7：管理SQL Server Agent的权限
• 設定使用者以建立與管理 SQL Server Agent 作業
• 實作 SQL Server Agent 安全性
• 查詢「作業(Job)」的執行記錄：使用「記錄檔檢視器」、預存程序 sp_help_jobhistory
• [SQL SERVER] 如何授權給USER使用AGENT JOB

[SQL] 報表功能

上課時老師提到 SSMS 內有四個地方有報表可以使用，腦海裡是只有 Instance 和 DB 內有，紀錄一下

• Instance 層級

• DB 層級

• 登入

• 資料收集

[SQL] 通用資料連結 (UDL) 設定

參與課程時，老師提到不在 Client 端安裝 SSMS 的話，要如何測試 SQL Server 連線，當時反應是利用 ODBC 就可以做到，沒想到 Windows 檔案內還有 udl 可以使用來進行連線測試

設定 ODBC 來對 SQL Server 進行連線設定，設定細節就不贅述

ODBC 連線測試成功訊息

Windows 內有 udl 檔案可以進行 SQL Server 連線測試，先新增一個 txt 檔案，再把副檔名更名為 udl 就可以

開啟 udl 就會出現熟悉的連線設定畫面

思考兩者使用情境發現，我使用 ODBC 方式，在於是公司內部 PC 且擁有 Windows 權限，本身就是 admin，萬一像老師去到企業支援或沒有 Windows 權限可以開啟並設定 ODBC 的話，udl 會是較好的選擇

參考資料
• 通用資料連結 (UDL) 設定
• [SQL]透過 .udl 來測試連接資料庫
• [SQL] 使用 Universal Data Link (.udl) 快速測試資料庫連線

[VFP] Command Windows 字型大小

最近有需求，常使用 Command Windows 來查看 scx 和 frx，就會想要調整字型大小，一直在 Tool => Option 內搜尋和調整字型大小，一無所獲

後來發現要調整 Command Windows 字型只要在按滑鼠右鍵，就可以找到 Property 選項來進行修改，Orz

參考資料
• 論壇討論

[VFP] C0000094 錯誤訊息

使用者回報發生 C0000094 錯誤訊息，Google 發現，極可能是因為字型造成，經過確認後發現，程式有使用到 Arial Narrow 字型，但電腦內沒有該字型，Form 和 frx 內改用其他字型或是把字型加進 Windows 內就解決該問題

在 VFP Command 內輸入語法，直接修正 Form 或 Frx 內的使用字型

USE FormFullPath/FrxFullPath IN 0 SHARED

// Form 針對 Properties 欄位
UPDATE FormFullPath SET Properties = STRTRAN(Properties , "Arial Narrow" , "Arial") WHERE ATC("Arial Narrow",Properties) > 0

// Frx 針對 Fontface 欄位
UPDATE FrxFullPath SET Fontface = "Arial" WHERE ALLTRIM(Fontface) == "Arial Narrow"

原以為是 Win10 1903 裡面預設沒有 Arial Narrow 字型，查 Arial Narrow font family 發現，原來這字型是安裝 Office 2007 才會有，從 Win7、Win8、Win10 的字型清單內都沒有發現 Arial Narrow 的存在

• Win10 內的 Arial 相關字型，還有一個 Arial Nova 系列就沒有特地截圖

• Arial Narrow 字型說明，只到 Office 2007 而已

查完資料就覺得好玩，該 PC 雖然有安裝 Office 2007，但也沒有 Arial Narrow 字型，確認其他 PC 內，沒有 Arial Narrrow 也不會拋出錯誤訊息，目前指向一個問題點，該台 PC 是 1903，^^''

參考資料
• C0000094 Error

[VS] 變更 Tangible T4 Editor 背景顏色

在 VS 2017 上學習 EF edmx 時有注意到會安裝 Tangible T4 Editor 來使用，安裝後打開 edmx 才發現不妙，因為 VS 是使用黑色背景，所以一整個眼花撩亂，Orz

安裝 Tangible T4 Editor 後未調整前畫面

VS 內調整 Tangible T4 Editor 的背景顏色

自定背景顏色為 [紅 60]、[綠 60]、[藍 60]

調整後的 Tangible T4 Editor 畫面

其實看見未調整前畫面時，第一反應是移除 Tangible T4 Editor，沒想到移除後，再打開 VS 2017 就出現下面的警告訊息 - [未正確載入套件 'EditorPackage']，打開 EF edmx 檔案，完全看不到 edmx 內容，只好再把 Tangible T4 Editor 安裝回來

參考資料
• CHANGING T4 TEMPLATE BACKGROUND COLORS IN VISUAL STUDIO

[SQL] 複合索引效能 - 2

Turning 時發現，某 Table 只有不到 4,000 筆資料，但卻出現異常的 statistics 資訊 (scan count 859，logical read 3554) 且執行計畫上有驚嘆號提示，畫面如下

確認後發現是複合索引欄位設定錯誤導致，原本是 [客戶編號 + 物料編號]，修正為 [物料編號 + 客戶編號]，狀況就解除啦

統計資訊改善前後

	改善前	改善後
Scan Count	859	0
Logical Read	3,554	1,780

延伸閱讀
• [SQL] 複合索引效能

[VS] 建構子快捷鍵

紀錄 VS 上建構子快捷鍵使用

輸入 ctor 並按兩次 tab 鍵，可以產生無參數建構子

結果

把 Parent class 修正為有參數建構子並在 Child class 繼承時，要利用 VS 快捷功能產生對應建構子

使用 [快速動作與重構] 功能

就可以在 Child class 上產生對應 Parant class 的參數建構子