Справка по openpyxl

openpyxl - лучшая либа для работы с Excel-файлами, тут будут рецепты как ей пользоваться


Начало работы

Книга / Workbook

openpyxl.Workbook - книга - полный эксель-файл, со всеми вкладочками и мета-инфой

Пустая книга

from openpyxl import Workbook

wb = Workbook()

Книга из файла

from openpyxl import load_workbook

wb = load_workbook("path/to/file.xlsx")

Можно передавать file-like объекты, напр. io.BytesIO:

wb = load_workbook(io.BytesIO(...))

Сохранение в файл

wb.save("path/to/file.xlsx")

Сохранение в bytes

Удобно, когда нужно отдать эксель наружу, напр. в ответе на запрос:

import io

stream = io.BytesIO()
wb.save(stream)
raw: bytes = stream.getvalue()

Лист / Worksheet

openpyxl.worksheet.worksheet.Worksheet - лист / вкладка / страница эксель-файла

Первый лист

sheet = wb.active

Лист по имени

sheet = wb["sheet-name"]

Создание листа

wb.create_sheet("sheet-name")

Ячейка / Cell

openpyxl.cell.Cell - ячейка; нумеруются с 1 - напр. ячейка A1 имеет координаты (1, 1)

(считаем, что sheet = wb.active)

Получение ячейки

cell = sheet.cell(row=1, column=1)
cell = sheet['A1']

Выставление значения

sheet.cell(row=1, column=1, value='val')
sheet.cell(row=1, column=1).value = 'val'

Строка

Как таковой сущности Строка нет, в openpyxl строка - это массив ячеек:

rows: Iterable[Tuple[Cell, ...]] = sheet.rows

Ячейки в промежутке

cells = sheet['A1:B2']

Объединение ячеек

sheet.merge_cells(
  start_row=1, start_col=1,
  end_row=2, end_column=2
)

Код выше объединяет ячейки A1, A2, B1, B2

Стилизовочка

(считаем, что cell = sheet.cell(row, col) и from openpyxl.styles import *)

Жирный шрифт

cell.font = Font(bold=True)

Границы

side = Side(style='thin', color='000000')
cell.border = Border(left=side, top=side, right=side, bottom=side)

Выравнивание по центру

cell.alignment = Alignment(horizontal='center')

Заливка / цвет бекграунда

color = 'D0F5A9'
cell.fill = PatternFill(
    start_color=Color(color), end_color=Color(color),
    fill_type='solid',
)

Стили и объединение ячеек

Если требуется применить стили для объединенных ячеек, то лучше применить стиль для одной ячейки, а затем произвести объединение

Генератор стилей

Ячеечка
a__
_a_
__a
B
I
🔲
🖌

Сложный эксель

Когда нужно сформировать сложный эксель файл, то код быстро превращается в такое:

sheet.cell(row=2, column=col).value = _('Всего назначено')
sheet.cell(row=2, column=col).style = self.bold_font_thin_border
sheet.merge_cells(start_row=2, start_column=col, end_row=3, end_column=col)
sheet.cell(row=2, column=col + 1).value = _('Исследований')
sheet.cell(row=2, column=col + 1).style = self.bold_font_thin_border
sheet.merge_cells(start_row=2, start_column=col + 1, end_row=2, end_column=col + 1 + 1)
sheet.cell(row=2, column=col + 3).value = _('Визитов')
sheet.cell(row=2, column=col + 3).style = self.bold_font_thin_border
sheet.merge_cells(start_row=2, start_column=col + 3, end_row=2, end_column=col + 3 + 8)
sheet.cell(row=2, column=col + 12).value = _('Всего услуг')
sheet.cell(row=2, column=col + 12).style = self.bold_font_thin_border
sheet.merge_cells(start_row=2, start_column=col + 12, end_row=3, end_column=col + 12)

А если еще if-ами приправить, то становится совсем не читаемо


Гораздо приятнее использовать декларативный подход, напр. html:

<table>
  <tbody>
    <tr>
        <td rowspan="2" style="border: 1px solid black; font-weight: bold">Всего назначено</td>
        <td colspan="2" style="border: 1px solid black; font-weight: bold">Исследований</td>
        <td colspan="8" style="border: 1px solid black; font-weight: bold">Визитов</td>
        <td rowspan="2" style="border: 1px solid black; font-weight: bold">Всего услуг</td>
    </tr>
  </tbody>
</table>

А поможет в генерации экселя из html моя библиотечка - jinja2xlsx:

from jinja2xlsx import render_xlsx

workbook: openpyxl.Workbook = render_xlsx(html_str)

Библиотечка парсит html-таблицы в эксель-таблицы + применяет стили из style + объединяет ячейки, согласно rowspan/colspan и тд.