PivotTable. Filtering in Excel 2007 and later versions

amjad.sahi · December 7, 2011, 1:35am

Hi,

We have supported auto-filters API in the latest Aspose's version: 7.0.3.4.

Sample Code:

PivotTable pt = wb.Worksheets[0].PivotTables[0];

int index = pt.PivotFilters.Add(0, PivotFilterType.Count);

PivotFilter filter = pt.PivotFilters[index];

filter.AutoFilter.FilterTop10(0, false, false, 2);

licenses · December 7, 2011, 5:45am

Hi Amjad,

Your filtering mechanism has much differences with Excel, so I have problems with understanding it relations with original model.

Could you please explain How do I read next data from your filtering model:

All assigned filters for the chosen field (by index or/and by type);
2. Type of filter - Label/Value (labelValue.png) and their subtypes (labelTypes.png, valueTypes.png);
3. Filter settings (all types have different settings).

By the way, your method ‘filter.AutoFilter.FilterTop10(0, false, false, 2)’ has two state argument ‘isPercent’, but in Excel it has at least three states (isPercent.png).

Thank you,
Alex Shloma

amjad.sahi · December 8, 2011, 3:24am

Hi,

1) We will give a method to get the pivot filter of a pivot field by type.

2) The label filters and value filters are in the PivotFilterType

for example:

PivotFilterType.CaptionEqual means label filters->equal,

PivotFilterType.ValueEqual means value filters->equal

3)

sample code:

filter.AutoFilter.Custom(0, FilterOperatorType.Equal, 1);

4)
By the way, your method ‘filter.AutoFilter.FilterTop10(0, false, false, 2)’ has two state argument ‘isPercent’, but in Excel it has at least three states (isPercent.png).

You could use PivotFilterType.Sum e.g “PivotFilters.Add(0, PivotFilterType.Sum)” to set the “sum” type.

Thanks.

licenses · December 8, 2011, 7:50am

Hi,

1) When are you plan to implement this feature?
3) It’s the write method, but I need to read current a filter settings.
4) ‘PivotFilterType.Percent’ also exists, but the method have separate argument ‘isPercent’. Why was it added to the method?

I created simple VBA macro in Excel for the enumeration of field filters. Could you explain how to create the method with the same functionality by Aspose.Cells?

Attribute VB_Name = "Module1"
Sub enumerateFilters()
With ActiveSheet.PivotTables(“PivotTable1”).PivotFields(“Product”)
On Error Resume Next
For Each pf In .PivotFilters
With pf
df = .DataField
v1 = .Value1
v2 = .Value2
mpf = .MemberPropertyField

MsgBox "Active = " & .Active _
& vbCrLf & "FilterType = " & fTypeName(.FilterType) _
& vbCrLf & "DataField = " & df _
& vbCrLf & "Value1 = " & v1 _
& vbCrLf & "Value2 = " & v2 _
& vbCrLf & "Description = " & Description _
& vbCrLf & "IsMemberPropertyFilter = " & IsMemberPropertyFilter _
& vbCrLf & "MemberPropertyField = " & mpf _
& vbCrLf & "Name = " & .Name _
& vbCrLf & "Order = " & .Order
End With
Next pf
End With
End Sub

