From 77786178ea9e9be63089d4e38b172d085ebf0d2f Mon Sep 17 00:00:00 2001 From: PJ Fanning Date: Tue, 19 Oct 2021 18:25:33 +0000 Subject: [PATCH] [bug-65644] Improved regex for Excel accounting formats. Thanks to Adam DeSapio git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1894386 13f79535-47bb-0310-9956-ffa450edef68 --- .../apache/poi/ss/format/CellFormatPart.java | 5 ++++- .../apache/poi/ss/format/TestCellFormat.java | 20 ++++++++++++++++++ poi/src/test/java9/module-info.class | Bin 4198 -> 4145 bytes 3 files changed, 24 insertions(+), 1 deletion(-) diff --git a/poi/src/main/java/org/apache/poi/ss/format/CellFormatPart.java b/poi/src/main/java/org/apache/poi/ss/format/CellFormatPart.java index abb9978242..141c98e735 100644 --- a/poi/src/main/java/org/apache/poi/ss/format/CellFormatPart.java +++ b/poi/src/main/java/org/apache/poi/ss/format/CellFormatPart.java @@ -108,7 +108,7 @@ public class CellFormatPart { " \\s*(-?[0-9]+(?:\\.[0-9]*)?)\\s* # The constant to test against\n"; // A currency symbol / string, in a specific locale - String currency = "(\\[\\$.{0,3}-[0-9a-f]{3}\\])"; + String currency = "(\\[\\$.{0,3}(-[0-9a-f]{3,4})?\\])"; String color = "\\[(black|blue|cyan|green|magenta|red|white|yellow|color [0-9]+)\\]"; @@ -307,6 +307,9 @@ public class CellFormatPart { if (currencyPart.startsWith("[$-")) { // Default $ in a different locale currencyRepl = "$"; + } else if (!currencyPart.contains("-")) { + // Accounting formats such as USD [$USD] + currencyRepl = currencyPart.substring(2, currencyPart.indexOf("]")); } else { currencyRepl = currencyPart.substring(2, currencyPart.lastIndexOf('-')); } diff --git a/poi/src/test/java/org/apache/poi/ss/format/TestCellFormat.java b/poi/src/test/java/org/apache/poi/ss/format/TestCellFormat.java index 3c0476a9c3..89badeded2 100644 --- a/poi/src/test/java/org/apache/poi/ss/format/TestCellFormat.java +++ b/poi/src/test/java/org/apache/poi/ss/format/TestCellFormat.java @@ -940,12 +940,20 @@ class TestCellFormat { // French style accounting, euro sign comes after not before String formatFR = "_-#,##0* [$"+euro+"-40C]_-;\\-#,##0* [$"+euro+"-40C]_-;_-\"-\"??* [$"+euro+"-40C] _-;_-@_-"; + // R English (South Africa) style accounting, R currency symbol and 4-digit locale specifier + String formatSA = "_-[$R-1C09]* #,##0_-;\\-[$R-1C09]* #,##0_-;_-[$R-1C09]* \"-\"??_-;_-@_-"; + // USD style accounting, USD currency symbol without -xxx or -xxxx locale specifier + String formatUSD = "_([$USD]\\ * #,##0_);_([$USD]\\ * \\(#,##0\\);_([$USD]\\ * \"-\"??_);_(@_)"; + // Has +ve, -ve and zero rules CellFormat cfDft = CellFormat.getInstance(formatDft); CellFormat cfUS = CellFormat.getInstance(formatUS); CellFormat cfUK = CellFormat.getInstance(formatUK); CellFormat cfFR = CellFormat.getInstance(formatFR); + CellFormat cfSA = CellFormat.getInstance(formatSA); + CellFormat cfUSD = CellFormat.getInstance(formatUSD); + // For +ve numbers, should be Space + currency symbol + spaces + whole number with commas + space // (Except French, which is mostly reversed...) assertEquals(" $ 12 ", cfDft.apply(12.33).text); @@ -953,22 +961,34 @@ class TestCellFormat { assertEquals(" "+pound+" 12 ", cfUK.apply(12.33).text); assertEquals(" 12 "+euro+" ", cfFR.apply(12.33).text); + assertEquals(" R 12 ", cfSA.apply(Double.valueOf(12.33)).text); + assertEquals(" USD 12 ", cfUSD.apply(Double.valueOf(12.33)).text); + assertEquals(" $ 16,789 ", cfDft.apply(16789.2).text); assertEquals(" $ 16,789 ", cfUS.apply(16789.2).text); assertEquals(" "+pound+" 16,789 ", cfUK.apply(16789.2).text); assertEquals(" 16,789 "+euro+" ", cfFR.apply(16789.2).text); + assertEquals(" R 16,789 ", cfSA.apply(Double.valueOf(16789.2)).text); + assertEquals(" USD 16,789 ", cfUSD.apply(Double.valueOf(16789.2)).text); + // For -ve numbers, gets a bit more complicated... assertEquals("-$ 12 ", cfDft.apply(-12.33).text); assertEquals(" $ -12 ", cfUS.apply(-12.33).text); assertEquals("-"+pound+" 12 ", cfUK.apply(-12.33).text); assertEquals("-12 "+euro+" ", cfFR.apply(-12.33).text); + assertEquals("-R 12 ", cfSA.apply(Double.valueOf(-12.33)).text); + assertEquals(" USD (12)", cfUSD.apply(Double.valueOf(-12.33)).text); + assertEquals("-$ 16,789 ", cfDft.apply(-16789.2).text); assertEquals(" $ -16,789 ", cfUS.apply(-16789.2).text); assertEquals("-"+pound+" 16,789 ", cfUK.apply(-16789.2).text); assertEquals("-16,789 "+euro+" ", cfFR.apply(-16789.2).text); + assertEquals("-R 16,789 ", cfSA.apply(Double.valueOf(-16789.2)).text); + assertEquals(" USD (16,789)", cfUSD.apply(Double.valueOf(-16789.2)).text); + // For zero, should be Space + currency symbol + spaces + Minus + spaces assertEquals(" $ - ", cfDft.apply((double) 0).text); assertEquals(" $ - ", cfUS.apply((double) 0).text); diff --git a/poi/src/test/java9/module-info.class b/poi/src/test/java9/module-info.class index 438e778ab515b0d480b1fb79ccaffeeafa4e55c6..028b943d0ae7577c19f08d4b20ce352134f4f685 100644 GIT binary patch literal 4145 zcmai1`Fj*a5PgH_iXbRkf)@fR9w<9RgZF``2r7z1#ak!4lg%W%Gvmx`LOk)l@B6;* z`}Xq({{a6eKkL=*Cd2Fy^L=?Q(_P(NU9Y;j^UuG3{S9Cf{;c3G2ZxMAouuw>4O+f~ z!`e}!)ABb2;aF6g@TNQm2ko$?D!eL29aM&fY8z`q8>^^S(Wt_&Vyud$+MQ7w8m00%V!_>lM34$HK}Rqpm=^32%n0rg+$*?G zaKGRI!GnUmf`zBx!?=Imx8YZUkknwd@J}) z@O>3O2!5>KrwV?q;Fk)1t>Cu`ey`vU2a9u2bg(>1nl&%>>f?SbjspHVSj6DgM!i&n zzB*@6kJ{}hOl$S1)$;3EpnnfLII`5D;ny7;+8@#Ovhgz=EE$QtB=xrh*@&O*JF*ou zn?cyDDGyJmrMb{+?0+`=bTW%#2g@cJleJEmcH%fnGQXiKg??5Woyq*sPNOm7;0QW3 z(Fuc0F%B|6(Ov4+%-Mb0)g`Z;I#{Yh+-@jb3j^oKHMrf)bVD-O?sif?(b)Q$OzTQV z&RMu)QJCqHjDCKmMq{k&V!qMelOVQ=xt!{2qY5%m}YE6dGbXYQL z%2`(yXVq4>;ZHd@uB^wO^24mR{p0s*kc(06|Bc*6r`?`O{CbqA!~@%)L%D4pRW@PI zqod3E`V$w`8t25_PS_3NQ!>gWQ+yvZ5) zQ(no%Q81vVlf3TcS#7Kn+STl!n9I4z`bH&HEoT*H`gEFS;~idCCDG35Q{uyH?_BX1 zNU7BAmJYr?@>NBhV~1PU+iTiu=_9UqC63Ht)9n_iEsH@MNK%XouN4~-7{E@28ybK^#zY-bKJeWe~<4>qN$+aYneE`V9f3HUlHzZ)p|6FCg;pI6wO;X z^O2U$IWiti&)FACP5(e{=gHdThw1~pC{8=)@(wQuXWiavj}Wa7Lj^awrGD1$Z6hOF z`@YYPDTbx*iarI~Uc8S(djUuP1?c`Pzykea0S>@IVBvBcXw6G-&|aW|gY9dP;UR{L z4VNe$ilvGbEHgaJ@NmWDs45DMFx-L_%D_s)BMpx-JlgOW!($Cs86Ic2+Hj5GTEpWF zPcS^uaGl{vh9?`IVtA_Idc)HUPdD6PSTl4DHyREZo?&>V;jrN*!?O&}Hay4hT*LDW z&o{il@Iu3j3@cUbB2z2!njTXxep4|r zH6v3sGIb+UIWn~)Q#~^EBU3>#H6&9-a(^e(liyU7OijsDl}ug9RF+I_$yAq2eaTdq zOpVD@nM|F@RGLh!$yA$6y~$LZOwGxgdraN=P36hdo=o-0)St`@ATtNZ%mOm=fXqxF zGZ)Cr1~T)3%#0v2C&z9K&^xMIm F{{h);L3;oI literal 4198 zcmai1X`9qU5UxgGT}4L4TQW)SOm;I#OfoZ!=ZdI!-}i-h zE8aIB`~m(bAFC>vnd~I9?8CnAcDlN|y58#Q=HLI${R04d;9MU}6ELUhSv5!Pu-#El z!0e(|3rsa+J4(R9RF^hp)C~bMN7%XmeF7GHL0L0=!>lOH_iXwtz$^i?$W$vCAx<%~ ze?Z%*?JB@r0qausrdO?cZm5}_<0vz-@$Z2G%ongc*T_<40Tv3F*BVzfqRQ?9EEX`g z=o>+(2JNV*q5{atQ#f9^Y`bL*d0-6P*`=XF{RLQt1KG{Qpa3gcUn~`lN1k7RRRR`_ zS>syG4Qsyd1(C7}um+uQRiu@gkt)?Jt0|zbU+c$z`wFmLz!LI*tmfJgqHjkkz!2F2 zX6(`S(!!e(|0bK@7K~~rVT*wIIDsBVRmX@*xHz6P^-YhXAmNfYsM#*P7ODW%Q;vj7 z1#Fo-XEMHHq>p-Tgv;-UMNQ9VS)&s4*y;3lbvz0TGx7on{Q?SI3sn3NhwT=yCZ|JB zjos73q@f&V-1Qo+gv)cL4{7>+0+w}8j~%+D>JqLLur{w#)s-71k-1vH#-28*2wp2d zJHMT7)vDEIpiD2YBwUxXF>)@o_v;0$$eT9t?x28`dEKoQZpi9dp%vnO-6)p>RW>4p zx*qD`Ok!$cFmDpDzV~XnSuui0!iazkQ(5t;mT+q}FVn7>m~9enJI`D}(~mp}#i_UH z+jTFBjFO`y+|gs3DJhTkn5}K1SCr$c0E2dSHtBg2waCU(b+3R`U6H`kIvTdm=KWdu z5(CpS!;htnN_M(5#5Q~i$9p-&$8Z$47&gO5RV7rjDHG3=Ho^-kKj>A%3_R5A zp1E~N zE+5G?NY#YLvNa87u&ccE6SBx!GcHV5qNlTA3q!mV0=#A=Je#dk zNpH3g|yq-6Xc*PpgQr$l{}lZ&Aq7pPCN z2MRYueY%O4Yr}BHC48E_TjSB6WqoY;od<)2FS1ENm%fhAJ{#X^U&Ylgc?tQB5m7eb zbB~{W628e+zw`~kRlN_s%U-;A8aja(QBmL(D~7KQ2VUK_RL}?C3)t3m2HNLKKiGP; zbnJf=u)WKk;?#OS=|?e{N}e~~8S%4#9o-|+UKJhmq*(D9ogu$w>tL%e9HO&*M7iiZ zx&M21@{ao>hV8buFvQQVW*_`1pufwX_@Zj>VYpZvYVD&v?C)%#T0Y@px@!27JevRz z_`inuXBu39e@uhvFauz^gqi$#E?hVPFdOD@YaZhQ#zl+;#3is4aW*VxT*hVa7)pA7`9k zJi&O9@fpTbjL$Q^$oMkjtBkKRzRCDD~*GmM`j3iuMfPB>PCzK|xw zTN5T-s7=ZclQzVp4smNH(uvxn6ftQni7+$#H1@RDN9V+5+6&rl(3aBX-xf4smF{w^Wx)YP~#H2m(M8YQ$KAAA(0QH|rnDT(ylnKO?3&fNS#FP)jlo7;~ v6U3Ai#FQ7rlo`a78^os*K9lea_!iE>5AYNGg4N(G+Wdws0e@gyz+e9ZM%i|U -- 2.39.5