Creating a Python function for LibreOffice Calc

Description

LibreOffice Calc allows users to create their own functions using the built-in programming language StarBasic. This language is very similar to VBA , however, has limited functionality because it is rarely used.

Modern programming languages, such as Python, provide much more possibilities and have a wider range of additional libraries for all occasions.

So, let's learn how you can create your own function using such a popular programming language as Python.

StarBASIC Code for the Greeting() function

First, let's remember how you can write your own program using the standard LibreOffice Calc tools for writing macros and functions.

Below is the code for the function Greeting , which returns the phrase "Hello " + input parameter into the selected cell using the built-in programming language StarBasic.

To add your own function, open the menu Tools - Macros - Edit Macros... , select Module1 and copy the following text into the module:

Function Greeting(sName As String) As String
    Greeting = "Hello " & sName & "!"
End Function

Then, close Macro Editor, return to LibreOffice Calc and enter the name of this function in any cell and specify the input parameter.

Python Folder

Before writing a function in Python, we need to first create a *.py file (myPyScripts.py) in the following folder:

%APPDATA%\LibreOffice\4\user\scripts\python

If folders scripts and python are missing, you need to create them manually.

Python Code for the Greeting() function

Below is the Python script for the function Greeting , which prescribes the phrase "Python say 'Hello' to " + input parameter in the selected cell in the program LibreOffice Calc.

To add this script, open the file myPyScripts.py by means of Notepad++ (or Windows Notepad) and copy the following text there:

def Greeting(sName):
    return "Python say 'Hello' to " + sName + "!"

Then, close Notepad++ and return to LibreOffice Calc.

We cannot run this function immediately because the LO Calc interface does not have a direct connection to the Python functions.

To solve this problem, we need to create an intermediate function on StarBasic:

Function Greeting(sName As String) As String
    pyFile = "myPyScripts.py"
	pyFunc = "Greeting"
	pyParams = Array(sName)
	
	ScriptProvider = CreateUNOService("com.sun.star.script.provider.MasterScriptProviderFactory").createScriptProvider("")
	pyScript = ScriptProvider.getScript("vnd.sun.star.script:" & pyFile & "$" & pyFunc & "?language=Python&location=user")
    Greeting = pyScript.invoke(pyParams, Array(), Array())
End Function

Add this intermediate function to the StarBasic macro editor (menu Tools - Macros - Edit Macros...).

Now call the StarBasic function Greeting() , which in turn calls the Python version of the function Greeting() . Just type Greeting() in any cell and select any value as a parameter.

Using the extension

You can also use a ready-made set functions and utility written on Python by installing the extension YLC Utilities .

After that, this functionality will be available in all files that are opened in LibreOffice Calc.