【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

事前準備

まず、サンプルデータ挿入用の関数などを用意します。

'サンプルデータ挿入用
Private Sub AddTestColumn(ws As IXLWorksheet)
  ws.Cell("A1").SetValue("B").Style.Fill.SetBackgroundColor(XLColor.LightGreen)
  ws.Cell("A2").SetValue("A").Style.Fill.SetBackgroundColor(XLColor.DarkTurquoise)
  ws.Cell("A3").SetValue("a").Style.Fill.SetBackgroundColor(XLColor.BurlyWood)
  ws.Cell("A4").SetValue("").Style.Fill.SetBackgroundColor(XLColor.DarkGray)
  ws.Cell("A5").SetValue("").Style.Fill.SetBackgroundColor(XLColor.DarkSalmon)
  ws.Cell("A6").SetValue("b").Style.Fill.SetBackgroundColor(XLColor.DodgerBlue)
  ws.Cell("A7").SetValue("B").Style.Fill.SetBackgroundColor(XLColor.IndianRed)
  ws.Cell("A8").SetValue("c").Style.Fill.SetBackgroundColor(XLColor.DeepPink)
End Sub

'サンプルデータ挿入用
Private Sub AddTestTable(ws As IXLWorksheet)
  ws.Cell("A1").SetValue("B").Style.Fill.SetBackgroundColor(XLColor.LightGreen)
  ws.Cell("A2").SetValue("A").Style.Fill.SetBackgroundColor(XLColor.DarkTurquoise)
  ws.Cell("A3").SetValue("a").Style.Fill.SetBackgroundColor(XLColor.BurlyWood)
  ws.Cell("A4").SetValue("A").Style.Fill.SetBackgroundColor(XLColor.DarkGray)
  ws.Cell("A5").SetValue("").Style.Fill.SetBackgroundColor(XLColor.DarkSalmon)
  ws.Cell("A6").SetValue("A").Style.Fill.SetBackgroundColor(XLColor.DodgerBlue)
  ws.Cell("A7").SetValue("a").Style.Fill.SetBackgroundColor(XLColor.IndianRed)
  ws.Cell("A8").SetValue("B").Style.Fill.SetBackgroundColor(XLColor.DeepPink)

  ws.Cell("B1").SetValue("").Style.Fill.SetBackgroundColor(XLColor.LightGreen)
  ws.Cell("B2").SetValue("a").Style.Fill.SetBackgroundColor(XLColor.DarkTurquoise)
  ws.Cell("B3").SetValue("B").Style.Fill.SetBackgroundColor(XLColor.BurlyWood)
  ws.Cell("B4").SetValue("A").Style.Fill.SetBackgroundColor(XLColor.DarkGray)
  ws.Cell("B5").SetValue("a").Style.Fill.SetBackgroundColor(XLColor.DarkSalmon)
  ws.Cell("B6").SetValue("A").Style.Fill.SetBackgroundColor(XLColor.DodgerBlue)
  ws.Cell("B7").SetValue("a").Style.Fill.SetBackgroundColor(XLColor.IndianRed)
  ws.Cell("B8").SetValue("a").Style.Fill.SetBackgroundColor(XLColor.DeepPink)

  ws.Cell("C1").SetValue("A").Style.Fill.SetBackgroundColor(XLColor.LightGreen)
  ws.Cell("C2").SetValue("").Style.Fill.SetBackgroundColor(XLColor.DarkTurquoise)
  ws.Cell("C3").SetValue("A").Style.Fill.SetBackgroundColor(XLColor.BurlyWood)
  ws.Cell("C4").SetValue("a").Style.Fill.SetBackgroundColor(XLColor.DarkGray)
  ws.Cell("C5").SetValue("A").Style.Fill.SetBackgroundColor(XLColor.DarkSalmon)
  ws.Cell("C6").SetValue("b").Style.Fill.SetBackgroundColor(XLColor.DodgerBlue)
  ws.Cell("C7").SetValue("A").Style.Fill.SetBackgroundColor(XLColor.IndianRed)
  ws.Cell("C8").SetValue("").Style.Fill.SetBackgroundColor(XLColor.DeepPink)
End Sub

'矢印を挿入
Private Sub InsertArrow(range As IXLRange, Optional arrowText As String = "→")

