Automating OpenOffice.org - Part 1

From LXF Wiki

AUTOMATING OPENOFFICE.ORG BASIC SCRIPTING SERIES

Part 1: OOo Basic: Use macros in Writer
Part 2: OOo Basic: Use macros in Calc
Part 3: OOo Basic: Query databases

(Original version written by Mark Bain for LXF issue 80.)


Recently it’s felt like another day, another big contract won for OpenOffice.org. And that’s something that should make us all very happy: the OOo office suite is one of open source’s poster children, and the more people that use it, the more people are exposed to free software. What most of these new converts won’t realise, however, is that just below the surface there is a powerful programming language built in to OpenOffice.org – and in this new series we, the lucky ones, are going to learn how to use it. The language is OpenOffice.org Basic, and in this installment we’ll apply it to the word processing part of the suite, Writer.

Table of contents

The basics of Basic

You may well ask why a user of OOo Writer should be interested in programming. The answer is simple: automation. Imagine that you have to produce a report every day, and in the report you need to include disk space usage, or a list of logged-on users. Not a difficult job by any means (you could just use who and df, then copy and paste the results into your Writer document).

XXXX.png (http://www.linuxformat.co.uk/images/wiki/XXXX.png)
Access macros, dialog boxes and libraries through the Macro Organizer

However, it’s boring and time-consuming – five minutes here, five minutes there. Wouldn’t it be better to have Writer do the work for you, then get down the pub a bit quicker?

As you’ll find in this tutorial, using OpenOffice.org Basic we can write macros to automate all sorts of tasks, from opening Writer documents and inserting external data to creating a dialog box, working with dynamic data and beyond. For the tutorial I’ve used OpenOffice.org 2.0 (1.9.79) on Linux and version 1.1.4 on Windows (sorry – I just wanted to see how well it would work).

XXXX.png (http://www.linuxformat.co.uk/images/wiki/XXXX.png)
The OpenOffice.org desktop contains a Basic code editor


There are many similarities between OpenOffice.org Basic and every other ‘Basic’ out there. I first used Basic on a Sinclair ZX81 in the early eighties. Now there are other implementations of Basic all over the place – Visual Basic and Gambas to name but two. All of them have the same command structure; it’s really just a matter of learning each one’s peculiarities. But remember that when I talk about Basic from now on I only mean OpenOffice.org Basic. Don’t expect to be able to take code from the examples and have it work in every other implementation of Basic.

The Macro Organiser

The OOo Macro Organizer is your way of accessing and maintaining macros, dialogs and even libraries. You can create new items, edit them and delete old ones if you need. If you are very brave (or maybe that’s foolhardy) you can modify OOo’s built-in macros. The way that you access the Organizer will depend on the version of OpenOffice.org that you’re using. In version 1.1.4 you need to click on Tools, then Macros > Macro... > Organizer. If you’re using version 2.0 you’ll need Tools > Macros > Organize Macros, OpenOffice.org Basic > Organizer.

You will naturally want me to discuss functions and subroutines, variables and objects before doing anything else. No? You just want to get in there and get something to work? OK, here’s a simple piece of code that opens a new blank Writer document. Use the Macro Organizer to create a new module (see The Macro Organizer box), then type in the following code:

Sub Main
 loadNewFile
End Sub
Sub LoadNewFile
 dim doc as object
 dim desk as object
 dim url as string
 dim args()
 desk = CreateUnoService(“com.sun.star.frame.Desktop”)
 url = “private:factory/swriter”
 doc = desk.loadComponentFromUrl(url, “_blank”, 0, args())
End Sub

You can now use the Run BASIC button on the toolbar to see the end result – which is a new document, as promised. From the code above you might be able to deduce how to load any file that you want. The secret is in the URL, such as

url =”file:///home/bainm/test.odt”

Even better, you can build a subroutine that will open either a named file or, failing that, an empty one:

Sub LoadNewFile (optional myFile as string)
 dim doc as object
 dim desk as object
 dim url as string
 dim Dummy()
 if isMissing(myFile) then
  myFile = “private:factory/swriter”
 end if
 desk = CreateUnoService(“com.sun.star.frame.Desktop”)
 url = myFile
 doc = desk.loadComponentfromurl(url,”_blank”,0,Dummy())
End Sub

If you’ve used Basic at all you’ll recognise the general structure – we’ve created two subroutines. The first (Main) is used to control the operation of the macro. The second (loadNewFile) does the actual work. It defines some variables to use (doc, desk, url and args), then creates a UNO (Universal Network Object), which gives you access to the methods and properties of the Writer objects.

Write to a document

A blank file is not particularly useful by itself, and you can create one pretty easily yourself with Ctrl+N. So let’s add a subroutine that will write to the document:

Sub Insert_words
 dim doc as object
 dim cursor as object
 doc=thisComponent
 cursor=doc.text.createTextCursor
 cursor.string=”Hello World”
End Sub

You’ll also need to modify the Main subroutine:

Sub Main
 loadNewFile
 insert_words
End Sub

This shows you how easy it is to use code to control the Writer application. To make most use of this technique you can write a subroutine that accepts an input and writes it to the document as a paragraph:

Sub Add_paragraph (myText as String)
 dim doc as object
 dim cursor as object
 doc=thisComponent
 cursor=doc.text.createTextCursor
 cursor.gotoEnd(False)
 doc.text.insertControlCharacter(cursor, _
 com.sun.star.text.ControlCharacter.PARAGRAPH_BREAK,False)
 doc.text.insertControlCharacter(cursor, _
 com.sun.star.text.ControlCharacter.PARAGRAPH_BREAK,False)
 cursor.string = myText
End Sub

This time the code moves the cursor to the end of the document, creates a new paragraph and inserts any text that you sent it. For instance:

Sub Main
 loadNewFile
 add_paragraph(“This is my first paragraph.”)
 add_paragraph(“This is my second paragraph.”)
End Sub

I know, I know, in this example it would actually be quicker to type the details into a Writer document. However, you’re probably already thinking of potential uses – especially if we now combine this functionality with the ability to load information from external files.

If you’ve used any Basic flavour at all, this next bit is going to be quite familiar to you:

Sub Load_report_file(myFile as String)
 dim filenumber As Integer
 dim iLine As String
 dim pText As String
 filenumber = Freefile
 open myFile For Input As filenumber
 while not EOF(filenumber)
  Line Input #filenumber, iLine
  if (iLine <> “”) then
   pText = PText & iLine
  else
   add_paragraph(pText)
   pText=””
  end if
 wend
 if (PText<>””) then
  add_paragraph(pText)
 end if
 close #filenumber
End Sub

This subroutine takes a filename as an input. It then scans through the file looking for complete paragraphs. If it finds a complete paragraph (it identifies this by an empty line) it will send it to our new document. If not, it goes on looking until it gets to the end of the file. There are just a couple of things that may need clarifying in the code. The first of these is the use of Freefile. The open statement expects you to assign a unique integer to the opened file as a reference number. You could give it your own number… but then have to remember any that you’ve already used (this becomes important if you have more than one file open at a time). Or you could use Freefile, which simply assigns the next from a sequence of numbers. The second thing that you may ask is why there is a second add_paragraph statement outside of the while... wend loop. This is simply to catch any paragraph at the end of a file that is not terminated by an empty line. OK, now you can use this functionality by loading information from any files that you need, ie:

Sub Load_report_simple
 dim rep_dir as String
 rep_dir = “~/articles/lxf75_ooobasic1/demo/”
 load_report_file(rep_dir & “manager_header.txt”)
 load_report_file(rep_dir & “body.txt”)
End Sub

There is still little advantage in building up your document in this way – you could just as easily type the information directly in to your Writer document, then save or print it. However, we can start making the code very useful if we introduce an element of choice:

Sub Load_report (optional reportType as integer)
 const rep_dir as string = “~/articles/lxf75_ooobasic1/demo/”
 if isMissing(reportType) then
  reportType = 1
 end if
 select case reportType
 case 1
  load_report_file(rep_dir & “manager_header.txt”)
 case 2
 load_report_file(rep_dir & “contractor_header.txt”)
 end select
 load_report_file(rep_dir & “body.txt”)
End Sub

Associating Code With Objects

You may be used to languages that automatically associate code and object – Delphi, Kylix or Gambas for instance (or even Visual Basic). However, when using OpenOffice.org Basic you must build the code and the objects separately, then manually associate the code with the object (such as a button). You can do this through the Events tab on the object’s property form (use the right mouse button to click on the object, then select Properties).

This time there are two possible files that can be loaded (manager_header.txt or contractor_header.txt) depending on the value of the reportType variable supplied to the subroutine. However, both cases end with the same file being inserted (body.txt). All you need to do is to change the Main subroutine, with

Sub Main
 loadNewFile
 load_report(1)
End Sub

or

Sub Main
 loadNewFile
 load_report(2)
End Sub

I’m sure that you’ll immediately see a drawback here – you have to modify the code every time that the different reports need to be created. We need some neat way of being able to call the particular files that we need. To do this we are going to build a nice little dialog box to allow us to control the output.

Build a dialog box

You need to use the Macro Organizer to create a dialog box. This time, go to the Dialogs tab before clicking on the New button. If you name the dialog ‘dlgReport’, you can call it with the following code:

dim dlgReport as object
Sub DlgReport_show
 basicLibraries.loadLibrary(“Tools”)
 dlgReport = loadDialog(“Standard”,”dlgReport”)
 dlgReport.execute()
End Sub

You’ll need to change the main subroutine as well:

Sub Main
 dlgReport_show
End Sub

The dialog box won’t actually do anything yet (when you’ve run the code, just press the Escape key to close the box). However, we can now create buttons and list boxes in the dialog box, and then write some code for the necessary functionality that we want.


XXXX.png (http://www.linuxformat.co.uk/images/wiki/XXXX.png)
You can use the Organizer to create new dialog boxes – with a selection of buttons – as well as code.


We’re going to use this dialog box to control the type of report that Writer opens. To do this we’ll need a list box and a button. For the button, select the object that you want from the toolbox, then draw it on to the dialog box. Use the Properties editor to set their names to lstReport and btnReport respectively, and put some useful text on the button (I’d suggest the words ‘Create Report’).

Next we’ll load the list box with details using one of OOo’s built-in methods – addItem. You might immediately jump in and try the following (I know I did):

Sub DlgReport_show
 basicLibraries.loadLibrary(“Tools”)
 dlgReport = loadDialog(“Standard”,”dlgReport”)
 dlgReport.lstReport.AddItem(“Managers”,0)
 dlgReport.lstReport.AddItem(“Contractors”,1)
 dlgReport.execute()
End Sub

Seems logical enough, but that’s not the way that OpenOffice.org Basic does it. Instead you need

dim lstReport as object
sub DlgReport_show
 basicLibraries.loadLibrary(“Tools”)
 dlgReport = loadDialog(“Standard”,”dlgReport”)
 lstReport = dlgReport.getControl(“lstReport”)
 lstReport.AddItem(“Managers”,0)
 lstReport.AddItem(“Contractors”,1)
 dlgReport.execute()
End Sub

Notice that you need to define the list box as a unique object. You can only access it once you’ve used the dialog box’s getControl method. We’ve also defined lstReport as a global parameter – this means that once it has been initiated, it can be used in any subroutine that we write (as we’ll see).

Activate the button

Next we write the code that will run when the Create Report button is clicked. Add this subroutine:

Sub BtnReport_Click
 loadNewFile
 load_report(lstReport.selectedItemPos)
End Sub

No doubt you’ve now run the main macro and found that you’ve got a working combo box, but when you click on the button nothing happens. You probably haven’t associated any code with it yet. Go to the button’s Properties screen, click on the Events tab and select the subroutine that you want to run when the button is clicked (use the Escape key to close the screen when you’ve finished).


XXXX.png (http://www.linuxformat.co.uk/images/wiki/XXXX.png)
Use a button’s Properties screen to associate code to it.


With that done you will have a fully functioning form to control the creation of the two different documents.

Going further

I’m sure you’ll agree that it’s always important to save your work. In all our examples so far we’ve compiled the documents but then needed to save them manually. Since this tutorial is all about automation, we’d better look at using code to save the files. Try this:

Sub SaveMyFile (fileUrl as string)
 dim params()
 doc.saveAsUrl(“file:/” & fileUrl, params())
 doc.close(true)
End Sub

We may, however, just want to print each document and not save it. In this case we could create a subroutine to do that:

Sub PrintMyFile
 dim params()
 doc.print(params())
 doc.close(true)
End Sub

OK, we’ve looked at handling Writer documents and how to extract data from external files. We’ve looked at static files and how to read from them. Excitingly, it is also possible to generate dynamic data by making use of OpenOffice.org’s SystemShellExecute method, thus:

Sub RunCommand (command as string)
 dim svc as object
 svc = createUnoService(“com.sun.star.system.
 SystemShellExecute”)
 svc.execute(command, “”, 0)
End Sub
Sub BtnReport_Click
 const tmpfile as string = “/tmp/myfile.tmp”
 loadNewFile
 load_report(lstReport.getselectedItemPos())
 runCommand(“df > “ & tmpfile)
 load_report_file(tmpfile)
End Sub

Here, the df command is sent as a Linux shell command with its output being saved to a file (in this case /tmp/myfile.tmp). The content of the file is then loaded in to the new Writer document with a result that looks something like

‘Filesystem 1K-blocks Used Available Use% Mounted on
/dev/hda3 3470204 3089264 201816 94% /
/dev/hda4 1510060 1064572 368780 75% /opt
/dev/hda1 4593600 3732708 860892 82% /
WINDOWS.’

Useful though this is, it isn’t particularly attractive or easy to read; the output would look much better in a table. In the Magazine directory on the coverdisc you’ll find the complete code for loading a table with the contents of a file – a skill that can save hours of valuable drinking time. The code is much too long for me to show you here, but if you look through the code on the disc you’ll find some useful Basic functionality such as Chr (which returns an ASCII code for a given integer), Array (which creates an array from a series of strings) and ubound (which returns the maximum index number for an array). Your homework is to look at the btnReport_Click subroutine. Examine the way that the command variable is built up and then sent to Use a button’s Properties screen to associate code to it. the Linux shell.

Quick Tips

  • OpenOffice.org Basic is case-insensitive. Therefore it will recognise myVariable as being the same as myvariable or even MYVARIABLE. It doesn’t matter which one you choose. Just choose one naming convention and stick to it.
  • To close a dialog box just press the Escape key.
  • When you’ve created a button, and written the code for it, don’t forget that you will have to associate the code with the button through the Properties screen.