Best Way to Connect Python to LibreOffice

[asymlabs]

This question is seeking advice from the experts. I have a LibreOffice spreadsheet that is largely based upon Basic macros, but have set up a Python/Latex subsystem that can produce formatted PDF reports by assembling various Latex templates, with such things as barcodes, qrcodes and other art/images. This is done by reading tex templates into Python Templates, passing to them a data dictionary (that would be loaded with spreadsheet data), writing them as temp files to disk, and then compiling them with Latex. What I need to do is connect the data from the spreadsheet to the subsystem. There is no need to return a value to the spreadsheet, just to produce the pdf report on disk (for now).

Firstly there is a need to pass the data via Basic to the Python subsystem. Secondly I am concerned about portability, ie I would like to simply pass a file that can be installed easily on another machine (spreadsheet and subsystem), that just works (on a machine that has xelatex of course). Thirdly there are various approaches:

1. Write an extension to LibreOffice calc
2. Launch a separate process by macro
3. Write a wrapper function for Python (is this possible?)
4. Are plugins possible (see 1 above?)

I had hoped that I could use LibreOffice's own built in PDF facilities to produce this output. However the difficulty is the production of barcodes, qrcodes, and other art, latex is much more flexible in this respect.

This initial item has about 15 data elements that need to be passed from the spreadsheet to Python, but I can forsee much more complex and elaborate reports in future. What would be your advice as to the best way to structure the system, and the best way to pass the data?

[Villeroy]

1. Write an extension to LibreOffice calc
2. Launch a separate process by macro
3. Write a wrapper function for Python (is this possible?)
4. Are plugins possible (see 1 above?)

Use a database or plain text files or json or xml or your own data structures. Anything is easier than spreadsheets, particularly when you don't do any arithmetics.

[asymlabs]

As a follow up, we've since learned that it is possible to connect Python to LibreOffice in almost any way. For example, Basic procedures can be run from Python, Libreoffice can be placed entirely under the control of Python, Python procedures can be run from Basic, with wrappers, as part of an extension, etc. We arrived upon the following solution for our purposes, which, following a similar concept, can permit extension with supported languages other than Python in future:

Code: Select all

REM  BACKGROUND

REM  This module is used to set up the master script provider for the supported
REM  language of choice (Python is the base).  This was obtained from reference:
REM  
REM      PythonFromCalc_8Dec2010.pdf
REM  
REM  at address:
REM  
REM      http://hobbyutil.googlecode.com/files/PythonFromCalc_8Dec2010.pdf
REM
REM  and futher information is found at address:
REM 
REM      http://stackoverflow.com/questions/7591656/
REM          how-can-i-call-a-python-macro-in-a-cell-formula-in-openoffice-org-calc 
REM
REM  The function can be called within a cell or in other ways, ie as part of
REM  a Basic procedure.  

REM  USAGE

REM  Note that the arguments and return value for the Basic function wrapper
REM  must match the underlying 'called' function.  In the case of a Python
REM  call, where we have a Basic wrapper, as fnPyFunc(a,b), and the Python 
REM  function of interest, as PyFunc(a,b) in the file utilities.py, an example
REM  usage is: 

REM  Function fnPyFunc( a As double, b As Long ) As Double
REM    Dim _function As Object
REM    _function = Provider.Master.getPythonFunctionByName( "PyFunc", "utilities.py" )
REM    fnPyFunc = _function.invoke(Array( a,b ), Array(), Array()) 'Pass args as array.
REM  End Function

Option Explicit

REM GLOBAL DATA AND PROCEDURES

Global GlobalProvider As Object							'Persistent for all modules
Private Const GScriptSvc = "vnd.sun.star.script:"				'Service that processes external scripts. 
Private Const GPythonArgs = "?language=Python&location=user"	'language Python, location ../user/Scripts/Python..

REM _SetMasterGlobals()
Sub _SetMasterGlobals()

	'Purpose: Create the master script provider (GlobalProvider) that can be used to
	'call a script language that is supported.  Once called the provider is not created
	'again.
	
	On Error GoTo ErrorHandler

	'Declare
	Dim _unosvc As Object
	
	'Create only when object does not exist
	If isNull( GlobalProvider ) Then
		_unosvc = createUnoService( "com.sun.star.script.provider.MasterScriptProviderFactory" )
		GlobalProvider = _unosvc.createScriptProvider( "" )
	End If
	
	Exit Sub
	
	ErrorHandler:
	
	'Stop by default so as not to harm any data.  Insert error message box here if needed.
	
	Stop
	
End Sub

REM PUBLIC FUNCTIONS

