How to create interactive objects in Calc worksheet using Python

[Roman001]

Hi, All. I need to create buttons, listboxes… and other interactive objects on my Calc worksheet. And then assign macroses for further actions. I mean create from macro, not from Calc interface. Please help me how to do this with Python macro.

[elmau]

Do you want to add a dialog box with controls?

Or do you want to add controls within the spreadsheet?

[Roman001]

@elmau

Or do you want to add controls within the spreadsheet?

Yes, I want to add controls (buttons, drop dowl lists) to the spreadsheet

[elmau]

ok, you need a form control. I’ll prepared an example for you.

[fpy]

Add controls to a Dialog With A Python Macro

Hello, Dialogs and macros inserting controls is a broad topic. In this post is an example → How to get Active Tab/Page ID from a python dialog box? Here is a link to documentation → UnoControl Service Reference

[Roman001]

Thanks for docs. It’s pitty, but links for the examples to git are dead, it would be great to see an working example)

[elmau]

Test this code, execute macro: example_add_control_to_sheet

import uno


from com.sun.star.awt import Size, Point
from com.sun.star.script import ScriptEventDescriptor


def _get_form(doc, draw_page):
    forms = draw_page.Forms
    if forms.Count:
        form = forms[0]
    else:
        form = doc.createInstance('com.sun.star.form.component.Form')
        forms.insertByName('MyForm', form)
    return form


def _add_control(doc, form, draw_page, type_control, properties):
    MODELS = {
        'label': 'com.sun.star.form.component.FixedText',
        'text': 'com.sun.star.form.component.TextField',
        'button': 'com.sun.star.form.component.CommandButton',
    }

    w = properties.pop('Width', 5000)
    h = properties.pop('Height', 1000)
    x = properties.pop('X', 100)
    y = properties.pop('Y', 100)

    control = doc.createInstance('com.sun.star.drawing.ControlShape')
    control.setSize(Size(w, h))
    control.setPosition(Point(x, y))
    model = doc.createInstance(MODELS[type_control])
    for k, v in properties.items():
        setattr(model, k, v)
    control.Control = model
    index = form.Count
    form.insertByIndex(index, model)
    draw_page.add(control)

    return index


def _add_event(form, index, control_name, control_event):
    EVENTS = {
        'action': 'actionPerformed',
        'click': 'mousePressed',
    }
    TYPES = {
        'actionPerformed': 'XActionListener',
        'mousePressed': 'XMouseListener',
    }

    url = 'vnd.sun.star.script'
    name = f'{control_name}_{control_event}'
    url = f'{url}:test.py${name}?language=Python&location=user'

    event = ScriptEventDescriptor()
    event.AddListenerParam = ''
    event.EventMethod = EVENTS[control_event]
    event.ListenerType = TYPES[event.EventMethod]
    event.ScriptCode = url
    event.ScriptType = 'Script'

    form.registerScriptEvent(index, event)
    return


def example_add_control_to_sheet():
    doc = XSCRIPTCONTEXT.getDocument()
    sheet = doc.CurrentController.ActiveSheet
    dp = sheet.DrawPage
    form = _get_form(doc, dp)
    properties = {
        'Name': 'cmd_test',
        'Label': 'Click Me',
    }
    index = _add_control(doc, form, dp, 'button', properties)
    _add_event(form, index, 'cmd_test', 'click')
    return


def cmd_test_click(event):
    print(event.Source.Model.Name)
    return

[Roman001]

Oh, thanks a lot! It works, but button click. When I click I see the error

[Roman001]

As I see the problem in the picture. Dona why but script registered event “test.py$cmd_test_click(user,Python)” where test.py is uncorrect file name. If I manualy choose in Properties correct file and macros is works. So I think I need to add file name while event register?

[Roman001]

ok, I’ve done, thanks a lot!