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 81.)

PART 2 Keep spreadsheets at arm’s length and work with data from the console

Table of contents

1 OOo Basic Use macros in Calc 2 Remember to be lazy 3 Adding maths to the equation 4 Functions are not subroutines 5 On my command…

OOo Basic Use macros in Calc

From Charles Babbage’s Difference Engine to OOo’s spreadsheet Calc, number crunching was always meant to be automated. Taking some of the pain and monotony out working with columns of data is one of the great reasons for using a spreadsheet in the first place. Thanks to a combination of OOo Basic and Calc, it’s possible not only to automate of the most arduous tasks but, as I’ll show you, to manipulate interesting data directly from the command line.

As with last month, the first step to macro nirvana is creating a document. The code to open a new, blank Writer document is:

sub main
 loadNewFile
end sub
sub loadNewFile
 dim doc as object, desk as object, myFile as string, Dummy()
 myFile = “private:factory/swriter”
 desk = CreateUnoService(“com.sun.star.frame.Desktop”)
 doc = desk.loadComponentFromUrl(myFile,”_blank”,0,Dummy())
end sub

If you look through the code you will see that the type of file to be opened is defined by the line

myFile = “private:factory/swriter”

It’s then just a matter of knowing what to put in instead of swriter. To open a spreadsheet we need to change it to scalc:

myFile = “private:factory/scalc”

Remember to be lazy

Now, I know what you’re thinking – you don’t want a separate subroutine for each file type; you just want a single subroutine to do all the work. Well that’s exactly how a good programmer should think, and here’s how you’d do it:

 sub main
loadNewFile(“scalc”)
end sub
sub loadNewFile (filetype as string)
 dim doc as object, desk as object, myFile as string, Dummy()
 myFile = “private:factory/” & filetype
 desk = CreateUnoService(“com.sun.star.frame.Desktop”)
 doc = desk.loadComponentfromurl(myFile,”_blank”,0,Dummy())
end sub

Notice how we now send the file type to the subroutine, giving us much more flexibility. Most important of all, we only need one subroutine regardless of whether we want to open a Writer document or a Calc spreadsheet. If you want, you can even give it a default file type, by making use of the Optional parameter and the isMissing method:

sub loadNewFile (optional filetype as string)
 if isMissing(filetype) then
  filetype = “scalc”
 end if

OK – now we can open up a blank spreadsheet – but what about writing to one of the cells? This next subroutine will do just that:

sub writeToCell
 dim sheet as object, cell as object
 sheet=thisComponent.sheets(0)
 cell=sheet.getCellByPosition(0,0)
 cell.string=”Hello World”
end sub

You’ll have to remember to run this from the Main subroutine.

It may be worth looking through the writeToCell subroutine just to understand the basics fully. We’ve seen thisComponent before (when we looked at OOo Basic and the Writer document) and it simply refers to the current document (in this case, the spreadsheet) that we’re in. Next we select the sheet that we’re dealing with, which is sheet(0) – the first sheet (or Sheet1) in Calc. Sheet(1) would refer to the second sheet, and so on. Finally we select the cell that we want by using the getCellByPosition method, which requires the column number and row number to be input. Position (0,0) refers to A1, (1,0) to B1, (0,1) to A2 etc. This is great, but the order of your sheets may change; what if you want to refer to them by name?. No problem – instead of the sheets statement use the getByName method:

sheet=thisComponent.sheets.getByName(“Sheet1”)

We’ve seen how easy it is to write text to the document (even easier than it is in Writer), so let’s start using the spreadsheet to do something useful:

sub simple_maths
 dim sheet as object, cell as object
 sheet=thisComponent.sheets.getByName(“Sheet1”)
 cell=sheet.getCellByPosition(0,0)
 cell.value=10
 cell=sheet.getCellByPosition(0,1)
 cell.value=10
 cell=sheet.getCellByPosition(0,2)
 cell.formula=”=A1+A2”
end sub

Admittedly that’s not particularly useful, but it does show you how simple it is to load the spreadsheet with data, and then to manipulate that data. It can be made more useful by allowing the numbers to be input to the subroutine:

sub simple_maths(numbA as double, numbB as double)
 dim sheet as object, cell as object
 sheet=thisComponent.sheets.getByName(“Sheet1”)
 cell=sheet.getCellByPosition(0,0)
 cell.value=numbA
 cell=sheet.getCellByPosition(0,1)
 cell.value=numbB
 cell=sheet.getCellByPosition(0,2)
 cell.formula=”=A1+A2”
end sub

Now you just need to amend the Main subroutine:

simple_maths(12.5,35.7)

This is a very simple example, and it would be quicker to type the details directly into the spreadsheet. However, it’s only meant to be a starting point, and you can start making the operation as complicated as you require. You may also think that only passing two numbers into the subroutine is just too limiting – you may want to pass 10 figures, or 100 or 1,000. Fortunately, it’s very easy to pass an array to a subroutine:

sub main
 loadNewFile
 simple_maths_array(array(45,67,89,34))
