Disable screen updating using ScriptForge (python) (or other tips to speed up code)

alo · September 6, 2022, 7:20am

I have a macro loop in Calc written in both Libreoffice Basic, and in python (via Scriptforge). The python code runs much slower, I think because it updates the Calc screen with each calculation, whereas the Basic code disables screen updating by default (it seems to me). It is a big complex spreadsheet and it takes 28 seconds for 100 simulations with LIbreoffice Basic code and and about three times that for Python via Scriptforge doing exactly the same thing. I have pasted my code below. Please tell me how to disable screen updating using Scriptforge (or provide some other tip to speed up the python/Scriptforge code). I am using Libreoffice 7.4, running on Debian.

Edit: My workaround solution is switch to a worksheet that has no values to update. The python code then runs just as fast as the Basic code.

Here is my Basic code:

Sub Simulation

REM copy paste section
i = 0
Do Until i = 100

REM copy configured

oCopyCell = ThisComponent.Sheets.GetByName("Simulation_Config").GetCellRangeByPosition(5,4,31,4)
oPasteCell = ThisComponent.Sheets.GetByName("Output").GetCellRangeByPosition(1,4+i,27,4+i)
oPasteCell.DataArray = oCopyCell.DataArray

REM copy CYOA activation 

oCopyCell = ThisComponent.Sheets.GetByName("Simulation_Config").GetCellRangeByPosition(5,14,31,14)
oPasteCell = ThisComponent.Sheets.GetByName("Output").GetCellRangeByPosition(30,4+i,56,4+i)
oPasteCell.DataArray = oCopyCell.DataArray


REM update counter
oCopyCell = ThisComponent.Sheets.GetByName("Simulation_Config").GetCellRangeByName("C6")
oPasteCell = ThisComponent.Sheets.GetByName("Simulation_Config").GetCellRangeByName("C5")
oPasteCell.DataArray = oCopyCell.DataArray




i = i + 1
Loop

End Sub

Here is my Python code:

from scriptforge import CreateScriptService

def run_simulation(args=None):
    # the following line is necessary to invoke service
    doc = CreateScriptService("Calc")
    i=0
    y=doc.GetValue("Simulation_Config.C4")
    while i < y:
        trackingValues=doc.GetValue("Simulation_Config.F5:AF5")
        targetCell = doc.Offset("Output.B5:AB5",0+i,0)
        doc.SetValue(targetCell, trackingValues)
        nextSim =doc.GetValue("Simulation_Config.C6")
        currentSim=doc.Offset("Simulation_Config.C5",0,0)
        doc.SetValue(currentSim, nextSim)
        i = i+1
g_exportedScripts = (
    run_simulation,
    )

[edit by karolus: “fenced” code with three backticslines]

Villeroy · September 6, 2022, 8:58am

The UNO Api is the same in all languages until you hide it behind an extra layer.
If you would abstain from the scriptforge stuff, you could copy the Basic code into your Python editor, change the block definitions, fix some upper/lower-case issues and run the code.

like this:

def Simulation():
    ThisCoponent = XSCRIPTCONTEXT.getDocument()
    i = 0
    while i < 100:
        oCopyCell = ThisComponent.Sheets.getByName("Simulation_Config").getCellRangeByPosition(5,4,31,4)
        oPasteCell = ThisComponent.Sheets.getByName("Output").getCellRangeByPosition(1,4+i,27,4+i)
        oPasteCell.DataArray = oCopyCell.DataArray
        oCopyCell = ThisComponent.Sheets.getByName("Simulation_Config").getCellRangeByPosition(5,14,31,14)
        oPasteCell = ThisComponent.Sheets.getByName("Output").getCellRangeByPosition(30,4+i,56,4+i)
        oPasteCell.DataArray = oCopyCell.DataArray
        oCopyCell = thisComponent.Sheets.getByName("Simulation_Config").getCellRangeByName("C6")
        oPasteCell = ThisComponent.Sheets.getByName("Simulation_Config").getCellRangeByName("C5")
        oPasteCell.DataArray = oCopyCell.DataArray
        i = i + 1

karolus · September 6, 2022, 9:22am

First of all: As python-programmer you DONT NEED SCRIPTFORGE! NEVER!

to the topic why do you compare complete different codes|methods??

the counterpart to your basic-Simulation written in python is:

def simulation(*_):
    for i in range(101):
        doc = XSCRIPTCONTEXT.getDocument()
        source = doc.Sheets["Simulation_Config"].getCellRangeByPosition(5,4,31,4)
        target = doc.Sheets["Output"].getCellRangeByPosition(1,4+i,27,4+i)
        target.DataArray = source.DataArray
        
        source = doc.Sheets["Simulation_Config"].getCellRangeByPosition(5,14,31,14)
        target = doc.Sheets["Output"].getCellRangeByPosition(30,4+i,56,4+i)
        target.DataArray = source.DataArray
        
        source = doc.Sheets["Simulation_Config"]["C6"]
        target = doc.Sheets["Output"]["C5"]
        target.DataArray = source.DataArray

this code runs in ~3seconds on a small hardware (Raspberry Pi4 8Gb RAM)

rafaelhlima · September 8, 2022, 1:58pm

Your response might give the impression that the user should not use ScriptForge or that it has poor performance. However this is not true. The problem with the original script posted by @alo is that the script is constantly reading and writing directly from the sheet (via GetValue and SetValue calls).

It would be better to process the simulation in memory and in the end update the entire sheet at once. For instance, the ScriptForge version of the script you proposed is:

