LibreOffice Calc & Python Programming: Part 3 – the for Loop.

In this episode of my LibreOffice Calc & Python Programming tutorial, we are going to use a simple Python loop to write numbers in a range of cells.

Here is the plan for this part 3 of the tutorial:

  • Create a function with one argument.
  • Use a main() function that calls our first function.
  • The for Loop.

Create a function with one argument

In my last tutorial we learnt to create a LibreOffice Python script to write “Hello World!” in cell A1 of a LibreOffice Document. It would be nice to be able to change the text that we want to display in cell A1.
So to do that we need to:

  1. create a new function, let’s name it ‘write_my_text’.
  2. have a variable that holds the text we want to display, let’s call it ‘my_text’.
  3. set this variable as argument of our new function.
  4. use that argument in this function where we set the text value to be displayed in cell A1.

So first let’s copy our function HelloWorld (see Part 2 Hello World) in a new one and let’s rename that new function “write_my_text”.
Secondly and thirdly we need to replace the argument ‘*args’ by our new argument ‘my_text’:

def write_my_text(my_text):

So our new function called ‘write_my_text’ has a new argument, here is an explanation of what we did:

  • *args : was used to pass information through the LibreOffice button. This argument is a parameter that we don’t really need to understand but that we need to have if we want the button on the LibreOffice sheet to trigger the Python function that it is assigned to.
    Now the button will not call directly our new function ‘write_my_text’ so we don’t want that argument ‘*args’ anymore.
  • my_text: that is our new argument that will replace the text “Hello World” and that we could for example enter manually through a field in a text box (in another tutorial).

At last we need to change now is the line that sets the value for Cell A1 like this:

    # write 'Hello World' in A1
    active_sheet.getCellRangeByName("A1").String = my_text

So the code for function write_my_text is:

def write_my_text(my_text):
    """Write what I want in in Cell A1"""
    
    # get the doc from the scripting context 
    # which is made available to all scripts
    desktop = XSCRIPTCONTEXT.getDesktop()
    model = desktop.getCurrentComponent()
    
    # access the active sheet
    active_sheet = model.CurrentController.ActiveSheet

    # write in A1
    active_sheet.getCellRangeByName("A1").String = my_text

This function is yet not assigned to a button and anyway it would not work if we were trying to execute it directly without giving any value to the argument my_text. So the next step is to create the ‘main’ function that will call our function ‘write_my_text’ and that will pass the argument ‘my_text’ to it. And this argument ‘my_text’ will be the text that we want to be displayed in Cell A1.

Use a main() function that calls our first function.

The function called ‘main’ is used to execute all sub-functions in the order we want, it is the back bone of a program. We place the ‘main’ function’s definition at the bottom of the file and we then will only need to write in the line that will call our ‘write_my_text’ function with the text we want to display in between the bracket, like this:

def main(*args):
    """Our main program, that call other functions in the order we want"""
    
    write_my_text("My first macro in Python!")

So here is the complete code with the ‘main’ function and with the ‘write_my_text’ function:

# Created by Super Busy Daddy 18/09/2019
# This program displays the text 'my_text' that we put as argument
# of function 'write_my_text' in Cell A1
# of a LibreOffice Calc Document.
    
def write_my_text(my_text):
    """Write what I want in Cell A1"""
    
    # get the doc from the scripting context 
    # which is made available to all scripts
    desktop = XSCRIPTCONTEXT.getDesktop()
    model = desktop.getCurrentComponent()
    
    # access the active sheet
    active_sheet = model.CurrentController.ActiveSheet

    # write in A1
    active_sheet.getCellRangeByName("A1").String = my_text

def main(*args):
    """Our main program, that call other functions in the order we want"""
    
    write_my_text("My first macro in Python!")

Now you can execute this code once you have saved it. As seen in my previous tutorial you can assign the program ‘main’ to a LibreOffice Button. That will save you a lot of time as you won’t need anymore to open the Macro organizer to run your function.

I everything goes right, you should get the text “My first macro in Python!” displayed in cell A1.


Let’s now do something a bit more useful like writing in many cells at a time using a loop.

In Python you can use 2 type of loops: the ‘for’ loop and the ‘while’ loop.
For now let’s focus on the for loop.

The for loop.

Let’s say that we want to write number 1 to 10 in cells from A1 to A10 (with 1 in A1, 2 in A2, etc…)
To do so we could add 9 lines in the ‘write_my_text’ function and change only the cell reference like that:

active_sheet.getCellRangeByName("A1").String = my_text
active_sheet.getCellRangeByName("A2").String = my_text
active_sheet.getCellRangeByName("A3").String = my_text
etc...

But that is inefficient and programming macros is to avoid doing these kind of redundant actions.
So instead we are going to use a loop that will do the same thing as many times as we want. There are a few different ways to achieve what we want to do using a loop, the one way I am presenting here does not pretend to be the best but is definitely easy to understand.

