Pivot table filter value not retained upon refresh

Aspose.Cells Product Family
1
I am using aspose cells 8.7.1.4

Please see attached spreadsheet.

I run the following code on it:

Workbook oWorkbook = new Workbook(@"c:\temp\book7.xlsx");
Worksheet oSheet = oWorkbook.Worksheets["Sheet2"];
oSheet.Cells.DeleteRow(4);

oWorkbook.Worksheets["Sheet1"].PivotTables["PivotTable1"].RefreshData();
oWorkbook.Worksheets["Sheet1"].PivotTables["PivotTable1"].CalculateData();
oWorkbook.Save(@"c:\temp\book8.xlsx");

When I open book8.xlsx, I find that 'test4' has been cleared from the filter (it is not selected, and in fact does not appear in the drop down at all) and all rows of data appear in the pivot table. This is inconsistent with Excel's behaviour when I delete row "test4" and refresh the pivot table. Excel retains "test4" as selected in the filter and the pivot table contains no rows, which is what I require.

Please note, setting the pivot table to refresh on open instead of doing it in code is not a workaround for me, as I need to perform the refresh in code so I know how many rows are going to be in the pivot table.

Could you please take a look.
2

Hi,

Thanks for your posting and using Aspose.Cells.

We were able to replicate this issue after executing your sample code with the latest version and found that pivot table filter value is not retained upon refresh as per your description.

We have logged this issue in our database for investigation. We will look into it and fix this issue. Once the issue is resolved or we have some other update for you, we will let you know asap.

This issue has been logged as

  • CELLSNET-44319 - Pivot table filter value is not retained upon refresh

3

Is there any workaround you might be able to suggest? As this issue is critical to us.


Is it possible for me to save the filter values before doing the refresh, and then reapply them afterwards?

Thanks
4

Hi Sarah,


I am afraid, the ticket attached to this thread is currently pending for analysis and is in the queue with other priority tasks therefore unless we have analyzed the presented scenario we cannot suggest a workaround for the situation or provide an estimated time frame for the fix. Please spare us little time for analysis. In the meanwhile, we will keep you posted with updates in this regard.
5

Hi, is there any update on this bug please? Or a workaround?

6

@sarah.hulbert

Please download and try the latest version and let us know your feedback.

If your issue still occurs, please provide us your sample code as well sample Excel file once again. We will re-look into this issue and help you asap.

7

Hi Shakeel, this issue is still occurring with the latest version.

See attached file and original code.

Book7.zip (8.8 KB)

Could you please look into it.

Thanks

8

@sarah.hulbert

Please check the Microsoft Excel 2016 generated Excel file (Download Link) and Aspose.Cells generated Excel file (Download Link). In both files, there is no test4. One has test1 and the other has (All).

Download Link:
Output Excel files by Microsoft Excel 2016 and Aspose.Cells.zip (18.6 KB)

C#

Workbook oWorkbook = new Workbook(@"book7.xlsx");
Worksheet oSheet = oWorkbook.Worksheets["Sheet2"];
oSheet.Cells.DeleteRow(4);

Worksheet ws = oWorkbook.Worksheets["Sheet1"];

PivotTable pt = ws.PivotTables[0];

pt.RefreshData();
pt.CalculateData();

oWorkbook.Save("Output by Aspose.Cells.xlsx");
9

@sarah.hulbert

Please download and try the following fix for your issue CELLSNET-44319 and let us know your feedback

10

The issues you have found earlier (filed as CELLSNET-44319) have been fixed in this Aspose.Cells for .NET 18.2 update.

Please also check the following article:

11

Hi Amjad, The filter reset issue does not seem to be resolved fully even with 18.2. It seems to be working only when “Refresh data when opening the file” option in Data tab of Pivot table options is unchecked in the original template. If this option is checked then Aspose is resetting the filter when input data does not have data matching filter condition. Even for a template for which the output report came all right (with this option unchecked) the filter is getting reset even with excel manual refresh (maybe the refresh is disturbing some Aspose logic) when there is no data matching filter condition.

I have attached zip file with template and input / output files. This template had the above mentioned option unchecked and so the expected and actual results matched. your team can test the same template with this option checked and see how the results are.AsposeFiles.zip (59.0 KB)

13

@rajendrak

Thanks for using Aspose APIs.

Please download and try the following most recent version and see if it does fix your issue. Let us know your feedback.

We have checked your attachment, it only contains Excel files. Please also provide us the runnable sample code and preferably console application that we could run and generate the actual Excel files (as you have shared) at our end.

