LibreOffice

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 in 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. On top of this, each respective style name retrieval function also lists any styles that have names differing from their internal names in the Remarks section of the header.

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: (No error checking is integrated, as we want to give a quick impression of how easy it is to create an AutoIt script with the LibreOffice UDF.)

#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, "Hello World")  ; 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 (3 levels: suite, module and object).

Each filename consists of the string "LibreOffice" the optional module name and the optional object name.
These optional parts of the file name (including description) are listed in the following tables.
To help you decide which files to include in your script, examples are provided in the last table.

Hierarchy of files

Level	Naming Scheme	Example filename	What do you get with this example?	Comment
suite level	LibreOffice.au3	LibreOffice.au3	All functions of all modules of the LibreOffice suite.	Use include files on this 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 cursor movements in Calc and Writer.	Database	Manipulate Base Databases.
DirectFormatting	Manipulate Direct Character and Paragraph formatting in Writer.	Doc	Manipulate documents in Base, Calc and Writer.
Field	Manipulate Calc and Writer document fields.	FootEndNotes	Manipulate Writer Foot/End notes.
Form	Manipulate Base Forms and Writer Forms and Form Controls.	Frame	Manipulate Writer Frames and Frame Styles.
Helper	Manipulate data for use in various functions of the UDF.	Images	Insert and modify Images in Writer.
Internal	Internal functions not to be used by Scripters!	Num	Manipulate Writer Numbering Styles.
Page	Manipulate Writer and Calc Page Styles.	Par	Manipulate Writer Paragraph Styles.
Query	Manipulate Base Queries.	Range	Create, modify, apply Calc Cell Ranges.
Report	Manipulate Base Reports.	Shapes	Create, modify, and insert shapes in Writer.
Sheet	Manipulate Calc document sheets.	SQLStatement	Create, modify and execute SQL Statements in Base.
Table	Manipulate Base Tables and Writer Text-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
Perform a Find and Replace operation in a Calc Document	#Include LibreOfficeCalc_Doc.au3 #Include LibreOfficeCalc_Helper.au3 #Include LibreOfficeCalc_Range.au3 Or simply #Include LibreOfficeCalc.au3
I don't care. I want a single include file that makes sure all functions are available.	#Include LibreOffice.au3

Functions - Naming Scheme

Functions Naming Scheme:	_LO<Module>_<Object><Function>
Example:	_LOWriter_ParObjCopy ("Copies" data selected by the ViewCursor)
Where to find:	LibreOfficeWriter_Par.au3
Description:	<Module> and <Object> can be found in the tables above. <Function> describes the manipulation to be done (read, modify, copy ...)

Example Code

On this page you'll find code examples for the LibreOffice modules Base, Calc, Writer and the Portable LibreOffice version.

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.

Troubleshooting

Debug your Script

To debug your script, as with any AutoIt function, the first place to start is checking for @error, and @extended values after each function call. Each function in this UDF has error, extended and return values documented in the function header.

@error codes

Currently there are 6 types of errors:

• 1. Input errors. This code indicates that the user has input the wrong type of data, or an out-of-bound value.
  Make sure to re-read the parameters and any appropriate remarks for what is expected.
  Example: @error = 1 and @extended = 1 stands for: $sFilePath not a string, or file not found.

• 2. Initialization errors. This code indicates that the UDF failed to create some Object or other necessary item for use in a certain function.
  These types of errors are generally out of the user’s control, and may indicate something has changed with LibreOffice that has caused this UDF to no longer function appropriately.
  Example: @error = 2 and @extended = 1 stands for: Failed to create ServiceManager Object.

• 3. Processing errors. This code indicates that the UDF failed to perform some action.
  Maybe something wrong with the UDF, or with LibreOffice. Double check that any input values are appropriate for what you are doing in the function that is failing.
  Example: @error = 3 and @extended = 1 stands for: No open Libre Office documents found.

• 4. Property Setting errors. This code indicates that the function failed to set the requested property values.
  Which properties have failed will be indicated in the returned @extended value, as a Bit value, this means that to determine what property or properties failed to be set you will need to us BitAND and compare the @extended value with the values listed in the function header.
  Example: @error = 4 and @extended = 10 stands for: Error setting $bReadOnly (2) AND Error setting $bLoadAsTemplate (8).

• 5. Printer errors. This code indicates problems with printers.
  Whether when there were no printers found when retrieving the currently available printer names, or whether there was an issue when setting printer related settings.
  Example: @error = 5 and @extended = 1 stands for: No default printer found.

• 6. Version errors. This code indicates that the current version of LibreOffice is too low for a specific function or parameter.
  Any version limitations are documented in the function or parameter description. Either do not use the function or parameter that requires a higher LibreOffice version, or upgrade to a newer version of LibreOffice.
  Example: @error = 6 and @extended = 1 stands for: Current Libre Office version less than 7.4.

Catching COM Errors

A further step in debugging scripts is to monitor for COM errors, which can be triggered if something fails on the LibreOffice side.
This UDF has user-COM error functions that allow the user to have a custom user function be called each time a COM error is encountered, or optionally, to either use MsgBox or ConsoleWrite to display the COM error details.
Instructions for this are found in each respective COM Error function. Each component has its own dedicated COM user error handler, e.g., LibreOfficeWriter has the _LOWriter_ComError_UserFunction, and also LibreOffice.au3 has its own COM error handler, called: _LO_ComError_UserFunction.
A COM error error handler can display the following information about a COM error: Number, Description, At line, Source, Error Description, though it will not always have all of the values filled in.
If no ComError_UserFunction has been defined by the Skript, LibreOffice does nothing but handle the COM error, so your script does not crash and set @error and @extended.

Example output:

! We intercepted a COM Error
Number: 0x80020006
Description: Unknown name.
At line: 199
Source:
Error Description:

References

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