Optimizing formula copying in PyUno

Question

LibreOffice 5.2.3.3

I'm trying to port an Excel VBScript program to PyUno. The logic works, but it runs much more slowly than it did in Excel.

I made two sheets, Sheet1 and Sheet2. Referencing the below script, I added a button to Sheet1 to call create and one to Sheet2 to call copy. After running create and waiting for it to complete, I run copy.

Is there any way to further optimize copy? When it runs in a separate thread, I can see each row get filled, while I hoped it would be instantaneous to the human eye. Removing the thread just makes the graphics wait to update.

(My original code copies data from an invisible CSV file, which takes even longer for some reason, to the point that it locks up Calc without separate threads. I thought this was going to manifest that problem, but apparently I need another test case. Or maybe it matters that those cells have more text.)

Edit 1: In response to @Jim K's comment: "Separate thread" means an additional function spawns a thread for the business logic, like so:

import threading


def _create():
    # ...
    pass


def create(clickEvent):
    t = threading.Thread(target=_create)
    t.start()


g_exportedScripts = create,

test.py (This is the code in question.)

import msgbox
import os
import uno


def copyFormula(a, b):
    formula = a.getFormula()
    b.setFormula(formula)

    return formula != ''


doc = XSCRIPTCONTEXT.getDocument()


def copy(clickEvent):
    sheet1 = doc.Sheets.getByName('Sheet1')
    sheet2 = doc.Sheets.getByName('Sheet2')

    for y in range(0, 5):
        for x in range(0, 150):
            source = sheet1.getCellByPosition(x, y)
            target = sheet2.getCellByPosition(x, y)
            copyFormula(source, target)


def create(clickEvent):
    sheet1 = doc.Sheets.getByName('Sheet1')
    sheet2 = doc.Sheets.getByName('Sheet2')

    for y in range(0, 5):
        for x in range(0, 150):
            target = sheet1.getCellByPosition(x, y)
            target.setFormula('({}, {})'.format(x, y))


g_exportedScripts = create, copy

Answer 1

Either of these functions should be much faster:

def copy2(clickEvent=None):
    sheet1 = doc.Sheets.getByName('Sheet1')
    sheet2 = doc.Sheets.getByName('Sheet2')
    range1 = sheet1.getCellRangeByPosition(0,0,150,5)
    range2 = sheet2.getCellRangeByPosition(0,0,150,5)
    range2.setDataArray(range1.getDataArray())

def copy3(clickEvent=None):
    sheet1 = doc.Sheets.getByName('Sheet1')
    sheet2 = doc.Sheets.getByName('Sheet2')
    range1 = sheet1.getCellRangeByPosition(0,0,150,5).RangeAddress
    range2 = sheet2.getCellRangeByPosition(0,0,150,5).RangeAddress
    cell2 = sheet2.getCellByPosition(
        range2.StartColumn, range2.StartRow).CellAddress
    sheet1.copyRange(cell2, range1)

Alternatively, use the dispatcher to copy and paste with the clipboard.

See section 5.23 in Andrew Pitonyak's macro document for more information about copying and pasting cells.