openpyxl: Automatize as tarefas do Excel com Python

O openpyxl é uma biblioteca Python eficiente que permite que você leia e grave arquivos do Excel nos formatos modernos baseados em XML (.xlsx, .xlsm) introduzidos no Excel 2007, com suporte total para recursos avançados do Excel 2010 em diante. Ele foi originalmente desenvolvido como uma porta Python do PHPExcel e continua a ser mantido por uma comunidade ativa de desenvolvedores. A biblioteca é excelente em automação de dados, relatórios e fluxos de trabalho de formatação, o que a torna especialmente útil para usuários que precisam trabalhar com arquivos do Excel, mas não têm o Excel instalado em seus sistemas.

Muitas organizações usam o openpyxl para automatizar relatórios financeiros, gerar gráficos a partir de exportações de bancos de dados e aplicar fórmulas e formatação a relatórios de marketing. Essa biblioteca oferece controle refinado sobre os arquivos do Excel inteiramente por meio do código Python.

O que é openpyxl?

O openpyxl foi projetado especificamente para oferecer suporte nativo aos formatos do Excel 2010+. A biblioteca funciona com vários formatos de arquivo:

•  .xlsx - Pasta de trabalho do Excel

•  .xlsm - Pasta de trabalho do Excel com macros

•  .xltx - Modelo do Excel

•  .xltm - Modelo do Excel com macros

Uma grande vantagem do openpyxl é que ele não exige que o Excel esteja instalado em seu computador. Isso o torna ideal para ambientes de servidor, sistemas de relatórios automatizados e pipelines de processamento de dados. Várias outras bibliotecas Python, incluindo a pandas, usam o openpyxl como interface do Excel, o que demonstra sua confiabilidade e funcionalidade.

Instalação e importação do openpyxl

A instalação do openpyxl é simples, usando o pip:

pip install openpyxl

Para aumentar a segurança ao trabalhar com arquivos de fontes não confiáveis, você também pode instalar o pacote opcional defusedxml:

pip install defusedxml

Para usar o openpyxl em seus scripts Python, importe as classes necessárias:

from openpyxl import Workbook, load_workbook

Conceitos-chave e terminologia

Para usar o openpyxl de forma eficaz, você precisa entender a estrutura básica do Excel:

• Pasta de trabalho: O próprio arquivo do Excel, contendo uma ou mais planilhas
• Planilha: Abas/planilhas individuais em uma pasta de trabalho
• Célula: Pontos de dados individuais em uma planilha, identificados pela letra da coluna e pelo número da linha (por exemplo, "A1")
• Fila: Linha horizontal de células, identificadas por números (1, 2, 3...)
• Coluna: Linha vertical de células, identificadas por letras (A, B, C...)

No openpyxl, você pode fazer referência a células usando qualquer um dos dois:

• Referências de estilo do Excel: sheet[“A1”]

• Indexação de linha-coluna: sheet.cell(row=1, column=1) (Observação: o openpyxl usa indexação baseada em 1, não em 0)

Leitura de arquivos do Excel com o openpyxl

Aqui está um exemplo prático que mostra como você pode ler dados de um arquivo do Excel:

Carregar e explorar uma pasta de trabalho

from openpyxl import load_workbook

# Load the workbook - use read_only=True for large files
wb = load_workbook('sample.xlsx', read_only=False, data_only=False)
# data_only=True reads values instead of formulas

Os parâmetros opcionais controlam como a pasta de trabalho é carregada:

• read_only=True melhora o desempenho de arquivos grandes, mas limita os recursos de edição

• data_only=True retorna valores calculados em vez de fórmulas

Iterar pelos dados

Para processar várias células com eficiência, use os métodos de iteração:

# Iterate through rows
for row in sheet.iter_rows(min_row=1, max_row=5, values_only=True):
    print(row)  # Returns a tuple of values

# Iterate through columns
for column in sheet.iter_cols(min_col=1, max_col=3, values_only=True):
    print(column)  # Returns a tuple of values