range.Merge.SetValue(arrowText).Style.Alignment _
    .SetVertical(XLAlignmentVerticalValues.Center).Alignment _
    .SetHorizontal(XLAlignmentHorizontalValues.Center) _
    .Font.SetFontSize(25).Font.SetBold(True) _
    .Font.SetFontColor(XLColor.Red)

End Sub

 

注意

元記事のサンプルデータは、Blank（空白、空欄）を表すためにスペース文字 " " を使用しています。

ところが元記事のサンプルデータをそのまま使用すると、ソートしても、元記事と同じソート結果にはなりませんでした。

何度か試してみた結果、ClosedXML の Sort メソッドのオプションである "IgnoreBlanks As Boolean" の "Blanks" はスペース文字のことではなく、空文字または Nothing のことだと思われます。

そのため、この記事でのサンプルデータでは、元記事でスペース文字 " " だったところを空文字 "" にしています。

by エレン・イースト（当サイトの管理人）

範囲の単純ソート

範囲をシンプルにソートします。

Dim wb = New XLWorkbook
Dim ws = wb.Worksheets.Add("範囲をシンプルにソート")

'サンプルデータを用意
Call AddTestTable(ws)

'用意したサンプルデータの右側にデータをコピー
Dim rangeSimple = ws.RangeUsed
Dim copySimple = rangeSimple.CopyTo(ws.Column(ws.LastColumnUsed.ColumnNumber + 2))



'コピーしたデータをソート
copySimple.Sort



'「→」を挿入
Call InsertArrow(ws.Range("D1:D8"))

'説明文を挿入
ws.Row(1).InsertRowsAbove(2)
ws.Cell(1, 1).SetValue(".Sort = 範囲を上から下へ昇順ソート、英字大小同一視、空白を無視").Style.Font.SetBold
'A1 が選択されてると説明文が読みにくいので別のところを選択
ws.LastCellUsed.CellRight.CellBelow.Select 

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

１つの列のソート

Dim wb As New XLWorkbook
Dim ws = wb.Worksheets.Add("１つの列をソート")

'サンプルデータを用意
Call AddTestColumn(ws)

'用意したサンプルデータの右側にデータをコピー
Dim rangeSimpleColumn = ws.RangeUsed
Dim copySimpleColumn = rangeSimpleColumn.CopyTo(ws.Column(ws.LastColumnUsed.ColumnNumber + 2))



'コピーしたデータをソート
copySimpleColumn.Sort(1, XLSortOrder.Descending, True)



'「→」を挿入
Call InsertArrow(ws.Range("B1:B8"))

'説明文を挿入
ws.Row(1).InsertRowsAbove(2)
ws.Cell(1, 1).SetValue(".Sort(1, XLSortOrder.Descending, True) = 範囲を上から下へ降順ソート、英字大小を区別、空白を無視").Style.Font.SetBold
'A1 が選択されてると説明文が読みにくいので別のところを選択
ws.LastCellUsed.CellRight.CellBelow.Select

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

複雑なソート（１）

Dim wb = New XLWorkbook
Dim ws = wb.Worksheets.Add("複雑なソート（１）")

'サンプルデータを用意
Call AddTestTable(ws)

'用意したサンプルデータの右側にデータをコピー
Dim rangeComplex1 = ws.RangeUsed
Dim copyComplex1 = rangeComplex1.CopyTo(ws.Column(ws.LastColumnUsed.ColumnNumber + 2))



'コピーしたデータをソート
copyComplex1.Sort(columnsToSortBy:="2, 1 DESC", matchCase:=True)
'元記事通り copyComplex1.Sort("2, 1 DESC", True) とすると
'オーバーロードの解決に失敗しエラーとなる。名前付き引数を使えば問題ない。
'by エレン・イースト（当サイトの管理人）



'「→」を挿入
Call InsertArrow(ws.Range("D1:D8"))

'説明文を挿入
ws.Row(1).InsertRowsAbove(2)
ws.Cell(1, 1).SetValue(".Sort = 範囲を上から下へ列２を昇順ソート、列１を降順ソート、英字大小を区別、空白を無視").Style.Font.SetBold
'A1 が選択されてると説明文が読みにくいので別のところを選択
ws.LastCellUsed.CellRight.CellBelow.Select 

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

複雑なソート（２）

Dim wb = New XLWorkbook
Dim ws = wb.Worksheets.Add("複雑なソート（２）")

