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

亓雪冬,張亮

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

0 引言

Excel是Microsoft微軟公司開發(fā)的電子表格軟件，是Office軟件集合中的重要組成部分。Excel將數(shù)據(jù)組織在多行多列的電子表格中，通過快速編輯和格式化數(shù)據(jù)實(shí)現(xiàn)對(duì)數(shù)據(jù)的高效管理，滿足辦公場(chǎng)景的需要。除此以外，通過篩選、排序、公式、函數(shù)等功能，Excel也能夠?qū)崿F(xiàn)對(duì)數(shù)據(jù)的簡(jiǎn)單處理。對(duì)于更復(fù)雜的數(shù)據(jù)處理功能，可采用Office內(nèi)置的腳本語言VBA(Visual Basic For Applications)進(jìn)行擴(kuò)展。然而VBA是一種簡(jiǎn)單的、非面向?qū)ο蟮某绦蛘Z言，缺乏C#等現(xiàn)代程序設(shè)計(jì)語言完善的面向?qū)ο笠约拔?、泛型、LINQ、異步等特征，也缺乏.Net豐富類庫(kù)的支持，編程能力受到限制。本研究通過融合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ù)的存儲(chǔ)倉(cāng)庫(kù)，可有不同的表現(xiàn)形式，如內(nèi)存對(duì)象(列表、集合等)、關(guān)系型數(shù)據(jù)庫(kù)、XML文檔等；ADO.NET是.Net平臺(tái)訪問外部數(shù)據(jù)的基本類庫(kù)，主要用于訪問關(guān)系型數(shù)據(jù)庫(kù)；LINQ作為C#語言的內(nèi)置功能，對(duì)于不同的數(shù)據(jù)源提供了統(tǒng)一、一致的查詢語法，如通過LINQ to DataSet可對(duì)外部關(guān)系型數(shù)據(jù)庫(kù)進(jìn)行查詢。

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

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

(2)傳統(tǒng)的方式下，不同的數(shù)據(jù)源(數(shù)據(jù)庫(kù)、XML文檔、內(nèi)存對(duì)象等)需要不同形式的查詢語句，編寫時(shí)需要學(xué)習(xí)不同的語法；而LINQ作為第一類語言特征，與類、方法和事件類似，提供了標(biāo)準(zhǔn)的關(guān)鍵字和運(yùn)算符，針對(duì)不同的數(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查詢對(duì)應(yīng)的關(guān)鍵字類似，from表示從哪個(gè)集合中查詢數(shù)據(jù);join表示兩個(gè)集合的內(nèi)連接;on表示內(nèi)連接的連接條件，where用于對(duì)查詢結(jié)果進(jìn)行篩選;orderby用于對(duì)結(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é)合能夠增強(qiáng)其復(fù)雜數(shù)據(jù)處理的能力。

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

LINQ設(shè)計(jì)為可對(duì)IEnumerable〈T〉泛型類型[5]的數(shù)據(jù)進(jìn)行查詢，IEnumerable〈T〉表示任意可被枚舉的數(shù)據(jù)，這為實(shí)現(xiàn)統(tǒng)一的查詢操作提供了基礎(chǔ)。因此對(duì)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，用以存儲(chǔ)和映射外部數(shù)據(jù)庫(kù)中的數(shù)據(jù)。

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

(3)根據(jù)需求對(duì)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ù)的前期和后期，需要解決幾個(gè)相關(guān)技術(shù)問題，例如如何連接Excel工作簿和工作表、如何將Excel工作表中的數(shù)據(jù)提取到DataTable、如何將查詢結(jié)果寫回到Excel工作表中等。下面針對(duì)這些問題具體闡述實(shí)現(xiàn)方法。

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

為了通過ADO.NET讀寫Excel文件，需要提前安裝Microsoft ACE OLEDB數(shù)據(jù)引擎，該引擎提供了對(duì)Access(*.mdb和*.accdb)以及Excel(*.xls和*.xlsx)等數(shù)據(jù)文件的讀寫支持。C#程序中對(duì)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文件的絕對(duì)路徑，Extended Properties表示文件為Excel文件，HDR=YES表示數(shù)據(jù)表的第一行為標(biāo)題行。

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

連接外部Excel文件后，即可讀寫Excel文件中的數(shù)據(jù)。Excel工作簿可看作簡(jiǎn)單的文件型關(guān)系數(shù)據(jù)庫(kù)，工作簿中的多張工作表可看作數(shù)據(jù)庫(kù)中的數(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對(duì)象；第2句建立數(shù)據(jù)適配器對(duì)象，從連接conn中根據(jù)Select語句提取Excel表中的數(shù)據(jù)；第3句將提取到的數(shù)據(jù)填入DataTable對(duì)象。

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

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

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

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

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

4 應(yīng)用實(shí)例

筆者所在高校的校園卡管理部負(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)和財(cái)務(wù)系統(tǒng)中均存儲(chǔ)了學(xué)生入學(xué)時(shí)的學(xué)號(hào)、姓名和銀行卡號(hào)。如果學(xué)生后期更換了銀行卡號(hào)，會(huì)在財(cái)務(wù)系統(tǒng)中進(jìn)行更改，每個(gè)學(xué)期末財(cái)務(wù)系統(tǒng)將最新的學(xué)生信息導(dǎo)出到Excel文件中，再交給校園卡管理部進(jìn)行數(shù)據(jù)更新。

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

圖4 查詢銀行卡號(hào)有變動(dòng)的記錄

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

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

IEnumerable〈DataRow〉 dt3 =

(from r1 in dt1.AsEnumerable()

join r2 in dt2.AsEnumerable()

on r1("學(xué)號(hào)")equals r2("學(xué)號(hào)")

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

select new {

學(xué)號(hào) =r1("學(xué)號(hào)"),

姓名 =r1("姓名"),

新銀行卡號(hào) =r2("銀行卡號(hào)"),

原銀行卡號(hào) =r1("銀行卡號(hào)")

});

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

5 總結(jié)

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