Yegor,
I'll take a look at how the AnalysisToolPak functions are implemented to see
if I can contribute.
After I modified the spreadsheet to not use EOMONTH I was checking some
other calculations and have come across a strange problem.
Since I can't use EOMONTH I'm using this to get the first day of the next
month.
Assume A2 = 1/1/2011, then the function in B2 to get the first day of the
next month is:
B2: =DATE(YEAR(A2), MONTH(A2)+1, 1)
This works fine in Excel and, I thought, in POI. But I noticed some formula
values were not being returned correctly in POI, and after digging into it
there seems to be an issue with the MONTH function.
In Excel I get this:
=MONTH(A2) => 1
But POI says MONTH(A2) => 12.0.
Say what? And that explains why a function that checks to see if two dates
are equal is failing when the dates should be equal, and the dependent
formulas are not returning the correct values.
The serial date value for "1/1/2011" is 39082. If I use the DATE function
above for B2 I get a serial number of 39083 and a date of 1/2/2011, which is
quite wrong.
Is there some trick to using MONTH and other date functions in Excel so that
POI will evaluate them properly?
I did look at the source for CalendarFieldFunction.java (I think that's
what's handling MONTH), and I noticed a TODO about "fix 1900/1904 problem".
I'm using a Mac, and the Excel docs do indicate that Mac and Windows systems
use 1904 and 1900, respectively, for their serial dates. Could this be the
problem, or at least part of it?
Cheers,
Chris
On Sat, Jun 11, 2011 at 11:23 PM, Yegor Kozlov <yegor.kozlov@dinom.ru>wrote:
> On Sun, Jun 12, 2011 at 8:28 AM, Chris McCann <testflyjets@gmail.com>
> wrote:
> > Thanks, Nick. Your response made me dig deeply into all of the functions
> > used in dependent cells. I found that we were using the EOMONTH function
> to
> > calculate the first/last day of a month. That function is part of the
> > Analysis Toolpak, and I assume that's why it's not implemented. Is there
> a
> > list of implemented functions somewhere in the docs?
> >
>
> Mappings for the AnalysisToolpack are defined here:
>
> http://svn.apache.org/repos/asf/poi/trunk/src/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java
>
> At the moment POI implements only five ATP functions:
>
> ISEVEN
> ISODD
> MROUND
> RANDBETWEEN
> YEARFRAC
>
>
> It should be easy to add support for EOMONTH, just follow the pattern:
> create a subclass of FreeRefFunction and register it in
> AnalysisToolPak.java.
>
> Patches are welcome. Let me know if you need guidance.
>
> > Changing our EOMONTH for a different function that is supported made
> > everything work, so that problem is solved.
> >
> > Not being familiar with the code base in terms of how unimplemented
> > functions are handled it seems to me it should be possible to at least
> > surface the name of the requested, unimplemented function in the
> exception
> > message. That would be tremendously helpful for debugging.
> >
>
> I'm going to look into the code and see if it is possible to propagate
> function name into the exception message.
>
> Yegor
>
> 
> To unsubscribe, email: userunsubscribe@poi.apache.org
> For additional commands, email: userhelp@poi.apache.org
>
>
