Interface IAutoFilters

Represents a collection of Autofilters.

Inherited Members
Namespace: Syncfusion.XlsIO
Assembly: Syncfusion.XlsIO.UWP.dll
Syntax
public interface IAutoFilters : IParentApplication

Properties

Count

Number of columns to be filtered. Read-only.

Declaration
int Count { get; }
Property Value
Type
System.Int32
Examples

Count property gets the number of columns present within the range set to FilterRange. Here for example, We set range "A1:C7" to FilterRange property. Since there are only three columns within the range, Count property returns three.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
            //Create worksheet
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet worksheet = workbook.Worksheets[0];

            //Load data
            worksheet["A1"].Text = "Products";
            worksheet["A2"].Text = "Alfreds Futterkiste";
            worksheet["A3"].Text = "Antonio Moreno Taqueria";
            worksheet["A4"].Text = "Around the Horn";
            worksheet["A5"].Text = "Bon app";
            worksheet["A6"].Text = "Eastern Connection";
            worksheet["A7"].Text = "Ernst Handel";
            worksheet["B1"].Text = "Qtr1";
            worksheet["B2"].Number = 744.6;
            worksheet["B3"].Number = 5079.6;
            worksheet["B4"].Number = 1267.5;
            worksheet["B5"].Number = 1418;
            worksheet["B6"].Number = 4728;
            worksheet["B7"].Number = 943.89;
            worksheet["C1"].Text = "Qtr2";
            worksheet["C2"].Number = 162.56;
            worksheet["C3"].Number = 1249.2;
            worksheet["C4"].Number = 1062.5;
            worksheet["C5"].Number = 756;
            worksheet["C6"].Number = 4547.92;
            worksheet["C7"].Number = 349.6;

            //Create style for number format
            IStyle style1 = workbook.Styles.Add("CurrencyFormat");
            style1.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* \" - \"??_);_(@_)";

            //Apply number format
            worksheet["B2:C7"].CellStyleName = "CurrencyFormat";

            //Create filters collection
            IAutoFilters filters = worksheet.AutoFilters;

            //Set filter range
            filters.FilterRange = worksheet["A1:C7"];

            //Set filter column
            IAutoFilter filter = filters[0];

            //Add filter
            filter.AddTextFilter("Around the Horn");

            //Check the filter count
            Console.WriteLine(filters.Count);

            //Apply AutoFit
            worksheet.UsedRange.AutofitColumns();

            //Save and Dispose
            workbook.SaveAs("Filters.xlsx");
            workbook.Close();
            Console.ReadKey();
        }
//Output will be
//3

DataSorter

Gets the DataSorter.

Declaration
IDataSort DataSorter { get; }
Property Value
Type Description
IDataSort

The DataSorter.
Examples

The following code illustrates how to access the DataSorter in AutoFilters.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
            //Create worksheet
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet worksheet = workbook.Worksheets[0];

            //Load data
            worksheet["A1"].Text = "Products";
            worksheet["A2"].Text = "Alfreds Futterkiste";
            worksheet["A3"].Text = "Antonio Moreno Taqueria";
            worksheet["A4"].Text = "Around the Horn";
            worksheet["A5"].Text = "Bon app";
            worksheet["A6"].Text = "Eastern Connection";
            worksheet["A7"].Text = "Ernst Handel";
            worksheet["B1"].Text = "Qtr1";
            worksheet["B2"].Number = 744.6;
            worksheet["B3"].Number = 5079.6;
            worksheet["B4"].Number = 1267.5;
            worksheet["B5"].Number = 1418;
            worksheet["B6"].Number = 4728;
            worksheet["B7"].Number = 943.89;
            worksheet["C1"].Text = "Qtr2";
            worksheet["C2"].Number = 162.56;
            worksheet["C3"].Number = 1249.2;
            worksheet["C4"].Number = 1062.5;
            worksheet["C5"].Number = 756;
            worksheet["C6"].Number = 4547.92;
            worksheet["C7"].Number = 349.6;

            //Create style for number format
            IStyle style1 = workbook.Styles.Add("CurrencyFormat");
            style1.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* \" - \"??_);_(@_)";

            //Apply number format
            worksheet["B2:C7"].CellStyleName = "CurrencyFormat";

            //Create filters collection
            IAutoFilters filters = worksheet.AutoFilters;

            //Set filter range
            filters.FilterRange = worksheet["A1:C7"];

            //Create sorter
            IDataSort sorter = filters.DataSorter;

            //Add sortfield
            ISortField sortField = sorter.SortFields.Add(1, SortOn.Values, OrderBy.Ascending);

            //Perform sort
            sorter.Sort();

            //Apply AutoFit
            worksheet.UsedRange.AutofitColumns();

            //Save and Dispose
            workbook.SaveAs("Filter.xlsx");
            workbook.Close();
        }

