You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by jo...@apache.org on 2008/12/31 06:31:44 UTC
svn commit: r730309 - in /poi/trunk/src: documentation/content/xdocs/
java/org/apache/poi/hssf/record/formula/functions/
testcases/org/apache/poi/hssf/record/formula/functions/
Author: josh
Date: Tue Dec 30 21:31:42 2008
New Revision: 730309
URL: http://svn.apache.org/viewvc?rev=730309&view=rev
Log:
Patch 46410 from Steven Butler - added impl for TIME() function.
Added:
poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/functions/TestTime.java
Modified:
poi/trunk/src/documentation/content/xdocs/changes.xml
poi/trunk/src/documentation/content/xdocs/status.xml
poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Time.java
poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/functions/AllIndividualFunctionEvaluationTests.java
Modified: poi/trunk/src/documentation/content/xdocs/changes.xml
URL: http://svn.apache.org/viewvc/poi/trunk/src/documentation/content/xdocs/changes.xml?rev=730309&r1=730308&r2=730309&view=diff
==============================================================================
--- poi/trunk/src/documentation/content/xdocs/changes.xml (original)
+++ poi/trunk/src/documentation/content/xdocs/changes.xml Tue Dec 30 21:31:42 2008
@@ -37,8 +37,9 @@
<!-- Don't forget to update status.xml too! -->
<release version="3.5-beta5" date="2008-??-??">
+ <action dev="POI-DEVELOPERS" type="add">46410 - added implementation for TIME() function</action>
<action dev="POI-DEVELOPERS" type="add">46320 - added HSSFPictureData.getFormat()</action>
- <action dev="POI-DEVELOPERS" type="fix">fixed HSSFSheet.shiftRow to move hyperlinks</action>
+ <action dev="POI-DEVELOPERS" type="fix">46445 fixed HSSFSheet.shiftRow to move hyperlinks</action>
<action dev="POI-DEVELOPERS" type="fix">fixed formula parser to correctly resolve sheet-level names</action>
<action dev="POI-DEVELOPERS" type="fix">46433 - support for shared formulas in XSSF</action>
<action dev="POI-DEVELOPERS" type="add">46299 - support for carriage return and line break in XWPFRun</action>
Modified: poi/trunk/src/documentation/content/xdocs/status.xml
URL: http://svn.apache.org/viewvc/poi/trunk/src/documentation/content/xdocs/status.xml?rev=730309&r1=730308&r2=730309&view=diff
==============================================================================
--- poi/trunk/src/documentation/content/xdocs/status.xml (original)
+++ poi/trunk/src/documentation/content/xdocs/status.xml Tue Dec 30 21:31:42 2008
@@ -34,8 +34,9 @@
<!-- Don't forget to update changes.xml too! -->
<changes>
<release version="3.5-beta5" date="2008-??-??">
+ <action dev="POI-DEVELOPERS" type="add">46410 - added implementation for TIME() function</action>
<action dev="POI-DEVELOPERS" type="add">46320 - added HSSFPictureData.getFormat()</action>
- <action dev="POI-DEVELOPERS" type="fix">fixed HSSFSheet.shiftRow to move hyperlinks</action>
+ <action dev="POI-DEVELOPERS" type="fix">46445 fixed HSSFSheet.shiftRow to move hyperlinks</action>
<action dev="POI-DEVELOPERS" type="fix">fixed formula parser to correctly resolve sheet-level names</action>
<action dev="POI-DEVELOPERS" type="fix">46433 - support for shared formulas in XSSF</action>
<action dev="POI-DEVELOPERS" type="add">46299 - support for carriage return and line break in XWPFRun</action>
Modified: poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Time.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Time.java?rev=730309&r1=730308&r2=730309&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Time.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Time.java Tue Dec 30 21:31:42 2008
@@ -1,25 +1,88 @@
-/*
-* 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.
-*/
-/*
- * Created on May 15, 2005
+/* ====================================================================
+ 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.hssf.record.formula.functions;
+
+import org.apache.poi.hssf.record.formula.eval.ErrorEval;
+import org.apache.poi.hssf.record.formula.eval.Eval;
+import org.apache.poi.hssf.record.formula.eval.EvaluationException;
+import org.apache.poi.hssf.record.formula.eval.MissingArgEval;
+import org.apache.poi.hssf.record.formula.eval.NumberEval;
+import org.apache.poi.hssf.record.formula.eval.OperandResolver;
+import org.apache.poi.hssf.record.formula.eval.ValueEval;
+
+/**
+ * @author Steven Butler (sebutler @ gmail dot com)
*
+ * Based on POI org.apache.hssf.record.formula.DateFunc.java
*/
-package org.apache.poi.hssf.record.formula.functions;
+public final class Time implements Function {
+
+ private static final int SECONDS_PER_MINUTE = 60;
+ private static final int SECONDS_PER_HOUR = 3600;
+ private static final int HOURS_PER_DAY = 24;
+ private static final int SECONDS_PER_DAY = HOURS_PER_DAY * SECONDS_PER_HOUR;
+
+
+ public Eval evaluate(Eval[] args, int srcCellRow, short srcCellCol) {
+ if (args.length != 3) {
+ return ErrorEval.VALUE_INVALID;
+ }
+ double result;
+
+ try {
+ result = evaluate(evalArg(args[0]), evalArg(args[1]), evalArg(args[2]));
+ } catch (EvaluationException e) {
+ return e.getErrorEval();
+ }
+ return new NumberEval(result);
+ }
+ private static int evalArg(Eval arg) throws EvaluationException {
+ if (arg == MissingArgEval.instance) {
+ return 0;
+ }
+ // Excel silently truncates double values to integers
+ return OperandResolver.coerceValueToInt((ValueEval) arg);
+ }
+ /**
+ * Converts the supplied hours, minutes and seconds to an Excel time value.
+ *
+ *
+ * @param ds array of 3 doubles containing hours, minutes and seconds.
+ * Non-integer inputs are truncated to an integer before further calculation
+ * of the time value.
+ * @return An Excel representation of a time of day.
+ * If the time value represents more than a day, the days are removed from
+ * the result, leaving only the time of day component.
+ * @throws org.apache.poi.hssf.record.formula.eval.EvaluationException
+ * If any of the arguments are greater than 32767 or the hours
+ * minutes and seconds when combined form a time value less than 0, the function
+ * evaluates to an error.
+ */
+ private static double evaluate(int hours, int minutes, int seconds) throws EvaluationException {
-public class Time extends NotImplementedFunction {
+ if (hours > 32767 || minutes > 32767 || seconds > 32767) {
+ throw new EvaluationException(ErrorEval.VALUE_INVALID);
+ }
+ int totalSeconds = hours * SECONDS_PER_HOUR + minutes * SECONDS_PER_MINUTE + seconds;
+ if (totalSeconds < 0) {
+ throw new EvaluationException(ErrorEval.VALUE_INVALID);
+ }
+ return (totalSeconds % SECONDS_PER_DAY) / (double)SECONDS_PER_DAY;
+ }
}
Modified: poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/functions/AllIndividualFunctionEvaluationTests.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/functions/AllIndividualFunctionEvaluationTests.java?rev=730309&r1=730308&r2=730309&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/functions/AllIndividualFunctionEvaluationTests.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/functions/AllIndividualFunctionEvaluationTests.java Tue Dec 30 21:31:42 2008
@@ -49,6 +49,7 @@
result.addTestSuite(TestSumproduct.class);
result.addTestSuite(TestStatsLib.class);
result.addTestSuite(TestTFunc.class);
+ result.addTestSuite(TestTime.class);
result.addTestSuite(TestTrim.class);
result.addTestSuite(TestValue.class);
result.addTestSuite(TestXYNumericFunction.class);
Added: poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/functions/TestTime.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/functions/TestTime.java?rev=730309&view=auto
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/functions/TestTime.java (added)
+++ poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/functions/TestTime.java Tue Dec 30 21:31:42 2008
@@ -0,0 +1,122 @@
+/* ====================================================================
+ 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.hssf.record.formula.functions;
+
+import java.util.regex.Pattern;
+
+import junit.framework.TestCase;
+
+import org.apache.poi.hssf.usermodel.HSSFCell;
+import org.apache.poi.hssf.usermodel.HSSFCellStyle;
+import org.apache.poi.hssf.usermodel.HSSFDataFormat;
+import org.apache.poi.hssf.usermodel.HSSFDataFormatter;
+import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
+import org.apache.poi.hssf.usermodel.HSSFSheet;
+import org.apache.poi.hssf.usermodel.HSSFWorkbook;
+
+/**
+ * Tests for {@link Time}
+ *
+ * @author @author Steven Butler (sebutler @ gmail dot com)
+ */
+public final class TestTime extends TestCase {
+
+ private static final int SECONDS_PER_MINUTE = 60;
+ private static final int SECONDS_PER_HOUR = 60 * SECONDS_PER_MINUTE;
+ private static final double SECONDS_PER_DAY = 24 * SECONDS_PER_HOUR;
+ private HSSFCell cell11;
+ private HSSFFormulaEvaluator evaluator;
+ private HSSFWorkbook wb;
+ private HSSFDataFormatter form;
+ private HSSFCellStyle style;
+
+ public void setUp() {
+ wb = new HSSFWorkbook();
+ HSSFSheet sheet = wb.createSheet("new sheet");
+ style = wb.createCellStyle();
+ HSSFDataFormat fmt = wb.createDataFormat();
+ style.setDataFormat(fmt.getFormat("hh:mm:ss"));
+
+ cell11 = sheet.createRow(0).createCell(0);
+ form = new HSSFDataFormatter();
+
+ evaluator = new HSSFFormulaEvaluator(wb);
+ }
+
+ public void testSomeArgumentsMissing() {
+ confirm("00:00:00", "TIME(, 0, 0)");
+ confirm("12:00:00", "TIME(12, , )");
+ }
+
+ public void testValid() {
+ confirm("00:00:01", 0, 0, 1);
+ confirm("00:01:00", 0, 1, 0);
+
+ confirm("00:00:00", 0, 0, 0);
+
+ confirm("01:00:00", 1, 0, 0);
+ confirm("12:00:00", 12, 0, 0);
+ confirm("23:00:00", 23, 0, 0);
+ confirm("00:00:00", 24, 0, 0);
+ confirm("01:00:00", 25, 0, 0);
+ confirm("00:00:00", 48, 0, 0);
+ confirm("06:00:00", 6, 0, 0);
+ confirm("06:01:00", 6, 1, 0);
+ confirm("06:30:00", 6, 30, 0);
+
+ confirm("06:59:00", 6, 59, 0);
+ confirm("07:00:00", 6, 60, 0);
+ confirm("07:01:00", 6, 61, 0);
+ confirm("08:00:00", 6, 120, 0);
+ confirm("06:00:00", 6, 1440, 0);
+ confirm("18:49:00", 18, 49, 0);
+ confirm("18:49:01", 18, 49, 1);
+ confirm("18:49:30", 18, 49, 30);
+ confirm("18:49:59", 18, 49, 59);
+ confirm("18:50:00", 18, 49, 60);
+ confirm("18:50:01", 18, 49, 61);
+ confirm("18:50:59", 18, 49, 119);
+ confirm("18:51:00", 18, 49, 120);
+ confirm("03:55:07", 18, 49, 32767);
+ confirm("12:08:01", 18, 32767, 61);
+ confirm("07:50:01", 32767, 49, 61);
+ }
+ private void confirm(String expectedTimeStr, int inH, int inM, int inS) {
+ confirm(expectedTimeStr, "TIME(" + inH + "," + inM + "," + inS + ")");
+ }
+
+ private void confirm(String expectedTimeStr, String formulaText) {
+// System.out.println("=" + formulaText);
+ String[] parts = Pattern.compile(":").split(expectedTimeStr);
+ int expH = Integer.parseInt(parts[0]);
+ int expM = Integer.parseInt(parts[1]);
+ int expS = Integer.parseInt(parts[2]);
+
+ double expectedValue = (expH*SECONDS_PER_HOUR + expM*SECONDS_PER_MINUTE + expS)/SECONDS_PER_DAY;
+
+ cell11.setCellFormula(formulaText);
+ cell11.setCellStyle(style);
+ evaluator.clearAllCachedResultValues();
+
+ double actualValue = evaluator.evaluate(cell11).getNumberValue();
+ assertEquals(expectedValue, actualValue, 0.0);
+
+ String actualText = form.formatCellValue(cell11, evaluator);
+ assertEquals(expectedTimeStr, actualText);
+ }
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org