How to add-in in OpenOffice.org Calc

This text is an instruction on how to use the add-in interface of OpenOffice.org Calc to create additional functions, which can be used like the embedded ones. The Analysis add-in gives us an example, on how this could work in the practice. You should also take into account, that only basic techniques are explained. Furthermore this shouldn't be a detailed instruction on UNO, but only some parts are mentioned, which are used in the context of solving problems in the Analysis add-in. The intention is, that you can reach a result in a few hours, without any knowledge of UNO. But you should approximately know, what OpenOffice.org Calc is and what and how you can solve something with Calc. Of course the knowledge on how to create C-/C++-programms and which tools are used for that, is essential. For an explanation of the necessary interfaces please see the description of the add-in interface at sc.openoffice.org.

A good point to start with an own add-in might be to copy the explained files and modify all identifier with “Analysis” to your own needings. E.g. to “MyOwnAddin”. Although this name is unusual, it will help you to find the important places... ;-)

Another remark: You will often find the use of macros like “constREFXPS”. I didn't use this to make the source obscure, but to make the very long expressions more readable. Furthermore, if you know the meaning ones, it's no longer of big interest. Important macros can be found in "analysisdefs.hxx".

What we need to create an add-in

• UDK

  The so called "UNO Development Kit" is necessary to deal with the OpenOffice.org API. Descriptions and other important things around this theme are located on http://udk.openoffice.org/

• OpenOffice.org Calc

  If you want to run an add-in, the easiest way is to use OpenOffice.org Calc. The AutoPilot for functions is a good place, where the functionality of the add-in can be checked.

• Solver Tarball

  The Analysis add-in makes use of a library called "tools", which contains e.g. the resource manager and other usable stuff. This is not always necessary for another add-in, but in this case it was a great help.

• Appropriate compiler

  Find more infos on which environment to use on homepage of the UDK.

• Additional descriptions

  The OpenOffice.org Calc project has got its own homepage on http://sc.openoffice.org/. There you will also find a link to description of the add-in service.

Minimum parts to use

The Analysis add-in mainly consists of the class "AnalysisAddIn". To implement this class, I used the following files.

IDL-file

This file defines the interfaces. In this case it is called “analysisadd.idl”. All the interfaces are located in the module com::sun:star::sheet:addin. All functions, which are extending the OpenOffice.org Calc, are listed here. What you define here by the signature of a function, will reflect the type of the parameters and the type of return value. At the end of the module declaration, these interfaces are combined to the service "Analysis". In the Analysis add-in we are using three interfaces: XcompatibilityNames (which will be described in the “Advanced parts to use”), “XAnalysis” and "XAddin".

For a further description of the IDL stuff used here, please look at http://udk.openoffice.org/common/man/concept/unointro.html#UNOIDL and in the context with add-ins http://api.openoffice.org/docs/common/ref/com/sun/star/sheet/AddIn.html.

Header file(s)

The main header file is "analysis.hxx" which contains the class "AnalysisAddIn". Each declaration of a function in the IDL file matches to its counterpart as a method in this class. You should always consider that neither something is left out, nor may the signature differ from the one in the IDL file, because the derivation is pure virtual.

Furthermore, there are several methods which must be implemented to provide all required functionality for an add-in.

Constructor

"AnalysisAddIn()" (constructor) is called, when the shared library is initialised. In these add-in in, nothing is created at this time. This will happen, when it's necassaray (and possible) at later time.

Destructor

But much more important in the context of initialise/deinitialise a shared library is the destructor. While collecting experiences with the handling of resources, it happend to me, that the destructor of the resource manager crashed. In the end I found out, that all resource managers are deleted before the destructor of a shared library is called. In the case of an add-in, it is nearly impossible (or with a huge amount) to delete resource managers by yourself. Fortunately is not really a must, because it's done from another instance.

getProgrammaticFuntionName

This is not used by OpenOffice.org Calc, but it might be implemented, when the add-in is used in another context.

getDisplayFunctionName

