Interface IAutoFilter
Represents an Autofilter in an Excel worksheet.
Namespace: Syncfusion.XlsIO
Assembly: Syncfusion.XlsIO.Portable.dll
Syntax
public interface IAutoFilterProperties
FilteredItems
Returns filter values for combination type ( Text and DateTime) and dynamic type. It returns null, if custom filter is applied. Read-only.
Declaration
IFilter FilteredItems { get; }Property Value
| Type |
|---|
| IFilter |
Examples
The following code illustrates how to access the applied filter using FilteredItems 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];
//Apply colors
worksheet["A2:A3"].CellStyle.Color = System.Drawing.Color.Red;
worksheet["A4:A5"].CellStyle.Color = System.Drawing.Color.Blue;
worksheet["A6:A7"].CellStyle.Color = System.Drawing.Color.Green;
//Create filters collection
IAutoFilters filters = worksheet.AutoFilters;
//Set range
filters.FilterRange = worksheet["A1:A7"];
//Set filter column
IAutoFilter filter = filters[0];
//Add filter
filter.AddColorFilter(System.Drawing.Color.Blue, ExcelColorFilterType.CellColor);
//Get filtered items
Console.WriteLine(filter.FilteredItems.FilterType.ToString());
//Apply AutoFit
worksheet.UsedRange.AutofitColumns();
//Save and Dispose
workbook.SaveAs("Filters.xlsx");
workbook.Close();
Console.ReadKey();
}
//Output will be
//ColorFilterFilterType
Specifies the type of filter.
Declaration
ExcelFilterType FilterType { get; set; }Property Value
| Type |
|---|
| ExcelFilterType |
Examples
The following code illustrates how to access the filter type.
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";
//Apply colors
worksheet["A2:A3"].CellStyle.Color = System.Drawing.Color.Red;
worksheet["A4:A5"].CellStyle.Color = System.Drawing.Color.Blue;
worksheet["A6:A7"].CellStyle.Color = System.Drawing.Color.Green;
//Create filters collection
IAutoFilters filters = worksheet.AutoFilters;
//Set range
filters.FilterRange = worksheet["A1:C7"];
//Set filter column
IAutoFilter filter = filters[0];
//Check filter type
Console.WriteLine(filter.FilterType.ToString());
//Add filter
filter.AddColorFilter(System.Drawing.Color.Blue, ExcelColorFilterType.CellColor);
//Check filter type
Console.WriteLine(filter.FilterType.ToString());
//Apply AutoFit
worksheet.UsedRange.AutofitColumns();
//Save and Dispose
workbook.SaveAs("Filters.xlsx");
workbook.Close();
Console.ReadKey();
}
//Output will be
//CustomFilter
//ColorFilterFirstCondition
First condition of autofilter.
Declaration
IAutoFilterCondition FirstCondition { get; }Property Value
| Type |
|---|
| IAutoFilterCondition |
Remarks
To know more about filters refer Data Filtering
Examples
Custom filters can be applied by setting FirstCondition and SecondCondition. To filter particular pattern we can make use of custom filters. Here for example, we set a custom filter to filter values those does not start with "A" but should end with "N". For this we set DoesNotBeginWith to FirstCondition's ConditionOperator and EndsWith to SecondCondition's ConditionOperator and set String to String for both coditions. Also we set "A" and "N" to String for FirstCondition and SecondCondition respectively.
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 range
filters.FilterRange = worksheet["A1:C7"];
//Set filter column
IAutoFilter filter = filters[0];
//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();
}IsAnd
True means to use AND operation between conditions, False to use OR. Read-only.
Declaration
bool IsAnd { get; set; }Property Value
| Type |
|---|
| System.Boolean |
Examples
If both the conditions given for CustomFilter should be applied without considering the other condition IsAnd should be set to "false". By default IsAnd is set to "true", so it combines both the conditions and applies the filter.
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 range
filters.FilterRange = worksheet["A1:C7"];
//Set filter column
IAutoFilter filter = filters[0];
//Set Is AND or OR
filter.IsAnd = false;
//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();
}IsFiltered
False indicates that this autofilter was not used; otherwise True. Read-only.
Declaration
bool IsFiltered { get; }Property Value
| Type |
|---|
| System.Boolean |
Examples
The following code illustrates how to access the IsFiltered 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 = "Title";
worksheet["A2"].Text = "Sales Representative";
worksheet["A3"].Text = "Sales Agent";
worksheet["A4"].Text = "Owner";
worksheet["A5"].Text = "Sales Associate";
worksheet["A6"].Text = "Sales Representative";
worksheet["A7"].Text = "Marketing Manager";
worksheet["B1"].Text = "DOJ";
worksheet["B2"].DateTime = DateTime.Parse("6/5/2011");
worksheet["B3"].DateTime = DateTime.Parse("9/5/2011");
worksheet["B4"].DateTime = DateTime.Parse("10/21/2007");
worksheet["B5"].DateTime = DateTime.Parse("9/28/2004");
worksheet["B6"].DateTime = DateTime.Parse("8/28/2007");
worksheet["B7"].DateTime = DateTime.Parse("8/12/2003");
//Create filters collection
IAutoFilters filters = worksheet.AutoFilters;
//Set range
filters.FilterRange = worksheet["A1:C7"];
//Set filter column
IAutoFilter filter = filters[1];
//Check filter applied or not
Console.WriteLine(filter.IsFiltered);
//Add filter
filter.AddDynamicFilter(DynamicFilterType.Quarter3);
//Check filter applied or not
Console.WriteLine(filter.IsFiltered);
//Apply AutoFit
worksheet.UsedRange.AutofitColumns();
//Save and Dispose
workbook.SaveAs("Tables.xlsx");
workbook.Close();
Console.ReadKey();
}
//Output will be
//False
//TrueIsPercent
True if the Top 10 AutoFilter shows percentage; (Used internally) False if it shows items. Read-only.
Declaration
bool IsPercent { get; }Property Value
| Type |
|---|
| System.Boolean |
IsSimple1
True if the first condition is a simple equality. Read-only.
Declaration
bool IsSimple1 { get; }Property Value
| Type |
|---|
| System.Boolean |
Examples
The following code illustrates how IsSimple1 property can be used.
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 range
filters.FilterRange = worksheet["A1:C7"];
//Set filter column
IAutoFilter filter = filters[0];
//Set Is AND or OR
filter.IsAnd = false;
//Add filter
filter.FirstCondition.ConditionOperator = ExcelFilterCondition.Equal;
filter.FirstCondition.DataType = ExcelFilterDataType.String;
filter.FirstCondition.String = "Alfreds Futterkiste";
filter.SecondCondition.ConditionOperator = ExcelFilterCondition.Equal;
filter.SecondCondition.DataType = ExcelFilterDataType.String;
filter.SecondCondition.String = "Ernst Handel";
//Apply AutoFit
worksheet.UsedRange.AutofitColumns();
Console.WriteLine(filter.IsSimple1);
Console.WriteLine(filter.IsSimple2);
//Save and Dispose
workbook.SaveAs("Filters.xlsx");
workbook.Close();
Console.ReadKey();
}
//Output will be
//True
//TrueIsSimple2
True if the second condition is a simple equality. Read-only.
Declaration
bool IsSimple2 { get; }Property Value
| Type |
|---|
| System.Boolean |
Examples
The following code illustrates how IsSimple2 property can be used.
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 range
filters.FilterRange = worksheet["A1:C7"];
//Set filter column
IAutoFilter filter = filters[0];
//Set Is AND or OR
filter.IsAnd = false;
//Add filter
filter.FirstCondition.ConditionOperator = ExcelFilterCondition.Equal;
filter.FirstCondition.DataType = ExcelFilterDataType.String;
filter.FirstCondition.String = "Alfreds Futterkiste";
filter.SecondCondition.ConditionOperator = ExcelFilterCondition.Equal;
filter.SecondCondition.DataType = ExcelFilterDataType.String;
filter.SecondCondition.String = "Ernst Handel";
//Apply AutoFit
worksheet.UsedRange.AutofitColumns();
Console.WriteLine(filter.IsSimple1);
Console.WriteLine(filter.IsSimple2);
//Save and Dispose
workbook.SaveAs("Filters.xlsx");
workbook.Close();
Console.ReadKey();
}
//Output will be
//True
//TrueIsTop
True if the Top 10 AutoFilter shows the top items; False if it shows the bottom items.
Declaration
bool IsTop { get; set; }Property Value
| Type |
|---|
| System.Boolean |
Examples
The following code illustrates how to set the IsTop 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 = "Top 10";
worksheet["A2"].Number = 7;
worksheet["A3"].Number = 1;
worksheet["A4"].Number = 8;
worksheet["A5"].Number = 2;
worksheet["A6"].Number = 3;
worksheet["A7"].Number = 9;
worksheet["A8"].Number = 15;
worksheet["A9"].Number = 19;
worksheet["A10"].Number = 23;
worksheet["A11"].Number = 42;
worksheet["A12"].Number = 5;
worksheet["A13"].Number = 7;
worksheet["A14"].Number = 12;
worksheet["A15"].Number = 16;
worksheet["A16"].Number = 22;
worksheet["A17"].Number = 33;
worksheet["A18"].Number = 25;
worksheet["A19"].Number = 30;
worksheet["A20"].Number = 38;
worksheet["A21"].Number = 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 filter
IAutoFilters filters = worksheet.AutoFilters;
filters.FilterRange = worksheet["A1:A21"];
IAutoFilter filter = filters[0];
//Set top
filter.IsTop = false;
//Set top 10 number
filter.Top10Number = 5;
//Apply AutoFit
worksheet.UsedRange.AutofitColumns();
//Save and Dispose
workbook.SaveAs("Filters.xlsx");
workbook.Close();
}IsTop10
True if the condition is a Top 10 AutoFilter.
Declaration
bool IsTop10 { get; set; }Property Value
| Type |
|---|
| System.Boolean |
Examples
The following code illustrates how Top 10 is set for 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 = "Top 10";
worksheet["A2"].Number = 7;
worksheet["A3"].Number = 1;
worksheet["A4"].Number = 8;
worksheet["A5"].Number = 2;
worksheet["A6"].Number = 3;
worksheet["A7"].Number = 9;
worksheet["A8"].Number = 15;
worksheet["A9"].Number = 19;
worksheet["A10"].Number = 23;
worksheet["A11"].Number = 42;
worksheet["A12"].Number = 5;
worksheet["A13"].Number = 7;
worksheet["A14"].Number = 12;
worksheet["A15"].Number = 16;
worksheet["A16"].Number = 22;
worksheet["A17"].Number = 33;
worksheet["A18"].Number = 25;
worksheet["A19"].Number = 30;
worksheet["A20"].Number = 38;
worksheet["A21"].Number = 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 filter
IAutoFilters filters = worksheet.AutoFilters;
filters.FilterRange = worksheet["A1:A21"];
IAutoFilter filter = filters[0];
//Set top 10
filter.IsTop10 = true;
//Set top 10 number
filter.Top10Number = 5;
//Apply AutoFit
worksheet.UsedRange.AutofitColumns();
//Save and Dispose
workbook.SaveAs("Filters.xlsx");
workbook.Close();
}SecondCondition
Second condition of autofilter.
Declaration
IAutoFilterCondition SecondCondition { get; }Property Value
| Type |
|---|
| IAutoFilterCondition |
Remarks
To setup a custom FirstCondition should be used. SecondCondition is optional.
Examples
The following code illustrates how to set second 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:C7"].CellStyleName = "CurrencyFormat";
//Create filters collection
IAutoFilters filters = worksheet.AutoFilters;
//Set 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();
}Top10Number
Number of elements to show in Top10 mode.
Declaration
int Top10Number { get; set; }Property Value
| Type |
|---|
| System.Int32 |
Examples
The following code illustrates how to set the Top10Number 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 = "Top 10";
worksheet["A2"].Number = 7;
worksheet["A3"].Number = 1;
worksheet["A4"].Number = 8;
worksheet["A5"].Number = 2;
worksheet["A6"].Number = 3;
worksheet["A7"].Number = 9;
worksheet["A8"].Number = 15;
worksheet["A9"].Number = 19;
worksheet["A10"].Number = 23;
worksheet["A11"].Number = 42;
worksheet["A12"].Number = 5;
worksheet["A13"].Number = 7;
worksheet["A14"].Number = 12;
worksheet["A15"].Number = 16;
worksheet["A16"].Number = 22;
worksheet["A17"].Number = 33;
worksheet["A18"].Number = 25;
worksheet["A19"].Number = 30;
worksheet["A20"].Number = 38;
worksheet["A21"].Number = 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 filter
IAutoFilters filters = worksheet.AutoFilters;
filters.FilterRange = worksheet["A1:A21"];
IAutoFilter filter = filters[0];
//Set top
filter.IsTop = false;
//Set top 10 number
filter.Top10Number = 5;
//Apply AutoFit
worksheet.UsedRange.AutofitColumns();
//Save and Dispose
workbook.SaveAs("Filters.xlsx");
workbook.Close();
}Methods
AddColorFilter(Color, ExcelColorFilterType)
Add Color Filter with the specified System.Drawing.Color object
Declaration
void AddColorFilter(Color color, ExcelColorFilterType colorFilterType)Parameters
| Type | Name | Description |
|---|---|---|
| Color | color | System.Drawing.Color to be filtered. |
| ExcelColorFilterType | colorFilterType | ExcelColorFilterType applied. |
Remarks
To apply filters within tables in worksheet, we have to set the IAutoFilters as child of IListObject. The range for the IAutoFilters will be same as the Location.
Examples
Color filter can be applied based on CellColor or FontColor. Here for example, we apply filter based on CellColor.
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";
//Apply colors
worksheet["A2:A3"].CellStyle.Color = System.Drawing.Color.Red;
worksheet["A4:A5"].CellStyle.Color = System.Drawing.Color.Blue;
worksheet["A6:A7"].CellStyle.Color = System.Drawing.Color.Green;
//Create filters collection
IAutoFilters filters = worksheet.AutoFilters;
//Set range
filters.FilterRange = worksheet["A1:C7"];
//Set filter column
IAutoFilter filter = filters[0];
//Add filter
filter.AddColorFilter(System.Drawing.Color.Blue, ExcelColorFilterType.CellColor);
//Apply AutoFit
worksheet.UsedRange.AutofitColumns();
//Save and Dispose
workbook.SaveAs("Filters.xlsx");
workbook.Close();
}AddDateFilter(DateTime, DateTimeGroupingType)
Apply DateTime filter with the specified date time.
Declaration
void AddDateFilter(DateTime dateTime, DateTimeGroupingType groupingType)Parameters
| Type | Name | Description |
|---|---|---|
| System.DateTime | dateTime | Date with time which need to add. |
| DateTimeGroupingType | groupingType | Type of grouping. |
Remarks
To apply filters within tables in worksheet, we have to set the IAutoFilters as child of IListObject. The range for the IAutoFilters will be same as the Location.
Examples
The following code illustrates how to apply DateTime filter by passing System.DateTime object.
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 = "Title";
worksheet["A2"].Text = "Sales Representative";
worksheet["A3"].Text = "Sales Agent";
worksheet["A4"].Text = "Owner";
worksheet["A5"].Text = "Sales Associate";
worksheet["A6"].Text = "Sales Representative";
worksheet["A7"].Text = "Marketing Manager";
worksheet["B1"].Text = "DOJ";
worksheet["B2"].DateTime = DateTime.Parse("9/5/2011");
worksheet["B3"].DateTime = DateTime.Parse("9/5/2011");
worksheet["B4"].DateTime = DateTime.Parse("10/21/2007");
worksheet["B5"].DateTime = DateTime.Parse("9/28/2004");
worksheet["B6"].DateTime = DateTime.Parse("8/28/2007");
worksheet["B7"].DateTime = DateTime.Parse("8/12/2003");
//Create filters collection
IAutoFilters filters = worksheet.AutoFilters;
//Set range
filters.FilterRange = worksheet["A1:C7"];
//Set filter column
IAutoFilter filter = filters[1];
//Add filter
filter.AddDateFilter(new DateTime(2004, 9, 28, 0, 0, 0, 0), DateTimeGroupingType.month);
//Apply AutoFit
worksheet.UsedRange.AutofitColumns();
//Save and Dispose
workbook.SaveAs("Tables.xlsx");
workbook.Close();
}AddDateFilter(Int32, Int32, Int32, Int32, Int32, Int32, DateTimeGroupingType)
Apply DateTime filter with the specified date time.
Declaration
void AddDateFilter(int year, int month, int day, int hour, int mintue, int second, DateTimeGroupingType groupingType)Parameters
| Type | Name | Description |
|---|---|---|
| System.Int32 | year | year in the date time. |
| System.Int32 | month | month in the date time. |
| System.Int32 | day | day in the date time. |
| System.Int32 | hour | hour in the date time. |
| System.Int32 | mintue | mintue in the date time. |
| System.Int32 | second | Seconds in the date time. |
| DateTimeGroupingType | groupingType | With respective grouping type. |
Remarks
To apply filters within tables in worksheet, we have to set the IAutoFilters as child of IListObject. The range for the IAutoFilters will be same as the Location.
Examples
The following code illustrates how to apply DateTime filter.
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 = "Title";
worksheet["A2"].Text = "Sales Representative";
worksheet["A3"].Text = "Sales Agent";
worksheet["A4"].Text = "Owner";
worksheet["A5"].Text = "Sales Associate";
worksheet["A6"].Text = "Sales Representative";
worksheet["A7"].Text = "Marketing Manager";
worksheet["B1"].Text = "DOJ";
worksheet["B2"].DateTime = DateTime.Parse("6/5/2011");
worksheet["B3"].DateTime = DateTime.Parse("9/5/2011");
worksheet["B4"].DateTime = DateTime.Parse("10/21/2007");
worksheet["B5"].DateTime = DateTime.Parse("9/28/2004");
worksheet["B6"].DateTime = DateTime.Parse("8/28/2007");
worksheet["B7"].DateTime = DateTime.Parse("8/12/2003");
//Create filters collection
IAutoFilters filters = worksheet.AutoFilters;
//Set range
filters.FilterRange = worksheet["A1:C7"];
//Set filter column
IAutoFilter filter = filters[1];
//Add filter
filter.AddDateFilter(2004, 9, 28, 0, 0, 0, DateTimeGroupingType.month);
//Apply AutoFit
worksheet.UsedRange.AutofitColumns();
//Save and Dispose
workbook.SaveAs("Filters.xlsx");
workbook.Close();
}AddDynamicFilter(DynamicFilterType)
Apply dynamic date filter based on the specified relative date constant.
Declaration
void AddDynamicFilter(DynamicFilterType dynamicFilterType)Parameters
| Type | Name | Description |
|---|---|---|
| DynamicFilterType | dynamicFilterType | Type dynamic filter |
Examples
The following code illustrates how to apply the DynamicFilter.
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 = "Title";
worksheet["A2"].Text = "Sales Representative";
worksheet["A3"].Text = "Sales Agent";
worksheet["A4"].Text = "Owner";
worksheet["A5"].Text = "Sales Associate";
worksheet["A6"].Text = "Sales Representative";
worksheet["A7"].Text = "Marketing Manager";
worksheet["B1"].Text = "DOJ";
worksheet["B2"].DateTime = DateTime.Parse("6/5/2011");
worksheet["B3"].DateTime = DateTime.Parse("9/5/2011");
worksheet["B4"].DateTime = DateTime.Parse("10/21/2007");
worksheet["B5"].DateTime = DateTime.Parse("9/28/2004");
worksheet["B6"].DateTime = DateTime.Parse("8/28/2007");
worksheet["B7"].DateTime = DateTime.Parse("8/12/2003");
//Create filters collection
IAutoFilters filters = worksheet.AutoFilters;
//Set range
filters.FilterRange = worksheet["A1:C7"];
//Set filter column
IAutoFilter filter = filters[1];
//Add filter
filter.AddDynamicFilter(DynamicFilterType.Quarter3);
//Apply AutoFit
worksheet.UsedRange.AutofitColumns();
//Save and Dispose
workbook.SaveAs("Filters.xlsx");
workbook.Close();
}AddIconFilter(ExcelIconSetType, Int32)
Apply Icon Filter with the specfied iconId for the Iconset applied.
Declaration
void AddIconFilter(ExcelIconSetType iconSetType, int iconId)Parameters
| Type | Name | Description |
|---|---|---|
| ExcelIconSetType | iconSetType | ExcelIconSetType to be filtered from. |
| System.Int32 | iconId | IconId to be filtered. |
Remarks
To apply filters within tables in worksheet, we have to set the IAutoFilters as child of IListObject. The range for the IAutoFilters will be same as the Location.
Examples
Iconsets can be applied by setting conditional formats for data in the worksheet. For the iconsets applied using conditional formats we can apply IconFilter to filter a particular icon based on it's id or index. Here for example, we set ThreeArrows using conditional formats and filter the first icon in it.
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 = 144.6;
worksheet["B3"].Number = 579.6;
worksheet["B4"].Number = 267.5;
worksheet["B5"].Number = 418;
worksheet["B6"].Number = 728;
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 range
filters.FilterRange = worksheet["A1:C7"];
//Set filter column
IAutoFilter filter = filters[1];
//Create conditional formats
IConditionalFormats formats = worksheet["B2:B7"].ConditionalFormats;
IConditionalFormat format = formats.AddCondition();
//Set Type as IconSet
format.FormatType = ExcelCFType.IconSet;
//Add filter
filter.AddIconFilter(ExcelIconSetType.ThreeArrows, 0);
//Apply AutoFit
worksheet.UsedRange.AutofitColumns();
//Save and Dispose
workbook.SaveAs("Filters.xlsx");
workbook.Close();
}AddTextFilter(IEnumerable<String>)
Declaration
void AddTextFilter(IEnumerable<string> filterCollection)Parameters
| Type | Name | Description |
|---|---|---|
| System.Collections.Generic.IEnumerable<System.String> | filterCollection |
AddTextFilter(String)
Apply text filter with the specified string.
Declaration
void AddTextFilter(string filter)Parameters
| Type | Name | Description |
|---|---|---|
| System.String | filter | string value need to add in the text filter |
Remarks
To apply filters within tables in worksheet, we have to set the IAutoFilters as child of IListObject. The range for the IAutoFilters will be same as the Location.
Examples
The following code illustrates how to apply text filter with the specified string.
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 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();
}RemoveColorFilter()
Removes the Color Filter applied
Declaration
void RemoveColorFilter()Examples
The following code illustrates how to remove the ColorFilter.
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";
//Apply colors
worksheet["A2:A3"].CellStyle.Color = System.Drawing.Color.Red;
worksheet["A4:A5"].CellStyle.Color = System.Drawing.Color.Blue;
worksheet["A6:A7"].CellStyle.Color = System.Drawing.Color.Green;
//Create filters collection
IAutoFilters filters = worksheet.AutoFilters;
//Set range
filters.FilterRange = worksheet["A1:C7"];
//Set filter column
IAutoFilter filter = filters[0];
//Add filter
filter.AddColorFilter(System.Drawing.Color.Blue, ExcelColorFilterType.CellColor);
//Remove filter
filter.RemoveColorFilter();
//Apply AutoFit
worksheet.UsedRange.AutofitColumns();
//Save and Dispose
workbook.SaveAs("Filters.xlsx");
workbook.Close();
}RemoveDate(DateTime, DateTimeGroupingType)
Remove specified date time from DateTime filter.
Declaration
bool RemoveDate(DateTime dateTime, DateTimeGroupingType groupingType)Parameters
| Type | Name | Description |
|---|---|---|
| System.DateTime | dateTime | Datetime which need to remove. |
| DateTimeGroupingType | groupingType | With respective grouping type |
Returns
| Type | Description |
|---|---|
| System.Boolean | Return true if value is removed, else return false. |
Examples
The following code illustrates how to remove a particular date time from the date time filter's filtered list by passing System.DateTime object as an argument.
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 = "Title";
worksheet["A2"].Text = "Sales Representative";
worksheet["A3"].Text = "Sales Agent";
worksheet["A4"].Text = "Owner";
worksheet["A5"].Text = "Sales Associate";
worksheet["A6"].Text = "Sales Representative";
worksheet["A7"].Text = "Marketing Manager";
worksheet["B1"].Text = "DOJ";
worksheet["B2"].DateTime = DateTime.Parse("6/5/2011");
worksheet["B3"].DateTime = DateTime.Parse("9/5/2011");
worksheet["B4"].DateTime = DateTime.Parse("10/21/2007");
worksheet["B5"].DateTime = DateTime.Parse("9/28/2004");
worksheet["B6"].DateTime = DateTime.Parse("8/28/2007");
worksheet["B7"].DateTime = DateTime.Parse("8/12/2003");
//Create filters collection
IAutoFilters filters = worksheet.AutoFilters;
//Set range
filters.FilterRange = worksheet["A1:C7"];
//Set filter column
IAutoFilter filter = filters[1];
//Add filter
filter.AddDateFilter(new DateTime(2004, 9, 28, 0, 0, 0, 0), DateTimeGroupingType.month);
filter.AddDateFilter(new DateTime(2007, 8, 28, 0, 0, 0, 0), DateTimeGroupingType.month);
filter.AddDateFilter(new DateTime(2011, 9, 5, 0, 0, 0, 0), DateTimeGroupingType.month);
//Remove filter
filter.RemoveDate(new DateTime(2007, 8, 28, 0, 0, 0, 0), DateTimeGroupingType.month);
//Apply AutoFit
worksheet.UsedRange.AutofitColumns();
//Save and Dispose
workbook.SaveAs("Filters.xlsx");
workbook.Close();
}RemoveDate(Int32, Int32, Int32, Int32, Int32, Int32, DateTimeGroupingType)
Remove specified date time from DateTime filter.
Declaration
bool RemoveDate(int year, int month, int day, int hour, int mintue, int second, DateTimeGroupingType groupingType)Parameters
| Type | Name | Description |
|---|---|---|
| System.Int32 | year | year in the date time. |
| System.Int32 | month | month in the date time. |
| System.Int32 | day | day in the date time. |
| System.Int32 | hour | hour in the date time. |
| System.Int32 | mintue | mintue in the date time. |
| System.Int32 | second | Seconds in the date time. |
| DateTimeGroupingType | groupingType | With respective grouping type. |
Returns
| Type | Description |
|---|---|
| System.Boolean | Return true if value is removed, else return false. |
Examples
The following code illustrates how to remove a particular date time from the date time filter's filtered list.
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 = "Title";
worksheet["A2"].Text = "Sales Representative";
worksheet["A3"].Text = "Sales Agent";
worksheet["A4"].Text = "Owner";
worksheet["A5"].Text = "Sales Associate";
worksheet["A6"].Text = "Sales Representative";
worksheet["A7"].Text = "Marketing Manager";
worksheet["B1"].Text = "DOJ";
worksheet["B2"].DateTime = DateTime.Parse("6/5/2011");
worksheet["B3"].DateTime = DateTime.Parse("9/5/2011");
worksheet["B4"].DateTime = DateTime.Parse("10/21/2007");
worksheet["B5"].DateTime = DateTime.Parse("9/28/2004");
worksheet["B6"].DateTime = DateTime.Parse("8/28/2007");
worksheet["B7"].DateTime = DateTime.Parse("8/12/2003");
//Create filters collection
IAutoFilters filters = worksheet.AutoFilters;
//Set range
filters.FilterRange = worksheet["A1:C7"];
//Set filter column
IAutoFilter filter = filters[1];
//Add filter
filter.AddDateFilter(2004, 9, 28, 0, 0, 0, DateTimeGroupingType.month);
filter.AddDateFilter(2007, 8, 28, 0, 0, 0, DateTimeGroupingType.month);
filter.AddDateFilter(2011, 9, 5, 0, 0, 0, DateTimeGroupingType.month);
//Remove filter
filter.RemoveDate(2007, 8, 28, 0, 0, 0, DateTimeGroupingType.month);
//Apply AutoFit
worksheet.UsedRange.AutofitColumns();
//Save and Dispose
workbook.SaveAs("Filters.xlsx");
workbook.Close();
}RemoveDynamicFilter()
Remove dynamic date filter if exist.
Declaration
bool RemoveDynamicFilter()Returns
| Type | Description |
|---|---|
| System.Boolean | Return true if dynamic filter is removed, else return false. |
Examples
The following code illustrates how to remove the DynamicFilter.
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 = "Title";
worksheet["A2"].Text = "Sales Representative";
worksheet["A3"].Text = "Sales Agent";
worksheet["A4"].Text = "Owner";
worksheet["A5"].Text = "Sales Associate";
worksheet["A6"].Text = "Sales Representative";
worksheet["A7"].Text = "Marketing Manager";
worksheet["B1"].Text = "DOJ";
worksheet["B2"].DateTime = DateTime.Parse("6/5/2011");
worksheet["B3"].DateTime = DateTime.Parse("9/5/2011");
worksheet["B4"].DateTime = DateTime.Parse("10/21/2007");
worksheet["B5"].DateTime = DateTime.Parse("9/28/2004");
worksheet["B6"].DateTime = DateTime.Parse("8/28/2007");
worksheet["B7"].DateTime = DateTime.Parse("8/12/2003");
//Create filters collection
IAutoFilters filters = worksheet.AutoFilters;
//Set range
filters.FilterRange = worksheet["A1:C7"];
//Set filter column
IAutoFilter filter = filters[1];
//Add filter
filter.AddDynamicFilter(DynamicFilterType.Quarter3);
//Remove filter
filter.RemoveDynamicFilter();
//Apply AutoFit
worksheet.UsedRange.AutofitColumns();
//Save and Dispose
workbook.SaveAs("Filters.xlsx");
workbook.Close();
}RemoveIconFilter()
Removes the Icon Filter applied.
Declaration
void RemoveIconFilter()Examples
The following code illustrates how to remove the IconFilter.
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 = 144.6;
worksheet["B3"].Number = 579.6;
worksheet["B4"].Number = 267.5;
worksheet["B5"].Number = 418;
worksheet["B6"].Number = 728;
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 range
filters.FilterRange = worksheet["A1:C7"];
//Set filter column
IAutoFilter filter = filters[1];
//Create conditional formats
IConditionalFormats formats = worksheet["B2:B7"].ConditionalFormats;
IConditionalFormat format = formats.AddCondition();
//Set Type as IconSet
format.FormatType = ExcelCFType.IconSet;
//Add filter
filter.AddIconFilter(ExcelIconSetType.ThreeArrows, 0);
//Remove filter
filter.RemoveIconFilter();
//Apply AutoFit
worksheet.UsedRange.AutofitColumns();
//Save and Dispose
workbook.SaveAs("Filters.xlsx");
workbook.Close();
}RemoveText(IEnumerable<String>)
Declaration
bool RemoveText(IEnumerable<string> filterCollection)Parameters
| Type | Name | Description |
|---|---|---|
| System.Collections.Generic.IEnumerable<System.String> | filterCollection |
Returns
| Type |
|---|
| System.Boolean |
RemoveText(String)
Remove text value in text filter with the specified string.
Declaration
bool RemoveText(string filter)Parameters
| Type | Name | Description |
|---|---|---|
| System.String | filter | Value need to remove from the filter list. |
Returns
| Type | Description |
|---|---|
| System.Boolean | Return true if value is removed, else return false. |
Examples
The following code illustrates how to remove a specific text from the text filter's filtered list.
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 range
filters.FilterRange = worksheet["A1:C7"];
//Set filter column
IAutoFilter filter = filters[0];
//Add filter
filter.AddTextFilter("Eastern Connection");
filter.AddTextFilter("Around the Horn");
filter.AddTextFilter("Bon app");
//Remove filter
filter.RemoveText("Around the Horn");
//Apply AutoFit
worksheet.UsedRange.AutofitColumns();
//Save and Dispose
workbook.SaveAs("Filters.xlsx");
workbook.Close();
}