Создание таблиц Excel с помощью Python

Инструменты Python для Excel: pandas, openpyxl, xlsxwriter

Зачем вообще несколько инструментов

В Python нет одной универсальной библиотеки, которая одинаково удобно решает все задачи работы с Excel-файлами. Обычно задачи делятся на три уровня:

Данные: собрать, очистить, агрегировать, посчитать итоги.

Файл Excel как документ: листы, ячейки, формулы, стили, ширины столбцов, объединения.

Отчетность: красивое форматирование, диаграммы, условное форматирование, многостраничные отчеты.

Для этих уровней чаще всего используют связку:

pandas: удобно работать с табличными данными и быстро выгружать их в Excel.

openpyxl: удобно читать и изменять существующие .xlsx файлы на уровне ячеек.

XlsxWriter: удобно создавать новый .xlsx файл с форматированием и диаграммами (в основном запись, без чтения).

Официальные источники:

Документация pandas

Документация openpyxl

Документация XlsxWriter

!Схема показывает, что pandas выгружает данные в Excel через движок (openpyxl или xlsxwriter), а openpyxl можно использовать напрямую для правки готовых файлов

Установка

Обычно достаточно установить pandas и нужные движки:

Если у вас уже установлен pandas, но при записи в Excel возникает ошибка про отсутствие движка, установите openpyxl или xlsxwriter отдельно.

Короткое сравнение: что выбрать

| Задача | Лучший выбор | Почему | |---|---|---| | Выгрузить DataFrame в Excel быстро и просто | pandas | Минимум кода, удобно из аналитики | | Прочитать существующий .xlsx, изменить ячейки, сохранить обратно | openpyxl | Чтение и запись, доступ к ячейкам, листам, стилям | | Сгенерировать новый отчет .xlsx с форматами, диаграммами, условным форматированием | XlsxWriter | Сильное форматирование и графики, удобная модель отчета | | “Сначала считаю в pandas, потом красиво оформляю” | pandas + (openpyxl или xlsxwriter) | pandas для данных, движок для записи и оформления |

Ключевое практическое правило:

Если вы создаете новый Excel-отчет с оформлением — чаще берут XlsxWriter.

Если вы правите существующий Excel-файл — чаще берут openpyxl.

Если вам важнее всего работа с данными — начинайте с pandas, а оформление добавляйте по мере необходимости.

pandas для Excel

Что такое DataFrame

DataFrame в pandas — это таблица: строки и столбцы с именами, похожая на таблицу Excel, но предназначенная для обработки данными в Python.

Чтение из Excel

pandas умеет читать лист Excel в DataFrame:

Полезные параметры:

sheet_name: имя листа или индекс (0 — первый лист).

usecols: выбрать столбцы.

dtype: задать типы данных.

Важно: чтение .xlsx через pandas обычно использует openpyxl как зависимость.

Запись в Excel одной таблицы

Параметры:

index=False — не записывать индекс DataFrame отдельным столбцом.

sheet_name — имя листа.

Запись в несколько листов через ExcelWriter

ExcelWriter позволяет писать несколько DataFrame в один файл и выбирать движок записи:

Если вместо engine="xlsxwriter" указать engine="openpyxl", логика pandas останется прежней, но запись будет идти через openpyxl.

Базовое форматирование через pandas Styler

pandas умеет применять стили к таблице и экспортировать их в Excel (зависит от версии pandas и движка). Это удобно для простых выделений.

Практический вывод: pandas хорош для данных и простого экспорта. Но когда нужен “отчет как документ” (ширины, форматы чисел, диаграммы, сложные стили), чаще подключают openpyxl или XlsxWriter.

openpyxl: чтение и правка существующих .xlsx

Когда нужен openpyxl

openpyxl выбирают, когда нужно работать с Excel как с набором листов и ячеек:

открыть готовый файл, изменить значения и формулы;

применить стили к ячейкам;

менять ширины столбцов, объединять ячейки;