This method returns the name for a given function, which should be displayed for the user, e.g. in a formula. In the Analysis add-in, the strings for this names are stored in resource files, so you will find several ones for different languages.

getFunctionDescription

The strings which this method returns are displayed in the AutoPilot for functions to give a short description for the entire function. As the DisplayFunctionName these strings resides in resources.

getDisplayArgumentName

For each parameter you will find a name which occurs in the parameter list. These strings are requested parameter wise.

One special parameter should be mentioned here: The "constREFXPS&" ("com::sun::star::beans::XPropertySet" in the IDL file) in some functions is not visible in the UI. It's a "hidden" parameter for which no request is generated, but it must be considered in the count of parameters, when informations for the other parameters are requested.

Same with resources applies here.

getArgumentDescription

Unlike the previous method, a description of a parameter is returned, which is also used in the AutoPilot for functions. The remarks done before also applying here.

getProgrammaticCategoryName

Each function in OpenOffice.org Calc can be assigned to a category. This is also used in AutoPilot for functions to make the listing of functions clearer. The lack in the current implementation of Calc is, that only the predefined categories are displayed. But I recommend to use own categories (e.g. "Technical"), when the predefined don't fit the proper context, even if these categories are displayed under add-in. But if one day the user defined will work, you don't need to change something! ;-)

So this method returns the internal name for the category, which the function should be assigned to.

getDisplayCategoryName

Same as getProgrammaticCategoryName but this name of the category will be displayed in the UI. Of course this makes only sense, if it's a user defined one. For the build in categories the names of course are fix. In the current implementation no language dependent strings are returned. This should be done, when Calc gets support for user defined categories.

getCompatibilityNames

This method is very important in the context with the im- and export of Excel files. For each function a list is returned, where every element holds a name and a accompanying language. With this it is possible to parse a number of language variants of the add-in for the import. One remark: Excel is only able to load such add-in functions, which are in the same language as the installed add-in. In this case, OpenOffice.org Calc is a little bit more advanced than Excel... :-) In the opposite direction, when exporting to Excel, the installed OpenOffice.org language is chosen to select a name.

The main goal for the implementation of the Analysis add-in was to get a better compatibility to Excel. For this reason the "CompatibilityNames" are chosen in a way, that they are identically to those which are uses by Excel. These don't need to match the "DisplayFunctionNames", but in most cases they do. Exceptions are the cases, where the Excel namings colliding with already build in functions of Calc. In this cases, a simple "_ADD" is appended to the name.

setLocale

By calling this method, Calc tells the add-in, in which language to operate. This affects of course the resource manager. Because this method is called after running the constructor, the resource manager is not initialised in the constructor but by calling this method. It would be even possible to change this language in the runtime, because all dependent parts will be created newly. But this is not provided by OpenOffice.org.

getLocale

Gives back, what is set by setLocale before.

getServiceName

The name is in this case simply "com.sun.star.sheet.addin.Analysis".

getImplementationName

"com.sun.star.sheet.addin.AnalysisImpl" is given back here.

supportsService

This method checks, wether a requested service can be supported or not. The Analysis add-in supports only "com.sun.star.sheet.AddIn" and "com.sun.star.sheet.addin.Analysis".

getSupportedServiceNames

In the returned sequence of strings are all services listed, which are supported by the add-in. This is a little bit "double" to supportsService, but it must be implemented to meet the requirements of an add-in implementation. The strings are same as supportsService can check.

Other methods in the class AnalysisAddIn

I'm only explaining that functions, which gives a good overview of the ways parameter can be passed to and from a function. A good description of possible parameters can also be found on http://api.openoffice.org/docs/common/ref/com/sun/star/sheet/AddIn.html.

getWorkday

This function returns a simple signed integer with a length of 32 Bits (sal_Int32). Even this is just a simple number, it represents a date. This date can be displayed in OpenOffice.org Calc by choosing a proper date format for a cell. The meaning of this number is the offset in days from a given base date. So it is necessary to get this base date to do the date calculations in the right way, e.g. if you try to get the weekday, month, etc. from a date. For this purpose the first parameter is good for. The XPropertySet contains a PropertyValue named "NullDate", which is the mentioned base for all date calculations in Calc.

