Sorting and Filtering in WPF Spreadsheet (SfSpreadsheet)
28 Jul 20213 minutes to read
This section explains about sorting and filtering functionalities in the SfSpreadsheet.
Filtering
By default, Filtering support will be enabled in the SfSpreadsheet, but if you want to disable the Filtering in SfSpreadsheet, set the AllowFiltering
property of the SfSpreadsheet
to be false.
<syncfusion:SfSpreadsheet x:Name="spreadsheet" AllowFiltering="False"/>
spreadsheet.AllowFiltering = false;
Programmatic Sorting and Filtering
Sorting
Programmatically sort the data while importing the workbook by using XlsIO in the WorkbookLoaded
event of SfSpreadsheet.
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
Programmatically filter the data while importing the workbook by using XlsIO in the WorkbookLoaded
event of SfSpreadsheet.
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 reference, please go through the XlsIO UG documentation.
NOTE
If sorting or filtering has been applied programmatically at runtime, then refresh the view to update it in
SpreadsheetGrid
. But this changes will not be updated in Filter popup even after refreshing the view.
Unsupported Features
Currently SfSpreadsheet does not have support for following features.
- Advanced filtering
- Table filtering
- Multi-column sorting
Limitations
Sorting
- In Microsoft Excel, sorting label should be varied in filter popup based on the type of values in a column (For e.g, “Sort Smallest to Largest” for numeric values, “Sort A to Z” for string values, etc.). But in Spreadsheet, sort label should not be varied based on values due to improve the loading performance of filter popup.
- Sort Ascending or Sort Descending label is not checked in the filter popup, if the column is sorted in Microsoft Excel. Because currently XlsIO do not have support to fetch the sorted order while importing the workbook.
Filtering
- If the filter applied in Microsoft Excel, then the filter will be cleared from all columns while clearing the filter from any one column Because unable to fetch the filtering order.
- While importing the workbook, checked and unchecked items are only displayed in the right most filtered column. Because unable to fetch the filtering order.
NOTE
You can refer to our WPF Spreadsheet feature tour page for its groundbreaking feature representations. You can also explore our WPF Spreadsheet example to know how to render and configure the spreadsheet.