Sorting and Filtering in Windows Forms Spreadsheet

14 Aug 20262 minutes to read

This section explains the features of sorting and filtering in the Spreadsheet.

Enable Filtering

By default, the Spreadsheet does not allow filtering support, so if you want to enable filtering in Spreadsheet, set the AllowFiltering property to be true.

C#

spreadsheet.AllowFiltering = true;

NOTE

Refer to the Filtering Demo for your reference.

Programmatic Sorting and Filtering

Sorting

Sort the data programmatically when importing the workbook in the WorkbookLoaded event of Spreadsheet.

C#

spreadsheet.AllowFiltering = true;
spreadsheet.WorkbookLoaded += spreadsheet_WorkbookLoaded;

void spreadsheet_WorkbookLoaded(object sender, WorkbookLoadedEventArgs args)
{
    IRange filterRange = spreadsheet.Workbook.ActiveSheet.Range["A1:D9"];
    spreadsheet.Workbook.ActiveSheet.AutoFilters.FilterRange = filterRange;
    IDataSort sorter = spreadsheet.Workbook.CreateDataSorter();
    sorter.SortRange = spreadsheet.ActiveSheet.Range["A1:D9"];
    ISortField sortField = sorter.SortFields.Add(1, SortOn.Values, OrderBy.Ascending);
    sorter.Sort();
}

Filtering

Filter the data programmatically when importing the workbook in the WorkbookLoaded event of Spreadsheet.

C#

spreadsheet.AllowFiltering = true;
spreadsheet.WorkbookLoaded += spreadsheet_WorkbookLoaded;

void spreadsheet_WorkbookLoaded(object sender, WorkbookLoadedEventArgs args)
{
    IRange filterRange = spreadsheet.Workbook.ActiveSheet.Range["A1:D9"];
    spreadsheet.Workbook.ActiveSheet.AutoFilters.FilterRange = filterRange;
    IAutoFilter filter = spreadsheet.Workbook.ActiveSheet.AutoFilters[0];
    filter.AddTextFilter("1");
}

For more details, please go through the documentation of the XlsIO UG.

NOTE

If you have programmatically applied the sorting or filtering at runtime, refresh the view to change it in SpreadsheetGrid. But even after refreshing the view, these changes won’t be updated in Filter popup.

Unsupported Features

Spreadsheet currently does not support the following features.

• Advanced filtering
• Table filtering
• Multi-column sorting

Limitations

Sorting

• Microsoft Excel varies the sort label in the filter popup based on the column’s value type (for example, “Sort Smallest to Largest” for numerical values, “Sort A to Z” for string values, etc.). However, the Spreadsheet does not vary the sort label based on values, in order to improve filter popup loading efficiency.
• If a column is sorted in Microsoft Excel, the “Sort Ascending” or “Sort Descending” option is not checked in the filter popup, because XlsIO does not currently expose the sorted order when importing a workbook.

Filtering

• When a filter is applied in Microsoft Excel, clearing the filter on any column in the Spreadsheet also clears filters on all other columns.