VALUE(A1) throws Exception when cell is null

Aspose.Cells Product Family
1

When trying the following with Aspose.Cells :
A1 is blank
A2 contains '=VALUE(A1)'

Then Excel will simply return 0, whereas Aspose.Cells throws the following Exception:
Exception in thread “main” com.aspose.cells.FormulaCalcException: Sheet1!A2[=VALUE(A1)]: String index out of range: 0
at com.aspose.cells.Cell.g(Unknown Source)
at com.aspose.cells.fP.a(Unknown Source)
at com.aspose.cells.fP.a(Unknown Source)
at com.aspose.cells.eG.a(Unknown Source)
at com.aspose.cells.Workbook.calculateFormula(Unknown Source)

Although the Excel behavior is open for discussion (i.e. returning 0 for a blank cell), currently the Aspose.Cells behavior differs from Excel (in fact, it throws an Exception during workbook.calculateFormula(false);

With kind regards,

Barry

2

In addition to the VALUE() function, it seems that the IF() function also does not work very well with blank cells. I will create a separate post for this.

Barry

3

Hi,

Please try the attached latest version as I tested your scenario with a sample file using it, it works fine.

Please try it and let us know if it works fine.

If your still find the issue, kindly do post your sample file with sample code here, we will check it soon.

Thank you.

4

I am already using Aspose.Cells 2.4.0.5.

The following snippet exhibits the behavior:

Workbook workbook = new Workbook();
Worksheet sheet = workbook.getWorksheets().getSheet(0);
sheet.getCells().getCell(“B1”).setFormula("=VALUE(B2)");
workbook.calculateFormula(false);
System.out.println(sheet.getCells().getCell(“B1”).getValue());

results in:
Exception in thread “main” com.aspose.cells.FormulaCalcException: Sheet1!B1[=VALUE(B2)]: String index out of range: 0
at com.aspose.cells.Cell.g(Unknown Source)
at com.aspose.cells.fP.a(Unknown Source)
at com.aspose.cells.fP.a(Unknown Source)
at com.aspose.cells.eG.a(Unknown Source)
at com.aspose.cells.Workbook.calculateFormula(Unknown Source)

Thanks for the great support !

Barry

5

Hi,

Thanks for the sample code.

Now, I can find the issue as you have mentioned. I was checking with empty cells.

I have logged your issue into our issue tracking system with an id: CELLSJAVA-20069. We will figure your issue out soon.

Thank you.

6

Thanks for confirming this.

Can you please also have a look at the problem I raised with the IF() statement ? ( IF() function fails to calculate for blank cells ).

It might be related to this issue, but I’m not sure.

With kind regards,

Barry

7

Hi,

“Can you please also have a look at the problem I raised with the IF()
statement ? (

).”

Yes, this issue to some extent is related to it. We will figure out your both of issues soon. Please also follow up in other thread too.

Thank you.

8

Hi,



We have fixed your issue for calculating formulas, please try the attached version.



Thank you

9

The issues you have found earlier (filed as 20069) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by aspose.notifier.