【VB.NET】ClosedXML:式、ラムダ式

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("式")

'1行目。タイトル行。
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

'2行目--------------------------------
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 形式で表示

'3行目--------------------------------
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)}" '中括弧で挟む
'上の2つの合計額は同じ?
ws.Cell("A7").Value = "上の2つの合計額は同じ?"
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")

セルに式を入力しないで式の計算をする

例1

Dim sum = XLWorkbook.EvaluateExpr("SUM(1,2,3)")

例2

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)

'「能力者」列(表の3列目)が 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")

コードを実行すると下図のような結果が得られます。

 

「【VB.NET】ClosedXML の使い方」の一覧を表示

コメントの投稿

avatar
  購読する  
通知を受け取る対象