openpyxl и pandas: read_excel и ExcelWriter под капотом

Если ты проходил раздел pandas, то уже читал Excel-файлы: pd.read_excel("файл.xlsx") — и DataFrame готов. Возможно, ты удивишься, но pandas при этом ни строчки не разобрал из xlsx сам: чтением и записью excel-формата у него занимается ровно та библиотека, которую ты учил последние восемнадцать уроков. Сегодня вскроем этот капот и поймём, когда какой инструмент брать.

Зачем это знать, если «и так работает»? Три причины: понимать, почему формулы приходят пустыми; уметь писать несколько листов одним ExcelWriter-ом; и осознавать, где заканчивается территория pandas — стили, диаграммы и валидация остаются openpyxl. Всё это чаще всего всплывает в момент дедлайна.

pip install pandas openpyxl
python -c "import pandas, openpyxl; print('оба на месте')"

У pandas, как и всюду в экосистеме Python, есть схема с помощниками: xlsx парсит openpyxl, старый бинарный .xls — xlrd, формат OpenDocument — odf, а быстрый движок calamine появился ради больших файлов. Параметр engine выбирает, кого позвать; для xlsx ответ уже решён — и это та самая библиотека, которую ты учил весь раздел.

Что под капотом у pd.read_excel?

Для файлов xlsx pandas вызывает openpyxl: параметр engine по умолчанию равен "openpyxl", и указывать его руками не нужно. Схема простая: pandas — интерфейс и превращение строк в DataFrame, openpyxl — парсер файла. Соберём файл Workbook-ом и прочитаем его pandas-ом.

import os
import pandas as pd
from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.title = "Продажи"
ws.append(["Напиток", "Штук"])
for row in [("Американо", 14), ("Капучино", 22), ("Латте", 18)]:
    ws.append(row)
wb.save("dukat_sales.xlsx")

df = pd.read_excel("dukat_sales.xlsx")
print(df.shape)
print(df.columns.tolist())
print(df["Штук"].sum())
os.remove("dukat_sales.xlsx")

(3, 2)
['Напиток', 'Штук']
54

Ничего нового для тебя здесь нет: заголовок из первой строки стал именами колонок, три строки данных стали тремя строками DataFrame. Точно так же работает и обратный путь: df.to_excel("файл.xlsx") под капотом создаёт Workbook и раскладывает ячейки — то, чем ты занимался руками все эти уроки.

Типы при чтении pandas определяет сам: числа становятся числами, то, что похоже на дату, — датами, всё остальное — строками (object). Поэтому прайс, аккуратно собранный openpyxl-ом с настоящими числами и datetime из урока про даты, приезжает в pandas готовым к арифметике — без парсинга строк.

Ещё два параметра read_excel, которые выручают на больших файлах: sheet_name принимает не только имя, но и номер листа (0 — первый) либо список — тогда вернётся словарь из нескольких листов сразу. А если нужен лист «Итоги» из книги на десять листов, читать весь файл целиком не обязательно.

ExcelWriter: несколько листов одной командой

Когда DataFrame надо положить не в отдельный файл, а листом в общий отчёт, включается ExcelWriter — контекстный менеджер pandas, который держит одну книгу openpyxl открытым и по очереди складывает в неё листы. Классический сценарий «Дуката»: факт продаж и прогноз рядом.

import os
import pandas as pd

df = pd.DataFrame({"Напиток": ["Американо", "Капучино", "Латте"], "Штук": [14, 22, 18]})
with pd.ExcelWriter("dukat_report.xlsx", engine="openpyxl") as writer:
    df.to_excel(writer, sheet_name="Продажи", index=False)
    forecast = df.copy()
    forecast["Штук"] = forecast["Штук"] * 2   # прогноз: вдвое больше
    forecast.to_excel(writer, sheet_name="Прогноз", index=False)

sheets = pd.read_excel("dukat_report.xlsx", sheet_name=None)
print(list(sheets))
print(sheets["Прогноз"]["Штук"].tolist())
os.remove("dukat_report.xlsx")

['Продажи', 'Прогноз']
[28, 44, 36]

Читается так: внутри with открывается книга, каждый to_excel пишет свой лист с именем из sheet_name, на выходе из блока файл сохраняется. Параметр sheet_name=None в read_excel возвращает словарь «имя листа: DataFrame» — удобный способ проверить, что листы на месте. Тот же словарь листов ты получал через wb.sheetnames в openpyxl — просто на другом уровне абстракции.

Кстати, ExcelWriter умеет и дописывать в готовый файл: с mode="a" и if_sheet_exists="replace" новый DataFrame заменит одноимённый лист, не тронув остальные. Так собирают ежемесячные отчёты: шаблон с оформленными листами живёт на диске, а скрипт обновляет только лист с данными за текущий месяц.

import os
import pandas as pd
from openpyxl import load_workbook

df = pd.DataFrame({"Напиток": ["Американо", "Капучино"], "Штук": [14, 22]})
df.to_excel("dukat_index.xlsx", sheet_name="Лист1")   # без index=False

wb = load_workbook("dukat_index.xlsx")
ws = wb["Лист1"]
print("A1:", ws["A1"].value)
print("B1:", ws["B1"].value)
wb.close()
os.remove("dukat_index.xlsx")

A1: None
B1: Напиток

Посмотри глазами openpyxl: ячейка A1 пуста — это и есть колонка индекса, навязанная pandas-ом, а сами данные сдвинулись на B и C. Никакой магии: одна забытая опция — и каждый читатель файла получает мусорный столбец.

Если такой файл уже гуляет по рукам, его чинят в один проход: read_excel, затем drop столбца с именем «Unnamed: 0» (или usecols с перечнем нужных колонок) и перезапись с index=False. После этого стоит найти в коде место, где файл родился, — иначе через месяц он появится снова.