In the context of passing parameters to an add-in another remark is essential: When a user e.g. enters a floating point value, the resulting double value is cut to a sal_Int32. So there is no need to do this in the add-in. This also applies to strings entered directly (not strings which are the result of a reference). When the functions want to have a number, Calc will convert the text to a proper number.

The next very important parameter is the ANY (::com::sun::star::uno::Any). This data type can carry various other (basic) data types like double, OUString, Sequence, Void... An optional paramter will be represented e.g. by Void, when this parameter is left empty from the user. But be carefull: The content of an empty cell is an empty string, not a Void as you may expected. This must be considered when dealing with a range of cells as a parameter and empty cells must be ignored for the calculation.

getYearfrac

This method gives back a double. This data type is similar to the usage of a sal_Int32, but with a bigger definition range. A special application for a double could also be the combination of a date and a time. In this case the fractional part of the number is the day time expressed as a fraction of a whole day. E.g. "0.5" would be displayed as "12:00" when you choose a number format like "hh:mm".

getIseven

This function is another example of the different meaning of number. The returned sal_Int32 is used as a boolean value where a zero simply means false and all other numbers true. This behaviour is like in C/C++.

getMultinomial

When a range is used as a parameter, e.g. "A1:D42", a sequence of sequences (in this case of sal_Int32) is passed to the method. The implementation of this function is also a good example on how to get each element of a sequence (of sequences).

What is missing as an example in the Analysis add-in

Whenever you want to do calculations with matrixes, a sequence of sequences must be given back as the result. Calc will call the function ones and copy the single results to the matching cells, depending on how the formula was entered. E.g., if you enter the matrix formula in the array A1:E5 and you give back a 5x5 sequence as the result of your function, you will find each element of the sequence in the corresponding cell. Since you won't find an example for this purpose, just try to play around on your own... ;-)

How errors are returned to Calc

When errors occur in an add-in, an exception is thrown to indicate, that something went wrong. In the current implementation, there are only two different exceptions used: RuntimeException and IllegalArgumentException.

The RuntimeException is thrown, when the resource manager could not be initialised.

But whenever a value paramter is invalid or a calculation can't be done (for several reasons), an IllegalArgumentException is used. Other errors (e.g. number of parameters is wrong) are checked by Calc so the function in the add-in isn't called in this cases. That's why other errors than "Illegal Argument" can be shown, although the add-in functions only use this one.

Source file(s)

Most things in the context of the needed methods are already mentioned in the part before. Here I want to draw your attention on some structs, wich might reduce the amount of work for a new add-in.

For each function the add-in has to know a bunch of information which are asked from Calc in the initialisation phase (see also the description of getProgrammaticFuntionName up to getSupportedServiceNames). This information is stored in the table pFuncDataArr at the beginning of "analysishelper.cxx". Each line expands to the info about the internal name (of the method to the function), Resource ID of the UI name, Resource ID of the function description (also with parameter name and parameter description), a boolean value which is true if the name already exists in Calc, a boolean value which is true when the internal parameter (XPropertySet) is passed to the function, Resource ID of the list for the translated function names for several languages, the number of parameters which are described and at least the category to which the function should belong to. Because the Resource IDs and the method names use a similar name scheme, the necessary number of details in a data line is reduced with the use of a macro to good readable amount. Another yield is the good extendability of this table.

When you go into the sources you might wonder, why in some cases a functionality is implemented, which is already availlable in other parts of the OpenOffice.org. Well, this is done because we wanted the Analysis add-in to be as independent as possible from the OpenOffice.org. The optimum would have been to be only dependent from UNO (or the UDK). This was not possible at all, e.g. the handling of the resources is to complicated. But when you create your own add-in it would be possible (without the use of resources and other small things) to be independent from these shared libs so you can use your add-in in various versions of OpenOffice.org Calc until the UDK changes its version (this happens much less for the UDK than for the OpenOffice.org!). The Analysis add-in must however be build new when the version number (e.g. 632 -> 638) of the OpenOffice.org is changed. This is of course uncomfortable when you have to do a build every several weeks to be always up to date even if you didn't change a bit of your own code.

