Facing issue with formula for specific values(1-CellID)

Aspose.Cells Product Family
1

Hi Team,

We are facing an issue with in a specific case while generating the output sheet.

Here in input sheet we have few hardcoded data for cells with formula.

We have a cell let take an example A1 and B1.

A1 contains the value 0.999999999 and B1 has formula 1-A1

Once we are calulating it with the Aspose API's we are getting the value B1 value as "-".

Here we are trying to migrate from Actuate to Aspose but Actuate gives the value for same example 0.0000.

Can you please help us here to identify what is the issue.?

Thanks,

Gurdeep


Additional Information on this post:
The same scenario is checked with Actuate and the value after calulation is "-" in Spreadsheet,But when we tried to read the value from the cell it is 0.0000.

In Aspose case,the value after calculation is "-" in the spreadsheet, and when we tried to read the value it is "-",which is the issue we are facing.

Find the attchment to get further info on the issue. in H1084 cell we have 1.000.Kindly help us with this issue.

Thanks,
Aghalya Ramanujam

2

Hi Gurdeep,

Thanks for your posting and using Aspose.Cells.

We have evaluated your issue by adding 0.999999999 in cell A1 and adding =1-A1 formula in cell B1 in Excel and Excel evaluated the value of B1 as 1E-09. This is the same value evaluated by Aspose.Cells.

Please check the source xlsx file used in this code and screenshot for your reference. Please check the console output of this code below.

So, we think, Aspose.Cells works fine.

Java


String filePath = “F:\Shak-Data-RW\Downloads\Book1.xlsx”;


Workbook workbook = new Workbook(filePath);


Worksheet worksheet = workbook.getWorksheets().get(0);


//Calculate the formulas

workbook.calculateFormula();


//Check the value of cell B1

Cell b1 = worksheet.getCells().get(“B1”);


System.out.println(b1.getStringValue());

Console Output:
1E-09
3

Additional Information on this post:
The same scenario is checked with Actuate and the value after calulation is “-” in Spreadsheet,But when we tried to read the value from the cell it is 0.0000.

In Aspose case,the value after calculation is “-” in the spreadsheet, and when we tried to read the value it is “-”,which is the issue we are facing.

Find the attchment to get further info on the issue. in H1084 cell we have 1.000.Kindly help us with this issue.

Thanks,
Aghalya Ramanujam

4

Hi,


You must have formatted the formula cell which is unknown to us.

I have tested your simple scenario with the attached template file with v8.1.x, it works fine. It gives correct “0” value before and after calculation of the formula in the B1 cell.
e.g
Sample code:

Workbook workbook = new Workbook(filePath);


Worksheet worksheet = workbook.getWorksheets().get(0);


Cell b1 = worksheet.getCells().get(“B1”);

System.out.println(b1.getStringValue()); //0 - Ok

//Calculate the formulas

workbook.calculateFormula();


//Check the value of cell B1

b1 = worksheet.getCells().get(“B1”); //0 - Ok


System.out.println(b1.getStringValue());


The B1 cell has a formula “=1-H1084” whereas the cell H1084 has a value i.e. “1.0000”. Please find attached the template file for your reference.

If you still feel Aspose.Cells does not work similar to MS Excel, please provide your template file here, we will check it soon.

Thank you.

5

Hi Team,

Sorry its not possible to us to provide the template's.

We have cell format _(* #,##0.0000_);_(* (#,##0.0000);_(* "-"??_);_(@_)v

This issue we are facing only for the specific scenario, not in all the cases as we mentioned earlier.

Thanks in Advance.

Gurdeep

Updating the comments.

I created a sample sheet and i am able replicate the issue. v

6

Hi,


Thanks for the template file.

I have tested your scenario using your file with our latest version v8.1.1( you may download it from our Downloads module), it works fine and as per Ms Excel.
e.g
Sample code:

String filePath = “Formula_issue.xls”;


Workbook workbook = new Workbook(filePath);


Worksheet worksheet = workbook.getWorksheets().get(0);


Cell c1 = worksheet.getCells().get(“C1”);

System.out.println(c1.getStringValue()); //"-" - Ok

//Calculate the formulas

workbook.calculateFormula();


//Check the value of cell C1

c1 = worksheet.getCells().get(“C1”); //"-" - Ok


System.out.println(c1.getStringValue());


Let us know if you still find the issue with latest version, kindly provide more details and sample code to reproduce the issue on our end.

Thank you.

7

Hi Team,

Still I am facing the same issue and here, we are using the Aspose verison 8.1.0.3.

System Out
[Console output redirected to file:D:\TEST\Excel_POC\Aspose_Log.logs]
-
-

I ran the same code which provided by you and getting the - as an output, instead of correct values. Can you please help us in resolving this issue.

Tested with 8.1.1 still i am facing the same issue.

Thanks,

Gurdeep

8

Hi,


Well, for your information, Cell.getStringValue() would give you the formatted string value what is shown in the cell in MS Excel, you may confirm that it gives correct “-” value in the cell.

If you need to get the value behind the scene/ mask, you should use Cell.getValue() method instead.
e.g
Sample code:

String filePath = “Formula_issue.xls”;


Workbook workbook = new Workbook(filePath);


Worksheet worksheet = workbook.getWorksheets().get(0);


Cell c1 = worksheet.getCells().get(“C1”);

System.out.println(c1.getStringValue()); //“-” - Ok
System.out.println(c1.getValue()); //0.0 - Ok

//Calculate the formulas

workbook.calculateFormula();


//Check the value of cell C1

c1 = worksheet.getCells().get(“C1”);


System.out.println(c1.getStringValue());//“-” - Ok
System.out.println(c1.getValue()); //0.0 - Ok


Hope, you understand now.

Thank you.