Details

Auto filters are an Excel feature that can be applied to a column of data that allows users to select what rows of data they want visible for a given area on a worksheet.

The ability to apply auto filters programmatically with ExcelApplication in XLSX and XLSM files was added in OfficeWriter 8.3. This post covers how to add auto filters with the ExcelWriter API.

Solution

1. Define the Area that the auto filter will be applied to.
2. Get a handle on the AutoFilter object for the Worksheet that will have the auto filter.
3. Set the worksheet’s AutoFilter.Area to the area that was defined earlier.

Here is the code:

Area filter_area = ws.PopulatedCells; //Returns a continuous area of all the populated cells
AutoFilter filter = ws.AutoFilter;
filter.Area = filter_area;
Here is the code in a single line:
ws.AutoFilter.Area = ws.CreateArea("A1:H1");

Additional Resources

To remove the auto filter from a worksheet, use AutoFilter.Clear():
ws.AutoFilter.Clear();
A few things to bear in mind:

• A worksheet can only contain one area with auto filters. If you set Worksheet.AutoFilter.Area, you will overwrite any existing auto filters.
• You must specify the entire area that you want to apply the filters to. ExcelWriter will not automatically detect blank rows and columns. We recommend Worksheet.PopulatedCells for getting a handle on all the populated cells in a worksheet.