基于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数据处理的思路和方法，提高了复杂数据的处理效率。