Free Support Forum - aspose.com

Aspose Cells and precision

Hello,

It seems that Workbook.calculateFormula does not use the same precision/rounding as Excel.

For example, I created one Excel spreadhseet. This template has the following formula for cell B1: POWER(A1;2). The following piece of code will set a value

I input 1 number in an excel template (A1). Workbook workbook = new Workbook();
workbook.open(XXX);

sheet.getCell("A1").setValue(new BigDecimal("380.2"));
Double xSquare = sheet.getCell("B1");

At this point, the value of xSquare

147077.0399999999790452420711517333984375

However, if you simply input the value in the Excel spreadsheet, the result will be 144552.04000.

To my mind the issue lies in the usage of Double, but I don't find a way to ensure that Aspose will make the calculation with BigDecimals instead of Doubles;
Any idea ?

Thanks in advance.

Hi,

Thanks for considering Aspose.

Well, I executed the the following code and it ran perfectly to show 144552.04 value.

Workbook workbook = new Workbook();

workbook.Open(@"d:\testformula.xls");

Worksheet sheet = workbook.Worksheets[0];

sheet.Cells["A1"].PutValue(380.2);

sheet.Cells["A2"].Formula = "=POWER(A1,2)";

workbook.CalculateFormula();

Double xSquare = sheet.Cells["A2"].DoubleValue;

MessageBox.Show(xSquare.ToString());

Regards

Amjad Sahi

Aspose Nanjing Team

Hi,

The problem is caused by the usage of Double in java application.It seems the class Bigdecimal also could not solve this problem.

We will add some processes to keep the same precision with Excel.

Hi,

Please try the fix in http://www.aspose.com/Community/forums/thread/56300.aspx

Hi Warren,

Thanks for your reply. I ran the same piece of code with the next version you sent me and the result is different:

Old Lib: 144552.0399999999790452420711517333984375
New Lib: 144552.04000000000814907252788543701171875

On this sample code, the new version is one digit more precise than the other. The result is exact up to 16 significant digits (which is - I believe - more than what we can expect generally from the double type itself).

I agree with you that the cause of the problem is the conversion from BigDecimal to double (which is a famous cause of imprecision). This is not due to the calculation itself. I can have some imprecision simply by trying to get back the value I just entered. The following code:

sheet.getCell("A1").setValue(new BigDecimal("380.2"));
Double xSquare = sheet.getCell("A1");

Will return: 380.19999999999998863131622783839702606201171875

The question is more: Which internal representation is used by Aspose in calculateFormula() ?

If aspose uses double in the calculation, the precision loss appears when inputing the value, so this precision loss can increase (depending on the formula: if I substract two close numbers, the precision loss will increase), so there is no way to assess the precision loss of the final result.

If aspose uses decimal representation in the calculation, the precision loss appears only when I am getting the result. In that case adding a method like cell.getBigDecimalValue() would solve the issue.

What's your opinion ?

Hi,

We could not get the same result.With following codes:

Cells cells = book.getWorksheets().getSheet(0).getCells();
Cell cell = cells.getCell("A1");
cell.setValue(new BigDecimal("380.2"));
System.out.println(cell.getValue());
cell = cells.getCell("A2");
cell.setFormula("=power(a1,2)");
book.calculateFormula();

System.out.println(cell.getValue());
BigDecimal b = new BigDecimal(380.2);
b = b.pow(2);

System.out.println(b);

We get result :

380.2
144552.04
144552.0399999999913552528596483172278645244223480741986576081359316958696581423282623291015625

We use double in the cell value and formula calculation.

Hi,

Do you use a 64-bit J2SE Runtime Environment ?

Hi,

You are right: If I display directly the (Double) results from cell.getValue(), I will have the correct display.

In fact, the problem occured when I tried to transform those doubles into Big Decimals. I used the constructor BigDecimal(double) without specifying a MathContext. On the other hand, if I provide MathContext.DECIMAL64, the result appears OK.

By the way, this is the reason why in your test case, the first result was not exact: you were using constructor BigDecimal(double) instead of BigDecimal(String) or BigDecimal(double, MathContext).

However, the conversion from double to big decimal does not trigger the precision loss, but in fact it simply reveals it (as far as I understand the java spec). Consequently, if Aspose uses double internally (which I understand perfectly: it is java's standard !), there may be some precision loss. I can mask part of it when converting back to BigDecimal, but I am never sure of the precision of my result.

I will continue the analysis of the tool with *real* test cases and see wether this has an impact on my application. Thank you very much for your help.

------------------------------------------------------------------------

New test case:

Workbook book = new Workbook();
Cells cells = book.getWorksheets().getSheet(0).getCells();
Cell cell = cells.getCell("A1");
cell.setValue(new BigDecimal("380.2"));
System.out.println("Input Cell value as double: " + cell.getValue());
System.out.println("Input Cell value as Big Decimal: " + new BigDecimal(((Double)cell.getValue()).doubleValue()));
System.out.println("Input Cell value as Big Decimal/MathContext: " + new BigDecimal(((Double)cell.getValue()).doubleValue(), MathContext.DECIMAL64));

Will give the following output:

Input Cell value as double: 380.2

Input Cell value as Big Decimal: 380.19999999999998863131622783839702606201171875

Input Cell value as Big Decimal/MathContext: 380.2000000000000

Hi,

MathContext.DECIMAL64 object with a precision setting matching the IEEE 754R Decimal64 format, 16 digits, and a rounding mode of, the IEEE 754R default.

In Ms Excel , a double-precision value is a 64-bit number with values ranging from negative 1.79769313486232e308 to positive 1.79769313486232e308, it complies with the IEC 60559:1989 (IEEE 754) standard for binary floating-point arithmetic.

So MathContext.DECIMAL64 is same precision setting to MS Excel.

In Aspose.Cells for Java,we add some procession to keep the same precision with the MS Excel after calculation.