Advanced parts to use

One big goal of the Analysis add-in should have been the ability to provide the function name translation for as much languages (in which the Excel Analysis add-in is provided for) as possible. To achieve this I used resources to get multiple language support for the names and descriptions. So depending from the installed language, you will get the proper (hope so) translation of all strings. A side effect of our translation process is, that I get a list of all translated function names in the a correct character encoding (UTF-8) from an intermediate file. Read more to this topic later in a following paragraph. Even so it's not that hard to use resources, it would be much heavier to translate them into several languages. It's not the process of translating itself which is not so easy, but to convert the strings to an encoding, which the resource manager can cope with. Internally we use several tools and automatism to control the flow of the data from the development <-> translation. Because this is not yet available / usable for the public, it's not easy to provide more than English for an outsider. Perhaps this will change in the future.

When dealing with resources, we need IDs to select the desired one out of *.res-files. These IDs are located in "analysis.hrc". The description of the functions and the name and description of the parameters is located in "analysis.src". In each sub-resource you will find several strings. The first one (String 1) is always the function description. After this (String 2) the name and description (String 3) of the first parameter is following. String 4 and String 5 belongs to the second parameter, and so on. The first step in the development was the providing of the texts without tag (default development languages = German) and (if possible) the English. The rest of the strings was merged with the above mentioned tools for the translation. To learn more about how to get these strings, you should follow what "AnalysisAddIn::GetFuncDescrStr()" does when it's called.

The next part of the resources is "analysis_funcnames.src". Here are function names located, which are visible in the UI of OpenOffice.org Calc. Follow "AnalysisAddIn::GetDisplFuncStr()" to examine the access to these strings.

But the more "tricky" part of the resources is in "analysis_deffuncnames.src". This set of string arrays, where each array holds all known function names in different languages for one function. These are always sorted in a special order. And even if they might be the same in some languages, they must appear for every language seperately to ensure, that the proper string is selected (for a given language) for the Excel export! In "AnalysisAddIn::getCompatibilityNames()" you can see the usage of these string arrays.

Building an add-in

When you create your own add-in you surely want to make it run in OpenOffice.org Calc. To do so you must build your source to get an executable shared library. This process is controlled by a makefile called "makefile.mk". As a first step for your own project you might copy the one from the Analysis add-in and exchange all name with "Analysis" by the name of your own add-in.

All source files which generate object files (C++/C-source) are listed in the define for "SLOFILES" e.g. "$(SLO)$/analysis.obj" for "analysis.cxx". So your additional C++ source files will go here.

The right place for resource files is found in the define for "SRCFILES". Put your one here and you should succeed with resources. If you don't use any resources, you should remove the section which is marked with "Resourcen".

In the definition for "SHL1STDLIBS" you will find at least all libraries to which the add-in should be linked against. While "CPPUHELPERLIB", "CPPULIB", "VOSLIB" and "SALLIB" is a good default for your own add-in, the use of "TOOLSLIB" is optional. Of course it's always possible to use the functionality of the shared libraries coming with OpenOffice.org. In this case you have to look up, which lib to link additionally.

Using an add-in

When you've successfully created an add-in, Calc should know, that this exists. So copy the created shared library into the directory "program" and if you use resources, copy the *.res files into "program/resource" directory. Then you have to register this new library with the regcomp tool (wich should come together with the Solver tarball), e.g. "regcomp -register -r \program\applicat.rdb -c analysis638mi.dll". If you then start OpenOffice.org and open a Calc spreadsheet, you can access your new functions as the build in ones. To check this, open the AutoPilot for functions an search in the list for your new ones. There they should work like all others. You can also check, if the functions are listed in the proper category.

Link summary

OpenOffice.org Calc project homepage
AddIn service API documentation
Browsable source code of the Analysis add-in
UDK project homepage
UNO-IDL description (in the UDK project)

Author: Gunnar Timm (created: 20010802)
Last change: 20011002