tdf106013 Add-In functions that are also Excel2013 OOXML functions

[Winfried Donkers]

Hi Eike,>> 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.
> 
>> 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 have been working on and off ,too much off :-(, at tdf106013, but with 
little success. Currently, I don't see how to to proceed in 
XclExpFmlaCompImpl::ProcessFunction().
Can you give some more information about how you think these functions 
should be processed to have them properly saved in OOXML format?

Also, I wonder -ignorant of the consequences- if a solution the other 
way round would be better in the long term.
I mean, can't we make the Add-In functions regular functions (getting 
rid of a lot of almost identical code in the process) and just treat the 
saving as Add-In function in xls-documents and OOXML-documents. My guess 
is that there will be less Add-In functions in OOXML with every new 
Excel version when MS moves the Add-In function to a regular function, 
just like the IMxx-functions.
This is quite a big task, I realise that. But if the benefits I expect 
really are, I think I can move one function at a time once the framework 
is there.

Winfried