ExcelのファイルをC#で読み込もうとしたら、少しググればCOM相互運用を使って使う方法が出てきます。でもこの方法、Excelを実際に起動させて自分のプログラムとやり取りするので画面がとても鬱陶しいことになりますし、おそらくExcelが無いと使えません。
ですが、このLINQ to Excel、若干癖があって、というよりか、使い方が一部直観的じゃなくてリファレンス無しにいろいろ使えるわけでもないんですよね（使い方がわかればとても便利だと思います）。一方、COM相互運用側からExcelを使う方法もあるため、特に日本語ではLINQ to Excelの記事はあまり見かけません（例によって自分のググり力が足りないだけかもしれませんが）。
とりあえず、適当なデータを用意しないとサンプルを示すに示せないので、こんなエクセルシートを用意してみました。にゃんぱすーฅ(๑'Δ'๑)
ファイルはxlsでもxlsxでもcsvでも読めるっぽいですが、どうもExcel2013で作ったxlsxは読み込めないっぽいです。Excel2010なら読み込めました。また、読み込もうとしているファイルをExcelで開いていたりすると途中で例外を吐いてしまいますので注意してください。
任意のセルを読み出す
LINQ to Excelは任意のセルを読み出すのにははっきり言って全くもって適していません。でも、まあ一応読み出せるので読み出し方を紹介します。var excel = new ExcelQueryFactory(@"test.xls");
excel.ReadOnly = true;
Console.WriteLine(excel.WorksheetRangeNoHeader("B3", "B3", "Sheet1").First().First().Value.ToString());
とりあえず呪文のように思ってください。この記事を最後まで読むと意味がわかると思います。読み出すデータはSheet1のB3です。これを実行するとしっかりと
2002/05/28 0:00:00と表示されます。
正攻法的に読み出す
さて、このLINQ to Excelは、基本的に上のエクセルシートのように1行目がヘッダーで、2行目以降がデータになっているデータを読み出すことに特化し、様々な機能が作られているようです。そのため、WorksheetはRowクラスの列挙可能型として定義されており、RowはList<Cell>を継承したものとして定義されています。というわけで、正攻法的に読みだしてみます。
var excel = new ExcelQueryFactory(@"test.xls");
excel.ReadOnly = true;
var worksheet = excel.Worksheet("Sheet1");
var q = from p in worksheet
select new {
Name = p["名前"].Cast<string>(),
Birthday = p["誕生日"].Cast<DateTime>(),
Height = p["身長"].Cast<int>(),
BloodType = p["血液型"].Cast<string>(),
};
foreach(var p in q)
Console.WriteLine("{0}, {1}, {2}cm, {3}", p.Name, p.Birthday, p.Height, p.BloodType);
Sheet1のワークシートを読み込んでからLINQのクエリ文で匿名クラスを定義し、各行をデータ化しています。ワークシートは行の列挙可能型で、行からはインデクサーのオーバーロードでヘッダー名でその列に相当するセルを読み出しています。セルはobject型のValueプロパティを持っていますのでこれを適当にキャストして読み出してもいいのですが、例えばdoube型がobjectにキャストされているときはいきなりintにキャストできませんし、例えば越谷卓は血液型が空欄ですので、そのデータ型はDBNullになっているのでstringにキャストはできません。そういう関係で、Cast<T>()型のほうが使い勝手が良いですね。
データをヘッダーと同名のプロパティを持つクラスに読み込む
さて、続いてこのようなクラスを作ってみます。public class Person
{
public string 名前 { get; set; }
public DateTime 誕生日 { get; set; }
public int 身長 { get; set; }
public string 血液型 { get; set; }
}
ヘッダーと同じ名前のプロパティを持ったクラスですね。違和感はとてもありますが、C#はUnicodeなので日本語プロパティも作ることができます。
var excel = new ExcelQueryFactory(@"test.xls");
excel.ReadOnly = true;
var worksheet = excel.Worksheet<Person>("Sheet1");
foreach(var p in worksheet)
Console.WriteLine("{0}, {1}, {2}cm, {3}", p.名前, p.誕生日, p.身長, p.血液型);
こうすることで、直接ワークシートをPersonの列挙可能型として読み込めるようになりました。超便利です。Cast<T>()も必要ないので、非常にすっきりしました。
データをヘッダーと同名のプロパティを持たないクラスに読み込む
しかし、常にヘッダーと同名のプロパティを持つクラスを用意できるかと言ったらそうとは限りません。ヘッダー名は実行するまでわからないかもしれませんし、そもそも上記のような日本語プロパティは回避したいときなどもあるでしょう。大丈夫です、そのようなときも、極力簡単に特定のクラスにデータを読み込むことができます。
まずは同様にPersonクラスを作ってみます。
public class Person
{
public string Name { get; set; }
public DateTime Birthday { get; set; }
public int Height { get; set; }
public string BloodType { get; set; }
}
さて、当然のことながら、ヘッダーとプロパティ名が異なってしまったので、それを対応付ける作業をしなければいけません。それを「マッピング」と呼んでいるようです。
var excel = new ExcelQueryFactory(@"test.xls");
excel.ReadOnly = true;
excel.AddMapping<Person>(p => p.Name, "名前");
excel.AddMapping<Person>(p => p.Birthday, "誕生日");
excel.AddMapping<Person>(p => p.Height, "身長");
excel.AddMapping<Person>(p => p.BloodType, "血液型");
var worksheet = excel.Worksheet<Person>("Sheet1");
foreach(var p in worksheet)
Console.WriteLine("{0}, {1}, {2}cm, {3}", p.Name, p.Birthday, p.Height, p.BloodType);
このような形でマッピングを追加することでプロパティ名とヘッダー名の対応付けをすることができます。これでめでたくヘッダーと違う名前のプロパティでも適当に代入してくれるようになりました。
ちなみに、AddMapping<T>()の1つ目の引数のラムダ式が何これ？って思う人もいるかもしれませんが、これはいわゆる式木を使った技法で、こうすることでこのラムダ式を受け取った側はプロパティの名前が取得できるようになります。AddMappingのオーバーロードを見るとわかりますが、このような方法を使わずとも単にプロパティ名をstringで与えるオーバーロードもありますが、そのようなものに対して、こっちのほうがIDEのサポートを受けられる（サジェスト、リファクタリング等）ので好んで使われる技法のようです。MVVMのViewModelを作るときに通知可能プロパティで同様の技法を使ったりすることもあります。
データをヘッダーと同名のプロパティを持たないクラスに読み込む その2
上記のようにメソッドでマッピングする以外に、属性を使った方法もあります。public class Person
{
[ExcelColumn("名前")]
public string Name { get; set; }
[ExcelColumn("誕生日")]
public DateTime Birthday { get; set; }
[ExcelColumn("身長")]
public int Height { get; set; }
[ExcelColumn("血液型")]
public string BloodType { get; set; }
}
このように、Personクラスのそれぞれのプロパティに属性をつけてやります。それだけでマッピングはしなくても同様に読み込めるようになります。簡単ですね。
ヘッダーが無いデータを読み込む
中にはヘッダーと呼べる行がなく、いきなり1行目からデータになっているエクセルシートもあるでしょう。そのようなものを読み込むこともできます。本来は、サンプルコードとしてはWorksheetNoHeader()を呼び出すべきなんでしょうが、元のエクセルシートを書き換えるのもアレなので、下記のようにWorksheetRangeNoHeaderを呼び出しています。これは、指定した範囲内をヘッダーの無いデータとして認識するメソッドです。
var excel = new ExcelQueryFactory(@"test.xls");
excel.ReadOnly = true;
var worksheet = excel.WorksheetRangeNoHeader("A2", "D6", "Sheet1");
var q = from p in worksheet
select new {
Name = p[0].Cast<string>(),
Birthday = p[1].Cast<DateTime>(),
Height = p[2].Cast<int>(),
BloodType = p[3].Cast<string>(),
};
foreach(var p in q)
Console.WriteLine("{0}, {1}, {2}cm, {3}", p.Name, p.Birthday, p.Height, p.BloodType);
NoHeaderがつくメソッドを使った場合、行はRowクラスではなくRowNoHeaderクラスになります。これは、インデクサーにstringを受け取るオーバーロードがなく、セルのインデックスをint型で与えるしかありません。
LINQする
さて、ここまでデータの読み込み方が中心でした。肝心のLINQのクエリ文はあまり書いていません。まあ、どうせみんなわかってるだろうから解説しても仕方がないっていうのもあるとは思いますが。var worksheet = excel.Worksheet<Person>("Sheet1");
var q = from p in worksheet
where p.Birthday > new DateTime(2000, 1, 1)
orderby p.Birthday descending
select p;
例えばクエリ文はこんな感じで書けますね。もう至って普通です。
前述しましたが、worksheetは行の集まりなので、pは各行になります。と言っても、Worksheet<Person>で行はPersonに変換済みですから、ここから容易にPersonの条件を設定できます。こでは、2000年以降に生まれた人を若い人順に並べていますね。あまり説明はいらないと思います。
特定のクラスに変換しない場合はpがRowになりますので、where文の中では例えばp["誕生日"].Cast<DateTime>()みたいな書き方をすればいいと思います。若干煩わしいので、変換をかましたほうがきれいそうですね。
さて、ここまで解説すれば大体のExcelファイルについて「ヘッダー+データ」という構造ならば簡単に読み込めると思います。そういう構造じゃなければ、もしくは、ワークシートに対してセルのデータを読み出す以上の仕事を要求するのならそもそもLINQ to Excelは使えません。
そもそもExcel自体に強力なデータ整理機能があるので、あえてExcelでできるようなことをC#でやる必要はないとは思いますが、複数のExcelファイルにまたがった処理とか、毎回やるような簡単な処理をバッチ化したいとかでLINQ to Excelを活用できるととても便利かもしれません。
0 件のコメント:
コメントを投稿