jupyter-notebookpiplibreoffice-calcpyuno

Setting up a python environment to control libre calc

I have a model in Libre Calc which I would like to access/control programmatically via python and Jupyter Notebook.

The reason for doing so being that I would like to use python to download data which can be fed to the Calc model, then have the model output returned to jupyter notebook where I can perform additional analysis (automated).

I have installed libre office and Jupyter notebook directly via the terminal.

I have set libre office to listening mode.

When I do this outside of a virtual environment, I can communicate with Calc via jupyter notebook (it works successfully).

The issue I am facing is that I cannot install any of my other required packages e.g. pandas, yfinance, scipy etc via pip - I receive the error message about "external environment" and the suggestion to use a virtual environment.

I am using a raspberry pi 4 running raspberry pi OS (64bit). The latest version of libre office and python3.

I have Tried creating a virtual environment and installing jupyter notebook within it, along with the various packages. When I acces jupyter notebook, I can import the packages e.g. pandas, without issue. However, I can no longer communicate with libre Calc and receive an error re Uno module not being found.

I don't believe I need to install uno/pyuno as this worked fine without having do so outside of the virtual environment (I believe this is preinstalled with libre office - pyuno bridge).

I have run import sys / sys.executable and says.path in both terminal (virtual environment) and Jupyter notebook and they are identical. I have also run pip list in both jupyter and terminal and I can see the environments are identical.

So I'm left with being able to connect to libre office outside of a virtual environment but without access to the required packages, or have access to the packages within a virtual environment but not able to connect to libre office.

Any thoughts would be most appreciated (I'm a novice so apologies in advance).

Solution

  • I have found the missing link no sooner had i clicked "submit". For the benefit of anyone faced with a similar problem, i have set out the steps i have taken to create my desired virtual environment. The missing link was oooenv https://pypi.org/project/oooenv/. I can now access Libre Calc programatically using Python with the Jupyter Notebook IDE, and access packages installed via pip. Hopefully this works for others too.

    From a fresh install of Raspberry Pi OS 64 Bit on Raspberry Pi 4.

    Enable remote desktop

    sudo apt-get install xrdp # allows for remote access via Microsoft Remote Desktop app

    Update/Upgrade software/firmware

    sudo apt-get update

sudo apt-get upgrade

sudo apt-get autoclean

    Install Libre Office

    sudo apt-get install libreoffice

    sudo apt-get install libreoffice-script-provider-python # Shouldn't be required as should already be installed with Libre Office

    Place Libre Office in headless listening mode (via terminal outside of virtual environment)

    libreoffice --headless --invisible --norestore --nolockcheck --accept="socket,host=localhost,port=2002;urp;"

    Create Virtual Environment

    python3 -m venv myenv 'source myenv/bin/activate' # activate virtual environment (type "deactivate" to exit VE)

    Install Jupyter Notebook

    pip install jupyter # within virtual environment

    Install wider packages as required

    pip install pandas numpy seaborn matplotlib scipy # within the virtual environment

    Add Libre UNO links to virtual environment (within virtual environment)

    pip install oooenv

    oooenv cmd-link -a

    Note that Pyuno / Uno / Unotools packages should not be installed - these will conflict with the PYUNO-Bridge that is pre-installed with Libre Office.

    Access Jupyter Notebook

    jupyter notebook # within virtual environment

    Jupyter Notebook Script for accessing Libre Calc and creating a new document

    import uno

    from com.sun.star.beans import PropertyValue

    from com.sun.star.uno import Exception

    Connect to LibreOffice Calc

    localContext = uno.getComponentContext()

    resolver = localContext.ServiceManager.createInstanceWithContext("com.sun.star.bridge.UnoUrlResolver", localContext)

    ctx = resolver.resolve("uno:socket,host=localhost,port=2002;urp;StarOffice.ComponentContext")

    smgr = ctx.ServiceManager

    desktop = smgr.createInstanceWithContext("com.sun.star.frame.Desktop", ctx)

    Create a new Calc document

    calc_doc = desktop.loadComponentFromURL("private:factory/scalc", "_blank", 0, ())

    Access the active sheet

    sheets = calc_doc.getSheets() sheet = sheets.getByIndex(0)

    Do something with the sheet (e.g., write data)

    cell = sheet.getCellByPosition(0, 0) cell.setString("Hello, from Jupyter!")

    Save the document

    calc_doc.storeAsURL("file:///home/user/Desktop/output_file.ods", ())

    Close the document

    calc_doc.close(True)