FilterRange

Range to be filtered.

Declaration
IRange FilterRange { get; set; }
Property Value
Type
IRange
Remarks

To know more about filters refer Data Filtering.

Examples

The following code illustrates how to set the range for filters.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
            //Create worksheet
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet worksheet = workbook.Worksheets[0];

            //Load data
            worksheet["A1"].Text = "Products";
            worksheet["A2"].Text = "Alfreds Futterkiste";
            worksheet["A3"].Text = "Antonio Moreno Taqueria";
            worksheet["A4"].Text = "Around the Horn";
            worksheet["A5"].Text = "Bon app";
            worksheet["A6"].Text = "Eastern Connection";
            worksheet["A7"].Text = "Ernst Handel";
            worksheet["B1"].Text = "Qtr1";
            worksheet["B2"].Number = 744.6;
            worksheet["B3"].Number = 5079.6;
            worksheet["B4"].Number = 1267.5;
            worksheet["B5"].Number = 1418;
            worksheet["B6"].Number = 4728;
            worksheet["B7"].Number = 943.89;
            worksheet["C1"].Text = "Qtr2";
            worksheet["C2"].Number = 162.56;
            worksheet["C3"].Number = 1249.2;
            worksheet["C4"].Number = 1062.5;
            worksheet["C5"].Number = 756;
            worksheet["C6"].Number = 4547.92;
            worksheet["C7"].Number = 349.6;

            //Create style for number format
            IStyle style1 = workbook.Styles.Add("CurrencyFormat");
            style1.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* \" - \"??_);_(@_)";

            //Apply number format
            worksheet["B2:C7"].CellStyleName = "CurrencyFormat";

            //Create filters collection
            IAutoFilters filters = worksheet.AutoFilters;

            //Set filter range
            filters.FilterRange = worksheet["A1:C7"];

            //Set filter column
            IAutoFilter filter = filters[0];

            //Add filter
            filter.AddTextFilter("Around the Horn");

            //Apply AutoFit
            worksheet.UsedRange.AutofitColumns();

            //Save and Dispose
            workbook.SaveAs("Filters.xlsx");
            workbook.Close();
        }

Item[Int32]

Returns single autofilter object by column index. Read-only.

Declaration
IAutoFilter this[int columnIndex] { get; }
Parameters
Type Name Description
System.Int32 columnIndex
Property Value
Type
IAutoFilter
Examples

The following code snippet shows how IAutoFilter can be accessed from IAutoFilters collection.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
            //Create worksheet
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet worksheet = workbook.Worksheets[0];

            //Load data
            worksheet["A1"].Text = "Products";
            worksheet["A2"].Text = "Alfreds Futterkiste";
            worksheet["A3"].Text = "Antonio Moreno Taqueria";
            worksheet["A4"].Text = "Around the Horn";
            worksheet["A5"].Text = "Bon app";
            worksheet["A6"].Text = "Eastern Connection";
            worksheet["A7"].Text = "Ernst Handel";
            worksheet["B1"].Text = "Qtr1";
            worksheet["B2"].Number = 744.6;
            worksheet["B3"].Number = 5079.6;
            worksheet["B4"].Number = 1267.5;
            worksheet["B5"].Number = 1418;
            worksheet["B6"].Number = 4728;
            worksheet["B7"].Number = 943.89;
            worksheet["C1"].Text = "Qtr2";
            worksheet["C2"].Number = 162.56;
            worksheet["C3"].Number = 1249.2;
            worksheet["C4"].Number = 1062.5;
            worksheet["C5"].Number = 756;
            worksheet["C6"].Number = 4547.92;
            worksheet["C7"].Number = 349.6;

            //Create style for table number format
            IStyle style1 = workbook.Styles.Add("CurrencyFormat");
            style1.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* \" - \"??_);_(@_)";

            //Apply number format
            worksheet["B2:C7"].CellStyleName = "CurrencyFormat";

            //Create filters collection
            IAutoFilters filters = worksheet.AutoFilters;

            //Set filter range
            filters.FilterRange = worksheet["A1:C7"];

            //Set filter column
            IAutoFilter filter = filters[0];

            //Add filter
            filter.AddTextFilter("Around the Horn");

            //Apply AutoFit
            worksheet.UsedRange.AutofitColumns();

            //Save and Dispose
            workbook.SaveAs("Filters.xlsx");
            workbook.Close();
        }

Extension Methods