LINQ（2）：使用LINQ查询Excel文件

LINQ是Visual Studio 2008及其以后版本中提供的功能，将强大的查询扩展到C#和VB.NET语言语法之中。LINQ提供了标准、简单的模式来查询和更新数据，同时还允许程 序开发人员进行扩展，支持任何类型的数据存储。Visual Studio 2008包含了LINQ提供者程序集，能够对.NET Framework集合，内存中的对象数组，SQL Server数据库，ADO.NET Dataset和XML文档进行语言集成的查询。另外，我们也可以自己编写LINQ提供者，提供对任何类型数据的查询，只要实现了IEnumerable 或者IEnumerable<T> (C#)或 IEnumerable (Of T) (Visual Basic)的数据类型，都可以进行使用LINQ进行操作。我们可以使用完全相同的语法查询SQL数据库、XML文档、ADO.NET的Dataset （DataTable）、内存中的集合对象，以及任何支持LINQ的远程或者本地数据源。在LINQ查询中，只与对象打交道，因此，可以完全不需要知道数 据存储的数据源，而且采用的编程模型和语法也完全相同。

所有的LINQ查询基本上都是由3个基本的操作组成：得到数据源，创建查询和执行查询3个过程。数据的获得是在执行查询的时候完成的。在上一部分的例子（http://blog.youkuaiyun.com/net_lover/archive/2008/01/23/2060425.aspx）中，

NorthWindDataContextNorthWind = new NorthWindDataContext();

是得到数据源

varquery = fromc in NorthWind.Customers where c.Country == " USA " select new {c.Country,c.CompanyName};

是创建查询，此时并没有对数据执行任何操作，

GridView2.DataSource = query;
GridView2.DataBind();

在数据绑定时，才真正得到数据。

但是，有的查询需要立即执行，将结果放在内存中，可以调用查询或者查询变量的ToList<(Of <TSource>)> 或者 ToArray<(Of <TSource>)>方法。例如：

varquery2 = fromc in NorthWind.Customers where c.Country == " USA " select new {c.Country,c.CompanyName};
varquery3 = query2.ToList();

好，既然说LINQ可以查询任何类型的数据，下面，咱们就试试如何查询一个Excel文件（说明：本程序在Excel 2003下今天测试的，在Excel2007中可能更简单，不过没有测试。运行本程序可能需要采用模拟或者调整Excel.exe的权限，另外注意：使用之前需要引用COM：Microsoft Office 11.0 Object Library
如果引用列表中没有，需要自行添加 C:/Program Files/Microsoft Office/OFFICE11/EXCEL.EXE）。
1，先准备一个Book1.xls，文件内容如下（将下面的XML文件在Excel里打开，另存为Book1.xls即可）：

