【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

下図のような既存のワークシートがあるとします。

前提条件

  • 対象データは「会社データ」という名前のワークシートにあるとします。
  • 対象のワークシートには1つ以上の空行を挟んで2つのテーブル(カテゴリテーブル、会社テーブル)があるとします。
  • いずれのテーブルも開始行・開始列が変わる可能性があるとします(プログラムでテーブルの場所を探す)。
  • 最初のテーブルの列タイトルは変わる可能性がありますが、列順は変わらないとします(列位置から対象データを探す)。
  • 2つ目のテーブルの列タイトルは変わりませんが、列順は変わる可能性があるとします(列タイトルから対象データを探す)。

処理内容

カテゴリ名のリストと会社名のリストを抽出します。

コード

呼び出し側(Main など)

Dim categories As New List(Of String)
Dim companies As New List(Of String)
Call カテゴリ名と会社名を取得("D:\test\会社データ.xlsx", categories, companies)

'取得したリストを好きなように処理しましょう

Console.WriteLine(vbCrLf & "===== カテゴリ =====")
For Each category As String In categories
    Console.WriteLine(category)
Next

Console.WriteLine(vbCrLf & "===== 会社 =====")
For Each company As String In companies
    Console.WriteLine(company)
Next

 

「カテゴリ名と会社名を取得」関数

Private Sub カテゴリ名と会社名を取得(targetFileFullname As String, categories As List(Of String), companies As List(Of String))

'対象ファイルを開いてワークブックを取得
Dim wb = new XLWorkbook(targetFileFullname)
'シート名からワークシートを取得
Dim ws = wb.Worksheet("会社データ")

Const categoryIdColNum As Integer = 1 'カテゴリID の列の位置
Const categoryNameColNum As Integer = 2 'カテゴリ名の列の位置

'使用されている最初の1行全体を取得(これには未使用の列範囲も含まれている)。
Dim firstRowUsed = ws.FirstRowUsed

'使用されている最初の1行全体の範囲から、使用されている範囲のみを求める(不要な列範囲を取り除く)
Dim categoryRow = firstRowUsed.RowUsed '現在、タイトル行を指している

'対象範囲を次の行に移動する(ここからデータ行)
categoryRow = categoryRow.RowBelow

'カテゴリ ID の列に ID が記入されている限り、行を順に処理してカテゴリ名を取得していく
While categoryRow.Cell(categoryIdColNum).IsEmpty = False
    Dim categoryName As String = categoryRow.Cell(categoryNameColNum).GetString
    categories.Add(categoryName)
    categoryRow = categoryRow.RowBelow
End While

'会社テーブルを取得する方法はいろいろあります。
'ここでは直接的なやり方をしてみましょう。
'他の方法としては、たとえば、row.IsEmpty が真である限りループ処理して
'会社テーブルの最初の行を見つけることもできます。

'会社テーブルの開始位置になり得る最初のアドレスを取得
'(現在 categoryRow はカテゴリテーブルの1つ下の行を指している)
Dim firstPossibleAddress = ws.Row(categoryRow.RowNumber).FirstCell.Address
'会社テーブルの終了位置になり得る最後のアドレスを取得
Dim lastPossibleAddress = ws.LastCellUsed.Address

'会社テーブルが存在し得る範囲から、使用されている範囲を取得する
Dim companyRange = ws.Range(firstPossibleAddress, lastPossibleAddress).RangeUsed

'取得した範囲をテーブルとして扱う(列タイトルを使用するため)
Dim companyTable = companyRange.AsTable

'会社名のリストを取得
' → 会社テーブルのデータ範囲の行を取得
' → 行を順次処理して「会社名」列の値をリストにして返す
' → companies に格納
companies.AddRange(
    companyTable.DataRange.Rows _
    .Select(Function(companyRow) companyRow.Field("会社名").GetString) _
    .ToList
)

End Sub

 

実行結果

 

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

コメントの投稿

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