poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Greg Woolsey <greg.wool...@gmail.com>
Subject Re: Value vs. DateValue
Date Tue, 12 Dec 2017 16:59:45 GMT
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 <blake.watson@pnmac.com> 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>
> blake.watson@pnmac.com
> www.PennyMacUSA.com <http://www.pennymacusa.com/>
>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message