Fix bug #48968 - Implement support for HOUR, MINUTE and SECOND formulas

Includes some re-working of the existing Calendar functions, unit tests for the old and new Calendar functions, and a wider date+formula+formatting test for this area


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

2 files changed, 142 insertions, 0 deletions

diff --git a/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java b/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java
index 590d096e11..d82674d733 100644
--- a/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java
+++ b/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java
@@ -22,10 +22,16 @@ import java.io.File;
 import java.io.FileNotFoundException;
 import java.io.FileOutputStream;
 import java.io.IOException;
+import java.text.DateFormat;
+import java.text.SimpleDateFormat;
 import java.util.ArrayList;
 import java.util.Arrays;
+import java.util.Calendar;
+import java.util.Date;
 import java.util.Iterator;
 import java.util.List;
+import java.util.Locale;
+import java.util.TimeZone;
 
 import junit.framework.AssertionFailedError;
 
@@ -46,6 +52,7 @@ import org.apache.poi.ss.formula.ptg.Ptg;
 import org.apache.poi.ss.usermodel.BaseTestBugzillaIssues;
 import org.apache.poi.ss.usermodel.Cell;
 import org.apache.poi.ss.usermodel.CellStyle;
+import org.apache.poi.ss.usermodel.DateUtil;
 import org.apache.poi.ss.usermodel.Name;
 import org.apache.poi.ss.usermodel.Row;
 import org.apache.poi.ss.usermodel.Sheet;
@@ -2042,4 +2049,54 @@ if(1==2) {
        assertEquals(1, wb.getNumberOfSheets());
        assertEquals("DGATE", wb.getSheetAt(0).getRow(1).getCell(0).getStringCellValue());
     }
+    
+    public void test48968() throws Exception {
+       HSSFWorkbook wb = openSample("48968.xls");
+       assertEquals(1, wb.getNumberOfSheets());
+       
+       // Check the dates
+       HSSFSheet s = wb.getSheetAt(0);
+       Date d20110325 = s.getRow(0).getCell(0).getDateCellValue(); 
+       Date d19000102 = s.getRow(11).getCell(0).getDateCellValue(); 
+       Date d19000100 = s.getRow(21).getCell(0).getDateCellValue();
+       assertEquals(s.getRow(0).getCell(3).getStringCellValue(), timeToUTC(d20110325));
+       assertEquals(s.getRow(11).getCell(3).getStringCellValue(), timeToUTC(d19000102));
+       // There is no such thing as 00/01/1900...
+       assertEquals("00/01/1900 06:14:24", s.getRow(21).getCell(3).getStringCellValue());
+       assertEquals("31/12/1899 06:14:24", timeToUTC(d19000100));
+       
+       // Check the cached values
+       assertEquals("HOUR(A1)",   s.getRow(5).getCell(0).getCellFormula());
+       assertEquals(11.0,         s.getRow(5).getCell(0).getNumericCellValue());
+       assertEquals("MINUTE(A1)", s.getRow(6).getCell(0).getCellFormula());
+       assertEquals(39.0,         s.getRow(6).getCell(0).getNumericCellValue());
+       assertEquals("SECOND(A1)", s.getRow(7).getCell(0).getCellFormula());
+       assertEquals(54.0,         s.getRow(7).getCell(0).getNumericCellValue());
+       
+       // Re-evaulate and check
+       HSSFFormulaEvaluator.evaluateAllFormulaCells(wb);
+       assertEquals("HOUR(A1)",   s.getRow(5).getCell(0).getCellFormula());
+       assertEquals(11.0,         s.getRow(5).getCell(0).getNumericCellValue());
+       assertEquals("MINUTE(A1)", s.getRow(6).getCell(0).getCellFormula());
+       assertEquals(39.0,         s.getRow(6).getCell(0).getNumericCellValue());
+       assertEquals("SECOND(A1)", s.getRow(7).getCell(0).getCellFormula());
+       assertEquals(54.0,         s.getRow(7).getCell(0).getNumericCellValue());
+       
+       // Push the time forward a bit and check
+       double date = s.getRow(0).getCell(0).getNumericCellValue();
+       s.getRow(0).getCell(0).setCellValue(date + 1.26);
+       
+       HSSFFormulaEvaluator.evaluateAllFormulaCells(wb);
+       assertEquals("HOUR(A1)",   s.getRow(5).getCell(0).getCellFormula());
+       assertEquals(11.0+6.0,     s.getRow(5).getCell(0).getNumericCellValue());
+       assertEquals("MINUTE(A1)", s.getRow(6).getCell(0).getCellFormula());
+       assertEquals(39.0+14.0+1,  s.getRow(6).getCell(0).getNumericCellValue());
+       assertEquals("SECOND(A1)", s.getRow(7).getCell(0).getCellFormula());
+       assertEquals(54.0+24.0-60, s.getRow(7).getCell(0).getNumericCellValue());
+    }
+    private String timeToUTC(Date d) {
+       SimpleDateFormat fmt = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss", Locale.UK);
+       fmt.setTimeZone(TimeZone.getTimeZone("UTC"));
+       return fmt.format(d);
+    }
 }
