Auto-filters and custom filters

xlsgen > overview > Auto-filters and custom filters

Creating auto-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

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.

Java code


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

VB code


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

C# code


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

C++ code


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.

Invoking custom filters in Excel

Editing custom filters in Excel

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 :

operator	description
=	equal
<>	not equal
>	greater than
>=	greater than or equal
<	less than
<=	less than or equal

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 :

operator	description	example
=	contains	= "ED"
<>	does not contain	<> "ED"
=	begins with	= "ED*"
<>	does not begin with	<> "ED*"
=	ends with	= "*ED"
<>	does not end with	<> "*ED"

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.

Java code


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");

VB code


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

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"

C# code


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

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";

C++ code


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

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";