Search code examples
pythonlibreofficelibreoffice-calc

Python Script in LibreOffice Calc doesn't refresh automatically it's variables

I need to know the number of the last row in my Table to work on it. So, I wrote this function in Python:

def find_last_cell(cursor):
    cursor.gotoEnd()
    last_cell = sheetCalc.getCellRangeByName(cursorCalc.AbsoluteName)
    last_column = last_cell.CellAddress.Column
    last_row = last_cell.CellAddress.Row

    return last_column, last_row

And I wrote this in my "main" function:

def calcula(oEvent):

    column_1, row_1 = find_last_cell(cursorCalc)
    write_Value(column_1, sheetCalc, 2, 28) #Just for debug
    write_Value(row_1, sheetCalc, 2, 29) #Just for debug

    #fill_qtd(row_1)
    #fill_tension(row_1)
    #fill_fp(row_1)


    return 0

Problem:

It works well in the first use: First use

But, when I modify the Table an press the button, it maintains the same results: Second use

Debugs:

  • If I go to File >> Refresh, this error comes out when I press the button again:

com.sun.star.uno.RuntimeException: (Error during invoking function calcula in module file:///C:/Users/34471945882/AppData/Roaming/LibreOffice/4/user/Scripts/python/Calc_Inst.py (<class 'uno.com.sun.star.uno.RuntimeException'>: File "C:\Program Files\LibreOffice\program\pythonscript.py", line 915, in invoke ret = self.func( *args ) File "C:\Users\34471945882\AppData\Roaming\LibreOffice\4\user\Scripts\python\Calc_Inst.py", line 154, in calcula column_1, row_1 = find_last_cell(cursorCalc) #Verifica a última célula ocupada File "C:\Users\34471945882\AppData\Roaming\LibreOffice\4\user\Scripts\python\Calc_Inst.py", line 95, in find_last_cell last_cell = sheetCalc.getCellRangeByName(cursorCalc.AbsoluteName) #Seleciona a última célula ))

  • If I make any modification in my python file (add a comment, for example) and save again, it works. But I need to do this for each modification in table, what makes it unusual for the user.

Question: How can I correct this behavior? I want to use the button for any times without need to refresh or modify the python file?

LibreOffice Version: Version: 7.2.5.2 (x64) / LibreOffice Community Build ID: 499f9727c189e6ef3471021d6132d4c694f357e5 CPU threads: 4; OS: Windows 6.3 Build 9600; UI render: Skia/Raster; VCL: win Locale: pt-BR (pt_BR); UI: pt-BR Calc: CL

Solution

  • Global variables in programming are usually a bad idea, and this is no exception. From the comment:

    If it's global then it will not get the changed value. Initialize those values at the beginning of calcula() instead... The initial part of the code only gets run once, when the python file gets modified, as you have already discovered. It's a good place to declare constants but not variables.