We are using Aspose as a tool for rendering excel reports in one of our applications and users have reported few issues with current version 17.5.0 of tool we are using. Users are forced to refresh pivot tables manually once report is created based on template & feed data to see the results in Pivot table.
Is this known issue with this version or it could be that our code is not invoking the right Aspose method to refresh the excel post generation?
Thanks for providing us details.
Could you try to refresh and calculate data for the PivotTables (in different worksheets) by Aspose.Cells APIs before saving the file(s), see the sample code segment for your reference:
e.g
Sample code:
.............
foreach (Worksheet worksheet in workbook.Worksheets)
{
foreach (PivotTable pivotTable in worksheet.PivotTables)
{
pivotTable.RefreshData();
pivotTable.CalculateData();
}
}
workbook.Save("e:\\test2\\out1.xlsx");
Alternatively, you may also try to add a line before saving to Excel file, see the line of code for your reference:
e.g
Sample code:
..... //This will ask MS Excel to refresh PivotTable when opening the file into it, so you do not need to manually //refresh the Pivot tables. pivotTable.RefreshDataOnOpeningFile = true;
Let us know if you still find the issue.
Thanks for your quick response. Will update once we check the code and compare it with your suggested sample code.
This refresh issue seems to be happening only some of the report templates and not all. Is there known issue with refresh in some scenarios like if SUMIF formula used in pivot table for example?
Similarly for some report templates the original formatting is getting lost or reset forcing users to manually update the format again after report is generated. Any inputs?
Another issue which is reported by multiple users is that Pivot tables filter is getting reset. That is let’s say user had set few filters on a pivot table in report template but when report is generated using Apsose the filters are reset to all or none. Given one of the examples of such issue with filters by our users. I have seen a post on similar pivot tables filter reset issue on your site but the comments indicated that it is fixed in one of the later versions. Is it possible that this issue resurfaced again in the version we are using 17.5.0 or it is still happening in some scenarios but not all?
Example:
The pivot filter has 3 options:
• Valid
• Invalid
• (Blank) (the Excel standard one)
The template has data with both values, “Valid” and “Invalid”; One pivot has “Valid” selected, and the other has “Invalid”
When the report is generated, and the data has no “Invalid” entries, the filter that was set to “Invalid” swaps to selecting “Valid”.
One of our users suggested a work-around; we don’t know if it works each time though.
It goes as follows: when setting the filter of the pivot to e.g. “Invalid”, he suggests to also select “(blank)”. That seemed to work in one case.
Thanks for providing us further details.
We recommend you to kindly try our latest version/fix: Aspose.Cells for .NET v18.2.2 if it makes any difference:
If you still find any issue with v18.2.2, kindly do create a sample console demo application (runnable) and post us here to reproduce the issue for every distinct issue, we will check it soon. Also, attach your template Excel files (input and output file). You may zip the files and sample project/code (without including Aspose.Cells assembly) and attach it here.
Ok. For the third issue the link shows similar issue was fixed earlier by your team. Pivot table filter value not retained upon refresh
Is it possible that these issues are happening for a specific version of excel?
Well, the issue might be template specific, so might need your template file(s) to evaluate it. We did include enhancements in latest versions/fixes (e.g v18.2.x) regarding PivotTables, so kindly do try our latest version/fix first.
Ok. Does Aspose 17.5.0 work equally well with Excel 2013 and Excel 2010?
Yes, Aspose.Cells for .NET v17.5.0 works well with all MS Excel versions (e.g 97-2003, 2007, 2010, 2013/2016).
we have been testing with Aspose latest version and have one issue with Pivot filters to report. Pivot table has two filter fields and if there is data row based on filter conditions Aspose report generated is showing the data correctly. But if there is no data matching filter conditions then Apsose report generated is showing data not matching with filter conditions or row with text (blank) (blank) (blank)… I can share the sample template if required.
Generally, Aspose.Cells follows MS Excel standards and specifications when refreshing or calculating PivotTables report. We appreciate if you could create a sample console demo application (runnable) with template file(s) and post us here to reproduce the issue, we will check it soon. Please also provide your template Excel files (input and output file). Moreover, provide a file with your desired PivotTable in tact (you may manually create or save the file in MS Excel) . You may zip the files and sample project/code (without including Aspose.Cells assembly) and attach it here.
our stand alone application to create such demo application needs some fix so can’t provide that but can provide template before (input) and report after (output). You can use the input file to put it through your Aspose and look at the output? It should not be too difficult for you to reproduce the issue I think. AsposeUploadFiles.zip (64.1 KB)
Thanks for the template files.
I evaluated your template files by opening into MS Excel manually and applied different report filtering options. I am afraid, I could not evaluate what is your desired PivotTable with the specified filtering options applied to show your desired data in the report even I played out different filtering and other PivotTable options manually in MS Excel. We are not entirely sure how did you write code to manipulate the PivotTable in the template file either. So, it is requested to kindly provide the following artifacts to precisely evaluate your issue on our end:
Please note, if something is not possible in MS Excel manually, I am afraid, it cannot be done via Aspose.Cells APIs too.
To fix your issue, we need to reproduce the issue on our end first. We appreciate your cooperation in this regard.