xlsgen > overview > Auto-filters and custom filters

Auto-filters provide users an interactive way to put constraints on how data is displayed.

Using xlsgen, you can programmatically :

• add range-based auto-filters to any worksheet, so that users will see visual controls (materialized by drop down in the range headers) next time they open the document in Excel.
• add custom filter expressions.
• add predefined filters such as Top values, Bottom values, Above average, Below average, Match blanks and non-blanks, values, styles and non-styles.
• apply and re-apply auto-filters.
• delete auto-filters
• sort filter columns

Due to historical Excel limitations, the following applies :

• you can only add one auto-filter group per worksheet.
• custom expressions can have only up to two conditions.
• Excel does not automatically update filters after changing the data in cells. xlsgen manages this by including a filter engine.

Creating auto-filters

xlsgen supports the creation of auto-filters, through the IXlsRange object interface. It returns a IXlsAutoFilter object.

XlsRange range = wksht3.NewRange("R1C1:R5C3");
range.NewAutoFilter();

Dim range As IXlsRange
Set range = wksht3.NewRange("R1C1:R5C3")
range.NewAutoFilter

IXlsRange range = wksht3.NewRange("R1C1:R5C3");
range.NewAutoFilter();

xlsgen::IXlsRangePtr range = wksht3->NewRange(L"R1C1:R5C3");
range->NewAutoFilter();

Creating custom filters

You can add custom filters to one or more columns of an auto-filter group. When more than one column have custom filters, the filter engine combines them by cascading results. A filter in a column is accessed by an index : for instance the left column has index 1, and the right-most column of a 4-column auto-filter is 4.

Operands in custom expressions are strings, surrounded by double-quotes, and numbers, integer and floating-point.

Custom expressions support the following operators for both strings and numbers :

With string operands, it is possible to emulate 6 other operators thanks to the ? and * wildcards. ? replaces any character. * replaces one or more characters. The additional string operators are :

A custom expression can use the AND or OR logical operator to combine two conditions. Due to a historical Excel limitation, it is not possible to specify more than two conditions in the same custom expression.

Here are examples :

• < -30
• <= 30.2
• = "*ED*"
• = *ED* invalid expression, missing double-quotes
• > 30 AND < 50
• < 30 OR > 50
• < 30 OR > 50 AND <> 40 invalid expression, three conditions
• <> "2006"

When setting a custom expression, xlsgen does not only store the custom expressions in the Excel file, a filter engine computes the resulting layout based on these.

XlsRange range = wksht3.NewRange("R1C1:R5C3");

XlsAutoFilter af = range.NewAutoFilter();

// filter 2nd column : keep all rows where name contains a A
af.getCustomFilter(2).putCustomExpression("= \"*A*\"");

// combine with filter on 3rd column : keep all rows where sales are less than 50
af.getCustomFilter(3).putCustomExpression("< 50");

Dim range As IXlsRange
Set range = wksht3.NewRange("R1C1:R5C3")

Dim af As IXlsAutoFilter 
Set af = range.NewAutoFilter

' filter 2nd column : keep all rows where name contains a A
af.CustomFilter(2).CustomExpression = "= " & Chr(34) & "*A*" & Chr(34) & ""

' combine with filter on 3rd column : keep all rows where sales are less than 50
af.CustomFilter(3).CustomExpression = "< 50"

IXlsRange range = wksht3.NewRange("R1C1:R5C3");

IXlsAutoFilter af = range.NewAutoFilter();

// filter 2nd column : keep all rows where name contains a A
af.get_CustomFilter(2).CustomExpression = "= \"*A*\"";

// combine with filter on 3rd column : keep all rows where sales are less than 50
af.get_CustomFilter(3).CustomExpression = "< 50";

xlsgen::IXlsRangePtr range = wksht3->NewRange(L"R1C1:R5C3");

xlsgen::IXlsAutoFilterPtr af = range->NewAutoFilter();

// filter 2nd column : keep all rows where name contains a A
af->CustomFilter[2]->CustomExpression = L"= \"*A*\"";

// combine with filter on 3rd column : keep all rows where sales are less than 50
af->CustomFilter[3]->CustomExpression = L"< 50";

Apply filters

Whenever a custom filter is added, xlsgen applies the filters, which results in hiding rows according to filters. But the client application may ask for filters to be applied at any time, for instance after a cell value change. That is possible thanks to xlsgen's built-in filter engine (a micro-calculation engine).

xlsgen::IXlsWorksheetPtr wksht = workbook->WorksheetByIndex[1];
wksht->AutoFilters->Apply();

Predefined filters

Alternatively to custom expressions, it is possible to use one of the following predefined filters :

• Top n values
• Bottom n values
• Top p% values, where p is expressed in percent (eg. Top 30% values)
• Bottom p% values
• Above average (all values above average, where the average is automatically computed)
• Below average
• Match all blank values
• Match all non-blank values
• Match a list of values
• Match a style (such as the background color)
• Match all but a style (all cells not having that style)

xlsgen::IXlsRangePtr range1 = wksht1->NewRange(L"C2:C13");
xlsgen::IXlsAutoFilterPtr af1 = range1->NewAutoFilter();

// filter 2nd column : four top values
af1->CustomFilter[2]->TopValues(4);

...


xlsgen::IXlsStylePtr style = wksht2->NewStyle();
style->Pattern->Pattern = xlsgen::pattern_solid;
style->Pattern->BackgroundColor = xlsgen::colorYellow;

xlsgen::IXlsRangePtr range2 = wksht2->NewRange(L"C2:C13");
xlsgen::IXlsAutoFilterPtr af2 = range2->NewAutoFilter();

// filter 1st column : match all cells having a yellow background color
af2->CustomFilter[1]->MatchStyle(style);

Note : xlsgen's built-in filter engine applies the filtering by style (MatchStyle) to both XLS and XLSX spreadsheets, but when it comes to writing to files, the filter definition itself is only saved in XLSX spreadsheets in which the file format has such a mechanism available. Yet, a client application can take full advantage of the xlsgen's built-in filter engine, combined with the ability to apply and re-apply filters.

Deleting auto-filters

A simple method call erases auto-filters in the current worksheet :

xlsgen::IXlsWorksheetPtr wksht = workbook->WorksheetByIndex[1];
wksht->AutoFilters->Delete();