Worksheet.calculateFormula method produces results of double type instead of NULL and Date in Java

When using simple formulas that simply refer to another cell, I get wrong results when the referred cell is empty or contains a date. Here is a code sample:

Workbook book;
String a1 = “=A1”; // A1 has a date type value
String a2 = “=A2”; // A2 is empty, NULL type
try (InputStream stream = getFileAsStream(“file.xlsx”)) {
book = new Workbook(stream);
Worksheet sheet = book.getWorksheets().get(0);

// because of the problem described in the following thread:
// Worksheet has no method calcualteFormula(String formula- CalculationOptions options)
// a formula has to be written into a cell to be evaluated
Cell lastCell = worksheet.getCells().getLastCell();
Cell unoccupiedCell = worksheet.getCells().get(lastCell.getRow() + 1,
lastCell.getColumn() + 1);
unoccupiedCell.setFormula(a1);
worksheet.calculateFormula(new CalculationOptions(), true);
// retrieving the result
Object result = unoccupiedCell.getValue();
System.out.println(result);
}

When A1 is referred, which contains a date, result is a double (with a value 36892.0 instead of 01-01-2001). When A2, which is empty, is referred, result is 0.0, not null.

Reading the same values directly from the same cells (sheet.getCells().get(“A1”).getValue()), not through the formulas produces correct results of DateTime type and null respectively.

This behavior is considered wrong in our context.

Thanks.

Hi,

Thanks for your posting and using Aspose.Cells.

Please also provide us your file.xlsx which you are using in your code. Also your code gives compile time error, please re-check it and fix it.

We will then investigate your issue and help you asap.

There you go.

Workbook book;
String a1 = “=A1”; // A1 has a date type value
String a2 = “=A2”; // A2 is empty, NULL type
try (InputStream stream = new FileInputStream(new File(“c:\file.xlsx”))) {
book = new Workbook(stream);
Worksheet sheet = book.getWorksheets().get(0);

// because of the problem described in the following thread:
// Worksheet has no method calcualteFormula(String formula- CalculationOptions options)
// a formula has to be written into a cell to be evaluated
Cell lastCell = sheet.getCells().getLastCell();
Cell unoccupiedCell = sheet.getCells().get(lastCell.getRow() + 1,
lastCell.getColumn() + 1);
unoccupiedCell.setFormula(a2);
sheet.calculateFormula(new CalculationOptions(), true);
// retrieving the result
Object result = unoccupiedCell.getValue();
System.out.println(result);
}

Hi,

Thanks for your posting and using Aspose.Cells.

Dates are stored as numbers. If you set any date’s format as General, you will see it will convert to number. To correct it, you will have to set the Style.Custom or Style.Number property and you can get Style object of any cell using Cell.getStyle() method.

For more information, please see this article.

( Data Formatting|Documentation )

I have tested this issue with the following sample code. I have also shown its console output below and attached the file2.xlsx and output check.xlsx and screenshot for your reference.

I am unable to find any problem. If you still find any issue, then please provide us some code for which results of Aspose.Cells and Microsoft Excel do not match. It will help us investigate this issue further.

Java

Workbook book;
String a1 = “=A1”; // A1 has a date type value
String a2 = “=A2”; // A2 is empty, NULL type
try (InputStream stream = new FileInputStream(new File(“file.xlsx”))) {
book = new Workbook(stream);
Worksheet sheet = book.getWorksheets().get(0);

Cell c2 = sheet.getCells().get(“C2”);
Cell c3 = sheet.getCells().get(“C3”);

c2.setFormula(a1);
c3.setFormula(a2);

sheet.calculateFormula(new CalculationOptions(), true);

System.out.println(c2);
System.out.println(c3);

book.save(“check.xlsx”);
}



Console Output
Aspose.Cells.Cell [ C2; ValueType : IsNumeric; Value : 36892; Formula:=A1 ]
Aspose.Cells.Cell [ C3; ValueType : IsNumeric; Value : 0; Formula:=A2 ]

That is exactly the problem!

If you type “=A1” in any Excel cell, it will be automatically formatted appropriately depending on the data type stored in the cell A1 (unless you manually change it to General, but why on earth would I want to do that?). Type “=TODAY()” into any excel cell, and see that is displays a DATE, not a number.

In my program I supply an arbitrary function to a cell, and I have no idea which data type it will produce, so I cannot format it and convert to a desired type in advance, before I read the result value.

In other words there is an inconsistency between reading the result of a formula that is already in excel file and setting a formula using Aspose. Check this code out:

Workbook book;
// file.xlsx is attached
try (InputStream stream = new FileInputStream(new File(“c:\file.xlsx”))) {
book = new Workbook(stream);
Worksheet sheet = book.getWorksheets().get(0);
// a1 has a formula (already in Excel file) “=TODAY()”
Cell a1 = sheet.getCells().get(“a1”);
Cell a2 = sheet.getCells().get(“a2”);
a2.setFormula("=TODAY()");
sheet.calculateFormula(new CalculationOptions(), true);
// retrieving the result
Object res1 = a1.getValue();
Object res2 = a2.getValue();
System.out.println(res1);
System.out.println(res2);

Me and my colleagues would expect this code to produce two identical lines in the output, but instead here is what we get:

2016-05-04T00:00:00
42494.0

This is wrong. And this is a problem.

Hi,

Thanks for your posting and using Aspose.Cells.

We were able to observe the issue as per your description. We have therefore logged this issue in our database for investigation. We will look into it and resolve it if possible. Once, there is some fix or other update for you, we will let you know asap.

This issue has been logged as

  • CELLSJAVA-41830 - Data type changes from Date to Numeric unlike Excel

Thanks a lot!
Please, keep in mind that Data type changed from Null to Numeric, too.

Hi,

Thanks for your posting and using Aspose.Cells.

I have attached the sample excel file. The cell C3 is numeric not null. The cell C3 has a formula =A1 and A1 is null.

So it means, converting null to numeric is MS-Excel behavior.

Hi,


Thanks for using Aspose.Cells.

We are afraid, we cannot support this feature and closed the issue CELLSJAVA-41830 as Won’t Fix.