poi-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From ye...@apache.org
Subject svn commit: r992591 - in /poi/trunk/src: documentation/content/xdocs/ java/org/apache/poi/hssf/usermodel/ ooxml/java/org/apache/poi/xssf/usermodel/ testcases/org/apache/poi/hssf/usermodel/ testcases/org/apache/poi/ss/usermodel/
Date Sat, 04 Sep 2010 12:33:10 GMT
Author: yegor
Date: Sat Sep  4 12:33:09 2010
New Revision: 992591

URL: http://svn.apache.org/viewvc?rev=992591&view=rev
Log:
 change cell type to error when setting Double.NaN or Infinities, see Bugzilla 49761

Modified:
    poi/trunk/src/documentation/content/xdocs/status.xml
    poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java
    poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java
    poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFCell.java
    poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestCell.java

Modified: poi/trunk/src/documentation/content/xdocs/status.xml
URL: http://svn.apache.org/viewvc/poi/trunk/src/documentation/content/xdocs/status.xml?rev=992591&r1=992590&r2=992591&view=diff
==============================================================================
--- poi/trunk/src/documentation/content/xdocs/status.xml (original)
+++ poi/trunk/src/documentation/content/xdocs/status.xml Sat Sep  4 12:33:09 2010
@@ -34,6 +34,7 @@
 
     <changes>
         <release version="3.7-beta3" date="2010-??-??">
+           <action dev="poi-developers" type="fix">49761 - change cell type to error
when setting Double.NaN or Infinities</action>
            <action dev="poi-developers" type="fix">49833 - ensure that CTNumPr is included
in poi-ooxml-schemas.jar</action>
            <action dev="POI-DEVELOPERS" type="fix">49841 - fixed LEFT and RIGHT to
return #VALUE! when called with a negative operand </action>
            <action dev="POI-DEVELOPERS" type="fix">49783 - fixed evaluation of XSSF
