poi-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From n...@apache.org
Subject svn commit: r1331796 - in /poi/trunk/src: documentation/content/xdocs/status.xml java/org/apache/poi/ss/formula/functions/DateFunc.java testcases/org/apache/poi/hssf/usermodel/TestFormulaEvaluatorBugs.java
Date Sat, 28 Apr 2012 17:29:07 GMT
Author: nick
Date: Sat Apr 28 17:29:06 2012
New Revision: 1331796

URL: http://svn.apache.org/viewvc?rev=1331796&view=rev
Log:
Fix bug #48528 - support negative arguments to the DATE() function

Modified:
    poi/trunk/src/documentation/content/xdocs/status.xml
    poi/trunk/src/java/org/apache/poi/ss/formula/functions/DateFunc.java
    poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestFormulaEvaluatorBugs.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=1331796&r1=1331795&r2=1331796&view=diff
==============================================================================
--- poi/trunk/src/documentation/content/xdocs/status.xml (original)
+++ poi/trunk/src/documentation/content/xdocs/status.xml Sat Apr 28 17:29:06 2012
@@ -34,6 +34,7 @@
 
     <changes>
         <release version="3.9-beta1" date="2012-??-??">
+          <action dev="poi-developers" type="fix">48528 - support negative arguments
to the DATE() function</action>
           <action dev="poi-developers" type="fix">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</action>
           <action dev="poi-developers" type="fix">53043 - don't duplicate hyperlink
relationships when saving XSSF file</action>
           <action dev="poi-developers" type="fix">53101 - fixed evaluation of SUM over
cell range &gt; 255</action>

Modified: poi/trunk/src/java/org/apache/poi/ss/formula/functions/DateFunc.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/functions/DateFunc.java?rev=1331796&r1=1331795&r2=1331796&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/functions/DateFunc.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/functions/DateFunc.java Sat Apr 28 17:29:06
2012
@@ -54,16 +54,29 @@ public final class DateFunc extends Fixe
 		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 Fixe
 			}
 		}
 
+		// 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) {

Modified: poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestFormulaEvaluatorBugs.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestFormulaEvaluatorBugs.java?rev=1331796&r1=1331795&r2=1331796&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestFormulaEvaluatorBugs.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestFormulaEvaluatorBugs.java Sat
Apr 28 17:29:06 2012
@@ -292,6 +292,48 @@ public final class TestFormulaEvaluatorB
 			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;



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


Mime
View raw message