Ask Your Question

How to Import Python scropt into LibreOffice Calc and run with a macro

asked 2021-01-09 08:30:40 +0100

LibreOffice_Mike gravatar image

Hi. I have a python script that gets data every 0.2s from the serial port. I want to have this data and a date+time stamp dumped into advacent columns across one row, then, the next set of data be put into the wor below.

1) I've put my script ( into the ...LibreOffice\share\Scripts\ folder, but when I 'Tools > Macros > Organise Macros ? Python... then I see my script presented as a 'folder' and not a runable script with the purple scroll icon. How can I get my script to be a runable script and not a 'folder'

2) I tried some of the standard scripts and they wrote Information into LibreOffice Write! I examined the code to see if I could tweak it to dump stuff into Calc, I didn't understand the code enough to modify it

3) Re tweaked code in 2), I realise I'm going to struggle to be able to get it to control where the python script dumps the info into calc. What python commands "know" how to dump data into Calc cells and at certain positions?

I can do all this - data logging - in Excel, but I'm tying to leave the world of Microsoft.

edit retag flag offensive close merge delete


your file should be stored in /OSdependent/.config/libreoffice/4/user/Scripts/python/ create python folder if not present
You can benefit from installing the extensions APSO and MRI, enter them as searchterm in the extension manager. With APSO you can get direct access to the python macros and execute them SHFT+ALT+F11. There are issues with MRI, if it doesn't work check the answer ( and download from github. With MRI you can inspect methods and properties during runtime of the macro.
You can also check link text for inspiration. See sample code below to MRI an object and write output to a writer document print.odt stored in your work directory.
If you return to tis post, include your operating system

parsely gravatar imageparsely ( 2021-01-09 09:38:01 +0100 )edit

Thanks for the input. I'm using Libre Office On installing the APSO, the shortcut keys don't work and choose python organiser from the Tools menu don't do anything. MRI couldn't be installed ('bad file') error message. Both extensions (never knew they existed before today) look useful, however so I'll try some more, and maybe the enigmatic UNO thing will begin to bake sense to me. If I can't do this all with a python script, I wonder if it me doable with LibreOffice BASIC? Thanks for the suggestions thus far.

LibreOffice_Mike gravatar imageLibreOffice_Mike ( 2021-01-09 18:27:21 +0100 )edit

1 Answer

Sort by » oldest newest most voted

answered 2021-01-09 09:53:06 +0100

parsely gravatar image

updated 2021-01-09 09:57:24 +0100

import uno   #copy>pasted, not checked for 'smaller' errors
import os

def study() :
    D = writer_print()

ctx = uno.getComponentContext()
smgr = ctx.ServiceManager
StarDeskTop = smgr.createInstanceWithContext( "",ctx)
dispatcher = smgr.createInstanceWithContext( "",ctx ) 

com_sun_star_text_ControlCharacter_PARAGRAPH_BREAK  = uno.getConstantByName( "" ) 
com_sun_star_text_ControlCharacter_LINE_BREAK       = uno.getConstantByName( "" ) 
com_sun_star_text_ControlCharacter_HARD_HYPHEN      = uno.getConstantByName( "" ) 
com_sun_star_text_ControlCharacter_SOFT_HYPHEN      = uno.getConstantByName( "" ) 
com_sun_star_text_ControlCharacter_HARD_SPACE       = uno.getConstantByName( "" ) 
com_sun_star_text_ControlCharacter_APPEND_PARAGRAPH = uno.getConstantByName( "" ) 

from import PropertyValue

class writer_print(): 
    """A class which allows conveniently printing stuff into a Writer document. 
    Very useful for debugging, general output purposes, or even for creating reports.
    Adapted from""" 

    def __init__( self ): 
        desktop = smgr.createInstanceWithContext('', ctx)

        for doc in desktop.getComponents():

            if doc.Title=="print.odt":
            pathSettings = smgr.createInstanceWithContext( "",ctx ) 
            i = pathSettings.Work.find("//")
            cwd = ( pathSettings.Work  )[i+2:]      # get user work directory in OS terms
            name = uno.systemPathToFileUrl( os.path.join( cwd , "print.odt") )
            doc = StarDeskTop.loadComponentFromURL( name , "_blank", 0,() )

        win = doc.getCurrentController().getFrame().getComponentWindow()

        self.oWriterDoc = doc

        self.oWriterText = self.oWriterDoc.getText() 
        self.oWriterCursor = self.oWriterText.createTextCursor()

    def writeLine( self, *args ): 
        if len( args ) > 0: 
            self.write( *args ) 

    def write( self, arg1, *argsRest ): 
        self.writeOne( arg1 ) 
        for arg in argsRest: 
            self.writeOne( arg ) 

    def writeOne( self, arg, bAbsorb=False ): 
        if type(arg) == list:
            expand = []
            for el in arg:
                if type(el) != str:
                    el = round(el,2)
            out = ""            
            for el in expand:
                out += str(el) + ","
            arg = out
        elif type(arg) != str:
            arg = arg #round(arg,2)
        self.writeString( "  " + str( arg ), bAbsorb ) 

    def writeTab( self, bAbsorb=False ): 
        self.writeString( "\t", bAbsorb ) 

    def writeParagraphBreak( self, bAbsorb=False ): 
        self.writeControlCharacter( com_sun_star_text_ControlCharacter_PARAGRAPH_BREAK, bAbsorb ) 
#         self.writeControlCharacter( com_sun_star_text_ControlCharacter_LINE_BREAK, bAbsorb ) 

    def writeString( self, cString, bAbsorb=False ): 
        self.oWriterText.insertString( self.oWriterCursor, cString, bAbsorb ) 

    def writeControlCharacter( self, nCtrlChar, bAbsorb=False ): 
        self.oWriterText.insertControlCharacter( self.oWriterCursor, nCtrlChar, bAbsorb ) 

    def writeEnd(self,character="="):
        self.writeString( 60*character+"<")

def mri( target):
    mri = ctx.ServiceManager.createInstanceWithContext( "mytools.Mri",ctx)

def calc():
    fLoad = uno.systemPathToFileUrl( "file://...." , fileTemplate) )
    document = StarDeskTop.loadComponentFromURL( fLoad , "_blank", 0, () ) 

    frame = document.getSheets().getByIndex(0)
    cell = frame.getCellByPosition(0,0)  # = A1
    cellByName = cell.getString()        # the contents of cell A1

edit flag offensive delete link more


Wow. I am so appreciative of you spending all that effort to help. There's a lot here to probe, so I'll get back as soon as I can on this. (Admins, please don't lick this question yet, tq)

LibreOffice_Mike gravatar imageLibreOffice_Mike ( 2021-01-09 18:29:30 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2021-01-09 08:30:40 +0100

Seen: 41 times

Last updated: Jan 09