добавлять или удалять листы.

Открыть файл, изменить ячейку, сохранить

Создать новый файл

Пример форматирования ячеек

Практический вывод: openpyxl удобен для редактирования существующих файлов и точечной работы с ячейками.

XlsxWriter: генерация отчетов с форматированием и диаграммами

Что важно знать про XlsxWriter

XlsxWriter в основном ориентирован на создание нового файла .xlsx. Он силен в:

форматах ячеек (числа, валюты, даты, шрифты, выравнивание);

условном форматировании;

диаграммах;

настройках листов (печать, поля, закрепление областей).

Ограничение: XlsxWriter не предназначен для чтения и редактирования существующего Excel-файла.

Минимальный пример записи

Форматы чисел и заголовков

Диаграмма (концептуально)

Диаграммы в XlsxWriter строятся на основе диапазона ячеек. Обычно процесс такой:

Записать данные в таблицу.

Создать объект диаграммы.

Указать диапазоны (категории и значения).

Вставить диаграмму на лист.

Официальный раздел по диаграммам: XlsxWriter Working with Charts

Практический вывод: если ваша цель — готовый отчет с оформлением “как в Excel”, XlsxWriter часто дает самый предсказуемый результат.

Как pandas связан с openpyxl и XlsxWriter

pandas не “сам” записывает .xlsx на низком уровне. Он использует движок:

engine="openpyxl" — запись через openpyxl.

engine="xlsxwriter" — запись через XlsxWriter.

Это важно, потому что:

одни и те же операции экспорта в pandas могут поддерживаться по-разному в зависимости от движка;

иногда удобнее сделать так: сначала выгрузить данные через pandas, затем открыть файл openpyxl и дооформить точечно.

Типичный рабочий сценарий:

pandas: собрать и подготовить DataFrame.

pandas: выгрузить в Excel на нужные листы.

openpyxl: открыть итоговый файл и добавить финальные правки (например, подписи, логотипы, дополнительные формулы, точечные стили).

Частые ошибки и как их избегать

Смешивание типов данных

- Если в столбце одновременно числа и текст, Excel может воспринимать значения непредсказуемо. В pandas заранее приводите типы и пропуски.

Большие файлы и скорость

- Запись миллионов ячеек в .xlsx будет медленной у любого инструмента. Если данных очень много, подумайте о CSV для передачи данных и Excel только для итогового отчета.

Перезапись файла

- openpyxl редактирует и сохраняет файл. XlsxWriter создает новый файл. Это влияет на выбор библиотеки.

Формулы

- Библиотеки могут записывать формулы как строки (например, "=SUM(A1:A10)"), но пересчет может выполняться уже в Excel при открытии файла.

Итог

pandas — ваш основной инструмент для работы с табличными данными и быстрого экспорта.

openpyxl — лучший выбор для чтения и редактирования существующих .xlsx файлов.

XlsxWriter — сильный выбор для генерации новых Excel-отчетов с форматированием и диаграммами.

В следующих материалах курса логично углубиться в практику: запись нескольких листов, форматирование (ширины, форматы чисел, условное форматирование), формулы и шаблоны отчетов.

Создание и заполнение таблиц: DataFrame, листы, диапазоны и типы данных

В прошлой статье мы разобрали, почему в Python обычно используют связку pandas + openpyxl или XlsxWriter: pandas отвечает за данные, а движок — за запись и оформление Excel-файла. Теперь соберем это в практическую картину: как создавать таблицы в виде DataFrame, как раскладывать их по листам и диапазонам Excel и как не «сломать» типы данных при экспорте.

!Визуально показывает, как DataFrame размещается в листе Excel и как влияет index/header и позиционирование

DataFrame как основа Excel-таблицы

DataFrame — это табличная структура данных в pandas: строки, столбцы и имена столбцов. Главное отличие от Excel в том, что DataFrame предназначен для вычислений и преобразований, а Excel-файл — для хранения и представления результата.

