tdf106013 Add-In functions that are also Excel2013 OOXML functions

[Eike Rathke]

Hi Winfried,

I'm also mixing in some quotes from your previous mail of this topic

On Sunday, 2017-02-26 14:10:57 +0100, Winfried Donkers wrote:
On Wednesday, 2017-04-12 16:37:54 +0200, Winfried Donkers wrote:

> I'm sort of stuck with tdf106013.
> 
> There is a group of Add-In functions that were newly defined in Excel 2013
> (IMCOSH, IMCOT, IMCSC, IMCSCH, IMSEC, IMSECH, IMSINH and IMTAN, I'll call
> them IMxx).
> 
> In Calc these functions are defined in scaddins and also mentioned in
> sc/source/filter/oox/formulabase.cxx in saFuncTable2013[].
> 
> 
> The problem is that the IMxx functions saved as xlsx by Calc won't open
> correctly by Excel and vice versa.
> 
> Calc writes the IMxx functions in xlsx as =IMxx(...), just as on ods.
> 
> Excel writes the IMxx functions in xlsx as =_xlfn.IMxx(...), and in ods as
> =IMxx(...).
> 
> The other Add-In functions are written by both Calc and Excel as
> =FUNCTIONNAME(...).

This is due to that the functions defined in the Add-In were defined by
Excel long ago and are part of OOXML, and those Add-In functions are
treated accordingly during export. Unfortunately the exceptional IM*
cases don't seem to fit into the XclFunctionInfo tables of
sc/source/filter/excel/xlformula.cxx, only the case of writing an
internal function as Add-In is handled there (those with
EXC_FUNCFLAG_ADDINEQUIV set). But the lookup is done by OpCode in those
tables, so that doesn't work for ocExternal.

> When I define the IMxx functions as regular functions, there are written as
> =_xlfn.IMxx(...) in xlsx, but when written to xls, they give problems when
> opening in Excel.

Where and how did you define them as regular functions? Were they also
written as _xlfn.IMxx to .xls? Or without _xlfn.? As macro calls? Or...?

> Any suggestions for a solution?

I could imagine to define some extra range of OpCode for XclFunctionInfo
starting at 0xFF00 or any other unused high value and for
ocExternal/svExternal do an extra lookup depending on the value of the
XclExpScToken's aTokData.mpScToken->GetExternal() programmatical name,
that for example is "com.sun.star.sheet.addin.Analysis.getImcsch", using
a simple std:map like
opcodevalue["com.sun.star.sheet.addin.Analysis.getImcsch"] = 0xFF00;

The corresponding XclFunctionInfo entry then could be

    { 0xFF00,               255,    2,  2, V, { RO_E, RO }, EXC_FUNCFLAG_EXPORTONLY, EXC_FUNCNAME( "IMCSCH" ) },

Note that these 255-export-only definitions due to the BIFF .xls macro
call storage use minparam+1 and maxparam+1, so for IMCSCH that expects
one parameter the definition is 2,2

In XclExpFmlaCompImpl::ProcessFunction() there's

    maFuncProv.GetFuncInfoFromOpCode( eOpCode )

which for (eOpCode == ocExternal && rTokData.GetType() == svExternal)
would needed to be called with the new eOpCode according to the mapping.

I hope that helps.


> I just noticed that tdf100450 regarding the Add-in function ACCRINT has a similar problem.
> It currently reflects pre-Excel2013 behaviour, but does not comply with ODFF1.2 or with the current Excel.

Probably keep the 7 parameters ACCRINT as Add-In function but name it
ACCRINT_EXCEL or such, and for 8 parameters introduce a new ACCRINT
function that is stored as _xlfn.ACCRINT (or whatever Excel does? OOXML
only defines 7 parameters).

  Eike

-- 
LibreOffice Calc developer. Number formatter stricken i18n transpositionizer.
GPG key "ID" 0x65632D3A - 2265 D7F3 A7B0 95CC 3918  630B 6A6C D5B7 6563 2D3A
Better use 64-bit 0x6A6CD5B765632D3A here is why: https://evil32.com/
Care about Free Software, support the FSFE https://fsfe.org/support/?erack
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 819 bytes
Desc: not available
URL: <https://lists.freedesktop.org/archives/libreoffice/attachments/20170412/77765be7/attachment.sig>