OpenOffice.org (OOo) Calc Add-in How-to

Introduction

The aim of this document is to guide novice users through the creation of a full-fledged OpenOffice Calc Add-in. "Full-fledged" means that

The example Add-in will reflect how an Add-in should (to the best of my knowledge) be written and packaged for OpenOffice 3.0.1 as of April 2009.

The accompanying example, including source code, is released under an extremely liberal license. That is:

Jan Holst Jensen

jan at biochemfusion.com, April 2009 - latest update July 2009

Overview

The steps to follow in creating an Add-in are

Defining the IDL

The interface definition is a text file that describes the functions that our Add-in will implement. Below is the definition of the DoobieDo interface that I have defined for this example. It demonstrates various data types and also how you can allow for optional parameters.

#include <com/sun/star/uno/XInterface.idl>

module com { module doobiecompany { module examples { module DoobieDoo {

    interface XDoobieDoo
    {
       long doobieMult( [in] long a, [in] long b );
       double doobieDiv( [in] double a, [in] double b );
       string doobieConcat( [in] string s1, [in] string s2 );
       // The string 's3' should be optional. Model it as an 'any' 
       // to allow it to be NULL/None/Missing.
       string doobieConcatOptional( [in] string s1, [in] string s2, [in] any s3 ); 
    };

}; }; }; };

It used to be that the interface had to inherit from com::sun::star::uno::XInterface but this is no longer necessary as it is done implicitly (thanks to Juergen Schmidt for this tip). By convention all interfaces are named XSomething. The above IDL is saved in the file idl\XDoobieDo.idl but as far as I know there is no requirement about the IDL file naming convention.

The 'module' definitions put the functions within a well-defined name space so they won't conflict with other functions having the same name. Since the extension comes from the fictive company that has a domain name that is doobiecompany.com I have chosen the first two module levels to be com and doobiecompany - in line with the standard package/module hierarchy convention used by the Java and OpenOffice community.

OpenOffice will not load an IDL file directly so you should compile it into a binary type library file first. After compiling the IDL with the 'idlc' tool a .URD file has been produced and the tool 'regmerge' can then be used to produce a type library from one or more .URD files. Check the supplied batch file (Windows batch file) make.bat for the syntax for doing this.

You will have to install the OpenOffice SDK to get the 'idlc' and 'regmerge' tools. The SDK requirements state that you should have 'GNU make' installed to use the SDK tools. However you won't need 'GNU make' to build this example. The whole process of compiling the IDL and buidling the .OXT file is so fast in this setup that using a real makefile and resolve dependencies to determine what needs to be built is unnecessary. The script in make.bat builds everything from scratch and completes in less than half a second on my laptop.

The OpenOffice SDK is available from http://download.openoffice.org/3.0.0/sdk.html.

Choice of implementation language(s)

OpenOffice directly supports macros and extensions written in OOo Basic (roughly equivalent to Microsoft Visual Basic for Applications), Python, Java and C++.

The easiest way to implement simple functions would be to write them in OOo Basic. This works fine for macros and other functionality that you can trigger from a menu item or a button. However, when it comes to creating functions that can be used as cell formulas, OOo Basic won't work for you. The only OOo Basic functions that Calc can access via cell formulas are those that live in the "Standard" library. Unfortunately you are not allowed to modify the "Standard" library when installing an extension. You could of course ask the end user to manually copy/paste code into the "Standard" library, but that sort of defeats the purpose of packing the code into an extension package.

Instead we can implement the interface in Python. OpenOffice contains a Python runtime and you won't need to install a compiler for the language as you would have to implement the interface in Java or C++.

The implementation of the DoobieDo interface in Python is shown below. This code can be found in the file src\doobiedo.py.

import uno
import unohelper
from com.doobiecompany.examples.DoobieDoo import XDoobieDoo
class DoobieDooImpl( unohelper.Base, XDoobieDoo ):
        def __init__( self, ctx ):
                self.ctx = ctx
        def doobieMult( self, a, b ):
                return a * b
        def doobieDiv( self, a, b ):
                return a / b
        def doobieConcat( self, s1, s2 ):
                return str(s1) + str(s2)
        def doobieConcatOptional( self, s1, s2, s3 ):
                if s3 == None:
                        return str(s1) + str(s2)
                else:
                        return str(s1) + str(s2) + str(s3)
 
