From 1d2f94829e8909c704fde1d10aa2bebd5563c005 Mon Sep 17 00:00:00 2001 From: =?utf8?q?C=C3=A9dric=20Walter?= Date: Fri, 16 Aug 2013 17:35:16 +0000 Subject: [PATCH] Bug 54720: Support for Row/Col Area Range like 8:8 or H:H git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1514812 13f79535-47bb-0310-9956-ffa450edef68 --- .../formula/OperationEvaluationContext.java | 49 ++++++++++++------ .../TestIndirectFunctionFromSpreadsheet.java | 35 +++++++++++++ .../TestMatchFunctionsFromSpreadsheet.java | 7 +-- .../IndirectFunctionTestCaseData.xls | Bin 0 -> 38912 bytes 4 files changed, 70 insertions(+), 21 deletions(-) create mode 100644 src/testcases/org/apache/poi/ss/formula/functions/TestIndirectFunctionFromSpreadsheet.java create mode 100644 test-data/spreadsheet/IndirectFunctionTestCaseData.xls diff --git a/src/java/org/apache/poi/ss/formula/OperationEvaluationContext.java b/src/java/org/apache/poi/ss/formula/OperationEvaluationContext.java index 87018e6abe..d1407c064b 100644 --- a/src/java/org/apache/poi/ss/formula/OperationEvaluationContext.java +++ b/src/java/org/apache/poi/ss/formula/OperationEvaluationContext.java @@ -37,6 +37,7 @@ import org.apache.poi.ss.util.CellReference.NameType; * For POI internal use only * * @author Josh Micich + * @author Cédric Walter */ public final class OperationEvaluationContext { public static final FreeRefFunction UDF = UserDefinedFunction.instance; @@ -83,13 +84,13 @@ public final class OperationEvaluationContext { } else { // look up sheet by name from external workbook String workbookName = externalSheet.getWorkbookName(); - try { - targetEvaluator = _bookEvaluator.getOtherWorkbookEvaluator(workbookName); - } catch (WorkbookNotFoundException e) { - throw new RuntimeException(e.getMessage(), e); - } - otherSheetIndex = targetEvaluator.getSheetIndex(externalSheet.getSheetName()); - if (otherSheetIndex < 0) { + try { + targetEvaluator = _bookEvaluator.getOtherWorkbookEvaluator(workbookName); + } catch (WorkbookNotFoundException e) { + throw new RuntimeException(e.getMessage(), e); + } + otherSheetIndex = targetEvaluator.getSheetIndex(externalSheet.getSheetName()); + if (otherSheetIndex < 0) { throw new RuntimeException("Invalid sheet name '" + externalSheet.getSheetName() + "' in bool '" + workbookName + "'."); } @@ -195,16 +196,32 @@ public final class OperationEvaluationContext { int firstRow, firstCol, lastRow, lastCol; switch (part1refType) { case COLUMN: - firstRow =0; - lastRow = ssVersion.getLastRowIndex(); - firstCol = parseColRef(refStrPart1); - lastCol = parseColRef(refStrPart2); - break; + firstRow =0; + if (part2refType.equals(NameType.COLUMN)) + { + lastRow = ssVersion.getLastRowIndex(); + firstCol = parseRowRef(refStrPart1); + lastCol = parseRowRef(refStrPart2); + } + else { + lastRow = ssVersion.getLastRowIndex(); + firstCol = parseColRef(refStrPart1); + lastCol = parseColRef(refStrPart2); + } + break; case ROW: - firstCol = 0; - lastCol = ssVersion.getLastColumnIndex(); - firstRow = parseRowRef(refStrPart1); - lastRow = parseRowRef(refStrPart2); + // support of cell range in the form of integer:integer + firstCol = 0; + if (part2refType.equals(NameType.ROW)) + { + firstRow = parseColRef(refStrPart1); + lastRow = parseColRef(refStrPart2); + lastCol = ssVersion.getLastColumnIndex(); + } else { + lastCol = ssVersion.getLastColumnIndex(); + firstRow = parseRowRef(refStrPart1); + lastRow = parseRowRef(refStrPart2); + } break; case CELL: CellReference cr; diff --git a/src/testcases/org/apache/poi/ss/formula/functions/TestIndirectFunctionFromSpreadsheet.java b/src/testcases/org/apache/poi/ss/formula/functions/TestIndirectFunctionFromSpreadsheet.java new file mode 100644 index 0000000000..b777dca8a7 --- /dev/null +++ b/src/testcases/org/apache/poi/ss/formula/functions/TestIndirectFunctionFromSpreadsheet.java @@ -0,0 +1,35 @@ +/* ==================================================================== + 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.ss.formula.functions; + +/** + * Tests INDIRECT() as loaded from a test data spreadsheet.

+ * + * Tests for bug fixes and specific/tricky behaviour can be found in the corresponding test class + * (TestXxxx) of the target (Xxxx) implementor, where execution can be observed + * more easily. + * + * @author Cédric Walter + */ +public final class TestIndirectFunctionFromSpreadsheet extends BaseTestFunctionsFromSpreadsheet { + + @Override + protected String getFilename() { + return "IndirectFunctionTestCaseData.xls"; + } +} diff --git a/src/testcases/org/apache/poi/ss/formula/functions/TestMatchFunctionsFromSpreadsheet.java b/src/testcases/org/apache/poi/ss/formula/functions/TestMatchFunctionsFromSpreadsheet.java index 7fcb1fa13b..29aeec8d1e 100644 --- a/src/testcases/org/apache/poi/ss/formula/functions/TestMatchFunctionsFromSpreadsheet.java +++ b/src/testcases/org/apache/poi/ss/formula/functions/TestMatchFunctionsFromSpreadsheet.java @@ -20,16 +20,13 @@ package org.apache.poi.ss.formula.functions; /** - * Tests lookup functions (VLOOKUP, HLOOKUP, LOOKUP, MATCH) as loaded from a test data spreadsheet.

- * These tests have been separated from the common function and operator tests because the lookup - * functions have more complex test cases and test data setup. + * Tests Match functions as loaded from a test data spreadsheet.

* * Tests for bug fixes and specific/tricky behaviour can be found in the corresponding test class * (TestXxxx) of the target (Xxxx) implementor, where execution can be observed * more easily. * - * @author Josh Micich - * @author Cedric Walter at innoveo.com + * @author Cédric Walter */ public final class TestMatchFunctionsFromSpreadsheet extends BaseTestFunctionsFromSpreadsheet { diff --git a/test-data/spreadsheet/IndirectFunctionTestCaseData.xls b/test-data/spreadsheet/IndirectFunctionTestCaseData.xls new file mode 100644 index 0000000000000000000000000000000000000000..c7f7173ec8de6b3f192148da24d9b61bfdd58485 GIT binary patch literal 38912 zcmeHw3wT`Bb?%xl%j=5Hs`0OgPWMiMCXK#|2oVzl;|lE0gBiDD@HXTmMMR`?x!3-NQDSBeSR zfnOhv1pL~6&Cw!x@VUstlQ6u?T!pYmw>W`wzBpgCT*-(#Gn~Sz^aIO1MO0&eo1Ow07LEW1nHk)3LEx zS@~E|c%dkQW<~j$^3|2AS62^h*nVY&nYnVqio#X!92cftzoK6szAC*?xhN4Ar3zJ5 zr{h7MsMe!9l-o<$uo*=!5gSFV5JO~Tqh@kuWUs7{fGam_Hvr|E%fa_hMbi+dREZ7v zpsKarM$KAjqn1{qQB;a;>BLkE(&EnQ$)9lP0&Y*s98Tr*Q_i_TWc;BUl8!% z z_4TAiIAut63p^V?+3-Ii@svO;(hiey_=_xX#ZP8=#ZM;u3t8|ylm-517WiLhfqy&; z{F_ixcnFYSl5k4C@o&3q1Eb#M8cvA1m&gYu&=S3fqD={CH{KI`J{0a_o3Jz~< z!ZjpO%m)8=K;@y}^O1JqbAbs@%2V~1g(m!YaW#?`nD`f&@Ff5D8FDu8pO`;?$?PT2 z%W(*^DaC$>prjZ0ir~)CJl50~dJ( zLLZ^YnG?n%9y=1c#AzU*8D>X9|2YjLG|lWt=xnEfgyxzZ2_4!rkkEv)BcZ#V1`?We zb|m!S=R|U{E4Oy&NT-1$w{|!>Oan=7?VLY7B)PSN0dzW?Np9_|m>!bc+QCd2U`#4ZjQj_P_7QR zdU{B5YiG^$kmT0R+UX(5t(|q#Ly}uN>!*h#w{|L$NH||QA&L`e^Dy=uUPwIsY$D|z zZ?+9y8&IM|4qhz#?h)aPT4D1{ zD^$(2!o|r7)HW5EW-^(Lu1;2-gmem~>YjStYLs>)*vGW^ z9ccqr{nOY8%Ws`bdcD`C%YkR0g{yEWHcvJ>RZz)a29Tpy1m0(f)c4- z+}L=~S?YYz) z?>V_0U@B#*8IsC0)yvW<*lpW&#IwgX;Os<&!tARh%H7_#t^M5`a56b*OmaeVgEG?P znOUJJS%Czn(bndfR%o4R1@BBNw9T|ad+rLixEElP3XAO8>b2Ttt=aF_L$R7;o^ElB zGaBP?6|P8}hdU+JVy)1`W}MB!kB=@wBF4KqP@xNV3`+|2k(XY1BT>Y(6tGCFNCk~ z%t1yCG?Gk-K62(Kzj7wC$WCTUCYh=nWH=NVy7A~EFaFzaoXIS-(vxH>ob6eUq3zWR}{=I9S^19AvDPcCzc0Z#?cyW|^Ig zgQcy{LB?unC%b-l=3CBW&bO0su(VA%$XG4yWY@_jUvwsOft`$lrCpqZjMdUkc0K;% zSDnc$x07+Ow7MK*td@4N>%A8qbtZEm$!yJSX&T6Eora~=)_v-cQ;H1CoPkeCpLUU* zjDw}E$w9_yX|;7P-gc`qnH6?24whDtgN)VEYU{px>JDczEA3<)EUhvJ8LOq$);)CR z)6QfxI~fN{tIk2jYH77~$DVn~nanCX83#+N&q2m&X|;9lKJn*HWac1T1N#TbakJSr zZeElq$vRdF(R=;3osq6iA%#WRk*=~KwOSEwmGyNyBVCh13j47mU28*XwHYDq_^S^( zBVC(93Tv?=-C#p%wGbhuhVODlx-NwjwqZxQ*@o0=4?O#YwJI&g4qvOs-V!7I&f8G*yr|=F!qwDJlSwR)}OAA0J2w z!LpT8#VA{0D_e}n-&9!*cND4-_JXMG~>uhxj6CTuwBT6zN=e~Y1nXFMYRe~B- zwi+dgITzzQu|hF4pE}3Klvbr<+xVP+}De zJHyr~4-CPE#O;wtvZ`I~NF}E|v&1DZGSL~Dh{H_d>3VlBf>1i4o(c358f3~faW~4; zJh!ZVN7pnDL9DRHu0ZI3K5Y0VyMZr*&+IacSQ&T9I~62*O43i7o{?Kr#gP6SaVhv0 zJ$gJA@xk@k1`XGR7&Lldl5A-NhDoQVmO;|jV~@&g?+I*VB|9Vf5Oh$NE;yF}$sTl< zh;&{=WvI}FBt{OSdMN@iVcI?6n10N&KOKjA7CIeq#K4q5`^-^+J6)!`70NRCn{%hqH-oDDo6X> zF;xWFtx0F0s0fPalP=5Ll%U9NanBN$(NqTmu^^;XlU}Q!4C)z#b;O`J$}6+WxQJq> zL=leB0&M#t1?Ni?6u8oUp){(ry${OWbVjPRW{xH{9|6vDgxKqggy{E_>u*eF)IA%c z$X-y0pkJA;&&lO;f*~dRNzXp()PtajbybeF$Ri3=Ib&x<7W@ zvrHVfFrsW(?Y`ZO=Q8fLTw~1}VcRZz9>lW{cygg|DR8k4VapJGi(?i#y?KP<>-ZI%6(!p>s#Jx=LuAZ1P7M`M~l=!-_RLt_E|m==%fQ4|V9wde#| zJ{%p>_1HGhs2D^|e>fDw9v%_-F<;Zx8`@dFs<}=>9b6v?M8@GV_fJYv#J|Ch8OT8$ za}SA3+pk9h!zL9J2L}+L#0|!x+DJGuj)|(KAM^#|a=vP?=_7Ao-KRxkzR<8QG8~A4 z6wCW!RoB9agtSQbkXBFrbRSwH30AU>UkX2}wN$oNddXTu*L*lB4|Po&Q`3W}R^F~S zD6iDYF?E8afl9v~jR#}p8VGCUt>N);$f5i_bbla_QVhiOhz}`kID9CijYPuZl9?@N z+##^4Y4K1j5Croi8N)10E8n4qhIf`za3kS3@Fw}xnyYY60A>KS1Y%<-89fk~U{SLr zTC-$7Fd7O+z+DCOHWCj~7MedEiGaD_Bq&DX6BFSG`QNXP`3?rc@yG_C?xg?@L3(D} zn>D{Lgfi5>lqfF9Cem+^;n7GqK0&!cz)=(oh7VO~-@!nwOv=y|>PDoTq=iV4Yb_86 zq!J3pV0bYoh3IrstVZ!vyILz{0gHJa3c%7LI@Mo1;tp&)FHCOY2hu|G#D-o zg($T!3PBC%AkthV8|+5p8P%Kq1h zEhG)4NA)08WtiJ~U(~?AUJFl9gH;%6Wq*O^HToxl>ulW<*Nm=-NWB5dTr=|9b_o%V zj8^$3z@c6>5e`&Eqg8Yra5YsU@sKo=Xcdjh9ADZEXgm7yK*}3p0z9B3K?Ox=>H%X! zLib?{_M`27^v}^ay@b&a%&Vx&R=SLI_nH#(L0<%26b_iC?U(+JJ-V~U+u7gNI;bW2 z#3=uU~-Oq?*mq*d){#(MR9}LwAiCMD57IPHif%N zi2d4}$!om^0fyJ8_Tr zF_dB1)NuoB`-U{Vy73$(xG;1AR+$?0EXA3s24m)pngVaGp0Fla{u4-DakUU%!?HUT z7UGLIAH?}XoPW4Iqg!YiyfqeuA*|RAVjTx|XMDH{Me$3bOKVL`Vcl9Pj?b?53`3}8 zur>OGeQs*J8M|D+ze6dHcFI+^5F3!7zU&mdojrTXMa8V)Ij=5U{JGVq?ANXNUTemZ zHm$;>X}KhmMX+c-cwZ<#_N&v&E8mp*o?fkdso%E*tU9iM+ZpH{+)Wzu#Iji(*xTUU z32t|DmrcXYpie~QuF-CM$K=&M*!dP2>Y|AOc`bnND6aLPZWs1HI6uNZSW6%z+I#!E z2YUDPx3-D?wgGQfm)H}+wnvnyjy`YLzo&CxPp9C6$)atbufKC}z+CJpAXcncfpH#% zo7L6B69LhVi8!G6#Z$~c(l<7ViK;Ky8wp^ZDyLkcuXm^qE3Jqx5EJR}{=xp{&OuaD z;C{K(s%fY;(0Sun92^!sy*+KB&o`k*1_B?_MRUFA)`tVW!O00^_6+u7f*!tWN|e39TJ_S44qOmJa=%>ZGb@91ldM`Ph}?;eVQ6#F^-6?Ih& zmmJ$0p8wGP-R;tQK1(* zU=i$liQO%`dy_c!hWpUfWgqV7+t=OI9ti4tumwZi)^Dk;-dc5doWkt(gJwupsI6gXN@-J;qRUUw}f_k5CR7cYtuJ;ZP+b*gWw(I(^)$6c1 zd-_09-5-Eov|nXo9R)DMv}!`q?(uo68Y2Ld<}xM}$O7!|`! z;iO#->}}u6`k_&(7CX!-)%FcE2eH%0%?y*bt^viH)OkmI{}B8F94g7@@Ge6j7s&?Bb>|s}ZTcdGEjklpqI1P7?ZewI~Pa)kl2T%ibIXMC|Uj z`UB)TynPr#;A3#9!%GL_3kKnlnCl%Gk-t4*Sh@W|M;|s)W4nT3^fqr>d-I+yHDX35 z71`bp*zWGY{DpRhbbF^X@Za}5?GFjxs|zus+tb&v6swWLCw_fIj_x9C@*H0AQ^T~_zu)-F*Pea-pWo?u%q z_Q9U}?+JbHQ*Yn@(0?5K%A;48-`G%9{LJW+pz_uKU1!FT7uI#m6pr@#WK%>tFwy zr{4V6yLW!zyEi`j&c>qUf#I8PzF_j>CwJet?Yq~Xx%aA%4U{Z+Ao7_TKGc8a`kMNm zKmN{t#QF;_yYKJ5_PuZX;~$k>bN3_nz4h${Eyw3=6LO~)?$r_)dD)|MZB z@bSc=Z?1U$^b2oyzxBYwS5^-G?@a?E-+$mQcYk&J=U)5bZ=btj+h?En*4%x&Htm`9 zgC9P5eCgA_S#aN`1&?3i{rIuBH~skRqOLV-AKd%1UmPv|{%_8lIr~qK9IpDsm*=&V zm#=uM@OwvFo_eVNwO{?u554^DuOEN-#L2(E{khqruYBsryT5+=j+ZeRM7 z#!sAm?75+{ANue+cfI?!*S@lJ^Y&jB?l?ZL#k1-o{JQMB7aeOK8~Q^s);uqH3FIY^ zmq12#r2#@z6FDL&(~Q`Z@VV=d3%_Riwe^*M|H zvfyKM&Kl6O#zrwpM#Z+7u~BmzVx#OZ#&_6ZY=s@hNFqCo@ke$TIkLkTTV%qfTwWQc z#kda>X2wu4F3ApKbdnv$C@?#WF@1Izqo(XI#*x`!We%|UfEC%~hv3f)7`CS~V2rD? z*JYfY9mZHfJB;r(w8NG;z|MDo@kN66y2~A4h)2yR$A>cPbywP8j22^5Pzxle;>s8w zR0dl#b0vtvpfpuU51xoj<@i8ndGZ>cN=;qkv$JN7PvMl|y)z06{~o}LzuPc#rMR{_ znVW?irC5OYKo|NzdXqHsFHjfn0EEo zvROk}J;F(%eX#`p{mh0TME6{i?;n~cHXr7>1Vq0 zl#H=wka(fgAR;!K6b>>*i&odDGdyl_4BRNvp_U>f87~7S?9DMNNo0gd{v@cNN<%p#Vu)fQ7^|r6kG%bhmlD zOnF*dDK$xH6sJj}SQ1_ZG*nZug)kqUCSgqyu8@SifVrS9bx;YjLAXk_HT8TJ$geWU zTR8ESI4P$D?4%5{1nhX_b5vvp6_CTA=PrgaV1Qr{ZrH{`X?7Oa29IJvCcPdf7CdPz z&{(}1Y*fOURY74IhLl8E0yFb405GOa+NUL zbaKU-IIB8h8^>upF+F7`&?Pm@9_^LAo0iLN>?Mj+p*kIbtiJ#TG(~(+DlL5aMWU61o&)cRC@AnRvgS93u_) zEBznbhcs%m5J`GsYH5ju;F2_gODqIwy$I3AH@)97)Pa2?#eeWpsSA>+<;m2A$rL9> zt+K{S2|IfK@}v1va_VIKXNhV08|#dI#7R2iR5{j5eYHrfg(YGR1#xQ?S*^)S6^! zZ8AkUE4x{bxra(sB(H8rrue{)!pDbpRH`zW+MG;LRtgIxq{>k)Dn)6i6lI`NY`IFY zy(-04dhwt~m6*bO^Cp*Rk^Eg`Sf>?crca?q@i2dB!8amreKdyNk}%cVPQg-WyLgO> zkB-QdYLm?0j1qXPDdW+RS>jGmxd?p7M@U$R1j=Obb38&K%jUoS8%p4PJXTWdf-%|- zI>w)V^mA{%(>pfrzMDN_)A~n#gy#@pBYdd=4MT_~oV3dhob>SfaB}1y!^tJ;6i#je z&?-4ve+DNhok1n~b=FaTD(Rf(BU#-6efA;Yci`myZXZtGyyg0zYxb*ga=p)e0B$%>;N(8QVVv9- zcp0a;51`iVb7g&iv;M9CV(vSTw(-(Fc{AC39h0n>dgGzIX1>@^61Tp?5IbdjGZP>2 z?GGQ4m+`!4@{NRI4v2AMgPRm=*xqpDfPB|c6nmqJcV1$f_$VM=uE$5^)x{zJ^G99+ zc?sktke5JS0(lAKC6JduUIKXu=(FR%A>rtapG;h{5t{sv4o0W;Pn=yTXA}Ew&85YxeF)vH+cOlPX7MCc=RJ` zj{nf1q2>I)QvPMqOpYa@NWM&#vY{Pm9)1h)r2LkmR4L0hWoc&j%GXUEz#ngrj{_!n z+P{buP0}Y1)2-M(p-L9vMV9+s+rpj!2#eN&SgfHoS-_!^@Xd|CuiRONoq~ d2S*XCE{s11BM<5yV`s)s!+)av%_MBl|38xN-QWNK literal 0 HcmV?d00001 -- 2.39.5