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.