基于LINQ的Excel數據查詢方法研究及應用

亓雪冬,張亮

(中國石油大學(華東)信息化建設處，山東 青島 266580)

0 引言

Excel是Microsoft微軟公司開發的電子表格軟件，是Office軟件集合中的重要組成部分。Excel將數據組織在多行多列的電子表格中，通過快速編輯和格式化數據實現對數據的高效管理，滿足辦公場景的需要。除此以外，通過篩選、排序、公式、函數等功能，Excel也能夠實現對數據的簡單處理。對于更復雜的數據處理功能，可采用Office內置的腳本語言VBA(Visual Basic For Applications)進行擴展。然而VBA是一種簡單的、非面向對象的程序語言，缺乏C#等現代程序設計語言完善的面向對象以及委托、泛型、LINQ、異步等特征，也缺乏.Net豐富類庫的支持，編程能力受到限制。本研究通過融合C#語言和Excel，研究了使用ADO.NET讀寫Excel數據，使用LINQ(Language Integrated Query語言集成查詢)進行復雜數據處理的方法。

1 LINQ語言集成查詢

數據源、ADO.NET、LINQ和C#之間的關系如圖1所示[1-2]。

圖1 數據源、ADO.NET、LINQ和C#之間的關系

數據源為數據的存儲倉庫，可有不同的表現形式，如內存對象(列表、集合等)、關系型數據庫、XML文檔等；ADO.NET是.Net平臺訪問外部數據的基本類庫，主要用于訪問關系型數據庫；LINQ作為C#語言的內置功能，對于不同的數據源提供了統一、一致的查詢語法，如通過LINQ to DataSet可對外部關系型數據庫進行查詢。

LINQ被稱為語言集成查詢，是將查詢能力直接集成入C#語言的一套技術。和傳統的查詢語言SQL相比，LINQ更具有優勢[3]。

(1)傳統的查詢語句表示為一個字符串序列，不支持編譯時類型和語法檢查以及編寫時的智能感知，編寫和調試效率較低；而LINQ將查詢表達式和C#語言深度集成，查詢表達式中的變量具有明確的數據類型，支持編譯檢查和編寫智能感知，編程效率更高；

(2)傳統的方式下，不同的數據源(數據庫、XML文檔、內存對象等)需要不同形式的查詢語句，編寫時需要學習不同的語法；而LINQ作為第一類語言特征，與類、方法和事件類似，提供了標準的關鍵字和運算符，針對不同的數據源提供了一致的查詢語法。

LINQ查詢語句與標準SQL語句非常相似，基本形式如圖2所示[4]。

圖2 LINQ查詢基本形式

LINQ語句中的from,join,on,where,orderby等關鍵字的含義與標準SQL查詢對應的關鍵字類似，from表示從哪個集合中查詢數據;join表示兩個集合的內連接;on表示內連接的連接條件，where用于對查詢結果進行篩選;orderby用于對結果進行排序。LINQ中的select關鍵字與標準SQL語句差別較大。SQL中select處于句首，而LINQ中處于句尾，此外LINQ中Select可結合C#的new關鍵字，生成查詢結果特定的元組類型。

此外，LINQ還支持分組查詢、分層查詢以及交、并、差等集合操作，極大提高了查詢的靈活性，與Excel結合能夠增強其復雜數據處理的能力。

2 結合LINQ與Excel查詢處理數據的思路

LINQ設計為可對IEnumerable〈T〉泛型類型[5]的數據進行查詢，IEnumerable〈T〉表示任意可被枚舉的數據，這為實現統一的查詢操作提供了基礎。因此對Excel數據進行LINQ查詢處理，其核心思路是如何將Excel數據轉換為IEnumerable〈T〉類型的數據。Excel數據進行LINQ查詢處理的流程如圖3所示。

圖3 Excel數據進行LINQ查詢的流程

(1)首先將Excel文件中的數據讀取到DataTable中。在ADO.NET技術中，內部數據表的類型為DataTable，用以存儲和映射外部數據庫中的數據。

(2)DataTable本身并不是IEnumerable〈T〉類型，通過調用DataTable對象AsEnumerable方法，可將其轉換為IEnumerable〈DataRow〉類型。這里DataRow表示元組(數據行)，IEnumerable〈DataRow〉表示可被枚舉的元組集合。

(3)根據需求對IEnumerable〈DataRow〉類型的數據進行LINQ查詢處理，返回的查詢結果仍為IEnumerable〈DataRow〉類型。

(4)將查詢結果寫入Excel文件，處理結束。

3 LINQ處理Excel數據關鍵技術

在使用LINQ處理Excel數據的前期和后期，需要解決幾個相關技術問題，例如如何連接Excel工作簿和工作表、如何將Excel工作表中的數據提取到DataTable、如何將查詢結果寫回到Excel工作表中等。下面針對這些問題具體闡述實現方法。

3.1 連接Excel數據源