Почему формула приходит в pandas пустой?

Ты записал openpyxl-ом формулу, открыл файл pandas-ом — а на её месте NaN. Это не баг и не поломка, а прямое следствие урока про data_only: openpyxl не вычисляет формулы, и если файл не был открыт и сохранён Excel-ом, кэша значений в нём нет. pandas читает с data_only=True — и получает пустоту.

import os
import pandas as pd
from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws["A1"] = 2
ws["A2"] = "=A1*10"   # openpyxl формулы не считает
ws["A3"] = 5
wb.save("dukat_formula.xlsx")

df = pd.read_excel("dukat_formula.xlsx", header=None)
print(df[0].tolist())
print("формула для pandas:", pd.isna(df.iloc[1, 0]))
os.remove("dukat_formula.xlsx")

[2.0, nan, 5.0]
формула для pandas: True

Второе и пятое значения на месте, вместо формулы — nan. Практический вывод: если отчёт строится кодом, считай значения в коде (или в pandas) и пиши числа, а не формулы. Формулы в файлах имеют смысл, когда файл дальше живут в Excel и пользователи пересчитывают их сами.

Для pandas nan — не поломка, а стандартная метка «здесь нет данных»: у DataFrame есть целые методы, чтобы с ней работать. Строки с пропусками убирает dropna, вместо пропусков подставляет значение fillna, а при чтении Excel можно сразу назначить, что считать пропусками. Поэтому формула в файле не роняет анализ — она просто превращается в пустоту, которую видно и можно обработать.

pandas считает, openpyxl красит

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

import os
import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import Font

df = pd.DataFrame({"Напиток": ["Американо", "Капучино"], "Штук": [14, 22]})
with pd.ExcelWriter("dukat_styled.xlsx", engine="openpyxl") as writer:
    df.to_excel(writer, sheet_name="Продажи", index=False)

wb = load_workbook("dukat_styled.xlsx")
ws = wb["Продажи"]
ws["A1"].font = Font(bold=True)
ws["B1"].font = Font(bold=True)
ws.column_dimensions["A"].width = 14
wb.save("dukat_styled.xlsx")

wb2 = load_workbook("dukat_styled.xlsx")
ws2 = wb2["Продажи"]
print("шапка жирная:", ws2["A1"].font.bold)
print("ширина A:", ws2.column_dimensions["A"].width)
wb2.close()
os.remove("dukat_styled.xlsx")

шапка жирная: True
ширина A: 14.0

Заметь: to_excel писал данные, а шапку и ширину красил уже открытый Workbook — те самые Font и column_dimensions из уроков про стили и ширины. Разделить труда получилось идеально: ни строчки ручной раскладки данных, ни одного нестилизованного файла.

В большом отчёте сюда же встают остальные инструменты из курса: number_format на колонку с деньгами, auto_filter на диапазон, закрепление шапки через freeze_panes. Всё это — обычные вызовы openpyxl поверх файла, который родился из pandas за долю секунды.

Как читать из большого файла только нужные колонки?

Файл на двадцать колонок, а нужны две: usecols отрезает лишнее ещё на чтении — экономит и память, и внимание. Обрати внимание, что колонки выбираются по именам заголовков, как в словаре синонимов из консолидации.

import os
import pandas as pd
from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.append(["Напиток", "Штук", "Цена"])
for row in [("Американо", 14, 190), ("Капучино", 22, 250)]:
    ws.append(row)
wb.save("dukat_use.xlsx")

df = pd.read_excel("dukat_use.xlsx", usecols=["Напиток", "Цена"])
print(df.columns.tolist())
print(df["Цена"].sum())
os.remove("dukat_use.xlsx")

['Напиток', 'Цена']
440

Что pandas умеет, а openpyxl нет?

Группировки. Соберём сводку продаж по двум точкам в openpyxl, а агрегацию поручим pandas: одна строчка groupby заменяет цикл со словарём-аккумулятором из консолидации.

import os
import pandas as pd
from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.append(["Точка", "Напиток", "Штук"])
for row in [
    ("Арбат", "Капучино", 22),
    ("Арбат", "Латте", 18),
    ("Маросейка", "Капучино", 17),
    ("Маросейка", "Латте", 21),
]:
    ws.append(row)
wb.save("dukat_con.xlsx")

df = pd.read_excel("dukat_con.xlsx")
by_drink = df.groupby("Напиток")["Штук"].sum()
print(by_drink.index.tolist())
print(by_drink.tolist())
os.remove("dukat_con.xlsx")

['Капучино', 'Латте']
[39, 39]

Четыре строки данных — и сразу сеть целиком: Капучино и Латте разлили по 39 стаканов. В openpyxl этот подсчёт занял бы цикл с аккумулятором и двумя if-ами; здесь — одна строка. Именно поэтому взрослые пайплайны выглядят как pandas-конвейер с openpyxl-отделкой на выходе.

Кстати, о больших данных: когда файл гигантский, а писать нужно быстро, у openpyxl есть режим write_only — потоковая запись без хранения всей книги в памяти. pandas to_excel его не использует, но твой финальный скрипт может: посчитал в pandas, выгрузил в CSV, собрал xlsx потоково — конвейер масштабируется без смены инструментов.

И не путай территории наоборот: добавить строку в DataFrame «вот в эту ячейку» нельзя, а покрасить столбец Excel-файла groupby-ом — тем более. Каждый инструмент хорош в своей модели данных: у pandas — таблица значений, у openpyxl — книга с ячейками. Как только ты сформулировал задачу в терминах одной из моделей, выбор инструмента становится очевидным.

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