| <!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 “Analysis” |
| to your own needings. E.g. to “MyOwnAddin”. 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 |
| “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".</P> |
| <H2>What we need to create an add-in</H2> |
| <UL> |
| <LI><P>UDK</P> |
| <P>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 <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 "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.</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 "AnalysisAddIn". |
| 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 |
| “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".</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 "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.</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>"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.</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 "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.</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. "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! ;-)</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 |
| "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.</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 |
| "com.sun.star.sheet.addin.Analysis".</P> |
| <H4>getImplementationName</H4> |
| <P>"com.sun.star.sheet.addin.AnalysisImpl" 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 "com.sun.star.sheet.AddIn" |
| and "com.sun.star.sheet.addin.Analysis".</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 "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.</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 "NullDate", 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. "0.5" |
| would be displayed as "12:00" when you choose a number |
| format like "hh:mm".</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. "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).</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 "Illegal Argument" 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 "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.</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 -> 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 <-> 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 "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.</P> |
| <P>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.</P> |
| <P>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.</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 "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.</P> |
| <P>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.</P> |
| <P>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".</P> |
| <P>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.</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 |
| "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.</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> |
