Difference between revisions of "Calc/Implementation/Spreadsheet Functions"
(→sc/inc/opcode.hxx) |
(→sc/source/core/src/compiler.src) |
||
Line 72: | Line 72: | ||
</source> | </source> | ||
− | === sc/source/core/src/compiler.src === | + | === sc/source/core/src/compiler.src (≤ DEV300_m38)<br />formula/source/core/resource/core_resource.src (≥ DEV300_m39) === |
These are the resources for function names. There are 3 resource bundles: | These are the resources for function names. There are 3 resource bundles: | ||
Line 119: | Line 119: | ||
Again, the absence of the <code>[ en-US ]</code> field tells the localization | Again, the absence of the <code>[ en-US ]</code> field tells the localization | ||
tools that the name '''must not''' be localized. | tools that the name '''must not''' be localized. | ||
− | |||
=== The compiler knows the function === | === The compiler knows the function === |
Revision as of 11:11, 8 February 2009
Procedure to add a new Calc spreadsheet function.
Contents
- 1 Introduction
- 2 Make the formula compiler know the function
- 2.1 sc/inc/compiler.hrc (≤ DEV300_m38)formula/inc/formula/compiler.hrc (≥ DEV300_m39)
- 2.2 sc/inc/opcode.hxx (≤ DEV300_m38)formula/inc/formula/opcode.hxx (≥ DEV300_m39)
- 2.3 sc/source/core/src/compiler.src (≤ DEV300_m38)formula/source/core/resource/core_resource.src (≥ DEV300_m39)
- 2.4 The compiler knows the function
- 3 Publish the function to the Function Wizard
- 4 Let the interpreter handle the function
- 5 Excel import/export
Introduction
Let's assume you want to implement a new spreadsheet function and the function was defined by the OASIS OpenDocument Format Formula subcommittee, see latest revision of the specification (draft). Let's further assume the function's name is MYFUNC and will take 2 parameters, of which the second parameter is optional and defaulted to 0, and returns a number, as following:
Syntax: MYFUNC( Number Param1 [ ; Number Param2 = 0 ] )
Returns: Number
Make the formula compiler know the function
sc/inc/compiler.hrc (≤ DEV300_m38)
formula/inc/formula/compiler.hrc (≥ DEV300_m39)
These are the defines used by the resources for function names and the Function Wizard, and the numerical values of OpCode for the formula compiler and interpreter. Note that once defined the names must not be changed because they are used by the localization tools as identifiers.
Add a new define, in this case for 2 parameters append it to the section for functions with more than 1 parameter near the end of the file. Name the define SC_OPCODE_MYFUNC and insert it right before the define of SC_OPCODE_STOP_2_PAR, give it the value SC_OPCODE_STOP_2_PAR had, and increment the values of SC_OPCODE_STOP_2_PAR and SC_OPCODE_LAST_OPCODE_ID. If before the section looked like
#define SC_OPCODE_STOP_2_PAR 393 #define SC_OPCODE_LAST_OPCODE_ID 392 /* last OpCode */
it should then be
#define SC_OPCODE_MYFUNC 393 #define SC_OPCODE_STOP_2_PAR 394 #define SC_OPCODE_LAST_OPCODE_ID 393 /* last OpCode */
sc/inc/opcode.hxx (≤ DEV300_m38)
formula/inc/formula/opcode.hxx (≥ DEV300_m39)
Here the OpCodeEnum values are defined. Note that in a non-product build
(--enable-dbgutil during configure) there is a
typedef OpCodeEnum OpCode;
to show enum names in the debugger, while in a product build it is
typedef OpCodeEnum USHORT;
to save some memory, since compilers tend to produce an int for an enum.
Find a "right" place for the new enum. Although the way the definitions are setup the placement doesn't matter, there are sections with different topics, such as String functions and Statistical functions. Maybe the correct place for MYFUNC would be under miscellaneous. Best practice is to add a new OpCode to the end of such section. Name the OpCode ocMyFunc and add the line
ocMyFunc = SC_OPCODE_MYFUNC,
sc/source/core/src/compiler.src (≤ DEV300_m38)
formula/source/core/resource/core_resource.src (≥ DEV300_m39)
These are the resources for function names. There are 3 resource bundles:
- RID_SC_FUNCTION_NAMES
- English UI display names. These get localized for the UI of other languages.
- RID_SC_FUNCTION_NAMES_ENGLISH
- These English names are used internally to store/load ODF v1.0/v1.1 and for API XFunctionAccess. Usually the name is identical to that in RID_SC_FUNCTION_NAMES. Once defined and "in the wild", the name must not be changed.
- RID_SC_FUNCTION_NAMES_ENGLISH_ODFF
- These English names are used internally to store/load ODFF aka OpenFormula as of ODF v1.2. Once defined, the name must not be changed.
The new function name must be defined for all 3 resource bundles.
To the end of
Resource RID_SC_FUNCTION_NAMES
add
String SC_OPCODE_MYFUNC { Text [ en-US ] = "MYFUNC" ; };
The [ en-US ]
field tells the localization tools that the name may
be localized.
To the end of
Resource RID_SC_FUNCTION_NAMES_ENGLISH
add
String SC_OPCODE_MYFUNC { Text = "MYFUNC" ; };
The absence of the [ en-US ]
field tells the localization tools
that the name must not be localized.
To the end of
Resource RID_SC_FUNCTION_NAMES_ENGLISH_ODFF
add
String SC_OPCODE_MYFUNC { Text = "MYFUNC" ; };
Again, the absence of the [ en-US ]
field tells the localization
tools that the name must not be localized.
The compiler knows the function
After having added the necessary entries to sc/inc/compiler.hrc, sc/inc/opcode.hxx and sc/source/core/src/compiler.src, the formula compiler now knows the new function name and is able to compile an expression where it is used, and it can be stored in and loaded from a document. Of course nothing else works, the interpreter doesn't know how to handle it and will generate an error if encountered. The function and its parameters will not appear in the Function Wizard.
Publish the function to the Function Wizard
sc/inc/scfuncs.hrc
Function groups (categories) and HelpIDs for functions are defined here. Lookup the group where functions of ID_FUNCTION_GRP_... matching the new function's category are defined and append an entry, incrementing the offset of the last entry by one. For our function that could be
#define HID_FUNC_MYFUNC (HID_SC_FUNC_DUMMY+(ID_FUNCTION_GRP_MATH*ID_FUNCTION_OFFSET)+56)
sc/source/ui/src/scfuncs.src
This large resource file contains all elements necessary to display functions in the Function Wizard. It defines the function's short description, the number of parameters, whether they are optional, and the description of each parameter. For a detailed description of fields see the comment on top of the file.
Add the new function to the end of one of the two resource blocks RID_SC_FUNCTION_DESCRIPTIONS1 or RID_SC_FUNCTION_DESCRIPTIONS2. Which one doesn't really matter, but functions should be more or less equally distributed over the two blocks. There are two blocks because one resource block couldn't have more than 64k data, just another legacy from Win16 times.. looked up again right now (2008-08-23) in the meantime this restriction seems to have been obsoleted, which would have to be verified by a test build using one resource block only though.
Resource SC_OPCODE_MYFUNC { String 1 // Description { Text [ en-US ] = "Calculates foo, optionally using bar." ; }; ExtraData = { 0; ID_FUNCTION_GRP_MATH; U2S( HID_FUNC_MYFUNC ); 2; 0; 1; 0; }; String 2 // Name of Parameter 1 { Text [ en-US ] = "Number" ; }; String 3 // Description of Parameter 1 { Text [ en-US ] = "A value for which foo is to be calculated." ; }; String 4 // Name of Parameter 2 { Text [ en-US ] = "Number" ; }; String 5 // Description of Parameter 2 { Text [ en-US ] = "The bar value." ; }; };
Some comments on the ExtraData block, for more details see scfuncs.src:
ExtraData = { 0; // The function is not suppressed and available in UI. ID_FUNCTION_GRP_MATH; // The category in which the function is displayed. U2S( HID_FUNC_MYFUNC ); // The HelpID of this function. 2; 0; 1; // 2 parameters, of which the 2nd is optional. 0; // None of the parameters are suppressed in the UI. };
sc/util/hidother.src
Here the HelpIDs to be used within the Function Wizard are propagated to the help system. Go to the end of the section containing HID_FUNC_... and append an entry,
hidspecial HID_FUNC_MYFUNC { HelpID = HID_FUNC_MYFUNC; };
The Function Wizard knows the function
Now the Function Wizard can display the function and its parameters, and online-help may be authored.
Let the interpreter handle the function
sc/source/core/inc/interpre.hxx
Add a member method to class ScInterpreter that will handle the new function. Here this would be
void ScMyFunc();
Take care that the new member function doesn't resemble the name of some already existing class, for example the method for the ADDRESS() function is named ScAddressFunc() because ScAddress(), if called without this-> prefix, would be the ctor of class ScAddress instead. If in doubt, add ...Func() to the name.
sc/source/core/tool/interpr4.cxx
In method ScInterpreter::Interpret() add to switch ( eOp )
the
call to the member function for the OpCode:
case ocMyFunc : ScMyFunc(); break;
sc/source/core/tool/interpr?.cxx
Pick one of the interpr?.cxx source files where the new method may fit. There is no general advice which file exactly that might be, be sensible. As a guide line
- interpr1.cxx
- Basic math functions and informational functions.
- interpr2.cxx
- Date functions and financial functions.
- interpr3.cxx
- Statistical functions.
- interpr4.cxx
- Interpreter managing and stack related functions, you shouldn't need to add anything there.
- interpr5.cxx
- Matrix functions.
- interpr6.cxx
- Some number crunching that in the past had to be compiled without optimizations, though in the mean time code changed and this probably is not necessary anymore.
For our function, since that expects two, one optional, numerical scalar arguments, this would be:
void ScInterpreter::ScMyFunc() { BYTE nParamCount = GetByte(); // The MustHaveParamCount...() functions check the number of parameters and // if they do not fit push an error on the stack, if the method fails // (returns false) we return immediately. if ( !MustHaveParamCount( nParamCount, 1, 2 ) ) return; // Arguments are popped from the stack from right to left. double fParam2; if (nParamCount == 2) fParam2 = GetDouble(); else fParam2 = 0.0; double fVal = GetDouble(); if ( /* does fVal meet all constraints */ ) { double fResult = /* calculate foo */ ; PushDouble( fResult ); } else PushIllegalArgument(); }
The not so easy case of non-scalar arguments
TODO: section needs elaboration.
If the new function would handle parameters that are not scalar values, for
example a NumberSequence or matrix/array, they would have to be treated
explicitly, checking and reacting on the type of each argument. Lookout for
functions that use the GetType()
call and handle StackVar
svDoubleRef
or similar. Ask on the dev@sc mailing list if in
doubt.
In this case the parameter types have to be added to file
sc/source/core/tool/parclass.cxx
that describes how parameters are to be treated in detail. If the function does not accept other than scalar parameters, nothing has to be done here. Please read the comments there and in sc/source/core/inc/parclass.hxx. Ask on the dev@sc mailing list if in doubt.
Excel import/export
sc/source/filter/excel/xlformula.cxx
If the new function is also supported by Microsoft Excel®, for
import/export it has to be added to the binary filter as well. Depending on
from which version Excel knows the function, it has to be added to a
corresponding XclFunctionInfo
table in xlformula.cxx. Ask
our Excel expert Daniel on the dev@sc mailing list for details. For details of
the tables' content see the declaration of struct XclFunctionInfo
in sc/source/filter/inc/xlformula.hxx.
oox/source/xls/formulabase.cxx
As if that wasn't enough, nearly the same has to be added to the new binary
filter for Microsoft Excel® that shares some mechanisms with the new
Microsoft Office Open XML (MOOXML) filter. The
implementation in sc/source/filter/{excel,xcl97}/ will be deprecated
later. For details of the new tables see declaration of struct FunctionInfo
in oox/inc/oox/xls/formulabase.hxx.