end sub
sub simple_maths_array(numbers)
 dim sheet as object, cell as object, r as integer, sum as double
 sheet = thisComponent.sheets.getByName(“Sheet1”)
 sum = 0
 for r = 0 to ubound(numbers)
  sum = sum + numbers(r)
  cell = sheet.getCellByPosition(0,r)
  cell.value = numbers(r)
 next
 cell = sheet.getCellByPosition(0,r+1)
 cell.value = sum
end sub

The simple_maths_array subroutine populates the first column of Sheet1 with the contents of an array of numbers, then inserts the sum of all of the numbers at the bottom. Having written information to a spreadsheet, you may well be asking if it is possible to use data in an existing one. Of course it is – I wouldn’t have mentioned it if it wasn’t. This next subroutine opens an existing spreadsheet (~/test.ods) and displays the contents of cell A1 of Sheet1:

sub dataFromExistingFile
 dim doc as object, desk as object, sheet as object, cell as object
 dim url as string, contents as double, Dummy()
 desk = CreateUnoService(“com.sun.star.frame.Desktop”)
 url=”file://~/test.ods”
 doc=desk.loadComponentfromurl(url,”_blank”,0,Dummy())
 sheet = thisComponent.sheets.getByName(“Sheet1”)
 cell = sheet.getCellByPosition(0,0)
 contents = cell.value
 msgbox(contents)
end sub

A thought may occur to you at this point – what happens if the cell contains text instead of a number? Surely the command contents = cell.value will cause the subroutine to crash. Actually it doesn’t: if the cell contains text, the value parameter is set to zero, thus preventing any such problems.

Adding maths to the equation

Everything we’ve looked at so far is very simple – just reading and writing to cells. How about doing something a little more interesting? How about using the mathematical formulae that are built into OpenOffice.org Calc? Let’s say that instead of just writing an array of numbers to the spreadsheet, we want the total, or the average, or even the standard deviation. We can do this by using the FunctionAccess service:

sub usingOOoFunctions(iArray)
 dim service as object, sheet as object, cell as object
 service = createUnoService( “com.sun.star.sheet.
FunctionAccess” )
 sheet = thisComponent.sheets.getByName(“Sheet1”)
 cell = sheet.getCellByPosition(0,0)
 cell.value = service.callFunction( “STDEV”, iArray )
end sub

As always remember to change Main so that the new subroutine can be run:

usingOOoFunctions(array(45,67,89,34))

I’m sure that you can immediately see a couple of disadvantages with usingOOoFunctions – at the moment it would only be able to calculate standard deviation, it will only use Sheet1, and it will only write to cell A1. However, by the suitable use of input parameters we can make this a very adaptable subroutine:

sub usingOOoFunctions( fType as string, sName as string, _c as integer, r as integer, iArray )
 dim service as object, sheet as object, cell as object
 service = createUnoService( “com.sun.star.sheet.FunctionAccess” )
 sheet = thisComponent.sheets.getByName(sName)
 cell = sheet.getCellByPosition(c,r)
 cell.value = service.callFunction( fType, iArray )
end sub

Modify Main so that it contains:

usingOOoFunctions(“STDEV”,”Sheet1”, 1, 1,
array(45,67,89,34))

This raises an important question – how can you handle results that might cause the program to crash? For instance, you could try

usingOOoFunctions(“SQRT”,”Sheet1”, 1, 1, array(-1))

It’s probably obvious to you that this won’t work, because it calls for the square root of –1, always a no-no. You can try capturing all error-creating situations by writing code such as

if (fType <> “SQRT” and iArray(0) <> -1 ) then 

but this means you’ll have to know every possible combination of function and number that could cause you a problem. The most efficient solution would be to write an error handler. Let’s look at an example (that will crash):

function dummy as double
 dim service as object
 service = createUnoService( “com.sun.star.sheet.FunctionAccess” )
 dummy = service.callFunction( “SQRT”, array(-1) )
end function

Run it with

msgbox (dummy)

This will complain as soon as it gets to the return line, but we can stop that happening by introducing an ‘on error resume next’ statement at the start of the function. If an error occurs this time, the function will just move straight on to the next line of code. However you may (quite rightly) say that you don’t want the code to continue – you want it just to exit neatly. If so, you need to add some code to handle the error appropriately:

function dummy as double
 dim service as object
 on error goto errorFound
 service = createUnoService( “com.sun.star.sheet.FunctionAccess” )
 dummy = service.callFunction( “SQRT”, array(-1) )
 exit function
 errorFound:
 msgbox(“Invalid input. Result set to -1”)
 dummy=-1
end function

Rather than just continuing, the function will jump to the point in the code marked errorFound: – the colon (:) defines it as being a jump destination. Notice that the code contains a line stating exit function just before the error-handling portion. Without this, the error-handling code will always be run even if there is no error – we, of course, only want the error handling to operate if there actually been an error.

Functions are not subroutines

In the examples above we’ve used functions and subroutines. You may be wondering what the difference is between the two. A function and a subroutine are basically the same, except that the function will return a result. This means that when you define a function you must state which data type it is going to return. Here’s a simple example to give you the idea. First we’ll set a variable by using a subroutine:

