Manipulando XLSX em Python

Captura de tela do LibreOffice Calc mostrando o conteúdo do arquivo 'aleatórios.xlsx'

Algo bem simples e que também serve como uma sequência indireta da publicação sobre manipulação de arquivos CSV complementando-a com a manipulação de arquivos no formato XLSX. Aqueles mesmos utilizados primariamente pelo Microsoft Excel mas também suportados por outras aplicações de planilha eletrônica.

Arquivos XLS e XLSX

O Microsoft Excel foi a segunda¹ planilha eletrônica de cálculo desenvolvida pela Microsoft e lançada no ano de 1985 para Macintosh, em 1987 para Windows, em 1989 para OS/2 e em algum momento da década de 1990 perdeu sua identidade como produto e se tornou um componente do Microsoft Office. O formato padrão de arquivos para o armazenamento das planilhas no Excel era o XLS, um arquivo binário proprietário, até o lançamento da versão 2007 quando foi introduzido o XLSX, um formato aberto baseado em XML e especificado pela normal ISO/IEC 29500-2:2012.

(¹) A primeira foi a Multiplan lançada em agosto de 1982 originalmente para CP/M e depois para diversas arquiteturas de 8-bit, Macintosh e até MS-DOS. Uma curiosidade dela é o uso da notação de R«linha»C«coluna» ao invés para fazer referências às células, ou seja, nela a célula “A1” é a “R1L1”.

OpenPyXL

O OpenPyXL é uma biblioteca que permite manipular arquivos XLSX — lembrando que ele NÃO suporta o formato binário proprietário do XLS² — e para instalá-la use.

$ python3 -m venv py3
$ source py3/bin/activate
(py3) pip install openpyxl
Collecting openpyxl
  Downloading openpyxl-3.0.6-py2.py3-none-any.whl (242 kB)
     |████████████████████████████████| 242 kB 1.3 MB/s 
Collecting et-xmlfile
  Downloading et_xmlfile-1.0.1.tar.gz (8.4 kB)
Collecting jdcal
  Downloading jdcal-1.4.1-py2.py3-none-any.whl (9.5 kB)
Using legacy setup.py install for et-xmlfile, since package 'wheel'
is not installed.
Installing collected packages: et-xmlfile, jdcal, openpyxl
    Running setup.py install for et-xmlfile … done
Successfully installed et-xmlfile-1.0.1 jdcal-1.4.1 openpyxl-3.0.6

E pronto!

(²) Para a leitura dos arquivos XLS binários você pode usar o Pandas.

Criando um arquivo XLSX

Começando com um exemplo simples de como criar uma planilha diretamente usando Python.

#!/usr/bin/env python3
from random import randint
from openpyxl import Workbook

RAND = (1, 100)
ROWS, COLS = 10, 10

wb = Workbook()
ws = wb.active

for r in range(1, ROWS+1):
    for c in range(1, COLS+1):
        ws.cell(row=r, column=c, value=randint(*RAND))

ws.title = "Aleatório"
wb.save("aleatórios.xlsx")

O programa começa carregando a função randint() da biblioteca padrão e também a classe Workbook do OpenPyXL. Então é criada uma pasta de trabalho (workbook) com o nome de wb e associando em ws a planilha (worksheet) que se encontra ativa — as pastas de trabalho sempre são criadas com uma planilha que, claro, é a que está ativa.

Dois laços for cuidam de preencher uma área de 10 colunas por 10 linhas com valores aleatórios e por questões de praticidade preferi usar uma notação numérica através de .cell() , bem mais direta, do que o formato convencional de coluna e linha com letras e números.

E no final o título da planilha é renomeada para “Aleatório” e a pasta de trabalho salva como o arquivo “aleatórios.xlsx”.

Lendo um arquivos XLSX

A leitura de um XLSX é bastante simples.

#!/usr/bin/env python3
from colorama import Back, Fore, init
from openpyxl import load_workbook

ROWS, COLS = 10, 10
WORKBOOK, WORKSHEET = "aleatórios.xlsx", "Aleatório"

wb = load_workbook(WORKBOOK)
ws = wb[WORKSHEET]

init()