workbooks containing formulas with reference errors (#REF!)</action>

Modified: poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java?rev=992591&r1=992590&r2=992591&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java Sat Sep  4 12:33:09 2010
@@ -46,11 +46,7 @@ import org.apache.poi.hssf.record.common
 import org.apache.poi.hssf.record.formula.ExpPtg;
 import org.apache.poi.hssf.record.formula.Ptg;
 import org.apache.poi.hssf.record.formula.eval.ErrorEval;
-import org.apache.poi.ss.usermodel.Cell;
-import org.apache.poi.ss.usermodel.CellStyle;
-import org.apache.poi.ss.usermodel.Comment;
-import org.apache.poi.ss.usermodel.Hyperlink;
-import org.apache.poi.ss.usermodel.RichTextString;
+import org.apache.poi.ss.usermodel.*;
 import org.apache.poi.ss.util.CellRangeAddress;
 import org.apache.poi.ss.util.CellReference;
 import org.apache.poi.ss.util.NumberToTextConverter;
@@ -464,20 +460,31 @@ public class HSSFCell implements Cell {
      *        will change the cell to a numeric cell and set its value.
      */
     public void setCellValue(double value) {
-        int row=_record.getRow();
-        short col=_record.getColumn();
-        short styleIndex=_record.getXFIndex();
-
-        switch (_cellType) {
-            default:
-                setCellType(CELL_TYPE_NUMERIC, false, row, col, styleIndex);
-            case CELL_TYPE_NUMERIC:
-                (( NumberRecord ) _record).setValue(value);
-                break;
-            case CELL_TYPE_FORMULA:
-                ((FormulaRecordAggregate)_record).setCachedDoubleResult(value);
-                break;
+        if(Double.isInfinite(value)) {
+            // Excel does not support positive/negative infinities,
+            // rather, it gives a #DIV/0! error in these cases.
+            setCellErrorValue(FormulaError.DIV0.getCode());
+        } else if (Double.isNaN(value)){
+            // Excel does not support Not-a-Number (NaN),
+            // instead it immediately generates a #NUM! error.
+            setCellErrorValue(FormulaError.NUM.getCode());
+        } else {
+            int row=_record.getRow();
+            short col=_record.getColumn();
+            short styleIndex=_record.getXFIndex();
+
+            switch (_cellType) {
+                default:
+                    setCellType(CELL_TYPE_NUMERIC, false, row, col, styleIndex);
+                case CELL_TYPE_NUMERIC:
+                    (( NumberRecord ) _record).setValue(value);
+                    break;
+                case CELL_TYPE_FORMULA:
+                    ((FormulaRecordAggregate)_record).setCachedDoubleResult(value);
+                    break;
+            }
         }
+
     }
 
     /**

Modified: poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java?rev=992591&r1=992590&r2=992591&view=diff
==============================================================================
--- poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java (original)
+++ poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java Sat Sep  4 12:33:09
2010
@@ -218,7 +218,14 @@ public final class XSSFCell implements C
      *        will change the cell to a numeric cell and set its value.
      */
     public void setCellValue(double value) {
-        if(Double.isInfinite(value) || Double.isNaN(value)) {
+        if(Double.isInfinite(value)) {
+            // Excel does not support positive/negative infinities,
+            // rather, it gives a #DIV/0! error in these cases.
+            _cell.setT(STCellType.E);
+            _cell.setV(FormulaError.DIV0.getString());
+        } else if (Double.isNaN(value)){
+            // Excel does not support Not-a-Number (NaN),
+            // instead it immediately generates an #NUM! error.
             _cell.setT(STCellType.E);
             _cell.setV(FormulaError.NUM.getString());
         } else {

Modified: poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFCell.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFCell.java?rev=992591&r1=992590&r2=992591&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFCell.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFCell.java Sat Sep  4 12:33:09
2010
@@ -320,4 +320,6 @@ public final class TestHSSFCell extends 
 			assertEquals("The maximum length of cell contents (text) is 32,767 characters", e.getMessage());
 		}
 	}
+
+
 }

Modified: poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestCell.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestCell.java?rev=992591&r1=992590&r2=992591&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestCell.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestCell.java Sat Sep  4 12:33:09
2010
@@ -489,4 +489,59 @@ public abstract class BaseTestCell exten
         int i2 = cell.getCellStyle().getIndex();
         assertEquals(i1, i2);
     }
+
+    /**
+     * Excel's implementation of floating number arithmetic does not fully adhere to IEEE
754:
+     *
+     * From http://support.microsoft.com/kb/78113:
+     *
+     * <ul>
+     * <li> Positive/Negative Infinities:
+     *   Infinities occur when you divide by 0. Excel does not support infinities, rather,
+     *   it gives a #DIV/0! error in these cases.
+     * </li>
+     * <li>
+     *   Not-a-Number (NaN):
+     *   NaN is used to represent invalid operations (such as infinity/infinity, 
+     *   infinity-infinity, or the square root of -1). NaNs allow a program to
+     *   continue past an invalid operation. Excel instead immediately generates
+     *   an error such as #NUM! or #DIV/0!.
+     * </li>
+     * </ul>
+     */
+    public void testNanAndInfinity() {
+        Workbook wb = _testDataProvider.createWorkbook();
+        Sheet workSheet = wb.createSheet("Sheet1");
+        Row row = workSheet.createRow(0);
+
+        Cell cell0 = row.createCell(0);
+        cell0.setCellValue(Double.NaN);
+        assertEquals("Double.NaN should change cell type to CELL_TYPE_ERROR", Cell.CELL_TYPE_ERROR,
cell0.getCellType());
+        assertEquals("Double.NaN should change cell value to #NUM!", ErrorConstants.ERROR_NUM,
cell0.getErrorCellValue());
+
+        Cell cell1 = row.createCell(1);
+        cell1.setCellValue(Double.POSITIVE_INFINITY);
+        assertEquals("Double.POSITIVE_INFINITY should change cell type to CELL_TYPE_ERROR",
Cell.CELL_TYPE_ERROR, cell1.getCellType());
+        assertEquals("Double.POSITIVE_INFINITY should change cell value to #DIV/0!", ErrorConstants.ERROR_DIV_0,
cell1.getErrorCellValue());
+
+        Cell cell2 = row.createCell(2);
+        cell2.setCellValue(Double.NEGATIVE_INFINITY);
+        assertEquals("Double.NEGATIVE_INFINITY should change cell type to CELL_TYPE_ERROR",
Cell.CELL_TYPE_ERROR, cell2.getCellType());
+        assertEquals("Double.NEGATIVE_INFINITY should change cell value to #DIV/0!", ErrorConstants.ERROR_DIV_0,
cell2.getErrorCellValue());
+
+        wb = _testDataProvider.writeOutAndReadBack(wb);
+        row = wb.getSheetAt(0).getRow(0);
+
+        cell0 = row.getCell(0);
+        assertEquals(Cell.CELL_TYPE_ERROR, cell0.getCellType());
+        assertEquals(ErrorConstants.ERROR_NUM, cell0.getErrorCellValue());
+
+        cell1 = row.getCell(1);
+        assertEquals(Cell.CELL_TYPE_ERROR, cell1.getCellType());
+        assertEquals(ErrorConstants.ERROR_DIV_0, cell1.getErrorCellValue());
+
+        cell2 = row.getCell(2);
+        assertEquals(Cell.CELL_TYPE_ERROR, cell2.getCellType());
+        assertEquals(ErrorConstants.ERROR_DIV_0, cell2.getErrorCellValue());
+    }
 }



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


Mime
View raw message