def simulation2(*_):
    doc = CreateScriptService("Calc")
    # Processing first range
    data_in_range_1 = doc.GetValue("Simulation_Config.F5:AF5")
    # Generate output data in memory
    output_data_1 = list()
    for i in range(101):
        output_data_1.append(data_in_range_1)
    # Insert data into Output sheet
    doc.SetArray("Output.B5", output_data_1)
    # Processing second range
    data_in_range_2 = doc.GetValue("Simulation_Config.F15:AF15")
    # Generate output data in memory
    output_data_2 = list()
    for i in range(101):
        output_data_2.append(data_in_range_2)
    # Insert data into Output sheet
    doc.SetArray("Output.AE5", output_data_2)
    # Copy last cell value
    doc.SetValue("Output.C5", doc.getValue("Simulation_Config.C6"))

Notice the use of SetArray instead of SetValue. The code above runs almost instantaneously on my machine.

All processing could be done inside the for loops and stored in memory. The examples aboves (both yours and mine) have the same output.

It should be noted that ScriptForge is meant to provide a simplified API for script programmers, however it’s still a new library and people are still getting to know how to use it.

Ratslinger · September 8, 2022, 3:43pm

In my opinion it is a waste of time and effort. It is better to learn the API from the start and not waste time on something that requires as much time to just figure out the documentation. Have found cases where is takes more to do something in ScriptForge than when using the API.
.
There are also many things you cannot do with ScriptForge. Why wait for it to maybe never appear. You still need to learn the API, at least in part.

karolus · September 8, 2022, 4:17pm

My response should give the impression that there is no need for ScriptForge especially for python-programmers.
Your optimized? simulation2 but without SF:

def simulation_2_no_sf(*_):
    doc = XSCRIPTCONTEXT.getDocument()
    sheets = doc.Sheets
    source_1 = sheets["Simulation_Config"]["F5:AF5"].DataArray
    source_2 = sheets["Simulation_Config"]["F15:AF15"].DataArray

    out_data1 = [source_1[0]] * 100
    out_data2 = [source_2[0]] * 100

    out_sheet = sheets.Output #or … sheets["Output"]

    cursor = out_sheet.createCursorByRange(out_sheet["B5"])
    cursor.collapseToSize(len(out_data1[0]), len(out_data1))
    cursor.DataArray = out_data1

    cursor = out_sheet.createCursorByRange(out_sheet["AE5"])
    cursor.collapseToSize(len(out_data2[0]), len(out_data2))
    cursor.DataArray = out_data2

    out_sheet["C5"].DataArray = sheets["Simulation_config"]["C6"].DataArray

rafaelhlima · September 12, 2022, 1:26pm

Just to be clear, anything you do with ScriptForge can also be done without it, since in the end of the day SF only uses existing API.

Its purpose is to simplify the API for users who are new to LibreOffice scripting (both in Python and Basic). For instance, your example uses cursors, which are a complex concept for someone just starting with LO scripting. Learning the LibreOffice API is an enormous effort that pays off in the long run. But for those getting started with LO scripting, I’d recommend starting with ScriptForge.

Also, SF simplifies common tasks for the end user, f.i. creating/opening files, filesystem handling, text file handling, dealing with dialogs in Python, just to name a few.

If you know the API it’s a good thing… but not everyone has the time to devote and learn the API. I have gone through that and I can say that the API is quite cryptic in some regards.

Sure… it’s a work in progress. What I do is switch between SF and the native API when needed.

Ratslinger · September 12, 2022, 3:24pm

Do not see any progress. Have seen issues with examples that don’t work (dialogs in Basic come to mind). Can’t see switching between SF & API - this is just going to add to confusing code if others are to look at it. Nope! API not SF for me.

erAck · September 6, 2022, 9:15am

Use XModel::lockControllers() and XModel::unlockControllers().

JohnSUN · September 6, 2022, 10:03am

I’m not sure I understand what exactly your code is simulating, but I know for sure that it won’t require a screen lock. I don’t remember who owns the aphorism “The longer the programmer thinks, the faster the program runs.” Try this code:

Option Explicit 

Sub Simulation
Dim aDataForPaste As Variant 
Dim nCount As Long, i As Long 

Dim oSheets As Variant, iSheet As Variant, oSheet As Variant 
	oSheets = ThisComponent.getSheets()
	iSheet = oSheets.GetByName("Simulation_Config")
	oSheet = oSheets.GetByName("Output")
	
	nCount = iSheet.getCellByPosition(2,3).GetValue()-1 ' Value from "Simulation_Config.C4"
	If nCount < 0 Then
		MsgBox "Wrong value in Simulation_Config.C4"
		End
	EndIf 

	aDataForPaste = iSheet.GetCellRangeByPosition(5,4,31,4).getDataArray()
	ReDim Preserve aDataForPaste(nCount)
	For i = 1 To nCount
		aDataForPaste(i) = aDataForPaste(0)
	Next i
	oSheet.GetCellRangeByPosition(1,4,27,4+nCount).setDataArray(aDataForPaste)

	aDataForPaste = iSheet.GetCellRangeByPosition(5,14,31,14).getDataArray()
	ReDim Preserve aDataForPaste(nCount)
	For i = 1 To nCount
		aDataForPaste(i) = aDataForPaste(0)
	Next i
	oSheet.GetCellRangeByPosition(30,4,56,4+nCount).setDataArray(aDataForPaste)

	iSheet.GetCellRangeByName("C6").setValue(iSheet.GetCellRangeByName("C5").getValue())

End Sub