def createInstance( ctx ):
        return DoobieDooImpl( ctx )
g_ImplementationHelper = unohelper.ImplementationHelper()
g_ImplementationHelper.addImplementation( \
        createInstance,"com.doobiecompany.examples.DoobieDoo.python.DoobieDooImpl",
                ("com.sun.star.sheet.AddIn",),) 



Line 3 imports the interface definition from the type library using the module path as defined in the IDL.

The implementation of doobieConcatOptional checks whether the s3 parameter is set to None. This will happen if Calc only supplies two parameters when calling the function.

Note the name that the DoobieDoImpl instance is given when instantiated. This is the name that we need to reference in the CalcAddIn.xcu file that defines metadata for the function wizard.

Generating XML files

I personally find it frustrating to hand-edit XML and keep the various XML files in sync so I have created a simple Python script to generate the XML files. You can find it as src\generate_xml.py.

Marking a parameter as optional is seemingly done by wrapping the parameter's display name in square brackets. I have not seen this documented specifically, but thanks to Ariel Constenla-Haile, Argentina for providing me with a Java example showing just this. [Update: I may have misunderstood this originally. The square brackets do not seem to affect OOo Calc's interpretation of the parameter. It seems more likely that the parameter is considered optional because it is of type any.]

Generating a CompatibilityName in the CalcAddIn.xcu file is essential! Otherwise your users will (silently :-( ) suffer data loss when saving files in XLS format since OOo will not be able to map the UNO function names into their Excel-equivalents. Reading an XLS file back in without a proper CompatibilityName setup will give you strange results.

Note that I write a special .DONE file at the end of generate_xml.py to signal that the Python script has completed. This was necessary for me as the python interpreter that comes bundled with OpenOffice 3.0 executes asynchronously and I have not found a way to call it synchronously (this is the behavior on Windows - I don't know if it is different on Linux). If I did not wait for the appearance of the .DONE file in make.bat the rest of the batch file would continue to happily execute before the XML generation had completed. NOTE: This is no longer necessary in OOo 3.1 - the new Python version works synchronously as expected, simplifying scripting a lot.

Packaging the Add-in

To zip the files together I am using the 7-zip utility. There is zip-functionality built into OpenOffice but I haven't bothered to find out how to use it from a batch script, and I don't even know if you easily can.

7-zip can be downloaded from http://www.7-zip.org/.

The zip-file is created and renamed to .OXT. And that's it. You can now double-click the DoobieDo.oxt file and install the add-in.

Compatibility with Excel

If you implement a corresponding add-in in Excel you should do so as a .NET or COM Automation add-in. I first tried to implement DoobieDo as a couple of VBA functions in a sheet and saving that as an Excel add-in. However, Excel saves references to user-defined functions as '<filename of add-in sheet>'!<function-name> (notice the quotes).

If you attempt to add the filename of the add-in as a prefix to the CompatibilityName it won't work, e.g. you define the CompatibilityName for doobieMult to be DoobieDo.xlam!doobieMult. The quoting goes wrong somewhere in the process and Excel interprets the formula as e.g. ='DoobieDo.xlam!doobieMult'(A1;B1). Trying to quote the sheet/add-in name by setting CompatibilityName to 'DoobieDo.xlam'!doobieMult makes Excel read a formula like =''DoobieDo.xlam'!doobieMult'(A1;B1). That won't do either.

An approach that works is to implement an Automation add-in for Excel and prefix functions with the ProgID of the interface that implements the functions. I have for instance created a standard COM Automation Object in Delphi 2007, its CoClass named AutoAddIn having an IDoobieDo interface. Prefixing the CompatibilityName with "AutoAddIn.DoobieDo." now automagically makes things work, e.g. doobieMult has a CompatibilityName of AutoAddIn.DoobieDo.doobieMult.

To get the otherwise completely ordinary COM automation server to work as an add-in in Excel you need to add a special "Programmable" registry key as a subkey of the CoClass GUID key as described at http://www.codeproject.com/KB/COM/excelnetauto.aspx. A very simple thing that just took hours to find.

NOTE: Even with all this, OOo doesn't like add-in functions in sheets saved by Excel 2007 as XLSX files - there is an extra "_xll." prefix that it chokes on and you need to remove that yourself. Good ol' Excel 97/2000/XP .XLS files work fine as far as I can tell.