for r in range(1, ROWS + 1):
    print(
        Fore.CYAN if r % 2 else Fore.MAGENTA,
        " | ".join(
            [
                "{:5d}".format(
                    ws.cell(row=r, column=c).value
                )
                for c in range(1, COLS + 1)
            ]
        ),
        Fore.RESET, sep=""
    )

Ao invés de usar a classe Workbook, uso a função load_workbook() e é ela quem cuida de carregar o conteúdo do arquivo XLSX, a pasta de trabalho, para a memória. Como já sabemos o nome da planilha a selecionamos diretamente pelo nome, caso contrário poderíamos usar o conteúdo de sheetnames:

ws = wb[wb.sheetnames[0]]

Para selecionar o nome da primeira planilha da pasta de trabalho que foi carregada.

Depois disto um laço for cuida de iterar pelas linhas enquanto que uma lista abrangente faz o mesmo com as colunas. Uma combinação de print e join() se encarrega de formatar cada linha a ser impressa, inclusive colorindo as linhas ímpares de ciano e as pares de magenta.

E antes de executar, um último detalhe…

$ pip install colorama
Collecting colorama
   Using cached colorama-0.4.4-py2.py3-none-any.whl (16 kB)
 Installing collected packages: colorama
 Successfully installed colorama-0.4.4

O colorama é usado para definir as cores das linhas e também de garantir que isto funcione corretamente tanto em sistemas POSIX quanto em Windows e é o motivo do init() solitário na linha 11.

$ python retrieve_worksheet.py
    4 |    52 |    16 |    63 |    75 |    25 |    40 |    79 |    52 |    52
   66 |    53 |    64 |    21 |   100 |    65 |    35 |    95 |    72 |    97
   37 |    25 |    14 |    23 |     4 |    33 |    13 |    63 |    62 |    79
   51 |    35 |    93 |    29 |    79 |    73 |    18 |    62 |    31 |    76
   92 |     6 |    26 |    68 |    41 |    10 |     3 |    93 |    93 |    36
   37 |    89 |    93 |    64 |    92 |    17 |    34 |    24 |    65 |    48
   71 |    44 |    57 |    80 |    35 |    13 |    81 |    64 |    71 |    33
   56 |    90 |    84 |    14 |    97 |    28 |    83 |     2 |     8 |    57
    9 |    53 |    44 |    69 |    68 |    19 |    62 |    14 |    62 |     7
   52 |    27 |    17 |    40 |    92 |    61 |    37 |    71 |    73 |     1

E pronto!

Criando um XLSX a partir de um CSV

Que tal algo além de dados aleatórios? Como, por exemplo, transformar a lista de estudantes da publicação de CSV em uma planilha XLSX.

#!/usr/bin/env python3
from csv import reader
from openpyxl import Workbook
from openpyxl.utils.cell import get_column_letter

STUDENTS = "students"
width = {}
wb = Workbook()
ws = wb.active

