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 の時に追加されたものです。

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

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#, データベース パーマリンク