This is the 20th day article of LibreOffice Advent Calendar 2020.

The previous article was by nogajun's "[If you want to edit XML in a table or write it to CSV, you can use the" XML source "of Libreoffice Calc" (http://www.nofuture.tv/diary/20201219.html#p01).

PyCall.rb is a bridge library for using Python from Ruby. See below for details.

mrkn/pycall.rb: Calling Python functions from the Ruby language -(2017) Ruby-Let's analyze data in Ruby using the Python bridge library "PyCall"! (1/3): CodeZine --Explanatory article by mrkn, the author of PyCall.rb

When I googled with "Ruby PyCall LibreOffice", there seemed to be no cases yet, so I tried it.

Preparation

I want to try it in a clean environment, so I use Docker. I think it's almost the same on plain Ubuntu 18.04, but please replace apt ~ with sudo apt ~ as appropriate.

Rough Dockerfile.

FROM ubuntu:18.04

RUN apt-get update
RUN apt-get -y install libreoffice-calc
RUN apt-get -y install ruby
RUN apt-get -y install build-essential

WORKDIR /root/work

Image creation + container startup. After that, it is the work inside the container except for editing the file.

docker build -t libo_pycall:trial .
docker run --rm -it -v "$(pwd):/root/work/" libo_pycall:trial bash

Check the version.

root@2377c5b80dfb:~/work# python3 -V
Python 3.6.9
root@2377c5b80dfb:~/work# ruby -v
ruby 2.5.1p57 (2018-03-29 revision 63029) [x86_64-linux-gnu]

This time, I will try using plain Python/Ruby without using pyenv, rbenv, etc.

Let's write in Python first

Before trying Ruby + PyCall, let's first write a sample in Python and run it. By the way, I'm not familiar with Python, and I'm imitating it. Perhaps there is a part that is doing something strange.

Try the following operations.

Open the sample.ods file
Read the number in the A1 cell of the Sheet1 sheet
Update the A1 cell by adding 1 to it
Save the file
Close the file

# sample.py

import uno

def get_desktop():
  local_ctx = uno.getComponentContext()
  resolver = local_ctx.ServiceManager.createInstanceWithContext("com.sun.star.bridge.UnoUrlResolver", local_ctx)
  ctx = resolver.resolve("uno:socket,host=localhost,port=2002;urp;StarOffice.ComponentContext")
  return ctx.ServiceManager.createInstanceWithContext("com.sun.star.frame.Desktop", ctx)

def open_ods_file(path):
  desktop = get_desktop()
  url = uno.systemPathToFileUrl(path)
  return desktop.loadComponentFromURL(url, "_blank", 0, ())

doc = open_ods_file("/root/work/sample.ods")
sheet = doc.Sheets.getByName("Sheet1")

cell = sheet.getCellByPosition(0, 0)
n = int(cell.getFormula())
print(n)
cell.setFormula(int(n) + 1)

doc.store()
doc.dispose()

Launch a LibreOffice instance (run only once at the beginning)

soffice --headless "--accept=socket,host=localhost,port=2002;urp;" &

Run sample.py

python3 sample.py

If you run sample.py multiple times and the number increases by 1 each time you run it, you are successful.

Install PyCall

For the time being, gem install.

gem install --pre pycall

I failed.

Fetching: pycall-1.3.1.gem (100%)
Building native extensions. This could take a while...
ERROR:  Error installing pycall:
        ERROR: Failed to build gem native extension.

    current directory: /var/lib/gems/2.5.0/gems/pycall-1.3.1/ext/pycall
/usr/bin/ruby2.5 -r ./siteconf20201217-110-g260nd.rb extconf.rb
mkmf.rb can't find header files for ruby at /usr/lib/ruby/include/ruby.h

extconf failed, exit code 1

Gem files will remain installed in /var/lib/gems/2.5.0/gems/pycall-1.3.1 for inspection.
Results logged to /var/lib/gems/2.5.0/extensions/x86_64-linux/2.5.0/pycall-1.3.1/gem_make.out

I was told that ruby.h could not be found. Install ruby-dev and try again.

apt install ruby-dev
gem install --pre pycall

Successful installation.

root@fac43278ae75:~/work# ruby -r pycall -e 'p PyCall::VERSION'
"1.3.1"

Check lightly

Let's take a quick look at the code example in README of pycall.rb.

root@2377c5b80dfb:~/work# irb --prompt simple
>> require 'pycall/import'
=> true
>> include PyCall::Import
=> Object
>> pyimport :math
Traceback (most recent call last):
  File "/var/lib/gems/2.5.0/gems/pycall-1.3.1/lib/pycall/python/investigator.py", line 1, in <module>
    from distutils.sysconfig import get_config_var
