ContextQMD
Back to Closedxml

Sorting

docs/features/sort.rst

0.105.05.6 KB
Original Source

Sorting

It's possible to sort a range in a worksheet.

.. image:: img/sort-ribbon.png :alt: A button in Excel ribbon menu to sort selected value along with a more detailed dialogue.

Sorting algorithm

The values are sorted same way as Excel, first by type, then by value.

  • Logical - false is less than true.

  • Number - numbers are sorted according to their value.

  • Text - text is sorted by current culture. Note that empty string is generally smallest possible text and thus is first element (blank is different from empty string).

  • Error - individual error values never change relative order during sorting.

  • Blank - blanks are always last, regardless of sorting order.

    • Ascending order is number, text, logical, error and blank.
    • Descending order is error, logical, text, number and blank.

Sorting is stable.

.. note:: Sorting can only be done on cell values, not cell or font colors.

Sorting methods may contain two flags that modify how are values sorted.

The matchCase flag

The flag determines if text values are sorted in a case-sensitive (using current culture) manner:

  • false (default value) - text values are sorted in a case insensitive way, Hello is treated same as hello or HELLO.
  • true - text values are treated in a case sensitive way. hello is different from HELLO. Note that whether lower-case letters are sorted before upper-case ones depends on the culture.

The ignoreBlanks flag

This flag determines how should blank values be treated during sorting:

  • true (default value) - blanks are always last in any sorting order.
  • false - blanks are treated as empty string and are thus grouped with text values. The empty text is (unless culture is insane) always smaller than any other text and is thus sorted before/after any other text in ascending/descending order.

.. note:: The simplest way to enter empty string into an Excel is to write apostrophe (') into a cell and press enter. The TYPE function will then return 2 for the cell, indicating text type. The apostrophe is displayed because of a style (IXLStyle.IncludeQuotePrefix), it's not actual value. To get rid of apostrophe, you can copy value of a cell into another one.

Sorting rows

In order to sort rows, select a range to be sorted and call one of the IXLRange.Sort() methods. There are three of them, later two are only more specialized version of first one:

  • IXLRange.Sort(string columnsToSortBy, XLSortOrder sortOrder, Boolean matchCase = false, Boolean ignoreBlanks = true)
  • IXLRange Sort(Int32 columnToSortBy, XLSortOrder sortOrder, Boolean matchCase = false, Boolean ignoreBlanks = true)
  • IXLRange.Sort() may use IXLRange.SortColumns property instead of columnsToSortBy parameter. The IXLRange.SortColumns defines sort state of the range. The general idea is to set IXLRange.SortColumns parameters and then call IXLRange.Sort().

The columnsToSortBy parameter can be used to select columns and sorting order of them, e.g. 2 ASC, 3 DESC will sort second column of a range in ascending order and then by values of third column in descending order.

  • Columns are separated by comma (,).
  • Column can be specified either by number (e.g. 5) or by column letter (e.g. E). Columns are specified relative to the range (e.g. C for range B2:E7 means column D in absolute coordinates).
  • Sort order can be ASC or DESC or nothing. If none is specified for a column, the sortOrder parameter is used.

Sorting rows example

.. code-block:: csharp

using var wb = new XLWorkbook(); var ws = wb.AddWorksheet(); ws.Column("D").Style.NumberFormat.SetNumberFormatId((int)XLPredefinedFormat.Number.PercentInteger); var range = ws.Cell("B3").InsertData(new[] { new object[]{ "Pastry", "Sold", "Margin" }, new object[]{ "Cake", 14, 0.25 }, new object[]{ "Croissant", 15, 0.60 }, new object[]{ "Fig Roll", 56, 0.50 }, new object[]{ "Waffle", 74, 0.60 }, }); range.CopyTo(ws.Cell("F3")); var dataRangeOfCopy = ws.Range("F4:H7");

dataRangeOfCopy.Sort("3 DESC, 2 DESC"); wb.SaveAs("sort-rows-example.xlsx");

.. image:: img/sort-rows-example1.png :alt: An output of the sample.

Sorting columns

Sometimes data we want to sort is stored is columns, not rows. The method to use is IXLRange.SortLeftToRight. It's significantly more limited than row sorting, it can't set columns to sort by, nor their order. The sorting uses data from first row, then second, then third and so on. It's mostly useful for rows (each row is of course range).

Sorting columns example

.. code-block:: csharp

using var wb = new XLWorkbook(); var ws = wb.AddWorksheet(); ws.Row(5).Style.NumberFormat.SetNumberFormatId((int)XLPredefinedFormat.Number.PercentInteger); var range = ws.Cell("B3").InsertData(new[] { new object[]{ "Pastry", "Waffle", "Fig Roll", "Cake", "Croissant", }, new object[]{ "Sold", 74, 56, 14, 15, }, new object[]{ "Margin", 0.60, 0.50, 0.25, 0.60, }, }); range.CopyTo(ws.Cell("H3")); var dataRangeOfCopy = ws.Range("I3:L5");

dataRangeOfCopy.SortRows.Add(3, XLSortOrder.Descending); dataRangeOfCopy.SortRows.Add(2, XLSortOrder.Descending); dataRangeOfCopy.SortLeftToRight(); wb.SaveAs("sort-cols-example.xlsx");

.. image:: img/sort-cols-example1.png :alt: An output of the sample.