Accessing com port using python script within lo calc


long time reader but first time poster. apologies for the longish post.

i’m trying to read data from a com port (com 7) from inside libre office (lo) calc using a python script. the data is captured on port com 7 on a windows 10 desktop from a digital caliper attached via an esp32 microcontroller (which is attached to the windows 10 desktop). i program the esp32 using the arduino ide and i can see the data on com port 7 via the arduino ide data window. if i close the arduino ide’s comm port window and open pycharm i can run a python script within pycharm accessing com port 7 and i can see the data. when i stop the python script from executing in pycharm and try to run a python script modified to work in lo calc as a macro i get a permissions error code (see bottom).

if i close all lo calc instances/workbooks that are open and go back to the arduino ide i can open the comm port window and see data again. close that window and go back into pycharm and run the python script and i can see data again.

as a side note, if any instance of lo remains open, neither the arduino ide nor pycharm are able to read data from the comm port (comm port busy error). i surmise that there is some bit of code that lo runs that spans all open instances (workbooks, documents, etc) that holds a connection open so long as any lo instance is open. close all lo instances and the port is released and other programs can use the comm port again.

i believe i worked out the bits in the python script that needed to be changed in order to port from pycharm and run as a macro under lo calc. i say that because when i navigate in lo calc to Tools-> Macros → Organize Macros → Python i can select the python macro i want to run and it allows execution with the execute button. but then i get a permission error. i have python installed local to the lo installation along with pip and pyserial local to it as well.

i must have omitted a step in the port to lo calc and was hoping someone could point it out. there may be other issues you see that i will run into after getting through the comm port access error. if so i would appreciate those insights as well. my skill level with python and knowledge of scripting within lo is fairly low.

here is the macro to read data from com 7 (10 times as a test). i’m not sure if the macro’s text will show as misaligned due to autowrap but here goes.

import serial
# removed unused irrelevant codelines by @karolus
doc = XSCRIPTCONTEXT.getDocument()
sheet = doc.getCurrentController().getActiveSheet()

def concat_cell(args=None):
	serialPort = serial.Serial(port="COM7", baudrate=115200, bytesize=8, timeout=2, stopbits=serial.STOPBITS_ONE)
serialString = "" 
for each_number in range(10):
    serialPort = serial.Serial(port="COM7", baudrate=115200, bytesize=8, timeout=2, stopbits=serial.STOPBITS_ONE)
    if serialPort.in_waiting > 0:
        serialString = serialPort.readline()
            cell = sheet.getCellByPosition(0, each_number)
g_exportedScripts = (concat_cell,)

here is the error i see when running:

A Scripting Framework error occurred while running the Python script$concat_cell?language=Python&location=user.

Message: <class 'serial.serialutil.SerialException'>: could not open port 'COM7': PermissionError(13, 'Access is denied.', None, 5)
  File "C:\Program Files\LibreOffice\program\", line 1057, in getScript
    mod = self.provCtx.getModuleByUrl( fileUri )
  File "C:\Program Files\LibreOffice\program\", line 494, in getModuleByUrl
    exec(code, entry.module.__dict__)
  File "C:\Users\user1\AppData\Roaming\LibreOffice\4\user\Scripts\python\", line 12, in <module>
    serialPort = serial.Serial(port="COM7", baudrate=115200, bytesize=8, timeout=2, stopbits=serial.STOPBITS_ONE)
  File "C:\Program Files\LibreOffice\program\python-core-3.8.15\lib\site-packages\serial\", line 33, in __init__
    super(Serial, self).__init__(*args, **kwargs)
  File "C:\Program Files\LibreOffice\program\python-core-3.8.15\lib\site-packages\serial\", line 244, in __init__
  File "C:\Program Files\LibreOffice\program\python-core-3.8.15\lib\site-packages\serial\", line 64, in open
    raise SerialException("could not open port {!r}: {!r}".format(self.portstr, ctypes.WinError()))

The error_message seems clear enough!

no warranty!!! because cant test here

import serial

def concat_cell(args=None):
    doc = XSCRIPTCONTEXT.getDocument()
    sheet = doc.getCurrentController().getActiveSheet()
    with serial.Serial(port="COM7",
                       stopbits=serial.STOPBITS_ONE) as port:
        for row_index in range(10):
            if port.in_waiting > 0:
                cell = sheet.getCellByPosition(0, row_index)
                cell.setString( port.readline())
g_exportedScripts = (concat_cell,)

i appreciate the help and code! it got me further than i was but the result i see in the spreadsheet appears to be data related to the esp32 itself (see below). the esp32 sits on a small pcb with a momentary button. when pressed the esp32 transforms the most recently buffered caliper data and sends to the windows 10 pc. the data below is what i saw in lo calc after 1 button push.

next i closed all lo components and went back into the arduino ide and started the comm channel window. when i pressed the button on the esp32 board as before, the anticipated data appeared in the comm channel window as expected (also below).

so thanks to your suggestion i’m closer than i was. i need to ponder on the results for a bit to see if i can understand how the pieces come together (at least now i have something to look at).

here is the output that appears in the lo calc cells A1-A10 after 1 press of the caliper data capture button:

A1 ets Jun 8 2016 00:22:57
A3 rst:0x1 (POWERON_RESET),boot:0x13 (SPI_FAST_FLASH_BOOT)
A4 configsip: 0, SPIWP:0xee
A5 clk_drv:0x00,q_drv:0x00,d_drv:0x00,cs0_drv:0x00,hd_drv:0x00,wp_drv:0x00
A6 mode:DIO, clock div:1
A7 load:0x3fff0030,len:1344
A8 load:0x40078000,len:13864
A9 load:0x40080400,len:3608
A10 entry 0x400805f0

when i shut down the lo components and use the arduino ide comm port window to show the data it receives, after 1 press of the caliper data capture button i see:


i ran the lo calc test twice and got the same result, so it’s repeatable and the outcome is visible. so it’s a good place to start. if i have an epiphany or otherwise find a solution for this, i’ll post it.


based on further research it appears the data displayed in lo calc is the output from the esp32 rebooting. i’ve tried running the macro a few more times and no data was received. the good news is we don’t see a “no permissions” error either. so on balance its a step in the right direction.
thanks again.