Interface IAutoFilterCondition

Represents the autofilter conditions for CustomFilter.

Namespace: Syncfusion.XlsIO
Assembly: Syncfusion.XlsIO.UWP.dll
Syntax
public interface IAutoFilterCondition

Properties

Boolean

Boolean value. Read-only. (Used internally)

Declaration
bool Boolean { get; }
Property Value
Type Description
System.Boolean

ConditionOperator

Gets or sets Comparison operator.

Declaration
ExcelFilterCondition ConditionOperator { get; set; }
Property Value
Type Description
ExcelFilterCondition
Examples

The following code illustrates how to set the operator for filter condition.

        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:C8"].CellStyleName = "CurrencyFormat";

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

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

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

            //Set Is AND or OR
            filter.IsAnd = true;

            //Add filter
            filter.FirstCondition.ConditionOperator = ExcelFilterCondition.DoesNotBeginWith;
            filter.FirstCondition.DataType = ExcelFilterDataType.String;
            filter.FirstCondition.String = "A";

            filter.SecondCondition.ConditionOperator = ExcelFilterCondition.EndsWith;
            filter.SecondCondition.DataType = ExcelFilterDataType.String;
            filter.SecondCondition.String = "n";

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

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

DataType

Gets or sets Data type.

Declaration
ExcelFilterDataType DataType { get; set; }
Property Value
Type Description
ExcelFilterDataType
Examples

The following code illustrates how to set datatype for autofilter condition.

        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:C8"].CellStyleName = "CurrencyFormat";

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

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

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

            //Set Is AND or OR
            filter.IsAnd = true;

            //Add filter
            filter.FirstCondition.ConditionOperator = ExcelFilterCondition.DoesNotBeginWith;
            filter.FirstCondition.DataType = ExcelFilterDataType.String;
            filter.FirstCondition.String = "A";

            filter.SecondCondition.ConditionOperator = ExcelFilterCondition.EndsWith;
            filter.SecondCondition.DataType = ExcelFilterDataType.String;
            filter.SecondCondition.String = "n";

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

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

Double

Gets or sets Floating-point value.

Declaration
double Double { get; set; }
Property Value
Type Description
System.Double
Examples

The CustomFilter can be used to filter String as well as Double. Here for example, we set the CustomFilter to filter numeric data in cells, so we set FloatingPoint to DataType and set FirstCondition and SecondCondition's Double value.

        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:C8"].CellStyleName = "CurrencyFormat";

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

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

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

            //Set Is AND or OR
            filter.IsAnd = true;

            //Add filter
            filter.FirstCondition.ConditionOperator = ExcelFilterCondition.Greater;
            filter.FirstCondition.DataType = ExcelFilterDataType.FloatingPoint;
            filter.FirstCondition.Double = 400;

            filter.SecondCondition.ConditionOperator = ExcelFilterCondition.Less;
            filter.SecondCondition.DataType = ExcelFilterDataType.FloatingPoint;
            filter.SecondCondition.Double = 3000;

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

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

ErrorCode

Error code. Read-only. (Used internally)

Declaration
byte ErrorCode { get; }
Property Value
Type Description
System.Byte

String

Gets or sets String value.

Declaration
string String { get; set; }
Property Value
Type Description
System.String
Examples

The CustomFilter can be used to filter String as well as Double. Here for example, we set the CustomFilter to filter texts in cells which DoesNotBeginWith "A" and EndsWith "n". For this we have to set String to DataType and set strings to String property.

        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:C8"].CellStyleName = "CurrencyFormat";

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

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

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

            //Set Is AND or OR
            filter.IsAnd = true;

            //Add filter
            filter.FirstCondition.ConditionOperator = ExcelFilterCondition.DoesNotBeginWith;
            filter.FirstCondition.DataType = ExcelFilterDataType.String;
            filter.FirstCondition.String = "A";

            filter.SecondCondition.ConditionOperator = ExcelFilterCondition.EndsWith;
            filter.SecondCondition.DataType = ExcelFilterDataType.String;
            filter.SecondCondition.String = "n";

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

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

Extension Methods