Pivot table filtering

Hi,


I am new here and just started evaluating the Aspose Cells dotnet component product.
It seems to be a great tool but I am stuck with pivot table filtering.
I created a simple pivot table and I would like to filter it. I attached the excel file
to this message.

I would like to put a value filter on label “Name” (in my example excel I would like to filter
on “Croissant” and “Doughnut”) and also add the pivot field (“Dummy”) in the pivot table as a “Report filter” so it would appear as a dropdown above the table itself.

What would be the proper way of coding these filters in dotnet (VB or c#)?

I have tried to use the following vb.net code for the value filter. The file was generated
but when I tried to open it in excel, it crashed.

filterindex = pivotTable.PivotFilters.Add(0, Pivot.PivotFilterType.ValueEqual)
filter = pivotTable.PivotFilters(filterindex)
filter.Value1 = “Croissant”

I would really appreciate any help regarding this.

Thanks,

Tamas

Hi,


Thanks for the sample file.

For crashed file issue, could you provide us complete sample code or preferably a sample console application ( you may zip it prior attaching here) to reproduce the issue on our end, we will check it soon. Also provide us the output Excel file that was crashed by opening the file into MS Excel.

Thank you.


Hi,


I have attached a file that contains both the code I was trying to run and
the generated xlsx file.

I hope this helps you to find out what the problem is.

Thanks for doing this!

Tamas
P.S: The Excel I am using is part of MS Office Professional Plus 2010
version: 14.0.7116.5000 (64-bit)
MS Visual Studio 2010 Version 10.0.40219.1 SP1Rel



Hi,

Thanks for the template file and sample code.

After an initial test, I observed the issue by using the sample code provided by you as you mentioned. After adding pivot filtering for the PivotTable, it crashes MS Excel when opening the file into it.
I even tried some options by updating the code segments but to no avail:
e.g:
Sample code:

//.........
filterindex = pivotTable.PivotFilters.Add(0, PivotFilterType.CaptionGreaterThanOrEqual);

filter = pivotTable.PivotFilters[filterindex];
filter.Value1 = "Croissant";
filter.MeasureFldIndex = 0;
filter.AutoFilter.Refresh();

pivotTable.RefreshData();
pivotTable.CalculateData()

I have logged a ticket with an id "CELLSNET-42533" for your issue. We will look into it soon.

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

Thank you.

Hi,


Thank you for analyzing the issue I have found.
As I mentioned earlier, we are currently evaluate Aspose Cells to find out
if it is eligible for us to use it in our products.
Unfortunately the the evaluation period is going to end soon so a decision needs to be made.
Because I would like to request an estimation regarding this issue as soon as possible, preferably by
tomorrow, because this issue is critical, we cannot approve this product unless this is solved.

Thank you for your understanding.



Hi,


Thanks for considering Aspose.

Well, we would love to figure out your issue soon (as we normally do for our clients) but unfortunately, our concerned Chinese developers are on leave for their “Qingming Festival”. Hopefully, they will come back to work tomorrow. Once they are back, we will try our level best to figure out your issue as soon as possible.

Keep in touch.

Thank you.

Hi,


Thank you for your swift response. Well, festivals and holidays are important, so in the
light of this new information, we of course will wait for your developers to return
and provide the requested estimation. :slight_smile:

Thank you for letting me know this.

Kind regards,

Tamas

Hi,


Please let us know if the developers are ready with the estimation.

Thank you & kind regards,

Tamas

Hi,


We will get back to you tomorrow to update you on your issue.

Thank you.

Hi,


Thank you!

Kind regards,

Tamas

Hi Tamas,

Thanks for your posting and using Aspose.Cells.

It is to update you that the ETA for this issue is 15-Apr-2014. Once, we will have some fix or further update for you, we will let you know asap.

Hi,

Thanks for using Aspose.Cells.

Please download and try the fix: Aspose.Cells for .NET v8.0.0.2.

Please change the code in your sample project as follow:


filterindex = pivotTable.PivotFilters.Add(0, Pivot.PivotFilterType.ValueEqual)

filter = pivotTable.PivotFilters(filterindex)

filter.Value1 = “Croissant”



To:

filterindex = pivotTable.PivotFilters.Add(0, Pivot.PivotFilterType.CaptionEqual)
filter = pivotTable.PivotFilters(filterindex)
filter.AutoFilter.Custom(0, FilterOperatorType.Equal, "Croissant")


Hi,


I tried the new version of Aspose Cells and the recommended code
and it works!

Thank you for your assistance!!!

Kind regards,

Tamas Vandorffy

Hi Tamas,


Good to know that you are up and running again. Please feel free to write back in case you have further questions or need our assistance.

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


This message was posted using Notification2Forum from Downloads module by Aspose Notifier.