hints asked for fdo#59727

[Eike Rathke]

Hi Winfried,

On Wednesday, 2013-05-15 11:23:56 +0200, Winfried Donkers wrote:

> > They are implemented in scaddins/source/datefunc/
> 
> I wouldn't have looked for ROT13 there... Is that a location to be maintained or should it be moved to scaddins/source/analysis?

Keep things as they are. Traditionally the analysis/ directory is for
functions that once upon a time Excel had as extensions in its
Analysis-Pack before they were included in the Excel core, and as such
also need to be stored differently for older Excel versions.

The datefunc/ directory contains functions that were Calc Add-In example
code already in the old binary Add-In times and were ported to the new
Add-In infrastructure to keep documents working that used them. ROT13 is
in there because there was no sense in creating yet another Add-In for
just one single function.


> > It seems they are missing from sc/source/filter/oox/formulabase.cxx so
> > adding them there should solve the store case, and if they are stored
> > correctly they should also be read correctly. (assuming, I didn't try or
> > investigate deeper)
> > 
> > The problem probably is that these funcions are not known by Excel, which is
> > to be verified. If they aren't, then we can (or need to?) store them as the
> > programmatic name, e.g.
> > com.sun.star.sheet.addin.datefunctions.getdiffweeks, but need the
> > mapping to ODFF ORG.OPENOFFICE.WEEKS in
> > sc/source/filter/oox/formulabase.cxx
> 
> As far as I can find out, the problem functions (WEEKS, MONTHS, YEARS, ISLEAPYEAR, DAYSINMONTH, DAYSINYEAR, WEEKSINYEAR, ROT13) do not exist in Excel. 
> I don't have Excel, so I can't be certain.

I checked with Excel 2013 and none of these functions is known.

Btw, funny thing is that in Excel =ROT13() understandably results in
#REF! error.

> In sc/source/filter/oox/formulabase.cxx I want to add these functions, but I have some questions about the members of struct FunctionData.
>  Take e.g. WEEKS:
>     { "WEEKS",                  "ORG.OPENOFFICE.WEEKS", NOID,   NOID,   3,  3,  V, { VR, VR, VR      }, FUNCFLAG_EXPORTONLY }

It seems your example follows the "Analysis add-in" section in
saFuncTableBiff4[], which is the add-in I mentioned above. The new
entries should go to a different place and also not be flagged as
FUNCFLAG_EXPORTONLY.

The correct place would be a duplicate of saFuncTableOdf[], say
saFuncTableOOoLO[], just to keep things cleanly separated because the
functions are only known to LO and not defined in OpenFormula. The
saFuncTableOOoLO also needs to be added to
FunctionProviderImpl::FunctionProviderImpl() after the init of
saFuncTableOdf.

This entry then should be 

    { "ORG.OPENOFFICE.WEEKS",   0,                      NOID,   NOID,   2,  2,  V, { VR }, FUNCFLAG_MACROCALLODF },

> Should mpcOdfFuncName also include ORG.OPENOFFICE.?

Yes, that is the identifier used to map the function.

> Should I use IDs (there not in any BIFF, so I set both to NOID)?

No IDs available, so NOID ;-)

> Which flag should I use (i.e. does filter mean: leave out, or does it mean: process)?

Filter here means conversion from/to file format to/from our core. So
FUNCFLAG_EXPORTONLY has the description  /// Only used in export filter.
which means the function is only written during export but not read
during import.

> I have not yet given serious attention to the FunctionParamInfo.

These are just functions that take "normal" parameters, no fancy thing
such as matrix, volatile result, ... so "V, { VR }" should be good for
all of them.


> If I understand you correctly, WEEKS in an ods should translate to 'org.openoffice.weeks' when saving tp xlsx, which should be translated to WEEKS when saving to ods. Am I correct?

Yes. Respectively use uppercase ORG.OPENOFFICE.WEEKS for .xlsx; setting
the table up like described should solve things.

> I intend to fix the xlsx-bug first and then to look at the
> inconsequent error messages when no parameters are entered

If you are referring the Err:511 vs Err:504 cases, the Err:511 is for
cases where no function name was recognized and the formula contains =()
That should magically disappear if the names are stored/loaded
correctly.

Note that to fully understand and test things you'll need different LO
versions, best 3.6.6, 4.0.3 and master and save/load with each
combination. The results will differ because different versions
understand different subsets of functions.

> and
> posisbly to see if I can get the locale presentation right (i.e.
> present English function names when set so in options).

Which is something completely different ;-)  There exists
https://bugs.freedesktop.org/show_bug.cgi?id=50118

> And of course do some clean-up if I can.

If you do clean-ups then please in commits separated from the changes
that actually solve some bugs' problems. Just saying ;-)

Thanks for your investigations!

  Eike

-- 
LibreOffice Calc developer. Number formatter stricken i18n transpositionizer.
GPG key ID: 0x65632D3A - 2265 D7F3 A7B0 95CC 3918  630B 6A6C D5B7 6563 2D3A
For key transition see http://erack.de/key-transition-2013-01-10.txt.asc
Support the FSFE, care about Free Software! https://fsfe.org/support/?erack
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 836 bytes
Desc: not available
URL: <http://lists.freedesktop.org/archives/libreoffice/attachments/20130515/7e3c213c/attachment.pgp>