Could anyone give a simple but generally useful template for python code to be embedded into LibreOffice Calc?

Question

Update acknowledging comment 1 and 4: Indentation corrected, does compile in Geany, obvious redundancies stripped.

The first question is: why am I getting the args error when I try to run my code? The error message refers to a LO file.... (linux) /opt/libreoffice7.2/programs/pythonscript.py @ line 915. An excerpt from that file says...

    def invoke(self, args, out, outindex ):                # 910
        log.debug( "PythonScript.invoke " + str( args ) )  # 911
        try:                                               # 912
            if (self.args):                                # 913
                args += self.args                          # 915
            ret = self.func( *args )                       # 915

My code is...

# -*- coding: UTF-8 -*-
from __future__ import unicode_literals

# Gets the current document
doc = XSCRIPTCONTEXT.getDocument()
ctx = XSCRIPTCONTEXT.getComponentContext()
sm = CTX.ServiceManager

import time
import datetime
import serial
import openpyxl


def PySerLO (args=None):
    from datetime import date
    from openpyxl import load_workbook
    ser = serial.Serial('/dev/ttyUSB0') # ""ls /dev/ttyUSB* -l"" if unsure. 

# Load the workbook and select the sheet...
    wb = load_workbook('/media/mpa/UserFiles/LibreOffice/mpaNEW-spreadsheet.xlsx')
    sheet = wb['Sheet1']
    headings = ("Date", "Time", "Raw Data")
    sheet.append(headings)
    
    try:
        while True:
            # Get current date, Get current time, Read the serial port
            today = date.today()
            now = datetime.datetime.now().time()
            data = ser.readline().decode()
            if data != "":
                row = (today, now, float((data)))   # For some reason the first data point is garbage so don't do anything with this
                row = (today, ("%s"%now), float((data)))
                sheet.append(row)
                ser.flush()
                #Save the workbook to preserve data 
                wb.save('/media/mpa/UserFiles/LibreOffice/mpaNEW-spreadsheet.xlsx')

    finally:
        # Make sure the workbook is saved at end
        wb.save('/media/mpa/UserFiles/LibreOffice/mpaNEW-spreadsheet.xlsx')
        print('Data Finished')

I get this error on executing a macro executing command button...

I appreciate your patience and suggestions on improvement thus far.

The ultimate aim of this is to track and instantly plot scientific data, introduce my students to the power of LibreOffice - They download LO and I just pass them the fully contained spreadsheet. They then connect the hardware and they have an excellent resource on LO forever. Almost none of them have ever heard of LibreOffice (or Linux either!)

Answer 1

First of all, I do not think you should start by embedding the macro in the document. I don't think it is required for the reasons you seem to believe. Instead, put your code in My Macros (the LibreOffice user directory) where it is easier to develop. Then call it from a button or by going to Tools > Macros > Run Macro - no need to use APSO as it seems like that is causing you even more confusion. The only reason for embedding is for convenience, to make it easier to keep the document and a bit of code together. For complex code that needs to be given to multiple people, it's generally better to create a full-fledged extension.

Second, it looks like the error may be from M1.py line 22 - is this your code? I cannot tell where line 22 might be or where exactly the error is coming from. Most of us reading your question are looking for the code where the error message comes from, but we cannot find it because you did not provide the relevant file name or line number of your code. It is your job to simplify your code enough so that you can see where the error is coming from, and then give that information in the question.

The error mentions create_instance which is the call for creating an UNO service.

Also I am not comfortable with the idea of storing XSCRIPTCONTEXT values in global variables unless you're sure you know what you're doing (and it's pretty clear you don't. :) )

Also I have strong doubts about achieving success by combining different libraries - it looks like you want to use both openpyxl and the UNO interface in the same code, and I would not expect that to turn out well. Just stick with plain UNO because there are plenty of people who can help with that. (Or if you choose openpyxl then I will ignore your question and someone knowledgeable about that may be able to help.)

Finally, it seems like import uno is missing. That alone could explain the error, and it's hard to imagine how you have all this code yet didn't even start with that.

Before trying all of this complex code, get a simple example working. It looks like there are a lot of python-uno fundamentals you still need to figure out first, and it doesn't look like you have put much effort into working through a tutorial yet.