poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Blake Watson <blake.wat...@pnmac.com>
Subject Re: Value vs. DateValue
Date Tue, 12 Dec 2017 22:03:32 GMT
And documentation error: parseYYYYMMDDDate doesn't return a double per the
text, but a java.util.Date.

parseYYYYMMDDDate

public static java.util.Date parseYYYYMMDDDate(java.lang.String dateStr)

Converts a string of format "YYYY/MM/DD" to its (Excel) numeric equivalent
Returns:a double representing the (integer) number of days since the start
of the Excel epoch

On Tue, Dec 12, 2017 at 1:56 PM, Blake Watson <blake.watson@pnmac.com>
wrote:

> DATEVALUE isn't currently supported, I realize, too, so...
>
> On Tue, Dec 12, 2017 at 8:59 AM, Greg Woolsey <greg.woolsey@gmail.com>
> wrote:
>
>> 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://urldefense.proofpoint.com/v2/url?u=https-3A__support
>> .office.com_en-2Dus_article_VALUE-2Dfunction-2D257d0108-
>> 2D07dc-2D437d-2Dae1c-2Dbc2d3953d8c2&d=DwIFaQ&c=dmLo
>> mitc30UP5j2qU8E1rg&r=p42pHJHEwFZOHtVFHKJUdL2fYbroN33stXXb3Ps
>> thjw&m=A-h6HE-pwhhmdsnyNXVeQUOADGf5O9XFaA9IkHejQZM&s=
>> rax8HzzVk34s6HLibxE-gWtf-oeyghcc66PZ2sPOCLU&e=
>>
>>
>> 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/>
>> >
>>
>
>
>
> --
>
> *Blake Watson*
>
> *PNMAC*
> Application Development Manager
> 5898 Condor Drive
> Moorpark, CA 93021
> (805) 330.4911 x7742
> blake.watson@pnmac.com
> www.PennyMacUSA.com <http://www.pennymacusa.com/>
>



-- 

*Blake Watson*

*PNMAC*
Application Development Manager
5898 Condor Drive
Moorpark, CA 93021
(805) 330.4911 x7742
blake.watson@pnmac.com
www.PennyMacUSA.com <http://www.pennymacusa.com/>

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