基于LINQ的Excel數(shù)據(jù)查詢方法研究及應(yīng)用

亓雪冬,張亮

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

0 引言

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

1 LINQ語言集成查詢

數(shù)據(jù)源、ADO.NET、LINQ和C#之間的關(guān)系如圖1所示[1-2]。

圖1 數(shù)據(jù)源、ADO.NET、LINQ和C#之間的關(guān)系

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

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

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

(2)傳統(tǒng)的方式下，不同的數(shù)據(jù)源(數(shù)據(jù)庫、XML文檔、內(nèi)存對象等)需要不同形式的查詢語句，編寫時需要學(xué)習(xí)不同的語法；而LINQ作為第一類語言特征，與類、方法和事件類似，提供了標(biāo)準(zhǔn)的關(guān)鍵字和運算符，針對不同的數(shù)據(jù)源提供了一致的查詢語法。

LINQ查詢語句與標(biāo)準(zhǔn)SQL語句非常相似，基本形式如圖2所示[4]。

圖2 LINQ查詢基本形式

LINQ語句中的from,join,on,where,orderby等關(guān)鍵字的含義與標(biāo)準(zhǔn)SQL查詢對應(yīng)的關(guān)鍵字類似，from表示從哪個集合中查詢數(shù)據(jù);join表示兩個集合的內(nèi)連接;on表示內(nèi)連接的連接條件，where用于對查詢結(jié)果進(jìn)行篩選;orderby用于對結(jié)果進(jìn)行排序。LINQ中的select關(guān)鍵字與標(biāo)準(zhǔn)SQL語句差別較大。SQL中select處于句首，而LINQ中處于句尾，此外LINQ中Select可結(jié)合C#的new關(guān)鍵字，生成查詢結(jié)果特定的元組類型。

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

2 結(jié)合LINQ與Excel查詢處理數(shù)據(jù)的思路

LINQ設(shè)計為可對IEnumerable〈T〉泛型類型[5]的數(shù)據(jù)進(jìn)行查詢，IEnumerable〈T〉表示任意可被枚舉的數(shù)據(jù)，這為實現(xiàn)統(tǒng)一的查詢操作提供了基礎(chǔ)。因此對Excel數(shù)據(jù)進(jìn)行LINQ查詢處理，其核心思路是如何將Excel數(shù)據(jù)轉(zhuǎn)換為IEnumerable〈T〉類型的數(shù)據(jù)。Excel數(shù)據(jù)進(jìn)行LINQ查詢處理的流程如圖3所示。

圖3 Excel數(shù)據(jù)進(jìn)行LINQ查詢的流程

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

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

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

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

3 LINQ處理Excel數(shù)據(jù)關(guān)鍵技術(shù)

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

3.1 連接Excel數(shù)據(jù)源

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

OleDbConnection conn =new OleDbConnection();

Data Source={FileName};

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

conn.Open();

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

3.2 Excel工作表導(dǎo)入到DataTable

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

DataTable dt =new DataTable();

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

da.Fill(dt);

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

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

如需要從Sheet1的B1:D10區(qū)域取得數(shù)據(jù)，則Select語句可寫為：

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

3.3 查詢結(jié)果導(dǎo)出到Excel工作表

使用LINQ對DataTable中的數(shù)據(jù)完成查詢操作后，需將查詢結(jié)果導(dǎo)出保存回Excel工作表中，相關(guān)代碼如下：

OleDbCommand cmd =new OleDbCommand();

cmd.Connection =conn;

foreach (DataRow dr in IEnumerable〈DataRow〉){

cmd.ExecuteNonQuery();

}

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

4 應(yīng)用實例

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

處理前，兩個部門的數(shù)據(jù)存儲在data.xlsx文件中，校園卡系統(tǒng)導(dǎo)出的原始學(xué)生信息存放在sheet1工作表中，財務(wù)系統(tǒng)導(dǎo)出新的學(xué)生信息存放在sheet2工作表中，兩個表均包含3列數(shù)據(jù)：學(xué)號、姓名和銀行卡號?，F(xiàn)需要將兩個工作表中銀行卡不一致的記錄取出，寫入sheet3工作表，處理過程如圖4所示。

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

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

接下來，可使用LINQ對dt1、dt2進(jìn)行雙表聯(lián)合查詢，語句如下。

IEnumerable〈DataRow〉 dt3 =

(from r1 in dt1.AsEnumerable()

join r2 in dt2.AsEnumerable()

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

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

select new {

學(xué)號 =r1("學(xué)號"),

姓名 =r1("姓名"),

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

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

});

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

5 總結(jié)

LINQ語言集成查詢，作為C#語言內(nèi)部支持的第一類語言特征，提供了查詢相關(guān)的標(biāo)準(zhǔn)關(guān)鍵字和運算符，將查詢能力直接集成入C#語言。與傳統(tǒng)查詢語言如SQL相比，LINQ具備更多優(yōu)勢，它支持編譯時類型檢查、語法檢查和編寫智能感知等特征，編程效率更高。LINQ與Excel相結(jié)合，增強了Excel進(jìn)行數(shù)據(jù)處理的能力，擴展了Excel數(shù)據(jù)處理的思路和方法，提高了復(fù)雜數(shù)據(jù)的處理效率。