From 49ddf85f9b4be3e217be24c7895931c5cd5ae311 Mon Sep 17 00:00:00 2001 From: Josh Micich Date: Mon, 8 Sep 2008 20:28:05 +0000 Subject: [PATCH] Fixes for DAY/MONTH/YEAR functions (junit cases added) git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@693250 13f79535-47bb-0310-9956-ffa450edef68 --- .../record/formula/eval/FunctionEval.java | 6 +- .../functions/CalendarFieldFunction.java | 98 ++++++++++++++++++ .../hssf/record/formula/functions/Day.java | 64 ------------ .../hssf/record/formula/functions/Month.java | 67 ------------ .../hssf/record/formula/functions/Year.java | 68 ------------ .../poi/hssf/data/FormulaEvalTestData.xls | Bin 153600 -> 154624 bytes 6 files changed, 101 insertions(+), 202 deletions(-) create mode 100644 src/java/org/apache/poi/hssf/record/formula/functions/CalendarFieldFunction.java delete mode 100644 src/java/org/apache/poi/hssf/record/formula/functions/Day.java delete mode 100644 src/java/org/apache/poi/hssf/record/formula/functions/Month.java delete mode 100644 src/java/org/apache/poi/hssf/record/formula/functions/Year.java diff --git a/src/java/org/apache/poi/hssf/record/formula/eval/FunctionEval.java b/src/java/org/apache/poi/hssf/record/formula/eval/FunctionEval.java index fe58c69c09..ff71416025 100644 --- a/src/java/org/apache/poi/hssf/record/formula/eval/FunctionEval.java +++ b/src/java/org/apache/poi/hssf/record/formula/eval/FunctionEval.java @@ -143,9 +143,9 @@ public abstract class FunctionEval implements OperationEval { retval[64] = new Match(); // MATCH retval[65] = new Date(); // DATE retval[66] = new Time(); // TIME - retval[67] = new Day(); // DAY - retval[68] = new Month(); // MONTH - retval[69] = new Year(); // YEAR + retval[67] = CalendarFieldFunction.DAY; // DAY + retval[68] = CalendarFieldFunction.MONTH; // MONTH + retval[69] = CalendarFieldFunction.YEAR; // YEAR retval[70] = new Weekday(); // WEEKDAY retval[71] = new Hour(); // HOUR retval[72] = new Minute(); // MINUTE diff --git a/src/java/org/apache/poi/hssf/record/formula/functions/CalendarFieldFunction.java b/src/java/org/apache/poi/hssf/record/formula/functions/CalendarFieldFunction.java new file mode 100644 index 0000000000..3df7c9c5d5 --- /dev/null +++ b/src/java/org/apache/poi/hssf/record/formula/functions/CalendarFieldFunction.java @@ -0,0 +1,98 @@ +/* ==================================================================== + 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.Calendar; +import java.util.Date; +import java.util.GregorianCalendar; + +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; +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; +import org.apache.poi.hssf.usermodel.HSSFDateUtil; + +/** + * Implementation of Excel functions DAY, MONTH and YEAR + * + * + * @author Guenter Kickinger g.kickinger@gmx.net + */ +public final class CalendarFieldFunction implements Function { + + public static final Function YEAR = new CalendarFieldFunction(Calendar.YEAR, false); + public static final Function MONTH = new CalendarFieldFunction(Calendar.MONTH, true); + public static final Function DAY = new CalendarFieldFunction(Calendar.DAY_OF_MONTH, false); + + private final int _dateFieldId; + private final boolean _needsOneBaseAdjustment; + + private CalendarFieldFunction(int dateFieldId, boolean needsOneBaseAdjustment) { + _dateFieldId = dateFieldId; + _needsOneBaseAdjustment = needsOneBaseAdjustment; + } + + public Eval evaluate(Eval[] operands, int srcCellRow, short srcCellCol) { + if (operands.length != 1) { + return ErrorEval.VALUE_INVALID; + } + + int val; + try { + ValueEval ve = OperandResolver.getSingleValue(operands[0], srcCellRow, srcCellCol); + + if (ve == BlankEval.INSTANCE) { + val = 0; + } else { + val = OperandResolver.coerceValueToInt(ve); + } + } catch (EvaluationException e) { + return e.getErrorEval(); + } + if (val < 0) { + return ErrorEval.NUM_ERROR; + } + return new NumberEval(getCalField(val)); + } + + private int getCalField(int serialDay) { + if (serialDay == 0) { + // Special weird case + // day zero should be 31-Dec-1899, but Excel seems to think it is 0-Jan-1900 + switch (_dateFieldId) { + case Calendar.YEAR: return 1900; + case Calendar.MONTH: return 1; + case Calendar.DAY_OF_MONTH: return 0; + } + throw new IllegalStateException("bad date field " + _dateFieldId); + } + Date d = HSSFDateUtil.getJavaDate(serialDay, false); // TODO fix 1900/1904 problem + + Calendar c = new GregorianCalendar(); + c.setTime(d); + + int result = c.get(_dateFieldId); + if (_needsOneBaseAdjustment) { + result++; + } + return result; + } +} \ No newline at end of file diff --git a/src/java/org/apache/poi/hssf/record/formula/functions/Day.java b/src/java/org/apache/poi/hssf/record/formula/functions/Day.java deleted file mode 100644 index 0ae5694767..0000000000 --- a/src/java/org/apache/poi/hssf/record/formula/functions/Day.java +++ /dev/null @@ -1,64 +0,0 @@ -/* -* 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.usermodel.HSSFDateUtil; - -import org.apache.poi.hssf.record.formula.eval.Eval; -import org.apache.poi.hssf.record.formula.eval.ValueEval; -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.NumberEval; -import org.apache.poi.hssf.record.formula.eval.NumericValueEval; - -/** - * @author Pavel Krupets - */ -public class Day extends NumericFunction { - /** - * @see org.apache.poi.hssf.record.formula.functions.Function#evaluate(org.apache.poi.hssf.record.formula.eval.Eval[], int, short) - */ - public Eval evaluate(Eval[] operands, int srcCellRow, short -srcCellCol) { - ValueEval retval = null; - - switch (operands.length) { - default: - retval = ErrorEval.VALUE_INVALID; - break; - case 1: - ValueEval ve = singleOperandEvaluate(operands[0], -srcCellRow, srcCellCol); - if (ve instanceof NumericValueEval) { - NumericValueEval ne = (NumericValueEval) ve; - if (HSSFDateUtil.isValidExcelDate(ne.getNumberValue())) { - java.util.Date d = HSSFDateUtil.getJavaDate(ne.getNumberValue(), false); // XXX fix 1900/1904 problem - java.util.Calendar c = java.util.Calendar.getInstance(); - c.setTime(d); - retval = new NumberEval(c.get(java.util.Calendar.DAY_OF_MONTH)); - } else { - retval = ErrorEval.NUM_ERROR; - } - } else if (ve instanceof BlankEval) { - // do nothing - } else { - retval = ErrorEval.NUM_ERROR; - } - } - return retval; - } -} diff --git a/src/java/org/apache/poi/hssf/record/formula/functions/Month.java b/src/java/org/apache/poi/hssf/record/formula/functions/Month.java deleted file mode 100644 index d5178b22df..0000000000 --- a/src/java/org/apache/poi/hssf/record/formula/functions/Month.java +++ /dev/null @@ -1,67 +0,0 @@ -/* -* 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 - * - */ -package org.apache.poi.hssf.record.formula.functions; - -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.NumberEval; -import org.apache.poi.hssf.record.formula.eval.NumericValueEval; -import org.apache.poi.hssf.record.formula.eval.ValueEval; -import org.apache.poi.hssf.usermodel.HSSFDateUtil; - -/** - * - * @author Guenter Kickinger g.kickinger@gmx.net - * - */ -public class Month extends NumericFunction { - - /* (non-Javadoc) - * @see org.apache.poi.hssf.record.formula.functions.Function#evaluate(org.apache.poi.hssf.record.formula.eval.Eval[], int, short) - */ - public Eval evaluate(Eval[] operands, int srcCellRow, short srcCellCol) { - ValueEval retval = null; - - switch (operands.length) { - default: - retval = ErrorEval.VALUE_INVALID; - break; - case 1: - ValueEval ve = singleOperandEvaluate(operands[0], srcCellRow, srcCellCol); - if (ve instanceof NumericValueEval) { - NumericValueEval ne = (NumericValueEval) ve; - if (HSSFDateUtil.isValidExcelDate(ne.getNumberValue())) { - java.util.Date d = HSSFDateUtil.getJavaDate(ne.getNumberValue(), false); // XXX fix 1900/1904 problem - retval = new NumberEval(d.getMonth()+1); - } else { - retval = ErrorEval.NUM_ERROR; - } - } - else if (ve instanceof BlankEval) { - // do nothing - } else { - retval = ErrorEval.NUM_ERROR; - } - } - return retval; - } -} diff --git a/src/java/org/apache/poi/hssf/record/formula/functions/Year.java b/src/java/org/apache/poi/hssf/record/formula/functions/Year.java deleted file mode 100644 index b461a09668..0000000000 --- a/src/java/org/apache/poi/hssf/record/formula/functions/Year.java +++ /dev/null @@ -1,68 +0,0 @@ -/* -* 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 - * - */ -package org.apache.poi.hssf.record.formula.functions; - -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.NumberEval; -import org.apache.poi.hssf.record.formula.eval.NumericValueEval; -import org.apache.poi.hssf.record.formula.eval.ValueEval; -import org.apache.poi.hssf.usermodel.HSSFDateUtil; - -/** - * - * @author Guenter Kickinger g.kickinger@gmx.net - * - */ - -public class Year extends NumericFunction { - - /* (non-Javadoc) - * @see org.apache.poi.hssf.record.formula.functions.Function#evaluate(org.apache.poi.hssf.record.formula.eval.Eval[], int, short) - */ - public Eval evaluate(Eval[] operands, int srcCellRow, short srcCellCol) { - ValueEval retval = null; - - switch (operands.length) { - default: - retval = ErrorEval.VALUE_INVALID; - break; - case 1: - ValueEval ve = singleOperandEvaluate(operands[0], srcCellRow, srcCellCol); - if (ve instanceof NumericValueEval) { - NumericValueEval ne = (NumericValueEval) ve; - if (HSSFDateUtil.isValidExcelDate(ne.getNumberValue())) { - java.util.Date d = HSSFDateUtil.getJavaDate(ne.getNumberValue(), false); // XXX fix 1900/1904 problem - retval = new NumberEval(d.getYear()+1900); - } else { - retval = ErrorEval.NUM_ERROR; - } - } - else if (ve instanceof BlankEval) { - // do nothing - } else { - retval = ErrorEval.NUM_ERROR; - } - } - return retval; - } -} \ No newline at end of file diff --git a/src/testcases/org/apache/poi/hssf/data/FormulaEvalTestData.xls b/src/testcases/org/apache/poi/hssf/data/FormulaEvalTestData.xls index 99cb61a58811b2246af6cc66651997d897dffe0c..68a12d834fed0d5ed7268b383a765729e8c9fa0e 100644 GIT binary patch delta 5088 zcmZu#3sh897vB5cJ1{fA0D`f1JX2nrE$S) zr9WDluB?_e)%Z$jX=Y0k^0$1al9r;msNXcT9$FpPF}w)NA{U8C)qT>|rxV#ggel<0QcF|fRNdrr@*vRmyP-+qR_aA_kb2K(a(=rGq2a9szdPq%32sOO@e)w^taqCNal z2A7IDcvHU@#+S~UJ#o^s;{5TmX3ts##eQ%_RMwlaCQU1x_T2aZlg2r_TGLZ^)aD+6 zQlYyosORz?Wu-|(p78xIa0PHiGLadBrS3#)fzyE|sQVUh6>v%lkwGT93S0>6(1U0n za6ZsqA^H@U3ykeabO2Zhj581!dl9`0Tm)?AM)WsObE7gU?TrN7iLL<`0gF#%`Vd_JehD0DB1%XjIt;V}2SeBqR-)a&jle!0kZn5AE?^a~3s&aem*`{QB49f& zB%eW416&LYx0DezgS=%SnwE(yy@@UZ%YeiCVOM~4z)3zt8CgWlz{{XV2gpPfRhFg z$$>;yfD3?=2NEekL>GZC0!QZ(-3I;uoDobkWDrpk@M~aU2vKSt(Fx$Ez}z<2p~2W8 z;A&ueC=z)Fi2&aKmc_Ki4uHG`Ed3Ws5k~Y2@Ks=OJ`v-LISDKU4tf@K2i5_xXzD)% zwFe#sWLx~On4+Arxm65|xM&NniP?>1LaH1c94Zso5#)uK9|1ps7M-UB!IPyml z9RVH$_JK&UM-kNm_W`>@6Un1dOW^0gF3?2O7>E@35wHU^5mkVD;A)@+ng|(-?FLo? z&CrBLAw&yY0K5xqE-WLO198k62LS?a0;fV8Q=TLG3HUQ`475>HM06H-0yqeo7&4yd zDDWsS3!3OZ0Z9VC0;WO}eTtDJa5wNtXd-?h(H7u(U=TDBIEiQ_usR#W3!>o3IHG~m zfxiHofD@sO*QX#$pzl!ET~& z_d`APhF0p$Gb2+lf?98Unbuirhn%%4KHNcH-+i&bf$%+oJJGpGtGXPY2a&cT*CD2a;7P7 zGtF7Yv}`leN86Z=?qV_@X39H;H|HtzooC8vV48PXEBKYy+KT4gG%7)L?~`rRf>cEb z7i`_)S+ZPPS!45C+Le_UnAEIgkxG5x5=EW5?0cEi8Z~oyf)d()v#icr{;?7-Tsp0I zSB|f)Q0rH0RxSwF7vET{)XpwYRKv<-IR<0;tgKd!2*Zh$t0T;WiqK@rB&Js+aVODC zS}!$N_u>ueN3w11s&YxruBuS)RX(Alh`FNGUP`_ovNXVj&~0^^(i?7N~2kB`f7f z%y#Trj0YQS_ipXyG9wxBc#2@&WR!d;6SaTb*1rW?b_=$g7Hk7runl@>v!&kY$YpL6 zMma2#RnLT|2S_YgnvIl+9j{H#!1qTeJd_6>O=&nOW#v9bGCk-w&HWcc$ttvGNMn+6~zuNg1WPaAeL^WKtyodiFiTkio1XK*1#6Wp=7~IEjUB_ z+SGf$nv{)@qV}4B<77c>2JYl@2~6bw4!bp*kiWLiz`aHVIY^#cIb!bCR+aQq?@h@XK=TFil%F2Jx>9 z)9kflILIikL5wpKu#h8Sq@@^an8-D~nB$bhBba?=G0*qZmGz$dP+2inQFr$T%F5K0 z1tp;lj!P)3&Y%e8Kv}MS!z2BO=v0>IkAxB*Rn~j5q1jV>ikFF)QMG!QHgI#OD;7pk zKnxS=iq*SC6r)~4JrAqsc1INb2?*;=GQ5OlHp)UXZ%YmhZgj3K#)<(>4K}4YG`LZA zYX?_{2si3;$FexZHo7LV+V9Nb>1?{BYws%M*=Ih_hr^V=4r42=RVcfUDErhZRE>sf z+NLU$eLc4RLKVth4-#07vYZ4NP>r(h!8rS-YW8X`ui0CmeYS&p%3~p`-*@m}C0vB+ zzms23;syJ1C#Nd4@5jlSw2LPwJ4Cgo?&4gI(bqJ>1DRD%0tarWYPB(ewoA!m5P$6X{L|b;jse+vn!CzkN6wwbo?Ef{R%bX#K7@&$KEnx$zZh0_hNERG zytka;E{dlxoff9`$U;7gEXIl~;?Lqd$%j|LS!c{Q&mw{%+z+1ROyyH#Xt$i>AVm(r zDmz}}e##6nvh*VS$_3ke5kt2j?5T_VguFwcZqezPB2VLv(p`Zb zoH@fpsu=O$%&B*m;U&(TdV~I+!Z7jPt9L99OTz%9=lJV5b0X{Y%p7MW?b}z1^q{IAfcLYT-1_S1Oe2K@R##8}GJTPs+mK$#?@X(pK4s)-2> z`C)6VX05PWq>U?@B2;GdXbDQ`u7IYG@gxR310k@<{qGo-J74!TNJ?tt4>*@7`poSZTr~t8M|1X6>F6{2Fq=cXEN`q ztdL3DL(5X#QWGV<-K$nGxc6(5!P6AMOtExWu~#%12HIN~jJ9UpmSIN6ROt?7jKA0AYhyoGrg6XR z;c3eG-?nu|TU-3JsxB9WHPBpa^j@X+q*Z#%{NJy9DhP>r)N;i)SVRtwjI@~jl5Apz zL%ik?=N%&0-(HjJ5Y-OxqeH~@x7WPj5Fa^&bAbI(nnRR1#8(dCH^5$#?GSG}#J3JH zBG6t_=nw}TqT3--2HI=ZJH+P$%WNBbEXXcy1`V@iLI>H47dyoJ4sp{V;)CtQi-YaM zmBIE=6CI*t@N>3?pAYo51P)nY`{nf^_K{xEH2;ybE_4zwC*nD++UR zpI(&tL_uM}OK?%tWlQ=PAHT-`NH@9B^uqat1!=jD*`K@871g?SOi5gkb2O1FO4fs$ zK;38}BgSapc2L9+8Q`~#V5#(7ENWDu)!$fA3>iZdp(A=7 zd=vB?OEkcVXbY%0Q4u-EV*$=Y#o$ZeEwCHRNg!GbyPX3sfR8*xG}l0M3_K2|jU$?7 zB-#%i0^<^i9&#c22lxRPmP9lhJF5b#LEmId>_+q&_!rPGg(%QeL{tv3%|xW866xKE zR)R&~_u!ArTE0{ikDAR+e3H%C7n}~%xK=dJa3>-0uXjC7f_rQH%#AKo< zPoj6gy;Tt-Uh|1=gXQy!uo{>{{}_%D+yFiW zbI{{Nm%!)2dGN+9umyZ-0q!53=mft2AAf>q0zAt5+%SBr@{ST#v-DT@Wdxz zB^aGU4cr8V zFUELyVUwkC`P)T5EyteAu%+EZeGkE!ALC3<5-mIn@3+Fj?XcZXl=OuJx{Z%$&Oukfsusr@`` zbCIQcHM0mD`KVSu%xJoZXBV{nc4vwbrhKX4fqZYu-QB%n>)y|87rD z&(Gj31TKN@AbvsIMRX&25V#n!u4(vtkb)Hq?;N3)W3Abpqtrd`PO@m7i6XRksm^k) zbF7-Zv&`aib+pJd7wW9juZFs4nL0LGH{RUGMJnmhM|N{3y<(M)6R|L zW(C|kiZ6-_xRDwyn&W*$3#4{tDu;>;*-lO8(Q0K@l@_1Qv1(3Ot+qU!N2&P`)#?18 z`W(cibexRxh~12VUymeYi{+*DmHr--9+X--G5pg^ZGZQK!_ zsa9h8ZAUm>mE~uT;DYOAQA8b&Q1^t@*37Hpp&sI}%x!99e{n#ncN;lC?33!GRFQIm zYmGSXJu(;2gxot)B|@nc*v$MUj#3X`s#lw2m0aXUO*q?1S#`aMgTz}>d7pyX0af$R zDPC<9+hwx81x=c9fM2)paPdbHuF)9o4F5vYD0q!$89)a!$}(& zwX%Pq^0W=jHk{GQE^;4jdMAI;>*Ge9^fBIIq$PdF@yR#vfz?2;F@@u&45?*&#KB}H zUwnWWbk0;M+$cb5Cl_+=VX~l)!DOaqNUQq}{><2LN~gtr&kOZZCB9jp({_K)-KrqH zR`~;mjq@cR{HN6)??8Mi$uHX{CH1D*h>2v%h;LtCS)~lc8f0oHzD*|0+{H&+rgShZ z#*%N{xcB~w+IpP>hjrsa-UBW1CMCRG$qivbxFgI6yln|@TIz%F)E>UhUgP~B2P6Cu z0SMV*02qiEhzLRqLg1xLLl9vIyqqZ%5sr}c-FxTHWNpPYUbp`SFLrn8_=%}f(KdQ0 z`SRVmvF2%^c)2-s3x%Q#HAn|2;*Zj0@AgsViu!Ok#MM&?RWrhCwHQyOK%9`NYELCx zorTmnPi4NE4Kc2-vQxCB{D8DIVvt{LR9K+BgoB|nvt9kG%9vf zQgm!|a&(+6ke`^65EL5|8yy#ulr%mrb@YRAsWCEAN7oF=ROTvr`#;sHW!lP^n)*kS GMBT4usuRQj -- 2.39.5