From c746a5f3c785e4158f26e8122b34d2a5dada19a5 Mon Sep 17 00:00:00 2001 From: Nick Burch Date: Sat, 28 Apr 2012 17:29:06 +0000 Subject: [PATCH] Fix bug #48528 - support negative arguments to the DATE() function git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1331796 13f79535-47bb-0310-9956-ffa450edef68 --- src/documentation/content/xdocs/status.xml | 1 + .../poi/ss/formula/functions/DateFunc.java | 35 +++++++++++++--- .../usermodel/TestFormulaEvaluatorBugs.java | 42 +++++++++++++++++++ 3 files changed, 73 insertions(+), 5 deletions(-) diff --git a/src/documentation/content/xdocs/status.xml b/src/documentation/content/xdocs/status.xml index bd5e74d84f..17c2490dfe 100644 --- a/src/documentation/content/xdocs/status.xml +++ b/src/documentation/content/xdocs/status.xml @@ -34,6 +34,7 @@ + 48528 - support negative arguments to the DATE() function 53092 - allow specifying of a TimeZone to DateUtil.getJavaDate(), for when it is known that a file comes from a different (known) timezone to the current machine 53043 - don't duplicate hyperlink relationships when saving XSSF file 53101 - fixed evaluation of SUM over cell range > 255 diff --git a/src/java/org/apache/poi/ss/formula/functions/DateFunc.java b/src/java/org/apache/poi/ss/formula/functions/DateFunc.java index 81da051bf7..03152e0c13 100644 --- a/src/java/org/apache/poi/ss/formula/functions/DateFunc.java +++ b/src/java/org/apache/poi/ss/formula/functions/DateFunc.java @@ -54,16 +54,29 @@ public final class DateFunc extends Fixed3ArgFunction { return new NumberEval(result); } + /** + * Note - works with Java Calendar months, not Excel months + */ private static double evaluate(int year, int month, int pDay) throws EvaluationException { - - if (year < 0 || month < 0 || pDay < 0) { + // We don't support negative years yet + if (year < 0) { throw new EvaluationException(ErrorEval.VALUE_INVALID); } - + // Negative months are fairly easy + while (month < 0) { + year--; + month += 12; + } + // Negative days are handled by the Java Calendar + + // Excel has bugs around leap years in 1900, handle them + // Special case for the non-existant 1900 leap year if (year == 1900 && month == Calendar.FEBRUARY && pDay == 29) { return 60.0; } + // If they give a date in 1900 in Jan/Feb, with the days + // putting it past the leap year, adjust int day = pDay; if (year == 1900) { if ((month == Calendar.JANUARY && day >= 60) || @@ -72,12 +85,24 @@ public final class DateFunc extends Fixed3ArgFunction { } } + // Turn this into a Java date Calendar c = new GregorianCalendar(); - c.set(year, month, day, 0, 0, 0); c.set(Calendar.MILLISECOND, 0); + + // Handle negative days of the week, that pull us across + // the 29th of Feb 1900 + if (pDay < 0 && c.get(Calendar.YEAR) == 1900 && + month > Calendar.FEBRUARY && + c.get(Calendar.MONTH) < Calendar.MARCH) { + c.add(Calendar.DATE, 1); + } - return DateUtil.getExcelDate(c.getTime(), false); // TODO - fix 1900/1904 problem + // TODO Identify if we're doing 1900 or 1904 date windowing + boolean use1904windowing = false; + + // Have this Java date turned back into an Excel one + return DateUtil.getExcelDate(c.getTime(), use1904windowing); } private static int getYear(double d) { diff --git a/src/testcases/org/apache/poi/hssf/usermodel/TestFormulaEvaluatorBugs.java b/src/testcases/org/apache/poi/hssf/usermodel/TestFormulaEvaluatorBugs.java index d0d41a8ce2..f013a8ed7b 100644 --- a/src/testcases/org/apache/poi/hssf/usermodel/TestFormulaEvaluatorBugs.java +++ b/src/testcases/org/apache/poi/hssf/usermodel/TestFormulaEvaluatorBugs.java @@ -292,6 +292,48 @@ public final class TestFormulaEvaluatorBugs extends TestCase { throw e; } } + + public void testDateWithNegativeParts_bug48528() { + HSSFWorkbook wb = new HSSFWorkbook(); + HSSFSheet sheet = wb.createSheet("Sheet1"); + HSSFRow row = sheet.createRow(1); + HSSFCell cell = row.createCell(0); + HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb); + + // 5th Feb 2012 = 40944 + // 1st Feb 2012 = 40940 + // 5th Jan 2012 = 40913 + // 5th Dec 2011 = 40882 + // 5th Feb 2011 = 40579 + + cell.setCellFormula("DATE(2012,2,1)"); + fe.notifyUpdateCell(cell); + assertEquals(40940.0, fe.evaluate(cell).getNumberValue()); + + cell.setCellFormula("DATE(2012,2,1+4)"); + fe.notifyUpdateCell(cell); + assertEquals(40944.0, fe.evaluate(cell).getNumberValue()); + + cell.setCellFormula("DATE(2012,2-1,1+4)"); + fe.notifyUpdateCell(cell); + assertEquals(40913.0, fe.evaluate(cell).getNumberValue()); + + cell.setCellFormula("DATE(2012,2,1-27)"); + fe.notifyUpdateCell(cell); + assertEquals(40913.0, fe.evaluate(cell).getNumberValue()); + + cell.setCellFormula("DATE(2012,2-2,1+4)"); + fe.notifyUpdateCell(cell); + assertEquals(40882.0, fe.evaluate(cell).getNumberValue()); + + cell.setCellFormula("DATE(2012,2,1-58)"); + fe.notifyUpdateCell(cell); + assertEquals(40882.0, fe.evaluate(cell).getNumberValue()); + + cell.setCellFormula("DATE(2012,2-12,1+4)"); + fe.notifyUpdateCell(cell); + assertEquals(40579.0, fe.evaluate(cell).getNumberValue()); + } private static final class EvalListener extends EvaluationListener { private int _countCacheHits; -- 2.39.5