Using XlCalcNet within LibreOffice Calc

Preparing LibreOffice Calc for using XlCalcNet

To enable the use of XlCalcNet in LibreOffice Calc, two extensions need to be installed: the first, named MpfunlabLocal.oxt, contains functions and dialogs written in LibreOffice Basic and is intended to be modified by the user during normal use. The second, named Mpfunlab.oxt, contains the functionality behind the spreadsheet functions APY0 - APY9 and ASDOUBLE. It has been compiled using the SDK of LibreOfffice 7.4 and is NOT intended to be modified by the user during normal use. Both extensions are located in a subfolder of the DataXlCalcNet folder in the Documents folder: DataXlCalcNet\SpreadsheetAddins\LibreOfficeCalc.

Installing MpfunlabLocal.oxt and Mpfunlab.oxt

To install these two extensions properly, following the correct order is important. First install MpfunlabLocal.oxt within a normally launched LibreOffice Calc (i.e. without administrator privileges): Open the Extension dialog from the main menu:Tools -> Extensions.... In the Extension dialog click the Add button and in the Documents folder navigate to the folder DataXlCalcNet\SpreadsheetAddins\LibreOfficeCalc. Double- click on MpfunlabLocal.oxt. Then click on the Close button in the Extension dialog. In the dialog Restart LibreOffice, click on Restart Now. This will close LibreOffice Calc and will open the general LibreOffice desktop. Exit LibreOffice.

Now start LibreOffice with administrator privileges: Right-click on the desktop icon of LibreOffice and select Run as administrator. In the following dialog, confirm that you want to proceed. In the LibreOffice desktop, open a Calc Spreadsheet. Open the Extension dialog from the main menu:Tools -> Extensions.... In the Extension dialog click the Add button and in the Documents folder navigate to the folder DataXlCalcNet\SpreadsheetAddins\LibreOfficeCalc. Double- click on Mpfunlab.oxt. In the following dialog box “For whom do you want to install the extension?”, click on For all users. In the following “License Agreement” dialog, click on Accept. Then click on the Close button in the Extension dialog. In the dialog Restart LibreOffice, click on Restart Now. This will close LibreOffice Calc and will open the general LibreOffice desktop. Exit LibreOffice.

Within a normally launched LibreOffice Calc, open the Macro Editor from the main menu: Tools -> Macros -> Edit Macros.... In the Object Catalog of the Macro Editor, select My Macros & Dialogs -> MpFunlabLocal -> CallsFromPython: In the function GetCPythonExeDirLocal set the path of the external Python installation which matches the main internal Python version, e.g. GetCPythonExeDirLocal ="C:\Python313". Then close the Macro Editor.

Using the Python standard library functions within spreadsheet formulas

Note

In the following subsections, we will work with the file LoDemoAPYstd.ods, which contains examples for using the Python standard library. More advanced examples using Numpy, Matplotlib, Pandas, Scipy, Seaborn and XlCalcNet start here.

Within LibreOffice Calc, open from the main menu File -> Open in the Documents folder the LibreOffice Calc workbook DataXlCalcNet\DataExamples\MainExamples\Workbooks\LoDemoAPYstd.ods.

This file contains several worksheets which demonstrate different possibilities of using the Python standard library within Libreoffice Calc spreadsheet formulas.

Before we start with that we will briefly explore the use of these functions with the Function Wizard of LibreOffice Calc: In the worksheet “Math”, select cell B12 and click on the icon of the Function Wizard in the Formula Bar. This opens the Function Wizard with the Structure tab selected:

We see that the formula in the spreadsheeet cell is =APY1("result = math.ceil(P1)",C12). The function APY1 has a required string parameter, Formula, which contains a Python script. This python script can contain several Python statement. The last statement is always expected to assign a value to the variable result, in this case a Python float, which is coverted to a floating point number in double precision (a “Double”) in LibreOffice. The next optional parameter, Param1, can be a string, a Double, a Boolean value or a reference. In this case, it is a reference (C12) which points to a Double with the value 3.123. This optional parameter, Param1, is referenced in the Python formula given above as P1. The last but one parameter, Transposed, and the last parameter, ShowShape, are only relevant when arrays are returned; this is discussed here.

If we open the Functions tab, the Functions wizard looks like this:

We see that the function APY1 is in the Category “Add-in”, and that there are 10 functions, APY0 to APY9, which differ only by the number of parameters Param1 to Param9 which they support (The function APY0 does not have a parameter Param0). There is also a function ASDOUBLE, which is used to convert string representations of a Python Fraction or Decimal into a Double; this is discussed here.

