From 244c3a65eddd262668ce175fa40665503d26e1d5 Mon Sep 17 00:00:00 2001 From: Javen O'Neal Date: Wed, 21 Sep 2016 05:20:36 +0000 Subject: [PATCH] bug 59983: add failing unit test demonstrating formula shifting is apply twice to shared formulas; patch from Luca Martini git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1761673 13f79535-47bb-0310-9956-ffa450edef68 --- .../usermodel/helpers/XSSFRowShifter.java | 3 +- .../usermodel/TestXSSFSheetShiftRows.java | 53 ++++++++++++++++-- .../TestShiftRowSharedFormula.xlsx | Bin 0 -> 8431 bytes 3 files changed, 51 insertions(+), 5 deletions(-) create mode 100644 test-data/spreadsheet/TestShiftRowSharedFormula.xlsx diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFRowShifter.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFRowShifter.java index d11ed1fa81..e55b014a7f 100644 --- a/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFRowShifter.java +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFRowShifter.java @@ -147,7 +147,8 @@ public final class XSSFRowShifter extends RowShifter { } - if (f.isSetRef()) { //Range of cells which the formula applies to. + //Range of cells which the formula applies to. + if (f.isSetRef()) { String ref = f.getRef(); String shiftedRef = shiftFormula(row, ref, shifter); if (shiftedRef != null) f.setRef(shiftedRef); diff --git a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFSheetShiftRows.java b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFSheetShiftRows.java index 3e2cd54030..6ff649fcc1 100644 --- a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFSheetShiftRows.java +++ b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFSheetShiftRows.java @@ -17,6 +17,8 @@ package org.apache.poi.xssf.usermodel; +import static org.apache.poi.POITestCase.skipTest; +import static org.apache.poi.POITestCase.testPassesNow; import static org.junit.Assert.assertEquals; import static org.junit.Assert.assertNotNull; import static org.junit.Assert.assertNull; @@ -24,6 +26,8 @@ import static org.junit.Assert.fail; import java.io.IOException; +import junit.framework.AssertionFailedError; + import org.apache.poi.ss.usermodel.BaseTestSheetShiftRows; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellType; @@ -35,7 +39,7 @@ import org.apache.poi.ss.util.CellAddress; import org.apache.poi.ss.util.CellUtil; import org.apache.poi.xssf.XSSFITestDataProvider; import org.apache.poi.xssf.XSSFTestDataSamples; -import org.junit.Ignore; +import org.apache.xmlbeans.impl.values.XmlValueDisconnectedException; import org.junit.Test; public final class TestXSSFSheetShiftRows extends BaseTestSheetShiftRows { @@ -377,7 +381,9 @@ public final class TestXSSFSheetShiftRows extends BaseTestSheetShiftRows { wb.close(); } - @Ignore("Bug 59733 - shiftRows() causes org.apache.xmlbeans.impl.values.XmlValueDisconnectedException") + // This test is written as expected-to-fail and should be rewritten + // as expected-to-pass when the bug is fixed. + //@Ignore("Bug 59733 - shiftRows() causes org.apache.xmlbeans.impl.values.XmlValueDisconnectedException") @Test public void bug59733() throws IOException { Workbook workbook = new XSSFWorkbook(); @@ -399,9 +405,48 @@ public final class TestXSSFSheetShiftRows extends BaseTestSheetShiftRows { at org.apache.poi.xssf.usermodel.XSSFRow.getRowNum(XSSFRow.java:363) at org.apache.poi.xssf.usermodel.TestXSSFSheetShiftRows.bug59733(TestXSSFSheetShiftRows.java:393) */ - sheet.removeRow(sheet.getRow(0)); - assertEquals(1, sheet.getRow(1).getRowNum()); + try { + sheet.removeRow(sheet.getRow(0)); + assertEquals(1, sheet.getRow(1).getRowNum()); + testPassesNow(59733); + } catch (XmlValueDisconnectedException e) { + skipTest(e); + } + workbook.close(); + } + + private static String getCellFormula(Sheet sheet, String address) { + CellAddress cellAddress = new CellAddress(address); + Row row = sheet.getRow(cellAddress.getRow()); + assertNotNull(row); + Cell cell = row.getCell(cellAddress.getColumn()); + assertNotNull(cell); + assertEquals(CellType.FORMULA, cell.getCellTypeEnum()); + return cell.getCellFormula(); + } + + // This test is written as expected-to-fail and should be rewritten + // as expected-to-pass when the bug is fixed. + @Test + public void testSharedFormulas() throws Exception { + XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("TestShiftRowSharedFormula.xlsx"); + XSSFSheet sheet = wb.getSheetAt(0); + assertEquals("SUM(C2:C4)", getCellFormula(sheet, "C5")); + assertEquals("SUM(D2:D4)", getCellFormula(sheet, "D5")); + assertEquals("SUM(E2:E4)", getCellFormula(sheet, "E5")); + + sheet.shiftRows(3, sheet.getLastRowNum(), 1); + try { + assertEquals("SUM(C2:C5)", getCellFormula(sheet, "C6")); + assertEquals("SUM(D2:D5)", getCellFormula(sheet, "D6")); + assertEquals("SUM(E2:E5)", getCellFormula(sheet, "E6")); + testPassesNow(59983); + } catch (AssertionError e) { + skipTest(e); + } + + wb.close(); } } diff --git a/test-data/spreadsheet/TestShiftRowSharedFormula.xlsx b/test-data/spreadsheet/TestShiftRowSharedFormula.xlsx new file mode 100644 index 0000000000000000000000000000000000000000..d8ac0c6e2e650e7cbe896cb589a16f7d11507c59 GIT binary patch literal 8431 zcmeHMgS09>I*@4-PhhgFwHHAt zUohwb(z20~Q|}WpE9USJEi9~=8i^H^iS6b;CAz-rY;vKkeQ8v~d~b3z(fUP%7Lo>E z{+ANtgW;{43=;x~`dc0}ngxWKxzj7lnnjPUh#Ka^vd9TxaAZx8l>Ul<)_m~`bBVl} zs#V>AdM&t4lGpo9PEY(_yIXV3Ivt}V%qAxbe))XjN9`+`ID+L@t~nTZE+r2Pjkhbm z+K441K^;}ESiki)6-x26#D1Y#H*rmtkOlnJNl;BQIt(Z5u; zxIZ+gD2_)zj*N|J`y&_sY$#-oFJu?M*AgP=S%42)HXqIlO1 zN8GX}TQ}DH5N=Wjo%2xu!p-FVO7z!#QazuP_UKIWm1?*Y)J)=^mu zJ16L4vECxIoik02Pl?(Pr(Du3f>y*ew^DXc{SMnN?61^IuHJzYM)BR#EwPZr%hBoKG!9V~feVipsQ7sCoO!E@IWc&ZQ<=>ZBva zRwE2RkoIZ!z8P9r5_r8cK=E~zvn>1pEf@0_HTD>bCgFXYeBz*of+>F5?-ZYZGQ6mOn-Y8Wr97yr zkvnTAZY#h@t z)J~jlp3F*V1FDpq4=&ipr2A>I&V01%IFE+Y-Fvz3_mmDC4*G|a6@7xGt6xbHiyXgq z57r`#000mIP~hCGLBI3F)y~1n(9X{4dn)^rGjOnc27C73eU!xxTlTV|OP)h+1137` z*}YVP>`f% z%Un#1tPl_ShDWGEy3FOR(`gV95NEe$G+Q+-W7Z1w-w1^i83w@jEZXIx&#MGe*bC0_ z6sH|T8*N+7Z%VeHh~+7ygJFtm-Em-$)*o9W{Dq)mmc7DaikAB4z{aA^YL@ zJ46Y;4qA;v9l99cy2qf!JUxZB#+FQ% zgBa$+o2WH=(o<+^7jkhwqj4l!C9HuT==b7I(P*|33ztLPIU>raN~pz{64!8QFB;sg zY5C1-p_c8^fGq1stF1Sa=gj3P)Gj$h1^R^_q#htYt39zXB4Dr?NsfCP<-a_X6v!k% zp0~+G&{D!CGtVPCn77(3)`&0tdv>)liS`^5g>4-VPcpAos z{DOoh?=;(A?Maq*lbj9+H7>H6hbc8s27s~|=H`)CEhhcN0~R_WxK0pqnKZlVwZ49^qLsYDEh&4N@hpddfqGf zrH;fSrf_>$RtJxuQ<(RnRxLh|c?T_V3#Cm&$(#lG?68QVaF-Ds@GG;iGX!@nCK*Jr z-b#gL>4UjXLuKOwy^#1@u%_`IMPxCL9nG*}?pFk)&Z232>nqX|! zW!5}dU%0a|tKuQi3#=G=L(upnaDRow-FPZZ%lyC(d-A?gg!=WGUZ3kf0!-ZQsec-* zr5<+T{thrla{~tx<7Z9|7Pe-N-(wW#Yf;pr>ug7JwJgI zD?6efX=nGM$@A{St-+_zTvv_ScwZk{rytTUfavW6CaP$~A$U3#7nM>RfOY@WwzZnl zvSj!}cUems&?Jq)-Qrm6MWzSKpjF?o$aB<7SyM_yAeN0s?>a)jqmv=+R(%c3u>=+$ z7Y+Z~ouD!yZ8ftaZvBy#f38cy{cW}4kN|*4!tZN`-{|6GZen8s`t8d0eK32^$HIxY zaXav@gwY*cZ>&tMK}Sd))@BQPv=qB}>qYe@Q8jO39u2m#QK-ZkWvjeN)J1JSQ<+D4 z@R{bDRfD3%vw_ZWx{+C`@(tTJw&cvud)RX?PfxeoxuJ2K_@JfN#g2i`l(as$l;Sg1 z1jGS};*95A>@bn@3wFVNv1~byyKP+knmsJ8zn!z>FauWz_{W7;bc()6iM?+aG7<7O z?FCgmQd*u1%x8N5PE{IS!zBq=N`}!?P9+j3`+`oCExG+kN;J?V=tEv>JLjiDa4HH5 zkrd}rQbF~ExIurgdgwA5>4!;PvAQpBd5l~HdR-yAD!qsT=B;>VTZxUJ#0ia0pX0U7EW8sZO6u{ z3F%wwX7{;Xot5~_?9I&ZxO!f49HPe0olhSIh@$9n!alEfg)0-*kAMGpC4kE__rtb3 z212C>kY;d&uw@nb49vTLXZ-eaB;KdvcG28KB9KZnM(JLxxPlhtlMU^N{VFE_;C`M= zbT2UcQAQTf*gk}wS0=Ppz5q!537sZF+%aF{k=o9o-mQRhzJPxN>xWnJx?O>lT(Ux# zWckLl^ZUX2Q&Z%iOs~7+Ih?JH+I~8$j0}N`?e&$OIAmedhKNXh`jj0KJEC=5?UfU^zB`{4rs-IwH+JUhE{uYx2PP!;w$S zg~LTQ(}JGHlq(RCNipw`;+1#9<}51K9J~^2QzRYX62?@`oifsV6=R4JufiA{uZ-R| zmygxsZk8f`Lk$TR-1Be(rVgk@i>Hpf>`ji`tL|YHJ9r z2Nc^K%U)#g9T-YEi*i0E2F7txFI1xr@nlzKu5OD+xfEjge9s`Co%}lJO+Pj7vw7vv zmOG`%+7c)2Nq&av4sHIzJWrf`Vftl3PUQ+CH?wK9S^jU{h0y&w-A?ZI;jPMfh)O^Z zeyMZ4OB6TD5?$a2uj(wfq|zz=;A2iwk=%KWqV^3-ChqWMb|^hTlSP;s8J)>06=lvh zlo9<4z6!Pk)?CYD=$i2W^F>y zNjSq^&JMNB&nQjVy32}u{YFP!*RAlt87=f(4KU}>m3s31C`5AjZ4Sc-NW4B`bqG(y z1`kh5n73-jvb7^A{a#lA>QjM4`youk#EFD%-WeG?2ihqPz(bs2bb89A*caSN%A+ju z^_n4dqGIygae;|JK*BWv{hhzpqpL#;l~>}!EW)qFG7_Hkl27c&h()R z6=}DOMCLi1685FjBG{j_9hjpN8ebFNTS8~gP&kzNY%rspS6Hi~a-qOfrmTKGgFKvJ zjk)qE>RtaRTzVllc3v{HNDevjelS9o<>m;RAI?ZZG~T6a3F}5pDH%fv@z6XlU6Q_% zz9rcVOq0XJw`043@P#FlgU4wJg8b~c{+;ubV5^$^m_^R>Nd<9rT+akLZ$KakO4lvs)<`yc(RPw%yi5} z`GWT(zHZ3_uX7E;CHz?Zjd$nzRJ&a9w(CxQ)tp%pT$=QR zrW*iT*s=ziwb3AnCs%*u)m0PhDwu9P!82 z*-y#)D#xwRovBSEOHyPiZEZwQUe-yC7}^7tquxM0M?rgjix!f%S#9I5Ivm8;vEkop z6by-ZjNizgkFkWB^(ut6PQbu*h{|ll@~ONaHr&WA zvOSw=bD54eZ}WBU2>~WrQWLhBMl~HENC2|KW2y#+O1K_ugs_6gSrfc{bGzEkaZ1oP z_!)j1esfzR6%}bqH7brl8L^ZEo6EEDyznE2XX89?kwbr&$m1N-dZcdoIm_K*w|%M;h2`c4S2_?=;t@gGL!l_5C5jLO)??}gklydC8b_+qz+Jk!=D;2-kh+0rA1V-HW zx{W)egf}P*3|C&460KVF%%@G&Kmzwf;}%YLlicX0v^4?m#Dg{H^4JT)>8INKgDmu1 z##ccQH4Kg>b(*Pk_{0TyMa3TSrUDEcW_!$-mR|9PkmYFE4vQ?VQ3=rz^yx~biR}QL z9#FMavbJ3d6H6*4vqsKCQ#-}bnZwW7*7=5|gwSt%Z&ohWgs2YMe=`5DWk+w`W+(iTbIp)yCD{E=NPmlo=>~CXv@@(JRm!2tLmf=a( zfq47@$2c*vufDKALONT|2d%;M;2!jtg=!%}gHvd##rx~paO_=ZL2j~bHT|vfNE%^> zW|9&KnR-|kmGjM`omo{ngpy~4u(MLQKuJhEU2Df$#Cd1LcW3glUu|!vkEElghlj;x zuG?eCFw8PWs8hgOCvd{>Sg-&>(w}vbB4}pASU^t4(lS>URZ$3eZEz3^dFtZu$mRHb z-%_u+A-oa>*vQ$!xqCXjzI|q|LHU<6@ zQDFRwi0!uOAt&fN2>6E8?UB=viz}--E2YegK3!d%`gum<;p?K8^LO5~7PZz(2FsGr zldWsg&G6icSY@#8RDlR56+Il4W%2t85m2fLpL8hJqKxho5~>q?)WeN0iY$+D>I3LB zb-<#2%9Mj{wAOxKDYSyB@jb>D%_aZAKPKIT~|xW>M~agj|YOqY03x)RCgBj^7S4Dk<{afe56QniZJ-bXKG~?2P?X+050eLS;r!9mM0?(_xQt zXuEvEzjUz1`=f7}P!|S_TW-%%BfYFUY z99phfKJf`%ga1LFNLkmW0Bl8L#AaTx8ljfxp&+`Y`jOgQsYtpZgx9UPe77B467bSW zLKQsk2i`zHxs$!@+d>5CUmzU2=6b+1$6q=0N%Y}d7>q+eSS$8lIb;9^|3@L%x%zh3un zyONN7Nyevl8a>gc61tIti+GR73jb?D6ZBit1tTgoj^&z>uN*O4U;XWcsEva{^dQ_r zVqnz+=BL`Sp$(AHW&e#XIUGl`kcGCSYKt^Ts(B_K3N>i>I6M$Rv?#knrci{9rsCBV zbewPA4~xvDzqd=d;_j?|U6o1*r`q}XBYW`mh2VsKK|F7-;?B{1i_lGBp`@iVBKO6t7*8MQHg#|BI zCw|fHe)jOQLiWRh0Qx`bWj{Okd3o}~!FR>&dl&w