Создание DataFrame

Самые частые способы создать DataFrame:

Из словаря списков

Из списка словарей

Из CSV или базы данных (в рамках курса позже это обычно источник данных для отчета)

Пример: создаем таблицу продаж из словаря:

Заголовки и индекс

У DataFrame есть два «служебных» элемента, которые важно контролировать при экспорте в Excel:

Заголовки столбцов: обычно должны стать первой строкой таблицы в Excel

Индекс: по умолчанию pandas попытается записать индекс в Excel как отдельный первый столбец

В отчетах индекс чаще всего не нужен, поэтому обычно используют index=False.

Запись таблицы в Excel: базовый сценарий

Для экспорта DataFrame в Excel используется to_excel.

Полезные параметры:

sheet_name: имя листа, куда будет записана таблица

index=False: не добавлять индекс DataFrame как первый столбец

header=True или header=False: писать или не писать заголовки

Документация:

pandas.DataFrame.to_excel

Несколько листов в одном файле

Когда отчет состоит из нескольких таблиц (например, Продажи, Справочник товаров, Итоги), используется ExcelWriter.

Что важно понимать:

pandas управляет тем, какие данные и куда писать

engine определяет, кто физически создает Excel-файл

Документация:

pandas.ExcelWriter

Диапазоны в Excel и позиционирование таблиц

Excel думает в терминах листов и диапазонов (например, A1:D20). Python-библиотеки часто используют числовые координаты.

Два способа адресации

A1-адресация (привычная для Excel): B2, A1:D10

Числовая адресация (часто в коде): строка и столбец числами

Важная разница по библиотекам:

pandas в to_excel(..., startrow=..., startcol=...) использует нумерацию с нуля

openpyxl при обращении ws.cell(row=..., column=...) использует нумерацию с единицы

Запись DataFrame не с A1, а в заданный диапазон

Иногда нужно разместить таблицу ниже заголовков отчета или рядом с другой таблицей. Для этого есть startrow и startcol.

Практический смысл:

startrow=4 означает, что первая строка таблицы (обычно заголовки) попадет на строку 5

startcol=1 означает, что первый столбец таблицы попадет в столбец B

Две таблицы на одном листе

Частый прием: слева детальные данные, справа — краткие итоги.

Здесь startcol=6 сдвигает вторую таблицу вправо (примерно на столбец G, так как нумерация с нуля).

Заполнение ячеек и диапазонов без DataFrame

Иногда таблица маленькая или нужна точечная запись в шаблон. Тогда удобнее работать на уровне ячеек.

openpyxl: точечная запись в существующий файл

Документация:

openpyxl.load_workbook

XlsxWriter: запись строк и столбцов как диапазонов

XlsxWriter удобен, когда вы генерируете отчет «с нуля» и хотите быстро писать массивы.

Документация:

XlsxWriter Worksheet.write_row

Типы данных: что важно для Excel

Самая частая причина «странностей» в Excel-отчетах — это не форматирование, а неверные типы данных.

Как pandas хранит типы

В pandas типы (dtypes) могут быть такими:

Числа: int64, float64

Текст: object или string

Даты и время: datetime64[ns]

Логические: bool или boolean

Пропуски: NaN (для чисел), NaT (для дат)

Проверить типы можно так:

Как типы превращаются в Excel

Обычно при записи происходит такое соответствие:

| В pandas | В Excel (обычно) | Риск/особенность | |---|---|---| | int64, float64 | Число | Excel может показывать в научной нотации при очень больших значениях | | object/string | Текст | Числа в виде текста не участвуют в формулах как числа | | datetime64[ns] | Дата/время | Важно, чтобы столбец действительно был датой, а не строкой | | bool/boolean | TRUE/FALSE | Иногда в шаблонах ждут 0/1 вместо логики | | NaN, NaT | Пустая ячейка | Это ожидаемое поведение при экспорте |

Типовая проблема: ведущие нули