with open(STUDENTS + ".csv", "r") as f:
    for r, student in enumerate(reader(f)):
        for c, data in enumerate(student):
            ws.cell(row=r + 1, column=c + 1, value=data)
            width[c] = max((width.get(c, 0), len(data))

for c, size in width.items():
    ws.column_dimensions[get_column_letter(c)].width = 1 + size

ws.title = STUDENTS
wb.save(STUDENTS + ".xlsx")

A lógica é a mesma do exemplo de criação, exceto pelo bloco with onde o arquivo CSV é aberto para ser lido linha a linha no primeiro for e depois com o conteúdo recuperado separado em células para preencher as colunas. E em ambos os laços a numeração para linhas e colunas é provida pela função enumerate().

Além do preenchimento da planilha em si é também criado um dicionário contendo a largura máxima para cada coluna, o “width”, que é usado no terceiro laço for para ajustar para uma largura razoavelmente próxima a ideal. Isto é feito usando column_dimensions a a função get_column_letter()² do próprio OpenPyXL que cuida de traduzir o número de coluna em sua letra, ou letras, correspondentes.

(²) Já que column_dimensions somente aceita letras e não números… :-/

XLSX sem criar arquivo

Nos exemplos anteriores o OpenPyXL gravou e leu arquivos que fisicamente existiam, assim, algo um pouco diferente com um pequeno servidor web que responde planilhas geradas dinamicamente e para tal vamos precisar do Flask para implementar a parte do HTTP.

$ pip install flask
Collecting flask
   Using cached Flask-1.1.2-py2.py3-none-any.whl (94 kB)
 Collecting itsdangerous>=0.24
   Using cached itsdangerous-1.1.0-py2.py3-none-any.whl (16 kB)
 Collecting Jinja2>=2.10.1
   Using cached Jinja2-2.11.3-py2.py3-none-any.whl (125 kB)
 Collecting Werkzeug>=0.15
   Using cached Werkzeug-1.0.1-py2.py3-none-any.whl (298 kB)
 Collecting click>=5.1
   Using cached click-7.1.2-py2.py3-none-any.whl (82 kB)
 Collecting MarkupSafe>=0.23
   Using cached MarkupSafe-1.1.1-cp38-cp38-manylinux2010_x86_64.whl (32 kB)
 Installing collected packages: itsdangerous, MarkupSafe, Jinja2, Werkzeug, click, flask
 Successfully installed Jinja2-2.11.3 MarkupSafe-1.1.1 Werkzeug-1.0.1 click-7.1.2 flask-1.1.2 itsdangerous-1.1.0 

O código em si é bastante parecido com o primeiro exemplo, só que agora a rotina principal encontra-se dentro de uma rota do Flask.

from datetime import datetime
from random import randint
from tempfile import NamedTemporaryFile
from flask import Flask, Response
from openpyxl import Workbook
from openpyxl.utils.cell import get_column_letter

RAND = (1, 1000)
ROWS, COLS = 100, 100
DATE_FORMAT = "%Y%m%d_%H%M%S"
HTTP_MIMETYPE_XLSX = (
    "application/"
    "vnd.openxmlformats-officedocument."
    "spreadsheetml.sheet"
)

app = Flask(__name__)

@app.route("/ws/", methods=["GET"])
def worksheet():

    wb = Workbook()
    ws = wb.active
    just_now = datetime.now()

    with NamedTemporaryFile() as xlsx:
        for r in range(1, ROWS + 1):
            for c in range(1, COLS + 1):
                ws[
                    "{}{}".format(
                        get_column_letter(c), r
                    )
                ] = randint(*RAND)

        ws.title = just_now.strftime(DATE_FORMAT)
        wb.save(xlsx)
        xlsx.seek(0)

        return Response(
            xlsx.read(), mimetype=HTTP_MIMETYPE_XLSX
        )

Duas diferenças com relação ao primeiro programa são as dimensões e limites da planilha — uma área de 100×100 células com números aleatórios entre 1 até 1000 — e a outra é que o laço principal é executado dentro de um with para justamente “capturar” o arquivo gerado pelo OpenPyXL com o NamedTemporaryFile e enviá-lo dentro da resposta do servidor HTTP pelo Flask e utilizando o tipo MIME correto para que o cliente na outra ponta saiba corretamente o que fazer com ele.

Para executar o servidor HTTP.

$ FLASK_APP=server.py flask run
 * Serving Flask app "server.py"
 * Environment: production
   WARNING: This is a development server. Do not use it in a production deployment.
   Use a production WSGI server instead.
 * Debug mode: off
 * Running on http://127.0.0.1:5000/ (Press CTRL+C to quit) 

E para acessá-lo.

$ curl http://127.0.0.1:5000 --output planilha.xlsx
% Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                  Dload  Upload   Total   Spent    Left  Speed
 100 51129  100 51129    0     0   264k      0 --:--:-- --:--:-- --:--:--  264k

Ah sim, o nome de cada planilha gerada é justamente a composição do dia, mês, ano, hora, minuto em segundo em que a requisição foi processada.

Finalizando

Captura de tela do LibreOffice Calc mostrando o conteúdo do arquivo 'students.xlsx'

Como disse lá no começo, era algo bem simples, mas terminando com duas informações importantes, a primeira é que com o OpenPyXL é possível também manipular o conteúdo da planilha como copiar ou mover blocos, inspecionar fórmulas, alterar o estilo de formatação etc a outra, é importante instalar o Pillow para o caso de manipulação das imagens embutidas dentro das planilhas.

Ah sim, os arquivos com programas e até a planilha de exemplo usados aqui estão disponíveis lá no repositório git do blog.

Até!