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

1 files changed, 57 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);
+    }
 }