Если у вас коды вроде 001234 (почтовые индексы, табельные номера, SKU), Excel и pandas часто превращают их в число 1234, и нули пропадают.

Решение: хранить такие значения как текст.

Если данные приходят как числа, можно привести к строке заранее:

Типовая проблема: даты как строки

Если даты попали в DataFrame как текст, Excel может не распознать их как даты (особенно при разных локалях и форматах). Надежнее привести столбец к дате в pandas:

Документация:

pandas.to_datetime

Чтение из Excel: контроль типов и диапазонов

Хотя эта статья про создание и заполнение, на практике вы часто начинаете с Excel-файла. В read_excel важно управлять тем, какие столбцы читаются и как задаются типы.

Документация:

pandas.read_excel

Частые ошибки при создании Excel-таблиц

Случайно записали индекс DataFrame в файл

Смешали числа и текст в одном столбце, и Excel перестал считать формулы

Потеряли ведущие нули, потому что код был воспринят как число

Записали даты строками, и Excel не распознал их как даты

Пытаетесь «дописать» в существующий файл через XlsxWriter (он создает новый файл, а не редактирует существующий)

Итог

DataFrame — удобная форма подготовки данных перед Excel.

Для размещения таблиц на листе используйте sheet_name, startrow, startcol.

Помните про разницу адресации: pandas часто использует нумерацию с нуля, openpyxl — с единицы.

Типы данных решают многое: заранее приводите SKU, даты и числовые поля к корректным типам.

Следующий логичный шаг после корректного заполнения — оформление отчета: форматы чисел, ширины столбцов, стили, условное форматирование и диаграммы (в зависимости от выбранного движка).

Оформление и структура: стили, формат чисел, автоширина, фильтры и закрепление

Обычно Excel-отчет состоит из двух слоев:

Данные (что записать): таблицы, итоги, группировки, вычисления

Представление (как выглядит): стили, форматы чисел, ширины колонок, фильтры, закрепление областей

В прошлых материалах курса мы делали упор на создание таблиц через pandas и раскладку по листам и диапазонам. Теперь добавим “документный” слой: приведем отчет к виду, который удобно читать в Excel.

!Схема показывает, что данные готовятся в pandas, а оформление делается на уровне движка записи

Базовый принцип: значение и формат отображения

Excel хранит в ячейке значение (число, дата, текст) и отдельно формат, который определяет, как это значение показывать.

Если записать число как текст, Excel может не суммировать его формулами.

Если записать дату как строку, Excel может не распознать ее как дату.

Если записать число корректно, но не задать формат, Excel покажет его “как умеет” (например, с лишними знаками после запятой).

Поэтому правило такое:

Сначала обеспечиваем правильные типы данных в pandas

Затем задаем нужный формат отображения при записи (XlsxWriter) или после записи (openpyxl)

Что удобнее для оформления: openpyxl или XlsxWriter

XlsxWriter удобен, когда вы создаете новый отчет и хотите сразу “печатный” вид: форматы, таблицы, фильтры, закрепления, диаграммы. Документация: Документация XlsxWriter

openpyxl удобен, когда вы редактируете существующий файл (например, шаблон) или хотите дооформить результат, который уже выгрузили через pandas. Документация: Документация openpyxl

Оформление через pandas + ExcelWriter

Частый рабочий сценарий:

В pandas готовим DataFrame

Через ExcelWriter пишем на лист

Достаем объект листа и применяем оформление средствами движка

Ниже будут два набора примеров: для engine="xlsxwriter" и для engine="openpyxl".

Заголовки таблицы: шрифт, заливка, границы

Вариант с XlsxWriter

Идея: создаем формат один раз, применяем к диапазону заголовков.

Что здесь важно:

df.to_excel записал таблицу “как есть”

затем мы переписали первую строку (заголовки) с нужным форматом

Вариант с openpyxl

Идея: пишем DataFrame, затем открываем доступ к ячейкам заголовка и применяем стили.

Обратите внимание:

В openpyxl строки и столбцы при обращении к ячейкам считаются с 1

ws[1] означает “первая строка на листе”

Документация по стилям: openpyxl styles

Формат чисел: деньги, проценты, даты

Формат числа влияет на отображение, но не меняет само значение. Наиболее частые форматы:

Целые числа с разделителем тысяч

Деньги

Проценты

Дата

XlsxWriter: формат для колонки

Ключевая идея:

set_column задает ширину и формат сразу для всего столбца

дата будет корректно отображаться, если в DataFrame это действительно datetime64[ns]

Документация: XlsxWriter set_column

openpyxl: number_format для диапазона

Что здесь происходит:

ws["A"] возвращает все ячейки столбца A

[1:] пропускает заголовок (первая строка)

Автоширина колонок: как сделать “похоже на Excel”

В Excel есть автоширина “по содержимому”, но библиотеки Python обычно не умеют сделать ее идеально, потому что точная ширина зависит от шрифта, размера, рендеринга.

Практичный подход:

вычислить максимальную длину строки в столбце

добавить небольшой “запас”

установить ширину через set_column (XlsxWriter) или column_dimensions (openpyxl)

Автоширина в XlsxWriter

Автоширина в openpyxl

Документация по get_column_letter: openpyxl.utils.get_column_letter

Автофильтр: чтобы таблицу можно было фильтровать

Автофильтр в Excel включается для прямоугольного диапазона: от левого верхнего угла до правого нижнего.

XlsxWriter: включить фильтр на диапазон

Здесь диапазон задается числами:

первая строка 0 и первый столбец 0 это ячейка A1

nrows учитывает строку заголовков, потому что в Excel фильтр ставится на заголовок и данные под ним

Документация: XlsxWriter autofilter

openpyxl: auto_filter.ref

Документация: openpyxl filters

Закрепление областей: фиксируем заголовок при прокрутке

Закрепление делает отчет удобным: заголовки остаются видимыми при прокрутке вниз.

XlsxWriter: freeze_panes

freeze_panes(1, 0) означает:

закрепить строки выше строки с индексом 1, то есть первую строку

не закреплять столбцы слева

Документация: XlsxWriter freeze_panes

openpyxl: freeze_panes через адрес

"A2" означает: закрепить все строки выше 2-й, то есть первую строку.

Документация: openpyxl freeze panes

Практический рецепт: “минимально приличный” отчет

Ниже чек-лист, который часто дает хороший результат без сложной верстки:

Заголовки: жирный, заливка, границы

Числа: формат #,##0 или #,##0.00

Даты: единый формат даты

Автоширина: по содержимому с ограничением

Автофильтр: включен на весь диапазон

Закрепление: первая строка закреплена

Если вы создаете новый файл отчета, чаще всего проще реализовать это через engine="xlsxwriter". Если вы правите существующий шаблон, используйте openpyxl.

Частые ошибки в оформлении

Путаница типов и форматов

Применение формата не к тем колонкам из-за смещения startrow и startcol

Автофильтр на неправильный диапазон, из-за чего фильтруется только часть таблицы

Попытка “дописать” в существующий файл через XlsxWriter

Официальные справочники по экспорту:

pandas.ExcelWriter

pandas.DataFrame.to_excel

Итог

Оформление в Excel это отдельный слой: стили, форматы, ширины, фильтры, закрепление

XlsxWriter удобнее для генерации нового “красивого” отчета

openpyxl удобнее для правки существующих файлов и шаблонов

Автоширина в Python обычно делается приближенно: по длине строк

Автофильтр и закрепление областей резко повышают удобство чтения отчета

Формулы и логика: вычисления, ссылки, условное форматирование и проверки данных

Excel-отчет становится по-настоящему полезным, когда в нем есть логика: формулы, которые считают итоги, правила, которые подсвечивают проблемы, и проверки, которые не дают вводить некорректные данные.

В предыдущих статьях курса мы:

готовили данные в pandas и записывали их на листы;

наводили “документный” порядок: стили, форматы чисел, автоширина, фильтры, закрепление.

Теперь добавим третий слой: вычисления и правила.

!Понимание, что формулы и правила — отдельный слой поверх данных и оформления

Как библиотеки работают с формулами

Важно различать два действия:

Записать формулу в ячейку.

Посчитать результат формулы.

Python-библиотеки для Excel (XlsxWriter, openpyxl) обычно умеют надежно записывать формулы, но не являются Excel и не всегда пересчитывают их так же, как приложение Excel. Чаще всего пересчет произойдет при открытии файла в Excel.

Полезное практическое правило:

если вам нужен готовый числовой результат в Python (например, чтобы отправить в API), считайте в pandas;

если вам нужна логика внутри Excel (чтобы пользователь мог менять входные значения и видеть пересчет), записывайте формулы.

Документация:

XlsxWriter Working with Formulas

openpyxl Using Formulae

Формулы в Excel: базовые принципы, которые нужно знать

Формула и значение

Значение в ячейке: 1200

Формула в ячейке: =B2*C2

В коде формула почти всегда передается как строка, начинающаяся с =.

Относительные и абсолютные ссылки

Ссылки бывают:

Относительные: B2 (при копировании формулы вправо/вниз ссылка смещается)

Абсолютные: 2 (не смещается)

Смешанные: 2 (фиксируется только столбец или только строка)

Это критично, когда вы генерируете формулы “на диапазон”, а потом ожидаете, что пользователь будет их копировать/протягивать.

Ссылки на другие листы

Ссылка на другой лист записывается так:

=SUM(Sales!C2:C100)

Если имя листа содержит пробелы, берут в одинарные кавычки:

=SUM('Sales 2026'!C2:C100)

Типичная ошибка: адресация строк и столбцов

Внутри формулы вы всегда пишете Excel-адреса в стиле A1. Но при позиционировании таблиц в pandas и XlsxWriter вы часто задаете координаты числами.

pandas startrow и startcol используют нумерацию с нуля

openpyxl ws.cell(row=..., column=...) использует нумерацию с единицы

Поэтому формулы для диапазонов удобнее строить через вычисление “где начинается таблица” и аккуратную конвертацию в A1.

Запись формул при генерации отчета

Вариант: pandas + XlsxWriter (удобно для новых отчетов)

Сценарий:

pandas записывает таблицу на лист

XlsxWriter добавляет формулы в новые столбцы и строку итогов

Ключевые идеи:

write_formula записывает формулу “как формулу”, а не как текст.

Координаты в write_formula(row, col, ...) задаются числами с нуля.

Внутри строки формулы ("=B2*C2") вы используете Excel-адреса A1.

Документация:

XlsxWriter Worksheet.write_formula

Вариант: pandas + openpyxl (удобно для правки и шаблонов)

Сценарий:

pandas записывает таблицу

openpyxl добавляет формулы в ячейки

Документация:

openpyxl Worksheet cell access

Условное форматирование: подсветка по правилам

Условное форматирование — это правила, которые автоматически меняют оформление в зависимости от значения.

Это отличается от обычных стилей:

обычный стиль вы применяете “навсегда”;

условное форматирование применится только если условие выполняется.

Типовые задачи:

подсветить отрицательную маржу

подсветить просроченные даты

выделить топ-N значений

сделать “светофор” по статусам

Условное форматирование в XlsxWriter

Пример: подсветим выручку revenue красным, если она меньше 2000.

Документация:

XlsxWriter Worksheet.conditional_format

Условное форматирование в openpyxl

Тот же смысл, но через правила openpyxl.

Документация:

openpyxl Conditional Formatting

Проверки данных: как ограничить ввод и сделать выпадающий список

Проверка данных в Excel нужна, когда файл не только читают, но и заполняют.

Типовые варианты:

выпадающий список допустимых значений

ограничение диапазона чисел

запрет пустых значений

