<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<head>
	<META HTTP-EQUIV="CONTENT-TYPE" CONTENT="text/html; charset=iso-8859-1">
	<TITLE></TITLE>
	<META NAME="GENERATOR" CONTENT="StarOffice">
	<META NAME="CREATED" CONTENT="20010729;13095251">
	<META NAME="CHANGEDBY" CONTENT="Gunnar Timm">
	<META NAME="CHANGED" CONTENT="20010802;17080864">
</head>
<body>
<H1>How to add-in in OpenOffice.org Calc</H1>
<P>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 <A HREF="http://sc.openoffice.org/">sc.openoffice.org</A>.</P>
<P>A good point to start with an own add-in might be to copy the
explained files and modify all identifier with &#147;Analysis&#148;
to your own needings. E.g. to &#147;MyOwnAddin&#148;. Although this
name is unusual, it will help you to find the important places... ;-)</P>
<P>Another remark: You will often find the use of macros like
&#147;constREFXPS&#148;. 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 &quot;analysisdefs.hxx&quot;.</P>
<H2>What we need to create an add-in</H2>
<UL>
	<LI><P>UDK</P>
	<P>The so called &quot;UNO Development Kit&quot; is necessary to
	deal with the OpenOffice.org API. Descriptions and other important
	things around this theme are located on <A HREF="http://udk.openoffice.org/">http://udk.openoffice.org/</A></P>
	<LI><P>OpenOffice.org Calc</P>
	<P>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.</P>
	<LI><P>Solver Tarball</P>
	<P>The Analysis add-in makes use of a library called &quot;tools&quot;,
	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.</P>
	<LI><P>Appropriate compiler</P>
	<P>Find more infos on which environment to use on homepage of the
	UDK.</P>
	<LI><P>Additional descriptions</P>
	<P>The OpenOffice.org Calc project has got its own homepage on
	<A HREF="http://sc.openoffice.org/">http://sc.openoffice.org/</A>.
	There you will also find a link to description of the add-in service.</P>
