Excel を LINQ で検索する方法

投稿日: 2012/01/20 作成者: masuda

よく業務の帳票を作る時は、Excel の隠しシートを使って「印刷画面」と「データ画面」を分けて作ります。
直接帳票をデータベースから書き込んでもいいのですが、

  • セルの名前付けの不整合などがややこしい。
  • 行列を指定するときなんか、かなり大変。

ということがあって、別にデータ用のシートを用意しておいて、セル参照させるんですよね。ただ、このパターンって、行数が増えるようなレポートの場合はうまくいかなくて、結局のところコードのほう（C#/VB）で、がりがりと行列を作り込んだりします。

さて、本来はデータの書き込みを紹介したいところなのですが、OleDb プロバイダって entity data model に対応していないじゃん、ということでちょっとげんなり…どうしたものかと思っていたところ、結構簡単に linq 実装が出来そうなソースを見つけました。

Using Linq with Excel sheets
http://geekswithblogs.net/CodeSpeaker/archive/2009/10/04/using-linq-with-excel-sheets.aspx

肝は、LinqToExcelProvider クラスのところで、難ということはない、従来の OLEDB 接続をしてから、LINQ で使えるようなリスト（EnumerableRowCollection<>）を返しているだけなんですね。なるほど、これで十分です。

DataTableExtensions.AsEnumerable メソッド (System.Data)
http://msdn.microsoft.com/query/dev10.query?appId=Dev10IDEF1&l=JA-JP&k=k(SYSTEM.DATA.DATATABLEEXTENSIONS.ASENUMERABLE);k(TargetFrameworkMoniker-%22.NETFRAMEWORK%2cVERSION%3dV4.0%22);k(DevLang-CSHARP)&rd=true

DataSet/DataTable の AsEnumerable メソッドは、拡張メソッドという訳で、ver3.5 の時に追加されたものです。

練習がてら、ちょっとだけソースコードを書き換えたのが以下のコードです。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
public partial class Form1 : Form
{
    public Form1()
    {
        InitializeComponent();
    }
 
    /// <summary>
    /// 内部クラス
    /// </summary>
    public class Book
    {
        public string ISBN { get; set; }
        public string Title { get; set; }
        public int Price { get; set; }
    }
 
    private void button1_Click(object sender, EventArgs e)
    {
        LinqToExcelProvider provider = new LinqToExcelProvider(@"app_data\sampleData.xlsx");
        var items = from t in provider.GetWorkSheet("book")
                    where t["title"].ToString().IndexOf("ひと目") >= 0
                    select new Book
                    {
                        ISBN = t["isbn"].ToString(),
                        Title = t["title"].ToString(),
                        Price = int.Parse(t["price"].ToString())
                    };
        foreach (var it in items)
        {
            Debug.Print("{0} {1}", it.ISBN, it.Title);
        }
        // バインドできるように List に変換
        dataGridView1.DataSource = items.ToList();
 
    }
}
 
/// <summary>
/// Provides linq querying functionality towards Excel (xls) files
/// </summary>
public class LinqToExcelProvider
{
    /// <summary>
    /// Gets or sets the Excel filename
    /// </summary>
    private string FileName { get; set; }
 
    /// <summary>
    /// Template connectionstring for Excel connections
    /// </summary>
    // private const string ConnectionStringTemplate = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0;";
    /// Excel 2007 Connection String Samples - ConnectionStrings.com
    /// http://www.connectionstrings.com/excel-2007
    private const string ConnectionStringTemplate = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES\";";
 
    /// <summary>
    /// Default constructor
    /// </summary>
    /// <param name="fileName">The Excel file to process</param>
    public LinqToExcelProvider(string fileName)
    {
        FileName = fileName;
    }
 
    /// <summary>
    /// Returns a worksheet as a linq-queryable enumeration
    /// </summary>
    /// <param name="sheetName">The name of the worksheet</param>
    /// <returns>An enumerable collection of the worksheet</returns>
    public IQueryable<DataRow> GetWorkSheet(string sheetName)
    {
        // Build the connectionstring
        string connectionString = string.Format(ConnectionStringTemplate, FileName);
 
        // Query the specified worksheet
        OleDbDataAdapter da = new OleDbDataAdapter(string.Format("SELECT * FROM [{0}$]", sheetName), connectionString);
 
        // Fill the dataset from the data adapter
        DataTable dt = new DataTable();
        da.Fill(dt);
 
        // Return the data table contents as a queryable enumeration
 
        return dt.AsEnumerable().AsQueryable();
    }
}

のような Excel を用意しておいて、シート名は「book」にします。

実行すると、こんな感じ

ちなみに「Microsoft.ACE.OLEDB」の「ACE」の部分は「Microsoft Access データベース エンジン」の略とのこと、以下のところでコンポーネントがダウンロードできます（って、visual studio 2010 が入っていないと入らない？）

ダウンロード詳細 Microsoft Access データベース エンジン 2010 再頒布可能コンポーネント
http://www.microsoft.com/downloads/ja-jp/details.aspx?FamilyID=c06b8369-60dd-4b64-a44b-84b371ede16d

カテゴリー: C#, データベース パーマリンク