проверка даты (не раньше/не позже)

Проверка данных в XlsxWriter

Пример: в столбце status разрешим только значения из списка.

Документация:

XlsxWriter Working with Data Validation

Проверка данных в openpyxl

Документация:

openpyxl Data Validation

Практический прием: формулы поверх DataFrame при смещении startrow/startcol

Частая реальная ситуация: у вас на листе есть шапка отчета, таблица начинается не с A1.

Если вы записали DataFrame с startrow и startcol, то:

формулы должны учитывать, где реально оказались колонки;

условное форматирование и проверки данных должны применяться к правильному диапазону.

Для надежности фиксируйте переменные:

table_row0 и table_col0 в координатах “с нуля” (как в pandas/XlsxWriter)

“Excel-строку” как table_row0 + 1 (потому что A1)

И затем стройте диапазоны последовательно, а не “на глаз”.

Ограничения и нюансы, о которых важно помнить

XlsxWriter не редактирует существующие файлы: он генерирует новый .xlsx.

openpyxl отлично подходит для шаблонов, но при очень больших объемах может быть медленнее.

Если в отчете много формул, расчет может происходить при открытии файла в Excel и занять время.

Условное форматирование не заменяет расчеты: оно только меняет вид, а не значение.

Итог

Формулы в Excel стоит использовать, когда логика должна жить внутри файла и пересчитываться у пользователя.

XlsxWriter удобен, когда вы строите отчет “с нуля” и хотите сразу добавить формулы, условное форматирование и проверки данных.

openpyxl удобен, когда вы работаете с шаблонами и правите существующий файл.

Всегда явно учитывайте смещения startrow и startcol, чтобы формулы и диапазоны попадали туда, куда вы ожидаете.

Отчеты и визуализация: сводные данные, диаграммы и генерация файлов по шаблонам

В предыдущих статьях курса мы закрыли базу:

подготовка данных в pandas и экспорт в Excel;

оформление как документа: стили, форматы, автоширина, фильтры, закрепление;

логика в файле: формулы, условное форматирование, проверки данных.

Следующий уровень автоматизации Excel-отчетов — это отчет как продукт: в нем есть сводные срезы, визуализация и повторяемая сборка «по шаблону» для разных периодов или клиентов.

!Схема показывает два типовых пути: создать отчет с нуля или заполнить шаблон

Сводные данные без PivotTable: когда проще посчитать в pandas

Под сводными данными чаще всего понимают агрегации:

итоги по категориям;

сравнение периодов;

срезы по менеджерам, регионам, продуктам;

доли и ранжирование.

В Excel это часто делают через PivotTable, но в Python надежнее и быстрее считать такие сводные в pandas, а затем выгружать готовый результат в отдельный лист.

Практическое правило:

если сводная должна быть стабильной и воспроизводимой — делайте ее в pandas;

если сводную будет интерактивно крутить пользователь — тогда PivotTable уместна, но ее лучше держать в шаблоне.

Полезные ссылки:

pandas.DataFrame.groupby

pandas.DataFrame.pivot_table

Пример: исходные данные и две сводные

Допустим, есть продажи с датой, регионом и выручкой.

Что здесь важно:

groupby(...).agg(...) удобен для «итоги по измерению»;

pivot_table удобен, когда нужен вид «строки — одно измерение, столбцы — другое».

Диаграммы в Excel-отчетах: почему чаще выбирают XlsxWriter

Диаграмма в Excel почти всегда строится по диапазону ячеек. Поэтому типовой подход такой:

Записать на лист данные, которые будут источником диаграммы.

Создать диаграмму и привязать серии к диапазонам.

Вставить диаграмму на лист.

Для генерации новых отчетов диаграммы чаще проще делать через XlsxWriter.

Ссылка:

XlsxWriter Working with Charts

Пример: отчет с листами Data, Summary и диаграммой

Ниже пример, где:

лист Data содержит исходные продажи;

