From 5d325ebdc80e3743135238995dd734ee63c987f7 Mon Sep 17 00:00:00 2001 From: Yegor Kozlov Date: Sat, 16 Mar 2019 15:41:46 +0000 Subject: [PATCH] Bug 61472: Convert date/time strings to numbers when evaluating formulas git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1855662 13f79535-47bb-0310-9956-ffa450edef68 --- .../poi/ss/formula/eval/OperandResolver.java | 16 ++++++++- .../poi/ss/formula/functions/Value.java | 16 ++++++++- .../org/apache/poi/ss/usermodel/DateUtil.java | 32 ++++++++++++++++++ .../ss/formula/eval/TestOperandResolver.java | 28 +++++++++++++++ .../BaseTestFunctionsFromSpreadsheet.java | 27 ++++++++++++--- .../TestDateTimeToNumberFromSpreadsheet.java | 29 ++++++++++++++++ .../spreadsheet/DateTimeToNumberTestCases.xls | Bin 0 -> 34304 bytes 7 files changed, 142 insertions(+), 6 deletions(-) create mode 100755 src/testcases/org/apache/poi/ss/formula/functions/TestDateTimeToNumberFromSpreadsheet.java create mode 100755 test-data/spreadsheet/DateTimeToNumberTestCases.xls diff --git a/src/java/org/apache/poi/ss/formula/eval/OperandResolver.java b/src/java/org/apache/poi/ss/formula/eval/OperandResolver.java index 1c6e604a13..162410f159 100644 --- a/src/java/org/apache/poi/ss/formula/eval/OperandResolver.java +++ b/src/java/org/apache/poi/ss/formula/eval/OperandResolver.java @@ -18,8 +18,10 @@ package org.apache.poi.ss.formula.eval; import org.apache.poi.ss.formula.EvaluationCell; +import org.apache.poi.ss.usermodel.DateUtil; import org.apache.poi.ss.util.CellRangeAddress; +import java.time.DateTimeException; import java.util.regex.Pattern; /** @@ -258,7 +260,9 @@ public final class OperandResolver { return ((NumericValueEval)ev).getNumberValue(); } if (ev instanceof StringEval) { - Double dd = parseDouble(((StringEval) ev).getStringValue()); + String sval = ((StringEval) ev).getStringValue(); + Double dd = parseDouble(sval); + if(dd == null) dd = parseDateTime(sval); if (dd == null) { throw EvaluationException.invalidValue(); } @@ -300,6 +304,16 @@ public final class OperandResolver { } + public static Double parseDateTime(String pText) { + + try { + return DateUtil.parseDateTime(pText); + } catch (DateTimeException e) { + return null; + } + + } + /** * @param ve must be a NumberEval, StringEval, BoolEval, or BlankEval * @return the converted string value. never null diff --git a/src/java/org/apache/poi/ss/formula/functions/Value.java b/src/java/org/apache/poi/ss/formula/functions/Value.java index 4280111b99..176a776906 100644 --- a/src/java/org/apache/poi/ss/formula/functions/Value.java +++ b/src/java/org/apache/poi/ss/formula/functions/Value.java @@ -22,6 +22,9 @@ import org.apache.poi.ss.formula.eval.EvaluationException; import org.apache.poi.ss.formula.eval.NumberEval; import org.apache.poi.ss.formula.eval.OperandResolver; import org.apache.poi.ss.formula.eval.ValueEval; +import org.apache.poi.ss.usermodel.DateUtil; + +import java.time.DateTimeException; /** * Implementation for Excel VALUE() function.

@@ -48,6 +51,7 @@ public final class Value extends Fixed1ArgFunction { } String strText = OperandResolver.coerceValueToString(veText); Double result = convertTextToNumber(strText); + if(result == null) result = parseDateTime(strText); if (result == null) { return ErrorEval.VALUE_INVALID; } @@ -59,7 +63,7 @@ public final class Value extends Fixed1ArgFunction { * * @return null if there is any problem converting the text */ - private static Double convertTextToNumber(String strText) { + public static Double convertTextToNumber(String strText) { boolean foundCurrency = false; boolean foundUnaryPlus = false; boolean foundUnaryMinus = false; @@ -189,4 +193,14 @@ public final class Value extends Fixed1ArgFunction { double result = foundUnaryMinus ? -d : d; return foundPercentage ? result/100. : result; } + + public static Double parseDateTime(String pText) { + + try { + return DateUtil.parseDateTime(pText); + } catch (DateTimeException e) { + return null; + } + + } } diff --git a/src/java/org/apache/poi/ss/usermodel/DateUtil.java b/src/java/org/apache/poi/ss/usermodel/DateUtil.java index 60cc74500a..82cf7f6e5c 100644 --- a/src/java/org/apache/poi/ss/usermodel/DateUtil.java +++ b/src/java/org/apache/poi/ss/usermodel/DateUtil.java @@ -18,6 +18,14 @@ package org.apache.poi.ss.usermodel; +import java.time.LocalDate; +import java.time.LocalTime; +import java.time.ZoneId; +import java.time.format.DateTimeFormatter; +import java.time.format.DateTimeFormatterBuilder; +import java.time.temporal.ChronoField; +import java.time.temporal.TemporalAccessor; +import java.time.temporal.TemporalQueries; import java.util.Calendar; import java.util.Date; import java.util.TimeZone; @@ -58,6 +66,14 @@ public class DateUtil { // for format which start with "[DBNum1]" or "[DBNum2]" or "[DBNum3]" could be a Chinese date private static final Pattern date_ptrn5 = Pattern.compile("^\\[DBNum(1|2|3)\\]"); + private static final DateTimeFormatter dateTimeFormats = new DateTimeFormatterBuilder() + .appendPattern("[dd MMM[ yyyy]][[ ]h:m[:s] a][[ ]H:m[:s]]") + .appendPattern("[[yyyy ]dd-MMM[-yyyy]][[ ]h:m[:s] a][[ ]H:m[:s]]") + .appendPattern("[M/dd[/yyyy]][[ ]h:m[:s] a][[ ]H:m[:s]]") + .appendPattern("[[yyyy/]M/dd][[ ]h:m[:s] a][[ ]H:m[:s]]") + .parseDefaulting(ChronoField.YEAR_OF_ERA, Calendar.getInstance().get(Calendar.YEAR)) + .toFormatter(); + /** * Given a Date, converts it into a double representing its internal Excel representation, * which is the number of days since 1/1/1900. Fractional days represent hours, minutes, and seconds. @@ -724,4 +740,20 @@ public class DateUtil { } return result; } + + public static Double parseDateTime(String str){ + TemporalAccessor tmp = dateTimeFormats.parse(str.replaceAll("\\s+", " ")); + LocalTime time = tmp.query(TemporalQueries.localTime()); + LocalDate date = tmp.query(TemporalQueries.localDate()); + if(time == null && date == null) return null; + + double tm = 0; + if(date != null) { + Date d = Date.from(date.atStartOfDay().atZone(ZoneId.systemDefault()).toInstant()); + tm = DateUtil.getExcelDate(d); + } + if(time != null) tm += 1.0*time.toSecondOfDay()/SECONDS_PER_DAY; + + return tm; + } } diff --git a/src/testcases/org/apache/poi/ss/formula/eval/TestOperandResolver.java b/src/testcases/org/apache/poi/ss/formula/eval/TestOperandResolver.java index 72fb29ca63..c329672265 100644 --- a/src/testcases/org/apache/poi/ss/formula/eval/TestOperandResolver.java +++ b/src/testcases/org/apache/poi/ss/formula/eval/TestOperandResolver.java @@ -20,6 +20,9 @@ package org.apache.poi.ss.formula.eval; import junit.framework.AssertionFailedError; import junit.framework.TestCase; +import java.util.LinkedHashMap; +import java.util.Map; + /** * Tests for OperandResolver * @@ -74,4 +77,29 @@ public final class TestOperandResolver extends TestCase { assertNull(OperandResolver.parseDouble(value)); } } + + public void testCoerceDateStringToNumber() throws EvaluationException { + Map values = new LinkedHashMap<>(); + values.put("2019/1/18", 43483.); + values.put("01/18/2019", 43483.); + values.put("18 Jan 2019", 43483.); + values.put("18-Jan-2019", 43483.); + + for (String str : values.keySet()) { + assertEquals(OperandResolver.coerceValueToDouble(new StringEval(str)), values.get(str), 0.00001); + } + } + + public void testCoerceTimeStringToNumber() throws EvaluationException { + Map values = new LinkedHashMap<>(); + values.put("00:00", 0.0); + values.put("12:00", 0.5); + values.put("15:43:09", 0.654965278); + values.put("15:43", 0.654861111); + values.put("3:43 PM", 0.654861111); + + for (String str : values.keySet()) { + assertEquals(OperandResolver.coerceValueToDouble(new StringEval(str)), values.get(str), 0.00001); + } + } } diff --git a/src/testcases/org/apache/poi/ss/formula/functions/BaseTestFunctionsFromSpreadsheet.java b/src/testcases/org/apache/poi/ss/formula/functions/BaseTestFunctionsFromSpreadsheet.java index 88b52493ea..7b899350c1 100644 --- a/src/testcases/org/apache/poi/ss/formula/functions/BaseTestFunctionsFromSpreadsheet.java +++ b/src/testcases/org/apache/poi/ss/formula/functions/BaseTestFunctionsFromSpreadsheet.java @@ -78,6 +78,8 @@ public abstract class BaseTestFunctionsFromSpreadsheet { public int formulasRowIdx; @Parameter(value = 4) public HSSFFormulaEvaluator evaluator; + @Parameter(value = 5) + public int precisionColumnIndex; @@ -92,7 +94,7 @@ public abstract class BaseTestFunctionsFromSpreadsheet { HSSFSheet sheet = workbook.getSheetAt(sheetIdx); processFunctionGroup(data, sheet, SS.START_TEST_CASES_ROW_INDEX, filename); } - + workbook.close(); return data; @@ -101,6 +103,14 @@ public abstract class BaseTestFunctionsFromSpreadsheet { private static void processFunctionGroup(List data, HSSFSheet sheet, final int startRowIndex, String filename) { HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet.getWorkbook()); + int precisionColumnIndex = -1; + HSSFRow precisionRow = sheet.getWorkbook().getSheetAt(0).getRow(11); + HSSFCell precisionCell = precisionRow == null ? null : precisionRow.getCell(0); + if(precisionCell != null && precisionCell.getCellType() == CellType.NUMERIC){ + precisionColumnIndex = (int)precisionCell.getNumericCellValue(); + } + + String currentGroupComment = ""; final int maxRows = sheet.getLastRowNum()+1; for(int rowIndex=startRowIndex; rowIndex data() throws Exception { + return data(TestDateTimeToNumberFromSpreadsheet.class, "DateTimeToNumberTestCases.xls"); + } +} \ No newline at end of file diff --git a/test-data/spreadsheet/DateTimeToNumberTestCases.xls b/test-data/spreadsheet/DateTimeToNumberTestCases.xls new file mode 100755 index 0000000000000000000000000000000000000000..7c235e64fda7af4b31413020988b77ae14d89be1 GIT binary patch literal 34304 zcmeHQ3ve6Pb-e&V5~MyPB}&xK(u$HyiXtIWq&}8pkrZjkbVNxb(XpL4wIOmTq5=U1 z03|DqV!Do}GqK|&QtGs}TIXX=r_TRZr_(f++fF_+N}IIKxN$XU(s4Zbcj7veCSw~t z=e=EEcd>xLB>km$mSbyDMi2XH-T zryB+LvsotX-vL|$R0;owBv2QIB5SJU4M)CO`xOaFO<3eR`29ra8GMWUE8^`kBMU`d zmwZb+dAjp9^JFq*{8xwliic=`JY0g|T^|U+B7<@fu}M}c-j=BErRw|RD(C0b_w}+w z?gYncZhQaMT|%o3$gRNvmG7PEyH$NJ#&Du2M?mVXk|QWBSD_LT%?H#`RO(G7adZX( zva2K?p{wS&0*fe(7ukTtIRZ#-sByvd!ZRchs$e5OdZ z8xPV%)rRKK>docBW@X(e9kNa24zki=*qm9(+uIf3_RV`NKxa2Dz?TqXLYQ+B(F=^D7{Db^e$`}^}mcgPm#BTv*WpQCFRR;2SKzl}D-@UyI; ze)ZQSgK+lT3jVTvs{;RZl+o>my^#58}7*uFen)O(ZTXdOa!D| zgaTyn!dXEE3to*&<=<$KDrk8MdL{)ASzs%F07?J+9(a!KQeQkGi;KW5JPx{V4L8eM zbNrE?o8-QWGXv+sU2@I^=e?jTLO)oAEAq-g1iIgj#Tc;!3kxvIyb9=n`8+6)k$F*I z#phLkDx6mVjuGZnpmx3msFZn8pxc{Q0s6yv6`&)VR{?ssc@?1Rn^yt)#Ca85;HuoC|xA(6yFT%j!_hmtJ})hosg*e9t}iWDP5XePDGC7;vjl z?*ls*_A=FhyqYNv>==A&_QVrU6fo7_-(SF#6J|3-m_{vPGv$O8j$*eUd&zNucbwcg z-vV9pEfAS+fvvd$)HW695^Q0myK@Do2@dZ(1-#=JEVWrKgA}m7@1|fIj~7DlBlltAN+G1LwjwdpewbC=e<>TcX^(ZQI%2 z?GC4qll~khG&d;2EziON1GxeuSVmn3=UZU^dg;4t@u0oMalb6i4LNcu-$mniJk*PK(U%K+o{$!ST$@CVIxwZru z-BBqrHRj|GfBG$dGE2Q=`U=T(lpv$KGDW7=oP6P5e&kQ4*-K`7A(==CGVJUu-Gt4_ z=YR6={$!SU$?Pa3v#kUfc99mDI&<>X?|;*u%yN?HD{X1(K&Gz@OS^pNbC+N6C)463 z<6~(XN|13{+T}wpe(ouMGAq1fd@QZ41R1BLT|V?rFMQsg%t|jAA4}U(f{fGBE+4vl z>3M%LtGr}E|>VIy8iwPrJrT#>dh&mLTJ_v~9i5Km1;QGHbnLd@QZK1R1BLZR`Ep z=N|PZv(8J#$I?1Wka1etw%$*^|4aU43@;fUOY1H{#%XEWde49Lia(k4UNSzG)>ndz z)6%x}{_^Q}`H@+OgBn<&$n~3po__P1Y;DoLQsl_Jf8~#KLmnwC%8PWp2dUGFu&H-+ z)F0`_JW|+?7wIMsQm4&`Jo=Zn`6JzwM+$54BHipk>a-A%xrq<@BfU0{6t>|-+T}s& zvV7#a@YDvpnnE3OTxL7~%u+FWrCRFmUMr);it%H~R^E(Ale zWv(iFK5V#IsjdQ%A&6uepD0L)FmLBv4f3{o^48$YZ$7UfZ$wYxe&@+>nRh; zhEZk~=Oa^U>^m-Jb0s2kwI~tsl&H-vEGXfowM@2hoD}kg3ZBX6868xjXSU^8#q7F8$#h6}Tf%Qf+IuT2pG%yA6)W50`3cC?(u}P7wlVDX(J(Ojk9j+TWr_s^E zYr&cJuNQ)grPqlZF4c>?$p^W1t3{A!`~UQ5P7R2>&xsta)r)+I4|45Ziy+q{j-W!m z-H9A7*5hZYmii#4o7IL;>l6zc!;ZNy48emW7*$ht1hcDhk)<=`;ds&}kfoNwTIX7$%*bo(3sjk5w?c zz8A4nmTQb?L(oC5TW|pZ$-`(aDdoI~%2Htfmk_^Iybeb&VcLh28S{Mjgd0b&9*vGT zGGIzjKf71pTAJ-{xek`^1a!)Fw>o9JTb;TPTp)V{dpSpDGfLxUJVciP^${h5LY+Ii zBYJO_&I!nKqEj{}I%RXBQ?%c`bJdXDMmG!9)lkGy<+AKW35x7OuwJgGsgA`maY(Dj zU8<@9>KTJ|WS}_8tFXxgh+>~Yk<8EnJnJG=D-{X~Tx&n1jLNn4LAl$`NY~a*(Zrf? zw0WV(EzwkheowppemA4R1?WX?0fiLWmF@ccTs{XFO2Q9?Zpvy3?c1!tSnv6k~E)Sx`O)ThK}L@LcNIA$Q_5xXAs|ATM%F84ctZoVGD) z-j9B)nM;;9zC0Ywm}9YN^O5ihdBnkpwq?D(_aQuy@gc`OmV6Ml9l+;fcnSi~EQI)r z^zIf2kz{!t%dU`(EKm;@V28x)^a(RHm@=bS%{?#gh38x-EAXTPLKyAX4#|YM z|Ll>3iG}Dja3L#%yNI&Tfhi4L#Lb~(e0DkkT+7kb)Z?>4HZW&hLC#~zGsxD0PaS3* zJ_s{YDHBA;_@i>J1Gf+q>-62jp=ACVf^lF zB9<{SW;$bx$D`@Aab_wuK4r|N%``H_(nfj)HJ?aNnPz4;Xta-^%|zY|my$42$umYD`7@)ajUw2| zGUH14X=8uqQ0FjNOPNL#k@C>@j43@>NE@vOGzYDnMk@wV=6D8Fj+yD%c&60=VWV{@ zIXw+Iw7vpo00c^kv5c95nWl}2s~W4 zv9Fban@r9EZ;nr6FoLykm;uxh%S<6>`c!O&S?!t_gNpsw$wV>*?%JWZ$=Nt%VT{kF zQeZBA78KL7Gc(B)`9EP!MNh|)v#HHMeS`uy1L@gyA2i0J3FM*vl|*qvRgqqV44+IT zXJ;r^2sn+5@#L9^@eI0cZBmx5P&TFHq%1^<+}IC+Kq`r328Ne`Qi#s9#cE~G*K4R# zWpJ41nHVfBWwIWS-efcupG}#l#29VYN~VpIChjIc1v;3}QX`=>w1@eRQX6fibX29- z^tJt<)y2%y(100D$G{c*K-)|-ouM63La}JPO*amUx&0&!rKim}Rb`o5TQqIqZ!?lJ z)L;Z1uWB#wyjJ_fVLngu#67EN;$+})%G|KhyWha3>UL}S>Ya|J&@|xqlsi;@Mzy45 z0)7h?MGJ!MLlZ)AOlgorZH`AN@^lu*tHbh9Od-&@OA0%}U&o+r0v+bx zKrG|o<%qaph2_?7P##8~_^{*7{_h6lQNSK`z}SaA2-t%T7!h!kuAg#jbUo#`AlS^? zDv+C3u#5W$I*3K=VZmPz-G76+Zc=@0BRGHM&wlz3qbH8`-=W|g3cf{s|DJ-(A&(t4 zVOS<&1n$M1XF{a44Dmjd`|axcTI8No;l+MF++<(3=dgUQf3xHI1AP1m728K%+D|8*+2<**vmPV3O9p`u$)u65P2xxz~L;$dpwP*HKa!*;UG_s^4g$~ zanT@zG2w;j(TF==l}|pXyPzzod-1_`02?4$TxhzioEHXrD}Yh+-h3RRIBC49>_ebw ztc(j&tQC*f>J$k#V3eg(tVx5)!JMoEEkxfV_YGY4Qdr3MAZH{hMUMBY{47Ua2bHXu zBA0`lPs!UbswRJ=-Z=0a+RAA#^JP#k{OrTl;h!U)$bkCRUk||kA8_eDhzIMy6%Cvf zU#OIQGTb639ob;;T)(dxF4bCiGE@hSZ|^)N@_j4}ed%}pH@BEkiJ;MqVxT^bSwd3M zh?5ww8yL{@X=!Z@T+!s!)+H+N+-a7RgWn|JF;u zwySa~`ekG~K00<2^ES%o0qa5>#tPU@T92noI1=uA@yzIbKdHU3<t3$=b)-9RO*qRAAif@vi`B#MBiw|T#?k>j z%gyjNTrWO~<%KjpXK+8I)_uJ>kKkUS%)CRu!6nIz97hS#qbK9CMI}@uP?11I0u>2V zBv6q+MFJHGR3uQ5Kt%!-2~;Fdkw8TPJ_}lmGJr8wqLzU2b9syh%dfCX@d*FJn7sl0JFO26iyf7X- z^1^C-U_2Y<%@_88)%n1f}>8XwqN4~#oLT+JV%HLATKTGWu*6XHF# zf+3Yhr>ckO8WIBJoj?BU`+xq$PwcC~VlEGHf%uO> z5nB;_Kr)bRkzvrgv~oZKEn%6O+C4qJJDncvmRcN-!xCxF_T7EGySuL=8#Mn0x#_W& zM;?3mg?^T=CL3tSIX3W29J2i2w(qJeWTP6{oNROe;${PAv&{}Rsx=#mhcNPL9>@UO zn%>zHi41TjL@`ifF~IG&8pQx_lX#7XcpD&Y;y{^gau5%KxT4IOfHKLlIXB4d@A~VX zwO=7wBwt0nYaL{371_X=KU8FEJ!Ct8!b`Tgpw5x$X=j}yeZ3@3zX2cF%X(93z(k=# zFiPmvLApr_y&=FFhdrdb0CAHB!UC;>G!6KqKz zc^K$G%WyLQgavi)a@V~}*PXu5X>d>kNn_(SXK-wAZiB1$Ft7#9r5Y!!^L7u0vdgs2 zTk7S;-J_$%;ON$)qpJS(+{x{Q(HlocDS!H2ciU6dU*O~MS59`c+ z3)mwKJ!~IgVq%>eE#hn+jf%K#AB_d1krRdT)&Ybb(pxR#%#q%zNwa$d>8&8L^sXlZW&c9Eo(320|L95_Y{zOn~s#oXC|$^y~udt+wFV7i)df0>XG}wqgK3 zIeqgL+ato;6#S9`7LeCfd$8nS0rbIzn+4DZ7d8v*`tvLZD5xg7)WHJZ@1191sfUG5 zL0%UNK$%@`Nnrt`0N*g(mE&PCd()m>T{lG&T{aUOYdD!`mg?7_2>j&az_--onP@Ix z0(m`5RJ)ikz=Yvt!T=KnnPBr=rf~9VIcVvI+LF6kk-Orn&-L9^xvSN=t2Mc+YjRg> z)zx#@s{A^f!Z7rB00(@3U+%@G<2iiaEpL~1z+bFT>AL~B59gTg2gLi~px8i1;vKB5 z_kp$gz;>dJ)s6u*hcU#5V?Fq0-HjaA>m66s*i7c#)*P%Acn&BSaSq6HaBq(Q$6nN; z86R~#h9iI6(}71j3XjL^*T-WheD=%BsD{v0j>lLCe>;9&!~n`1bvez4VJC zQ;i>fPgu6Jefpc-fIW_=&tz%QQf}xRl>*1`y96->WOBy;f5*mx?LPEKZw8}7nsRISpzd*&X0^Opky$T# z@Vv6R?R+|27J?YoE!3g}8M!5yI;9@VPAh&{53XZyy@SuF<3a0a;-sl>>IZLdrxGd> zs7Rn9frW>MIX+*@#yx+|^P3U*jvLPPxgNkbhO9(fg}54#&)i>w$a(TQL<4a> zVk_bX#EpoX5U)jSLu^OfjM#y=1+f#c3o(MY74bE^4+F=x_)p(>vjG259j}Z-+=0kH zo8f)D)Q5l0jaN1DaERc=hw3$Qc)g&$|0?n@-~ZQe4DTf1dm0TKvEe%l_-=vugymAL z-W@~v(T