The code just loads an excel file with Aspose.Cells and saves it as “bin\Debug\net6.0-windows\result.xlsx”. Open this file, go to sheet 2, select the pivot table and click the “Refresh pivot table” button in the ribbon (hope you find your to it despite my german excel screenshot): an error is shown that the datasource “Database” cannot be found: error.png (52.8 KB)
The reason is that the source document contains a named range “Datenbank”, which spans all data on the first sheet and has a voodoo formula to calculate this range. Don’t know where this originates from as the sheet is rather old. The pivot table points to this range.
Somehow, this range reference is translated from “Datenbank” to “Database”, but the range still has its original name.
When renaming either the original range in “Name manager” to “Database” or entering the target range “Datenbank” in pivot table datasource, it works again. name_manager.png (55.5 KB)
Do you have an idea whats happening here? Is it a bug or something that we could improve. Probably, it would be sufficient to change the name of the original range, but we might have bunch of old documents spread over a lot of customer sites, so it would be hard to pick them all.
I tested your scenario/case using your sample and it works fine. The output XLSX file (attached) is Ok. The named range is not changed and the pivot data is refreshed without error in MS Excel on my end. Could you please open the file in MS Excel in your environment and do you still find the issue?
If you still find the issue, please provide your environment details, MS Excel version and its language settings, etc. Also, provide your output XLSX file. We will check your issue soon. files1.zip (23.1 KB)
@amjad.sahi The issue also happens with the file from your “files1.zip” attachment.
Attached is a file that I created on my side and which also shows the issue. result.zip (23.8 KB)
My excel is “Microsoft® Excel® für Microsoft 365 MSO (Version 2405 Build 16.0.17628.20006) 64 Bit” (german)
I don’t have access to an english excel installation, we would have to setup such an environment.
Thanks for providing output file and further details.
We need to evaluate your issue in details. We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.
Issue ID(s): CELLSNET-55943
You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.
My initial requirement was to refresh the pivot table by code.
This works on my machine even though Aspose.Cells uses a pivot table datasource name “Database”, which appears to be wrong.
But if I use my workaround to set the correct data source name “Datenbank”, then “PivotTable.CalculateData” will simply do nothing (without error).
This is code that refreshes the pivot table and also makes it valid in excel:
Workbook wb = new Workbook("..\\..\\..\\Stundenauswertung Aushilfen.xlsx");
//Replace "Person1" by "Person one":
Worksheet sheet = wb.Worksheets[0];
for (int row = 1; row <= sheet.Cells.LastCell.Row; row++)
{
if (sheet.Cells[row, 0].Value.ToString() == "Person1")
{
sheet.Cells[row, 0].Value = "Person one";
}
}
wb.Worksheets[1].PivotTables[0].RefreshData();
wb.Worksheets[1].PivotTables[0].CalculateData();
wb.Worksheets[1].PivotTables[0].DataSource = new string[] { "Datenbank" };
wb.Save("result.xlsx");
If I move the line of code to set the DataSource before the call to “RefreshData/CalculateData”, then no error is shown, but the pivot table is not refreshed either.
Shouldn’t Aspose.Cells throw an exception about invalid data source?
Workbook wb = new Workbook("..\\..\\..\\Stundenauswertung Aushilfen.xlsx");
//Replace "Person1" by "Person one":
Worksheet sheet = wb.Worksheets[0];
for (int row = 1; row <= sheet.Cells.LastCell.Row; row++)
{
if (sheet.Cells[row, 0].Value.ToString() == "Person1")
{
sheet.Cells[row, 0].Value = "Person one";
}
}
wb.Worksheets[1].PivotTables[0].DataSource = new string[] { "Datenbank" };
wb.Worksheets[1].PivotTables[0].RefreshData();
wb.Worksheets[1].PivotTables[0].CalculateData();
wb.Save("result.xlsx");
Thank you for providing further details and findings. We have logged it with your existing ticket “CELLSNET-55943” into our database. Apparently, it seems to be an issue with German Excel region and language settings. Anyways, please spare us a little time to thoroughly evaluate your issue. Once we have an update on it, we will let you know here.
@wknauf
Thank you for your feedback. The fix will be included in our upcoming release (Aspose. Cells v24.6) that we plan to release in the first half of June 2024. You will be notified when the next version is released.
@wknauf
This issue is caused by an incorrect prefix “_xlnm”. Database is a built-in defined name, which requires the prefix “_xlnm” when we save “Database” defined name to the file, though we can not see the prefix “_xlnm” in MS Excel UI.
Hope helps a bit.
I see. After digging through the xlsx files, I see that in “xl\pivotCache\pivotCacheDefinition1.xml”, there is a difference between the original file and the one written by Aspose.Cells:
So, the name “Datenbank” was probably not choosen by us but was somehow created by Excel when we created the sheet years ago. If we had used a custom name, the problem would not have occurred I assume.
The issues you have found earlier (filed as CELLSNET-55943) have been fixed in this update. This message was posted using Bugs notification tool by johnson.shi