Автоматизация Python Excel: openpyxl, pandas или xlwings
Автоматизация Python Excel обычно начинается с одной фразы: «я каждый вторник два часа свожу выгрузки руками». Дальше вопрос не в том, можно ли это автоматизировать (можно), а в том, чем именно — pandas, openpyxl, XlsxWriter или xlwings решают разные задачи и упираются в разные потолки. Ниже — что чем делать, где каждый инструмент останавливается и когда скрипт вообще не нужен.
Три типа задач, которые называют одним словом
Просьба звучит одинаково — «автоматизировать Excel», — но в работе это три разные задачи с разной технологией и разной ценой. Путать их дорого: под каждую нужен свой инструмент.
- Сборка отчёта. Из нескольких выгрузок (1С, CRM, банк, кабинет маркетплейса) собрать один файл: свести, сверить, посчитать. Excel здесь — только формат доставки, вся логика в Python.
- Наполнение готового шаблона. Есть красивый файл с формулами, сводными и фирменным оформлением. Раз в месяц в него надо подставить свежие цифры и ничего не сломать. Самая коварная из трёх.
- Разбор входящих файлов. Приходят прайсы, акты, реестры в чужих форматах — из них нужно достать поля и положить в базу или CRM. Тут больше половины работы приходится на разнобой источников, а не на код.
Практический признак, что задача сложнее, чем кажется: файлы приходят от разных людей. Один и тот же «реестр» будет с объединёнными ячейками, шапкой на третьей строке, датами как текстом и суммами с неразрывными пробелами. Это нормально, но это работа, а не одна строка кода.
openpyxl, pandas или xlwings: чем что делать
Библиотеки не конкурируют — они отвечают за разные слои. Считать удобно одним, оформлять другим, дёргать живой Excel третьим.
| Инструмент | Для чего он | Чего не умеет |
|---|---|---|
| pandas | Считать: свести таблицы, сгруппировать, сверить, выгрузить итог на лист | Оформление — только через Styler; тонкая вёрстка при to_excel не переносится |
| openpyxl | Читать и писать xlsx без установленного Excel: ячейки, стили, листы, формулы как текст | Не вычисляет формулы; на сотнях тысяч строк нужен отдельный режим |
| XlsxWriter | Быстро создавать оформленные файлы с диаграммами и условным форматированием | Только запись — открыть существующий файл он не может |
| python-calamine | Быстрое чтение xlsx, xlsb, ods; в pandas подключается как engine="calamine" | Только чтение |
| xlwings | Управлять живым Excel: пересчитать книгу, вызвать макрос, работать с открытым файлом | Нужен установленный Excel; на сервере это не поддерживается |
Рабочая связка в 80 % проектов простая: pandas считает, openpyxl или XlsxWriter собирает файл. Если данные надо не только положить, но и оформить по фирменному шаблону — pandas пишет значения через ExcelWriter(engine="openpyxl"), а стили и формулы уже лежат в шаблоне. Начиная с pandas 1.4 у ExcelWriter есть mode="a" и if_sheet_exists="overlay" — это позволяет дописывать данные на существующий лист, не стирая его содержимое.
xlwings берут не потому, что он мощнее, а когда без настоящего Excel нельзя: нужен пересчёт сложной книги, вызов чужого VBA или инструмент, которым человек пользуется прямо в открытом файле.
Где каждый инструмент упирается в потолок
Формулы никто не считает за вас
Самая частая неожиданность. openpyxl — не калькулятор: он читает и пишет формулы как текст, но не вычисляет их. Флаг data_only=True в load_workbook вернёт не результат вычисления, а значение, которое Excel сохранил при последнем открытии файла. Если книгу собрал скрипт и её ни разу не открывали в Excel, кэша нет — на месте значений будет None.
Отсюда три честных варианта: посчитать всё в Python и записать готовые числа; записать формулу текстом и позволить Excel пересчитать её при открытии у пользователя; либо взять xlwings и пересчитать книгу настоящим Excel. Четвёртого нет.
Объём
Лист xlsx вмещает 1 048 576 строк и 16 384 столбца — это ограничение формата, а не библиотеки. Задолго до этого предела кончается память: openpyxl держит книгу целиком в оперативке. Лечится штатными режимами — load_workbook(read_only=True) для чтения и Workbook(write_only=True) для записи (последний с установленным lxml удерживает расход памяти около 10 МБ, но писать умеет только последовательно, через append).
Форматы и потери при пересохранении
Старый .xls — отдельная история: xlrd с версии 2.0 читает только его и не открывает xlsx. Формат .xlsb берут pyxlsb или calamine. А файлы, которые 1С и старые ERP называют «Excel», нередко оказываются HTML или CSV с расширением xls — их читает не Excel-библиотека, а парсер таблиц.
Отдельно про пересохранение чужого файла: openpyxl читает не все элементы xlsx. Фигуры и часть графики теряются, если файл открыть и сохранить под тем же именем, а в режиме read_only диаграммы и картинки недоступны совсем. Поэтому шаблон в проекте всегда лежит отдельным неприкосновенным файлом, а скрипт пишет в его копию.
Пока читаете — можно сразу проверить свою задачу. Опишите процесс, и я скажу, решается ли он и во сколько обойдётся.
Автоматизация данных в Excel: скрипт против процесса
Скрипт, который вчера собрал правильный отчёт, — это ещё не автоматизация. Автоматизация начинается там, где его можно запустить в пятницу вечером и не проверять результат руками.
Ключевое требование — идемпотентность: повторный запуск за тот же период должен дать тот же результат, а не удвоить строки. Правила, которые я закладываю по умолчанию:
- Никогда не писать в файл, который открыт у человека. Сборка идёт во временный файл, готовый результат подменяется одним движением.
- Период — часть ключа. Отчёт за март перезаписывает отчёт за март, а не дописывается в конец.
- Схема входа проверяется до расчёта. Нет колонки, изменился формат даты, пришло 12 строк вместо 12 000 — останавливаемся и пишем ответственному.
- Исходные выгрузки сохраняются. Через месяц спросят, откуда взялась цифра, — понадобится именно тот файл, а не сегодняшний.
- Молчаливый сбой запрещён. Скрипт либо отдал результат, либо прислал сообщение об ошибке. Пустой файл в папке — худший из возможных исходов.
Excel, Python и API: автоматизация данных без ручных выгрузок
Самое дешёвое улучшение в таких проектах — убрать из цепочки человека, который нажимает «экспорт». Если у источника есть API, скрипт забирает данные напрямую: без папки «Загрузки», без переименованных файлов и без пропущенных дней. Как устроена эта часть — в разборе про парсинг данных через API.
Если API нет, промежуточный вариант — оставить ручной экспорт, но перенести его в общую папку или почтовый ящик, откуда скрипт забирает файлы сам. Это некрасиво, зато работает и не требует ничьих доступов.
Сроки и цена
Стоимость определяется не объёмом данных, а количеством источников и тем, насколько предсказуем их формат. Пять аккуратных выгрузок из одной системы — быстрая работа; три файла от трёх разных поставщиков — вдвое дольше.
| Задача | Что входит | Срок и цена |
|---|---|---|
| Разбор входящих файлов | Один формат, извлечение полей, результат в CSV или базу | 1–2 дня, от 15 000 ₽ |
| Регулярный отчёт | 2–5 источников, шаблон, расписание, проверки, уведомления | 3–7 дней, 40 000–90 000 ₽ |
| Замена ручных выгрузок на API | Интеграции, хранилище, догрузка за прошлые периоды | от 120 000 ₽ |
| Поддержка | Правки под новые форматы источников и новые поля | от 8 000 ₽ в месяц |
Когда автоматизацию заказывать не нужно
Часть задач, под которые просят скрипт, дешевле закрыть штатными средствами. Где я честно отговариваю:
- Задача разовая. Свести два файла один раз — это час работы человека, разработка тут не окупится никогда.
- Хватает формул. ВПР, XLOOKUP и сводная таблица закрывают больше, чем принято думать. Если вся задача — подтянуть цену по артикулу, Python не нужен.
- Хватает Power Query. Он встроен в Excel, умеет забирать файлы из папки, чистить их и обновлять отчёт одной кнопкой. Источники однотипные и лежат в одном месте — это ответ на вопрос, и он бесплатный.
- В системе уже есть выгрузка по расписанию. Многие CRM и учётные системы умеют слать отчёт на почту сами. Парсить собственную систему через файлы — чинить водопровод через окно.
- Нет договорённости, как считать. Если два отдела считают маржу по-разному, скрипт просто зафиксирует спор в коде. Сначала методика, потом автоматизация.
И наоборот, скрипт окупается за первый-второй месяц, когда отчёт нужен регулярно, источников больше двух, а цена ошибки в цифрах заметна. Дальше та же логика распространяется на сводные дашборды — как это устроено, разобрано в статье про автоматизацию учёта и отчётности.
Частые вопросы
Что лучше для работы с Excel — pandas или openpyxl?
Это не альтернативы. pandas считает: сводит таблицы, группирует, сверяет. openpyxl читает и пишет сам файл xlsx — ячейки, стили, листы. В типовом проекте они работают вместе: pandas готовит данные, openpyxl кладёт их в оформленный шаблон.
Почему openpyxl возвращает формулу вместо значения?
Потому что он не вычисляет формулы. С флагом data_only=True вы получите значение, сохранённое Excel при последнем открытии книги. Если файл создан скриптом и в Excel не открывался, кэша нет и вернётся None. Считайте в Python либо пересчитывайте книгу через xlwings.
Нужен ли установленный Excel для автоматизации на Python?
Для openpyxl, pandas и XlsxWriter — нет, они работают с файлом напрямую и запускаются на любом сервере. Excel нужен только для xlwings, который управляет самим приложением. При этом Microsoft не поддерживает серверную автоматизацию Office, поэтому для расписаний xlwings — плохой выбор.
Как обработать файл на миллион строк, чтобы не кончилась память?
Использовать штатные режимы openpyxl: read_only=True для чтения и write_only=True для записи. Если такие объёмы регулярны, Excel уже не место для хранения — данные переносят в базу или parquet, а в файл выгружают только итоговую витрину.
Сколько времени занимает автоматизация отчёта?
Регулярный отчёт из 2–5 источников с расписанием и уведомлениями — 3–7 дней. Разбор одного формата входящих файлов — 1–2 дня. Дольше всего идут не расчёты, а согласование того, как именно считать спорные показатели.
Что будет, если поставщик изменит формат файла?
Правильно написанный скрипт это заметит: проверка схемы на входе увидит пропавшую колонку и остановится с уведомлением, а не посчитает отчёт по мусору. Починка под новый формат — обычно от получаса до пары часов.
Читайте дальше
Посчитать ваш отчёт
Пришлите пример файлов и опишите, что с ними делают руками — скажу, хватит ли Power Query или формул, а если нет, назову срок и цену скрипта.
Обсудить задачу