diff --git a/src/testcases/org/apache/poi/ss/formula/functions/TestCalendarFieldFunction.java b/src/testcases/org/apache/poi/ss/formula/functions/TestCalendarFieldFunction.java
new file mode 100644
index 0000000000..ede20de393
--- /dev/null
+++ b/src/testcases/org/apache/poi/ss/formula/functions/TestCalendarFieldFunction.java
@@ -0,0 +1,85 @@
+/* ====================================================================
+   Licensed to the Apache Software Foundation (ASF) under one or more
+   contributor license agreements.  See the NOTICE file distributed with
+   this work for additional information regarding copyright ownership.
+   The ASF licenses this file to You under the Apache License, Version 2.0
+   (the "License"); you may not use this file except in compliance with
+   the License.  You may obtain a copy of the License at
+
+       http://www.apache.org/licenses/LICENSE-2.0
+
+   Unless required by applicable law or agreed to in writing, software
+   distributed under the License is distributed on an "AS IS" BASIS,
+   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+   See the License for the specific language governing permissions and
+   limitations under the License.
+==================================================================== */
+
+package org.apache.poi.ss.formula.functions;
+
+import junit.framework.AssertionFailedError;
+import junit.framework.TestCase;
+
+import org.apache.poi.hssf.usermodel.HSSFCell;
+import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
+import org.apache.poi.hssf.usermodel.HSSFSheet;
+import org.apache.poi.hssf.usermodel.HSSFWorkbook;
+import org.apache.poi.ss.usermodel.Cell;
+import org.apache.poi.ss.usermodel.CellValue;
+
+/**
+ * Test for YEAR / MONTH / DAY / HOUR / MINUTE / SECOND
+ */
+public final class TestCalendarFieldFunction extends TestCase {
+
+    private HSSFCell cell11;
+    private HSSFFormulaEvaluator evaluator;
+
+    public void setUp() {
+        HSSFWorkbook wb = new HSSFWorkbook();
+        HSSFSheet sheet = wb.createSheet("new sheet");
+        cell11 = sheet.createRow(0).createCell(0);
+        cell11.setCellType(HSSFCell.CELL_TYPE_FORMULA);
+        evaluator = new HSSFFormulaEvaluator(wb);
+    }
+
+    public void testValid() {
+        confirm("YEAR(2.26)", 1900);
+        confirm("MONTH(2.26)", 1);
+        confirm("DAY(2.26)", 2);
+        confirm("HOUR(2.26)", 6);
+        confirm("MINUTE(2.26)", 14);
+        confirm("SECOND(2.26)", 24);
+        
+        confirm("YEAR(40627.4860417)", 2011);
+        confirm("MONTH(40627.4860417)", 3);
+        confirm("DAY(40627.4860417)", 25);
+        confirm("HOUR(40627.4860417)", 11);
+        confirm("MINUTE(40627.4860417)", 39);
+        confirm("SECOND(40627.4860417)", 54);
+    }
+
+    public void testBugDate() {
+        confirm("YEAR(0.0)", 1900);
+        confirm("MONTH(0.0)", 1);
+        confirm("DAY(0.0)", 0);
+        
+        confirm("YEAR(0.26)", 1900);
+        confirm("MONTH(0.26)", 1);
+        confirm("DAY(0.26)", 0);
+        confirm("HOUR(0.26)", 6);
+        confirm("MINUTE(0.26)", 14);
+        confirm("SECOND(0.26)", 24);
+    }
+
+    private void confirm(String formulaText, double expectedResult) {
+        cell11.setCellFormula(formulaText);
+        evaluator.clearAllCachedResultValues();
+        CellValue cv = evaluator.evaluate(cell11);
+        if (cv.getCellType() != Cell.CELL_TYPE_NUMERIC) {
+            throw new AssertionFailedError("Wrong result type: " + cv.formatAsString());
+        }
+        double actualValue = cv.getNumberValue();
+        assertEquals(expectedResult, actualValue, 0);
+    }
+}