Column filter and sort

Create column filter

Use the method CreateColumnFilter to create a column filter on specified columns:

// put data array on spreadsheet
worksheet["C1:C5"] = new object[] { "A", "B", "C", "D", "E" };

// create built-in column filter
var filter = worksheet.CreateColumnFilter("C", 0);

The second argument specifies that how many title rows exist on the spreadsheet, in this example there is no title rows exist so pass zero (the default value is 1).

By default, a column filter will add drop-down buttons on every specified columns:
143
Click on the drop-down button, a popup-menu with the candidates list will be displayed:
144
To create filter without GUI, pass forth argument AutoColumnFilterUI as NoGUI:

var filter = worksheet.CreateColumnFilter("C", "C", 0, AutoColumnFilterUI.NoGUI);

Do filter by programming

To change items what can be selected and displayed on spreadsheet, add or remove the items as text into filter on specified columns:

// select items
 filter.Columns["C"].SelectedTextItems.AddRange(new string[] { "A", "C", "D" });

Then apply filter:

// do filter
 filter.Apply();

Result:
145

Create filter in ReoGrid Editor

Preparing the spreadsheet data:

worksheet["C1:C6"] = new object[] { "Title", "A", "B", "C", "D", "E" };

Since the first row is the title row, it shouldn’t be included into the filter and sort range, select the second row and choose menu ‘Sheet’ -> ‘Filter’ to create the filter.
Select a range to add filter, the title rows will not be included into filter and sort range:
148
Select the menu ‘Sort Z-A’:
149
The title row will not be changed:
151

Sort by programming

Use method SortColumn to sort data on specified column:

// sort on column "C"
 worksheet.SortColumn("C", false);

Pass second argument as true to perform ascending sort, false to perform descending sort.

Sort column and apply to specified range

It is possible to sort data on a column but move other data only inside a specified range:

// put data on spreadsheet
worksheet["A1"] = new object[,] {
  { "1", "A", "D" },
  { "2", "B", "E" },
  { "3", "C", "F" },
  { "4", "D", "G" },
  { "5", "E", "H" },
};

Sort on column “B”, but only move the column A and B, the column C will not be changed.

worksheet.SortColumn(1, new RangePosition("A1:B5"), false);

Result is:
152


Next: Events

7 Responses to “Column filter and sort”

  1. arlodvig says:

    a beginner question:
    how to put filters on all columns?

    • Jing says:

      sheet.CreateColumnFilter(0, sheet.ColumnCount - 1, 1, unvell.ReoGrid.Data.AutoColumnFilterUI.DropdownButtonAndPanel);

  2. arlodvig says:

    I want to color the rowheader that is filtered. Is there an event when a filter is created?

  3. Koen says:

    Is there any way to create a filter with the UI for the user, but to hide the button to open it and instead open the window when the header is clicked? Or perhaps to show the sort button when the mouse hovers over the column? I use column header texts, and the button overlaps with the headers.

    • Jing says:

      You can create a filter without GUI by
      var filter = worksheet.CreateColumnFilter("C", "C", 0, AutoColumnFilterUI.NoGUI);

      And create your own header body to implement the UI behaivors (like show button when hover)
      sheet.ColumnHeaders["A"].Body = myHeaderBody
      you have to create a class myHeaderBody inherit from HeaderBody, that works like CellBody.
      https://reogrid.net/document/custom-cell/

  4. zzy says:

    the sort button seems to delete all cell data after click, is there any way to disable the sort button?