【VB.NET】ClosedXML：テーブルの作成（１）－コレクションの挿入－

cell.Value = collection

cell.Value にコレクションを設定することによりワークシートにデータを挿入します。

Class Person
  Public Property Name As String
  Public Property House As String
  Public Property Age As Int32
End Class

Private Function GetTable As System.Data.DataTable 
  Dim table = New System.Data.DataTable
  table.Columns.Add("名前", GetType(String))
  table.Columns.Add("出生地", GetType(String))
  table.Columns.Add("誕生日", GetType(DateTime))
  table.Columns.Add("身長", GetType(Integer))

  table.Rows.Add("日本 太郎", "東京都", New DateTime(2000, 1, 1), 180)
  table.Rows.Add("西洋 花子", "ロンドン",  New DateTime(2003, 12, 7), 172)
  table.Rows.Add("エレン・イースト", "埼玉県", New DateTime(1996, 2, 16), 161)
  table.Rows.Add("ミカサ・アッカーマン", "シガンシナ区近郊", New DateTime(2002, 2, 10), 170)
  table.Rows.Add("中島 みゆき", "札幌市", New DateTime(1952, 2, 23), 161)

  Return table
End Function

Dim wb = New XLWorkbook
Dim ws = wb.Worksheets.Add("コレクション")



' String のリストから。
Dim listOfStrings = New List(Of String)
listOfStrings.Add("家")
listOfStrings.Add("車")
ws.Cell(1, 1).Value = "String"
ws.Cell(1, 1).AsRange.AddToNamed("Titles") '名前付き範囲
ws.Cell(2, 1).Value = listOfStrings



'配列のリストから。
Dim listOfArr = New List(Of Int32())
listOfArr.Add(New Int32() { 1, 2, 3 })
listOfArr.Add(New Int32() { 1 })
listOfArr.Add(New Int32() { 1, 2, 3, 4, 5, 6 })
ws.Cell(1, 3).Value = "配列"
ws.Range(1, 3, 1, 8).Merge.AddToNamed("Titles") '名前付き範囲
ws.Cell(2, 3).Value = listOfArr



'DataTable から。
Dim dataTable = GetTable
ws.Cell(6, 1).Value = "DataTable"
ws.Range(6, 1, 6, 4).Merge.AddToNamed("Titles") '名前付き範囲
'列タイトルは dataTable.Columns(1).ColumnName のように取得することもできるが
'ここではあえて列タイトルを入力しない（他の入力方法について記事の後半を参照）
'データ部分
ws.Cell(7, 1).Value = dataTable



'クエリから。
Dim list = New List(Of Person)
list.Add(New Person With { .Name = "John",  .Age = 30, .House = "Elm St."  })
list.Add(New Person With { .Name = "Mary",  .Age = 15, .House = "Main St." })
list.Add(New Person With { .Name = "Luis",  .Age = 21, .House = "23rd St." })
list.Add(New Person With { .Name = "Henry", .Age = 45, .House = "5th Ave." })

Dim people = from p in list where p.Age >= 21 select New With { p.Name, p.House, p.Age }

ws.Cell(6, 6).Value = "クエリ"
ws.Range(6, 6, 6, 8).Merge().AddToNamed("Titles") '名前付き範囲
'元記事に「.AsEnumerable メソッドを呼び出さないとコピーされない」と
'書いてあるが呼ばなくてもコピーされた。by エレン・イースト（当サイトの管理人）
'ws.Cell(7, 6).Value = people.AsEnumerable
ws.Cell(7, 6).Value = people



'名前付き範囲（タイトル）に書式を設定
Dim titlesStyle = wb.Style
titlesStyle.Font.Bold = true
titlesStyle.Alignment.Horizontal = XLAlignmentHorizontalValues.Center
titlesStyle.Fill.BackgroundColor = XLColor.Cyan

wb.NamedRanges.NamedRange("Titles").Ranges.Style = titlesStyle

'コンテンツが日本語の場合は日本語フォントにしておかないと AdjustToContents が正しく動作しない
ws.Style.Font.FontName = "游ゴシック";
ws.Columns.AdjustToContents

wb.SaveAs("D:\test\Collections.xlsx")

cell.InsertData(collection)

cell.value = collection のかわりに cell.InsertData(collection) を使用することもできます。

cell.InsertData(collection) は、collection が挿入された範囲 (IXLRange) を返します。

Dim rng = ws.Cell(7, 6).InsertData(collection)
rng.Style.Fill.BackgroundColor = XLColor.Green

cell.InsertTable(collection)

cell.value = collection のかわりに cell.InsertTable(collection) を使用することもできます。

cell.InsertTable(collection) は、collection が挿入された範囲のテーブル (IXLTable) を返します

Dim tbl = ws.Cell(7, 6).InsertTable(people)
tbl.DataRange.Style.Fill.BackgroundColor = XLColor.LightGreen

• InsertData と InsertTable の違いは、InsertData が列タイトルを挿入しないで範囲 (IXLRange) を返すのに対して、InsertTable は列名も挿入してテーブル (IXLTable) を返すことです。
• Excel のテーブルを作成するかどうか、テーブル名（名前付き範囲）を付けるかどうかをオプションで指定できます。
• （.NET 4 のみ）オブジェクトのコレクションからテーブルを挿入するとき、ClosedXML はカスタムプロパティ <ClosedXML.Attributes.XLColumn(Header:="フィールド名")> をチェックし、存在する場合は列タイトルとして使用します。存在しない場合はプロパティ名を使用します。

前述の「cell.Value = collection」で掲載したコードの cell.value = collection を cell.InsertTable(collection) に変更すると、実行結果は下図のようになります。

DataTable をワークシートとして追加する

DataTable をワークシートとして追加できます。

Private Function GetTable(tableName As String) As System.Data.DataTable 
    Dim table = New System.Data.DataTable

    table.TableName = tableName

    table.Columns.Add("名前", GetType(String))
    table.Columns.Add("出生地", GetType(String))
    table.Columns.Add("誕生日", GetType(DateTime))
    table.Columns.Add("身長", GetType(Integer))

    table.Rows.Add("日本 太郎", "東京都", New DateTime(2000, 1, 1), 180)
    table.Rows.Add("西洋 花子", "ロンドン",  New DateTime(2003, 12, 7), 172)
    table.Rows.Add("エレン・イースト", "埼玉県", New DateTime(1996, 2, 16), 161)
    table.Rows.Add("ミカサ・アッカーマン", "シガンシナ区近郊", New DateTime(2002, 2, 10), 170)
    table.Rows.Add("中島 みゆき", "札幌市", New DateTime(1952, 2, 23), 161)

    Return table
End Function

Dim wb = New XLWorkbook

'DataTable をワークシートとして追加
wb.Worksheets.Add(GetTable("Infomation"))

wb.SaveAs("D:\test\AddingDataTableAsWorksheet.xlsx")

DataTable.TableName を設定している点に注意してください。これがワークシートの名前になります。なお、列幅の調整のコードは省略してあります。

複数の DataTable を DataSet にまとめて追加することもできます（複数のワークシートが追加されます）。

Dim wb = New XLWorkbook

Dim ds = New System.Data.DataSet
ds.Tables.Add(GetTable("Patients"))
ds.Tables.Add(GetTable("Employees"))
ds.Tables.Add(GetTable("Information"))

wb.Worksheets.Add(ds)

wb.SaveAs("D:\test\AddingDataSet.xlsx")

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