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
Custom spreadsheet functions will be added to Calc.
Calc's Function Wizard will be aware of the custom functions so they can be browsed and tested interactively.
The Add-in will be packaged as an .OXT file so an end-user can install the Add-in by simply double-clicking the .OXT package.
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:
You are absolutely free to adapt the code and the accompanying documentaton for whatever purpose you see fit (obeying any license restrictions of the underlying tools and programs of course).
Acknowledgement of origin would be appreciated but is not required.
No warranty whatsoever. This code may break your computer, make your kittens eat your socks, spoil your milk, etc. You cannot hold me responsible for it. However, feedback will be appreciated, be it positive or negative.
Jan Holst Jensen
jan at biochemfusion.com, April 2009 - latest update July 2009
The steps to follow in creating an Add-in are
Define the Add-in functions in UNO IDL (Interface Definition Language) and compile the IDL to create a UNO type library.
Implement the specified Add-in functions in Python, Java or C++.
Write or generate XML files for the .OXT package.
description.xml - which describes the package (who made it, what is it called, etc.).
manifest.xml - which lists the files that the package consists of.
CaldAddin.xcu - which describes the Add-in functions (function names and their parameters) that should appear in Calc's Function Wizard.
Create the .OXT file - which is just a zip file of all of the above, with an extension of .OXT instead of .ZIP.
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.
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.
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.
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.
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.