</UL>
<H2>Minimum parts to use</H2>
<P>The Analysis add-in mainly consists of the class &quot;AnalysisAddIn&quot;.
To implement this class, I used the following files.</P>
<H3>IDL-file</H3>
<P>This file defines the interfaces. In this case it is called
&#147;analysisadd.idl&#148;. 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 &quot;Analysis&quot;. In the
Analysis add-in we are using three interfaces: XcompatibilityNames
(which will be described in the &#147;Advanced parts to use&#148;),
&#147;XAnalysis&#148; and &quot;XAddin&quot;.</P>
<P>For a further description of the IDL stuff used here, please look
at <A HREF="http://udk.openoffice.org/common/man/concept/unointro.html#UNOIDL">http://udk.openoffice.org/common/man/concept/unointro.html#UNOIDL</A>
and in the context with add-ins
<A HREF="http://api.openoffice.org/docs/common/ref/com/sun/star/sheet/AddIn.html">http://api.openoffice.org/docs/common/ref/com/sun/star/sheet/AddIn.html</A>.</P>
<H3>Header file(s)</H3>
<P>The main header file is &quot;analysis.hxx&quot; which contains
the class &quot;AnalysisAddIn&quot;. 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.</P>
<P>Furthermore, there are several methods which must be implemented
to provide all required functionality for an add-in.</P>
<H4>Constructor</H4>
<P>&quot;AnalysisAddIn()&quot; (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.</P>
<H4>Destructor</H4>
<P>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.</P>
<H4>getProgrammaticFuntionName</H4>
<P>This is not used by OpenOffice.org Calc, but it might be
implemented, when the add-in is used in another context.</P>
<H4>getDisplayFunctionName</H4>
<P>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.</P>
<H4>getFunctionDescription</H4>
<P>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.</P>
<H4>getDisplayArgumentName</H4>
<P>For each parameter you will find a name which occurs in the
parameter list. These strings are requested parameter wise.</P>
<P>One special parameter should be mentioned here: The &quot;constREFXPS&amp;&quot;
(&quot;com::sun::star::beans::XPropertySet&quot; in the IDL file) in
some functions is not visible in the UI. It's a &quot;hidden&quot;
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.</P>
<P>Same with resources applies here.</P>
<H4>getArgumentDescription</H4>
<P>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.</P>
<H4>getProgrammaticCategoryName</H4>
<P>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. &quot;Technical&quot;), 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! ;-)</P>
<P>So this method returns the internal name for the category, which
the function should be assigned to.</P>
<H4>getDisplayCategoryName</H4>
<P>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.</P>
<H4>getCompatibilityNames</H4>
<P>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.</P>
<P>The main goal for the implementation of the Analysis add-in was to
get a better compatibility to Excel. For this reason the
&quot;CompatibilityNames&quot; are chosen in a way, that they are
identically to those which are uses by Excel. These don't need to
match the &quot;DisplayFunctionNames&quot;, 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 &quot;_ADD&quot;
is appended to the name.</P>
<H4>setLocale</H4>
<P>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.</P>
<H4>getLocale</H4>
<P>Gives back, what is set by setLocale before.</P>
<H4>getServiceName</H4>
<P>The name is in this case simply
&quot;com.sun.star.sheet.addin.Analysis&quot;.</P>
<H4>getImplementationName</H4>
<P>&quot;com.sun.star.sheet.addin.AnalysisImpl&quot; is given back
here.</P>
<H4>supportsService</H4>
<P>This method checks, wether a requested service can be supported or
not. The Analysis add-in supports only &quot;com.sun.star.sheet.AddIn&quot;
and &quot;com.sun.star.sheet.addin.Analysis&quot;.</P>
<H4>getSupportedServiceNames</H4>
<P>In the returned sequence of strings are all services listed, which
are supported by the add-in. This is a little bit &quot;double&quot;
to supportsService, but it must be implemented to meet the
requirements of an add-in implementation. The strings are same as
supportsService can check.</P>
<H4>Other methods in the class AnalysisAddIn</H4>
<P>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
<A HREF="http://api.openoffice.org/docs/common/ref/com/sun/star/sheet/AddIn.html">http://api.openoffice.org/docs/common/ref/com/sun/star/sheet/AddIn.html</A>.</P>
<H4>getWorkday</H4>
<P>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 &quot;NullDate&quot;, which is the mentioned base
for all date calculations in Calc.</P>
<P>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.</P>
<P>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.</P>
<H4>getYearfrac</H4>
<P>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. &quot;0.5&quot;
would be displayed as &quot;12:00&quot; when you choose a number
format like &quot;hh:mm&quot;.</P>
<H4>getIseven</H4>
<P>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++.</P>
<H4>getMultinomial</H4>
<P>When a range is used as a parameter, e.g. &quot;A1:D42&quot;, 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).</P>
<H4>What is missing as an example in the Analysis add-in</H4>
<P>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... ;-)</P>
<H4>How errors are returned to Calc</H4>
<P>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.</P>
<P>The RuntimeException is thrown, when the resource manager could
not be initialised.</P>
<P>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 &quot;Illegal Argument&quot; can be shown, although
the add-in functions only use this one.</P>
<H3>Source file(s)</H3>
<P>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.</P>
<P>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 &quot;analysishelper.cxx&quot;. 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.</P>
<P>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 -&gt; 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.</P>
<H2>Advanced parts to use</H2>
<P>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 &lt;-&gt; 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.</P>
<P>When dealing with resources, we need IDs to select the desired one
out of *.res-files. These IDs are located in &quot;analysis.hrc&quot;.
The description of the functions and the name and description of
the parameters is located in &quot;analysis.src&quot;. 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 &quot;AnalysisAddIn::GetFuncDescrStr()&quot;
does when it's called.</P>
<P>The next part of the resources is &quot;analysis_funcnames.src&quot;.
Here are function names located, which are visible in the UI of
OpenOffice.org Calc. Follow &quot;AnalysisAddIn::GetDisplFuncStr()&quot;
to examine the access to these strings.</P>
<P>But the more &quot;tricky&quot; part of the resources is in
&quot;analysis_deffuncnames.src&quot;. 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
&quot;AnalysisAddIn::getCompatibilityNames()&quot; you can see the
usage of these string arrays.</P>
<H2>Building an add-in</H2>
<P>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 &quot;makefile.mk&quot;. As a first step for your own project
you might copy the one from the Analysis add-in and exchange all name
with &quot;Analysis&quot; by the name of your own add-in.</P>
<P>All source files which generate object files (C++/C-source) are
listed in the define for &quot;SLOFILES&quot; e.g.
&quot;$(SLO)$/analysis.obj&quot; for &quot;analysis.cxx&quot;. So
your additional C++ source files will go here.</P>
<P>The right place for resource files is found in the define for
&quot;SRCFILES&quot;. 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 &quot;Resourcen&quot;.</P>
<P>In the definition for &quot;SHL1STDLIBS&quot; you will find at
least all libraries to which the add-in should be linked against.
While &quot;CPPUHELPERLIB&quot;, &quot;CPPULIB&quot;, &quot;VOSLIB&quot;
and &quot;SALLIB&quot; is a good default for your own add-in, the use
of &quot;TOOLSLIB&quot; 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.</P>
<H2>Using an add-in</H2>
<P>When you've successfully created an add-in, Calc should know, that
this exists. So copy the created shared library into the directory
&quot;program&quot; and if you use resources, copy the *.res files
into &quot;program/resource&quot; directory. Then you have to
register this new library with the regcomp tool (wich should come
together with the Solver tarball), e.g. &quot;regcomp -register -r
\program\applicat.rdb -c analysis638mi.dll&quot;. 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.</P>
<H2>Link summary</H2>
<P>
<A HREF="http://sc.openoffice.org/">OpenOffice.org Calc project homepage</A><BR>
<A HREF="http://api.openoffice.org/docs/common/ref/com/sun/star/sheet/AddIn.html">AddIn service API documentation</A><BR>
<A HREF="http://sc.openoffice.org/source/browse/sc/scaddins/source/analysis/">Browsable source code of the Analysis add-in</A><BR>
<A HREF="http://udk.openoffice.org/">UDK project homepage</A><BR>
<A HREF="http://udk.openoffice.org/common/man/concept/unointro.html#UNOIDL">UNO-IDL description (in the UDK project)</A><BR>
</P>
<P><BR><BR>
</P>
<P>
Author: Gunnar Timm (created: 20010802)<BR>
Last change: 20011002
</P>
</body>
</HTML>