LibreOffice Calc & Python: part 9 – Reverse the order of cells content from a cell range

Using the code created in part 8, in this part 9 we are going to manipulate the data with functions getDataArray, setDataArray and Python Tuples. In this first example we are going to reverse the order of data within a cell range.

Here is the plan for this tutorial:

  • Prepare your file
  • Some useful functions
  • How to increment a tuple
  • Try in the console
  • Increment tuple step by step
  • Use built-in functions ‘tuple’ and ‘reversed’ together
  • Full ReverseRange.py file
  • Walk through the code
  • Test the code

Prepare your file

We start with creating a new folder, I named mine ‘part 9’, in the python scripts folder for LibreOffice. Depending on your OS, it will be at a specific location in your user folder. Within that location you can organise your python scripts the way you want and each folder will be seen as a module within the LibreOffice Python Script manager.
This part 9 will re-use some of the code from part 8 (also available on my gitHub repository).

I named the file for this tutorial ‘ReverseRange.py’.

Check my tutorial hello world if you need help to get started with python scripts for LibreOffice.

I usually assign a button or a keyboard shortcut to the main() program of my new python file. This is explained in tutorial Annex 1.

If you are starting from part 8 code, I am not using a context() function anymore to define the global variable needed for pyUNO, but instead I declare them at the global scope straight away. This make things easier and avoid repeating calling context in every function that need the global variables.

So this code below:

def context():
    
    # set global variables for context
    
    global desktop
    global model
    global active_sheet
    
    # 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

Is replaced by:

# set global variables for context
    
# 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

Some useful functions

I also replaced the section that is calculating Cell addresses from the current Range selection, by a simpler function returning a new range with horizontal and vertical offset as arguments. These two arguments having a default value of 0.

def getSelectionAddresses(horizontalOffset = 0 , verticalOffset = 0):
    # get the range of addresses from selection
    oSelection = model.getCurrentSelection()
    oArea = oSelection.getRangeAddress()
    return oArea.StartColumn + horizontalOffset, oArea.StartRow + verticalOffset, oArea.EndColumn + horizontalOffset, oArea.EndRow + verticalOffset

This function returns a tuple(a list you cannot change, also called immutable, see further for more details) with 4 elements. You can indeed create a tuple without using parenthesis.
If you are not convinced, try this in a python console:

>>> fruits = "oranges","bananas","apples"
>>> fruits
('oranges', 'bananas', 'apples')
>>> type(fruits)
<class 'tuple'>

(Don’t type the ‘>>>’, it’s python key prompt!)

The python built-in method type() tells you that you have defined the object ‘fruits‘ as an instance of class ‘tuple‘.

How to increment a tuple

A tuple in python is a special type of array of data to store any type of data. Once a tuple has been created you cannot change its elements individually. A common solution to build a tuple is to use a ‘for loop‘ and to recreate the entire tuple with the same name at each loop iteration. It will look exactly like incrementing a variable, with each loop’s iteration looking like this:

myTuple = myTuple + (newElement,)

The comma at the end within the parenthesis is important, if you don’t have it, the (newElement) is not a tuple but just a single element of whatever the type of newElement is.

Try in the console

So let’s open an APSO console (see part 1 if you need help), or if any other python console like the Python Idle, or even your terminal after starting python3.

Then in the python console of your choice just create two new tuples like this:

source = (1,2,3)
target = ()

You can do a quick print of your two tuple if you want to make sure your 2 tuples are good:

>>> source
(1, 2, 3)
>>> target = ()
>>> ()
()
>>> type(target)
<class 'tuple'>
Note: as you can see you can create an empty tuple simply with empty parenthesis.

Increment tuple step by step

To have our ‘target‘ tuple elements in the exact reverse order than the elements from the ‘source‘ tuple we need to do the following steps:

  1. define target tuple equal to target tuple (which is empty at this step) + last element of source tuple (3)
  2. define target tuple equal to target tuple(as it is) + second last element of source tuple (2)
  3. define target tuple equal to target tuple(as it is) + first element of source tuple (1)

In the console, it looks like this:

>>> target = target + (source[-1],)
>>> target
(3,)
>>> target = target + (source[-2],)
>>> target
(3, 2)
>>> target = target + (source[-3],)
>>> target
(3, 2, 1)

Use the reversed() function

Using a for loop and the reversed() function, we can now define a new function that takes a a tuple as argument and returns the reversed tuple, I called mine reverseTuple():

def reverseTuple(aTuple):
    oReversedSource = ()
    for t in reversed(aTuple): 
        oReversedSource = oReversedSource + (t,)
    return oReversedSource

Use built-in functions ‘tuple()’ and ‘reversed()’ together.

A nicer and more ‘pythonic’ way of doing it, is to use the function tuple() and building the elements using what is called ‘tuple comprehension’ with a loop on the reversed source tuple. This is done in one line only. So in the console it would look like this:

>>> source = (1,2,3)
>>> target = tuple(i for i in reversed(source))
>>> target
(3, 2, 1)
>>> 

So let’s create a reverse function. Now we have two functions doing the same job, just to shoe you that most of the time there are many ways to do the same thing in python and in programming in general. What counts is how readable the code is for you and other people that will read your code.

