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

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.

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.

  1. Open the sample.ods file
  2. Read the number in the A1 cell of the Sheet1 sheet
  3. Update the A1 cell by adding 1 to it
  4. Save the file
  5. 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

image.png

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

Recommended Posts

I tried to automate LibreOffice Calc with Ruby + PyCall.rb (Ubuntu 18.04)
I tried DI with Ruby
I tried to interact with Java
I tried to solve the problem of "multi-stage selection" with Ruby
I tried to reimplement Ruby Float (arg, exception: true) with builtin
I tried to get started with WebAssembly
I tried to build Ruby 3.0.0 from source
I tried to implement ModanShogi with Kinx
[Amateur remarks] I tried to automate SSL possible (self-signed certificate) with Docker-Compose
I tried to solve the tribonacci sequence problem in Ruby, with recursion.
I tried to verify AdoptOpenJDK 11 (11.0.2) with Docker image
I tried to manage struts configuration with Coggle
I tried to manage login information with JMX
I made blackjack with Ruby (I tried using minitest)
[Ruby basics] I tried to learn modules (Chapter 1)
I tried to break a block with java (1)
I tried to get the distance from the address string to the nearest station with ruby
When I tried to use a Wacom tablet with ubuntu 20.04, I didn't recognize it.
I tried what I wanted to try with Stream softly.
I tried to implement file upload with Spring MVC
I tried to read and output CSV with Outsystems
I tried to implement TCP / IP + BIO with JAVA
[Java 11] I tried to execute Java without compiling with javac
I started MySQL 5.7 with docker-compose and tried to connect
I tried to get started with Spring Data JPA
I tried to draw animation with Blazor + canvas API
I tried to implement Stalin sort with Java Collector
I want to redirect sound from Ubuntu with xrdp
roman numerals (I tried to simplify it with hash)
[Beginner's point of view] I tried to solve the FizzBuzz problem "easily" with Ruby!
I tried installing Ruby on Rails related plugin with vim-plug
I tried to make an introduction to PHP + MySQL with Docker
I tried to create a java8 development environment with Chocolatey
I tried to modernize a Java EE application with OpenShift.
I tried to increase the processing speed with spiritual engineering
[Rails] I tried to create a mini app with FullCalendar
I tried to link chat with Minecraft server with Discord API
[Rails] I tried to implement batch processing with Rake task
I tried UPSERT with PostgreSQL.
I tried BIND with Docker
I tried to verify yum-cron
I tried Jets (ruby serverless)
I tried to create a padrino development environment with Docker
I tried to get started with Swagger using Spring Boot
I tried upgrading from CentOS 6.5 to CentOS 7 with the upgrade tool
I tried to be able to pass multiple objects with Ractor
I tried to create an API to get data from a spreadsheet in Ruby (with service account)
I want to connect SONY headphones WH-1000XM4 with LDAC on ubuntu 20.04! !!
I want to add a browsing function with ruby on rails
I tried to build the environment of PlantUML Server with Docker
I tried to write code like a type declaration in Ruby
[Ruby] Tonight, I tried to summarize the loop processing [times, break ...]
I tried to build an http2 development environment with Eclipse + Tomcat
I tried to implement flexible OR mapping with MyBatis Dynamic SQL
I tried connecting to Oracle Autonomous Database 21c with JDBC Thin
I tried to make an Android application with MVC now (Java)
I tried to check the operation of gRPC server with grpcurl
I tried to make Numeron which is not good in Ruby
I tried to make a group function (bulletin board) with Rails
I tried to chew C # (indexer)
I tried using JOOQ with Gradle