The worksheet “GeneralInfo”

The worksheet “GeneralInfo” contains calls to the Python modules os, platform and sys.

For example, the cell B4 contains the formula =APY0("result = platform.processor()"). The function result depends on the hardware, e.g. Intel64 Family 6 Model 165 Stepping 5, GenuineIntel.

The cell B16 contains the formula =APY0("result = os.getcwd()"). The function result depends on the LibreOffice installation, e.g. C:\Program Files\LibreOffice\program.

The cell B33 contains the formula =APY0("result = str(sys.float_info.epsilon)"), which return the machine epsilon in double precision. The function result is: 2.220446049250313e-16.

The worksheet “Math”

The worksheet “Math” demonstrates the use of the Python module math and the use of parameters in functions.

For example, the cell B33 contains the formula =APY1("result = math.exp(P1)-1",C33), which calculates \(\exp(\text{P1})-1\) naively. The function result is 1.00000500000696E-05, with the cell C33 containing the value 0.00001.

The cell B34 contains the formula =APY1("result = math.expm1(P1)",C34), which calculates \(\exp(\text{P1})-1\) using the expm1 function. The function result is 1.00000500001667E-05, with the cell C34 again containing the value 0.00001.

The floating point values NaN, +inf and -inf are not supported in spreadsheet programs, which only return #NUM! in these cases. Use the string representation instead. This is shown in the cells A4:B9 on this worksheet.

The cell B36 contains the formula =APY2("result = math.log(P1, P2)",C36, D36), with the cell C36 containing the value 3.123 and the cell D36 containing the value 10. The function result is: 0.494571984230199.

The worksheet “Arrays”

The worksheet “Arrays” demonstrates the use of arrays in functions. The Python equivalent of arrays are lists.

For example, the cell B3 contains the formula =APY0("result = str(sys.path)"). The function sys.path returns a list of strings (containing the entries of the Python path), which is converted into one string by writing str(sys.path). This makes sure that the result can be displayed in one cell, but the result string is quite long and hard to read.

We can use an array formula to improve readability. We recall that in addition to the usual parameters P1 - P9 in the functions APY0 to APY9 there are two more: The last but one paramter, named Transposed, which is optional with default value 0, where a non-zero value means that the returned array should be transposed; and the last paramter, named ShowShape, which is optional with default value 0, where a non-zero value means that the shape of the returned array should be indicated like R7C3 for an array with 7 rows and 3 columns.

As an example, the cell B6 contains the formula =APY0("result = sys.path",1,1) and returns the shape information R12C1 followed by the separator |, followed by the first entry of the transposed array, i.e. in this case R12xC1| C:\Users\DUHad\Documents\DataXlCalcNet.

We can build the corresponding array formula by first selecting a range like R12C1, then typing in the input line of the formula bar =APY0("result = sys.path",1,0), and finally, while keeping the shift and control keys pressed down, pressing the enter key.

This is what has been done in cells B9:B20. The formula is now displayed as {=APY0("result = sys.path",1,0)}, with curly braces, to indicate that it is an array formula. We can change the range which is covered by the array formula by dragging the small blue rectangle in the lower right corner in the selected range (see the screenshot below).

The worksheet “Data”

The worksheet “Data “ demonstrates the use of named ranges in LibreOffice Calc spreadsheet formulas.

Currently there a two small datasets with predefined as named ranges: matA defined as the range $Data.$A$3:$A$7 and matB defined as the range $Data.$A$3:$A$7.

These ranges are used in the workbook “Arrays”.

Using Numpy, Matplotlib, Pandas, Scipy, Seaborn and XlCalcNet within spreadsheet formulas

Note

In the following subsections. we will work with the file LoDemoAPYadv.ods, which contains more advanced examples using Numpy, Matplotlib, Pandas, Scipy, Seaborn and XlCalcNet. Examples for using LibreOffice Calc with the Python standard library start here.

Within LibreOffice Calc, open from the main menu File -> Open in the Documents folder the LibreOffice Calc workbook DataXlCalcNet\DataExamples\MainExamples\Workbooks\LoDemoAPYadv.ods.

This file contains several worksheets which demonstrate different possibilities of using Numpy, Matplotlib, Pandas, Scipy, Seaborn and XlCalcNet within Libreoffice Calc spreadsheet formulas.

