Opening and saving a .xlsx document using libreoffice headless

English
#1

I have a requirement where I just need to open a .xlsx in libreoffice and save and close it. I am trying to see if i can do it using headless function. I am new to libreoffice and while search i thought this post ‘Save and close ods file from command line - #2 by JohnSUN’ is close to what i need. However when I try to do what this post says and execute the macro on my file using the instructions given in the post, i simply get a “>” prompt and no further information is displayed. I am not sure how to proceed. Kindly advise.

I have created a library ‘updateRefs’ and a module ‘UpdateHidden’ and have the following code within it:

Option Explicit 

Sub updateAndSave(sSourcePath As String)
Dim sPath As String
Dim oDoc As Variant 
Dim loadComponentProperties(0) As New com.sun.star.beans.PropertyValue
Rem Prevent screen flashing - perform file upload in "stealth mode"
	loadComponentProperties(0).Name= "Hidden"
	loadComponentProperties(0).Value= True
Rem We can load the spreadsheet using loadComponentFromURL().
Rem But the OpenDocument()  from the Tools library does it better.
Rem Therefore, we load the Tools library:
	GlobalScope.BasicLibraries.LoadLibrary("Tools")
Rem Convert the full path to the file, received in the parameter, 
Rem into the correct URL:
	sPath = ConvertToURL(sSourcePath)
Rem If a file with that name exists (and it may not exist):
	If FileExists(sPath) Then
Rem Open spreadsheet:
		oDoc = OpenDocument(sPath,loadComponentProperties)
Rem Recalculate all formulas (and update external links at the same time)
		oDoc.calculateAll()
Rem Save:
		oDoc.store()
Rem and close
    	oDoc.close(True)
    EndIf 
End Sub

now when i try to execute this macro from the terminal using:

libreoffice --headless --nologo "macro:///updateRefs.UpdateHidden.updateAndSave("/home/balajit/Python\ Projects/Calculate\ Capital\ Gains/testinput1.xlsx")

I get the “>” prompt.

#2

If you show the code you use, it’ll be easier to help you.

#3

Hi, Thanks for your response. I have updated my post with the macro code and how I am trying to execute it using libreoffice headless option. Kindly have a look and please advise.

#4

Do you want to edit your file from a Python script?

#5

yes. my requirement is since I am updating a group of cells with formulas from my python script, in order to read the calculated values in my python script i need to get these formulas evaluated by excel / libreoffice by opening the file and saving it. Hence I am trying to use this method. Hope it clarifies. Thanks again for your prompt response.

#7

I guess the curly quotes are added by this site, so I will ignore this, but I see 3 of them, but expect 2 or 4.
.
The path implies you are on some Linux or Mac. Why do you quote Space with backslash inside quotes?
.
I’d try to omit headless first, so you can see, if LibreOffice starts, then maybe put a MsgBox after the ConvertToURL to check your filename. And for first tests I suggest a path and filename without spaces…
.
I assume you already checked security settings, so your LibreOffice is allowed to execute this Macro… In headless mode you can’t see the typical warnings.
.
Without testing my guess on a working command would be:

libreoffice --headless --nologo "macro:///updateRefs.UpdateHidden.updateAndSave(/home/balajit/Python Projects/Calculate Capital Gains/testinput1.xlsx)"
#8

--headless and macro: URI are mutually exclusive.

1 Like
#6

This code I use in a rather large project. I cleaned it up to show you he can control everything from Python.

LibreOffice needs to use the Python system to make this code work.

def main():
    PATH = '/home/elmau/Projects/test/python/test.xlsx'

    app = LIBO()
    if app.is_running:
        options = {'Hidden': True}
        doc = app.open(PATH, options)

        sheet = doc.Sheets[0]
        sheet['A1'].String = 'Work fine...'

        doc.calculateAll()
        doc.store()
        doc.close(True)

        app.close()
        print('Ok...')
    return

codigo.pdf (18.5 KB)

#9

OOO Development Tools (OooDev) is very good at this.

There are fantastic examples such as Write Convert Document Format.

See Also: Python LibreOffice Programming Chapter 2. Starting and Stopping.

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