<? xmlversion="1.0" ?>
<? mso-applicationprogid="Excel.Sheet" ?>
< Workbook xmlns ="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o ="urn:schemas-microsoft-com:office:office"
xmlns:x ="urn:schemas-microsoft-com:office:excel"
xmlns:ss ="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html ="http://www.w3.org/TR/REC-html40" >
< DocumentProperties xmlns ="urn:schemas-microsoft-com:office:office" >
< Author > zhy </ Author >
< LastAuthor > zhy </ LastAuthor >
< Created > 2008-01-25T13:13:34Z </ Created >
< LastSaved > 2008-01-26T13:10:56Z </ LastSaved >
< Version > 11.9999 </ Version >
</ DocumentProperties >
< ExcelWorkbook xmlns ="urn:schemas-microsoft-com:office:excel" >
< WindowHeight > 13050 </ WindowHeight >
< WindowWidth > 16020 </ WindowWidth >
< WindowTopX > 0 </ WindowTopX >
< WindowTopY > 105 </ WindowTopY >
< ProtectStructure > False </ ProtectStructure >
< ProtectWindows > False </ ProtectWindows >
</ ExcelWorkbook >
< Styles >
< Style ss:ID ="Default" ss:Name ="Normal" >
< Alignment ss:Vertical ="Center" />
< Borders />
< Font ss:FontName ="宋体" x:CharSet ="134" ss:Size ="12" />
< Interior />
< NumberFormat />
< Protection />
</ Style >
</ Styles >
< Worksheet ss:Name ="Sheet1" >
< Table ss:ExpandedColumnCount ="5" ss:ExpandedRowCount ="21" x:FullColumns ="1"
x:FullRows ="1" ss:DefaultColumnWidth ="54" ss:DefaultRowHeight ="14.25" >
< Column ss:Index ="3" ss:AutoFitWidth ="0" ss:Width ="102" />
< Column ss:AutoFitWidth ="0" ss:Width ="263.25" />
< Column ss:AutoFitWidth ="0" ss:Width ="246.75" />
< Row ss:Index ="2" >
< Cell ss:Index ="2" >< Data ss:Type ="String" > Category </ Data ></ Cell >
< Cell >< Data ss:Type ="String" > CategoryID </ Data ></ Cell >
< Cell >< Data ss:Type ="String" > CategoryName </ Data ></ Cell >
</ Row >
< Row >
< Cell ss:Index ="3" >< Data ss:Type ="Number" > 1 </ Data ></ Cell >
< Cell >< Data ss:Type ="String" > ASP.NET </ Data ></ Cell >
</ Row >
< Row >
< Cell ss:Index ="3" >< Data ss:Type ="Number" > 8 </ Data ></ Cell >
< Cell >< Data ss:Type ="String" > VB.NET </ Data ></ Cell >
</ Row >
< Row >
< Cell ss:Index ="3" >< Data ss:Type ="Number" > 9 </ Data ></ Cell >
< Cell >< Data ss:Type ="String" > VisualC# </ Data ></ Cell >
</ Row >
< Row >
< Cell ss:Index ="3" >< Data ss:Type ="Number" > 11 </ Data ></ Cell >
< Cell >< Data ss:Type ="String" > XML/XSL </ Data ></ Cell >
</ Row >
< Row >
< Cell ss:Index ="3" >< Data ss:Type ="Number" > 6 </ Data ></ Cell >
< Cell >< Data ss:Type ="String" > WebServices </ Data ></ Cell >
</ Row >
< Row >
< Cell ss:Index ="3" >< Data ss:Type ="Number" > 12 </ Data ></ Cell >
< Cell >< Data ss:Type ="String" > .NETFAQs </ Data ></ Cell >
</ Row >
< Row ss:Index ="16" >
< Cell ss:Index ="2" >< Data ss:Type ="String" > Article </ Data ></ Cell >
< Cell >< Data ss:Type ="String" > CategoryID </ Data ></ Cell >
< Cell >< Data ss:Type ="String" > Title </ Data ></ Cell >
< Cell >< Data ss:Type ="String" > Guid </ Data ></ Cell >
</ Row >
< Row >
< Cell ss:Index ="3" >< Data ss:Type ="Number" > 1 </ Data ></ Cell >
< Cell >< Data ss:Type ="String" > ASP.NET2.0中将文件上传到数据库 </ Data ></ Cell >
< Cell >< Data ss:Type ="String" > 17612afb-3fc0-4fb9-bfa9-00cba28336e9 </ Data ></ Cell >
</ Row >
< Row >
< Cell ss:Index ="3" >< Data ss:Type ="Number" > 1 </ Data ></ Cell >
< Cell >< Data ss:Type ="String" > 不经保存，直接读取上传文件的内容 </ Data ></ Cell >
< Cell >< Data ss:Type ="String" > 78280914-a75c-40dc-9dac-322b3d81be35 </ Data ></ Cell >
</ Row >
< Row >
< Cell ss:Index ="3" >< Data ss:Type ="Number" > 1 </ Data ></ Cell >
< Cell >< Data ss:Type ="String" > 一次编辑GridView的所有行 </ Data ></ Cell >
< Cell >< Data ss:Type ="String" > a933b187-06c3-4263-9eec-414a54d9c815 </ Data ></ Cell >
</ Row >
< Row >
< Cell ss:Index ="3" >< Data ss:Type ="Number" > 11 </ Data ></ Cell >
< Cell >< Data ss:Type ="String" > 创建、查询、修改带名称空间的XML文件的例子 </ Data ></ Cell >
< Cell >< Data ss:Type ="String" > 7b4c7a42-4cdf-40d1-b293-e86da109a34c </ Data ></ Cell >
</ Row >
< Row >
< Cell ss:Index ="3" >< Data ss:Type ="Number" > 11 </ Data ></ Cell >
< Cell >< Data ss:Type ="String" > 用XSL把XML的数据转换成完美的多列表格形式 </ Data ></ Cell >
< Cell >< Data ss:Type ="String" > yawo3qgm-xd53-4d3d-oybr-blsbx5bngaym </ Data ></ Cell >
</ Row >
</ Table >
< WorksheetOptions xmlns ="urn:schemas-microsoft-com:office:excel" >
< Print >
< ValidPrinterInfo />
< PaperSizeIndex > 9 </ PaperSizeIndex >
< HorizontalResolution > 200 </ HorizontalResolution >
< VerticalResolution > 200 </ VerticalResolution >
</ Print >
< Selected />
< Panes >
< Pane >
< Number > 3 </ Number >
< ActiveRow > 6 </ ActiveRow >
< ActiveCol > 2 </ ActiveCol >
</ Pane >
</ Panes >
< ProtectObjects > False </ ProtectObjects >
< ProtectScenarios > False </ ProtectScenarios >
</ WorksheetOptions >
</ Worksheet >
< Worksheet ss:Name ="Sheet2" >
< Table ss:ExpandedColumnCount ="0" ss:ExpandedRowCount ="0" x:FullColumns ="1"
x:FullRows ="1" ss:DefaultColumnWidth ="54" ss:DefaultRowHeight ="14.25" />
< WorksheetOptions xmlns ="urn:schemas-microsoft-com:office:excel" >
< ProtectObjects > False </ ProtectObjects >
< ProtectScenarios > False </ ProtectScenarios >
</ WorksheetOptions >
</ Worksheet >
< Worksheet ss:Name ="Sheet3" >
< Table ss:ExpandedColumnCount ="0" ss:ExpandedRowCount ="0" x:FullColumns ="1"
x:FullRows ="1" ss:DefaultColumnWidth ="54" ss:DefaultRowHeight ="14.25" />
< WorksheetOptions xmlns ="urn:schemas-microsoft-com:office:excel" >
< ProtectObjects > False </ ProtectObjects >
< ProtectScenarios > False </ ProtectScenarios >
</ WorksheetOptions >
</ Worksheet >
</ Workbook >

