Interface IAutoFilterCondition

Represents the autofilter conditions for CustomFilter.

Namespace: Syncfusion.XlsIO

Assembly: Syncfusion.XlsIO.NET.dll

Syntax

public interface IAutoFilterCondition

Properties

Boolean

Boolean value. Read-only. (Used internally)

Declaration

bool Boolean { get; }

Property Value

Type
System.Boolean

ConditionOperator

Gets or sets Comparison operator.

Declaration

ExcelFilterCondition ConditionOperator { get; set; }

Property Value

Type
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
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
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
System.Byte

String

Gets or sets String value.

Declaration

string String { get; set; }

Property Value

Type
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();
        }