From a8f1e7acd19669f60a7d50a83a3cc6edb20be796 Mon Sep 17 00:00:00 2001 From: PJ Fanning Date: Sat, 9 Apr 2022 12:02:55 +0000 Subject: [PATCH] [github-321] Fix issue with rounding in DataFormatter. Thanks to Colin Wang. This closes #321 git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1899680 13f79535-47bb-0310-9956-ffa450edef68 --- .../poi/xssf/usermodel/TestXSSFWorkbook.java | 2 ++ .../poi/ss/usermodel/DataFormatter.java | 8 ++++---- test-data/spreadsheet/github-321.xlsx | Bin 8122 -> 8573 bytes 3 files changed, 6 insertions(+), 4 deletions(-) diff --git a/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFWorkbook.java b/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFWorkbook.java index 3a7fa17d30..ab606f93dc 100644 --- a/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFWorkbook.java +++ b/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFWorkbook.java @@ -1406,9 +1406,11 @@ public final class TestXSSFWorkbook extends BaseTestXWorkbook { DataFormatter dataFormatter = new DataFormatter(); FormulaEvaluator formulaEvaluator = wb.getCreationHelper().createFormulaEvaluator(); XSSFCell a3 = xssfSheet.getRow(2).getCell(0); + assertEquals(2.05, a3.getNumericCellValue()); assertEquals("2.05", dataFormatter.formatCellValue(a3)); assertEquals("2.05", dataFormatter.formatCellValue(a3, formulaEvaluator)); XSSFCell a4 = xssfSheet.getRow(3).getCell(0); + assertEquals(2.05, a4.getNumericCellValue()); assertEquals("2.1", dataFormatter.formatCellValue(a4)); assertEquals("2.1", dataFormatter.formatCellValue(a4, formulaEvaluator)); } diff --git a/poi/src/main/java/org/apache/poi/ss/usermodel/DataFormatter.java b/poi/src/main/java/org/apache/poi/ss/usermodel/DataFormatter.java index 7a18d30e68..4b8a3b8689 100644 --- a/poi/src/main/java/org/apache/poi/ss/usermodel/DataFormatter.java +++ b/poi/src/main/java/org/apache/poi/ss/usermodel/DataFormatter.java @@ -51,7 +51,7 @@ import org.apache.poi.util.LocaleUtil; /** - * DataFormatter contains methods for formatting the value stored in an + * DataFormatter contains methods for formatting the value stored in a * Cell. This can be useful for reports and GUI presentations when you * need to display data exactly as it appears in Excel. Supported formats * include currency, SSN, percentages, decimals, dates, phone numbers, zip @@ -139,7 +139,7 @@ public class DataFormatter { private static final Pattern localePatternGroup = Pattern.compile("(\\[\\$[^-\\]]*-[0-9A-Z]+])"); /** - * A regex to match the colour formattings rules. + * A regex to match the colour formatting's rules. * Allowed colours are: Black, Blue, Cyan, Green, * Magenta, Red, White, Yellow, "Color n" (1<=n<=56) */ @@ -948,9 +948,9 @@ public class DataFormatter { Format numberFormat = getFormat(cell, cfEvaluator); double d = cell.getNumericCellValue(); if (numberFormat == null) { - return String.valueOf(d); + return NumberToTextConverter.toText(d); } - String formatted = numberFormat.format(d); + String formatted = numberFormat.format(new BigDecimal(NumberToTextConverter.toText(d))); return formatted.replaceFirst("E(\\d)", "E+$1"); // to match Excel's E-notation } diff --git a/test-data/spreadsheet/github-321.xlsx b/test-data/spreadsheet/github-321.xlsx index 0b517e9eaa7e1888ee54f7e22d24ed81503ef8c8..2f50722c55bb6dc55f9ed00250283e0b83c6f38e 100644 GIT binary patch delta 3761 zcmZ8kcT^M3(hUiOUZqQu-kbD}pb|iuQUg*#?+~O4p(r3Afdr)YDpd$X5NU!G=|xl^ zbbo?UgY@?Bz4INv_x6uHyJu(i?3uZDcJ4d%i#v2idLTl202n|5003?QN*{h4YoHSX zB8CilBx&v*>6~k=i6!6~HArN)qWkII33A%4KpZ|xgv?J~s@^zi&?sVXmv3+wn}@63 z&L5VWq4$WdY_I;BiL@Z7JJfB%IusGB}A z!Pu|Ndt0mcp))>pG3ljA`ImK?C!tQSfoxiYuit=lW_I8Ei!>`At96fc3*x0a8tn+$|M zN#F9;_S|Hla&bUs(+#3tN05_OO85t1hzFe^ToN z+k~=zSDoe7#xqkGe|hhiCD@;Q;7-nMsmCzR$=bo{+*GFmt}DU&S!b24n*sdBpmq5I zMveha0hiqa3yLB#B%D}i2v^N!qFi|+k}=yba{g#o3xp%W8UhU$&4RUR}5JOy=f2j^`5 zA)!5QtwUo)g4o@9jkva!2y^(2!$#Ud-_?W*W&gn?yQmH>GiEoIiL+%?RUfm{O`czK zfNp4+Ylx4N1MV#_Ipg47#_wI4^Jr|9Ykz6fVSr@IPAN?Zq&W_vqpxT^OBKYX`!TFTrT*t4-ClA|}JyqW)+>v-WBDN#a zkesRoB>5Tg_zq9Z_fY8tysoTNBU4A);#u5Y9GLlr`g9Ig#E9rU355O1se*(Fh6FV) zN~WX1EDlH0KVDfK9=ri09p?+ya&s@x@XCG}fjT$GnUMZ=Q#FL#i%t5iycTko|Jg|c z3d^TsZHR_xykU2z#u_ulWIG!%5r_7E90bPIy@2=M#&I=`bUpEeZ#V~Ti{V8LRc$zN zmnG^GgaWDe_k-?Y5T2EfzMx(qcV*u>JkcelnU>>PI(y~$lSn8qvKX53ApjCTB6DCf zHAL1AP4UbM`zT_`9&yplNN+JUl~hHWT%ndVr8Eg^n8W$Z%)PY9TgYo)7oG}PRJ}kX ze;i3oW;!{*dHL1A@rom;yv~CHO^b)90|iJ=wjTWOnsry`XCPWcWZdV2c_09=4F&)x z|HDCeK!~Rk{0{?5O&5J~BxxcFny$K6@>pH%AcT+fOiFcaWMAhsnG@?fd{$49V)@o| z74E|Ita9qz6NzrMvyQg2j(xrkH=_H*RUYMzE63*fF_pun-D~209V$-S^P0`K8o8_? z*$Vfql;sf{XF+~m$+>FVLb4=Wj(G}UuqwX3Cmj{h)8NWNu0iluSx~4u|23%Zx!iceopn%)Up~FjMoUr?F;rbSTz|G0vnNNix-rg^4|`d2F*d; ztP%H#KXlVsBw72FrAC1P){9oWv`w82{JCDTm_8IqAF{o5|5vDUivY9WSH9`{T{cQf zy$A(P5%NN54;`2*P^)>^E++P1sv&xp?zA~>W4qlytYUD2`ojy*Hn4opYy-H^pEk9S z)|dMV{At4T?OWRt;bWXItT1k}#m=#Uclp&Ey8lgOl<=pMl5j2CA@WUZt}9Xfgqe>M zMo-Yu;xPE4Z^dvTbd$7(@7JQeF*Ny@39+CBYJWAej?^*TTh-#hK61gyFo%{5vEp-6 z{4_sW&^OwoDNxU3_3YoI)0&I*>sj-01q>If-k6hZX@C`#JWT?YGB)v2${ViQujGCy zaP4NLO?s<_iIHa4zTO|1A=De&wV-g@9!#W!yNq5g=i6%P#1SvX7PyaWkjoc@OaRle z`)Vxaz#TKl_;{%nVGuioNGkV!6+M0FX$fbx3l$6$+}rci%N;B??ELjqCoKDnJN`Yg zXs7jC^N%)6{4g#gfc>52I&qFG_8le07ZrBgNBfWc;!a%A12t<_enA&}>1RmMYX)%?SOP~VDe9R=3MKCo&~pb*f9f1A903xkx@x6vaI;R$55Ld04a&BLzh zQuErzn}P`|T5;R&G?;O>iqmvYrvPQziJM+itV4RwJCMd7h!m}!Iy~)X$|{PeC@p)_ zmor(){Gi_nlUGA_ z8k9X;KE-Pdo>~(ko0MT8PK2nCPi7p1veflsGEC2rqx-rmlt*>(&)*d$3PSY-bAD(= zL_ic;uKIfS@ydcU97a6FrAQaIVXWoR$bo_|j&Nt$FIG$`)YBk24ABJuI#8=q>c^X;Ys_$_i$%E?sQAKK4f z9I3rGgB5!;^-fXnP;&ToD&JzRuzs6(xkFHr|r$;M~+GpJ(7AmkerfeTgJ9}x0RlD!Ey9RJ;QOIHf%I&piSc6?*O4=LDXa) z9$S*65C8x-K^V)MoCFBe1D`obir5{{DJKQ)rlOZIMO2N0j`{C54g{z`7lU$GQ6q1B zP3q0u%PXWw2W*vM9UK%& zd_Sbq72moppuU%gr{`9ema8)Rc-mHJ$C1QL| z_<^8?PL-ED|Mn7sD+eVNn5wYBd8^$daiG+ZQU9Y;byinkbQFl5 z?9+jji{5pD!ho1;X0Gc58SC|27nwz(E3Lt>j3)NtqAd+4LnBjxSMO)jb3m_|p<~v&qqoH@ykzLt0aRk2 zh7xn-({7R*&dAuc6EYE@0@LJ)W61lP0)GC%Ta9P!#j0v zfMbOF6hU1rEHBpM>7Q!LabmX#qG6SqQHN9l7(zsts>SyR_Vx4^4mM&i<(Vvy z?42VEk6>W5h1{cg8`l{-`f|Cf@l zL)h&v`v3r%|1wx+0RS9*U`GBvzHkY9U*A8ym609+Pz>-_Xa0o-06_a+-W6>B&l~3B P?}RSoqz4Oe{xSL&RRY}B delta 3334 zcmZ8jcQoAF7XFPIy+=2KC^w?FaP>YTN`x3CjFQo75JG->?-Oy=5Hq^y!nkU5CPX)) zlSm?35UzwL_pbHw?mK6#v-Upc`)lv-eBb_|fm3BPh7*%817yGz006E50VVF9b&MiJ zAl{Hjk~#XDV29>pb+$;0M88Ds9;#e)590bb)@92!n5t*tacLM;WL!92X7adT_wZeS z3_@wdJZ+q0c@L?)Lj5Nt;UwtwGD>_}8TAKtc7lwKW*ImbI{C|0W4AMXIsdq%hJ_5mwm$hnnj)P~YIs?k1GHe5TS{y5>U3Akt5wdl6VW zR$lXxk3xNztOKnG!(7b^b==CWQhz?Ugtd90ZW6N^?XcU1h)Ts8sz@z*&OI4>W!!rX z*e_Xh!ENjLa6*X(DFe0*VZ@5WU;_5dKnD>343Gf;-DRC0xr+yS`MTMAdAW%_@^HtP zd3fbY(4VWgTy!4#K~fVm+*qzII! z2co@HL}B$j>&bV_>~j3;k)+i%T79rIM~$m@yYIZj<1&&;j$$OuHcC7Y1qkE&Yup;V z;@$L!ex|GO7!ix|0N#wvyO1BjuPbr7H!|QWl)u^L4C`r`KzgOeT7y5UN&c2$S0@&Y z8N1c8$jI8n=t;+8-)#qed#3jALB&cubJO4yi(0PI1nQh=L|{XJn5M76(EZjo ztKpf4Dnr9cc0sxl_CMq~!Y4ktUJqLdWyC9cH|#q;-Op3rhOxe_z+t1`+)Xhtbdxn| zwg1RQXD8CU-Lh&Ykn2j=-&mi<1B*Wsf8N(`14|j9j^BG)>SKT&Ala$ z(g{}Qb1s&9oF(&~O{wd`pb_ICe$9M*@6h>TkzgBBaKRQlAA76`{z;eT@wxbGa6Ccx zeu+I*oljTxt0&r`AZxLQQ1C`MK~0bAqUBru{t?;9U*=gT7dK0X5Dg*1+xS(fLvM|N z@5x59{9*Uxi|a=|x9@P0PJX-YFTB^;zv+qNt14DK6-fnb#y|*!lr9b(GEW3=#b5} zf=jH_q-9qAf`glhQGxwq9lUt{B2usu9Y)O3fFt82qJ1i{mSJ{Dat=}eU<1(Oyd2;q z)h{moKJqe#JbNVQV|FwDqJ6fz5>2%<(fJ|5~DRO@O z#~Esy{dRz5I*o#OwZ6-{6k!OzyC7Z)`yARZ!+%}H8|~vngLz0-VA%gHu(;a)hAlu6 zL9%XY8D%V(6l4hcven-Mn+!rg82Rm2fRlOa{_cQ{*jQws^9CNRh;j&Aau8ZLS&G zW>0?88e3r(k-Q^0IuC({$g!+dy7@F89|49P};bDWZusZkwRo9CW7^RT@>GkWUtrGWjT9<#43pROVBT`a%Ylwc@tOm2 z1lG@6CtFI(tq%gLnYX%O+8^#$}d4;B0rGJeMKpeOhsq>)oL)ZaPZE@cOVB#$&8kd5~h2 z7m)O+kmcP7*a)|O2%a=>fR>6MfrrY&IB{3invw+@H9TZwC0+4D`lX?(x(CC~m7IaZ zEn4z4HFI#Bh(1NNm|oa-MT{_b4H?4n*zJD2%(WQq7ICOpz0M7k3HIBtx+J-F`aE)RGQPLbvcHu+0e#D>srT2 zhtDwq+Ue?8F6oLWDvpg0>$LA~I9KqLG@QjA^^ZF+3l)=N{45;ZJX}HNHJ`2aia`=L zE_&IJhNIF+rIVLUe#TpTmA4k1wWH}a5AErB^AafP4wBESTYEe?F~{{!ck13qCM6m5 z=trrM)eU?W=K+&u96H&rHsNJecvGBh(^cAZJC<&pZwr~K$JLJLG6}Sg2i$ zT`c`QR+|*vETLuRMGJ;P<@W{slY0(~(dt2*I^B*iRJVI@E{T*AW8cU}$NjXYLvfD} zhLh;HD`dSh}^U*Jfe3Bznt1^EyNxy zyb{T?yiR;NdNl1e`#_xSg--zgu=X{n#+Ampw&au6cKkiQL`;==bwdjSdD2=%>q#S- zZ6wtRzd~v_b$?R6dsVkrFH?FAHfb*^KY_~J0u+SWY%xnxD}7}7y^?#lLbzj)R^LS% zMjO-moV(Al9NBz$Hl9MpA<#TmgcTmq7D=`28Se|^)OoKp&#w4r$%W(Fl$9nQ>c~x| zp?3F7yOV2RSWQK_SURCj(LKdak-O z&kMeHtrB0zr8&H&7@8U+}?~1(r3Pw2_Phh{BEXVdzsTBJ%(lI%wSc&7s>v*`|Ved)rq}MAkVk*#A zkmpTJ(})?irs?ntUoV4KRF{7L8`_PIp8D6d1}!?3_qx#kOZhKAH_P9n z@IS(iF63pt@$3Hp0K+ffWfZ{%0FGV`#=c(Oe&P;ZzD_^!TEF@V0QCRDt8@Mj9_j7< Y(>ILaq?dLA02G&7<