Excellent file for LO and AOO Base users

If you have ever wanted to understand more about LO or AOO Base.  An excellent resource is:

http://www.pitonyak.org/database/AndrewBase.odt

This shows quite a bit about using the UNO API to do all kinds of wonderful things.

Additionally, LO 4.2 is now out!  You should go check it out.  I highly disagree with the new startup screen and rather have the old one, but I'm pretty sure this new one will grow on me.

Using MRI and a basic spreadsheet macro

Introduction:

I'll assume that MRI is already added to your install of LibreOffice (LO).

That being said, I'll be using LO 4.1 on Mac OS X 10.9, however, you'll find that most of everything covered here is quite true for the Linux or Windows version as well.  However!!  If you by chance do find some difference between what I've got here and what is on your screen, then feel free to drop a comment telling me what you found and what not.

For this post, I am just going to quickly go over the most basic macro possible and tie in MRI along with it.  Our macro will be to quickly create a table that says "Hello, world!" in one column and in the second column have a number counting up from zero to ten.  That is, a total of eleven rows.

Starting up the macro:

The first thing we need to do is start up the macro editor and MRI.  To start up the macro editor, first open a new blank spreadsheet, then from the main menu select:  Tools | Macros | Organize Macros | LibreOffice Basic...   This brings up the macro organizer.

You'll see a box on the left called "Macro From", this indicates where the macro in question is coming from.  You will always have at least three sources here.  The first is your personal macros that are common to anything you open in LO (My Macros), the second is the macros that come with your extensions and the default install of LO, and the third and final location is from an open document (in this case our open spreadsheet).  It is actually possible to have more than three locations to pull macros from as the location list includes all of the currently open files.

We will create today's macro within the spreadsheet, so that when I pass off the spreadsheet, someone else will be able to run the macro.  Usually, you do not want to do this because that can be a security risk, especially if you are not signing your documents before distribution.  However, for example, we will let it pass.  Just click on the "New" button after selecting the spreadsheet from the "Macro From" box.  A dialog will appear to ask you to name the new module, just go ahead and accept the default "Module1".  Finally, the macro editor appears.

Starting up MRI:

I'm going to start up MRI so that I can follow along and do introspection while I'm writing the macro.  To begin MRI I'll need to head to Tools | Add-ons | MRI.

MRI may be a bit confusing at first but it'll make sense in a bit.  Note the box on the top left.  It should state com.sun.star.lang.XComponent.  XComponent is an UNO interface that is used by a lot of stuff.  Remember that MRI, and for that matter everything else that doesn't own the actual spreadsheet document, is looking from outside in.  So XComponent is generic enough to begin getting access to underlying stuff.  MRI doesn't know at this time that what it is looking at is indeed a spreadsheet, hence the XComponent interface.

You'll notice four buttons:  Attributes, Methods, Interfaces, and Services.  For the most part you'll be dealing with Attributes and Interfaces when you write a macro, however, that's not exactly a hard and fast rule and more complex stuff will use all four.

For the time being, go ahead and head over to XComponent's methods.  I'm going to be covering that in just a second.

ThisComponent:

When you start up a macro, you automatically receive at a global scope a value called ThisComponent.  ThisComponent is the component that owns the macro.  However, it is passed to your macro as a generic XComponent interface.  It's up to you to test the XComponent to ensure that it is actually a spreadsheet and what-not (thing called type safety).  However, I'm going to skip all of that because we're doing a simple macro that is going to be traveling with the spreadsheet.  So type safety be damn...

So ThisComponent in macro is equal to our com.star.lang.XComponent that MRI is currently showing.  If we look at the methods for this interface we will see that there is a method called getSheets.  In the second column we will see the call signature which is () or no parameters are expected with this call.  In the third column we can see that calling this method will return .sheets.XSpreadsheets, which as you can tell from the X at the beginning, it's an interface as well.  So calling this method will return us an interface to ALL of the spreadsheets in the document.

Now just to be totally clear, if we were in say Writer and making a macro, we'd see in MRI that ThisComponent would still point to com.star.lang.XComponent, however you wouldn't have a getSheets() method in that because a writer document doesn't have sheets.  Again, that's because XComponent is a generic interface that we use to get more specific things.  If you look at the fourth column in MRI for the getSheets() method, you'll see that the method is actually provided by .sheets.XSpreadsheetDocument.  So within your XComponent MRI found a XSpreadsheetDocument.  When you fire MRI up in writer, you'll notice your XComponent doesn't have a XSpreadsheetDocument within it.

Writing the code:

