使用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.csdn.net/net_lover/archive/2008/01/23/2060425.aspx）中，

NorthWindDataContext NorthWind  =   new  NorthWindDataContext();

是得到数据源

var query  =  from c  in  NorthWind.Customers  where  c.Country  ==   " USA "  select  new  { c.Country, c.CompanyName };

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

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

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

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

var query2  =  from c  in  NorthWind.Customers  where  c.Country  ==   " USA "  select  new  { c.Country, c.CompanyName };
var query3  =  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即可）： 

<? xml version="1.0" ?>
<? mso-application progid="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" > Visual C# </ 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" > Web Services </ Data ></ Cell >
    </ Row >
    < Row >
     < Cell  ss:Index ="3" >< Data  ss:Type ="Number" > 12 </ Data ></ Cell >
     < Cell >< Data  ss:Type ="String" > .NET FAQs </ 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：

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

<! DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd" >
< html  xmlns ="http://www.w3.org/1999/xhtml" >
< head  runat ="server" >
     < title > LINQ to Excel </ 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, EventArgs e)
    {
         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 （Office 2003），操作可能会出现问题。 " );
            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();

        var query  =  from art  in  articlelist
                    join cat  in  categorylist on art.CategoryID equals cat.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 > ();
        Category c;

        m_objRange  =  m_objSheet.get_Range( " C2 " ,  " D8 " );
         for  ( int  i  =   1 ; i  <  m_objRange.Rows.Count; i ++ )
        {
            c  =   new  Category();
            Range r  =  (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 > ();
        Article a;
        m_objRange  =  m_objSheet.get_Range( " C16 " ,  " E21 " );
         for  ( int  i  =   1 ; i  <  m_objRange.Rows.Count; i ++ )
        {
            a  =   new  Article();
            Range r  =  (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  Application m_objExcel  =   null ;
     private  Workbooks m_objBooks  =   null ;
     private  _Workbook m_objBook  =   null ;
     private  Sheets m_objSheets  =   null ;
     private  _Worksheet m_objSheet  =   null ;
     private  Range m_objRange  =   null ;
     private   object  m_objOpt  =  System.Reflection.Missing.Value;

}
public   class  Category
{
     public  Int32 CategoryID;
     public   string  CategoryName;
}

public   class  Article
{
     public  Int32 CategoryID;
     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>
///  Summary description for LinqExcelProvider
///   </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  ExcelProvider Create( string  filePath,  string  sheet)
    {
        ExcelProvider provider  =   new  ExcelProvider();
        provider.sheet  =  sheet;
        provider.filePath  =  filePath;
         return  provider;
    }

     private   void  Load()
    {
         string  connectionString  =   @" Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;"" " ;
        connectionString  =   string .Format(connectionString, filePath);
        rows.Clear();
         using  (OleDbConnection conn  =   new  OleDbConnection(connectionString))
        {
            conn.Open();
             using  (OleDbCommand cmd  =  conn.CreateCommand())
            {
                cmd.CommandText  =   " select * from [ "   +  sheet  +   " $] " ;
                 using  (OleDbDataReader reader  =  cmd.ExecuteReader())
                {
                     while  (reader.Read())
                    {
                        ExcelRow newRow  =   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.IEnumerator System.Collections.IEnumerable.GetEnumerator()
    {
        Load();
         return  rows.GetEnumerator();
    }

}

使用方法：

ExcelProvider provider  =  ExcelProvider.Create(Server.MapPath( " ~/App_Data/Book2.xls " ),  " Sheet1 " );
foreach  (ExcelRow row  in  (from x  in  provider select x))
{
    Response.Write( " <li> "   +  row.GetString( 0 )  +   "   "   +  row.GetString( 1 ));
}

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