From 307c17ae26dcaa69b8b3edfd57b180aea45ebfdb Mon Sep 17 00:00:00 2001 From: Nick Burch Date: Tue, 8 Jan 2008 17:18:38 +0000 Subject: [PATCH] Fix bug #34023 - when shifting rows, update the any formula references to those rows to point to their new location git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@610072 13f79535-47bb-0310-9956-ffa450edef68 --- src/documentation/content/xdocs/changes.xml | 1 + src/documentation/content/xdocs/status.xml | 1 + .../apache/poi/hssf/usermodel/HSSFSheet.java | 53 ++++++++++++++++-- .../org/apache/poi/hssf/data/ForShifting.xls | Bin 0 -> 18432 bytes .../hssf/usermodel/TestSheetShiftRows.java | 43 ++++++++++++++ 5 files changed, 92 insertions(+), 6 deletions(-) create mode 100755 src/testcases/org/apache/poi/hssf/data/ForShifting.xls diff --git a/src/documentation/content/xdocs/changes.xml b/src/documentation/content/xdocs/changes.xml index d0f4f0e642..ff7a397290 100644 --- a/src/documentation/content/xdocs/changes.xml +++ b/src/documentation/content/xdocs/changes.xml @@ -36,6 +36,7 @@ + 34023 - When shifting rows, update formulas on that sheet to point to the new location of those rows Support getting all the cells referenced by an AreaReference, not just the corner ones 43510 - Add support for named ranges in formulas, including non-contiguous named ranges 43937 - Add support for hiding and un-hiding sheets, and checking their current hidden status diff --git a/src/documentation/content/xdocs/status.xml b/src/documentation/content/xdocs/status.xml index f4d7c1b07a..575bb6ba33 100644 --- a/src/documentation/content/xdocs/status.xml +++ b/src/documentation/content/xdocs/status.xml @@ -33,6 +33,7 @@ + 34023 - When shifting rows, update formulas on that sheet to point to the new location of those rows Support getting all the cells referenced by an AreaReference, not just the corner ones 43510 - Add support for named ranges in formulas, including non-contiguous named ranges 43937 - Add support for hiding and un-hiding sheets, and checking their current hidden status diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java b/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java index b0d8222865..5855a4937e 100644 --- a/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java +++ b/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java @@ -28,6 +28,7 @@ import org.apache.poi.hssf.model.Sheet; import org.apache.poi.hssf.model.Workbook; import org.apache.poi.hssf.record.*; import org.apache.poi.hssf.record.formula.Ptg; +import org.apache.poi.hssf.record.formula.ReferencePtg; import org.apache.poi.hssf.util.HSSFCellRangeAddress; import org.apache.poi.hssf.util.HSSFDataValidation; import org.apache.poi.hssf.util.Region; @@ -1215,12 +1216,52 @@ public class HSSFSheet // Update any formulas on this sheet that point to // rows which have been moved - - // Update any named ranges defined for this workbook - // that point to this sheet and had rows they reference - // moved - for(int i=0; iKM8Lh z!^7VSQ+z%8BtC`s9pVk*IDNpL@v&pcp_%EiqsjmMFeGIdH7LV8WZhxJAe{+4pTTDX z{0g6-_wl(zem)}0J|RDM;K@;AyI9qcU$zA$I8R+~4#;vJm!GZja}hob@hw^Bc}eMk zcX|Qk6XGsv$P{0U8^f@K30T4*Sjf2ezf|HVY;TKrf=aL@y{RbYsFbwBr-ZjH(Xu68 zfR1!7UJPoiv8=|HmMyWpY>Ay^N*JPF(}ympA$rS{5GWDe`v&Gkuhw-goHfHSOw5tt zB@9?$IWDz<7%kCe_-4$@t@pRKb+om0Z@Zz>O14BV#Vn8c*&=Lv&;HEe_qq$TU}-j4 zhz(*rD>}t)aHUE2l;0sbV5S~M5J2OlC=R>+eurVN*d#7*Q>3^+^&VlS#K8Q|Kzw&Qqx*?Kmo(iVYGgqxr504e;(a%f$m(sr$ zK>tbr{ow%m=>Ym80ram1&_5eMzaxMiBu^0jqY{U!a<6_q7eH6?l$KZWl+wQxfb)$2 z`eOm~?+4I77C`?_0KHmyzA16IyYcEr*$H6)BNE&8vqloLB`J#go$m%lZ!RNP7h*4~sDKnxz$3q^b&PVOIqQZbPc#z`a0K73fl{ zs=z*}ssfF+stW9ssw&XTtE#~9RaFH#l&UIlWK>mwL$9g|97|PIp!2J$!lr5~bW~fR zv)T$>)mC^%wH3B`DscR{oq%yNF{pWs9u$e#UPG^7CW`Ka1C#6EBtj`jsuGYwe>h0G z5|C8d3!*#0UVu1OQqr7K(XkgOwx8Y&{)m>EU;`QS%?{#hZo{p}u*Ga}{>QuuKg z$mL#;On@YXKb&z%@=2{b`v4M`-ASqY`#%sMN&Yi7NW9p@+itt9!0u&Ww-dlkt98-v zNQMPIw37*4C?0OPh3fYg>fE_=B|`Q0_m>FerddLfR+I@_T_wDmRw~p6k5I3l3{bb9 zP;RaKeVWoo<7Nn@MkBRfB9z6fFl*SFDs9d(#u}J~LcXLGXV0E3F&a0`Y6WS)mccR_H?4HZ zg#Eay6|@77YELHKrH#k!s1UW?Ye_4pcQ()M)TnohrbeTm%WXw1$8RH3+Q}A3u;1ds z*a0b#jyjHrOx7)3sR!lzm9ilDk#G>cjB!Aa@oSFm{Poe(RtboDsT8aeZl)Yb0(HWL zXD|rgHqru)6K)@ZL2#s59wa{!4ubuo3j`T|orHs|s{};7gbi*E zH?|e%ggc~Q5Wb%+3z8oR2T6KBknz_^ILP`+K-5d=;O6jti}7{~o4Zu^+GJ;PaBM6ynd_NL>=tuWT`?HZmcm6Mg>&__A}Kl% z2k{PZOsF@AYKLLqeSJKd$^PpruN7*T=7_;6U@gxk$fi+elPYD?8p1~HK_r`+O!nN1 zFZi=rtg}g%vRN0xM(t80n^-3MAFsXW&t{3vrni*M#t=5#$3(E|fuiD>>?<$*(Vxvy zolRdUo30Qx+%6Q^B+xg1`inoCWjdS9rEF3mY}B$=wx%|dee>U+^JmjUHof7NwhC-| zE3mZu$hY$^`?FcDv+=RCwh%UMOUsYE`kin4vuW1Z_*mNd5H@a0%a1(!@)Q1SR_JVe zENxQ=8@Hw9NAmN}`?I-NXX9gOmxZu#TUvhP@fUyZ&t|30#>djqA#B{1mLGZJ>Bs!p zTtYUR!!0cdHk&K3v{d@jk3Or|(4isNKJ8MSjgO_R31Q>5v{d@Jdp_;YrbTDtV`=Rn zY}}TXO8@Ygd;Qt0(%JY}T1N;Qx22`hXYc#5Kbxe^#>djSL)f@2EtNj?RMA80eY09; z<6~*PA#B{1mP)@qf0tit7K&DEVud|#4r=q}rGNq6xO1HZqPv879zy#;V=4wu5&^b}yjWRHzf`Y7nn)O#s@t zE*6(734-!C%VNOeCCMO$JCuCL^9%!OaOV=9T&*W}%B;t23cQUNKAOR0tl`YW#9ZPw zO|IpI1(53~7K3BBSeHaP1L-EvY)2{{Mae~>dyraE@%$-fOQmv_R4QjlrE;fmXuD}P zQkY95y`t1az+@Qr+tmF!@DeQBF&jhKcCBm-w~d{$N%0=c1n*@PS*LS0iaP7GI?+M` zb!KocYg(>-$BMTX$)#&Hjv8HBjd)>UNewTrW#Tg4Y`x;d@rlvN(cIMZEy)9!6S=v> zm0l>buh4)?lP7RFj(tBV`=6H?Z-LGaK%5h>2OeHoz0b}rnpZ8_hU1wlr>0=2+q}5O z#f7N25=xuWOqD0%78aOUSc`Da3pqZFOA5KK88*|?C}5*+Zt_O zWO*MRamS#zh2nC^FZN_+a?_)5FPdXEE`ej-17Nmv8a748qINXWpW(-!XN}zT zNKtT5ZMifaGCPh|#&Z+UR*$z5cw9F z+Ksl;8S?7)R%mftd_Y=|Q-UMt2xie7T;*nQqr4*gQM77?ZzNzD*B;Geay_KXalK8_x(NM2k7mMN>t(tTpZxJn zLcH-@n0?z7Jb1A{vm-fXz?~X&HXiZ+&Mb&S24Y#t{QGykR@%a~a+RDH7tO z>b@I|aj}UecJ+Y2>KW zun;JrQI~o-^3rJ>FA*2tH)a2;pHB&)at8X9vZ$OXds!mMy1a#RU@dkg@N@Gh)Fj@C z7R;|05XSPDNVZRBvKxlXeMTIu#1V@enL|B_{vkMW&WOZVH$Z&ysfR?08}(YxiBv zcyC#wctaNQMwK$W(++RWYvjFA@@9+cxgM04KXy8MUJYRYXYwlDI+^F<68K!XnS& zmqVTCu@mA}`!6qkXekLybp&g8>9bnH-GGVVtxXPByPQ9r*y9V- zWB*&$N8HsC(H?f-Kf)8-c3^unMOymQ#sn#V&q~tX;RJ(1$B!UVA9o_M?av^(^+8=Ss5UH=Sj=0Sef_%yOt=VP zf2iXKCz*1RX_-{V1WvNgN@CM576OUf*(>C7-PH84n|Z)-vlNB`@ezgJGkOzF9`}z< z9?3WlL4S+EfLDoN*~3w#|L=fxz;yb3Z#FwVmQmfGOm%msleUyn3q#}C>=qop@aP^E z(j_(0wMG1O>)H3eMdAJ*R5Ob2T#n;3^N@I7)h*^P@ZBD<5&*7prz Row 11 + // So strings on row 11 unchanged, but reference in formula is + assertEquals("cell B1 (ref)", sheet.getRow(0).getCell((short)3).getRichStringCellValue().toString()); + assertEquals("CONCATENATE(B1,\" (ref)\")", sheet.getRow(0).getCell((short)3).getCellFormula()); + assertEquals(0, sheet.getRow(1).getPhysicalNumberOfCells()); + + // still save b2 + assertEquals("cell B2 (ref)", sheet.getRow(11).getCell((short)3).getRichStringCellValue().toString()); + // but points to b12 + assertEquals("CONCATENATE(B12,\" (ref)\")", sheet.getRow(11).getCell((short)3).getCellFormula()); + + assertEquals("cell B3 (ref)", sheet.getRow(2).getCell((short)3).getRichStringCellValue().toString()); + assertEquals("CONCATENATE(B3,\" (ref)\")", sheet.getRow(2).getCell((short)3).getCellFormula()); + + // one on a non-shifted row also updated + assertEquals("cell B2 (ref)", sheet.getRow(6).getCell((short)1).getRichStringCellValue().toString()); + assertEquals("CONCATENATE(B12,\" (ref)\")", sheet.getRow(6).getCell((short)1).getCellFormula()); + } } -- 2.39.5