2，创建aspx：

<% @PageLanguage = " C# " AutoEventWireup = " true " CodeFile = " LinqExcel.aspx.cs " Debug = " true "
Inherits = " LinqExcel " %>

<! DOCTYPEhtmlPUBLIC"-//W3C//DTDXHTML1.0Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd" >
< html xmlns ="http://www.w3.org/1999/xhtml" >
< head runat ="server" >
< title > LINQtoExcel </ title >
</ head >
< body >
< form id ="form1" runat ="server" >
< div >
< asp:GridView ID ="GridView1" runat ="server" AutoGenerateColumns ="false" CellPadding ="4" >
< Columns >
< asp:HyperLinkField DataNavigateUrlFields ="CategoryID" Target ="_blank"
DataNavigateUrlFormatString ="http://dotnet.aspx.cc/ShowList.aspx?id={0}"
HeaderText ="栏目" DataTextField ="CategoryName" DataTextFormatString ="【{0}】" />
< asp:HyperLinkField DataNavigateUrlFields ="ArticleGuid" Target ="_blank"
DataNavigateUrlFormatString ="http://dotnet.aspx.cc/article/{0}/read.aspx"
HeaderText ="文章标题" DataTextField ="ArticleTitle" />
</ Columns >
</ asp:GridView >
</ div >
</ form >
</ body >
</ html >

3，编写代码文件：

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Collections.Generic;
using Microsoft.Office.Interop.Excel;

public partial class LinqExcel:System.Web.UI.Page
{
protected void Page_Load( object sender,EventArgse)
{
string f = Server.MapPath( " ~/App_Data/Book1.xls " );
Open(f);

}
public void Open( string FilePath)
{
m_objExcel = new Application();
m_objExcel.Visible = false ;
m_objExcel.DisplayAlerts = false ;

if (m_objExcel.Version != " 11.0 " )
{
Response.Write( " 您的Excel版本不是11.0（Office2003），操作可能会出现问题。 " );
m_objExcel.Quit();
return ;
}

m_objBooks = (Workbooks)m_objExcel.Workbooks;
m_objBook = m_objBooks.Open(FilePath,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt);
m_objSheets = (Sheets)m_objBook.Worksheets;
m_objSheet = (_Worksheet)(m_objSheets.get_Item( 1 ));
List < Category > categorylist = LoadCategory();
List < Article > articlelist = LoadArticle();

varquery = fromart in articlelist
joincat in categorylistonart.CategoryIDequalscat.CategoryID
select new {art.ArticleTitle,art.CategoryID,art.ArticleGuid,cat.CategoryName};


GridView1.DataSource = query;
GridView1.DataBind();

this .Close();
this .Dispose2();
}


private void Close()
{
m_objBook.Close( false ,m_objOpt,m_objOpt);
m_objExcel.Quit();

}

public void Dispose2()
{
ReleaseObj(m_objSheets);
ReleaseObj(m_objBook);
ReleaseObj(m_objBooks);
ReleaseObj(m_objExcel);
System.GC.Collect();
System.GC.WaitForPendingFinalizers();

}
private void ReleaseObj( object o)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(o);
}
catch {}
finally {o = null ;}
}


