[patch] Patch to implement TIME() Formula - includes new unit test file TestTime.java

diff.txt (text/plain), 9.90 KB, created by Steven Butler on 2008-12-17 03:58:05 UTC

(hide)

Description: Patch to implement TIME() Formula - includes new unit test file TestTime.java

Filename:

MIME Type:

Creator: Steven Butler

Created: 2008-12-17 03:58:05 UTC

Size: 9.90 KB

patch

obsolete

>Index: src/java/org/apache/poi/hssf/record/formula/functions/Time.java >=================================================================== >--- src/java/org/apache/poi/hssf/record/formula/functions/Time.java (revision 725967) >+++ src/java/org/apache/poi/hssf/record/formula/functions/Time.java (working copy) >@@ -20,6 +20,53 @@ > */ > package org.apache.poi.hssf.record.formula.functions; > >-public class Time extends NotImplementedFunction { >+import org.apache.poi.hssf.record.formula.eval.ErrorEval; >+import org.apache.poi.hssf.record.formula.eval.EvaluationException; > >+/** >+ * @author Steven Butler (sebutler @ gmail dot com) >+ * >+ * Based on POI org.apache.hssf.record.formula.DateFunc.java >+ */ >+public final class Time extends NumericFunction.MultiArg { >+ >+ public Time() { >+ super(3, 3); >+ } >+ /** >+ * Approximate value of 1 second in Excel >+ */ >+ private static final double EXCEL_SECOND = 0.000011574074074074074074; >+ >+ /** >+ * 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. >+ */ >+ protected double evaluate(double[] ds) throws EvaluationException { >+ // Excel silently truncates double values to integers >+ long hour = (int) ds[0]; >+ long minutes = (int) ds[1]; >+ long seconds = (int) ds[2]; >+ >+ if (hour > 32767 || minutes > 32767 || seconds > 32767) { >+ throw new EvaluationException(ErrorEval.VALUE_INVALID); >+ } >+ seconds = (hour * 3600L + minutes * 60L + seconds); >+ >+ if (seconds < 0) { >+ throw new EvaluationException(ErrorEval.VALUE_INVALID); >+ } >+ return (seconds % 86400L) * EXCEL_SECOND; >+ } > } >Index: src/testcases/org/apache/poi/hssf/record/formula/functions/AllIndividualFunctionEvaluationTests.java >=================================================================== >--- src/testcases/org/apache/poi/hssf/record/formula/functions/AllIndividualFunctionEvaluationTests.java (revision 725967) >+++ src/testcases/org/apache/poi/hssf/record/formula/functions/AllIndividualFunctionEvaluationTests.java (working copy) >@@ -32,6 +32,7 @@ > result.addTestSuite(TestAverage.class); > result.addTestSuite(TestCountFuncs.class); > result.addTestSuite(TestDate.class); >+ result.addTestSuite(TestTime.class); > result.addTestSuite(TestFind.class); > result.addTestSuite(TestFinanceLib.class); > result.addTestSuite(TestIndex.class); >Index: src/testcases/org/apache/poi/hssf/record/formula/functions/TestTime.java >=================================================================== >--- src/testcases/org/apache/poi/hssf/record/formula/functions/TestTime.java (revision 0) >+++ src/testcases/org/apache/poi/hssf/record/formula/functions/TestTime.java (revision 0) >@@ -0,0 +1,148 @@ >+ >+/* ==================================================================== >+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 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; >+ >+/** >+ * @author @author Steven Butler (sebutler @ gmail dot com) >+ */ >+public final class TestTime extends TestCase { >+ >+ private HSSFCell cell11; >+ private HSSFFormulaEvaluator evaluator; >+ private HSSFWorkbook wb; >+ HSSFDataFormatter form; >+ 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); >+ cell11.setCellType(HSSFCell.CELL_TYPE_FORMULA); >+ form = new HSSFDataFormatter(); >+ >+ evaluator = new HSSFFormulaEvaluator(wb); >+ } >+ >+ /** >+ * Test disabled pending a fix in the formula evaluator >+ * TODO - create MissingArgEval and modify the formula evaluator to handle this >+ */ >+ public void DISABLEDtestSomeArgumentsMissing() { >+ confirm("TIME(, 0, 0)", 0.0); >+ confirm("TIME(, 0, 0)", 1.0); >+ } >+ >+ public void testValid() { >+ confirm("TIME(0,0,1)", 0.000011574074074074100000); >+ confirmTime("TIME(0,0,1)", "00:00:01"); >+ confirm("TIME(0,1,0)", 0.000694444444444444000000); >+ confirmTime("TIME(0,1,0)", "00:01:00"); >+ >+ confirm("TIME(0,0,0)", 0.000000000000000000000000); >+ confirmTime("TIME(0,0,0)", "00:00:00"); >+ >+ confirm("TIME(1,0,0)", 0.041666666666666700000000); >+ confirmTime("TIME(1,0,0)", "01:00:00"); >+ confirm("TIME(12,0,0)", 0.500000000000000000000000); >+ confirmTime("TIME(12,0,0)", "12:00:00"); >+ confirm("TIME(23,0,0)", 0.958333333333333000000000); >+ confirmTime("TIME(23,0,0)", "23:00:00"); >+ confirm("TIME(24,0,0)", 0.000000000000000000000000); >+ confirmTime("TIME(24,0,0)", "00:00:00"); >+ confirm("TIME(25,0,0)", 0.041666666666666700000000); >+ confirmTime("TIME(25,0,0)", "01:00:00"); >+ confirm("TIME(48,0,0)", 0.000000000000000000000000); >+ confirmTime("TIME(48,0,0)", "00:00:00"); >+ confirm("TIME(6,0,0)", 0.250000000000000000000000); >+ confirmTime("TIME(6,0,0)", "06:00:00"); >+ confirm("TIME(6,1,0)", 0.250694444444444000000000); >+ confirmTime("TIME(6,1,0)", "06:01:00"); >+ confirm("TIME(6,30,0)", 0.270833333333333000000000); >+ confirmTime("TIME(6,30,0)", "06:30:00"); >+ confirm("TIME(6,59,0)", 0.290972222222222000000000); >+ confirmTime("TIME(6,59,0)", "06:59:00"); >+ confirm("TIME(6,60,0)", 0.291666666666667000000000); >+ confirmTime("TIME(6,60,0)", "07:00:00"); >+ confirm("TIME(6,61,0)", 0.292361111111111000000000); >+ confirmTime("TIME(6,61,0)", "07:01:00"); >+ confirm("TIME(6,120,0)", 0.333333333333333000000000); >+ confirmTime("TIME(6,120,0)", "08:00:00"); >+ confirm("TIME(6,1440,0)", 0.250000000000000000000000); >+ confirmTime("TIME(6,1440,0)", "06:00:00"); >+ confirm("TIME(18,49,0)", 0.784027777777778000000000); >+ confirmTime("TIME(18,49,0)", "18:49:00"); >+ confirm("TIME(18,49,1)", 0.784039351851852000000000); >+ confirmTime("TIME(18,49,1)", "18:49:01"); >+ confirm("TIME(18,49,30)", 0.784375000000000000000000); >+ confirmTime("TIME(18,49,30)", "18:49:30"); >+ confirm("TIME(18,49,59)", 0.784710648148148000000000); >+ confirmTime("TIME(18,49,59)", "18:49:59"); >+ confirm("TIME(18,49,60)", 0.784722222222222000000000); >+ confirmTime("TIME(18,49,60)", "18:50:00"); >+ confirm("TIME(18,49,61)", 0.784733796296296000000000); >+ confirmTime("TIME(18,49,61)", "18:50:01"); >+ confirm("TIME(18,49,119)", 0.785405092592593000000000); >+ confirmTime("TIME(18,49,119)", "18:50:59"); >+ confirm("TIME(18,49,120)", 0.785416666666667000000000); >+ confirmTime("TIME(18,49,120)", "18:51:00"); >+ confirm("TIME(18,49,32767)", 0.163275462962963000000000); >+ confirmTime("TIME(18,49,32767)", "03:55:07"); >+ confirm("TIME(18,32767,61)", 0.505567129629629000000000); >+ confirmTime("TIME(18,32767,61)", "12:08:01"); >+ confirm("TIME(32767,49,61)", 0.326400462962965000000000); >+ confirmTime("TIME(32767,49,61)", "07:50:01"); >+ >+ } >+ >+ private void confirmTime(String formulaText, String expectedResult) { >+ cell11.setCellFormula(formulaText); >+ cell11.setCellStyle(style); >+ >+ evaluator.clearAllCachedResultValues(); >+ String cellValue = form.formatCellValue(cell11, evaluator); >+ >+ >+ assertEquals(expectedResult, cellValue); >+ } >+ >+ private void confirm(String formulaText, double expectedResult) { >+ cell11.setCellFormula(formulaText); >+ cell11.setCellStyle(style); >+ >+ evaluator.clearAllCachedResultValues(); >+ // Excel seems to have more internal precision than can be displayed - we fudge the numeric tests passing >+ // against the Excel displayed values to 14 decimal places >+ double actualValue = evaluator.evaluate(cell11).getNumberValue(); >+ assertEquals(expectedResult, actualValue, 0.00000000000001); >+ } >+} >+ View Attachment As Diff View Attachment As Raw

This is ASF Bugzilla: the Apache Software Foundation bug system. In case of problems with the functioning of ASF Bugzilla, please contact bugzilla-admin@apache.org. Please Note: this e-mail address is only for reporting problems with ASF Bugzilla. Mail about any other subject will be silently ignored.

• • Home
  • | New
  • | Browse
  • | Search
  • |
    [?]
  • | Reports
  • | Help
  • | New Account
  • | Log In
    Remember [x]
  • | Forgot Password
    Login: [x]