лист Summary содержит итоги по регионам;

лист Dashboard содержит столбчатую диаграмму по итогам.

Что стоит запомнить:

Диапазоны для диаграмм задаются в Excel-нотации A1.

Источник диаграммы лучше держать отдельным листом Summary, чтобы он был стабильным.

Диаграммы через openpyxl: когда важен шаблон

openpyxl также умеет создавать диаграммы, но чаще его выбирают не ради генерации графиков с нуля, а ради работы с существующим файлом: если в шаблоне уже есть листы, оформление, логотип, готовые диаграммы.

Ссылки:

openpyxl Charts introduction

openpyxl.load_workbook

Практическая стратегия для шаблонов:

дизайнер отчета один раз собирает шаблон в Excel;

Python заполняет «сырьевые» листы данными;

диаграммы в шаблоне привязаны к диапазонам этих листов;

пользователь открывает файл, и Excel пересчитывает формулы и обновляет отображение.

PivotTable в Python: реальность и безопасная альтернатива

PivotTable как объект Excel — это не просто таблица, а отдельная структура с кэшем, настройками и связями. Поддержка полноценного создания PivotTable из Python-библиотек ограничена и часто зависит от версии Excel.

Надежная альтернатива:

считать «сводные» в pandas и выгружать результат как обычную таблицу;

если PivotTable обязательна, держать ее в шаблоне, а Python обновляет только лист-источник.

Так вы получаете:

предсказуемую генерацию;

минимум «магии»;

меньше рисков, что файл не откроется или PivotTable окажется поврежденной.

Генерация файлов по шаблонам: заполняем и сохраняем как новый отчет

Шаблон — это .xlsx, в котором уже есть:

брендирование и оформление;

листы и структура;

возможно, формулы, диаграммы, проверки данных;

ячейки, куда нужно подставить значения.

Самый практичный путь — openpyxl: открыли шаблон, записали данные, сохранили результат.

Пример: заполнение шапки и таблицы в шаблоне

Предположим, в template.xlsx есть:

лист Cover с ячейкой B2 под название отчета и B3 под дату;

лист Data для таблицы, начиная с A1.

Что важно:

Мы сохраняем новый файл, чтобы не портить шаблон.

В шаблоне можно заранее настроить форматы столбцов, фильтры, закрепления, диаграммы.

Практический прием: не «рисовать» оформление кодом

Если у вас уже есть требования к внешнему виду отчета, то попытка «собрать весь дизайн» в Python превращается в большой проект.

Часто выгоднее:

один раз сделать шаблон руками в Excel;

в коде заниматься только данными, диапазонами и контрольными значениями.

Как собрать отчет, который удобно поддерживать

Ниже устойчивый подход, который хорошо масштабируется.

pandas отвечает за расчеты:

- очистка данных; - сводные через groupby и pivot_table; - подготовка источников для диаграмм.

XlsxWriter используют, когда отчет создается «с нуля» и нужен управляемый результат:

- форматы; - диаграммы; - условное форматирование; - проверки данных.

openpyxl используют, когда есть шаблон:

- заполняем листы-источники; - обновляем отдельные ячейки; - сохраняем как новый файл.

Частые ошибки в отчетах с диаграммами и шаблонами

Диапазон диаграммы «уехал», потому что поменялось количество строк, а ссылки были зафиксированы.

Сводную посчитали в Excel формулами на тысячи строк, и файл стал тяжелым.

Перепутали адресацию при позиционировании данных и получили пустую диаграмму.

Попытались создать PivotTable программно и столкнулись с непредсказуемым поведением.

Итог

Сводные данные надежнее считать в pandas и выгружать как отдельные таблицы.

Диаграммы удобнее всего генерировать через XlsxWriter, если отчет создается с нуля.

Для отчетов «как в компании принято» эффективнее использовать шаблон и openpyxl для заполнения.

PivotTable как объект Excel чаще стоит держать в шаблоне, а Python должен обновлять данные-источники.