Hi,
In Excel 2007 was changed mechanism of filtering for fields in pivot table, but I did not find any way to use it in last version of Aspose.Cells.
Could you fix the compatibility with last MS Office formats or show me how to use it, if such methods already exist?
http://msdn.microsoft.com/en-us/library/bb245128%28v=office.12%29.aspx
I use Aspose.Cells v7.0.3.3.
Thank you,
Alex Shloma
Hi Alex,
I have logged a ticket for it with an id: CELLSNET-40147. We will investigate it and get back to you soon.
Thank you.
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);
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).
Thank you,
Alex Shloma
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).
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.
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
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.
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
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;
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;
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
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.
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
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.
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);
Thank you.
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
Hi Alex,
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.
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
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.
Hi,
Thank you,
Alex Shloma