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

Is LibreOffice trying to redraw the GUI anyway? I'm wondering if there might be something similar to "freeze" and "thaw" in wxpython where you tell the GUI "stop drawing until I tell you to do so" — alex314159, Dec 20 '16 at 23:41
I ran this code on a fast machine, and each function took less than one second. On a slow machine (about 11 years old), it took about 4 seconds. Is that what you found, or did it run much slower on your machine? Also I didn't understand what you meant by running in a separate thread. Does this mean something different from just pressing the button on each sheet? Note: If LibreOffice is opened in [listening mode](https://wiki.openoffice.org/wiki/Documentation/DevGuide/ProUNO/Starting_OpenOffice.org_in_Listening_Mode), it will run much slower. — Jim K, Dec 21 '16 at 00:04
@alex314159: yes, there is `doc.lockControllers()` and `doc.unlockControllers()` [https://www.openoffice.org/api/docs/common/ref/com/sun/star/frame/XModel.html] — ngulam, Dec 21 '16 at 00:09
@Grault: After the edit, the threading part of the question is clearer; thanks. — Jim K, Dec 21 '16 at 01:11
@JimK ~1 second is what I get for the above code - enough time for me to watch it, but not as much as my application code. I applied the first technique in your answer and got enough speed for my purposes. I'm a little embarrassed that the function I needed was right next to the one I was using in the docs. — Grault, Dec 21 '16 at 17:39

score 1 · Accepted Answer · answered Dec 21 '16 at 01:01

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.

Thanks, I'll take a look at this tomorrow. Also, I don't think my reference to you worked, so see my edit to the question. — Grault, Dec 21 '16 at 01:05

Optimizing formula copying in PyUno

1 Answers1