Handling of worksheet functions from Excel 2010 and newer

[Kohei Yoshida]

On Tue, 2013-11-12 at 00:30 +0100, Markus Mohrhard wrote:
> Hey,
> 
> 
> 2013/11/11 Kohei Yoshida <kohei.yoshida at collabora.com>
>         Hi there,
>         
>         Eike brought to my attention that my recent change to use the
>         internal
>         API to import formulas from xlsx broke the extra handling that
>         the UNO
>         API was doing to translate some of the newer Excel functions
>         from Excel
>         2010 and newer.  These new functions are typically prefixed
>         with _xlfn.
>         in the XML stream so that when the function name is e.g.
>         BETA.DIST, it
>         would appear as _xlfn.BETA.DIST in the sheet stream where the
>         formula
>         cell is stored.
>         
>         Long story short, the way I've decided to solve this was to
>         add another
>         set of function names and associate it with formula language
>         XL_ENGLISH,
>         which is, for now, only used for importing xlsx in a
>         significant way.
>         
>         So, when you guys have implemented these new functions in Calc
>         core, but
>         have trouble importing them from xlsx, please take a look at
>         resource
>         RID_STRLIST_FUNCTION_NAMES_ENGLISH_OOXML in
>         formula/source/core/resource/core_resource.src and see if the
>         stored
>         function name matches what's in the XML stream.
>         
>         Hopefully this fixes the old functionality from the UNO formar
>         parser
>         that we were missing in ScCompiler.
>         
> 
> 
> And a small request by me. Could we maybe add always directly an
> import/export test when we add a new function?

I did my share for my change.  The ones that were missing from the test
which went unnoticed by my change, I added them.

That said, I can't write all necessary tests on my own, so I'd much like
to get more help from others who work in this area.

> Adding an import/export test for a function is normally a task of a
> few minutes and should help us prevent regressions with these
> functions.

Agreed, though I have to say that "a task of a few minutes" is a bit of
a stretch. ;-)

For more complex functions, understanding the functions may require a
bit of a trial and error, and coming up with a suitable test case may
take a lot more than just "a few minutes"....

But I do agree with trying to make the writing of tests more a normal
part of our day to day development.

Kohei