Aspose cells getValue method reurns null or empty string randomly when empty string exists in the cell

Aspose.Cells Product Family
1

Hello,

We are using the cells.getType() method to get the value of a particular cell

which has the formula

=IF(F36=F38,"","FX"&TEXT(MAX($E253:E253)+1,"000"))

As can be seen the formula puts empty string in some cases.

When there is an empty string then getType sometimes returns null( value =3) and sometimes it

returns empty string(value=5) .

What is the reason for this randon behaviour ? and how to keep the behaviour consistent?

aspose cells version used = 8.4.2

2

Hi,


Please try our latest version/ fix: Aspose.Cells for Java v8.5.0.4

If you still find any issue, kindly provide us sample JAVA program (runnable) and template Excel file to reproduce the issue on our end, we will check it soon.

Thank you.

3

Hi,

Thanks for using Aspose.Cells.

For existing excel template file saved by ms excel, commonly the value
is saved as null for empty string. That is, Cell.getType() will return
null for empty string value after being read from a template file. If
the formula has been re-calculated by code such as calling
Workbook.calculateFormula(), as the formula itself denotes, the value
will be set to empty string value so that its type becomes string.

4

Hello,
Tried with the latest version 8.5.0.4
Still face the issue. However if we use a version being used previously
7.3.2.1 then the issue is not there.
Has a regression been introduced in the latest versions?
Can you please check?
We did a license upgrade recently and started using the 8.5.x version and now
lots of code broke because of this issue.

Thanks

5

Hello,

For the same code version, same excel, same formula
for version 7.3.2.1 and 8.5.0.4 the behaviour is different.
For 7.3.2.1 it is identified as null and for 8.5.0.4 it is identified as
empty string.
Any explanation for this behaviour?

6

Hi,

Thanks for your posting and using Aspose.Cells.

Please give us your template file and sample code, we will test it with different versions to look into this issue.

7

Hello,

Please find attached sample code to demonstrate the difference in behaviour between the two versions which is causing regression issues for us.

Regards

Abhijit

8

Hi,

Thanks for your posting and using Aspose.Cells.

We were able to observe this difference between the older and newer version after executing your sample code with your source excel file. The older version returns null for the value type while newer version returns string for the value type. However, it might be a desired behavior.

We have logged this issue in our database for investigation. We will look into it and fix this issue. Once the issue is resolved or we have some other update for you, we will let you know asap.

This issue has been logged as

  • CELLSJAVA-41467 - Older version returns value type as null but newer version returns value type as string.

I have also shown the console output of your code with older and newer version for a reference.

Console Outputs
7.7.0.0
Book1.xlsx
Aspose.Cells.Cell [ M13; ValueType : IsNull; Formula:=IF(M12=12,12,"") ]
Cell type is null

8.5.2.0
Book1.xlsx
Aspose.Cells.Cell [ M13; ValueType : IsString; Value : ; Formula:=IF(M12=12,12,"") ]
Cell type is string


9

Hi,

Thanks for using Aspose.Cells.

For such kind of situation(reading empty string value of formula’s
calculated result), we have changed the behavior in recent versions of
our component so that user can get the correct result produced by ms
excel without calculating formulas again after loading it by
Aspose.Cells. And some users and cases have used and depended on this
behavior.


So we cannot revert this behavior back to that in old versions. Please change your code to make your application adapt to current behavior of reading value from template files.

Also one notable thing users that use such kind of formula and its value: when loading a template file which created by ms excel, commonly the formula has been calculated by ms excel and the calculated result has been saved in the file, so the value is not null. If the formula is created by user's program with Aspose.Cells and user has not invoked CalculateFormula() methods, then the cell value will keep null util user calculate it explicitly in the program.