Function fTypeName(code As Integer)
Select Case code
Case xlTopCount
fTypeName = “xlTopCount”
Case xlBottomCount
fTypeName = “xlBottomCount”
Case xlTopPercent
fTypeName = “xlTopPercent”
Case xlBottomPercent
fTypeName = “xlBottomPercent”
Case xlTopSum
fTypeName = “xlTopSum”
Case xlBottomSum
fTypeName = “xlBottomSum”
Case xlCaptionEquals
fTypeName = “xlCaptionEquals”
Case xlCaptionDoesNotEqual
fTypeName = “xlCaptionDoesNotEqual”
Case xlCaptionIsGreaterThan
fTypeName = “xlCaptionIsGreaterThan”
Case xlCaptionIsGreaterThanOrEqualTo
fTypeName = “xlCaptionIsGreaterThan”
Case xlCaptionIsLessThan
fTypeName = “xlCaptionIsLessThan”
Case xlCaptionIsLessThanOrEqualTo
fTypeName = “xlCaptionIsLessThanOrEqualTo”
Case xlCaptionBeginsWith
fTypeName = “xlCaptionBeginsWith”
Case xlCaptionDoesNotBeginWith
fTypeName = “xlCaptionDoesNotBeginWith”
Case xlCaptionEndsWith
fTypeName = “xlCaptionEndsWith”
Case xlCaptionDoesNotEndWith
fTypeName = “xlCaptionDoesNotEndWith”
Case xlCaptionContains
fTypeName = “xlCaptionContains”
Case xlCaptionDoesNotContain
fTypeName = “xlCaptionDoesNotContain”
Case xlCaptionIsBetween
fTypeName = “xlCaptionIsBetween”
Case xlCaptionIsNotBetween
fTypeName = “xlCaptionIsNotBetween”
Case xlValueEquals
fTypeName = “xlValueEquals”
Case xlValueDoesNotEqual
fTypeName = “xlValueDoesNotEqual”
Case xlValueIsGreaterThan
fTypeName = “xlValueIsGreaterThan”
Case xlValueIsGreaterThanOrEqualTo
fTypeName = “xlValueIsGreaterThanOrEqualTo”
Case xlValueIsLessThan
fTypeName = “xlValueIsLessThan”
Case xlValueIsLessThanOrEqualTo
fTypeName = “xlValueIsLessThanOrEqualTo”
Case xlValueIsBetween
fTypeName = “xlValueIsBetween”
Case xlValueIsNotBetween
fTypeName = “xlValueIsNotBetween”
Case xlSpecificDate
fTypeName = “xlSpecificDate”
Case xlNotSpecificDate
fTypeName = “xlNotSpecificDate”
Case xlBefore
fTypeName = “xlBefore”
Case xlBeforeOrEqualTo
fTypeName = “xlBeforeOrEqualTo”
Case xlAfter
fTypeName = “xlAfter”
Case xlAfterOrEqualTo
fTypeName = “xlAfterOrEqualTo”
Case xlBetween
fTypeName = “xlBetween”
Case xlNotBetween
fTypeName = “xlNotBetween”
Case xlFilterToday
fTypeName = “xlFilterToday”
Case xlFilterYesterday
fTypeName = “xlFilterYesterday”
Case xlFilterTomorrow
fTypeName = “xlFilterTomorrow”
Case xlFilterThisWeek
fTypeName = “xlFilterThisWeek”
Case xlFilterLastWeek
fTypeName = “xlFilterLastWeek”
Case xlFilterNextWeek
fTypeName = “xlFilterNextWeek”
Case xlFilterThisMonth
fTypeName = “xlFilterThisMonth”
Case xlFilterLastMonth
fTypeName = “xlFilterLastMonth”
Case xlFilterNextMonth
fTypeName = “xlFilterNextMonth”
Case xlFilterThisQuarter
fTypeName = “xlFilterThisQuarter”
Case xlFilterLastQuarter
fTypeName = “xlFilterLastQuarter”
Case xlFilterNextQuarter
fTypeName = “xlFilterNextQuarter”
Case xlFilterThisYear
fTypeName = “xlFilterThisYear”
Case xlFilterLastYear
fTypeName = “xlFilterLastYear”
Case xlFilterNextYear
fTypeName = “xlFilterNextYear”
Case xlFilterYearToDate
fTypeName = “xlFilterYearToDate”
Case xlFilterAllDatesInPeriodQuarter1
fTypeName = “xlFilterAllDatesInPeriodQuarter1”
Case xlFilterAllDatesInPeriodQuarter2
fTypeName = “xlFilterAllDatesInPeriodQuarter2”
Case xlFilterAllDatesInPeriodQuarter3
fTypeName = “xlFilterAllDatesInPeriodQuarter3”
Case xlFilterAllDatesInPeriodQuarter4
fTypeName = “xlFilterAllDatesInPeriodQuarter4”
Case xlFilterAllDatesInPeriodJanuary
fTypeName = “xlFilterAllDatesInPeriodJanuary”
Case xlFilterAllDatesInPeriodFebruary
fTypeName = “xlFilterAllDatesInPeriodFebruary”
Case xlFilterAllDatesInPeriodMarch
fTypeName = “xlFilterAllDatesInPeriodMarch”
Case xlFilterAllDatesInPeriodApril
fTypeName = “xlFilterAllDatesInPeriodApril”
Case xlFilterAllDatesInPeriodMay
fTypeName = “xlFilterAllDatesInPeriodMay”
Case xlFilterAllDatesInPeriodJune
fTypeName = “xlFilterAllDatesInPeriodJune”
Case xlFilterAllDatesInPeriodJuly
fTypeName = “xlFilterAllDatesInPeriodJuly”
Case xlFilterAllDatesInPeriodAugust
fTypeName = “xlFilterAllDatesInPeriodAugust”
Case xlFilterAllDatesInPeriodSeptember
fTypeName = “xlFilterAllDatesInPeriodSeptember”
Case xlFilterAllDatesInPeriodOctober
fTypeName = “xlFilterAllDatesInPeriodOctober”
Case xlFilterAllDatesInPeriodNovember
fTypeName = “xlFilterAllDatesInPeriodNovember”
Case xlFilterAllDatesInPeriodDecember
fTypeName = “xlFilterAllDatesInPeriodDecember”
End Select
End Function