private List < Category > LoadCategory()
{
List < Category > lc = new List < Category > ();
Categoryc;

m_objRange = m_objSheet.get_Range( " C2 " , " D8 " );
for ( int i = 1 ;i < m_objRange.Rows.Count;i ++ )
{
c = new Category();
Ranger = (Range)m_objRange.Cells[i + 1 , 1 ];
c.CategoryID = Convert.ToInt32(r.Value2);

r = (Range)m_objRange.Cells[i + 1 , 2 ];
c.CategoryName = Convert.ToString(r.Value2);
lc.Add(c);

}
return lc;

}

private List < Article > LoadArticle()
{
List < Article > al = new List < Article > ();
Articlea;
m_objRange = m_objSheet.get_Range( " C16 " , " E21 " );
for ( int i = 1 ;i < m_objRange.Rows.Count;i ++ )
{
a = new Article();
Ranger = (Range)m_objRange.Cells[i + 1 , 1 ];
a.CategoryID = Convert.ToInt32(r.Value2);

r = (Range)m_objRange.Cells[i + 1 , 2 ];
a.ArticleTitle = Convert.ToString(r.Value2);

r = (Range)m_objRange.Cells[i + 1 , 3 ];
a.ArticleGuid = Convert.ToString(r.Value2);
al.Add(a);

}
return al;
}
private Applicationm_objExcel = null ;
private Workbooksm_objBooks = null ;
private _Workbookm_objBook = null ;
private Sheetsm_objSheets = null ;
private _Worksheetm_objSheet = null ;
private Rangem_objRange = null ;
private object m_objOpt = System.Reflection.Missing.Value;

}
public class Category
{
public Int32CategoryID;
public string CategoryName;
}

public class Article
{
public Int32CategoryID;
public string ArticleTitle;
public string ArticleGuid;
}

上面的代码将Excel中的两个区域分别放到了两个对象里面，利用对象进行LINQ查询。当然，这里的例子只是说明技术，在实际的应用中需要进行进一步的封装，也可能换别的方法。

4，运行结果：

由于LINQ可以自己定义提供者，因此，我们可以定义自己的数据源提供者。下面是网上写的一个LINQ to Excel Provider （作者是：http://solidcoding.blogspot.com/2007/12/linq-to-excel-provider.html）。其代码和使用方法如下：

LinqExcelProvider.cs：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;

/// <summary>
/// SummarydescriptionforLinqExcelProvider
/// </summary>

public class ExcelRow
{
List < object > columns;

public ExcelRow()
{
columns = new List < object > ();
}

internal void AddColumn( object value)
{
columns.Add(value);
}

public object this [ int index]
{
get { return columns[index];}
}

public string GetString( int index)
{
if (columns[index] is DBNull)
{
return null ;
}
return columns[index].ToString();
}

public int Count
{
get { return this .columns.Count;}
}
}

public class ExcelProvider:IEnumerable < ExcelRow >
{
private string sheet;
private string filePath;
private List < ExcelRow > rows;


public ExcelProvider()
{
rows = new List < ExcelRow > ();
}

public static ExcelProviderCreate( string filePath, string sheet)
{
ExcelProviderprovider = new ExcelProvider();
provider.sheet = sheet;
provider.filePath = filePath;
return provider;
}

private void Load()
{
string connectionString = @" Provider=Microsoft.Jet.OLEDB.4.0;DataSource={0};ExtendedProperties=""Excel8.0;HDR=YES;"" " ;
connectionString = string .Format(connectionString,filePath);
rows.Clear();
using (OleDbConnectionconn = new OleDbConnection(connectionString))
{
conn.Open();
using (OleDbCommandcmd = conn.CreateCommand())
{
cmd.CommandText = " select*from[ " + sheet + " $] " ;
using (OleDbDataReaderreader = cmd.ExecuteReader())
{
while (reader.Read())
{
ExcelRownewRow = new ExcelRow();
for ( int count = 0 ;count < reader.FieldCount;count ++ )
{
newRow.AddColumn(reader[count]);
}
rows.Add(newRow);
}
}
}
}
}

public IEnumerator < ExcelRow > GetEnumerator()
{
Load();
return rows.GetEnumerator();
}

System.Collections.IEnumeratorSystem.Collections.IEnumerable.GetEnumerator()
{
Load();
return rows.GetEnumerator();
}

}

使用方法：

ExcelProviderprovider = ExcelProvider.Create(Server.MapPath( " ~/App_Data/Book2.xls " ), " Sheet1 " );
foreach (ExcelRowrow in (fromx in providerselectx))
{
Response.Write( " <li> " + row.GetString( 0 ) + " " + row.GetString( 1 ));
}

这样，查询数据就很方便了。