[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