Libreoffice-WIP

This page is still a work in progress.

This article uses material from the Wikipedia article "LibreOffice" and other Wikipedia articles, which are released under the Creative Commons Attribution-Share-Alike License 4.0.

Introduction

This UDF (AU3LibreOffice) aids the user in creating and manipulating LibreOffice Documents.
This wiki article tries to explain basic principles of the UDF and offers tips on how to get started with automating LibreOffice. It also points out some differences between automating the Microsoft Office and LibreOffice suites.
It is not intended to explain LibreOffice's API.
To work with this UDF, you should be familiar with both AutoIt and LibreOffice.

What is LibreOffice?

LibreOffice is a free and open-source office productivity software suite developed by The Document Foundation (TDF).
It was created in 2010 as a fork of OpenOffice.org, itself a successor to StarOffice.
The suite includes applications for

• word processing (Writer),
• spreadsheets (Calc),
• presentations (Impress),
• vector graphics (Draw),
• database management (Base), and
• formula editing (Math).
  

It supports the OpenDocument format and is compatible with other major formats, including those used by Microsoft Office.
Generally speaking, OpenOffice and LibreOffice have the same foundational features, but given the fact that LibreOffice is more actively developed, LibreOffice has more, and newer features available.

Feature comparison: LibreOffice vs Microsoft Office

This page compares the features of LibreOffice and Microsoft Office in several form factors.
The comparisons separate major and minor feature differences and also includes notes on LibreOffice extensions.
The comparison highlights differences and therefore does not display any features which are present in both office suites.

LibreOffice UDF

Since LibreOffice is a fork of OpenOffice, this UDF also works with OpenOffice in many places. However, you should not rely on this, as this UDF was developed with a focus on LibreOffice.
All functions for which a minimum version of LibreOffice is specified are most likely NOT compatible with OpenOffice.

LibreOffice Object Model

The LibreOffice UDF is a wrapper for the complex LibreOffice object model (details can be found on this wiki page). It hides the complexity of the object model and replaces it with a few AutoIt function calls.
The functions provided by the UDF cover most of the functionality that is typically required. However, if you need more complex functions, you will have to write them yourself.
First, you need to familiarize yourself with the LibreOffice API.
The following books will be a valuable help also in this line.

• OpenOffice.org Macros Explained — OOME Fourth Edition
• Useful Macro Information For OpenOffice.org

Technical comparison: LibreOffice vs Microsoft Office

For those users familiar with Microsoft Office and its automation API, we have included some comparisons between the two suites' APIs and internal behaviour on the following page.

Requirements

Currently there are no external requirements needed to run this UDF. You are able to work with both the Installed and also the Portable version of LibreOffice. However do take note of the methods followed to work with Portable LibreOffice?? to make sure it will fit your requirements.

Limitations

• The ability to automate LibreOffice Portable (and OpenOffice) has been added, and should work correctly. However to achieve this, it currently adds some temporary Registry entries to HKEY_CURRENT_USER, unless there is an installed version of LibreOffice or OpenOffice present. See _LO_InitializePortable for more detail.
• This UDF has been written using the English version of LibreOffice, and some aspects may only work for the English version of LibreOffice installations without errors.
• Many functions in this UDF may or may not work with OpenOffice, and some are definitely for LibreOffice only. These unfortunately have not been documented.
• For those using AutoIt versions older than 3.3.16.1 some functions in this UDF use Maps, which will not be recognized as proper syntax in older AutoIt versions.

Special Terminology

Some terminology you will encounter frequently in the UDF, and sometimes in these articles, are the following (in alphabetical order):

Component

Any component or module of LibreOffice. They are: Base, Calc, Draw, Impress, Math, and Writer.

Hundredths of a MM (HMM)

As implied by the name itself, Hundredths of a MM is a hundredth part of a Millimeter, and is the measurement unit used by LibreOffice internally for nearly all widths, heights, distances, etc. For simplicity, this is the unit type used in this UDF as well, unless otherwise stated in the Parameters. A converter function has been supplied with this UDF, found in the “LibreOffice_Helper.au3" file, called _LO_UnitConvert. This function can convert from millimeters, centimeters or inches to HMM, and back again, depending on the flag called.

RGB color Integer

A color unit you will find frequently in this UDF. LibreOffice nearly always uses this type of color value when setting or retrieving a color property. The RGB color integer is a number from 0 to 16,777,215, where each separate number represents a color. The relationship between RGB and a RGB Integer is based on a simple calculation: RGB Integer = Blue x (256 x 256) + Green x 256 + Red, or in Hexadecimal, 0x00RRGGBB, where RR = Red component (0-255), GG = Green component (0-255), BB = Blue component (0-255), and the highest byte (alpha) is unused and usually 0.