# Access a range of cells
cell_range = sheet["A1:C5"]
for row in cell_range:
    for cell in row:
        print(cell.value)

Converter para estruturas de dados Python

Você pode transformar facilmente os dados do Excel em estruturas de dados Python:

# Convert worksheet data to a list of dictionaries
def sheet_to_dict(sheet):
    data = []
    headers = [cell.value for cell in sheet[1]]  # First row as headers
    
    for row in sheet.iter_rows(min_row=2, values_only=True):
        row_data = {}
        for key, value in zip(headers, row):
            row_data[key] = value
        data.append(row_data)
    
    return data

# Example usage
data_dict = sheet_to_dict(sheet)
print(data_dict)

Gravação de arquivos do Excel com o openpyxl

Criar e modificar arquivos do Excel é igualmente simples com o openpyxl.

Criar uma pasta de trabalho e gravar dados básicos

from openpyxl import Workbook

# Create a new workbook
wb = Workbook()
sheet = wb.active

# Rename the sheet
sheet.title = "Data"

# Write values to cells
sheet["A1"] = "Name"
sheet["B1"] = "Age"
sheet["C1"] = "City"

# Add data
data = [
    ["Alice", 25, "New York"],
    ["Bob", 30, "San Francisco"],
    ["Charlie", 35, "Chicago"]
]

for row_idx, row_data in enumerate(data, start=2):
    for col_idx, cell_value in enumerate(row_data, start=1):
        sheet.cell(row=row_idx, column=col_idx, value=cell_value)

# Save the workbook
wb.save("new_workbook.xlsx")

Modificar arquivos Excel existentes

from openpyxl import load_workbook

# Load an existing workbook
wb = load_workbook("existing_file.xlsx")
sheet = wb.active

# Modify cell values
sheet["D1"] = "Updated Data"
sheet.cell(row=5, column=2).value = 42

# Save to a new file (to preserve the original)
wb.save("modified_file.xlsx")

Anexar dados dinamicamente

O método .append() facilita a adição de novas linhas:

from openpyxl import Workbook

wb = Workbook()
sheet = wb.active

# Add headers
sheet.append(["Date", "Product", "Quantity", "Price"])

# Add multiple rows of data
sales_data = [
    ["2025-04-01", "Laptop", 5, 1200],
    ["2025-04-01", "Mouse", 10, 25],
    ["2025-04-02", "Monitor", 3, 350]
]

for row in sales_data:
    sheet.append(row)

wb.save("sales_report.xlsx")

Formatação e estilo no Excel

O openpyxl oferece várias opções para formatação e estilo de células.

Adicione fontes, bordas e alinhamento

from openpyxl import Workbook
from openpyxl.styles import Font, Alignment, Border, Side, PatternFill, NamedStyle

wb = Workbook()
sheet = wb.active

# Create a header style
header_style = NamedStyle(name="header_style")
header_style.font = Font(bold=True, size=12, color="FFFFFF")
header_style.fill = PatternFill(fill_type="solid", start_color="366092")
header_style.alignment = Alignment(horizontal="center", vertical="center")
header_style.border = Border(
    bottom=Side(border_style="medium", color="000000"),
    left=Side(border_style="thin"),
    right=Side(border_style="thin"),
    top=Side(border_style="thin")
)

# Add the style to the workbook
wb.add_named_style(header_style)

# Apply the style to header row
headers = ["ID", "Product", "Category", "Price"]
for col_idx, header in enumerate(headers, start=1):
    cell = sheet.cell(row=1, column=col_idx, value=header)
    cell.style = "header_style"

# Basic styling without named styles
sheet["A2"].font = Font(bold=True, italic=True)
sheet["A3"].alignment = Alignment(horizontal="center")
sheet["A4"].border = Border(bottom=Side(border_style="thin"))

wb.save("styled_workbook.xlsx")

Formatação condicional