Besides, we see, there is no pivot table in your these expected Excel files. It means, pivot table is lost in the expected Excel file. Let us know how can we create such expected file manually using Microsoft Excel. Or have you drawn them by yourself.

  • Sample_PivotTemplate_OutputReport_20180315_Expected.xlsx
  • Sample_PivotTemplate_OutputReport_20180316_Expected.xlsx
14

Hi Shakeel, Thanks for your response. I think it is better if you can validate with 18.3.6 on your side and confirm that the issue is resolved as upgrading to a new version requires us to go through bit time taking process and we just upgraded to 18.2 version.

The simple test you can do is, use the original template Sample_PivotTemplate_Original.xlsx I provided to create an output report using input file SampleFeedFile_Input_20180315.csv (the data from this input file should be copied to Data tab of the template before report based on the template is generated). Please test once with “Refresh data when opening the file” option unchecked and once with this option checked in the template to observe the difference in output report. Then maybe you will be able to share reason for difference in output and if there is any issue with Aspose 18.2 version. Apologies unable to provide executable providing demo of output. Can you please test with 18.3.6 and let us know? Thanks again.

15

@rajendrak

Thanks for using Aspose APIs.

We were able to observe this issue as shown in the following screenshot and recorded it in our database for further investigation and resolution. Once, we will have some fix for you, we will update you asap.

This issue has been logged as

  • CELLSNET-46033 - Page Field named Absent item Yes is lost on Refresh even when - Show items with no data - is enabled

Screenshot:

16

Thank you for the update. Appreciate it.

1 Like
17

@rajendrak

Thanks for using Aspose APIs.

We will not keep the missing data when refreshing the PivotTable for now. We reconstruct the PivotTable only according to the real data.

So if you call the PivotTable.RefreshData(), some missing data will be lost.

If you want to keep it, you can set PivotField.ShowAllItems to true and do not call the method PivotTable.RefreshData().

The testing code is as follows. Here, you can discover the output will keep the missing data:

C#

Workbook wb = new Workbook(filePath + "SampleFeedFile_Input_20180315.csv"); 
Range source = wb.Worksheets[0].Cells.CreateRange("A1", "C4"); 

Workbook wb2 = new Workbook(filePath + "Sample_PivotTemplate_Original.xlsx"); 
Range dest = wb2.Worksheets["Data"].Cells.CreateRange("A1", "C4"); 
dest.CopyData(source); 
PivotTable pt = wb2.Worksheets[0].PivotTables[0]; 
             
pt.PageFields[0].ShowAllItems = true; 
pt.RefreshDataOnOpeningFile = true; 
wb2.Save(filePath + "out_Refresh.xlsx"); 

pt.RefreshDataOnOpeningFile = false; 
wb2.Save(filePath + "out_noRefresh.xlsx");
18

Thanks for your response. But if code doesn’t call RefreshData() then the pivot table results won’t be refreshed and the results won’t reflect the data in the report? The original template may have some data based on which Pivot table will show results but when actual report is generated the pivot is expected to show results based on data copied into data tab.

19

@rajendrak

This is the limitation of Aspose.Cells as we reconstruct Pivot Table on RefreshData() method. But when you use RefreshDataOnOpeningFile property, you ask Microsoft Excel to refresh your data whenever Excel file is opened.

20

Hi Shakeel, Trying to make sure your team actually got the issue. Attached zip file has sample output file for both scenarios (with refresh data option selected and not selected) along with original template. You may notice that when this refresh option is not selected, the output file shows the result which is similar to excel whereas when the option is selected the output file result is such that it is changing the pivot filter value to some other value and showing results based on that which is not correct.

When you say “We will not keep the missing data when refreshing the PivotTable for now. We reconstruct the PivotTable only according to the real data.” can you please explain looking at result in file ‘Sample_PivotTemplate_OutputReport_20180315_Actual_WithRefreshDataOptionSelected.xlsx’ ? The original template had Absent as Yes but this output selected No since the data did not have that value. The expectation is that pivot table should be constructed based on real data which is what you are also saying but the question here is why Aspose is resetting the filter value to some random value instead of leaving it blank like in first scenario. Rather than limitation it might be a logical bug? PivotFilter_Issue_SampleOutputFiles.zip (29.8 KB)

21

@rajendrak

Thanks for your feedback and using Aspose APIs.

We have logged your comment and concerns for further evaluation and investigation. We will look into it and help you asap. Once, there is some news for you, we will update you in this topic.