From 7d47c205e9c6878b870f705d27992822098c4d04 Mon Sep 17 00:00:00 2001 From: PJ Fanning Date: Sun, 10 Oct 2021 06:39:56 +0000 Subject: [PATCH] [bug-65306] issue with shift rows that remove rows and how shared formulas are affected git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1894089 13f79535-47bb-0310-9956-ffa450edef68 --- .../apache/poi/xssf/usermodel/XSSFSheet.java | 11 +++++--- .../poi/xssf/usermodel/TestXSSFBugs.java | 25 ++++++++++++++++++ test-data/spreadsheet/bug65306.xlsx | Bin 0 -> 9515 bytes 3 files changed, 32 insertions(+), 4 deletions(-) create mode 100644 test-data/spreadsheet/bug65306.xlsx diff --git a/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFSheet.java b/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFSheet.java index e953378dee..48436bb826 100644 --- a/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFSheet.java +++ b/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFSheet.java @@ -2951,8 +2951,8 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet { * Calls shiftRows(startRow, endRow, n, false, false); * *

- * Additionally shifts merged regions that are completely defined in these - * rows (ie. merged 2 cells on a row to be shifted). + * Additionally, shifts merged regions that are completely defined in these + * rows (i.e. merged 2 cells on a row to be shifted). * @param startRow the row to start shifting * @param endRow the row to end shifting * @param n the number of rows to shift @@ -2968,8 +2968,8 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet { * Code ensures that rows don't wrap around * *

- * Additionally shifts merged regions that are completely defined in these - * rows (ie. merged 2 cells on a row to be shifted). All merged regions that are + * Additionally, shifts merged regions that are completely defined in these + * rows (i.e. merged 2 cells on a row to be shifted). All merged regions that are * completely overlaid by shifting will be deleted. * * @param startRow the row to start shifting @@ -3054,6 +3054,9 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet { // check if we should remove this row as it will be overwritten by the data later if (shouldRemoveRow(startRow, endRow, n, rownum)) { + for (Cell c : row) { + c.setBlank(); + } // remove row from worksheet.getSheetData row array // Performance optimization: explicit boxing is slightly faster than auto-unboxing, though may use more memory //noinspection UnnecessaryBoxing diff --git a/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFBugs.java b/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFBugs.java index 156e89cf25..ae0770f555 100644 --- a/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFBugs.java +++ b/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFBugs.java @@ -3598,4 +3598,29 @@ public final class TestXSSFBugs extends BaseTestBugzillaIssues { assertEquals("2-1,2-1,1+2,2-1,2-1,3+3,3+3,3+3,2-1,2-1,", sb.toString()); } } + + @Test + void testBug65306() throws IOException { + try (XSSFWorkbook wb1 = XSSFTestDataSamples.openSampleWorkbook("bug65306.xlsx")) { + XSSFSheet sheet = wb1.getSheetAt(0); + assertNotNull(sheet); + XSSFCell a1 = sheet.getRow(0).getCell(0); + XSSFCell b1 = sheet.getRow(0).getCell(1); + XSSFCell a2 = sheet.getRow(1).getCell(0); + XSSFCell b2 = sheet.getRow(1).getCell(1); + assertEquals(1.0, a1.getNumericCellValue()); + assertEquals(2.0, a2.getNumericCellValue()); + assertEquals("$A$1+3*$A$2", b1.getCellFormula()); + assertEquals("$A$1+3*$A$2", b2.getCellFormula()); + sheet.shiftRows(1, 1, -1); + assertNull(sheet.getRow(1), "row 2 was removed?"); + a1 = sheet.getRow(0).getCell(0); + b1 = sheet.getRow(0).getCell(1); + assertEquals(2.0, a1.getNumericCellValue()); + assertEquals("#REF!+3*$A$1", b1.getCellFormula()); + try (FileOutputStream fos = new FileOutputStream("abc.xlsx")) { + wb1.write(fos); + } + } + } } diff --git a/test-data/spreadsheet/bug65306.xlsx b/test-data/spreadsheet/bug65306.xlsx new file mode 100644 index 0000000000000000000000000000000000000000..a2af1869fdc02c8a99417b9d9fe8be145779c8c7 GIT binary patch literal 9515 zcmeHNg)FrV@9&wl_WOHtgX9npfdFIxDgXeW0W2&Cp<2TM0N)V-00ICiypFgH z#1RZ}e5LAY3wF@sd}D1zorMU`oC$!3J^#P)U%Ue)@h>4=+_(W332%dX=U5*)Dyb3r zAXz7IC~v`q^U@GSTDu~n*B^9$XJuc z;L6gLe{faZ8y#O9e}*kv2?xwxzMVH{Lh~loc*_1l9H^foF)vbFaR^5dHSxSI^pbZN z40`)vgPO{%m8r0m&Nf~ppC-^xqhNAO`MhppQHP0lRpRl&@D3;!M5CEaSgF)#KS?FM zzcXuA(4t?*u4fRO$!Y~CsC^L}7;=OW{DcJEHJ-F{1w5)(UR2kl|bDfPR{hIg**%nD(;O?>7dq3Wdd7aqPDjM?(#cM%beSFwkr zRNbbSCU5m^X!_FNXsy|oN|T<2q|;b}&l;9X8y|kpbPF9RE!mOJZLtLb)*m52U{b^i-Ai-6!dDW84}3~j=TaJ&bZzH^!pdl$C}~%++>Pfv zc{X{TEG6s8?A#i~THH|ZL9TC+PICN6q7rw6Q-chRAeSPL=vlIl_NSLx3r3e^aFb#x zd!>OD_57LJaU-c-Q;B(7Si->q^1EZnguM<%rZZ*ky;d}5S7d4`W&-9FM(Or~w63pA ztU694Qrih`-8mG}dX;JU@Gp5rqKnNyLR)VL5q72`+UQw3Ko7#5~*}6 zlWQ13Mv(yk9N5mhvEp*Ev9~m`v9bK=#!A%mY*M*_9vL-1yuT|{qD6bl=F!vZ2k_DvO?&MLE7Xce#@UjZoboXPLqVpuW!l z+Dk0V$^P*Oqnc3$~XF-yoTr0p=uKV54~f_3p1y? z2MsAjPIC>7CFI@>%Y^*R*gyfNbc#}c)zduq86A8<9V87zFW(PGH~gZTL&X;9Y=!G% z6o8s~rdvtj&IdGHbp|)iI153*H=*XM71Rg|nZkoZfaKjQ}4L-pkg>XPePd<%q8QAk`@rJiOm z=Nrh)s&7z)e}zK(g0rS^_GGy~J<}R-nyyy%g}zrg&tDJi?1cC7 zQ8zPBA0_t?d%0CpB>VYd%|Z&SUzfGA!fO21+Qi{(F!6Mc^b*B*>}%1P$bS0R-CeDt zL0;^FJCmeA-+CGWWrQiyLa9@1ecjUpEw*qp#Mnsvh<=!Q69btBwBq(rdr4#JQmZWE^@%#kztp{Y=xoEC(A1*Rf51p|Bi3GvLHN_1-fHUhVjJBD#s3J?$dZ z+2LX;x-sJ8@;D>#v2fir(~oRq=Ld?{BcWg|W$-ZJ(A42WLGS>}q(MO|^%92Xu9n-a z%4$1|1po9U6EbL3$FMt9FshINP~l)S`O}^Jl`Q{uEpV{F4@S2C?okpuBHPW4`{}^< z0Cxj2UPeosx-W`%&UywXCTQK2C({@1b$uQfszaQ3Ha5_~4|QMU60d76?+n9{Uo!%M z2pLc@8S6Mt(HByZQc-Zb1#4z|ywMryrzfX0<}^-IPVlw(V|zJ$fSMaeO0qmeC@)`7 zJY16@9?ujQm>5t>_1jm1^dIZzwLbu5yb1Lvrrw*=!5!k;So_gxJSE^a^0@_vITnM`?lzwJCAHL=0DUD>?)rD(LnOoqTP=5C+)7=T6gf6aysVz7 zIh$_{K8{ozksI$&^LVn z=2z=%42v*#86m@CeRUD&CkDOnm*^u=N?b~8@91i;yFJ&@_?#GS8~!X>v75-^Dki?V z)JQ4ZfFy*?Dv;pPy6a6iz0k3_-QZ`m%?R^KW!A~ihqNIF49v*2^-KgjT?;c?D5m+s z8Sg!pZMc{=Ro*egh+%~@FKTR8GUQgSW><*?2W%?uQ_{^I8cEOB~?`d|$ zaQlt8zMSZ@I-|rovX(d`^9N6(yn(ywXNTjhPKz;WR6lLE+*b$%x;WBG7q ze7*^Jx>oPcWchn5keP3{N9T(#ioh5)o8@J8kf3ej=0yDUMWI8%p) z`7>KH*N>P#PJKB%Q*I`n;J76ND#cQievmUPoz*LIMoQ*xTJ`6L8i2}8`W=80P}Zgi zAVFg9%Sxhb@lPlKkVq)Kj8NmdWi&ETdKWwfl(aX^CS;;ZL!d51%p}%a(L(eelT4$F zHBz66Bm-+!kmS2(qgPf#@R)dKj%~cv7rljUj(Vzl7BimRE$^P5?jF_8eAP~1sP~I4 zWVyJ}p!#uib+cfz=y7!ur#MzPvKkQLbvG0=;&r`VJ^>ANP!YO0-8wnQcz&~+{i@w_ zWprU5Fl#n+4x z>Q?-ueuzIuiVL$WeaZ@=oZ{`LNN(qneK?v#y^G#p-cH6bH<1=>T7@{wJ|9Bq#!>8$ zPA&{$QQKN@YU_=DkkV9<+vT)mSDna&sAq5?_|uLVr_!#nfW*T`SZ!e@YqX8+OLPQN z#P~wbKE}J=J|ya5a7cx|#mZmw$6R&3d7#(*I9ASnZLBejgg54y^F#zl$ibdb#>8k5 zPYO4yw;7s)(bls|nU&s&!#uOOe}kyA?`cQdfYLXa6OR&n4IZX!Yl%#5r6P9ptza9d zakTS`fTg%aaIu9dB&9 zhMUxyg^*|`=R(uv$SI`y?-x97nCI)u#rty6o#pHZA-B2Kyb=>B)Hv^GeBq%95YcXX zFV3uCHIBzF{V#W4rZ&T&(-kj-$Q*aAeAAsFxZ?#!HG4OD`}UMx9>fc#D)$dJ!kZH8 z3u}Hk9TjTyTc+0<(MnPq0;{e5I4Dke>2_6>*jNJQv?QIHn<6g+A0H#JrnU@ZyFEYp zlsMuKYT_aW9-z;k;T=lyKIq7VNNuIB6(KzdTeAFOP-UwUSrguq?4HzXCM?ElhLzxi z;K0f=sj@uu&O0*Z!IwQ%cZK@>r$ejH9xFV3zh1R%k;e#>-dN4GeT1#Ynw({M*5X#L zTz>OGWge`D52`8`>v}iHsN@t~z=hI@;q^kSd{<19kCL5C_I6B7j;l-_ZbDt(M+1VR zKcVAEl0#`?_DR(Bxw@xpYMo?tke16in6g&XKIS;(OB0u3&#DGe%ocJDtkaY}q z*ASzjzab)5mzHA+$3@tw6R%6*3&mukMsj=9lL%8L>l(R?il zA1zr|%rwS)wMyC@39``(R#@H!)4Mh;zhbH$Y>0kFpEa>0xRoTqBE=Y<->_IBv1hT-k43I|LoGh?gCoUfE&nQlkMbKV zx=f!5DQ|14e2(HvNimxsU4Dgv20zXVZ&tFkwT7iru9D5cgiaPsaa*ZR*Ra&DXe=sa zRq8$SiCoJpIUOhwi%#O-bQa#=qxgz)WDI8Ik(B2fCRA4|rOqM|AqFy7knh$B(xoN7 znUAAd8Y$)~=aySR>BweS53=yT6$4?(21rhq5zQKEa$G>|3#ST&eEnWX+6l;)@1ocSh!W3y zY<=*mxg{#8o=-!Pos-8y;MGL;*}6yLN>d9}QL5`AU9{suu^RG_MYReWU-6zO&88Ds z!0Y-iETS>zY`G#V6`qgFqTYB30j$ZoIhT8``w`qciM_G54CbNy6HD2< zSJmCqSox-MvK;WJ=E^NK&X0~fRM-otH$+Mc5EXN4sEO*{E~h5s#z0+{c={Xn)_AwwN}a*$B) z$OP>)HdlLN>BaoDd$q5&wAoD}f5Y9Iw;%c#!&erN+R@%IIONK`dHvKb4ZD;@?nx>8 zTKSC8R-1b1l1o`hfcju;Y)n9`fAj1f#1TU}KQk?(G5@lxDM(L4y6gq#u6eT2n~AdR zW(N8(4xJX~I0paf0HCUGsH%>7!#=ZtoX+w#I?3s3?dJ>%lF7+-9`ARp69){QQn-zt zvPXMUT=l}jP?b&J%&Qc;0Tc2u!n1v3gT%A8a37V=OKsBX2RF}e@<*Px zDT!7bOJ`2`3l(x;@?hY>RC(I0;rL}uT{&h8+GSSlYnHn=JRT3z->=2+qvz4t*h(AQ zP$-(}3q466etq>M*mqU2HRRmi^$X?_yN}GnhZK3~`>}hy8GF_&`_|gmFEbGKo?qDU zVd{S}Tv`tj-NXtsS&Ik^s!ZxJ=H^2cxQFPpwi*KVwP$qE-UDvy zh|*8rF`{|p`?vpa8SwmV4eO~jk6b5=6=JZvbbqqrXIlQRRO`EN0#m%)fw8w z4SaEda3}u8E$&MIrnm|}ZG+l31f%O&=^cm zCis9gV`8(4L$_IucG1cw+h2ib9755CQL9*M2rH@DaWdx!P zo@Yr5^uE|T_BImTaXC-7^uR{UvmP8cIY;}QeK7V7_C|%R9tf-AG5xs%w)W<*o|L@` z*wN7(V(P$k@A@I9kvRn2&=zd_8f0K=G>TN!MI!HQg@sIqjDT!R%@**%n)4vycz z#L%c6YS7tV-rw8N+tKkRC?SRcX;2W0o|IIC9zmR0oO|??T4*Dh?jzzvH)c0AIW=Mt zi=4M_8z+q?wNr_RI~hGkU&o<8N)`l#A3@ybiN6O*0X2LMF535-s5!L_5nc#frMDof ze#gfS+u#TvLloSd3U2Nv07@4$vv9;|W;iEY|IH5T^MXM$)bPNLUR0k4M2rS3ogKE= znj+N1!tw^`xD^zy4Y;{&k&EF9n>!S!bw$X$sgZ&)5J^etLrF`Dt&3B0JGxEggvi z-ygW=5EVDvn7jaVPan^Y*s_<@Gx1iqw{F>)Nj|O!yyHj=NNsCIKX{wgp+ck<@k;nVwvw6EL9Ip z3{z}F5Mo**vP9KA?yy6V-;2D^4s1%RR zPHzES`V)c_{qxU?)GR&7w%Yo(-;GFxQ|94yTq)M?dVgQ?-BX z4bQ&1rXAH6yEz@Ch}m~f6> zsf){*Th2A^(R|b&jGWALj$+^8;sx3+N!Iy)t9T1D9#QXMibsm@lR_-#o6}G4B-Gis|M>1CC~~b zL~kI9s+;y2fQOY6&aTU<8A0+ojY&^Dd(7WNN_O2iJ9?~PxveNXki49V+_rb*!*E!c^z&IEo`d`AMA9Kk(DYP%3eHOg~jNMvu4o_>O-XHUQ8r1D(L5 zV%Pc{s@-1Eial%3pdHVa*ewAR`xGZ*2ERDTiwml+4iA;I(y0KkO(XkjXRLi6+L{{Tvf Bdh`GQ literal 0 HcmV?d00001 -- 2.39.5