Introducing New LibrePythonista Extension. The power of python now inside of Calc

English
, ,
#1

Introducing LibePythonista

This is the official announcement of LibrePythonista. An extension that brings the full power of python into of Calc Spreadsheets.

Finally Python inside of LibreOffice Calc.

LibrePythonista brings the power of Pandas, Matplotlib and much more to LibreOffice Calc.

Watch the Introduction Video

Highly recommened to watch first.

Watch the video

Link

Screenshot

plot
plot1011Ă—796 137 KB

Details

All python code is executed on your local computer. This can alleviate many concerns around data privacy as your data does not leave your computer to be processed.

Using LibrePythonista is it possible to create Data frame’s, Series, custom Graphs an much more directly in a spreadsheet.

Resources:

Download Extension here

Repository is found on GitHub

YouTube Channel is found at @LibrePythonIsta

6 Likes
#2

Beautiful work :grinning: :smiling_face_with_three_hearts:

1 Like
#4

Good News! As of verson 0.2.0 LibrePythonista now has a pip python package installer build in.

#5

Surely an example something.ods wouldn’t work wiothout an iostall of the extension, but would show how it can be used “Inside of Calc”.
Are there additional functions usable in formulas?
Or must py code always be called explicitly via a menu or the like?

#6

I am not super clear on what you mean here. I am assuming you mean that can you have a spreadsheet that show how to use the extension without having the extension installed. If this is the case then currently that answer is no, unless you export as pdf or print of course.

LibrePythonista is early in development. There is near limitless room for expansion for the extension.


Already the PY.C formula, which is currently the only formula for LibrePythonista, can act like a custom formula. It can return multiple types of information such as, ploted images, numeric values, text values, DataFrames, Series and Arrays. The PY.C function is a dynamic function. However, much more functionality will be added to LibrePythonista over time.

#7

I would prefer a spreadsheet with different use cases to demonstrate some of the capabilities with the extension being installed. To me, the demo in your video is not of interest, because I use Base (SQL) with “data frames”.

#8

There is not much in this area yet. Currently LibrePythonista is Calc centric. This is expected to change and get extended to LibreOffice Apps over time.


PS. Along with Named Ranges, LibrePythonista can access DataBase Ranges. This should allow for bringing Data from database sources into Calc and make it available to LibrePythonista.


PSS. LibrePythonista is now also a PIP python package installer. This opens a lot of other possibilities.

#9

OK, one idea that could be useful. Normalized data from a database or cell range:

May Carot 9
May Apple 5
May Pear 4
Jun Carot 3
Jun Apple 7
Jun Pear 8

3 data series in a cross table as needed for a chart:

    Apple Carot Pear
May 5     9     4
Jun 7     3     8

Can you transform one into the other?
I would do that with INDEX and MOD(ROW();3)

#10

I am thiniking yes.
See https://youtu.be/AQLuSNHhUY4?si=QN84_MfS_hMF_Kav&t=910
That speaks about grouping.


Unfortunately, I am not an experted in Pandas, Calc, or Base. However, I have a lot of experience building the tools such as LibrePythonista to extend the tools. You might benifit from consulting with someone who is really good with Pandas and the Libraries that go with it.

#11

assuming a selected Cellrange with:

month product amount
May Carot 9
May Apple 5
May Pear 4
Jun Carot 3
Jun Apple 7
Jun Pear 8

with pandas, but without LibrePythonista :

import pandas as pd

doc = XSCRIPTCONTEXT.getDocument()
data = doc.CurrentSelection.DataArray

df = pd.DataFrame(index=data[0],
                  data=tuple(zip(*data[1:]))).T

table = pd.pivot_table(df, index='month',
                       columns='product',
                       values='amount',
                       aggfunc='sum')
table

table

transform back to calc-DataArray:

out = [('', *tuple(table))] # header
for row in table.itertuples():
    out.append(tuple(row)) # the data
sheet = doc.CurrentController.ActiveSheet
sheet[ :len(out), 5 : 5+len(out[0])].DataArray = out
#12

OK, so I stick to StarBasic or formulas, because neither the extension nor the Python macro fills out my cell range, unless I wrap the Python code into a Calc add-in.

#13

thats not exactly True! see the last 5 loc above.

and yes, as long as you do not use an interactive tool like “jupyter-notebook”, you have to bundle this code into function definitions in a meaningful way (just like BASIC in the Basic.IDE ):

import pandas as pd

def main():
    doc = XSCRIPTCONTEXT.getDocument()
    data = doc.CurrentSelection.DataArray
    
    df = pd.DataFrame(index=data[0],
                      data=tuple(zip(*data[1:]))).T
    
    table = pd.pivot_table(df, index='month',
                           columns='product',
                           values='amount',
                           aggfunc='sum')
    out = [('', *tuple(table))]
    for row in table.itertuples():
        out.append(tuple(row))
    sheet = doc.CurrentController.ActiveSheet
    sheet[:len(out), 5:5+len(out[0])].DataArray = out
#14

Pushing the </> inserts =PY.C(SHEET();CELL("ADDRESS")) without any control.

Version: 24.2.6.2 (X86_64) / LibreOffice Community
Build ID: ef66aa7e36a1bb8e65bfbc63aba53045a14d0871
CPU threads: 4; OS: Linux 5.15; UI render: Skia/Raster; VCL: x11
Locale: de-DE (de_DE.UTF-8); UI: en-US
Calc: threaded

