DO NOT REPLY [Bug 49761] Double.NaN can be written but not read with POI

[bu...@apache.org]

https://issues.apache.org/bugzilla/show_bug.cgi?id=49761

Yegor Kozlov <ye...@dinom.ru> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
             Status|NEW                         |RESOLVED
         Resolution|                            |FIXED

--- Comment #6 from Yegor Kozlov <ye...@dinom.ru> 2010-09-04 08:34:22 EDT ---
A very interesting case, thanks for your investigations.

The point is that Excel's implementation of floating-point arithmetic does not
fully adhere to IEEE 754. In particular, Excel does not support the notion of
Positive/Negative Infinities and Not-a-Number (NaN). 

In case of Infinities Excel generates a #DIV/0! error. This typically occurs
when you divide by 0.
In case of NaN Excel generates an #NUM! error which indicates invalid number.
For example, SQRT(-1) will result in a #NUM! error. 

More details can be found at http://support.microsoft.com/kb/78113


POI allows you to set Double.NaN, but Excel displays an unexpected value of
2.69653970229347E+308. If the result is referenced by a Excel formula then your
scientific software may give incorrect results because any math operation
involving NaN should result in NaN. 


To make POI compatible with Excel the following rules must be followed:

 - setting a cell value to Double.NaN should change the cell type to
CELL_TYPE_ERROR and error value #NUM!
 - setting a cell value to Double.POSITIVE_INFINITY or Double.NEGATIVE_INFINITY
should change the cell type to CELL_TYPE_ERROR and error value #DIV/0!

The rules should work both in HSSF and XSSF.

I applied this fix in r992591.

If you process the generated workbooks in Java you should check type of cells
because double can be retrieved only from numeric cells. The code may look as
follows:

        double value;
        switch(cell.getCellType()){
            case Cell.CELL_TYPE_ERROR:
                byte errorValue = cell.getErrorCellValue();
                if(errorValue == ErrorConstants.ERROR_DIV_0)
                    value = Double.POSITIVE_INFINITY;
                if(errorValue == ErrorConstants.ERROR_NUM)
                    value = Double.NaN;
                break;
            case Cell.CELL_TYPE_NUMERIC:
                value = cell.getNumericCellValue();
                break;
        }


Regards,
Yegor

-- 
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org