The for loop is designed to repeat the same actions for a number of times we want, or to be more precise, from a starting number to an ending number.
A for loop in Python looks like that:

for each_number in range(10):
    #do something

Here ‘each_number’ is a variable that will take values from the range of numbers from 0 to 9. So it stops before 10.
An easy way to write the same text from cell A1 to A10 would be by using another type of cell reference than the one we used in our ‘helloworld’ function (see previous tutorial), that use the numerical index for both columns and rows :
getCellByPosition(index_column, index_row)
So the cell A1 has a numerical index (column,row) of (0,0).
To write in column A from row 1 to 10 we effectively need to write in all cells with column index 0 (zero) and with row index from 0 (zero) to 9.
Our for loop will look like that:

for each_number in range(10):
    active_sheet.getCellByPosition(0, each_number).Value = each_number

When we run the loop above, it will start with each_number = 0, so it will be exactly the same as doing:

active_sheet.getCellByPosition(0,0).String = 0

It means, write the String of text “0” in Cell (0,0) which is Cell A1.

Once this is done, at the next step of the loop we will have each_number = 1, so it will be the same as running:

active_sheet.getCellByPosition(0,1).String = 1

This will write the String of text “1” in Cell (0,1) which is Cell A2.

And so on… But then this means we are not writing numbers from 1 to 10, but numbers from 0 to 9! So we actually should add 1 to our ‘each_number’ value:

for each_number in range(10):
    active_sheet.getCellByPosition(0, each_number).Value = each_number + 1

So let’s create a new function called “write_1_to_10”. But to not overwrite the text written in cell A1 by our function ‘write_my_text’, we could do a loop to write on row B instead of row A, so ‘from B1 to B10’. Using numerical indexes this translates to ‘from cells (1,0) to (1,9)’ and in the Python:

active_sheet.getCellByPosition(1,each_number)

Our function should then look like:

def write_1_to_10():
    
    # get the doc from the scripting context 
    # which is made available to all scripts
    desktop = XSCRIPTCONTEXT.getDesktop()
    model = desktop.getCurrentComponent()
    
    # access the active sheet
    active_sheet = model.CurrentController.ActiveSheet

    
    for each_number in range(10):
        active_sheet.getCellByPosition(1, each_number).String = each_number + 1

As you can see in the code for our function above, we need to set the ‘desktop’, ‘model’ and ‘active_sheet’ objects again. It is exactly the same lines of codes than we have in ‘write_my_text’. But even if the name of the objects are the same, the objects themselves are only define for the function where they belong, we call them ‘local’ objects. They are defined locally in the function, the have a local scope.

It would be more economical to actually set these objects only once in the global scope of our scripts. Defining objects(i.e desktop, model and active_sheet) or variables in the global scope of our scripts means that they are define outside of all our functions and therefore available to all of these functions without to have to define them within the functions.
This will allow us for example to use the object active_sheet within our 2 functions without to redefine it.

To define our 3 objects globally we just need to write them at the top of our file without including them in any function, with no indentation:

# get the doc from the scripting context 
# which is made available to all scripts
desktop = XSCRIPTCONTEXT.getDesktop()
model = desktop.getCurrentComponent()

# access the active sheet
active_sheet = model.CurrentController.ActiveSheet

And now let’s add our ‘write_1_to_10’ function to the list of functions to be executed within the ‘main’ function.

def main(*args):
    """Our main program, that call other functions in the order we want"""
    
    write_my_text("My first macro in Python!")
    write_1_to_10()

Finally, this is the complete code:

# Created by Super Busy Daddy 18/09/2019
# This program displays the text 'my_text' that we put as argument
# of function 'write_my_text' in Cell A1 and 
# write number 1 to 10 in cells B1 to B10
# of a LibreOffice Calc Document.

# get the doc from the scripting context 
# which is made available to all scripts
desktop = XSCRIPTCONTEXT.getDesktop()
model = desktop.getCurrentComponent()

# access the active sheet
active_sheet = model.CurrentController.ActiveSheet

def write_my_text(my_text):
    """Write what I want in in Cell A1"""
    
    # write in A1
    active_sheet.getCellRangeByName("A1").String = my_text

def write_1_to_10():
    """Write numbers 1 to 10 in Cells B1 to 
B10"""
   
    for each_number in range(10):
        active_sheet.getCellByPosition(1, each_number).String = each_number + 1

def main(*args):
    """Our main program, that call other functions in the order we want"""
    
    write_my_text("My first macro in Python!")
    write_1_to_10()

And to practice you could try to write in diagonal (in cell A1, B2, C3, etc…) or even combine ‘write_my_text’ with a loop to display something like ‘hello 1′, hello 2’, from cells A1 to J1. The best way to learn is to experiment!

Enjoy and let me know if it works!

2 Comments

  1. Nukool Chompuparn

    This is one of the best tutorials of flow controls.

    Reply
    1. gweno (Post author)

      Thank you Nukool

      Reply

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.