#15

Is the macro securitys set and what OS are you using please.

#16

I turned security off and Linux 5.15 (Ubuntu 20.4).

#17

Thanks, on some systems I found that Calc was not completely shut down which caused some issues. Have you tried rebooting or at least make sure that soffice.bin is not running in System Monitor.


Also logging can be set to debug which outputs a lot more information to the log file. I am create a short tutorial on how to change and read log files for LibrePythonista.

#18

Starting LibreOffice:

$  libreoffice24.2
2024-10-03 17:44:42,655 - INFO - libre_pythonista.install.requirements_check: Package typing-extensions 4.12.2 already installed. Requirements met for constraints: >=4.12.2
2024-10-03 17:44:42,656 - INFO - libre_pythonista.install.requirements_check: Package verr 1.1.2 already installed. Requirements met for constraints: >=1.1.2
2024-10-03 17:44:42,658 - INFO - libre_pythonista.install.requirements_check: Package pandas 2.0.3 already installed. Requirements met for constraints: >=2
2024-10-03 17:44:42,660 - INFO - libre_pythonista.install.requirements_check: Package odfpy 1.4.1 already installed. Requirements met for constraints: >=1.4
2024-10-03 17:44:42,662 - INFO - libre_pythonista.install.requirements_check: Package ooo-dev-tools 0.47.17 already installed. Requirements met for constraints: >=0.47.17
2024-10-03 17:44:42,663 - INFO - libre_pythonista.install.requirements_check: Package matplotlib 3.7.5 already installed. Requirements met for constraints: >=3.7.5
2024-10-03 17:44:42,665 - INFO - libre_pythonista.install.requirements_check: Package seaborn 0.13.2 already installed. Requirements met for constraints: >=0.13
2024-10-03 17:44:42,667 - INFO - libre_pythonista.install.requirements_check: Package sortedcontainers 2.4.0 already installed. Requirements met for constraints: >=2.4.0
2024-10-03 17:44:42,667 - INFO - uno_component: LibrePythonista execution time: 0.014 seconds

menu:LibrePy>Insert Python inserts the formula.
menu:LibrePy>Debug Log Module gives the following console ouput:

2024-10-03 17:45:30,182 - ERROR - PyModule: Error initializing module
Traceback (most recent call last):
  File "/home/andreas/.config/libreoffice/4/user/uno_packages/cache/uno_packages/lu1791561zkq26.tmp_/LibrePythonista.oxt/pythonpath/libre_pythonista_lib/code/py_module.py", line 71, in _init_mod
    exec(code, self.mod.__dict__)
  File "<string>", line 3, in <module>
  File "/opt/libreoffice24.2/program/uno.py", line 346, in _uno_import
    return _builtin_import(name, *optargs, **kwargs)
  File "/home/andreas/.local/lib/python3.8/site-packages/matplotlib/__init__.py", line 124, in <module>
    import numpy
  File "/opt/libreoffice24.2/program/uno.py", line 346, in _uno_import
    return _builtin_import(name, *optargs, **kwargs)
  File "/home/andreas/.local/lib/python3.8/site-packages/numpy/__init__.py", line 141, in <module>
    from . import core
  File "/opt/libreoffice24.2/program/uno.py", line 425, in _uno_import
    raise uno_import_exc
  File "/opt/libreoffice24.2/program/uno.py", line 346, in _uno_import
    return _builtin_import(name, *optargs, **kwargs)
  File "/home/andreas/.local/lib/python3.8/site-packages/numpy/core/__init__.py", line 49, in <module>
    raise ImportError(msg)
ImportError: 

IMPORTANT: PLEASE READ THIS FOR ADVICE ON HOW TO SOLVE THIS ISSUE!

Importing the numpy C-extensions failed. This error can happen for
many reasons, often due to issues with your setup or how NumPy was
installed.

We have compiled some common reasons and troubleshooting tips at:

    https://numpy.org/devdocs/user/troubleshooting-importerror.html

Please note and check the following:

  * The Python version is: Python3.8 from "/usr/bin/python3"
  * The NumPy version is: "1.24.4"

and make sure that they are the versions you expect.
Please carefully study the documentation linked above for further help.

Original error was: No module named 'numpy.core._multiarray_umath' (or 'numpy.core._multiarray_umath.add_docstring' is unknown)
 (or '.core' is unknown)

All the other menu items do nothing. No dialog, no message box, no console output.

#19

OK, that is very helpful. Clearly a Numpy issue. I have seen similar issue on windows before I included _bz2 with the installer.


I am going to see if I can set up a Virtual Machine with your specs.
Is there anything special I should know. Are you using Snap, Flatpak, AppImage or apt installed version of LibreOffice?


In the mean time you can try reinstalling numpy if the Pip Installer will pop up.
Make sure you have at least version 0.3.0 of the extension installed.
Next LibrePy -> Pip -> Install Pip Package input numpy and check the Force Install checkbox.
If that does not work you can try a different version of numpy. Eg install numpy>=2

#20

I use the Debian packages downloaded from libreoffice.org.

This menu item does nothing.

#21

Does that mean you installed via apt install ...? The log file suggest this is the case.

That’s really too bad. I will see if I can reproduce the error and see if it is possible to allow those menus to continue to work.

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