Hello,
we ran into an issue with Excel pivot tables not correctly updating when dates are involved using Aspose Cells for Java.
The issue is exactly described CELLSNET-47633 https://forum.aspose.com/t/pivot-table-with-dates-does-not-correctly-update/219091 and was fixed for .NET version 20.10.
Can the fix bei applied to the Java version of Aspose Cells? Please let me know if you need any more informations on this.
Kind regards
Stefanie
@stefgt,
Aspose.Cells for Java is ported from Aspose.Cells for .NET hence this issue should be resolved in it. Your issue needs further investigation therefore you may please share your sample Excel file, runnable console application, program output file and expected output created by MS Excel. We will look into it and share our feedback soon.
Hello,
We are using a Pivot Chart in which a Date Column from the data source is used for the rows of the Pivot Table. The data from the data source is updated dynamically and afterwards the Pivot Table and Chart are updated via calls to the update methods.
Please have a look at the attached example. In the example the data source is cleared and refilled with 10 new data rows. After updating the Pivot Table it only has 8 entry, but also showing a grand total of 10 and the Pivot Chart is showing 8 bars instead of 10.
Kind regards
Stefanie
AsposeCells.zip (56.7 KB)
@stefgt,
We have logged the issue as “CELLSJAVA-43418” in our database for investigations. Once we will have some news for you, we will update you in this topic.
Please specify your changed/updated Table/List after you have updated the Table/List object via PivotTable.changeDataSource. See the following code segment (notice the bold line), it will work as expected as I tested using the line of code:
e.g.
Sample code:
…
WorksheetCollection sheets = workbook.getWorksheets();
for (int i = 0; i < sheets.getCount(); i++) {
PivotTableCollection pts = sheets.get(i).getPivotTables();
for (int j = 0; j < pts.getCount(); j++) {
PivotTable pt = pts.get(j);
pt.changeDataSource(new String[]{“ChartData”});
pt.refreshData();
pt.calculateRange();
pt.calculateData();
}
…
By the way, I did test your scenario/ case using our latest version/fix (Aspose.Cells for Java v21.3) with the added line and it works fine.
Let us know if you still find any issue.
Hello,
thank you for your help. Unfortunately there still is an issue here. The number of entries is correct using your code, but the calculated dates are wrong. In the given example the output contains entries for April and May, but in the data source are no entries for that months. After using the refresh data functionality in excel the dates are correct. Please see the attached screenshots.
Kind regards
Stefanie
screenshots.zip (52.6 KB)
It looks like you are still using older version of the APIs. Please try using our latest version/fix and it will work as expected.
I tested it with Aspose.Cells for Java 21.3 and still encountering this problem.
Please find attached the output Excel file which is ok. Please open it into MS Excel and let us know if you find any issue.
out1.zip (19.6 KB)
Also, test your code in a separate console application using Aspose.Cells for Java v21.3 and generate the output files. If you still find the issue, kindly do share your output files and environment details, we will check it soon.
Hello,
Please find attached output file that contains the error.
outputFile.zip (19.7 KB)
What environment details do you need?
Please share your regional settings/locale settings of the OS where you are processing your sample code, we will investigate further. Also add a line to save to PDF file format and share the output PDF for reference. By the way, did you open my attached file into MS Excel and if it is fine tuned?
Hello,
my regional settings and OS:
Region: Germany
Regional format: German (Germany)
Time Zone: UTC+1:00 Europe/Berlin
OS: Windows 10
Here is the PDF you asked for.
outputFile.zip (67.2 KB)
Yes, I opened your attached file and it is all fine.
Kind regards
Stefanie
Could you please try specifying the region and try your scenario/ case if it works:
e.g.
Sample code:
Workbook workbook = new Workbook(stirngFilePath);
workbook.Settings.Region = CountryCode.Germany;
.............
Let us know if you still find the issue.
I tried this code:
WorkbookSettings settings = workbook.getSettings();
settings.setLocale(Locale.GERMAN);
settings.setRegion(CountryCode.GERMANY);
settings.setLanguageCode(CountryCode.GERMANY);
Still the same probleme. Here is the output:
files.zip (86.9 KB)
We are sorry that it still does not work on your end. As we have already logged a ticket with an id “CELLSJAVA-43418”. Please spare us little time so we could evaluate your issue thoroughly.
Once we have an update on it or we figure it out, we will let you know.
@stefgt,
Please try our latest version/fix: Aspose.Cells for Java v21.3.6 (attached)
aspose-cells-21.3.6-java.zip (7.2 MB)
Your issue should be fixed now.
Let us know your feedback.
Hi,
I tried v21.3.6 and unfortunately I am still seeing the same problem.
Here is my output with v21.3.6
files.zip (86.9 KB)
Could you also share your sample code (runnable) that you are using with latest fix to generate the output file(s). This will help to trace the issue and we could fix the issue (if found) soon.
I did test your sample code with your template file and it works fine on my end. Please find attached the output files for your reference.
files1.zip (112.7 KB)
I have logged your output files and sample code with your existing ticket. We will investigate and look into it further on why it is not working on your end. Once we have an update on it, we will let you know here.