Some of these functions write their output into the XlCalcNetIDE\OutputMonitor folder in the AppDataLocal folder. In order to see it, we need to open the Output Monitor application: start the Tiny IDE by clicking on its icon in the task bar, and in the main menu, click on Tools -> Start Output Monitor.

The worksheet “MpInput”

The worksheet “MpInput “ demonstrates the use of named ranges in LibreOffice Calc spreadsheet formulas.

If we try to enter a number like 123456789012345678/901234567890, i.e. a number with more than 16 digits into a spreadsheet cell, it will automatically be shortened to 1.23456789012346E+029. In order to be able to enter such numbers into spreadsheet cells, the cells must first be formatted as text, and thereafter these numbers can be entered as text. This is mostly useful when working with Python Fractions and Decimals.

As an example, worksheet “MpInput” contains a named range called “MpInputFractions” in column A, which can be used as input for descriptive statistics or linear algebra routines.

However, this kind of input is hard to read; it is also not directly usable for numerical spreadsheet functions. This can be changed by using the function ASDOUBLE, a shown in column B

The worksheet “Programming”

The worksheet “Programming” demonstrates the use of small python scripts in LibreOffice Calc spreadsheet formulas.

=APY0("temp = 0 $n for i in range(14): $n$t temp += i $n result = 2 * temp") is the formula in the cell B3. Here we are using $n for newline and $t for indentation; the result is 182. The corresponding Python code with conventional formatting would look like this:

temp = 0
for i in range(14):
    temp += i
result = 2 * temp

=APY1("temp = 0 $n for i in range(int(P1)): $n$t temp += i $n result = 2 * temp", C4) is the formula in the cell B4, and the cell C4 contains the value 45; the result is 1980. The corresponding Python code with conventional formatting would look like this:

temp = 0
for i in range(int(P1)):
    temp += i
result = 2 * temp

=APY0("from xlcalcnet import mpm $n  mpm.dps=40 $n result = str(mpm.sqrt(2))") is the formula in the cell B8; the result is 1.41421356237309504880168872420969807857. The corresponding Python code with conventional formatting would look like this:

from xlcalcnet import mpm
mpm.dps=40
result = str(mpm.sqrt(2))

=APY0("from scipy.integrate import quad $n def integrand(x, a, b): return a*x**2 + b $n a = 2.1; b = 1.1;  $n I = quad(integrand, 0, 1, args=(a,b))   $n result = str(I) ") is the formula in the cell B12; the result is the tuple (1.8000000000000003, 1.998401444325282e-14), where the first item is the value of the integral and the second item is the error estimate. The corresponding Python code with conventional formatting would look like this:

from scipy.integrate import quad
def integrand(x, a, b): return a*x**2 + b
a = 2.1; b = 1.1;
I = quad(integrand, 0, 1, args=(a,b))
result = str(I)

=APY0("from A06_UserlibExamplesPython.B29_InferentialStatistics.C01_BasicTests1Sample import D01_StudentT_PValues $n D01_StudentT_PValues.demo_stats_student_t_1sample_test() $n result='Done'") is the formula in the cell B17; the result is a CSV file which is written to the Output Monitor folder. The corresponding Python code with conventional formatting would look like this:

from A06_UserlibExamplesPython.B29_InferentialStatistics.C01_BasicTests1Sample import D01_StudentT_PValues
D01_StudentT_PValues.demo_stats_student_t_1sample_test()
result='Done'

And this is the Output Monitor showing the result (with the project panel hidden)

=APY0("from A01_ExamplesPython.B18_FunctionsAndCurvesPlots.C02_BasicCurves import D01_RegularConvexPolygon  $n D01_RegularConvexPolygon.RegularConvexPolygon(OutputMode='svg') $n result='Done'") is the formula in the cell B22; the result is a SVG file which is written to the Output Monitor folder. The corresponding Python code with conventional formatting would look like this:

from A01_ExamplesPython.B18_FunctionsAndCurvesPlots.C02_BasicCurves import D01_RegularConvexPolygon
D01_RegularConvexPolygon.RegularConvexPolygon(OutputMode='svg')
result='Done'

And this is the Output Monitor showing the result (with the project panel hidden)

Managing procedures instead of functions

It is possible to call the spreadsheet functions APY0 to APY9 from LibreOffice Basic and to use this to start procedures instead of functions. A few simple examples have already been prepared to demonstrate the technique.

To access the relevant dialog, click on the XlCalcNet logo (in orange) on the main menu bar:

The following dialog box with the title Navigator for XlCalcNet will appear:

The first example, with CSV Output in “Category” and ShowTTest1 in “Subroutine”, will write a CSV file into the XlCalcNetIDE\OutputMonitor folder in the AppDataLocal folder. In order to see it, we need to open the Output Monitor application: start the Tiny IDE by clicking on its icon in the task bar, and in the main menu, click on Tools -> Start Output Monitor. With the Output Monitor application running, click OK. The CSV file will then immediately appear in the Output Monitor.

The corresponding code in the module MpFunlabLocal -> BasicAndDialogs is shown below:

Sub ShowTTest1()
    REM Output (a .csv file) is shown in output monitor
    fa = createUnoService("com.sun.star.sheet.FunctionAccess")
    Code = "from A06_UserlibExamplesPython.B29_InferentialStatistics.C01_BasicTests1Sample import D01_StudentT_PValues;"
    Code = Code + "D01_StudentT_PValues.demo_stats_student_t_1sample_test(); result='Done';"
    ResultArray = fa.callFunction("APY0", Array(Code))
    result = ResultArray(0)(0)
End Sub

The second example, with SVG Output in “Category” and ShowPolygon in “Subroutine”, will write a SVG file into the XlCalcNetIDE\OutputMonitor folder in the AppDataLocal folder. With the Output Monitor application running (see above), click OK. The SVG file will then immediately appear in the Output Monitor.

The corresponding code in the module MpFunlabLocal -> BasicAndDialogs is shown below:

Sub ShowPolygon()
    REM Graphics is shown in output monitor
    fa = createUnoService("com.sun.star.sheet.FunctionAccess")
    Code = "from A01_ExamplesPython.B18_FunctionsAndCurvesPlots.C02_BasicCurves import D01_RegularConvexPolygon;"
    Code = Code + "D01_RegularConvexPolygon.RegularConvexPolygon(OutputMode='svg'); result='Done';"
    ResultArray = fa.callFunction("APY0", Array(Code))
    result = ResultArray(0)(0)
End Sub

Exporting and uninstalling

Exporting changes in MpfunlabLocal to MpfunlabLocal.oxt

Changes which are made in the MpfunlabLocal library are stored in the script folders of LibreOffice and are not automatically saved to MpfunlabLocal.oxt. However, they can be exported to MpfunlabLocal.oxt, so that they are available for installation (re-installation at the current LibreOffice installation or new installation for another LibreOffice installation).

To export the MpfunlabLocal library to MpfunlabLocal.oxt, follow these steps: open the Macro Editor from the main menu of LibreOffice Calc: Tools -> Macros -> Edit Macros.... In the main menu of the Macro Editor, select Dialogs -> Organize Dialogs. In the dialog “Basic Macro Organizer” select the tab “Libraries”. “Location” should be set to “My Macros & Dialogs” and “Library: Name” should be “MpFunlabLocal”. Click on Export.... In the following dialog box select “Export as extension”, and click on OK. In the dialog “Export library as extension” navigate to the documents folder, and within this folder to DataXlCalcNet\SpreadsheetAddins\LibreOfficeCalc. Select MpfunlabLocal.oxt and click on Save. Confirm to replace. In the dialog “Basic Macro Organizer” click on Close. Then close the Macro Editor.

Uninstalling MpfunlabLocal.oxt and Mpfunlab.oxt

To uninstall these two extensions properly, following the correct order is important.

First start LibreOffice with administrator privileges: Right-click on the desktop icon of LibreOffice and select Run as administrator. In the following dialog, confirm that you want to proceed. In the LibreOffice desktop, open a Calc Spreadsheet. Open the Extension dialog from the main menu:Tools -> Extensions.... In the Extension dialog select MpFunlab.oxt, click the Remove button and click on OK in the confirmation dialog. Then click on the Close button in the Extension dialog. In the dialog Restart LibreOffice, click on Restart Now. This will close LibreOffice Calc and will open the general LibreOffice desktop. Exit LibreOffice.

Now uninstall MpfunlabLocal.oxt within a normally launched LibreOffice Calc (i.e. without administrator privileges): Open the Extension dialog from the main menu: Tools -> Extensions.... In the Extension dialog select MpfunlabLocal.oxt, click the Remove button and click on OK in the confirmation dialog. Then click on the Close button in the Extension dialog. In the dialog Restart LibreOffice, click on Restart Now. This will close LibreOffice Calc and will open the general LibreOffice desktop. Exit LibreOffice.