LibreOffice Calc & Python Programming: part 8 – From Python Tuple to Cells Range

This time we are going to create a new Range of Cells and fill it with the data stored in our tuple created in part 7.

Here is the plan for this tutorial:

  • Start with code from the previous chapter
  • Set the top-left and bottom-right Cells addresses
  • Set the Target Range
  • Create a new Array of data

Start with code from the previous chapter

Starting with the code from the previous episode of this series LibreOffice Calc & Python Programming from its corresponding gitHub rep, or a copy paste here:

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
    
def main():
    
    # call function context()
    context()
    
    # get the range of addresses from selection
    oSelection = model.getCurrentSelection()
    oArea = oSelection.getRangeAddress()

    # store the attribute of CellRangeAddress 
    nLeft = oArea.StartColumn
    nTop = oArea.StartRow
    nRight = oArea.EndColumn
    nBottom = oArea.EndRow
    #(note: could the attribute directly instead of using intermediary variable)
    
    # get the Cell Range 
    oRangeSource = active_sheet.getCellRangeByPosition(nLeft, nTop, nRight, nBottom)
    
    # example by name:
    # ~ oRangeSource = active_sheet.getCellRangeByName('A1:C10')
    
    # get data from the Range of cells and store in a tuple
    oDataSource = oRangeSource.getDataArray()
    
    # print to console
    print(oDataSource)

Set the top-left and bottom-right Cells addresses

This step is to calculate the addresses for our target range of cells, by defining the columns and rows for the top-left and bottom-right cells or our target range, the range where we are going to transfer the data from the selected area.
To avoid having python to throw an error, we need to make sure that our target area is exactly the same size than our source area.
We also don’t want to overlap our source area with our target area.
In this example, let’s define an area starting two columns to the right of our selected area. So the addresses of the cells in the new range will depend on the addresses of cells within the source range.
We can set an offset value that will define how many columns to the right of our selected range will our target range will be.

For example:
offset = 2

Then we set the leftmost column of our new range to be like this:
tLeft = nRight + offset
With nRight being the right column of our range of selected cells.
So here we are simply adding the offset (=2) to the end column of the source range.

Then we need to know the number of columns in the source area:
nbCol = nRight – nLeft + 1
This is the difference between the address of the right column of the source area and the address of the left column area, to which we add 1.
Check the illustration below to understand:

There are 3 three columns in the yellow range starting at number 2, ending at number 4:
(4-2)+1 = 2 + 1 = 3

To remember that logic I always use a metaphor about number of holidays: if I start my holidays the 2nd of the month and my last day of holidays is the 4th of the month, I know I had 3 days of holidays, the 2nd, the 3rd and the 4th. And first day subtracted to last day makes 4 -2 = 2 , and I need to add 1 day to have the right count: 3 days from the 2nd to the 4th.
In the illustration above, the column 2 is the day 2 of the month, the column 4, the day 4…

To set the address of the last column of our target range we do the following:
tRight = nRight + nbCol – 1 + offset
Starting from the last column of the source area, you add the number of columns in that source area, subtract 1, and then add the same offset that you used for the starting column of this new range.
To use the same number of holidays example, to know what are the starting date and ending date of my holidays knowing that I have 3 days off next week, I need to add the number of days off to the starting date and take away 1: 2+3=5, 5-1 = 4 -> the 4th.

We can keep the same rows than the source area for the target area:
tTop = nTop
tBottom = nBottom

Altogether the snippet for setting the columns and rows of our new range is:

    # set the target columns and rows
    # relative to the selected area
    offset = 2
    tLeft = nRight + offset
    nbCol = nRight - nLeft + 1
    tRight = nRight + nbCol - 1 + offset
    tTop = nTop
    tBottom = nBottom

    print(tLeft,tTop,tRight,tBottom)

A simplified version, of tRight, if you replace nbCol by its value, would be:
tRight = 2*nRight – nLeft + offset
In which case you don’t need the nbCol variable anymore, but to make the explanation easier I am keeping it.

As usual, to help with testing, I added a print statement. It will print the value you calculated in the console. You can read my tutorial about the APSO console if you haven’t yet. Also to make testing easier, I usually assign the program to a combination of key, like CTRL + 0. Check my post that explains how to do that easily.

Set the Target range

We are going to use getCellRangeByPosition again to create a range of cells, but this time we are not going to use the addresses of the area defined by the mouse selection. We are now going to use the variables that we created in the previous step tLeft, tTop, tRight, tBottom.

    # create target Range
    oRangeTarget = active_sheet.getCellRangeByPosition(tLeft, tTop, tRight, tBottom)

Now that we have define a cell range, we can assign data to it.

Create a new Array of data

