From 36f27c7385b67da5f1261c952982d8d09999068f Mon Sep 17 00:00:00 2001 From: Nick Burch Date: Mon, 5 Dec 2011 00:52:39 +0000 Subject: [PATCH] Patch from Marcel May from bug #51875 - More XSSF formula new-line support git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1210299 13f79535-47bb-0310-9956-ffa450edef68 --- src/documentation/content/xdocs/status.xml | 1 + .../apache/poi/ss/formula/FormulaParser.java | 6 ++++-- .../usermodel/XSSFEvaluationWorkbook.java | 18 +----------------- .../poi/xssf/usermodel/TestXSSFBugs.java | 8 +++++++- test-data/spreadsheet/NewlineInFormulas.xlsx | Bin 8703 -> 8935 bytes 5 files changed, 13 insertions(+), 20 deletions(-) diff --git a/src/documentation/content/xdocs/status.xml b/src/documentation/content/xdocs/status.xml index ffee4df0d6..6db3227e44 100644 --- a/src/documentation/content/xdocs/status.xml +++ b/src/documentation/content/xdocs/status.xml @@ -34,6 +34,7 @@ + 51875 - More XSSF new-line in formula support POIFS EntryUtils.copyNodes(POFS,POIFS) now uses FilteringDirectoryNode, so can exclude from copying nodes not just directly under the root POIFS Helper FilteringDirectoryNode, which wraps a DirectoryEntry and allows certain parts to be ignored 52209 - fixed inserting multiple pictures in XSLF diff --git a/src/java/org/apache/poi/ss/formula/FormulaParser.java b/src/java/org/apache/poi/ss/formula/FormulaParser.java index f9f3ca0c77..3e2f07cc49 100644 --- a/src/java/org/apache/poi/ss/formula/FormulaParser.java +++ b/src/java/org/apache/poi/ss/formula/FormulaParser.java @@ -121,7 +121,9 @@ public final class FormulaParser { private ParseNode _rootNode; - private static char TAB = '\t'; + private final static char TAB = '\t'; // HSSF + XSSF + private final static char CR = '\r'; // Normally just XSSF + private final static char LF = '\n'; // Normally just XSSF /** * Lookahead Character. @@ -229,7 +231,7 @@ public final class FormulaParser { /** Recognize White Space */ private static boolean IsWhite( char c) { - return c ==' ' || c== TAB; + return c ==' ' || c== TAB || c == CR || c == LF; } /** Skip Over Leading White Space */ diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFEvaluationWorkbook.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFEvaluationWorkbook.java index e5bec593ba..e9ba3c7bb1 100644 --- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFEvaluationWorkbook.java +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFEvaluationWorkbook.java @@ -34,8 +34,6 @@ import org.apache.poi.ss.formula.udf.UDFFinder; import org.apache.poi.xssf.model.IndexedUDFFinder; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDefinedName; -import java.util.HashMap; - /** * Internal POI use only * @@ -147,26 +145,12 @@ public final class XSSFEvaluationWorkbook implements FormulaRenderingWorkbook, E public Ptg[] getFormulaTokens(EvaluationCell evalCell) { XSSFCell cell = ((XSSFEvaluationCell)evalCell).getXSSFCell(); XSSFEvaluationWorkbook frBook = XSSFEvaluationWorkbook.create(_uBook); - String formulaText = cleanXSSFFormulaText(cell.getCellFormula()); - return FormulaParser.parse(formulaText, frBook, FormulaType.CELL, _uBook.getSheetIndex(cell.getSheet())); + return FormulaParser.parse(cell.getCellFormula(), frBook, FormulaType.CELL, _uBook.getSheetIndex(cell.getSheet())); } public UDFFinder getUDFFinder(){ return _uBook.getUDFFinder(); } - - /** - * XSSF allows certain extra textual characters in the formula that - * HSSF does not. As these can't be composed down to HSSF-compatible - * Ptgs, this method strips them out for us. - */ - private String cleanXSSFFormulaText(String text) { - // Newlines are allowed in XSSF - text = text.replaceAll("\\n", "").replaceAll("\\r", ""); - - // All done with cleaning - return text; - } private static final class Name implements EvaluationName { diff --git a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFBugs.java b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFBugs.java index 4467dc46a4..d163221d52 100644 --- a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFBugs.java +++ b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFBugs.java @@ -623,7 +623,7 @@ public final class TestXSSFBugs extends BaseTestBugzillaIssues { /** * Newlines are valid characters in a formula */ - public void test50440() throws Exception { + public void test50440And51875() throws Exception { Workbook wb = XSSFTestDataSamples.openSampleWorkbook("NewlineInFormulas.xlsx"); Sheet s = wb.getSheetAt(0); Cell c = s.getRow(0).getCell(0); @@ -636,6 +636,12 @@ public final class TestXSSFBugs extends BaseTestBugzillaIssues { assertEquals("SUM(\n1,2\n)", c.getCellFormula()); assertEquals(3.0, c.getNumericCellValue()); + + // For 51875 + Cell b3 = s.getRow(2).getCell(1); + formulaEvaluator.evaluateFormulaCell(b3); + assertEquals("B1+B2", b3.getCellFormula()); // The newline is lost for shared formulas + assertEquals(3.0, b3.getNumericCellValue()); } /** diff --git a/test-data/spreadsheet/NewlineInFormulas.xlsx b/test-data/spreadsheet/NewlineInFormulas.xlsx index 6a3ac949e9f0416d9986f3e0a5c5c18fb13ada42..18dec0aa5cd20672223456f1a6c0dc62a2579a19 100644 GIT binary patch delta 1697 zcmV;S244C9L+3@X7YPNMPOS=JlN<>gf6Gq8Fc7>W@eh{o)JghKS~aPJ0MQFVqO@ld zZyKwP9oY_P|DKJbmZDxbc^{MU%s9){u~y_8tdmA(G#*8ifG&)XdY{qH&C5ediSt|w zu8f9^p1{#net);@j6HlAb0A2dT}CVKn+0R8sG#OhB8$^xED(bk~-Kw?30kMdn58%OhHDHUu7M}#-c8?RCKQBk6} z^Up$h5E-3fYdZKdux<0$N`>3`I7%qXFDBnD5m540`3>4TvtwS#BuQpHvX5dbp>w|< z^({H>q!y;j=p@BxPv(-ZP@Ju8>N>f1F!@UN;9LGwUy795g8QR^i%~U z{z;TBf4If1PMN7R@VGftqdKBz$^){L<~X zf8V`s=WD={Fj$BEfUPl4@fsM_f$PxSe`UzFr$m~IDI>2z@{S|b!d;p?s*K4K6vJd( zLrhy+Ulk`tHWo#0xAQGRx`VPl6fn4kc5q1BGGQOB!{PC=(80^g8lJ(Q9LFDBgFiZh z@4or|L7?$91miOV-ia2fhu+1;PtFkZRtLu=nqEUNJwq@&;W3VqD@wrVgXVqLMU&}4 z4eh*WuX)8&c;q^jr6D;a%`8BBbUQ4Z9IpH%wp%D`Rk7P;_!^=N^^GkGu^MF&a&m zd0tX6lLSy#f_Mg}6%ds&0MVaG|9Jhw1K3PKIR)D(NG-nV!Lgr$A~heq98-{S96EHa z-;==x6$yuHJVZ|c003jNgB&0Me|(Z%OT#c2h3^IbL&<%cez2mU?LcK$D+pE;??Uo! zD>fe_(XId9w4Jp>@iOl@d2-H6iuFl!!6z8oDp{f=ictWPDc;I`iJqHHc!L7#sN}R$ z5=zvAMeFMNs$iNhW#G{$4aT)#15c8cFkPa9bDCgm*#QK#k#|Th?vxSKfBB=?V@=sR z-2+bJIL87U<HlWk|?@pz1m*%-s;Pw;EKeHyEVtsHj6096HNgc+btnW|Aj^ue|*@M1VD zu%p)1e%HGe_+4Ms)G+9ROPclt{;hiASYW~e4}2st75G+OvipZ-gQ_%6;xI|W__j$? zk|iX|--gF8?hg^CfjCF_pO|=Dn5IpflT}W#)q?m9sK(rv-=g{gliMEH1)EN-3SyJE zA5sCBlM5g~0bP?!AVmW65|a)S8k4Oc5F7a_OV?Tg005*0000;O000000000000000 ziIdnMMFJTYlOG{D0*o1xS|K7F4Avp=w*UYDH~|0v5C8xG0000000000003Sblb9hQ r9EWQ>L{9+#0Am6G01*HH00000000000000(9h24}BL>MI00000TpbCl delta 1524 zcmZ8h3pCRU82=l_yc*48GmjZ5uSq0Q(q@jtXp@Ar%{#@sr!k{YspwxMc~)Ls^2#l$ zYm`TMC3%#0Nmn76IfUKad+xc;x!*azbAIRh&UeoDo!{^KW^4ucDrJ~>ZY$D?3Zh|X zwpU1^F(S^pM$pTPgDA?PxIV*~t2GAM?`E!@x?f#)-w-d@Ffy}L_U1?YA(s*^XONjf z%m}G+Lgl{mL69L0riW*}oU8|N+mJfl;f7C4P+_rRc3H1BMvKg&1iqblnw%($isvCS zE}vhcQ1irM@vB*{hmy~u=ZwNC#Em@JvJO-c`Oc&Z*v>BxMIIbT>e@(<)6BKs?u#5)^ack zxFpYQyBxhs_9EORdVDi5hC zYZFZ+NQDBoOT4yjIYyzyhWuSJm90`~+Q5N7o;zzva_+usr0#Lu=KX=y_>U~tSSqPQ ze9T?VxX2zk`O<*bx0L&2JK=^ey@R_iN~wvm-j*!w?$okMpF{{QxgT4W<`uBFo7Q2w zTZacze=Sa7)u!oXPfFu%`w7q|M2Tu^JZX|Ox86&uU6zS7RP16(xV$rffoDU;Y`;^E z)yTxq>Xc`Z5@zR^Wao9k5%<|eBV@*d4BR79{=mbothw0brwC%E9n1N7MDofLEN6zz{Gq!IJNDgLiPjT9 z06-uR1)Lp3#lT4ns@7cDOq`8Ht9su80H90=xDYYHI65_$jt`&)o->L$8*FvWMhdbU zF1C=!mJE;fA52Qsb|?H0UrR_=bJWZ>$ktpt|LK(j{;l(9Z2ZjqQxqMgy~eWGoiy*v zc9Q7I$H-_govy{HV^otL7|!rH^AC-maa`NuqfIo;y81l$D@TdfPRTV2D_>lKqAKWd zMZet|(c+X&U--qaIz11m`tUGl!isZwL$s9VP=^v=`O;fEAOI*JDV!Jsl!+x04+@<~ ztv#uxDI#>DheM08O7eoZ0N)XpqkcLlaKH2LduXEKtHyNJx5O@v_J`&1oO+LnPH7yt z(go^j>>99p3-vy2;8sJhN8AInUDMta6L8Brp4oCD2_}7dFr8U$QDkJCt8*^dccNpb zY%8ORqidf-_9{vWw`?2g6P2W4$I}mv=wnmaOAhZ&JARc21WFjsnjvWFOL;2*e_;Dt zSjX42{`_J!ku+L>O+ti{u1g}jK`yO|t*!#vl0yg22#Ue^8(m)!-E;S*!>hkFl)fR# zF62-AKFVjZg4(Bz^`%PnRJzJUrf!x6=@2x6nH=Su7Qllx+T9b|!x>$sE|p=ALd6xMt~=+0pwq2V}cXegM0L7@NdxvkzZRWLzI3;my