def reverse(aTuple):
    return tuple(t for t in reversed(aTuple))
Note that you could do without this function and use the tuple comprehension directly where you need to reverse your tuple.

For our final code we’ll keep the reverse function instead of the ReverseTuple, it’ll make people reading your code thinking, “That person knows python!”.

Now that we know how to reverse a tuple with python, we can apply it to a selected range of data within LibreOffice Calc.

Full ReverseRange.py file

# Created by Gwenole Capp 05/05/2021
# For tutolibro.tech
# Public Domain, feel free to copy, modify, use in your own scripts
# 
# email: [email protected]
   
# set global variables for context
    
# 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

# define useful functions

def getSelectionAddresses(horizontalOffset = 0 , verticalOffset = 0):
    # get the range of addresses from selection
    oSelection = model.getCurrentSelection()
    oArea = oSelection.getRangeAddress()
    return oArea.StartColumn + horizontalOffset, oArea.StartRow + verticalOffset, oArea.EndColumn + horizontalOffset, oArea.EndRow + verticalOffset
    
def reverse(aTuple):
    return tuple(t for t in reversed(aTuple))

# main function
def main():
       
    # get the Cell Range
    # use tuple unpacking of getSelectionAddresses returned tuple as parameter of getCellRangeByPosition
    oRangeSource = active_sheet.getCellRangeByPosition(*getSelectionAddresses())
    
    # get data from the Range of cells and store in a tuple
    oDataSource = oRangeSource.getDataArray()
    
    # print to console
    print(oDataSource)
    
    # create a new range of cells
    # from current selection using function getSelectionAddresses with offset
    # you can use the kwarg (keyword argument) horizontalOffset in parameter
    oRangeTarget = active_sheet.getCellRangeByPosition(*getSelectionAddresses(horizontalOffset = 3))
    
    # Reverse the data 
    oReversedSource = reverse(oDataSource)
    # Then set data for the target range using the 'reversed' data from the source range.
    oRangeTarget.setDataArray(oReversedSource)

Walk through the code

The global variables at the start are what allow us to manipulate LibreOffice Objects like the spreadsheet and its cells.
Then the function getSelectionAddresses(), as we have seen earlier is to get the addresses of the selected cells on LibreOffice.
The function reverse() is returning a reversed version of a tuple.

Let’s focus on the main() function.
In the main function, The function getCellRangeByPosition() to get the cells coordinates of the range of cells, takes 4 arguments, and not a tuple with 4 elements. But our function getSelectionAddresses() returns a tuple of 4 elements. So that is why the argument getSelectionAddresses() is preceded with a ‘*’ (asterisk). This mean that we are ‘unpacking‘ the tuple returned by the function getSelectionAddresses() into a sequence of for single elements.
When you create a tuple, you are ‘packing’ it with elements. You can ‘unpack’ it to access all its elements at once. And the asterisk can do it for you!

    # get the Cell Range
    # use tuple unpacking of getSelectionAddresses returned tuple as parameter of getCellRangeByPosition
    oRangeSource = active_sheet.getCellRangeByPosition(*getSelectionAddresses())

Then we are using getDataArray() to get the data within the selected range, we are printing the tuple to the console.

    # get data from the Range of cells and store in a tuple
    oDataSource = oRangeSource.getDataArray()
    
    # print to console
    print(oDataSource)

Next, we create a new range of cells, using again the ‘unpacked’ version of getSelectionAddresses() preceded with an asterix, but this time with the argument ‘horizontalOffset = 3‘, we don’t need to specify the verticalOffset if we want to use its default vaue set to 0 in the function definition. But we have to use the kwarg (the keyword argument) horizontalOffset to make sure we are passing the wanted value to the correct argument.
So in this case I have an offset of 3 cells horizontally, the reversed range of data will be displayed 3 cells on the right of the selected data.
If you wanted to reverse the range ‘in place’ and overwrite the source data, you just need to put no arguments in the code for function getSelectionAddresses().

    # create a new range of cells
    # from current selection using function getSelectionAddresses with offset
    # you can use the kwarg (keyword argument) horizontalOffset in parameter
    oRangeTarget = active_sheet.getCellRangeByPosition(*getSelectionAddresses(horizontalOffset = 3))
Note: there is no exception handling, no safeguard, to check if the offset your enter makes the range being 'out of range'. That could be a further improvement of this program. Another improvement could be to have the offset defined in a cell, or defined using a small control form with a text input.

Finally, we reverse the tuple of data and store it in our new range of Cell addresses in the LibreOffice Calc sheet.

    # Reverse the data 
    oReversedSource = reverse(oDataSource)
    # Then set data for the target range using the 'reversed' data from the source range.
    oRangeTarget.setDataArray(oReversedSource)

You can also find the file with the full code on this gitHub rep.

Test the code

Now it’s time to create a range of data, select that rage and run the python macro!

The selected range is reversed!

You can a look at tutorial Annex 1 to map a combination of key to this macro (I use CTRL + 0).
I hope this tutorial helped you understand a bit more about using python to control LibreOffice Calc. Please leave a comment to let me know!

Leave a Comment

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