From 08a1f9af227ad39a01844d4d9d0fc32947f4511d Mon Sep 17 00:00:00 2001 From: PJ Fanning Date: Sat, 9 Oct 2021 12:26:21 +0000 Subject: [PATCH] [bug-65268] issue reading shared formulas git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1894064 13f79535-47bb-0310-9956-ffa450edef68 --- .../apache/poi/xssf/usermodel/XSSFSheet.java | 22 ++++----- .../poi/xssf/usermodel/TestXSSFBugs.java | 44 ++++++++++++++---- .../testSharedFormulasSetBlank.xlsx | Bin 0 -> 8889 bytes 3 files changed, 43 insertions(+), 23 deletions(-) create mode 100644 test-data/spreadsheet/testSharedFormulasSetBlank.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 b383413e70..e953378dee 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 @@ -4666,25 +4666,21 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet { XSSFCell nextCell = row.getCell(j); if(nextCell != null && nextCell != cell && nextCell.getCellType() == CellType.FORMULA) { CTCellFormula nextF = nextCell.getCTCell().getF(); - nextF.setStringValue(nextCell.getCellFormula(evalWb)); - //https://bz.apache.org/bugzilla/show_bug.cgi?id=65464 - nextF.setT(STCellFormulaType.SHARED); - if (!nextF.isSetSi()) { - nextF.setSi(f.getSi()); + if (nextF.getT() == STCellFormulaType.SHARED && nextF.getSi() == f.getSi()) { + nextF.setStringValue(nextCell.getCellFormula(evalWb)); + CellRangeAddress nextRef = new CellRangeAddress( + nextCell.getRowIndex(), ref.getLastRow(), + nextCell.getColumnIndex(), ref.getLastColumn()); + nextF.setRef(nextRef.formatAsString()); + + sharedFormulas.put(Math.toIntExact(nextF.getSi()), nextF); + break DONE; } - CellRangeAddress nextRef = new CellRangeAddress( - nextCell.getRowIndex(), ref.getLastRow(), - nextCell.getColumnIndex(), ref.getLastColumn()); - nextF.setRef(nextRef.formatAsString()); - - sharedFormulas.put(Math.toIntExact(nextF.getSi()), nextF); - break DONE; } } } } } - } } 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 29bbde1239..156e89cf25 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 @@ -35,15 +35,7 @@ import java.io.IOException; import java.io.InputStream; import java.nio.charset.StandardCharsets; import java.time.Instant; -import java.util.Arrays; -import java.util.Calendar; -import java.util.HashMap; -import java.util.HashSet; -import java.util.List; -import java.util.Map; -import java.util.Set; -import java.util.TimeZone; -import java.util.TreeMap; +import java.util.*; import org.apache.commons.compress.archivers.zip.ZipArchiveEntry; import org.apache.commons.compress.archivers.zip.ZipFile; @@ -3560,18 +3552,50 @@ public final class TestXSSFBugs extends BaseTestBugzillaIssues { XSSFSheet sheet = wb.getSheet("SheetWithSharedFormula"); XSSFCell v15 = sheet.getRow(14).getCell(21); XSSFCell v16 = sheet.getRow(15).getCell(21); + XSSFCell v17 = sheet.getRow(16).getCell(21); assertEquals("U15/R15", v15.getCellFormula()); assertEquals(STCellFormulaType.SHARED, v15.getCTCell().getF().getT()); assertEquals("U16/R16", v16.getCellFormula()); assertEquals(STCellFormulaType.NORMAL, v16.getCTCell().getF().getT()); //anomaly in original file + assertEquals("U17/R17", v17.getCellFormula()); + assertEquals(STCellFormulaType.SHARED, v17.getCTCell().getF().getT()); int calcChainSize = wb.getCalculationChain().getCTCalcChain().sizeOfCArray(); v15.removeFormula(); assertEquals(CellType.NUMERIC, v15.getCellType(), "V15 is no longer a function"); assertNull(v15.getCTCell().getF(), "V15 xmlbeans function removed"); assertEquals("U16/R16", v16.getCellFormula()); - assertEquals(STCellFormulaType.SHARED, v16.getCTCell().getF().getT()); + assertEquals(STCellFormulaType.NORMAL, v16.getCTCell().getF().getT()); + assertEquals("U17/R17", v17.getCellFormula()); + assertEquals(STCellFormulaType.SHARED, v17.getCTCell().getF().getT()); assertEquals(calcChainSize - 1, wb.getCalculationChain().getCTCalcChain().sizeOfCArray()); } } + + @Test + void testSetBlankOnNestedSharedFormulas() throws IOException { + try (XSSFWorkbook wb1 = XSSFTestDataSamples.openSampleWorkbook("testSharedFormulasSetBlank.xlsx")) { + XSSFSheet s1 = wb1.getSheetAt(0); + assertNotNull(s1); + Iterator rowIterator = s1.rowIterator(); + int count = 0; + StringBuilder sb = new StringBuilder(); + while (rowIterator.hasNext()) { + Row row = rowIterator.next(); + Iterator cellIterator = row.cellIterator(); + while (cellIterator.hasNext()) { + Cell cell = cellIterator.next(); + + // the toString is needed to exhibit the broken state + sb.append(cell.toString()).append(","); + count++; + + // breaks the sheet state + cell.setBlank(); + } + } + assertEquals(10, count); + assertEquals("2-1,2-1,1+2,2-1,2-1,3+3,3+3,3+3,2-1,2-1,", sb.toString()); + } + } } diff --git a/test-data/spreadsheet/testSharedFormulasSetBlank.xlsx b/test-data/spreadsheet/testSharedFormulasSetBlank.xlsx new file mode 100644 index 0000000000000000000000000000000000000000..e5aa087287a5ee5f85b5303975f300a27293dc47 GIT binary patch literal 8889 zcmeHMXHZnzvYsL5oO2W;=PVhCLz0|?ff0}#2PG&&4w9oFaS#EC0wOTt0D>SOIZ99@ zXF)*X8@=y5&*6Bh?yvXfo;6i_*HrE9y;raA>;6_ReQk6M3IG-W2LJ%D0v>6%ggOHO zfGkV^fE<8>W~t)g?hA4Ey=`>Q6XIhn5a8y@QiOrVT>wBsJ^#=6FOEQ88b}XKm?U!N z+C>zo`p(OTF~X4%=<5d33&hX1_+|#(a&(oBj|MUl(al*^$q=qDw)#$6&$DO~CLZ;e zkm$==)`bk&)0(ZNHSVVb_u1^LXV_7g;4bo|xuU=8DxS&tDcws) z7aFl|>s;bpNSYkdksHWL4JHa2+_>Sn=88eTKY6!iXqT3!R!lh1eB@wU6A;FidRLH| zDt~}Kc7%;lz}naux3>qP!lhq8%tA2^s1;VDi@WL6auf{9hb5O5K7g=qU8L+r$YWi0 zcAqnUk3P5vE#29v=IF_N1&F6d4_KOYSNo7uBIIp2atk4HRuCAg_f3>=fZZDrlttdEf;%-1?z6o&wn9b3d zqMI&(!OnDc&h>TxJS_)2ar&$-E{?7_?Tj5P#;8YuzqjdKk}PDUEa zWE+z{zuXw|E)I4ecDekC-1oYdVjzzI!R26Zrf$4q9=Ys$|0R_>_+=kvo;_B6uNg+U zpF2X@bIN9qo_B59O7GyZQOb)=J7!R0VP9@O)<G6(g0EA4E5}P_h?N; z-bH~XawiNCHWciY;tq+Ix$&IfKI1Gpjd~1-QPym`vGrUBYYmUHCESLfEgThe&73W$ z;}jQ`$Q&+r9RrQblFOn6kU8pQl%p+0Nkz#wB1{5}n#%1Ri5(}1PQFG?o|(57hhq0+ zo2RapJ=gX98_lE6L8}frBK57A>)e)KAbHjIjyHf~5nMA9Xzj0UfU*R^S{L&(l8+3U z_SZ)X<4oC(Gl|uQqlL432f-9V>CuT#j5DG`Z%1ACAhzlfzHB?L-OlYiR*}*5 zq+2X~nI$hHyg+KQesiSgZ2i@7H|L_g469seVoKtU7}9w%)wyQzZw%=f099o!EBp!Er?`y@fTA7KFLj_?E)+ zS#k~``4R}uw_M@;Onuz>7veOV6ywHhwrl=<6GY($oQluH7Bxlgr0YlK(JpT*Ff?)- z5fMu$7}>UGcZN~{Cyq+WZ}d||4A)5WvK3X`R*eW`7U#4$-sLt1WDk>OG=?%`Z|l!9 zr*&<+CJ}JImr1Tp(BSS=iW2W_sVTl4^zM6;TBC_8h1kWZp=HXV39WNZ8d0g!)|w&6 zGlX2$l2awyP(Hh_`Guo?i%9(eE?V*v>mhY?FP->KRX>l)vf!XHi7I`-vU`<~7w?n@ zZAjsFO1gcb{!3Njs=+Mi>MB{>mnK62-vpBeRsms#NB43E1ll-HQPKW8nNPtbZfRHm zKq>tXQTlT&e4QX}5W$~U;U84qG}rT(k)UWJo`kaadbn{l(qsF@PS)mBg6KQlqnMnt zGt6!kDGqA}(@!rx1a>srWdd8agj{(C(OP`-uUSS_qMuM6YG~Ebyw@Jaq8}fNQe}G( zbUDAs?)PmvEV@vIjbq7croy`Fesrfi^N5sFAT;t^o1Q5JuiL&aKxcX`S7T>-{B+0q zZ4M6ysZx!6Mo!aKgcfH`dhCH6&HMLqtipYursx`&DXCEx8Z&S<+IYNt><(wU*r9$r zJ4;}oNTIxv3TSwyH~NUCwM%Oqm>&~*4U|iXC7U-craS=2#Py9fpFx+m4-w-aPK%<+ zz`HhWGj`V6J3fXtr$a5=0Cuypo-jEYoEZ1@E> zqB6ND0gVTlQAzC-0bfjkZBRl{> z#gE&mxuYZHn#JO96UWNU;&y`tP5^_7k(7^;A9Fdtf}=kRB;167`vD$gR#~+wudV2Y zTaykjK}IvDLdYai{QK2;4=&fg-#S?Tp1K(IA%XeA_iS}&uY;MN-|>?`LfGf_%=wVR zl?mozg}jjcuY1%>0p^{TCmmdEXJ_}MNkp7jhvEeD&ki?x;;QA(&s(}VcilqO)sB;H zsuo#(!C~CR@fD5q;^boYdQI6ua?25uDAXT!Ef0@zp;Gz|q>0Gu5!6(1j~m@4e-`S% z7!H2wgu6m-_XThy<;WZoSiF)D$d^-BP2U>jiAck z^#zpLPl|WseiTEqh6YZbRyZ@+a@L+q-gSobWQNbtl!PJV6*W5VkXFZPR(d?UzKS&J z2;t;9*M2wP2~}7%*UHbZV zVoB|KBu^zh*^EohfULz-fF8F!y13()r(%eg;e<{fqm&Q>3^JM_B-+XWquB%m3|1^P z=3yA7%m$Q1%DIa3Hv3}9icjI_F=PUtTAd=bon7P(E_(AM=ua5HyiuKZ6BD;~o0$d@ z=%s1djco_&X^Z_p3(xz;tKq6jFDL9wMwFjqTlPoai7o2jH#N_h%zcmi^6IWomL<6i zJ;phS*(==ckbb%W*{tN+#+Gje%T$XIalXj}ds%S#;roK8&+g==z3ok5vmmA?IN1z{ z9mD%*@pzNoQ#LL&c5|I~5;7M^E(jrQ-6hkP%}(GGxW*MXWH93*K2FE<)ob|hnx|1@ zH#e|adsYWb^(HvAI-KzIR_c_)_x7Ah9brk_yPl_ZTd(0Yx3I}r$A~ft2?<5hBLQ*Z zT)dYS=zaGM@Y0NmUuf381|oN>HG%%V*4o#|KUWmJz-OQKKNROL33?h$6TZkU1{U>t zh&-1uQpy)j_gu8&frW%_NV~a-E>W$Bt;J@7u^hXFwUu)-ET0saU$bnwuJMXsxsrux z)r6@S&dG%p~l`twsClLVJh`Ho=WN_ zi#>z=>oiJ*J9|UHo(Duh5?R8WAR%NaPiSEjQi;_D;$^Ct46lEtU|(qVfDuj$Gex|B4f>l=1LU5zb^+%e9sm^0QhFY{B9 zL{>{AaW(TFoO?IRXJ?$l_UYWZRt)-R%_2$~{kq!P8edQk1SK`5@mst*rj@(Bas*u? zot={egpwjp;w4#qV@PboZQ+SE=-TOcPm)6oP4VZYla_=WENY_{x$NMLK^z?0R9GhI zCN)ZaLbe?^UW2`gC&9(C3PbVDn2zun=H_hW5z?Z;N}?5DZ?-{CRpY9|(C}R8_hKXR zK#i*1o4ip>6(pp(@ygXuo0xPK!@P{g602^GMCI{q1FznVAaj`s)h`9_ASN(BzZTr= zjqeVwXj-2ovUwn7qRdI`cHFs0zuJuJJIU$1W2(`+kJZYygG0`rk*O{zqEX~u7YSXv z>vqGXkghpT+K2sG0yoj*w-+D6`GhdUZjBH6g;vq16XzO7+iBfz$h=IsM8*E^(&9f@ z714iI<^OwCngiR@{j(6N1!X!7tqHPJBFfcDgDb0z9fC`Y zk32ncHNm9a<}-??hqC@uqa`(L)Hw9HJIo&xL^YXhAS#CikM!Mc7JF&n)>}~)Rf?SK zcaN;*k=dJD*4%7J8j=(`V7)xMeuEn!F8+{H!wGcL#ifMRhuyO}3Wo969gh0OC?}_^x5prU86UW22)?ceV%Cqn z${{NHkyCi^zP=WRUS5TXbEEU_T=h`e9{Y@k{6fis1+DS4@MoC(U?S88>pej8o&`>8 zsJ85o{vBUR&I)e0Qr-#1Ihm)>&Uj~CKh)`=CHEkgmeqx%mfI}S@$9-Pd=Hx;#f$fF z7YCF&)QxFzJQKG-h6zh;KmBH~)gyZdN=4IZblHrP2?837Q9&HDfSJ+o0L!pXs{p*j z#i{F?;xG908e~X($&1ApeHutPn_ize5SCvRew&{M`!v!tP?+1q000R8m7jfl@3}&Je$XGD28x^c z0knth_sc8N*6bN!XuyTYDOF9-qqNQ_+~K8!JVa@F5-V?Af2}skqLX*UTmhM*#|5 zVTl7{yvnX24k^92Xjo%B=CqVC1y zwjGQI->Q`qR|s%?otiwYX3<*?Le1+Z$igqMa(6aEz8U&IQ$=b`PT0hD|3~F#D;%pCN`oorj5QNJw z`tP^&qng$37a!&7p>i*_p-El4H6|u+?8Q{-RAO5y1d)=dHX#$mEc8|9DHyej9z5sx zzbih0SWLwksIsJwYEqG)np6%R_WIr)o<4$ho}NEYhwA74)0#q2F*j97Z&8ROVn^dF z>K$~wUJb`MVW^z%^P9W84R5A4bm;KA8|73+ziv!uj!Hm2)xF$nEiL^Z+SoKBF5$vj zv{z+2qB$VqIi1<%hJV<4uc;YAo8nzkH>ZR=e7LD=V-szB0A!1<2X9l96$}O%c zG?3D))7DzDFXm=k%s4F6%WKn~oMXnGZ@U1)=0@@tm7#F)UyQ0N z00 zl0~2=QTvhIMkk?>L+m?Ys_{2Rn)#rkaDWrNl3bY2iB<{Y!_^&auDQ#5k=8^T4CTIE zf_L+zmuvd%QdOR)OuOQh?0?_F{vBNd?m?n7P}6fmRSD8R)79R?8}bidQH%b+BR=J} zUIz=s+j-C#5#bJhiDe&$TjsN=Ft3=HeZ3%+rWwx&GF$)C!(sml(S4-4)XIg_N@P&; zE#m{s@g586XpZZ#iu)EC2%f=xFqNME7r7CbaYH297P8R!-o0cWm3wZVhQ_g;XD*;R zdI{dQ3L1_g!JEf)6h4}st1JR86)7k$CoIGa^k zmw;&Q@Z#X~;;%Bz2+KbG>=N3T5wF~2Oxlry$4^s_B;HhimvDVhNF;m#;vj#VLjIXl zfBASbENvbY$A4{AfN1=He|I?lN3{RA|K_M(U;D2P{(5%!hvAQV5vm0J=_K*0;a`ta z{%rUTCDH%=;mTD%SG(%JJmKR0t;c@V_-Y&YmvK7YZ=1qb9bDb){c>PI`hRc#+lKr< z_p(d&TQ5I1kXOB2-QxWEmPm@0iaiFGEy*qXhu2 zN|CG9SJVA3dufKBFaAsNziR#0O!~_f0AOJJ)4Tu3tNPlQD3k#J1gIYl3aB~EKmPp> DeK;8# literal 0 HcmV?d00001 -- 2.39.5