Setting the value for filter in Pivot Table

Aspose.Cells Product Family
1

Hi,


I have a pivot table, which has multiple filters.
Please refer the - ‘Pivot’ tab in the attached excel(Global_Report.xlsx).
The following are the filters in the pivot table - Year, MonthName, InScope, OU.
I need to set the value for Year filter to current year eg: 2012.
MonthName to Jan.
Please note initially all the filters are set to (All).

Please let me know how to go about this using aspose.
Thanks
Shobha
2

Hi Shobha,


Thanks for the template file.

Please see the sample code for your needs for your reference:

Sample code:

string filePath = @“e:\test2\Global_Report.xlsx”;

Workbook workbook = new Workbook(filePath);

PivotTable table = workbook.Worksheets[“Pivot”].PivotTables[0];

PivotFieldCollection pivotFieldCollection = table.PageFields;
//Select the Item
pivotFieldCollection[0].CurrentPageItem = 0;

table.RefreshData();
table.CalculateData();

workbook.Save(“e:\test2\432outGlogbalrepor.xlsx”);

Thank you.
3

Hi,


Thanks a lot for your response.
I tried the sample program.
In the Year filter the value - ‘2012’ is selected, but still the value (All) is getting displayed in the filter instead of ‘2012’.
Please let me know how to display the value which is selected in the filter.
Please refer the attached excel- ‘Pivot’ tab and Year Filter.

Thanks
Shobha
4

Hi Shobha,




I have tested your case, it works fine and the data is filtered fine but still (All) is shown in the pivot filter.

I have logged a ticket with an id “CELLSNET-41367” for your issue. We will look into your issue soon.

Thank you.

5

Hi,


Thanks for your response.
Please update me once the issue is resolved as this is one of the requirements in our application.

Thanks
Shobha
6

Hi,


I selected the Pivot table option - ‘Refresh data when opening the file’. Now the filters are refreshed!!
But i am still facing some issue in selecting a particular value in the filter.
PFA the sample program and excel template.
The below scenario is not working.Please check out the attached program with the excel attached.
If the month is JAN and year is 2013, i want to select 2012 in the Year filter.
If the month is not JAN and year is 2013, i want to select 2013 in the Year filter.
The other filters are working as expected.
Please help me on this.

Thanks
Shobha
7

Hi,


Thanks for the file and sample project.

I have tested your project a bit with latest version v7.4.0.
Could you try our latest version v7.4.0:
http://www.aspose.com/community/files/51/.net-components/aspose.cells-for-.net/entry439948.aspx

If you find it is not working as per your expectation and as per your code segment, please give us two files.
i) Your output file generated by Aspose.Cells v7.4.0.
ii) Your expected file that you can create manually in MS Excel.

We will check your issue further.

Thank you.

8
Hi,

Thanks for your response.
I tried with the latest version Aspose.Cells v7.4.0, unfortunately it is still not working.
I have attached the 2 files :
1) Global_Report_test_aspose_7.4.0.xlsx - output file generated from aspose 7.4.0.
2) Global_Report_test_expected_output.xlsx - output file expected from my program attached in the previous post.

I am facing another issue -
I am trying to open a file which is 7MB using the below line of code and it is throwing System.OutOfMemoryException. Is there any limitation on the size of file in aspose. I am currely using 7.3.3 version.

Workbook workbook = new Workbook("C:\\Global_Report.xlsx");

The excel file - Global_Report.xlsx is 7MB.
Please let me know the limitation in aspose regarding the size of the file as we are expecting a file which could be more than 15MB.

Thanks for your support.

Regards,
Shobha

9
shobha.vinay:
I am facing another issue -
I am trying to open a file which is 7MB using the below line of code and it is throwing System.OutOfMemoryException. Is there any limitation on the size of file in aspose. I am currely using 7.3.3 version.

Workbook workbook = new Workbook("C:\\Global_Report.xlsx");

The excel file - Global_Report.xlsx is 7MB.
Please let me know the limitation in aspose regarding the size of the file as we are expecting a file which could be more than 15MB.


There is no limitation regarding the size of the Excel file that you are loading via Aspose.Cells. But make sure that you have sufficient resource (e.g RAM) to load bigger/huge files. Aspose.Cells uses 10 times or more memory of the original file size to load the file but it is not final and this may well depend on your Excel data or contents, objects etc.

Could you attach your Excel file file here, we will evaluate your issue soon.


Regarding your original issue filed as "CELLSNET-41367", we will provide our next fix v7.4.1 before the end of this week which fixes your issue.

Thank you.
10

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

We have fixed this issue.

Please download and try this fix: Aspose.Cells for .NET v7.4.0.1 and let us know your feedback.

11

hi,


Thanks for fixing this issue, i will check this out.

Thanks
Shobha

12

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

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