A formatação condicional permite que você aplique estilos com base nos valores das células:

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill
from openpyxl.styles.differential import DifferentialStyle
from openpyxl.formatting.rule import Rule, ColorScaleRule

wb = Workbook()
sheet = wb.active

# Add sample data
for row in range(1, 11):
    for col in range(1, 5):
        sheet.cell(row=row, column=col, value=(row-1)*4 + col)

# Add color scale (green to red gradient)
color_scale = ColorScaleRule(
    start_type="min", start_color="63BE7B",  # Green
    mid_type="percentile", mid_value=50, mid_color="FFEB84",  # Yellow
    end_type="max", end_color="F8696B"  # Red
)
sheet.conditional_formatting.add("A1:D10", color_scale)

# Add a rule for values less than 5
red_text = Font(color="FF0000")
red_fill = PatternFill(start_color="FFCCCC", end_color="FFCCCC", fill_type="solid")
dxf = DifferentialStyle(font=red_text, fill=red_fill)
rule = Rule(type="cellIs", operator="lessThan", formula=["5"], dxf=dxf)
sheet.conditional_formatting.add("A1:D10", rule)

wb.save("conditional_format.xlsx")

Recursos avançados

O openpyxl oferece suporte a muitos recursos avançados do Excel que ajudam nas tarefas complexas de planilhas.

Adição de fórmulas

from openpyxl import Workbook

wb = Workbook()
sheet = wb.active

# Add data
for row in range(1, 6):
    sheet.cell(row=row, column=1, value=row)

# Add formulas
sheet["B1"] = "=A1*2"
sheet["B2"] = "=A2*2"
sheet["C1"] = "=SUM(A1:A5)"
sheet["C2"] = "=AVERAGE(A1:A5)"
sheet["C3"] = "=COUNTIF(A1:A5,\">2\")"

wb.save("formulas.xlsx")

Observação: Ao usar load_workbook(), as fórmulas não são calculadas automaticamente pelo openpyxl. Para ver os valores calculados, use load_workbook(“file.xlsx”, data_only=True).  

Inserir gráficos

O openpyxl pode criar vários tipos de gráficos:

from openpyxl import Workbook
from openpyxl.chart import BarChart, LineChart, Reference

wb = Workbook()
sheet = wb.active

# Add data
months = ["Jan", "Feb", "Mar", "Apr", "May"]
values = [30, 45, 37, 50, 62]

for i, (month, value) in enumerate(zip(months, values), start=1):
    sheet.cell(row=i, column=1, value=month)
    sheet.cell(row=i, column=2, value=value)

# Create a bar chart
bar_chart = BarChart()
bar_chart.title = "Monthly Sales"
bar_chart.x_axis.title = "Month"
bar_chart.y_axis.title = "Sales"

# Define the data range
data = Reference(sheet, min_col=2, min_row=1, max_row=5)
categories = Reference(sheet, min_col=1, min_row=1, max_row=5)

# Add the data to the chart
bar_chart.add_data(data)
bar_chart.set_categories(categories)

# Add the chart to the worksheet
sheet.add_chart(bar_chart, "D1")

# Create a line chart on the same data
line_chart = LineChart()
line_chart.title = "Monthly Sales Trend"
line_chart.add_data(data)
line_chart.set_categories(categories)
sheet.add_chart(line_chart, "D15")

wb.save("charts.xlsx")

Inserir imagens

from openpyxl import Workbook
from openpyxl.drawing.image import Image

wb = Workbook()
sheet = wb.active

# Add an image
img = Image("logo.png")

# Resize the image (optional)
img.width = 150
img.height = 75

# Add the image to cell A1
sheet.add_image(img, "A1")

wb.save("with_image.xlsx")

Observação: Você precisa ter a biblioteca Pillow instalada para trabalhar com imagens: pip install pillow

Gerenciamento de planilhas, linhas e colunas

O openpyxl fornece funções para gerenciar a estrutura dos arquivos do Excel:

Criar e remover planilhas