REM getPythonFunctionByName(a,b)
REM a is the function name as a string.
REM b is the Python script file name that contains the function, optional.
Function getPythonFunctionByName( functionname As String, Optional scriptfile As String ) As Variant

	'Purpose: Supply the Python function name to Basic and return the procedure.  If
	'the script file name is not given, defaults to main.py.

	'Declare
	Dim _scripturl As String
	Dim _scriptsvc As String
	
	_SetMasterGlobals()										'Ensure GlobalProvider is set
	
	On Error GoTo ErrorHandler

	'Initialize
	_scriptsvc = GScriptSvc & "main.py$" 					'Ensure script service is set
	If Not isMissing( scriptfile ) Then
		_scriptsvc = GScriptSvc & scriptfile & "$"
	End If
	_scripturl = _scriptsvc & functionname & GPythonArgs	'Assemble Python-specific url
	
	'Set return value.
	getPythonFunctionByName = GlobalProvider.getScript( _scripturl )
	
	Exit Function
	
	ErrorHandler:
	
	'Stop by default so as not to harm any data.  Insert Error MsgBox here if needed.
	
	Stop
	
End Function

Python code would be placed in the location ../user/Scripts/python/.. and the Basic wrapper can be called directly from a spreadsheet cell or from other Basic procedures. This would utilize the Python 3+ that comes bundled with LibreOffice 4+, can take some of the computational load off LibreOffice (if needed) and also provide access to the extensive library capability of Python for numerical analysis etc. We've used this only under Linux but it should be portable across platforms. Lastly we intend to use this as a template to access other supported languages, by adding the appropriate G<Language>Args and get<Language>FunctionByName(a,b).

[Villeroy]

1. Write an extension to LibreOffice calc
2. Launch a separate process by macro
3. Write a wrapper function for Python (is this possible?)
4. Are plugins possible (see 1 above?)

1. Of course there are extensions. You can find them on the extensions site.
2. Did you try?
3. Whatever you like to wrap, just do it and if you encounter difficulties, ask some _concrete_ question here.
4. Whatever the difference between "extension" and "plugin" is ... Yes, it is possible, I think.

For example, Basic procedures can be run from Python,

Well, yes. Once you control the office by the language of your choice, you can run all macros. But what is your question? You searched the home page but did not find http://www.openoffice.org/udk/python/python-bridge.html? You searched the web but did not find anything like http://www.linuxjournal.com/content/sta ... ice-python? Did you download and inspect existing Python extensions?

[karolus]

Hallo
Rewrite your "subsystem" to make it run fromout OfficeGui
or
connect remote from your "subsystem" , pickup the Data, and proceed...

Any of your Ideas makes it more complicated.

[asymlabs]

Hi Villeroy - In the end we decided that conceptually there were three main choices, (1) turn over control of LibreOffice to Python, (2) make a system call to the Python script or (3) run the Python from LibreOffice Basic. I did experiment with various options (ie XSCRIPTCONTEXT, PyUno, etc), but decided in this case that keeping control within LibreOffice Basic (Option 3) was the best for us. Didn't want to rewrite the LibreOffice Basic in Python (which tends to happen with Option 1), and didn't want make system calls (Option 2) that can cause problems with cross-platform portability. Granted, in this case because Latex (xelatex) is used, there will be some problems with cross platform portability anyway, but we have in mind other applications in the future where this would be useful for numerical analysis purposes.

Hi karolus - at the moment the system does run from outside the officegui, but we are trying to find a way to make it easier for the user, with just one interface. But I don't disagree with you, the coupling will make it more complicated.

[Villeroy]

From your Python program you can connect to a running office suite that has been started in listening mode.
Dumping some figures into an Open Document spreadsheet can be done without any office suite but with the help of libraries for zip and xml. Prepare a document template with all the complicated formatting stuff and let your program write raw data and style names into the content.xml. [Solved] Setting Custom Properties from external application

[asymlabs]

Thanks Villeroy - Since your comment I've been looking at the Python libraries Zipfile and others and using these it would be possible to dispense with tex altogther (the preferred option), except for the generation of QRCodes and Barcodes. I have tried the Python libraries to generate these but they only produce bitmaps. Latex generates proper vector format. I don't think it is necessary to parse the XML, why not just use flags in the template file and just do a search and replace? This is what our Python libraries are doing with the tex templates, so using odf files instead would be a simple modification, with in-memory parsing, using the zipfile library. This could be the best solution.

[markling]

Any fool would assume you could simply import... read... . What you actually have to do is spend hours trawling forums trying one library and tool after another to find they are all a headache.

If you just want to get the data out of a document so you can process it, I would suggest looking into something like this, which worked with LibreOffice:

Code: Select all

soffice --headless --convert-to txt:"Text" file_to_convert.odt

http://ask.libreoffice.org/en/question/ ... -id-50415

[Villeroy]

Any fool would assume you could simply import... read... . What you actually have to do is spend hours trawling forums trying one library and tool after another to find they are all a headache.

You do not even understand what the topic is about.