I attached *.xlsm document with this macro and images with result of it work.

Thank you,
Alex Shloma

amjad.sahi · December 9, 2011, 4:34am

Hi,

1). We will implement this feature in the coming days, hopefully it will be available in the next official release of the product.

3). We will make them open for these properties in the next version.

4). It is also using to filter cells in the worksheet. We will provide you some sample code for your need.

Thanks.

licenses · December 9, 2011, 5:00am

Hi,

4. Are there any difference between these options? If they are different, could you create samples for situations When they are used.

As I understand, I can’t realize functionality of macro from my previous post by Aspose.Cells. After implementation of 1) and 3), could you explain how to create the method with such functionality?

Thank you,
Alex Shloma

shakeel.faiz · December 13, 2011, 3:52am

Hi,

We support getting pivotfilter in the pivotfield by pivotfilter type in the latest: Aspose.Cells for .NET 7.0.3.6.

Please try this sample code to get filter settings.

C#

PivotFilter filter = wb.Worksheets[1].PivotTables[0].RowFields[0].GetPivotFilterByType(PivotFilterType.Count);

Console.WriteLine(filter.FilterType);

Console.WriteLine(filter.MeasureFldIndex);

Console.WriteLine(filter.Value1);

Console.WriteLine(filter.Value2);

Console.WriteLine(filter.MemberPropertyFieldIndex);

Console.WriteLine(filter.Name);

Console.WriteLine(filter.EvaluationOrder);

Try this sample code to create filters,source file is in the appendix:

C#

PivotTable table = wb.Worksheets[1].PivotTables[0];
int index = table.PivotFilters.Add(1, PivotFilterType.Count);
PivotFilter filter = table.PivotFilters[index];
filter.AutoFilter.FilterTop10(0, true, false, 1);
filter.MeasureFldIndex = 3;

index = table.PivotFilters.Add(1, PivotFilterType.CaptionGreaterThan);
filter = table.PivotFilters[index];
filter.Value1 = "1";
filter.AutoFilter.Custom(0, FilterOperatorType.GreaterThan, 1);

index = table.PivotFilters.Add(0, PivotFilterType.Count);
filter = table.PivotFilters[index];
filter.AutoFilter.FilterTop10(0, true, false, 10);
filter.MeasureFldIndex = 0;

licenses · December 13, 2011, 9:07am

Hi,

1.You forgot about my question in previous post. Could you please answer on it?
‘4. Are there any difference between these options? If they are different, could you create samples for situations When they are used.’

2. These methods is not work with documents in *.xls format. I converted document from previous post to *.xls and Aspose.Cells found nothing. The macro in converted document returns the same information as the macro in original document.

3. Some properties return incorrect data. I used document from previous post. The result from macro you can see at attached image (macroFilter2.png).
Macro:
EvaluationOrder = 2
Value1 = “1”
Aspose.Cells 7.0.3.6
EvaluationOrder = -1
Value1 = “”

‘Value2’ has the same problem as ‘Value1’. For example, ‘ValueNotBetween’ filter.

PivotFieldCollection pfc = pt.RowFields;
for (int i = 0; i < pfc.Count; i++)
{
PivotField pf = pfc[i];
try
{
foreach (PivotFilter pfl in pf.GetPivotFilters())
{
printLine(“Name”, pfl.Name, SPACE4); // my inner printing method
printLine(“FilterType”, Enum.GetName(typeof(PivotFilterType), pfl.FilterType), SPACE5);
printLine(“EvaluationOrder”, pfl.EvaluationOrder.ToString(), SPACE5);
printLine(“FieldIndex”, pfl.FieldIndex.ToString(), SPACE5);
printLine(“MeasureFldIndex”, pfl.MeasureFldIndex.ToString(), SPACE5);
printLine(“MemberPropertyFieldIndex”, pfl.MemberPropertyFieldIndex.ToString(), SPACE5);
printLine(“Value1”, pfl.Value1, SPACE5);
printLine(“Value2”, pfl.Value2, SPACE5);
}
}
catch
{
printLine(“Filters”, " ============== ERROR ============== ", SPACE4);
}
}

4. Some filter types have no full information about filter. For example, ‘Top 10…’ filter returns ‘Count’ without ‘Top’ or ‘Bottom’. Is it exist any way to read these options?