In the last tutorial we used the function getDataArray() to create a python tuple that we named oDataSource from the data stored in the source range of cells. This time we are going to use the function setDataArray() with the existing tuple of data oDataSource as parameter.

This will store the data from the tuple oDataSource to the target Range of Cells that we defined earlier, and hopefully display the data in the cells on the LibreOffice current spreadsheet.

    # set data for the target range using datafrom the source range.
    oRangeTarget.setDataArray(oDataSource)

Here is what I get with a bit of data, including the APSO console:

Simple example

This is the full code:

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
    
def main():
    
    # call function context()
    context()
    
    # get the range of addresses from selection
    oSelection = model.getCurrentSelection()
    oArea = oSelection.getRangeAddress()

    # store the attribute of CellRangeAddress 
    nLeft = oArea.StartColumn
    nTop = oArea.StartRow
    nRight = oArea.EndColumn
    nBottom = oArea.EndRow
    #(note: could the attribute directly instead of using intermediary variable)
    
    # get the Cell Range 
    oRangeSource = active_sheet.getCellRangeByPosition(nLeft, nTop, nRight, nBottom)
    
    # example by name:
    # ~ oRangeSource = active_sheet.getCellRangeByName('A1:C10')
    
    # get data from the Range of cells and store in a tuple
    oDataSource = oRangeSource.getDataArray()
    
    # print to console
    print(oDataSource)
    
    # set the target columns and rows
    # relative to the selected area
    offset = 2
    tLeft = nRight + offset
    nbCol = nRight - nLeft + 1
    tRight = nRight + nbCol - 1 + offset
    tTop = nTop
    tBottom = nBottom
    
    print(tLeft,tTop,tRight,tBottom)
    
    # create target Range
    oRangeTarget = active_sheet.getCellRangeByPosition(tLeft, tTop, tRight, tBottom)
    
    # set data for the target range using data from the source range.
    oRangeTarget.setDataArray(oDataSource)

You can also find this code on this gitHub rep.

The getDataArray() function creates a tuple of tuples. Tuples are immutable objects, so you cannot change them once you have created them.
So if you want to change the data that you stored from a range of cells, you will need to create a new tuple, while looping on the source tuple element by element.
In the next episode of this series of Tutorials about LibreOffice Calc and Python Programming, I will show you some examples on how to work with these tuples of tuples to work on the data.

If you like this tutorial you could buy me a coffee to help me continue writing add-free tutorials.

Thank you!

5 Comments

  1. Nukool Chompuparn

    Sorry for cutting in this chapter, I have a big problem with my LO BASIC project.
    The problem is gotoEndofUsedArea.
    I have reported and asked as follows:
    1. https://ask.libreoffice.org/en/question/257095/basiccalc-gotoendofusedarea/
    2. https://bugs.documentfoundation.org/show_bug.cgi?id=135203
    3. https://forum.openoffice.org/en/forum/viewtopic.php?f=20&t=102683
    4. No. 3 is separated from the old question, https://forum.openoffice.org/en/forum/viewtopic.php?f=20&t=102683

    I have not started learning Python for using with Lo yet. But I would like to ask if it has the same problem in using Python ?

    Reply
    1. gweno (Post author)

      Thank you nukool for your comments. I am really happy to hear from you again! I am looking at your bug reports question and will get back to you regarding gotoEndofUsedArea. I suspect it’d be the same problem when using Python as PyUno is just a binding of LibreOffice Uno environment. However I am sure it is possible to build a python script to do what you want. Thanks

      Reply
  2. karolus

    The Sheet-Object provides the Methods:

    cursor = sheet.createCursor()
    #or:
    cursor = sheet.createCursorByRange( some_cell_range )
    # these cursor-objects are simply CellRanges
    # but with additional Methods to shrink|expand in size:
    cursor.collapseToSize(width_in_columns, height_in_rows)
    # …and to move around relativ to current Area:
    cursor.gotoOffset( number_of_Columns, number_of_Rows )
    # Use it instead your errorprone target-range-calculations via ….getRangeAddress!

    Reply
    1. gweno (Post author)

      Another constructive comment from you karolus, Thanks a lot. I do agree that it would simplify the macro and maybe avoid miscalculation of ranges. However I wanted to illustrate getRangeAddress in combination with getCellRangeByPosition and getCellRangeByName. Now, thanks to you, I am thinking of writing another tutorial using the createCursor and createCursorByRange functions.

      Reply
      1. gweno (Post author)

        Hi karolus. Once again thank you for taking the time to comment my articles, your remarks are clever and helpful. So I will be revamping this tutorial or making an extra one at some point, to simplify the process of managing ranges of cells. Many thanks

        Reply

Leave a Comment

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