dim sheet as object, cell as object
sub main
 loadNewFile
 sheet=thisComponent.sheets(0)
 cell=sheet.getCellByPosition(0,0)
 simple_sub
end sub
sub simple_sub
 cell.value = 1
end sub

Next we’ll do the same again, but this time by using a function:

dim sheet as object, cell as object
sub main
 loadNewFile
 sheet=thisComponent.sheets(0)
 cell=sheet.getCellByPosition(0,0)
 cell.value = simple_function
end sub
function simple_function as integer
 simple_function = 1
end function

Notice that the subroutine writes to the cell directly, whereas the function supplies an output that is then used to write to the cell. A second thing to take note of is that some of the variables (sheet and cell) have been made global. This means that they are made available to all of the functions and subroutines. If a variable is defined within a procedure, it only exists for the time that the subroutine or function is running (this is often referred to as the scope of the variable). This is very useful, but it does mean that you have to be very careful when it comes to the naming of variables:

dim sheet_number as integer
dim sheet as object, cell as object
sub main
    loadNewFile
    set_sheetnumber
    sheet= _
    thisComponent.sheets(sheet_number)
    cell=sheet.getCellByPosition(0,0)
    cell.value = sheet_number
end sub
sub set_sheetnumber
    sheet_number = 1
end sub

The number 1 is written to A1 in Sheet2. If we were to insert dim sheet_number as integer into the subroutine set_sheetnumber in the example above, a new variable called sheet_number would be created. This new variable would only be accessible within the set_sheetnumber subroutine. Despite having the same name as the variable in the main subroutine, both variables are different, and can hold different values. Now we can happily read and write to any cell that we want, in any of the sheets within the spreadsheet. This means that we can have a look at the sheet names next. They’re a bit boring as they stand – Sheet1, Sheet2, Sheet3 – and not very informative. And there are only three of them anyway.

sub changeSheetNames
 dim sheet as object
 sheet = thisComponent.createInstance(“com.sun.star.sheet.Spreadsheet”)
 thisComponent.Sheets.insertByName(“MySheet”, Sheet)
 thisComponent.sheets.removebyname(“Sheet1”)
 thisComponent.sheets.removebyname(“Sheet2”)
 thisComponent.sheets.removebyname(“Sheet3”)
end sub

Nice and easy – but still a bit limiting. It can be made really useful by passing in a array containing the sheet names to be created – watch:

 dim i as integer
 for i = 0 to ubound(sheetNames)
  sheet = thisComponent.createInstance(“com.sun.star.sheet.Spreadsheet”)
 thisComponent.Sheets.insertByName(sheetNames(i), Sheet)
 next

On my command…

Finally, we can bring together everything that we’ve looked at in this tutorial (plus some bits from last month). The following code will run shell commands (in this case df and du), save the results to file and load the data into a spreadsheet. Here goes:

const tmpFile as string = “/tmp/myfile.tmp”
const bshFile as string = “/tmp/runme.bsh”
sub main
 theFullWorks
end sub
function buildCommand (ipCommand as string) as string
 buildCommand = “rm -f “ & tmpFile & “;” _
 & ipCommand & “ | sed s/’\t’/’ ‘/g >” & tmpFile & “;” _
 & “while [ “”$(grep ‘ ‘ “ & tmpFile & “)”” != “””” ];” _
 & “do cat “ & tmpFile & “ | sed s/’ ‘/’ ‘/g > “ & tmpFile & “1;” _
 & “mv “ & tmpFile & “1 “ & tmpFile & “;” & “done”
end function
sub theFullWorks
 dim command as string
 loadNewFile
 changeSheetNames (array(“Disk Space Usage”,”File Usage”))
 command = buildCommand(“df|grep -v Filesystem”)
 reportSheet(command,”Disk Space Usage”)
 command = buildCommand(“du /| sort -nr”)
 reportSheet(command,”File Usage”)
end sub
sub reportSheet (command as string, sheetName as string)
 dim sheet as object, cell as object
 dim iNumber As Integer, oNumber As Integer, iLine As String
 dim i as integer, c as integer
 iNumber = Freefile
 oNumber = Freefile
 Open bshFile For output As #oNumber
 print #oNumber,command
 close #oNumber
 shell(“bash -c “”” & bshFile & “”””,,,true)
 i = 1
 sheet=thisComponent.sheets.getByName(sheetName)
 Open tmpFile For Input As #iNumber
 While not EOF(iNumber)
  dim cArray
  Line Input #iNumber, iLine
  cArray = split(iLine)
  for c=0 to ubound(cArray)
   cell=sheet.getCellByPosition(c,i)
   cell.string=cArray(c)
  next
  i = i + 1
 wend
 Close #iNumber
end sub

Most of the code here is quite straightforward, but there are a few places that may look a bit intimidating. For instance, that bit with all the &s. What’s that all about? This is just building up the command that will be sent to the Linux shell. If you want to see what is actually going to be sent, just add a msgbox thus:

Sub main
    dim command as string
    command = buildCommand(“df|grep -v Filesystem”)
    msgbox(command)
end sub

(see the example at the top of the page). The code that we’ve looked at is all fairly simple, but I’m sure that you’ll agree that we can start to do some very powerful things with it.