5. Missing properties.
Excel Developer Reference
PivotFilter Object Members
Active - Returns whether the specified PivotFilter is active. Read-only Boolean.
Description - Provides an optional description for the PivotFilter object. Read-only String.

Thank you,
Alex Shloma

aspose.notifier · December 19, 2011, 1:26am

The issues you have found earlier (filed as CELLSNET-40147) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by aspose.notifier.

licenses · December 19, 2011, 8:42am

Hi,

I checked the latest version (7.0.4.0) and it has the same problems. Should I create new ticket for this or you can reopen?

Best regards,

Alex Shloma

amjad.sahi · December 19, 2011, 12:11pm

Hi,

We did fix certain issues and added some new APIs as we mentioned in our previous posts in the new release v7.0.4.

Please create a new thread and create a sample console demo application with v7.0.4, zip it and attach with your post (in the new thread) to show which issues you still find. Also give us some screen shots in MS Excel to better understand the issues you get. We will check it soon.

Thank you.

amjad.sahi · December 21, 2011, 4:51am

Hi,

About this issue, we have the following findings:

1. Auto-filter is a child of the PivotFilter. And Auto-filter can filter by color, etc.

2. PivotFilter cannot be set in the interface of Excel 2003, We will support pivotfilter for Excel 2003 later on.

3,5. Evaluation order, Active, Description are not using for the PivotFilter creation. We could not support it now, we will look into it later on.

4. Please use this sample code for reference:

AutoFilter auto = filter.AutoFilter;

FilterColumnCollection filterColumns = auto.FilterColumns;

FilterColumn filterColumn = filterColumns[0];

Top10Filter top10 = (Top10Filter)filterColumn.Filter;

Console.WriteLine(top10.IsTop);

Also, we recommend you to try the latest fix: Aspose.Cells for .NET v7.0.4.1

Thank you.

licenses · December 23, 2011, 11:02am

Hi,

1. There are two ways to set ‘Percent’ in ‘FilterTop10’ filter:

a) Argument in method ‘filter.AutoFilter.FilterTop10’;

b) Value from enumeratin ‘PivotFilterType.Percent’.

Are there any difference between these options? If they are different, could you create samples for situations When they are used.

2. Must I create a new issue for this problem?

3,5. The property EvaluationOrder is already exist, but not work properly.

Must I create a new issue for this problem?

4. Works fine, but it has strange name ‘Columns’ despite the fact that it used for rows too.

Sample contains a typo: ‘auto.FilterColumns’ instead of ‘auto.FilterColumnCollection’.

Thank you,

Alex Shloma

amjad.sahi · December 23, 2011, 11:56am

Hi Alex,

1) Please see the post that has code segments on how to use these APIs:

https://forum.aspose.com/t/120331

2) As we told you that we will support PivotFilter for Excel 2003 later on, when we incorporate the feature, we will let you know.

3,5)

As we told you that EvaluationOrder may not be working properly. Also, Active, Description are not using for the PivotFilter creation. We do not support it currently, we will look into it later on in future versions.

4) Yes, you are right, you may fix the code accordingly.

Thank you.

licenses · December 26, 2011, 2:53am

Hi Amjad,

1. It's no need more links, I seen your previous posts. The question was not 'How can I?', I asked about 'Why is so?'.

2,3,5. I asked because you have closed this issue and I don't know where I can see a status of this problem.

Thank you,

Alex Shloma

shakeel.faiz · December 26, 2011, 7:10am

Hi,

I have checked the issue id attached with this thread: CELLSNET-40147

It is still opened and has not been closed.

However, for your unsolved problems, please create a separate thread for each of them. We will log them and attach the ids to it. You will then be able to keep track of them.

Please be descriptive enough and help the development team with screenshots red-circles etc.

amjad.sahi · January 16, 2012, 4:38am

The issues you have found earlier (filed as CELLSNET-xxxxx) have been fixed in this update.

licenses · January 16, 2012, 7:20am

Hi,

I've checked issue with new 7.1.0 version, but current issue still present.

Thank you,

Alex Shloma

amjad.sahi · January 16, 2012, 7:34am

Hi,

Well, we made some enhancements for pivot tables in this new version v7.1.0.

For issues e.g 3,5)

We will confirm when we fix it completely.

Thank you.

John.He · June 30, 2021, 7:39am

@licenses,
Please try our latest version/fix: Aspose.Cells for .NET v21.6.3 (attached)
Aspose.Cells21.6.3 For .Net2_AuthenticodeSigned.Zip (5.5 MB)
Aspose.Cells21.6.3 For .Net4.0.Zip (5.5 MB)
Aspose.Cells21.6.3 For .NetStandard20.Zip (5.5 MB)

Your issue should be fixed in it.
Let us know your feedback.