ClosedXML - Excelファイルの操作
概要
Microsoft Office(Word / Excel / PowerPoint)のファイルをプログラムから操作するために、Open XML SDKがMicrosoftから提供されているが、
汎用的な構造になっているため、特にExcelファイルのデータや書式を扱うには煩雑になっている。
Excelファイルのみを扱う場合、サードパーティ製のClosed XMLを使用するとよい。
ここでは、そのClosed XMLの使用方法を記載する。
Closed XMLとは
Open XML形式のExcelファイル(拡張子 : xlsx / xlsm)を読み書きするためのライブラリである。
MicrosoftのOpen XML SDKをラップし、Excelに特化することで使用しやすいライブラリになっている。
ソースコードはGitHubで公開されている。(MITライセンス)
- できること
- Open XML形式のExcelファイルの編集と数式の実行。
- Open XML形式のExcelファイルの実体は、XMLファイルを格納したzipアーカイブである。
- そのアーカイブを作成したり、アーカイブ内のデータを読み書きしたりできる。
- セルのデータや数式などの読み書きや書式の設定などが可能である。
- 更に、Excelの主な関数を独自に実行する機能も持っている。
- できないこと
- Excelの機能を利用すること。
- Open XML形式のExcelファイルの読み書きは可能だが、Excelアプリの機能を利用しているわけではない。
- Excelアプリの機能、例えば、グラフを生成したりはできない。
- ただし、Open XML SDKとは異なり、Excelの主な関数はサポートされている。
- サポートされているプラットフォーム
- .NET Framework 4.0以降
- .NET Standard 2.0以降
- .NET Standard 2.0で動作するということは、それをサポートしている以下のプラットフォームからも利用できる。
- .NET Core(ASP.NET Core) 2.0以降
- UWP 10.0.16299（1709）～
- Xamarin.iOS 10.14以降
- Xamarin.Android 8.0以降
Closed XMLのドキュメントは、ClosedXMLのWikiで公開されている。
ここでは、Excelファイルを操作する例をいくつか記載するだけなので、詳しい使用方法はこのドキュメントを参照すること。
Closed XMLの導入方法
GitHubからソースコードをダウンロードしてビルドしてもよいが、バイナリファイルがNuGetで公開されている。
Visual Studioでプロジェクトを作成して、NuGetでClosedXmlを検索してインストールする。
Open XML SDKなど、いくつかの必要なパッケージが一緒にインストールされる。
Closed XMLの使用方法
ここからは、Excelファイルを操作する方法を簡単に記載する。(詳細は前述のドキュメントを参照すること)
const string ExcelFilePath = @"sample.xlsx";
// Excelファイルの作成
using (var workbook = new XLWorkbook())
{
// ワークシートの追加
using (var worksheet = workbook.Worksheets.Add("サンプルシート1"))
{
// セルに値や数式を代入
worksheet.Cell("A1").Value = 10;
worksheet.Cell("A2").SetValue(20);
worksheet.Cell("A3").FormulaA1 = "SUM(A1:A2)";
// セルに書式を設定
var sumCellStyle = worksheet.Cell("A3").Style;
sumCellStyle.Fill.BackgroundColor = XLColor.Red; // 塗りつぶし
sumCellStyle.NumberFormat.Format = "#,##0.00"; // 数値の書式
// 次のようにメソッドチェーンでも書ける
//worksheet.Cell("A3").SetFormulaA1("SUM(A1:A2)")
// .Style.Fill.SetBackgroundColor(XLColor.Red)
// .NumberFormat.SetFormat("#,##0.00");
// ワークブックを保存する
workbook.SaveAs(ExcelFilePath);
}
Console.WriteLine(@"Excelファイルを保存しました。");
}
Excelファイルのデータを読み書きする
上記のサンプルコードで作成したExcelファイルを読み込み、セルのデータを読み書きするサンプルコードを以下に記述する。
const string ExcelFilePath = ".\\sample.xlsx";
// Excelファイルを開く
using (var workbook = new XLWorkbook(ExcelFilePath))
{
// ワークシートの取得
using (var worksheet = workbook.Worksheet("サンプルシート1"))
{
// 位置を指定してセルを取得
var cellA1 = worksheet.Cell("A1");
var cellA2 = worksheet.Cell(2, "A"); // 行番号と列名でも指定可能
var cellA3 = worksheet.Cell(3, 1); // 行番号と列番号でも指定可能
Console.WriteLine($"A1: {cellA1.GetValue<int>()}"); // 出力 : A1 = 10
Console.WriteLine($"A2: {cellA2.GetValue<int>()}"); // 出力 : A2 = 20
Console.WriteLine($"A3（値）: {cellA3.GetValue<int>()}"); // 出力 : A3(値) = 30
Console.WriteLine($"A3（数式）: {cellA3.FormulaA1}"); // 出力 : A3(数式) = SUM(A1:A2)
// セルA1の値を書き換える
cellA1.Value = 15;
Console.WriteLine($"A1の値を{cellA1.GetValue<int>()}に書き換えました。"); // 出力 : A1の値を15に書き換えました。
Console.WriteLine($"A3の値は{cellA3.GetValue<int>()}に変わりました。"); // 出力 : A3の値は35に変わりました。
// ワークブックを保存する
workbook.Save();
}
}
コレクションやDataTableの使用
Closed XMLは、コレクションやLINQなどと相性が良い。
コレクションやDataTableオブジェクト(System.Data名前空間)を簡単にワークシートに挿入できる。
以下のサンプルコードは、コレクションを挿入している例である。
データを挿入するInsertDataメソッドは、挿入したセルの範囲を返すので、続けてその範囲のセルを処理しやすい。
また、その範囲から特定の行を抽出するには、ラムダ式で条件を指定できる。
以下のサンプルコードではコレクションを挿入したが、DataTableにデータを挿入するときも同様にInsertDataメソッドを使用する。
const string ExcelFilePath = @"sample.xlsx";
// Excelファイルに貼り付けたいデータ
// 例として、ここでは今月の日付と曜日のコレクションを用意する
List<(DateTime, string)> days = new List<(DateTime, string)>();
var firstDayOfThisMonth = new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1);
int daysInThisMonth = DateTime.DaysInMonth(firstDayOfThisMonth.Year, firstDayOfThisMonth.Month);
for (int i = 0; i < daysInThisMonth; i++)
{
var d = firstDayOfThisMonth.AddDays(i);
days.Add((d, d.DayOfWeek.ToString()));
}
// Excelファイルを開く
using (var workbook = new XLWorkbook(ExcelFilePath))
{
// ワークシートの追加
using (var worksheet = workbook.Worksheets.Add("サンプルシート2"))
{
// タイトル行
var cellA1 = worksheet.Cell("A1");
cellA1.Value = "今月のカレンダー";
cellA1.Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center)
.Font.SetFontName("Meiryo")
.Font.SetBold(true)
.Font.SetFontColor(XLColor.White)
.Fill.SetBackgroundColor(XLColor.FromArgb(0x00A2E8));
// セルの連結
worksheet.Range("A1:B1").Merge();
// ワークシートに日付と曜日のコレクションを挿入
var calendarRange = worksheet.Cell("A2").InsertData(days);
// 日曜日の行を抽出して使用しているセルの背景を赤に変更
var sundayRows = calendarRange.Rows(r => r.Cell(1).GetDateTime().DayOfWeek == 0);
foreach (var r in sundayRows)
{
r.CellsUsed().Style.Fill.BackgroundColor = XLColor.Red;
}
// ワークブックの保存
workbook.Save();
}
Console.WriteLine("ワークシートを追加しました。");
}
上記で作成したExcelファイルを読み取る。
何らかのデータや数式が入力されているセルの範囲を取得するRangeUsedメソッドがあるので、セルの位置を指定せずに簡単にデータ範囲を取り出せる。
そのセル範囲からコレクションを生成するには、LINQのSelect拡張メソッドを使用する。
また、DataTableに変換するのも簡単である。
const string ExcelFilePath = ".\\sample.xlsx";
// Excelファイルを開く
using (var workbook = new XLWorkbook(ExcelFilePath))
{
// ワークシートの取得
using (var worksheet = workbook.Worksheet("サンプルシート2"))
{
// データや数式が入力されているセルの範囲
var rangeUsed = worksheet.RangeUsed();
Console.WriteLine($"使用中のセル範囲：{rangeUsed.FirstCell().Address}～{rangeUsed.LastCell().Address}");
// 1列目がDateTime型の行のみを取得してコレクションに変換
IEnumerable<(DateTime,string)> data = rangeUsed.Rows(r => r.Cell(1).DataType == XLDataType.DateTime)
.Select(r => (r.Cell(1).GetDateTime(), r.Cell(2).GetString()));
// 最初の7個を表示
// 出力 : 10/1(Monday) 10/2(Tuesday) 10/3(Wednesday) 10/4(Thursday) 10/5(Friday) 10/6(Saturday) 10/7(Sunday)
foreach (var (d, s) in data.Take(7))
{
Console.WriteLine($"{d:M/d}({s})");
}
// DataTableへの変換もサポート
System.Data.DataTable dataTable = rangeUsed.CreateTable().AsNativeDataTable();
// 8個目から7個を表示
// 出力 : 10/8(Monday) 10/9(Tuesday) 10/10(Wednesday) 10/11(Thursday) 10/12(Friday) 10/13(Saturday) 10/14(Sunday)
foreach(System.Data.DataRow r in dataTable.Select().Skip(7).Take(7))
{
Console.WriteLine($"{r[0]:M/d}({r[1]})");
}
}
}