This sounds like it should be a Bugzilla issue. The Excel documentation [1] says "Text can be in any of the constant number, date, or time formats recognized by Microsoft Excel." I think that means the function needs to be rewritten in terms of the cell formats defined in org.apache.poi.ss.usermodel.BuiltinFormats, Checking would need to be strict, since most of them start with digits, and lenient parsing would make the result ambiguous. Order would also matter, and require some testing in Excel to see how it handles formats like "h:mm" and "mm:ss" - values like "1:11" match both. A quick test of this value shows Excel converts it with the "h:mm" format, which has a lower built-in index than "mm:ss". So perhaps just attempting to parse in built-in index order is sufficient.
Also present are the fractional data formats - I don't know if POI parses those yet or not. Using Format instances will make this function incredibly slow, as those are so heavy-weight to construct and use. We can't easily cache them either, since they are not thread safe. A ThreadLocal Map could be used, I suppose, and lazy-populated, but even that would only help a little. Sounds like, from the same Excel help page, this function should also be called implicitly whenever a formula needs to use a String value in a numeric context. [1] https://support.office.com/en-us/article/VALUE-function-257d0108-07dc-437d-ae1c-bc2d3953d8c2 On Mon, Dec 11, 2017 at 3:34 PM Blake Watson <[email protected]> wrote: > In Excel, if I have a cell with any of these: > > =VALUE("12-1-2017") > =VALUE("2017/11/05") > =VALUE("03/31/2015") > > Excel recognizes it's a date and returns the appropriate Double. In POI, > if I evaluate the cell, I get an error back. Looking at the code (and > documentation) for Value, it seems as though POI expects a non-date number. > There's also DateValue, of course. > > I think, Value should evaluate as Excel's VALUE function does. Unless I > miss something. In any event, VALUE in a cell should be something that POI > should be able to catch. > -- > > *Blake Watson* > > *PNMAC* > Application Development Manager > 5898 Condor Drive > Moorpark, CA 93021 > (805) 330.4911 x7742 <(805)%20330-4911> > [email protected] > www.PennyMacUSA.com <http://www.pennymacusa.com/> >