Style Names

LibreOffice uses Styles for various things, such as Paragraph or Character styling. LibreOffice automatically translates these style names according to the user's default language currently set, these are called the “Display Name”. To enable Macros, as well as LibreOffice itself to work on any language system these style names are also internally named so that they can remain constant. In the English version of LibreOffice the Display Name and the Internal name, or “Programmatic Name” of every style is most cases the same. There are a handful of exceptions to this however, for example “Default Paragraph Style” is internally named “Standard”, “No Character Style” is also internally called “Standard”, (but is classed as a Character Style, not a Paragraph Style).

To aid the user in recognizing what Styles are which, each respective style name retrieval function (e.g. _LOWriter_ParStylesGetNames) offers the ability to still retrieve the Display name, which will be returned in the same order as the Internal names are returned, allowing the user to easily have a list of each name and see which belong to which. Further than this, as stated above, each respective style name retrieval function also lists any styles that have names differing from their internal names.

UI

User Interface, the visual windows and buttons and tabs the user sees and is used to work with LibreOffice manually.

Installation

Place the entire set of .au3 files of this UDF into a directory searched by SciTE (details can be found in the AutoIt help file for keyword #include).
No further installation or files are required.

Getting started

With the exception of working with Portable LibreOffice (see the Portable LibreOffice section regarding this), the first step will always be to acquire a Document object.
Simply open an existing file, connect to an already opened document or create an entirely new document.
Once a Document object is acquired, you then go forward from there to accomplish the task you desire to do.
Example:

#include "..\LibreOfficeWriter.au3"
Global $oDoc, $oViewCursor
$oDoc = _LOWriter_DocCreate(True, False)                     ; Create a New, visible, Blank Libre Office Document.
$oViewCursor = _LOWriter_DocGetViewCursor($oDoc)             ; Retrieve the document view cursor to insert text with.
_LOWriter_DocInsertString($oDoc, $oViewCursor, "Some text")  ; Insert some text at the ViewCursor.
_LOWriter_DocClose($oDoc, True)                              ; Close and Save the document as .odt document to the desktop

What do I do with all of these files?

The LibreOffice UDF consists of a large number of files. How do you know which to include in your script?
To begin with, the files are arranged in a semi-hierarchical form.

Hierarchy of files

Level	Naming Scheme	Example filename	What do you get with this example?	Comment
suite level	LibreOffice<_Object>.au3	LibreOffice.au3	All functions of all modules of the LibreOffice suite.	Use include files on the suite level only when you need ALL LibreOffice modules in your script.
module level	LibreOffice<Module>.au3	LibreOfficeWriter.au3	All functions of the LibreOffice Writer module.	That's the recommended approach for 99.99% of the scripts.
object level	LibreOffice<Module><_Object>.au3	LibreOfficeWriter_Doc.au3	All functions related to a document object of the Writer module.	Use this level if you need to process a single object of a LibreOffice module.

Valid Modules

Module	Description	Module	Description
Base	Database management program, similar to Microsoft Access.	Calc	Spreadsheet program, similar to Microsoft Excel.
Draw	Vector graphics editor, similar to Microsoft Visio.	Impress	Presentation program, similar to Microsoft PowerPoint.
Math	Formula editor for mathematical expressions.	Writer	Word processor program, similar to Microsoft Word.

Valid Objects

Object	Description	Object	Description
Cell	Manipulate cells in Calc and Writer.	Char	Manipulate character styles in Writer.
Comments	Manipulate cell comments in Calc documents.	Constants	Constants for the respective module.
Cursor	Perform various cursor movements in Calc and Writer.	Database	Provides basic functionality to register, unregister, connect to, etc. Base Databases.
DirectFormatting	Provides basic functionality to manipulate Direct Character and Paragraph formatting in Writer.	Doc	Provides basic functionality to manipulate documents in Base, Calc and Writer.
Field	Provides basic functionality to create, modify, remove, etc. Calc document fields.	FootEndNotes	Provides basic functionality to manipulate Writer Foot/End notes.
Form	Provides basic functionality to manipulate Base Forms and Writer Forms and Form Controls.	Frame	Provides basic functionality to manipulate Writer Frames and Frame Styles.
Helper	Functions to manipulate data for use in various functions of the UDF.	Images	Provides basic functionality to insert and modify Images in Writer.
Internal	Internal functions not to be used by Scripters.	Num	Provides basic functionality to manipulate Writer Numbering Styles.
Page	Provides basic functionality to manipulate Writer and Calc Page Styles.	Par	Provides basic functionality to manipulate Writer Paragraph Styles.
Query	Provides basic functionality to manipulate Base Queries.	Range	Provides basic functionality to create, modify, apply Calc Cell Ranges.
Report	Provides basic functionality to manipulate Base Reports.	Shapes	Provides basic functionality to create, modify, and insert shapes in Writer.
Sheet	Provides basic functionality to manipulate Calc document sheets.	SQLStatement	Functions used to create, modify and execute SQL Statements in Base.
Table	Provides basic functionality to manipulate Base Tables.

Which files to include - Examples

Task	Recommendation
Manipulate an existing Writer document. The script should add some text, modify some formatting.	#Include LibreOfficeWriter.au3
Extract data from Calc and insert into a Writer document.	#Include LibreOfficeCalc.au3 #Include LibreOfficeWriter.au3
Remove a Sheet from an existing Calc document.	#Include LibreOfficeCalc_Doc.au3 #Include LibreOfficeCalc_Sheet.au3
I don't care. I want a single include file that makes sure all functions are available.	#Include LibreOffice.au3

Still missing Naming Scheme of function names

**************************************

This chart illustrates part of the hierarchy, the file “LibreOffice.au3” has Includes in it for all sub-components. (Note: Some components in the chart aren't supported yet.). If you include this file, you can use any of the functions in all other sub-component files, all those listed in the chart. However, if you don't want such a large number of files included in your Script. You can include any sub-component you may need, such as LibreOfficeCalc.au3, or LibreOfficeBase.au3. Whichever component(s) you intend to automate.
Going a step further, consider this chart:

[[File:Chart_2.jpg|800px|||Sub level LibreOffice file]

This chart considers only one component, in this case Writer. LibreOfficeWriter.au3 has many sub-files for the various tasks that can be done in Writer; Cell, Cursor, Doc, etc. More detail about these sub-files will be touched a little later. If you include LibreOfficeWriter.au3 in your script, you can use any of the functions in all the sub-files for Writer. You can identify a sub-file because the name will contain the Component's name, followed by an underscore, in this case, LibreOfficeWriter_*.au3., where “*” is a sub-file category of some type, i.e. Table, Doc or Page.
Lastly, you also have the option to include in your script only some of the sub-files themselves. This may be a better approach depending on how much you need to do. For example, if you only need to Export a document, one could potentially only include LibreOfficeWriter_Doc.au3, which would include all the needed functions for the task, such as *_DocOpen, *_DocExport, etc.
One small tip, you will never need to deal with the files named “*_Internal.au3”, i.e. LibreOfficeWriter_Internal.au3, they contain only functions needed by the various sub-files and are already included in the sub-files where needed.

How Do You Find What is Needed?

Now we have reached the more interesting part, and also the more difficult. Most likely you have a certain task you need to accomplish, but how do you know what files you need for the job? Part of this problem we solved above, you (should) know already what component(s) of LibreOffice you want to automate, be it Calc, Base, or Writer, etc.Thus you know if you'll need to include LibreOfficeCalc.au3, LibreOfficeBase.au3 or LibreOfficeWriter.au3.
The next step is finding the right functions. This is admittedly the harder part to do, especially when you first begin with this UDF. We have attempted to logically sort functions according to their usage as best as possible. The naming of the sub-files is the first clue. Functions dealing with Document related actions, such as opening, closing, saving, maximize, minimize, visible/invisible, undo/redo, are found in the “*_Doc.au3” file. Taking Calc as an example, The LibreOfficeCalc_Sheet.au3 file has functions for adding or deleting Sheets from the document, setting and retrieving which Sheet is currently active, as well as retrieving the Object for the Sheets to use in further operations. There are also files for dealing with Ranges, as well as Cursors and Fields. This should help narrow down which file you will need to look through.
Next, open each sub-file that seems to be related and look at the “; #CURRENT#” header near the top of the script. This will have a list of all the functions contained in the file. With each function we have tried to include in the name a hint of the corresponding tab or section in the LibreOffice UI, if applicable, as well as, of course, the attributes the function will affect or the action it will perform. For example when setting background color or background gradient in L.O., these settings are found under the “Area” tab in most UI pages. The corresponding functions are some of the following: _LOCalc_CommentAreaColor and _LOCalc_CommentAreaGradient. In this case the functions all have to do with modifying a Calc Comment, Area is the Tab name when setting some of these properties, (Background Color or Gradient).
While these tips are generally correct, they aren't a solid rule yet. This UDF has attempted to adhere to this as best as possible, but LibreOffice has a wide variety of similar settings, not always located in identical settings. For example, for Cell background, there is no “Area” tab, thus the background color functions are simply _LOCalc_CellStyleBackColor and _LOCalc_CellBackColor.
Once you find the function you need to use, always check the parameters section in the header, it will aid in indicatating where to find what you need to use to call each parameter, or what kind or where to obtain the kind of Object you need. As an example, consider the following Parameter entries from _LOCalc_CellUnderline,

$oCell - [in/out] an object. A Cell Range or Cell object returned by a previous _LOCalc_RangeGetCellByName, _LOCalc_RangeGetCellByPosition, _LOCalc_RangeColumnGetObjByPosition, _LOCalc_RangeColumnGetObjByName, _LOcalc_RangeRowGetObjByPosition, _LOCalc_SheetGetObjByName, or _LOCalc_SheetGetActive function.

This contains a few important details. First: The parameter $oCell accepts either a Calc Cell Range Object (i.e. a range Object covering A1:B3), Or a single Cell Object (i.e. Cell Object for Cell C3). Then follows a list of functions that would return such Objects that would be acceptable, this list is not always exhaustive when there are many functions that would return a similar acceptable Object or value. However the Object or value will either be named the same, or a note will be in the remarks section of the header indicating what else would be acceptable. Another example from _LOCalc_CellUnderline,

$iUnderLineStyle - [optional] an integer value (0-18). Default is Null. The Underline line style, see constants, $LOC_UNDERLINE_* as defined in LibreOfficeCalc_Constants.au3.

This parameter has a few different and important details. First you will see some numerical values inside of parenthesis, 0-18, I will explain these shortly, you will also see it mentions a certain Constant name, in this case “$LOC_UNDERLINE_*”, this means, of course, that this parameter accepts the Constants of this name as values, the constants will be found in the file LibreOfficeCalc_Constants.au3. If you went to the Constants file, you would see 19 separate Constants to choose from. The values inside of the parenthesis means that this parameter accepts any of those constants that have values between 0 and 18.

$iULColor - [optional] an integer value (-1-16777215). Default is Null. The color of the underline, set in Long integer format. Can be a custom value, or one of the constants, $LO_COLOR_* as defined in LibreOffice_Constants.au3. Set to $LO_COLOR_OFF(-1) for automatic color mode.

This parameter is similar, again some values are found in parenthesis, -1 to 16,777,215. However if you went to the constants indicated, you would see that they only have a few options to choose from, but not an exhaustive list ranging from -1 to 16,777,215. That is why the parameter description indicates you can choose any custom value between -1 and 16,777,215, you can make up your own value as desired as long as it falls between those two values. We will touch another Parameter from another function as a final example.

$iDistance - [optional] an integer value. Default is Null. The distance of the Shadow from the Comment box, set in 1/100th MM.

Considering this Parameter, you will see that there are no parenthesis in this one, this generally means there are no limits, or perhaps only a minimum. If there is a minimum, and it is not mentioned in the parameter, it can be found by looking at the documented Input errors in the header. In this case, $iDistance has the following Input error:

@Error 1 @Extended 4 = $iDistance not an Integer, or less than 0.

That means $iDistance must be an integer, and cannot be less than 0. But there is seemingly no maximum. Or perhaps the maximum varied, which would be noted in the Remarks section, but generally it means it is not present.

A Few More Notes

Throughout this UDF you will encounter functions that are for setting properties, whether it is Styles, Tables, Cells, etc. Usually, you will see that nearly all the parameters will be optional, except for the required Object parameter(s). The optional parameters will have an assigned value of “Null”, which will tell you that, unless indicted otherwise, this function can be used to both set AND retrieve the current property values. You can confirm if this is the case by looking at the Remarks section of the header for a statement similar to the following:

Call this function with only the required parameters (or by calling all other parameters with the Null keyword), to get the current settings.

If you call this function with all optional parameters to Null the return will be an array (except when there is only one optional parameter, in which case the return will be of what ever type the Parameter is, an Integer, a String etc.) If there is more than one optional parameter, the returned array will be a single dimension (unless indicated otherwise), with each element of the array in the order of the optional parameters. For example:

_LOWriter_ParStyleBorderColor(ByRef $oParStyle, $iTop = Null, $iBottom = Null, $iLeft = Null, $iRight = Null)

In this function you have the non-optional parameter, $oParStyle, and four optional parameters, $iTop, $iBottom, $iLeft, and $iRight. If I called this function with only a Paragraph Style Object, it would return the current Paragraph Style Border color values, the returned array would contain the following values: $aArray[0] = $iTop, $aArray[1] = $iBottom, $aArray[2] = $iLeft, and $aArray[3] = $iRight.
Furthermore, any functions that have optional parameters with the “Null” value assigned to them, means that you can also skip any parameters you do not want to set a value for by calling “Null” in the position, nothing will be modified for that property.

Some of the other files

There are some files I have not yet touched upon in these explanations, which we will now examine. Each separate component has a file named *_Helper.au3, there is also a general file called LibreOffice_Helper.au3. These files contain functions that either don't fit under the other categories, or are purely for aiding in other work. For example there is in LibreOffice_Helper.au3 functions such as _LO_UnitConvert, and _LO_VersionGet. _LO_UnitConvert can be used for converting from common units of measurement to the measurement unit used by LibreOffice internally (100th MM). VersionGet is used to determine the current LibreOffice/OpenOffice version being automated. This is beneficial when certain functions or parameters are only available for certain L.O. versions (These limitations will be indicated in any applicable functions, either in the parameter, or the description of the function itself if the whole function is restricted to a certain version.).
Taking a look at LibreOfficeWriter_Helper.au3, we find some functions like the following: _LOWriter_FontsGetNames, and _LOWriter_SearchDescriptorCreate. _LOWriter_FontsGetNames, obviously, retrieves an array of all available Fonts for use in the Document. _LOWriter_SearchDescriptorCreate creates a Search descriptor Object for usage in the Writer Find and Replace functions found in the LibreOfficeWriter_Doc.au3 file.

How LibreOffice Portable is Initialized

Keeping in mind the information mentioned in the differences section, particularly the difference between Microsoft Office using Applications, and LibreOffice using a ServiceManager for Document management. Also how this UDF has chosen to spare the user from having to create the ServiceManager themselves for this UDF, instead creating the ServiceManager in the background, and storing it as a static variable. The exception to this method is when working with LibreOffice Portable. Obviously, the behind the scenes method of using ObjCreate for creating the ServiceManager wont work with portable LibreOffice because the COM server is not registered in the registry, which AutoIt relies upon to create COM Objects.
To work around this, and allow the user to work with Portable LibreOffice, this UDF has a single function found in the LibreOffice_Helper.au3 file, named _LO_InitializePortable. This function is used to point the UDF to the location of the Portable LibreOffice folder. Depending on whether there is an existing installation of LibreOffice or OpenOffice, one of two things occurs:

1. If there currently is not an installed version of either LibreOffice or OpenOffice, and if the path called is valid, a temporary registry entry is added to the HKEY_CURRENT_USER Registry key (See __LO_SetPortableServiceManager for all keys created) mirroring the installed version's entries to allow AutoIt to create an instance of the ServiceManager. Once a ServiceManager has been created, the entries are deleted.
2. If there is already an installed version of LibreOffice or OpenOffice, the ServiceManager is created using the already existing entry, and the registry remains unmodified.The reason this still works for the Portable version will be described next.

From this point, whether the ServiceManager was created using the Portable version or the installed version, the portable instance of LibreOffice found at the called path is started invisibly using some special flags (See __LO_SetPortableServiceManager), causing it to begin listening on a certain port and localhost IP Address. Certain languages, such as C++, and Java are able to create a local instance of the ServiceManager object, and using this local version, are able to connect to this local IP and Port to obtain the COM reference to the started Libreoffice Portable service. Because AutoIt is not able to do this, the above process has been adopted. Using the created ServiceManager, either from an existing install, or by temporarily registering the Portable Version of LibreOffice in the User Registry, I can now use that ServiceManager to connect to the running Portable Version of LibreOffice, and obtain the Portable LibreOffice ServiceManager object from that instance of LO. Once this is obtained, it is then stored as a static variable for future use, eliminating the need to re-create it each time a ServiceManager is needed.

Getting started

With the exception of working with Portable LibreOffice (see the Portable LibreOffice section regarding this), the first step will always be to acquire a Document object. In order to obtain a Document Object, the beginning step is to either open an existing file by providing the file path to the appropriate function, for example an odt or docx file would be opened with Writer, or an ods or xlsx file would be opened by Calc, etc. Alternatively you could also connect to an already opened document instance using one of the “Connect” functions. Or, finally, you may just want to create an entirely new Document using the appropriate “Create” function. The actual process on how to do this will be discussed in more detail for each element in another section.
Once a Document object is acquired, you then go forward from there to accomplish the task you desire to do. We will touch on this shortly, but first it is important to deal with the most probable question on any newcomer's mind,

References

• LibreOffice Developer's Guide: Glossary
• LibreOffice 24.2 SDK API Reference