ModuleNotFoundError: No module named 'distutils.sysconfig'
Traceback (most recent call last):
        8: from /usr/bin/irb:11:in `<main>'
        7: from (irb):3
        6: from /var/lib/gems/2.5.0/gems/pycall-1.3.1/lib/pycall/import.rb:18:in `pyimport'
        5: from /var/lib/gems/2.5.0/gems/pycall-1.3.1/lib/pycall.rb:62:in `import_module'
        4: from /var/lib/gems/2.5.0/gems/pycall-1.3.1/lib/pycall/init.rb:16:in `const_missing'
        3: from /var/lib/gems/2.5.0/gems/pycall-1.3.1/lib/pycall/init.rb:35:in `init'
        2: from /var/lib/gems/2.5.0/gems/pycall-1.3.1/lib/pycall/libpython/finder.rb:41:in `find_libpython'
        1: from /var/lib/gems/2.5.0/gems/pycall-1.3.1/lib/pycall/libpython/finder.rb:36:in `find_python_config'
PyCall::PythonNotFound (PyCall::PythonNotFound)

I got an error with pyimport. Install python3-distutils and try again.

apt install python3-distutils

This time it was successful.

root@2377c5b80dfb:~/work# irb --prompt simple
>> require 'pycall/import'
=> true
>> include PyCall::Import
=> Object
>> pyimport :math
=> :math
>> math.sin(math.pi / 4) - Math.sin(Math::PI / 4)           
=> 0.0
>>

Ported sample script to Ruby

Now let's rewrite the script written in Python in Ruby.

# sample.rb

require "pycall/import"
include PyCall::Import

pyimport "uno"

def get_desktop
  local_ctx = uno.getComponentContext()
  resolver = local_ctx.ServiceManager.createInstanceWithContext("com.sun.star.bridge.UnoUrlResolver", local_ctx)
  ctx = resolver.resolve("uno:socket,host=localhost,port=2002;urp;StarOffice.ComponentContext")
  ctx.ServiceManager.createInstanceWithContext("com.sun.star.frame.Desktop", ctx)
end

def open_ods_file(path)
  desktop = get_desktop()
  url = uno.systemPathToFileUrl(path)
  desktop.loadComponentFromURL(url, "_blank", 0, [])
end

doc = open_ods_file("/root/work/sample.ods")
sheet = doc.Sheets.getByName("Sheet1")

cell = sheet.getCellByPosition(0, 0)
n = cell.getFormula().to_i
puts n
cell.setFormula(n + 1)

doc.store()
doc.dispose()

In the Python version, I passed an empty tuple as the third argument like desktop.loadComponentFromURL (url," _blank ", 0, ()), but I made it an empty array here. Other than that, it's almost the same as the Python version.

Reference: LibreOffice: XComponentLoader Interface Reference

Run.

ruby sample.rb

As with the Python version, it now moves by 1 each time it is run. It looks okay.

Sample 2

As another example, the contents of sheet Sheet2

I wrote a script to dump to standard output.

# sample2.rb

require "json"

require "pycall/import"
include PyCall::Import

pyimport "uno"

def get_desktop
  local_ctx = uno.getComponentContext()
  resolver = local_ctx.ServiceManager.createInstanceWithContext("com.sun.star.bridge.UnoUrlResolver", local_ctx)
  ctx = resolver.resolve("uno:socket,host=localhost,port=2002;urp;StarOffice.ComponentContext")
  ctx.ServiceManager.createInstanceWithContext("com.sun.star.frame.Desktop", ctx)
end

def open_ods_file(path)
  desktop = get_desktop()
  url = uno.systemPathToFileUrl(path)
  desktop.loadComponentFromURL(url, "_blank", 0, [])
end

def all_select_cursor(sheet)
  range = sheet.getCellRangeByName("A1")
  cursor = sheet.createCursorByRange(range)
  cursor.gotoEndOfUsedArea(true)
  cursor
end

def get_col_index_max(sheet)
  all_select_cursor(sheet).Columns.Count - 1
end

def get_row_index_max(sheet)
  all_select_cursor(sheet).Rows.Count - 1
end

doc = open_ods_file("/root/work/sample.ods")
sheet = doc.Sheets.getByName("Sheet2")

(0..get_row_index_max(sheet)).each do |ri|
  cols = 
    (0..get_col_index_max(sheet)).to_a.map do |ci|
      cell = sheet.getCellByPosition(ci, ri)
      cell.getFormula()
    end
  puts JSON.generate(cols)
end

doc.dispose()

Run.

root@fac43278ae75:~/work# ruby sample2.rb 
func=xmlSecCheckVersionExt:file=xmlsec.c:line=188:obj=unknown:subj=unknown:error=19:invalid version:mode=abi compatible;expected minor version=2;real minor version=2;expected subminor version=25;real subminor version=26
["id","name","score","note"]
["1","foo","12.3","hoge"]
["2","bar","-12.3",""]

Yoshi!

in conclusion

So, I got a little stuck around the installation, but I found that if it was a simple one, it would work smoothly. PyCall.rb That's great ...

I also wrote the following article for LibreOffice Advent Calendar 2020. Please also.

-Written Lisp interpreter (mal) in LibreOffice Basic -Ported a naive homebrew language compiler to LibreOffice Basic

Relation

-Sample 2019 to read and write LibreOffice Calc fods file with JRuby

I tried to automate LibreOffice Calc with Ruby + PyCall.rb (Ubuntu 18.04)