Add tests to verify that XSSF and HSSF do the same thing with retrieving the wrong type of value from string/numberic/formula cells, and tweak documentation to match the long standing behaviour (bug #47815)

git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@951466 13f79535-47bb-0310-9956-ffa450edef68

5 files changed, 83 insertions, 7 deletions

diff --git a/src/documentation/content/xdocs/status.xml b/src/documentation/content/xdocs/status.xml
index c29e9f9ee4..20794454f7 100644
--- a/src/documentation/content/xdocs/status.xml
+++ b/src/documentation/content/xdocs/status.xml
@@ -34,6 +34,7 @@
 
     <changes>
         <release version="3.7-SNAPSHOT" date="2010-??-??">
+           <action dev="POI-DEVELOPERS" type="fix">47815 - correct documentation on what happens when you request a String from a non-string Formula cell</action>
            <action dev="POI-DEVELOPERS" type="fix">49386 - avoid NPE when extracting OOXML file properties which are dates</action>
            <action dev="POI-DEVELOPERS" type="fix">49377 - only call DecimalFormat.setRoundingMode on Java 1.6 - it's needed to match excel's rendering of numbers</action>
            <action dev="POI-DEVELOPERS" type="fix">49378 - correct 1.6ism</action>
diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java b/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java
index a490d44dfd..c6f5a839d1 100644
--- a/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java
+++ b/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java
@@ -700,7 +700,7 @@ public class HSSFCell implements Cell {
     /**
      * get the value of the cell as a string - for numeric cells we throw an exception.
      * For blank cells we return an empty string.
-     * For formulaCells that are not string Formulas, we return empty String
+     * For formulaCells that are not string Formulas, we throw an exception
      */
     public String getStringCellValue()
     {
@@ -711,7 +711,7 @@ public class HSSFCell implements Cell {
     /**
      * get the value of the cell as a string - for numeric cells we throw an exception.
      * For blank cells we return an empty string.
-     * For formulaCells that are not string Formulas, we return empty String
+     * For formulaCells that are not string Formulas, we throw an exception
      */
     public HSSFRichTextString getRichStringCellValue() {
 
diff --git a/src/java/org/apache/poi/ss/usermodel/Cell.java b/src/java/org/apache/poi/ss/usermodel/Cell.java
index 66c9d38b3a..7859e029d8 100644
--- a/src/java/org/apache/poi/ss/usermodel/Cell.java
+++ b/src/java/org/apache/poi/ss/usermodel/Cell.java
@@ -257,7 +257,7 @@ public interface Cell {
      * Get the value of the cell as a XSSFRichTextString
      * <p>
      * For numeric cells we throw an exception. For blank cells we return an empty string.
-     * For formula cells we return the pre-calculated value.
+     * For formula cells we return the pre-calculated value if a string, otherwise an exception.
      * </p>
      * @return the value of the cell as a XSSFRichTextString
      */
@@ -267,7 +267,7 @@ public interface Cell {
      * Get the value of the cell as a string
      * <p>
      * For numeric cells we throw an exception. For blank cells we return an empty string.
-     * For formulaCells that are not string Formulas, we return empty String.
+     * For formulaCells that are not string Formulas, we throw an exception.
      * </p>
      * @return the value of the cell as a string
      */
diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java
index 9a6be02014..374c6b00d2 100644
--- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java
+++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java
@@ -195,7 +195,15 @@ public final class XSSFCell implements Cell {
                 return 0.0;
             case CELL_TYPE_FORMULA:
             case CELL_TYPE_NUMERIC:
-                return _cell.isSetV() ? Double.parseDouble(_cell.getV()) : 0.0;
+                if(_cell.isSetV()) {
+                   try {
+                      return Double.parseDouble(_cell.getV());
+                   } catch(NumberFormatException e) {
+                      throw typeMismatch(CELL_TYPE_NUMERIC, CELL_TYPE_STRING, false);
+                   }
+                } else {
+                   return 0.0;
+                }
             default:
                 throw typeMismatch(CELL_TYPE_NUMERIC, cellType, false);
         }
@@ -223,7 +231,7 @@ public final class XSSFCell implements Cell {
      * Get the value of the cell as a string
      * <p>
      * For numeric cells we throw an exception. For blank cells we return an empty string.
-     * For formulaCells that are not string Formulas, we return empty String.
+     * For formulaCells that are not string Formulas, we throw an exception
      * </p>
      * @return the value of the cell as a string
      */
@@ -236,7 +244,7 @@ public final class XSSFCell implements Cell {
      * Get the value of the cell as a XSSFRichTextString
      * <p>
      * For numeric cells we throw an exception. For blank cells we return an empty string.
-     * For formula cells we return the pre-calculated value.
+     * For formula cells we return the pre-calculated value if a string, otherwise an exception
      * </p>
      * @return the value of the cell as a XSSFRichTextString
      */
diff --git a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFBugs.java b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFBugs.java
index ed8f03e784..504eceb0ac 100644
--- a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFBugs.java
+++ b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFBugs.java
@@ -28,6 +28,7 @@ import org.apache.poi.ss.usermodel.BaseTestBugzillaIssues;
 import org.apache.poi.ss.usermodel.Cell;
 import org.apache.poi.ss.usermodel.DataFormatter;
 import org.apache.poi.ss.usermodel.Font;
+import org.apache.poi.ss.usermodel.FormulaEvaluator;
 import org.apache.poi.ss.usermodel.Name;
 import org.apache.poi.ss.usermodel.Row;
 import org.apache.poi.ss.usermodel.Sheet;
@@ -330,4 +331,70 @@ public final class TestXSSFBugs extends BaseTestBugzillaIssues {
        assertEquals("123", df.formatRawCellContents(123.0, -1, "@"));
        assertEquals("123", df.formatRawCellContents(123.0, -1, "General"));
     }
+    
+    /**
+     * Ensures that XSSF and HSSF agree with each other,
+     *  and with the docs on when fetching the wrong
+     *  kind of value from a Formula cell
+     */
+    public void test47815() {
+       Workbook[] wbs = new Workbook[] {
+             new HSSFWorkbook(),
+             new XSSFWorkbook()
+       };
+       for(Workbook wb : wbs) {
+          Sheet s = wb.createSheet();
+          Row r = s.createRow(0);
+          
+          // Setup
+          Cell cn = r.createCell(0, Cell.CELL_TYPE_NUMERIC);
+          cn.setCellValue(1.2);
+          Cell cs = r.createCell(1, Cell.CELL_TYPE_STRING);
+          cs.setCellValue("Testing");
+          
+          Cell cfn = r.createCell(2, Cell.CELL_TYPE_FORMULA);
+          cfn.setCellFormula("A1");  
+          Cell cfs = r.createCell(3, Cell.CELL_TYPE_FORMULA);
+          cfs.setCellFormula("B1");
+          
+          FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator();
+          assertEquals(Cell.CELL_TYPE_NUMERIC, fe.evaluate(cfn).getCellType());
+          assertEquals(Cell.CELL_TYPE_STRING, fe.evaluate(cfs).getCellType());
+          fe.evaluateFormulaCell(cfn);
+          fe.evaluateFormulaCell(cfs);
+          
+          // Now test
+          assertEquals(Cell.CELL_TYPE_NUMERIC, cn.getCellType());
+          assertEquals(Cell.CELL_TYPE_STRING, cs.getCellType());
+          assertEquals(Cell.CELL_TYPE_FORMULA, cfn.getCellType());
+          assertEquals(Cell.CELL_TYPE_NUMERIC, cfn.getCachedFormulaResultType());
+          assertEquals(Cell.CELL_TYPE_FORMULA, cfs.getCellType());
+          assertEquals(Cell.CELL_TYPE_STRING, cfs.getCachedFormulaResultType());
+          
+          // Different ways of retrieving
+          assertEquals(1.2, cn.getNumericCellValue());
+          try {
+             cn.getRichStringCellValue();
+             fail();
+          } catch(IllegalStateException e) {}
+          
+          assertEquals("Testing", cs.getStringCellValue());
+          try {
+             cs.getNumericCellValue();
+             fail();
+          } catch(IllegalStateException e) {}
+          
+          assertEquals(1.2, cfn.getNumericCellValue());
+          try {
+             cfn.getRichStringCellValue();
+             fail();
+          } catch(IllegalStateException e) {}
+          
+          assertEquals("Testing", cfs.getStringCellValue());
+          try {
+             cfs.getNumericCellValue();
+             fail();
+          } catch(IllegalStateException e) {}
+       }
+    }
 }