Calculation with Aspose Cells breaks table formula, corrupts workbook


#1

Hi,

Using Aspose Cells for Java, version 19.8.

The provided file input.xlsx is loaded, recalculated and saved using the following code:

System.out.println(CellsHelper.getVersion());
Workbook workbook = new Workbook("input.xlsx");
workbook.calculateFormula();
workbook.save("output.xlsx");

Attempting to open the recalculated workbook with Microsoft Excel results in either a corruption error, or a straight crash of the Excel process.

Inspecting and comparing input.xlsx and output.xlsx files, some formulas containing structured references seem to have been broken by Aspose Cells. For example, checking table14.xml in the xlsx content, the original content for column with id 8 (“Inflation - No shock”) was:

<tableColumn id="8" name="Inflation - No shock" dataCellStyle="SF_FORMULA"><calculatedColumnFormula>IF(SFInterface[[#This Row],[Calculation of Inflation rates performed]],SUMIFS(CalscKobe[Inflation - No shock],CalscKobe[Currency],SFInterface[[#This Row],[Currency]],CalscKobe[Year],SFInterface[[#This Row],[Maturity of cashflow element - in years]]),SUMIFS(liability_cf_kobe[Inflation precalculated],liability_cf_kobe[Cashflow element ID],SFInterface[[#This Row],[Cashflow element ID]]))</calculatedColumnFormula></tableColumn>```

After the recalculation with Aspose Cells, the formula becomes:

<tableColumn id="8" name="Inflation - No shock"><calculatedColumnFormula>IF(SFInterface[[#This Row],[Calculation of Inflation rates performed]],SUMIFS(CalscKobe[Inflation - No shock],CalscKobe[Currency],SFInterface[[#This Row],[Currency]],CalscKobe[Year],SFInterface[[#This Row],[Maturity of cashflow element - in years]]),SUMIFS([Inflation precalculated],[Cashflow element ID],SFInterface[[#This Row],[Cashflow element ID]]))</calculatedColumnFormula></tableColumn>

Note that Aspose Cells has removed the qualifying references to table liability_cf_kobe. As a result the formula now contains column references that can not be resolved in the current table.

From trying older builds, it seems that this regression has been introduced in Aspose Cells for Java, release 18.5.

Kind regards,
Taras

case.zip (313.9 KB)


#2

@TarasTielkes,
We were able to observe the issue but we need to look into it more. We have logged the issue in our database for investigation and for a fix. Once, we will have some news for you, we will update you in this topic.

This issue has been logged as

CELLSJAVA-43000 – CalculateFormula() corrupts the Excel file

#3

@TarasTielkes,
This is to inform you that we have fixed your issue (logged earlier as “CELLSJAVA-43000”) now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.


#4

@TarasTielkes,

Please try our latest version/fix: Aspose.Cells for Java v19.8.6 (attached)

Your issue should be fixed in it.

Let us know your feedback.
aspose-cells-19.8.6-java.zip (6.7 MB)


#5

Hi @Amjad_Sahi,

Our initial testing of 19.8.6 is positive, thank you for the quick turn-around time.

Kind regards,
Taras


#6

@TarasTielkes,

Good to know that your issue is sorted out by the new fix/version. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.