Python Macros in LibreCalc

Python Macros in LibreOffice Calc

The promised land of using python macros in LibreOffice Calc was my prime motivation to ditch other spreadsheets. Somehow that notion mired in UNO¹ intricacies, further fuelled by scarcity of help available in LibreOffice community and world wide web. However since LibreOffice 7.2, things improved with scriptforge² module for python. For Basic it was available since LibreOffice 7.1. Without further ado, let’s introduce the opening act!

Python scripts organization and location³

for our concern macros may have two places to live

• LibreOffice Macros Container, depending upon the OS, it can be found at {Installation}/share/Scripts/python. Windows user may use '\' instead of '/'. One need administrative rights to store or edit macros here and are available to every user.

• My Macros container is accessible only by the LibreOffice user. Macros in this location are stored in the LibreOffice user profile, depending upon the OS:

  • For Windows

        %APPDATA%\LibreOffice\4\user\Scripts\python
    

  • For Linux and macOS

        $HOME/.config/libreoffice/4/user/Scripts/python
    

Python script

Let’s say we have some sales data and we want to find total sales, average sales, highest sales and lowest sales. We can use our preferred editor to write python codes. First decide where your code would be residing, for this example let’s save it in My Macros, simply type the following code into the summary.py file.

from scriptforge import CreateScriptService

doc = CreateScriptService("Calc")
bas = CreateScriptService("Basic")


def get_summary(args=None):
    sales_qty_range = bas.InputBox("Range containing sales qty data")
    total_sales_qty = doc.DSum(sales_qty_range)
    avg_sales_qty = doc.DAvg(sales_qty_range)
    highest_sales_qty = doc.DMax(sales_qty_range)
    lowest_sales_qty = doc.DMin(sales_qty_range)
    summary_dict = {
        "Total Sales (SqMtr)": total_sales_qty,
        "Avg Sales (SqMtr)": avg_sales_qty,
        "Highest Sales (SqMtr)": highest_sales_qty,
        "Lowest Sales (SqMtr)": lowest_sales_qty,
    }

    first_cell = bas.InputBox("Cell address, where you want to put results")
    values = [[k, v] for k, v in summary_dict.items()]
    doc.SetArray(first_cell, values)


# display in LibreOffice as user scripts
g_exportedScripts = (get_summary,)

The script creates the get_summary function, it will ask for the cell range where the data is and cell address where you want the results.

To run this script fire up Calc:

• Enter some numeric data. for e.g.

  

• Create a button in spreadsheet, for that ensure Form Controls are available. If not go to View - Toolbars and check Form Controls option.

• Be on Design Mode, select Push Button and draw a rectangular box near your data.

  

• Come out of Design Mode by clicking it once again.

• Click Calculate Summary button you made, give him the range for sales data i.e. C5:C20 and provide cell address where your result should be i.e E5 and voila … there is the summary of your data.

  

if this makes you curious enough to explore what else can you do, check Creating Python Scripts with ScriptForge.