FilterString() criteria support

I’m stuck on trying to create a filter for a Worksheet in the GridDesktop. In your sample, the filter is set to “Customer 1” which seems to work fine. But when I try to use criteria that is not equivalent, such as “<>0”, the filter does not take the “<>” into account.
This seems to be a limitation for the GridDesktop, because the Aspose.Cells filtering seems to support the FilterOperatorType.NotEqual.
How can I apply a “<> 0” to my FilterString?

@mgranic,

Could you share a sample WinForm application having your code segment using latest Aspose.Cells.GridDesktop v20.6, zip the project and post us, we will check it soon.

Amjad, I’m using your GridDesktop.Examples project. Simply change the code in FilteringData.button2_Click from:
rowFilter.FilterRows(0, "Customer 1");
to
rowFilter.FilterRows(0, "<> Customer 1"); // note the NotEquals <>

image.png (42.2 KB)

Microsoft Excel’s Range.AutoFilter() API allows for this style of criteria, which we currently use.

In the end, I can filter on 0, but I cannot filter on NOT 0.

However, your RowFilterSettings class shows the following comment, which tells me that it might be possible to apply this style of filter but these examples target a specific cell. Is there a complete reference guide for this that would demonstrate how to achieve the “<> 0” that I’m looking for?

The filter criteria string.
/// below are the criteria string examples:
/// CELL0 = 12.3
/// CELL1 = “ABC”
/// AND(CELL0 = 123, CELL1 = “ABC”)
/// OR(CELL0 = 123, CELL1 = “ABC”)
/// CELL0 = B1 + 4
public string Criteria { get; set; }

@mgranic,

Thanks for providing us further details.

I tried different ways to accomplish your task but to no avail. It seems your desired task may not be implemented in your way. Anyways, I have logged a ticket with an id “CELLSNET-47432” for your task/feature. We will look into it and provide means to support your demand.

Once we have an update on it, we will let you know.

Thanks for looking Amjad.

@mgranic,

You are welcome.

@mgranic,

We plan to add the following API for your needs:

Aspose.Cells.GridDesktop.RowFilterSettings add the below methods
///


/// Filters a list with a custom criteria.
///

/// The column of the worksheet
///
/// The filter operator type
/// The custom criteria
public void CustomRows(int column, GridFilterOperatorType operatorType1, object criteria1)
///
/// Filters a list with a custom criteria.
///

/// The column of the worksheet
/// The filter operator type
/// The custom criteria
///
/// The filter operator type
/// The custom criteria
public void CustomRows(int column, GridFilterOperatorType operatorType1, object criteria1, bool isAnd,
GridFilterOperatorType operatorType2, object criteria2)

Once we implement it, we will update you with the new version.

Amjad, would that solution also work for the AutoFilter? Would the Worksheet.FilterString() method have an overload for the same?

@mgranic,

Thanks for your further query.

We will analyze it and then update you soon.

Could you elaborate your requirements and your expected API with some samples, we can evaluate it.

I tried several options for filtering, and the AddAutoFilter seemed to have the behavior I was looking for when matching the full string, such as “Customer 1”. Of course it was not able to handle the “<> 0” criteria that we have been discussing. Here’s the code that I was using:

var range = AWorksheet.CreateRange(beginCell, endCell);
AWorksheet.AddAutoFilter(range.StartRow, range.StartColumn, range.EndColumn);
AWorksheet.FilterString(range.StartColumn, "Customer 1");
AWorksheet.RowFilter.EnableAutoFilter = true;
AWorksheet.RefreshFilter();

@mgranic,

Good to know that you have sorted it out for auto filtering data.

Regarding handling the criteria e.g “<> 0”, as we logged a ticket for it already and are on it,

@mgranic,

We have decided to add the following methods under Aspose.Cells.GridDesktop.Worksheet:

  /// <summary>
        /// Add custom filter for the specified row range from start row to end row.
        /// </summary>
        ///  <param name="startrow">The startrow of the filter range</param>
       ///   <param name="startcolumn">The startcolumn of the filter range </param>
        ///   <param name="critiras">The critira array for the columns,each one apply to each column</param>
        ///    <param name="filterOperatorTypes">The filter operate type array for the columns,each one apply to each column</param>
        /// <remarks>
        /// 
        /// </remarks>
        public void AddCustomFilter(int startrow,int startcolumn, Object[] critiras,GridFilterOperatorType[] filterOperatorTypes)

  /// <summary>  reset filter type to none </summary>
        /// <param name="startFieldIndex">tht first field index, The integer offset of the field on which you want to apply ,based on the first filter column
        /// (from the left of the list; the leftmost field is field 0).</param>
        ///  <param name="endFildIndex">tht last field index,The integer offset of the field on which you want to apply ,based on the first filter column
        /// (from the left of the list; the leftmost field is field 0).</param>
         public void ResetFilters(int startFieldIndex,int endFildIndex)

Thank you, please let me know when it is available.

@mgranic,

Sure, we will keep you posted with updates once available.

@mgranic,

Please try our latest fix/version: Aspose.Cells.GridDesktop v20.6.2 (attached)
Aspose.Cells.GridDesktop.20.6.2.zip (5.1 MB)

Your issue should be fixed in it.

Let us know your feedback.

The issues you have found earlier (filed as CELLSNET-47432) have been fixed in Aspose.Cells for .NET v20.7. This message was posted using Bugs notification tool by ahsaniqbalsidiqui