Sorting and Filtering Excel Ranges with ExpertXls |
The rows of a range can be sorted by one or more columns, and a range can get an AutoFilter: the drop-down buttons in the header row that Excel uses to show only the rows that meet the filters of the columns. Both work in the Excel 97-2003 (.xls) and Excel 2007 or later (.xlsx) formats.
Sort(Int32, ExcelSortOrder, Boolean) sorts by a column; the overloads with an array of ExcelSortKey sort by several columns, in order of priority. The column indexes are one based and relative to the range. When the first row of the range is a header row, it stays in place.
The sort is stable and follows the Excel order: numbers and dates first, then texts compared without regard to case (unless requested), then FALSE and TRUE, then errors; blank cells are placed last in both orders. The values, formulas and cell formats move with the rows and the relative references of the moved formulas are adjusted to the new rows, as in Excel. Formula cells are sorted by their calculated results: call UpdateFormulaResult first for the formulas added in code. A range with merged cells cannot be sorted.
// A6:G18 has a header row; sort by Region (column 2) A to Z, then by Total (column 7) from the largest workbook.UpdateFormulaResult(); worksheet["A6:G18"].Sort(new ExcelSortKey[] { new ExcelSortKey(2, ExcelSortOrder.Ascending), new ExcelSortKey(7, ExcelSortOrder.Descending) }, true); // sort a range without a header row by its first column worksheet["J1:K20"].Sort(1, ExcelSortOrder.Ascending, false);
AddAutoFilter adds the AutoFilter to a range whose first row is the header row and returns an ExcelAutoFilter. A worksheet has one AutoFilter: AutoFilter returns it (or null) and RemoveAutoFilter removes it.
A column is filtered by a list of values or by one or two conditions (ExcelAutoFilterOperator): comparisons with a number or a text, begins with, ends with, contains. When a filter is set the library hides the rows that do not meet the filters of all the columns, as Excel does, and Excel keeps the filters so the user can change them. ApplyFilters applies the filters again after the values were changed.
ExcelAutoFilter autoFilter = worksheet["A6:G18"].AddAutoFilter(); // column 2 (Region): North or South autoFilter.FilterByValues(2, new string[] { "North", "South" }); // column 7 (Total): at least 2500 autoFilter.FilterByCondition(7, ExcelAutoFilterOperator.GreaterThanOrEqualTo, 2500); // column 1 (Product): begins with "PDF" and does not contain "Split" autoFilter.FilterByConditions(1, ExcelAutoFilterOperator.BeginsWith, "PDF", true, ExcelAutoFilterOperator.DoesNotContain, "Split"); // remove the filter of a column, or all the filters autoFilter.ClearFilter(1);
In the Excel 97-2003 format a column can be filtered by at most two values or two conditions.