Time to write the code.  So our XComponent has multiple sheets (at least in theory, right now there's only one sheet in the entire workbook), so when we call the getSheets() method we're going to receive every single one of them.  Because of that, we don't have a lot of functionality exposed to us in the macro.  So we need to focus on just one sheet before we can get more methods to play with our sheet.

In MRI if you double click on the getSheets method name, which will look weird because it's just a text box so its a bit difficult the first time to know if you are doing it right, it'll call that method within the introspection window.  If you now look in the top left window, you'll notice that the current interface that you are inspecting is com.sun.star.sheet.XSpreadsheets.  Notice the getByIndex and getByName methods.  Both take in a parameter and return an any type.  Any type just means that the returning type is depending on how you call it.  Let's double click on the getByIndex, you'll be prompted to provide a value.  Since pretty much everything in UNO is zero based indexes, your first sheet is sheet 0, so push a zero as the int argument for getByIndex.

You will now notice that the interface that you are browsing is com.sun.star.sheet.XSpreadsheet, which is the correct interface for doing whatever it is we want to do with a given spreadsheet.  So when you look at some macros, you'll notice something common, they all pretty much begin with the following.

REM  *****  BASIC  *****

Sub Main

 Dim oSheet
 oSheet = ThisComponent.getSheets().getByIndex(0)

End Sub

That's because the first thing you'll do is get a reference to the spreadsheet that you want to work with.  Once you have that reference you can grab cells, rows, columns, and what not that happen to be on that sheet.  So let's move to our main objective which should now be pretty clear on how to do this.

The macro:

The easiest way of dealing with this is to just grab each cell that we need.  You'll notice a method within .sheet.XSpreadsheet called getCellByPosition(int, int).  We will use this method for the time being to access our cell.  Go ahead and double click on the method, this dialog should appear:

Just enter 0 for both items.  You should now have a .table.XCell interface.  Switch over to the Properties tab (which I know, hasn't had much love so far) and you can see all the fun things that you can mess with for the XCell interface.  You'll notice that in the fifth column you can see if the Property has been marked as ReadOnly, WriteOnly, or blank which usually means Read/Write, but not always.

Now if you are coming from an Excel background, I can already hear you, "Oh!  I know, we pass a string in via the .Value property to set the value of the cell."  Nope, wrong.  UNO makes a distinction between numbers and strings.  The Value property is what you will use if you want to explicitly state that the cell is a numeric value.  The String property is what you will use if you want to explicitly state that the cell is a text value.  This is also a Formula property which does exactly what you think it does.

So with that out of the way now, let's look at our Macro's code:

REM  *****  BASIC  *****

Sub Main

 Dim oSheet
 oSheet = ThisComponent.getSheets().getByIndex(0)

 For cRow = 0 to 10
  oSheet.getCellByPosition(0, cRow).String = "Hello, World!"
  oSheet.getCellByPosition(1, cRow).Value = cRow
 Next cRow

End Sub

As you can see, I used the String property to state that I'm passing a String and Value to state that I'm passing a number. Had I used the String property to pass in a number, it would treat the number as if it were a string, which might come in handy if you want that to happen.  Also notice that I didn't "Dim cRow".  Basic is pretty loose as a language, and while you should always Dim your variables, I've not done it here simply because I want to show that you don't really have to.

Press the F5 key while in the macro editor or click on the run macro button and voila, our desired results are produced.

Conclusion:

I hope this shows you the basics of using MRI and the macro editor together to get the results that you want out of the UNO framework that underpins LO and AOO.  MRI isn't a panacea for all the ills one might have with developing macros within AOO or LO, but it will help you understand the layout of the API if you get lost along the way.

Eventually, you'll want to pull in interfaces and create concrete objects from UNO that aren't directly connected to ThisComponent.  Unless you know the classname of what it is that you want and how to directly build it, MRI won't help you out much.

Any questions, comments, rants, wishes, or hating, feel free to do so in the comments below.  I'll stop by each weekend to check if anything has changed.

Getting started with using Apache OpenOffice/LibreOffice Basic (Installing MRI)

Introduction:

While I think macros with spreadsheets are typically a bad idea, every once and a while you will find yourself writing one.  Either to format a big spreadsheet quickly, change calculations, or add a bit of magic to your spreadsheet.  However, one of the biggest downsides to using LibreOffice (LO) or Apache OpenOffice (AOO) is that the libraries included with these products are insanely more confusing to new users than say Microsoft's offering in their Office suite.  Especially true if you are coming from a hard core background in MS Office, say like a power user of Excel or Access.

There are a ton of projects that are on-going to make LO and AOO, which both use the Universal Network Objects (UNO) framework to provide pretty much all of the functionality within the multitude of components within those suites, slightly easier to deal with.  One of those is MRI which is a plug-in for LO and AOO that allows you to do UNO introspection.  This allows you to browse easily the family of available UNO methods, objects, properties, and so forth.  MRI is an indispensable first tool when doing UNO macro programming.

The download:

So you may ask, where can I get this MRI?!  Well here's where you can get the latest version.  The AOO site has the latest and greatest whereas the LO site only has version 1.1.2 stable and 1.1.4 pre-release, both of which only work with LO 3.x series.

Grab MRI from the AOO Extensions site here!

The nice thing about both AOO and LO both using UNO is that a lot of extensions for one will work on the other.  The downside is that because AOO and LO both use different versioning and they are slowly making their versions of UNO incompatible with each other (not out of spite but AOO is okay with Java depends and LO isn't, so LO is ditching a lot of interfaces that deal with that), their isn't a sure fire way to be absolutely sure that what works for AOO will work for LO and vice-versa.

Luckily, MRI works on AOO and LO versions 4.  That includes LO 4.1, but do note that I have not tested MRI 1.2.1 on LO 4.2 which is the next release.  There are quite a few changes in UNO in LO 4.2 so it will be interesting to see how MRI 1.2.1 works on that.

Install:

So once you download MRI, you'll need to add the extension to LO.  To do that, head over to Tools | Extension Manager in the main menu.  A dialog will appear

Extension Manager dialog

In the dialog click the "Add..." button.  This will open up a file browser.  From the file browser, find the MRI extension that you downloaded.  Once the extension is loaded, you'll need to restart LO to see the changes.

Double Check...

To double check that MRI was successful with the install.  Head over to the Tools menu again and you should see an option called "Add-Ons".  You should see in that menu "MRI" and "MRI ← selection".  The difference between the two is that "MRI" executes the extension with the current document as the parent object, the other uses whatever is currently selected as the parent object.

Make sure MRI is installed

Final:

With MRI you will now be able to perform introspection on the UNO tree, which as you will see soon enough, is pretty complex.  MRI allows one of the most important functions that the MS Office suite gets by default in its Basic environment, an object browser.  I will be referencing MRI a lot in the next few posts as I go over a bit of AOO/LO Basic.  So this is a good first step with learning the UNO basic environment.