Update formula does not work properly with xls


#1

Hello,

we are using Aspose Cells for Java (Version 19.10) and it seems that there is a problem with update of formula in excel files of type xls.

After calling calculateFormula() and saving to xls or pdf some formula fields are not resolved.
Doing the same with identical excel file that is based on xlsx the problem does not occure!

I think the underlying problem is that the given excel file has links to other files that do not exist anymore (see Excel Unknown Link.png in zip-file).
It seems that in case of xls these source files will be freshed in case of xlsx there is no refresh.

Obviously we would prefer the result of xlsx variant in xls variant too.

I have added a file to reproduce the problem.
formulaNotResolved.zip (205.0 KB)

Thanks a lot.

Best regards
Matthias


#2

@curmas,

We are able to reproduce the issue as you have described. 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-43036 – Update formula does not work properly with XLS when rendering to PDF

#3

@curmas,

We evaluated your issue further. For the cell Konstanten!C37, with en-US locale the formula in the XLS file is:

=NETTOARBEITSTAGE,DATE-1)*12

In the XLSX file the formula is:

=NETWORKDAYS,DATE-1)*12

It looks like the function NETWORKDAYS is expressed as NETTOARBEITSTAGE in your region so the two files look identical. However, the function names saved in the template files are different in fact. When you open the XLS file in MS Excel with en-US locale and re-calculate the formula, it will become #NAME? too.

So the issue was locale-relevant and caused by the XLS file itself. Maybe you created it by program and set the function as NETTOARBEITSTAGE instead of NETWORKDAYS in the program. You should correct it and re-generate the XLS file.


#4

Thanks for your efforts


#5

@curmas,

You are welcome. And, hopefully you can cope with it by correcting the file as suggested.