'サンプルデータを用意
Call AddTestTable(ws)

'用意したサンプルデータの右側にデータをコピー
Dim rangeComplex2 = ws.RangeUsed
Dim copyComplex2 = rangeComplex2.CopyTo(ws.Column(ws.LastColumnUsed.ColumnNumber + 2))



'コピーしたデータをソート
copyComplex2.SortColumns.Add(1, XLSortOrder.Ascending, False, True)
copyComplex2.SortColumns.Add(3, XLSortOrder.Descending)
copyComplex2.Sort
'.Sort メソッドと .SortColumns メソッドでは matchCase, ignoreBlanks オプションの順番が異なるので注意。
'紛らわしいときは名前付き引数（matchCase:=True など）を使うと良いかも。
'by エレン・イースト（当サイトの管理人）



'「→」を挿入
Call InsertArrow(ws.Range("D1:D8"))

'説明文を挿入
ws.Row(1).InsertRowsAbove(4)
ws.Cell(1, 1).SetValue(".SortColumns.Add(1, XLSortOrder.Ascending, False, True) = 列１を昇順ソート、空白を無視しない、英字大小を区別").Style.Font.SetBold
ws.Cell(2, 1).SetValue(".SortColumns.Add(3, XLSortOrder.Descending) = 列３を降順ソート、空白を無視、英字大小を無視").Style.Font.SetBold
ws.Cell(3, 1).SetValue(".Sort = SortColumns に設定されたパラメータを使用してソート").Style.Font.SetBold
'A1 が選択されてると説明文が読みにくいので別のところを選択
ws.LastCellUsed.CellRight.CellBelow.Select 

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

左から右にソート

Dim wb = New XLWorkbook
Dim ws = wb.Worksheets.Add("左から右にソート")

'サンプルデータを用意
Call AddTestTable(ws)

'サンプルデータを回転
ws.RangeUsed().Transpose(XLTransposeOptions.MoveCells)

'用意したサンプルデータの下にデータをコピー
Dim rangeLeftToRight = ws.RangeUsed
Dim copyLeftToRight = rangeLeftToRight.CopyTo(ws.Row(ws.LastRowUsed.RowNumber + 2))



'コピーしたデータをソート
copyLeftToRight.SortLeftToRight



'「↓」を挿入
Call InsertArrow(ws.Range("D4:D4"), "↓")

'説明文を挿入
ws.Row(1).InsertRowsAbove(2)
ws.Cell(1, 1).SetValue(".SortLeftToRight = 範囲を左から右にソート、昇順、空白を無視、英字大小を無視").Style.Font.SetBold
'A1 が選択されてると説明文が読みにくいので別のところを選択
ws.LastCellUsed.CellRight.CellBelow.Select 

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

テーブルのソート

Dim wb = New XLWorkbook
Dim ws = wb.Worksheets.Add("テーブルのソート")

'サンプルデータを用意
Call AddTestTable(ws)

'サンプルデータの上に列タイトルを挿入
Dim header = ws.Row(1).InsertRowsAbove(1).First
For co As Int32 = 1 To ws.LastColumnUsed.ColumnNumber
    header.Cell(co).Value = "列" & co.ToString
Next

'用意したサンプルデータの右側にデータをコピー
Dim rangeTable = ws.RangeUsed
Dim table = rangeTable.CopyTo(ws.Column(ws.LastColumnUsed.ColumnNumber + 2)) _
    .CreateTable 'そしてテーブルを作成



'テーブルをソート
table.Sort("列2, 列3 Desc, 列1 ASC")



'「→」を挿入
Call InsertArrow(ws.Range("D2:D9"))

'説明文を挿入
ws.Row(1).InsertRowsAbove(2)
ws.Cell(1, 1).SetValue(".Sort(""列2, 列3 Desc, 列1 ASC"") = テーブルを上から下へソート、列2 昇順、列3 降順、列1 昇順、空白を無視、英字大小を無視").Style.Font.SetBold
'A1 が選択されてると説明文が読みにくいので別のところを選択
ws.LastCellUsed.CellRight.CellBelow.Select 

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

 

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

購読する
通知を受け取る対象
guest
0 Comments
Inline Feedbacks
View all comments

前の記事

【VB.NET】ClosedXML：データの検索と抽出

次の記事

【VB.NET】ClosedXML：セルの書式