Unable to run the simpliest python macro for Calc udf

English
,
#1

Hi
I’ve just started writing a python macro, here is how it looks now:

import uno
def main():
    doc = XSCRIPTCONTEXT.getDocument()
    sheet = doc.Sheets.getByName("Sheet1")
    return None

In the end, I need this to be a UDF function, so I also have Basic macro to invoke it:

Sub invoker()
Dim oScriptProvider, oScript
oScriptProvider = ThisComponent.getScriptProvider("")
oScript = oScriptProvider.getScript("vnd.sun.star.script:py.py$main?language=Python&location=user") 
oScript.invoke(Array(), Array(), Array())
End Sub

Yet it’s already giving me error:

image

What is wrong here?

#2

Hallo

  1. the Errormessage states AttributeError :Sheets - it seems there is no Spreadsheet_document in Focus ( maybe the Basic-IDE , or a MRI-Dialog or XRay-Dialog instead … none of them has Attribute: Sheets

  2. your main-function returns explicitly »None« … why do you assume thats the sheet-object occurs by magic into the basic-sub??

  3. the Basic-code itself is a sub BUT NOT a function with the optional property to return something.

  4. Beside all the previous points, it will never happen to pass the sheet-object back to the calc-function-call, because its not in the allowed set of simple Data-Types for Basic-UDFs (which are String’s, Doubles or array of array[s] (which entries of the mentioned Types))

#5

Thank you for your answer.
I’m a complete novice in LibreOffice, so, could you please, elaborate a bit more? In both Basic and Python code, I don’t need any data or object to return, only to call Python and it will be the end of program. What is the benefit of creating a return? If you could refer me good code for Calc’s Python, it would be awesome.
I saw Python code for add-ins, however, it only use Class to make program communicate with Calc. Should I also use Class in Python instead of Functions?

#6

Unable to run the simpliest python macro for Calc udf

It is not possible to implement any user defined function by means of macro code other than StarBasic. A Python function for Calc needs to be implemented as an add-in wrapped into an extension package. That StarBasic wrapper is a dirty hack.
https://api.libreoffice.org/docs/idl/ref/servicecom_1_1sun_1_1star_1_1sheet_1_1AddIn.html
https://forum.openoffice.org/en/forum/viewtopic.php?t=83856

#9

Even when using the dirty hack with a Basic UDF calling a Python function, the Python function should not work with UNO objects. Spreadsheet functions take simple values and they return simple values. Whenever a user defined function makes use of some active document, active sheet or selection, failure hangs in the air.

1 Like
#10

again, what exactly do you want to do?

#3

Replace

sheet = doc.Sheets.getByName("Sheet1")

with:

sheet = doc.Sheets['Sheet1']

but… What do you really want to do?

#4

@elmau : of course the »dictionary-like-access« provided by pyuno since ~ LO5.? should be preferred … but both works the same way, so that isn’t a issue here.

#11

Is that documented anywhere?

#12

Hallo
https://wiki.documentfoundation.org/ReleaseNotes/5.1

and https://cgit.freedesktop.org/libreoffice/core/commit/?id=af8143bc40cf2cfbc12e77c9bb7de01b655f7b30

1 Like
#7

The problem is that in Python, there is no convenience like Basic’s ThisComponent. The XSCRIPTCONTEXT.getDocument() returns you the actually active component, which - in case of running the macro from the Basic IDE - is Basic IDE. ThisComponent in Basic would give you a reference to the last active component, other than Basic IDE, for your convenience…

Try running the macro from Calc, using Tools → Macros → Run Macro.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.