【C#でエクセル操作】
ClosedXMLの特徴と操作まとめ

記事イメージ


(最終更新日:

お疲れ様です。堺です。

ClosedXMLは、C#でのエクセル操作ライブラリの中でも、実用性や使いやすさの面で信頼しているライブラリです(私自身も長年活用させていただいています)。その優れたポイントと基本的な実装コードをまとめてご紹介します。

以下、ページ内リンクです。目的に応じてご活用ください。

(ページ内リンク)チートシートを見たい、具体的な実装方法を探す方はこちら

(ページ内リンク)ClosedXMLと何?特徴は?導入方法は?を知りたい方はこちら

(ページ内リンク)XLWorkbookオブジェクト生成時の小技を知りたい方はこちら

スポンサーリンク

◇目次

  1. ClosedXMLの特徴 ~メリットが多く使いやすいライブラリ
  2. 導入:NuGetでVisual Studioのプロジェクトにインストール
  3. チートシート:基本的な処理実装方法まとめ
  4. ワークブックを開く際のXLWorkbook引数のテクニック
  5. トラブルシューティング:作成したワークブックを開くと、「'XXXX.xlsx'の一部の内容に問題が見つかりました。」と表示される場合
  6. 「C#でエクセル」ができるEPPlusについて、ClosedXMLとの比較を関連記事にまとめています。
  7. 参考リンク(Github)
  8. 記事筆者へのお問い合わせ、仕事のご依頼

ClosedXMLの特徴 ~メリットが多く使いやすいライブラリ

ClosedXMLはC#のライブラリです。概要は次の通りです。

ClosedXMLの概要
  • C#でエクセルファイルを新規または既存ファイルを操作をするためのライブラリ。
  • NuGet(Visual Studio標準のパッケージマネージャ)で入手可能。
  • オープンソースによる開発、MITライセンス
ClosedXMLで何ができるのか?→エクセルの標準機能は一通り実行可能
  • エクセルファイルの新規作成
  • 既存エクセルファイルの読み込み
  • 値や式の入力
  • セル挿入削除、結合
  • シート追加
  • その他諸々(ピボットテーブル、表示の固定、その他)

そして、いくつかのエクセル操作用C#ライブラリを試した中でClosedXMLをオススメする理由をまとめてみます。

ClosedXMLのオススメポイント
  • APIが直感的で簡潔に記述できる。特にマクロ制作経験があれば、VBAに文法が似ており理解しやすい。
  • 値や数式の取得及び記述、行列単位や範囲での切り出し、シート追加、表示関連操作など、幅広い機能が実装されている。基本的な操作はまず困らない。
  • Open XML SDK(Microsoft純正API)のラッパーであり、安定感がある。
  • そこそこ高速な動作
  • .NET Framework系、Core系のいずれにも対応

尚、上記の通りOpen XML規格が前提となっています。そのためExcel2003以前のフォーマット(.xls形式)では使用することができません。

プロジェクトで扱うファイルを事前に確認した上で導入するようにしましょう。

新しいファイルを作り、値や式を書き込めます(右がコード、左が実行結果)。既存ファイルを開いて編集することもできます。
新しいファイルを作り、値や式を書き込めます(右がコード、左が実行結果)
既存ファイルを開きセルから値を読み込むことも、もちろんできます。(右がコード、左が実行結果)
既存ファイルを開きセルから値を読み込むことも、もちろんできます

導入:NuGetでVisual Studioのプロジェクトにインストール

ClosedXMLは下図の通りNuGetパッケージマネージャーでVisual Studioのプロジェクトにインストール可能です。

MITライセンスであるため、商用の場合でも安心して利用することができます。(必要な表示は必ず行いましょう。)

nuget

チートシート:基本的な処理実装方法まとめ

ワークブックの作成、編集、保存、変換の一連の流れをまとめます。

コード冒頭のusing記述

NuGetでのインストール後、コード冒頭に記述するusingは1行でOKです。

C#
using ClosedXML.Excel;

新しいワークブックの作成

空のワークブックを作成します。

リソースはアンマネージドのため、usingステートメントと一緒に使用することをオススメします。(保存する前にオブジェクトを破棄することがないように注意しましょう。)

C#
using var wb = new XLWorkbook();

//尚、C#8.0より前であれば以下の様に記述します。
using (var wb = new XLWorkbook()) {
    //do something
}

※詳細はこちらで後述(ページ内リンク)しますが、new XLWorkbookについては次のようなテクニックもご活用ください。

C#
//①XLEventTracking.Disabledの指定(セル挿入・削除追跡をオフ)によるパフォーマンス向上
//既存ファイルを開く場合
using var wb = new XLWorkbook(@"C:\Users\sakai\aaa.xlsx", XLEventTracking.Disabled);
//新規ファイルの場合
using var wb = new XLWorkbook(XLEventTracking.Disabled);

//②読み取り専用で既存ファイルを開く
using var fileStream = new FileStream(@"C:\Users\sakai\aaa.xlsx", FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
using var wb = new XLWorkbook(fileStream);

ワークブックにワークシートを追加

ワークブックを新規に作っただけではシートが一つもないので、追加していきます。

IXLWorksheets.Addメソッドに新規シート名を指定します。

Addメソッドの戻り値に新しく作ったワークシートオブジェクトが格納されますので、このオブジェクトに対して作業をしていきます。

C#
using var wb = new XLWorkbook();
var ws = wb.Worksheets.Add("新しいシート名");

ちなみにwb.Worksheetsの型IXLWorksheetsは、IEnumerable<IXLWorksheet>の派生型です。(AddメソッドはIXLWorksheetsクラスで定義されています。)

したがって、例えばLinqを使って既存のワークシートの取得・絞り込みなどが行えます。

C#
//例えば名前で絞込み
var wss = wb.Worksheets.Where(x => x.Name.Contains("test"));

既存のワークブックファイルを開く

既存のファイルを開くには、引数にパスを指定します。

C#
using var wb = new XLWorkbook(@"C:\Users\sakai\test.xlsx");

ワークブックの中の既存のワークシートを取得

XLWorkbook.Worksheetメソッドの引数にシート名を指定します。存在しないシート名が指定された場合、例外がスローされます。

C#
var ws = wb.Worksheet("シート名");

シート位置をint型で指定して取得することもできます。

位置番号は、ワークブックの一番左のシートが1で、右に行くにつれて1ずつ大きくなります。引数に0以下の数値を指定したり、シート数を超えた数値を指定すると、例外がスローされます。

C#
//一番左のシートを取得
var ws = wb.Worksheet(1);

尚、混同しやすいですが、XLWorkbook.Worksheetはメソッド、XLWorkbook.Worksheetsはプロパティとして存在しています。

セルオブジェクトを取得

C#
//A1セルに値を記述(第1引数が行番号、第2引数が列番号にあたります)
var cell = ws.Cell(1, 1);
//次の記法も同等の効果が得られます。
cell = ws.Cell("A1");
cell = ws.Row(1).Cell(1);

ワークシートのセルに値を記述

セルに値をセットする場合はこちらです。

尚、他のセルを参照するなどのエクセル式を記述したい場合、後段のFormulaA1等を使用する必要がありますのでご注意ください。(Value/SetValueを使用すると文字列になってしまいます。)

C#
using var wb = new XLWorkbook();
var ws = wb.Worksheets.Add("新しいシート名");
var cell = ws.Cell(1, 1);
//SetValueメソッドを使用するか、Valueプロパティに値をセットします。
//以下2つのコードは同等の結果を得られます。
cell.SetValue("てすと");
cell.Value = "てすと";

//SetValueメソッドは当該IXLCellオブジェクトが返り値となるため、
//次のように続けて書式指定などをする場合に便利です。
cell
    .SetValue("てすと")
    .Style.Fill.SetBackgroundColor(XLColor.FromArgb(155, 194, 230));

尚、RangeやCellsのValueプロパティもしくはSetValueメソッドに値を指定することで、範囲内にまとめて値や式、書式をセットすることが出来ます。

C#
//A1~A10000セルにまとめて値をセット
ws.Range(1, 1, 10000, 1).SetValue("2");
ws.Range(1, 1, 10000, 1).Value = "2";

//A1~A10000セルにまとめて式をセット
ws.Range(1, 1, 10000, 1).FormulaA1 = "=1+1";

//A1~A10000セルの背景をまとめて黒にする
ws.Range(1, 1, 10000, 1).Style.Fill.BackgroundColor = XLColor.Black;

※ちなみに、2022年初頭時点のGAS(Google Apps Script、Googleスプレッドシート版VBAライクな処理が可能な開発環境)では、Rangeでやるかセル個別処理でやるかという違いで大幅な処理速度の差が出る(範囲の方が早い)のですが、ClosedXMLで実測してみたところ、Rangeに対して値をセットしたほうが若干早い程度で、大きな差は見られませんでした。

ワークシートのセルから値を取得

セルの値に合わせてobject型で取得されます。適宜必要な形式に変換して使いましょう。

C#
using var wb = new XLWorkbook();
var ws = wb.Worksheets.Add("新しいシート名");
var cell = ws.Cell(1, 1);
//ワークシートのセルから値を取得
var value = cell.Value;

ワークシートのセルに式をセット

A1形式で式を指定する場合はFormulaA1系、RC形式で使用する場合はFormulaR1C1系のメソッド・プロパティを使用します。

C#
using var wb = new XLWorkbook();
var ws = wb.Worksheets.Add("新しいシート名");
var cell = ws.Cell(1, 1);
//SetValueと同じように、SetFormulaA1メソッドを使用するか、FormulaA1プロパティに値をセットします。
//以下2つのコードは同等の結果を得られます。
cell.SetFormulaA1("=B1+C1");
cell.FormulaA1 = "=B1+C1";

//SetFormulaA1メソッドは当該IXLCellオブジェクトが返り値となるため、
//次のように続けて書式指定などをする場合に便利です。
cell
    .SetFormulaA1("=B1+C1")
    .Style.Fill.SetBackgroundColor(XLColor.FromArgb(155, 194, 230));

印刷範囲(改ページ)を指定

IXLWorksheet.PageSetup.PrintAreasプロパティにAddメソッドで印刷範囲を追加します。

追加した順に、シート内で1ページ目、2ページ目・・・と増えていきます。

尚、PrintAreasプロパティの型IXLPrintAreasはIEnumerable<IXLRange>の派生型で、こちらもLinq等が有効です。(AddメソッドはIXLPrintAreasクラスで定義されています。)

C#
using var wb = new XLWorkbook();
var ws = wb.Worksheets.Add("新しいシート名");
//A1:F10の範囲を印刷範囲に指定
ws.PageSetup.PrintAreas.Add("A1:F10");

セルの書式設定

セルやテキストの色、数値の表示書式、罫線などは、IXLCell.Styleプロパティに対して設定します。

C#
using var wb = new XLWorkbook();
var ws = wb.Worksheets.Add("新しいシート名");
var cell = ws.Cell(1, 1);
//A1セルに書式をセット
cell.Style.Fill.BackgroundColor = XLColor.FromArgb(155, 194, 230); //セルの色
cell.Style.Border.TopBorder = XLBorderStyleValues.Dotted; //セルの下側の罫線
cell.Style.Border.BottomBorder = XLBorderStyleValues.Thin; //セルの上側の罫線(点線)
cell.Style.DateFormat.Format = "0.0%;▲0.0%;- "; //数値の書式

//以下の書き方でも同じ結果を得られます。
//メソッド形式の場合、戻り値に対して連続で書式設定ができるので便利です。
cell.Style
    .Fill.SetBackgroundColor(XLColor.FromArgb(155, 194, 230)) //セルの色
    .Border.SetBottomBorder(XLBorderStyleValues.Thin) //セルの下側の罫線
    .Border.SetTopBorder(XLBorderStyleValues.Dotted) //セルの上側の罫線(点線)
    .NumberFormat.SetFormat("0.0%;▲0.0%;- "); //数値の書式


ワークブックを上書き保存

既存ファイルを開いている場合、Saveメソッドで上書き保存ができます。

C#
using var wb = new XLWorkbook(@"C:\Users\sakai\Desktop\Book1.xlsx");
//上書き保存
wb.Save();

新規作成ファイル(new XLWorkbook()で引数なしで作成したXLWorkbookオブジェクト)に対してSaveメソッドを実行すると、例外がスローされます。新規ファイルの場合は、次に説明するSaveAsメソッドで名前を付けて保存しましょう。
新規作成ファイル(new XLWorkbook()で引数なしで作成したXLWorkbookオブジェクト)に対してSaveメソッドを実行すると、例外がスローされます

ワークブックを名前を付けて保存(別名保存)

別名保存する場合、SaveAsメソッドに保存先のファイルパスを引数として渡します。

C#
using var wb = new XLWorkbook();
var ws = wb.Worksheets.Add("新しいシート名");
//引数に保存先パスを指定
wb.SaveAs(@"C:\Users\sakai\test2.xlsx");

ワークシートからDataTable型への変換

ワークシートの範囲を指定して.NETネイティブ型であるSystem.Data.DataTable型に変換できます。

それなりに時間がかかる処理なので、組み込む場合は応答時間に注意しましょう。

尚、コード内で使用しているRangeUsedメソッドは、ワークシート内で使用済みのセル範囲(A1:XX)を取得する機能を持ちます。

C#
using var wb = new XLWorkbook();
var ws = wb.Worksheets.Add("新しいシート名");
//DataTable型への変換処理
DataTable table = ws.RangeUsed().AsTable().AsNativeDataTable();

ASP.NET Core MVC / ASP.NET MVCでエクセルファイルをダウンロード

MVCプロジェクトでファイルをダウンロードするには、ファイルをバイト配列(byte[]型)に変換した上、FileメソッドでFileContentResultオブジェクト(ActionResult派生クラス)を返します。

XLWorkbook.SaveAsメソッドでMemoryStreamに書き出し可能なため、経由してバイト配列に変換します。

C#
public IActionResult DownloadXlsx() {
    using var wb = new XLWorkbook();
    var ws = wb.Worksheet("新しいシート名");

    using var ms = new MemoryStream();
    wb.SaveAs(ms);
    //ASP.NET MVCの場合
    return System.Web.Mvc.File(ms.ToArray(), "application/msexcel", "test3.xlsx");
}

上記コードのreturnステートメントにて、System.Web.Mvc.Fileメソッドを使用しています。

Core MVCの場合では、名前空間が下のようになりますのでご注意ください。

C#
    // ASP.NET Core MVC(.NET5.0~)の場合
    return Microsoft.AspNetCore.Mvc.File(ms.ToArray(), "application/msexcel", "test3.xlsx");

ワークブックを開く際のXLWorkbook引数のテクニック

XLWorkbookクラスのコンストラクタ(new XLWorkbook())でワークブックを開く際のテクニックを紹介します。

セル挿入・削除トラッキングをオフにすることによるメモリ節約・パフォーマンス向上

デフォルトのオープン方法では、セルの挿入・削除は追跡され、その後のセル指定に反映されます。(例えば、1行目を削除した後に3行目を指定した場合、実際には2行目が選択される、といった具合)

この機能が不要の場合、XLEventTracking.Disabledをコンストラクタに指定することで、メモリを節約し、パフォーマンスを向上できます。

セルを挿入・削除しない、またはセルが移動することを前提に範囲指定する場合、このモードが推奨されます。

C#
//引数にXLEventTracking.Disabledを指定することがここでのポイントです。
//セル挿入・削除トラッキングをオフにしてパフォーマンスを向上します。

//既存ファイルを開く場合
using var wb = new XLWorkbook(@"C:\Users\sakai\aaa.xlsx", XLEventTracking.Disabled);

//新規ファイルの場合
using var wb = new XLWorkbook(XLEventTracking.Disabled);

参考:Turning off events - github

他のプロセスで使用されている既存ファイルを開くテクニック

ワークシートを開いたままにしているなどの場合、new XLWorkbookでパスを指定して開こうとすると例外が発生します。

XLWorkbookコンストラクタは、ファイルパス(string型)の他、System.IO.Streamを指定できます。そこで、一旦FileStreamで読み取り専用で開き、XLWorkbookオブジェクトを作成します。

C#
//本タスクでは、FileStreamに第4引数まで指定する必要があります。
//【引数の内容】
//第2引数:新規作成 or 既存を開くの指定
//第3引数:ファイルアクセスモード(書き込み/読み取り)
//第4引数:開いている間に他のプロセスで許可する内容
using var fileStream = new FileStream(@"C:\Users\sakai\aaa.xlsx", FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
using var wb = new XLWorkbook(fileStream);

ファイルが使用中の場合、一工夫しないとIOExceptionがスローされます。
ファイルが使用中の場合、一工夫しないとIOExceptionがスローされます
FileStream経由で読み取り専用で開いたファイルは、Saveメソッド(上書き)を使用すると例外がスローされます。SaveAsメソッドで別名保存することは可能です。
FileStream経由で読み取り専用で開いたファイルは、Saveメソッド(上書き)を使用すると例外がスローされます

トラブルシューティング:作成したワークブックを開くと、「'XXXX.xlsx'の一部の内容に問題が見つかりました。」と表示される場合

記事分離しました。下記関連記事をご参考ください。結論だけ述べると、不完全な式をセルにセットしていることが原因です。

関連記事:【マクロ・ClosedXML】'xxxx.xlsx'の一部の内容に問題が見つかりました。とエラーが表示される場合の原因と対処法

「C#でエクセル」ができるEPPlusについて、ClosedXMLとの比較を関連記事にまとめています。

同じくC#でエクセル操作ができる代表的なライブラリとしてEPPlusが挙げられます。

ClosedXMLとの差(ライセンス、処理速度、出来ること出来ないこと等)について以下の関連記事にまとめています。セル読み書き処理速度についてはEPPlusの方が高速という結果が出ています。

関連記事:EPPlus vs ClosedXML 処理速度・概要比較
スポンサーリンク

参考リンク(Github)

Githubにてソースコードが公開されています。

記事筆者へのお問い合わせ、仕事のご依頼

当社では、IT活用をはじめ、業務効率化やM&A、管理会計など幅広い分野でコンサルティング事業・IT開発事業を行っております。

この記事をご覧になり、もし相談してみたい点などがあれば、ぜひ問い合わせフォームまでご連絡ください。

皆様のご投稿をお待ちしております。

記事筆者へ問い合わせする

※ご相談は無料でお受けいたします。
IT活用経営を実現する - 堺財経電算合同会社