"PivotTable.RefreshData" crashes: "Formula is empty.(Based on PivotCalculatedItem[])"

Aspose.Cells Product Family
1

This is a followup to https://forum.aspose.com/t/named-range-for-pivot-table-is-translated-when-opening-saving-file-with-aspose-cells/285132: I updated the sample to 24.6 and added a call to “PivotTable.RefreshData/CalculateData”.
AsposeCellsPivot_2024-06-20.zip (27.3 KB)

This causes an exception in my sample sheet - with 24.5 it did not happen:

Aspose.Cells.CellsException
  HResult=0x80131600
  Message=Formula is empty.(Based on PivotCalculatedItem[])
  Source=Aspose.Cells
  StackTrace:
   at ????.?(StringBuilder ?)
   at ????.?(Int32 ?, String ?)
   at ????.???????(???? ?)
   at ????.?(PivotTableRefreshOption ?, ???? ?, Boolean ?)
   at ????.?(Boolean ?, Boolean ?, PivotTableRefreshOption ?)
   at AsposeCellsPivot.Form1.button1_Click(Object sender, EventArgs e) in C:\Temp\AsposeCellsPivot\Form1.cs:line 32

I drilled it down to a value “Formel1” (translates to “Formula1”), which is found in the value list of the field “Personalnummer”, as shown in the left part of the screenshot.
formel1.png (66.6 KB)

This “Formel1” can be found in “Fields, Items & Sets”, item “Calculated Item”, and there I can delete it (right part of my screenshot).
An english screenshot of this part of the menu is found at https://www.exceldemy.com/wp-content/uploads/2022/01/7.-Calculated-Field-in-Pivot-Table.png
I don’t know the purpose of “Formel1”, it might exist since ages in the excel sheet. So it might be an old error.

My questions:
a) why does excel not report any error when calculating the table, but Aspose.Cells does? If you report an error: could you improve the error message to point to the culprit?
b) how can I access those “Calculated items” with Aspose.Cells" API? I just want to check how the item looks like - and maybe I could create cleanup code, as we probably have a bunch of templates created from the same ancestor.

Best regards

Wolfgang

2

@wknauf,

Thanks for the sample Excel file and code snippet with details.

After an initial test, I was able to reproduce the issue as you mentioned by using your template XLSX file. I found an exception ‘Formula is empty.(Based on PivotCalculatedItem[])’ when refreshing pivot table.

We require thorough evaluation of the issue. We will then evaluate and clarify your queries (at the bottom). 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-56029

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.

3

I see that the linked issue is resolved. What was the outcome?

4

@wknauf
The fix will be included in our upcoming release (Aspose.Cells v24.7) which is due in the second week of July this month. You will be notified when the next version is released.

5

@John.He Thanks! Do you have any details on the issue? Was this an error (invalid formula) in our excel file that is somehow “ignored” with the new Aspose.Cells version? Or was it a bug on your side?

6

@wknauf,

It was logged as a bug in Aspose.Cells APIs which is fixed now.

1 Like
7

@wknauf
There is a strange calculated item “Formel1” in the pivot field “Personalnummer”.
The formula of “Formel1” is empty.
The empty formula of calculated item caused this exception.
But it’s strange that it’s filtered again, so it’s completely redundant.

8

So you ignore this formula now?

As I wrote before I have no idea why this formula exists, it might be corrupt data which was created ages ago in this sheet.

9

@wknauf,

We will provide complete details and clarify how we deal with it. We will get back to you soon.

10

@wknauf
The calculated item is filtered in the template file, so we will work the same as MS Excel to ignore it as well.

11

The issues you have found earlier (filed as CELLSNET-56029) have been fixed in this update. This message was posted using Bugs notification tool by johnson.shi

12

Perfect, I can confirm that my sample works now.

13

@wknauf,

Thank you for your feedback, and it’s good to know that your issue has been resolved by the new version. Please feel free to write back to us if you have any further queries or comments.