MEMO
- この記事は GitHub - ClosedXML - Wiki を参考に書かせていただいたものですが、C# から VB.NET への書き換えをし、英語から日本語へ翻訳しただけでなく、元記事の構成・コード・コメント等を変更している場合もありますのでご了承ください。
- 現在 ClosedXML のメンテナンスをご担当されている Francois Botha 様からの掲載許可もいただいてます。
- この記事のコードサンプルについては、Imports ClosedXML.Excel が宣言されていることを前提としています。
- ClosedXML の作成者について：Francois Botha, Aleksei Pankratev, Manuel de Leon, Amir Ghezelbash
- ClosedXML のライセンスについて：GitHub - ClosedXML - MIT License
式（Formula）
式を使用した表の作成
下図のような表を作成します。
Dim wb = New XLWorkbook
Dim ws = wb.Worksheets.Add("式")
'１行目。タイトル行。
ws.Cell(1, 1).Value = "商品"
ws.Cell(1, 2).Value = "単価"
ws.Cell(1, 3).Value = "数量"
ws.Cell(1, 4).Value = "小計"
ws.Cell(1, 5).Value = "小計の式（A1形式）"
ws.Cell(1, 6).Value = "小計の式（R1C1形式）"
'タイトル行の書式
ws.Range(1, 1, 1, 6).Style.Fill.BackgroundColor = XLColor.Cyan
ws.Range(1, 1, 1, 6).Style.Font.Bold = True
'２行目--------------------------------
ws.Cell(2, 1).Value = "りんご" '商品
ws.Cell(2, 2).Value = 100 '単価
ws.Cell(2, 3).Value = 5 '数量
Dim cellWithFormulaA1 = ws.Cell(2, 4) '小計セル
'式を A1 形式で設定
cellWithFormulaA1.FormulaA1 = "=B2*$C$2" '式の先頭の = はあっても無くても良い
ws.Cell(2, 5).Value = cellWithFormulaA1.FormulaA1 '小計の式を A1 形式で表示
ws.Cell(2, 6).Value = cellWithFormulaA1.FormulaR1C1 '小計の式を R1C1 形式で表示
'３行目--------------------------------
ws.Cell(3, 1).Value = "バナナ" '商品
ws.Cell(3, 2).Value = 200'単価
ws.Cell(3, 3).Value = 2 '数量
Dim cellWithFormulaR1C1 = ws.Cell(3, 4) '小計セル
'式を R1C1 形式で設定
cellWithFormulaR1C1.FormulaR1C1 = "RC[-2]*R3C3" '式の先頭の = はあっても無くても良い
ws.Cell(3, 5).Value = cellWithFormulaR1C1.FormulaA1 '小計の式を A1 形式で表示
ws.Cell(3, 6).Value = cellWithFormulaR1C1.FormulaR1C1 '小計の式を R1C1 形式で表示
'データ範囲の定義
Dim rngData = ws.Range(2, 1, 4, 6) 'firstCellRow, firstCellColumn, lastCellRow, lastCellColumn
'合計（小計の合計）
ws.Cell("A5").Value = "合計額（小計の合計）："
ws.Cell("B5").FormulaA1 = "=SUM(D2:D3)"
'合計（配列数式）
ws.Cell("A6").Value = "合計額（配列数式）："
ws.Cell("B6").FormulaA1 = "{=SUM(B2:B3*C2:C3)}" '中括弧で挟む
'上の２つの合計額は同じ？
ws.Cell("A7").Value = "上の２つの合計額は同じ？"
ws.Cell("B7").FormulaR1C1 = "=IF(R[-2]C=R[-1]C, ""はい"", ""いいえ"")"
'セルのコンテンツに合わせて列幅を調整
'コンテンツが日本語の場合は日本語フォントにしておかないと AdjustToContents が正しく動作しない
ws.Style.Font.FontName = "游ゴシック";
ws.Columns().AdjustToContents
'ワークブックの参照形式の変更
wb.ReferenceStyle = XLReferenceStyle.R1C1
'ワークブックの計算方法
wb.CalculateMode = XLCalculateMode.Auto
wb.SaveAs("D:\test\Formulas.xlsx")セルに式を入力しないで式の計算をする
例１
Dim sum = XLWorkbook.EvaluateExpr("SUM(1,2,3)")
例２
Dim wb As New XLWorkbook
Dim ws = wb.AddWorksheet("Sheet1")
ws.Cell("A1").Value = 3
ws.Cell("A2").Value = 5
Dim sum = wb.Evaluate("SUM(Sheet1!A1:B2)")
'Dim sum = ws.Evaluate("SUM(A1:B2)")式が使えない場合
Excel で使用できるすべての式が ClosedXML で利用できるわけではないので、事前に式のテストをするようにしてください。
式の追加は常に行っていますが、使用したい式が ClosedXML で使えない場合は Issue Tracker で私（ClosedXML の開発者）にお知らせください。
できるだけ早く追加するようにします。
ラムダ式
下図のような既存のワークシートがあるとします。
上図の表から能力者でない者の行を削除し、データ型がテキストのセルに色を付けます。
Dim workbook = New XLWorkbook("D:\test\むぎわらの一味.xlsx")
Dim ws = workbook.Worksheet(1)
'データ範囲を定義する
Dim firstDataCell = ws.Cell("B4") '開始セル
Dim lastDataCell = ws.LastCellUsed '終了セル
Dim rngData = ws.Range(firstDataCell.Address, lastDataCell.Address)
'「能力者」列（表の３列目）が False の行をすべて削除する
rngData.Rows(Function(r) r.Cell(3).GetBoolean = False).ToList.ForEach(Sub(r) r.Delete)
'rngData.Rows(Function(r) r.Cell(3).GetBoolean = False).Delete
'データ型がテキストのセルに色を付ける
rngData.Cells(Function(c) c.DataType = XLDataType.Text).ToList.ForEach(Sub(r) r.Style.Fill.BackgroundColor = XLColor.Gold)
'rngData.Cells(Function(c) c.DataType = XLDataType.Text).Style.Fill.BackgroundColor = XLColor.Gold
'表の底辺に太線を付ける（表の最後の行を削除する可能性があるため）
rngData.LastRow.Style.Border.BottomBorder = XLBorderStyleValues.Medium
workbook.SaveAs("D:\test\むぎわらの一味（能力者）.xlsx")コードを実行すると下図のような結果が得られます。
