Manipulación de hojas de cálculo utilizando Python, Flask y Open Office SDK

Usar solo en casos extremos

Sergio Canis

Published in

The Cocktail Engineering

4 min readMar 27, 2017

London’s financial district - https://www.flickr.com/michaelduxbury

Introducción

El objetivo de este artículo es implementar una solución que posibilite la interacción con una hoja de cálculo. ¿Para qué querríamos esto? Igual una parte de nuestra aplicación necesita escribir sobre un fichero XLS y preferimos hacerlo desde un microservicio. O incluso disponemos un XLS de consulta de datos y en vez de extraerlos por la complejidad de dicho fichero, preferimos escribir datos y consultar sobre él. Estas cosas pasan.

Buscando herramientas

Para ello necesitaremos una herramienta para interactuar con una hoja de cálculo. Dado que este ejemplo funciona sobre sistemas Linux, la opción de un SDK de MS Office queda descartada. Para este ejemplo usaremos el SDK de Open Office, que dispone de conectores sobre C++, Java, Python, CLI, StarBasic y OLE. Dentro de este abanico de posiblidades elegiremos Python.

Elegido el lenguaje solo nos quedaría ver la manera de interactuar. Para hacer un ejemplo más completo; usaremos un DSL que usaremos para exponer el servicio via HTTP, en este caso usaremos Flask. Por lo tanto, las piezas claves serán:

Enunciado del problema

Dado un fichero XLS que se utiliza para determinar un identificador único definido por dos datos Edad Actual y Fecha Actual (cuyas celdas son A1 y A2). Dichas celdas son entradas de datos, y mediante una serie de fórmulas internas se logrará un resultado que se volcará en A3. Las fórmulas son dignas del mejor departamento de la NASA y son totalmente imposibles de extraer. Como dificultad adicional, comentar que este fichero varía cada 5 minutos, por lo tanto se deben revisar las fórmulas

Lo último que se nos pasa por la cabeza es usar el propio XLS, pero visto las posibles opciones:

¿Y si codificamos las fórmulas? Varían cada 5 minutos, por lo que descartamos esta opción.
¿Y si extraemos las fórmulas a cada subida de fichero? Varían en forma, por lo que una estructura inicial definida es descartada.

La opción que más cuadra es interactuar con el propio XLS, recargando el fichero cada 5 minutos.

Instalación

En el caso de Python, las pruebas las hemos realizado sobre la versión 2.7.13; pero valdría cualquier versión compatible con Flask.

Para el SDK de Open Office, necesitaremos tener instalado Open Office y el correspondiente SDK. Además necesitaremos tener instalado el JRE de Java, para ciertas funciones:

$ sudo aptitude install openoffice.org openoffice.org-dev openoffice.org-dev-doc

La libreria que necesitamos de Python para conectarnos a Open Office se llama pyoo. Para instalarlo usaremos el administrador de paquetes de python pip:

$ easy_install pip
$ pip install pyoo

Implementación

Esto lo traduciremos en un servicio que a partir de dos parámetros, nos devuelva un resultado (la ruta GET /calculate_secret_data parece adecuada). Empecemos creando el esqueleto de cualquier aplicación Flask tipo con las librerias que usaremos:

import sys
from flask import Flask
from flask import request
application = Flask(__name__)
import pyoo
import os

@app.route("/calculate_secret_data", methods=['GET']))
def hello():
    return "Hello World!"

if __name__ == "__main__":
    app.run()

Este servicio deberá leer un XLS que le pasaremos por parámetro que almacenaremos en spreadsheet_path. Lo primero de todo es lograr una conexión con la hoja de cálculo:

spreadsheet_dir_path = sys.argv[1]
desktop = pyoo.Desktop('localhost', 2002)
doc = desktop.open_spreadsheet(spreadsheet_path)

Para evitar problemas será interesante controlar posibles errores, pese a que todo quede más feo:

try:
  spreadsheet_dir_path = sys.argv[1]
  spreadsheet_path = spreadsheet_dir_path + str(os.listdir(spreadsheet_dir_path)[-1])
  desktop = pyoo.Desktop('localhost', 2002)
  doc = desktop.open_spreadsheet(spreadsheet_path)
  
except OSError:
  print("No such file or directory: ["+ sys.argv[1] +"]")
  quit()  
  
except:
  print("Where is OpenOffice connection? This is not in 0.0.0.0:2002...")
  quit()
      
shutdown_server()
    return 'Server shutting down...'

El lugar donde haremos la magia será en la ruta GET /calculate_secret_data. En dicha ruta controlaremos los dos parámetros e interactuaremos con la hoja de cálculo. Luego leeremos los resultados y los devolveremos.

@application.route("/calculate_secret_data", methods=['GET'])
def calculate_secret_data():
    params = {
      'current_age': request.args.get('current_age'),
      'current_date': request.args.get('current_date'),
    }
    
    sheet = doc.sheets[0]
    sheet[1,2].value = int(params['current_age'])
    sheet[2,2].value = int(params['current_data'])
    results = sheet_out[1,3].valuereturn str(results)

Y ya que estamos en faena, añadimos una ruta para apagar el servicio de manera correcta:

def shutdown_server():
    func = request.environ.get('werkzeug.server.shutdown')
    if func is None:
        raise RuntimeError('Not running with the Werkzeug Server')
    func()@application.route('/shutdown', methods=['POST'])
def shutdown():
    try:
      doc.close()
      
    except:
      print("Doc cannot be close (where is Open Office?)")
      print("Forcing shutdown: Secret Data Calculator API cannot find Open Office doc to Close")

Al final, nos debería quedar un código similar al del siguiente gist:

Pruebas y resultados

Lo primero que debemos hacer es arrancar un proceso de Open Office en background, para ello nos situaremos en el directorio donde se encuentre el binario de OO. Esto posibilitará que al lanzar nuestro pequeño servicio, pueda entablar conversación con dicha hoja de cálculo.

sudo soffice -headless -nologo -nofirststartwizard -accept="socket,host=localhost,port=2002;urp;StarOffice.ServiceManager"

A continuación lanzamos el servicio de Python:

python oo_secret_dat.py spreadsheet.xls

En este punto, si no ha habido ningún problema, tendremos nuestro servicio con una conexión permanente a través del puerto 2002 con la hoja de cálculo spreadsheet.xls. Para probar nuestros servicio lanzamos:

curl 0.0.0.0:5000/calculate_secret_data/?current_age=2&current_time=1490213438

Conclusiones

Durante este artículo se ha tratado de explicar, instalar e implentar una solución a un problema poco común: la interacción sobre una hoja de cálculo a bajo nivel.

Para nuestro día a día puede que las usemos mucho, pero los SDK que aportan diferentes herramientas (como en este caso Open Office) nos pueden sacar de un apuro para problemas puntuales.

E infintas cosas a mejorar: Gestión de colas de acceso, publicación del microservicio, OAuth…