from openpyxl import Workbook

wb = Workbook()

# Create new sheets
wb.create_sheet("Data")
wb.create_sheet("Summary", 0)  # Add at the beginning

# Remove a sheet
wb.remove(wb["Sheet"])  # Remove the default sheet

print(wb.sheetnames)  # ['Summary', 'Data']

wb.save("multiple_sheets.xlsx")

Renomear planilhas

wb = Workbook()
sheet = wb.active
sheet.title = "Sales Report"
wb.save("renamed_sheet.xlsx")

Cópia de planilhas

from openpyxl import Workbook

wb = Workbook()
source = wb.active
source.title = "Original"

# Add some data to copy
source["A1"] = "Test Data"

# Create a copy
wb.copy_worksheet(source)

# The copied sheet will have "Copy of Original" name
wb.save("copied_sheet.xlsx")

Inserção e exclusão de linhas e colunas

from openpyxl import Workbook

wb = Workbook()
sheet = wb.active

# Add some data
for i in range(1, 6):
    for j in range(1, 4):
        sheet.cell(row=i, column=j, value=f"R{i}C{j}")

# Insert a row at position 2
sheet.insert_rows(2)

# Insert multiple rows
sheet.insert_rows(5, 3)  # Insert 3 rows at position 5

# Insert a column at position 2
sheet.insert_cols(2)

# Delete rows
sheet.delete_rows(7, 2)  # Delete 2 rows starting at row 7

# Delete columns
sheet.delete_cols(3)  # Delete column C

wb.save("modified_structure.xlsx")

Congelamento de painéis e adição de filtros

from openpyxl import Workbook

wb = Workbook()
sheet = wb.active

# Add headers
headers = ["ID", "Name", "Department", "Salary"]
for col_idx, header in enumerate(headers, start=1):
    sheet.cell(row=1, column=col_idx, value=header)

# Add data
data = [
    [1, "John Smith", "HR", 55000],
    [2, "Jane Doe", "IT", 65000],
    [3, "Bob Johnson", "Finance", 60000]
]
for row_idx, row_data in enumerate(data, start=2):
    for col_idx, value in enumerate(row_data, start=1):
        sheet.cell(row=row_idx, column=col_idx, value=value)

# Freeze the header row
sheet.freeze_panes = "A2"

# Add autofilter
sheet.auto_filter.ref = "A1:D4"

wb.save("freeze_and_filter.xlsx")

Limitações e feedback da comunidade

Embora o openpyxl seja rico em recursos, os usuários notaram algumas limitações:

• Suporte limitado para intervalos nomeados
• Os exemplos de documentação podem ser escassos para recursos avançados
• O desempenho pode ser lento para arquivos muito grandes (embora o modo somente leitura ajude)

Apesar dessas limitações, o openpyxl tem grande apoio da comunidade. Os problemas podem ser relatados no rastreador do GitLab e, às vezes, há suporte profissional disponível. Para obter mais informações e ajuda, você pode consultar:

• Documentação do openpyxl
• Código-fonte
• Rastreador de problemas
• Suporte profissional

Conclusão

O openpyxl é uma biblioteca bem estabelecida para automação do Excel baseada em Python que oferece suporte completo para leitura, gravação e estilização de arquivos do Excel. Ele permite o controle detalhado da saída da planilha em fluxos de trabalho automatizados, desde a manipulação de células individuais até a criação de gráficos e a aplicação de formatação complexa.

Para os usuários que precisam gerar ou analisar arquivos do Excel de forma programática, especialmente ao criar relatórios ou processar dados tabulares, o openpyxl oferece um amplo conjunto de ferramentas. Sua capacidade de trabalhar sem exigir a instalação do Excel o torna especialmente valioso para ambientes de servidor e pipelines de dados automatizados.

Quer você esteja criando modelos financeiros, analisando dados ou gerando relatórios, o openpyxl oferece as ferramentas para lidar com arquivos do Excel de forma eficiente por meio do código Python.