為了通過ADO.NET讀寫Excel文件，需要提前安裝Microsoft ACE OLEDB數據引擎，該引擎提供了對Access(*.mdb和*.accdb)以及Excel(*.xls和*.xlsx)等數據文件的讀寫支持。C#程序中對EXCEL文件建立連接的代碼如下。

OleDbConnection conn =new OleDbConnection();

Data Source={FileName};

Extended Properties='Excel 12.0 Xml;HDR=YES' ";

conn.Open();

這里，第1句和第3句分別表示建立數據源連接conn和打開連接。第2句設置了連接字符串屬性ConnectionString，用于描述外部數據源的訪問方式，其中Provider表示采用Microsoft ACE OLEDB數據引擎，Data Source通過FileName變量指定了Excel文件的絕對路徑，Extended Properties表示文件為Excel文件，HDR=YES表示數據表的第一行為標題行。

3.2 Excel工作表導入到DataTable

連接外部Excel文件后，即可讀寫Excel文件中的數據。Excel工作簿可看作簡單的文件型關系數據庫，工作簿中的多張工作表可看作數據庫中的數據表。ADO.NET中數據表的類型是DataTable，為了進行LINQ查詢，需要將Excel工作表中的數據導入到DataTable中，代碼如下。

DataTable dt =new DataTable();

OleDbDataAdapter da =new OleDbDataAdapter("Select語句",conn);

da.Fill(dt);

第1句建立DataTable對象；第2句建立數據適配器對象，從連接conn中根據Select語句提取Excel表中的數據；第3句將提取到的數據填入DataTable對象。

如需要從工作表Sheet1中取得數據，則Select語句可寫為：

如需要從Sheet1的B1:D10區域取得數據，則Select語句可寫為：

這里，數據表或數據區域兩邊需添加中括號定界符，此外，表名后的表示這個工作表已存在于Excel工作簿中。

3.3 查詢結果導出到Excel工作表

使用LINQ對DataTable中的數據完成查詢操作后，需將查詢結果導出保存回Excel工作表中，相關代碼如下：

OleDbCommand cmd =new OleDbCommand();

cmd.Connection =conn;

foreach (DataRow dr in IEnumerable〈DataRow〉){

cmd.ExecuteNonQuery();

}

前兩句建立并設置命令對象，用以執行SQL語句。查詢結果的類型為IEnumerable〈DataRow〉，表示可枚舉的元組集合。Foreach循環用以提取集合中的每一個元組，構造并執行Insert語句，將數據插入到名為Sheet3的Excel工作表中。

4 應用實例

筆者所在高校的校園卡管理部負責校園卡綜合業務和資金結算，與學校其他部門業務往來中經常涉及數據交換，為了保障校園卡系統的安全性，校園卡系統和其他業務部門并沒有直接聯通，數據交換一般采用Excel文件格式。例如，校園卡系統和財務系統中均存儲了學生入學時的學號、姓名和銀行卡號。如果學生后期更換了銀行卡號，會在財務系統中進行更改，每個學期末財務系統將最新的學生信息導出到Excel文件中，再交給校園卡管理部進行數據更新。

處理前，兩個部門的數據存儲在data.xlsx文件中，校園卡系統導出的原始學生信息存放在sheet1工作表中，財務系統導出新的學生信息存放在sheet2工作表中，兩個表均包含3列數據：學號、姓名和銀行卡號。現需要將兩個工作表中銀行卡不一致的記錄取出，寫入sheet3工作表，處理過程如圖4所示。

圖4 查詢銀行卡號有變動的記錄

這些數據處理工作直接使用Excel操作實現將會比較繁瑣，由于LINQ具備描述復雜查詢的能力，實現較為簡便。按照前文方法連接data.xlsx數據源，并將Sheet1和Sheet2兩張工作表導入DataTable類型變量dt1和dt2。

接下來，可使用LINQ對dt1、dt2進行雙表聯合查詢，語句如下。

IEnumerable〈DataRow〉 dt3 =

(from r1 in dt1.AsEnumerable()

join r2 in dt2.AsEnumerable()

on r1("學號")equals r2("學號")

where r1("銀行卡號")!=r2("銀行卡號")

select new {

學號 =r1("學號"),

姓名 =r1("姓名"),

新銀行卡號 =r2("銀行卡號"),

原銀行卡號 =r1("銀行卡號")

});

這里，dt1和dt2兩張表通過學號完成內連接，并通過where子句篩選出銀行卡號不一致的學生數據，查詢結果包含4個字段：學號、姓名、新銀行卡號和原銀行卡號。獲取查詢結果后，可按前文方法將查詢結果寫入Sheet3工作表，繼而導入校園卡系統實現數據更新。

5 總結

LINQ語言集成查詢，作為C#語言內部支持的第一類語言特征，提供了查詢相關的標準關鍵字和運算符，將查詢能力直接集成入C#語言。與傳統查詢語言如SQL相比，LINQ具備更多優勢，它支持編譯時類型檢查、語法檢查和編寫智能感知等特征，編程效率更高。LINQ與Excel相結合，增強了Excel進行數據處理的能力，擴展了Excel數據處理的思路和方法，提高了復雜數據的處理效率。