From ba665e8b75f700626580fa205c0e29034f782754 Mon Sep 17 00:00:00 2001 From: Josh Micich Date: Sat, 6 Sep 2008 06:04:01 +0000 Subject: [PATCH] Minor fixes to YEARFRAC(). Added ISEVEN() and ISODD(). Added test cases. git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@692614 13f79535-47bb-0310-9956-ffa450edef68 --- .../record/formula/atp/AnalysisToolPak.java | 4 +- .../record/formula/atp/ParityFunction.java | 74 ++++++++++++++++++ .../poi/hssf/record/formula/atp/YearFrac.java | 7 +- .../poi/hssf/data/externalFunctionExample.xls | Bin 16384 -> 16896 bytes .../formula/TestExternalFunctionFormulas.java | 15 +++- .../formula/atp/TestYearFracCalculator.java | 1 + 6 files changed, 95 insertions(+), 6 deletions(-) create mode 100644 src/java/org/apache/poi/hssf/record/formula/atp/ParityFunction.java diff --git a/src/java/org/apache/poi/hssf/record/formula/atp/AnalysisToolPak.java b/src/java/org/apache/poi/hssf/record/formula/atp/AnalysisToolPak.java index 60f06695d0..f46b4aea71 100644 --- a/src/java/org/apache/poi/hssf/record/formula/atp/AnalysisToolPak.java +++ b/src/java/org/apache/poi/hssf/record/formula/atp/AnalysisToolPak.java @@ -109,8 +109,8 @@ public final class AnalysisToolPak { r(m, "IMSUB", null); r(m, "IMSUM", null); r(m, "INTRATE", null); - r(m, "ISEVEN", null); - r(m, "ISODD", null); + r(m, "ISEVEN", ParityFunction.IS_EVEN); + r(m, "ISODD", ParityFunction.IS_ODD); r(m, "LCM", null); r(m, "MDURATION", null); r(m, "MROUND", null); diff --git a/src/java/org/apache/poi/hssf/record/formula/atp/ParityFunction.java b/src/java/org/apache/poi/hssf/record/formula/atp/ParityFunction.java new file mode 100644 index 0000000000..dd9ecc2af3 --- /dev/null +++ b/src/java/org/apache/poi/hssf/record/formula/atp/ParityFunction.java @@ -0,0 +1,74 @@ +/* ==================================================================== + 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.atp; + +import org.apache.poi.hssf.record.formula.eval.BlankEval; +import org.apache.poi.hssf.record.formula.eval.BoolEval; +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.OperandResolver; +import org.apache.poi.hssf.record.formula.eval.ValueEval; +import org.apache.poi.hssf.record.formula.functions.FreeRefFunction; +import org.apache.poi.hssf.usermodel.HSSFSheet; +import org.apache.poi.hssf.usermodel.HSSFWorkbook; +/** + * Implementation of Excel 'Analysis ToolPak' function ISEVEN() ISODD()
+ * + * @author Josh Micich + */ +final class ParityFunction implements FreeRefFunction { + + public static final FreeRefFunction IS_EVEN = new ParityFunction(0); + public static final FreeRefFunction IS_ODD = new ParityFunction(1); + private final int _desiredParity; + + private ParityFunction(int desiredParity) { + _desiredParity = desiredParity; + } + + public ValueEval evaluate(Eval[] args, int srcCellRow, short srcCellCol, HSSFWorkbook workbook, + HSSFSheet sheet) { + if (args.length != 1) { + return ErrorEval.VALUE_INVALID; + } + + int val; + try { + val = evaluateArgParity(args[0], srcCellRow, srcCellCol); + } catch (EvaluationException e) { + return e.getErrorEval(); + } + + return BoolEval.valueOf(val == _desiredParity); + } + + private static int evaluateArgParity(Eval arg, int srcCellRow, short srcCellCol) throws EvaluationException { + ValueEval ve = OperandResolver.getSingleValue(arg, srcCellRow, srcCellCol); + + if (ve == BlankEval.INSTANCE) { + return 0; + } + double d = OperandResolver.coerceValueToDouble(ve); + if (d < 0) { + d = -d; + } + long v = (long) Math.floor(d); + return (int) (v & 0x0001); + } +} diff --git a/src/java/org/apache/poi/hssf/record/formula/atp/YearFrac.java b/src/java/org/apache/poi/hssf/record/formula/atp/YearFrac.java index 5861871920..ac196723e2 100644 --- a/src/java/org/apache/poi/hssf/record/formula/atp/YearFrac.java +++ b/src/java/org/apache/poi/hssf/record/formula/atp/YearFrac.java @@ -21,6 +21,7 @@ import java.util.Calendar; import java.util.GregorianCalendar; import java.util.regex.Pattern; +import org.apache.poi.hssf.record.formula.eval.BlankEval; 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; @@ -96,6 +97,9 @@ final class YearFrac implements FreeRefFunction { Calendar date = parseDate(strVal); return HSSFDateUtil.getExcelDate(date, false); } + if (ve instanceof BlankEval) { + return 0.0; + } return OperandResolver.coerceValueToDouble(ve); } @@ -120,7 +124,7 @@ final class YearFrac implements FreeRefFunction { } catch (NumberFormatException e) { throw new EvaluationException(ErrorEval.VALUE_INVALID); } - if (f0<0 || f1<0 || f2<0 || f0>12 || f1>12 || f2>12) { + if (f0<0 || f1<0 || f2<0 || (f0>12 && f1>12 && f2>12)) { // easy to see this cannot be a valid date throw new EvaluationException(ErrorEval.VALUE_INVALID); } @@ -150,6 +154,7 @@ final class YearFrac implements FreeRefFunction { if (day <1 || day>cal.getActualMaximum(Calendar.DAY_OF_MONTH)) { throw new EvaluationException(ErrorEval.VALUE_INVALID); } + cal.set(Calendar.DAY_OF_MONTH, day); return cal; } diff --git a/src/testcases/org/apache/poi/hssf/data/externalFunctionExample.xls b/src/testcases/org/apache/poi/hssf/data/externalFunctionExample.xls index 07eafb414416e98875b4c0a9cd2a78b453a5413a..fc81e60a18397f0a47b49bb77c3e32309d15662d 100755 GIT binary patch delta 2158 zcmchZU1(fI6vxlpy-DtFv)zyE-t6~RQV7B2CfRM8G-h8XJ*da-AaX3j|U?B#!*>3@?@>Yul$_ICRls(QL- z{6(A!bgO)`F%XR7i9oO+K68p24UuoS%mf|&&pE|8r})t!bT@5{0$w^n7xj4E{t^DJ z4hL@DDVCh#s#EA8$DG?kK`*C`+}G2)nJPrVV{x&(#khg*C4}$C2?IY$3UN7U;95$E z?@|V?r-fh{1I>&O;jDpI;zC>}+BHe@5I~cF6af+vFpxH|PXcZu+C)|W?jhP12^gVt zhGjaFMB6862UCVlmx#7XZWsxB!yY5{VaZ+~_A$wRC7Z?{GJc$r**-+sJ}84#DA+SH z*z*+ZxD56trT)51eVOoQCH@P-f31tuH}o_fjpXbqI2AcW!msyIT5W$#@@TY-k2_E3 zEvNX>DK>=#pBLiAm=N>2D7Piuz?Y@VQh3}Bzacwgy09c-ah&UvvpWxABASaf)r3Z+ z$KRf2g_-zlJcd2eeXP^KZ8Gw{n6)xgA_8_QvITcU_TqBPkE1dZp6pz>x#}3M$3)e2 z#0z*mD)x%|UJ{2?`zCubpwPKg3wIRNcbQ5zfo<=C06$-9EwJbA$%zRC5}jQTv~AEz z#@$o9_j4m7*$4If4y@aG3557dV^jxXxwxRA|n zMlx8VEqjz^j>04qh(4wS;eBiGN`&LBPykq9UT`MHvj+t delta 1500 zcmah}TSyd982-;Vd*8L$ZQP5qv+J6f88(KxY-Z&Wc7NzX50X@1AQE8+K|P34Qcy_R z{~{F`ln+@%wp3_R8I(yk3bCi~L69CIy4Z_|c4qb*cPye^&dmRv|NFlGKZkRst(~;B zk&%8@B&4_aWk-~ zrcA;NiejcrZOrG=C@Ocq7smB zAj&4*MKzp=Fz_qVbTKR;<}uu*EXTKb+wq8!pDxp-G~wYW1LvYqnvqcAYbp%4;^ZIA z3iu_;nw9gKFc_1tQC++q0Z+sjZjQ+s*7;ec*{|VDfB`9} z;U}*7Ni|tHEECGn`~iAQvf!^n^N>1h3^Lw6>X4tyKq6Pet=!>2j)qgb>MYf?bIm2H zY2ca`s_EdG>r}IfHRCAkao>9#QuK7$g$GGMU)nxMp6@)bZibZRnAbs#{ ziiQE+TnTONK6iLU9rkjE#!wWmMkJK;(<@9B=l6Mq8>?}=Y{&Y_W%N3we|D&?N$%Oa zC%H+0aNrIR=@lbl0J7;vNdNZD&FfbVV3^qH0BD%P3+2V65C_YTMusWbDgw9!ZkN8H zgBn0ZDb`f{aDb3)=n3wMt9UamJD&o~hF~pa;z)eWmXBbCdV{h-@WBJC9#$I|U|SEs sdAmdhn=oG6dsJ8gS;Xj&c1!<)kP0T+$-T+}!P@hvkA9g`PpXoZ--0=Au>b%7 diff --git a/src/testcases/org/apache/poi/hssf/record/formula/TestExternalFunctionFormulas.java b/src/testcases/org/apache/poi/hssf/record/formula/TestExternalFunctionFormulas.java index 2b60e23c3b..b3a82c6de7 100755 --- a/src/testcases/org/apache/poi/hssf/record/formula/TestExternalFunctionFormulas.java +++ b/src/testcases/org/apache/poi/hssf/record/formula/TestExternalFunctionFormulas.java @@ -74,9 +74,18 @@ public final class TestExternalFunctionFormulas extends TestCase { public void testEvaluate() { HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("externalFunctionExample.xls"); HSSFSheet sheet = wb.getSheetAt(0); - HSSFCell cell = sheet.getRow(0).getCell(0); HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(sheet, wb); - CellValue evalResult = fe.evaluate(cell); - evalResult.toString(); + confirmCellEval(sheet, 0, 0, fe, "YEARFRAC(B1,C1)", 29.0/90.0); + confirmCellEval(sheet, 1, 0, fe, "YEARFRAC(B2,C2)", 0.0); + confirmCellEval(sheet, 2, 0, fe, "IF(ISEVEN(3),1.2,1.6)", 1.6); + confirmCellEval(sheet, 3, 0, fe, "IF(ISODD(3),1.2,1.6)", 1.2); + } + + private static void confirmCellEval(HSSFSheet sheet, int rowIx, int colIx, + HSSFFormulaEvaluator fe, String expectedFormula, double expectedResult) { + HSSFCell cell = sheet.getRow(rowIx).getCell(colIx); + assertEquals(expectedFormula, cell.getCellFormula()); + CellValue cv = fe.evaluate(cell); + assertEquals(expectedResult, cv.getNumberValue(), 0.0); } } diff --git a/src/testcases/org/apache/poi/hssf/record/formula/atp/TestYearFracCalculator.java b/src/testcases/org/apache/poi/hssf/record/formula/atp/TestYearFracCalculator.java index aaf03a238e..890a5c2cbd 100644 --- a/src/testcases/org/apache/poi/hssf/record/formula/atp/TestYearFracCalculator.java +++ b/src/testcases/org/apache/poi/hssf/record/formula/atp/TestYearFracCalculator.java @@ -38,6 +38,7 @@ public final class TestYearFracCalculator extends TestCase { confirm(md(1999, 3, 31), md(1999, 4, 3), 1, 0.008219178); confirm(md(1999, 4, 5), md(1999, 4, 8), 1, 0.008219178); confirm(md(1999, 4, 4), md(1999, 4, 7), 1, 0.008219178); + confirm(md(2000, 2, 5), md(2000, 6, 1), 0, 0.322222222); } private void confirm(double startDate, double endDate, int basis, double expectedValue) { -- 2.39.5