Python в excel вместо vba

Обновлено: 19.05.2024

Так вышло, что изучать программирование я начал полтора года назад из-за необходимости автоматизации процессов на работе. Первым языком, с которым я познакомился, был Visual Basic for Applications (или VBA). Вы можете посмеяться, сказать, что VBA нельзя отнести к полноценным языкам программирования. Спорить не буду.

Я изучал VBA самостоятельно и добился, без преувеличения, неплохих успехов. Так как главной страстью моей жизни были и остаются видеоигры, то изучать язык я начал с их разработки. Знаю, это звучит странно, но игры в Excel — вполне реальная вещь. Я очень много раз рассказывал про человека, который сделал в Excel полноценный ретро-шутер, основанный на рейкастинге.

Ссылка на сайт проекта:

В свое время я написал несколько статей, которые были посвящены разработке игр в Excel с использованием VBA:

Все мы знакомы с Excel. Вот и я, душный банковский служащий, что-то да слышал об этом инструменте. Тогда я расскажу вам, как создать игру "3 в 1" на рабочем листе Excel. Бесплатно, без регистрации… В прошлый раз я рассказал как малой кровью сделать подобие игры «3 в ряд» в рабочей книге Excel с… Пока люди попрятались в своих норах, ваш покорный насильник Excel не успокаивается и придумывает… Вам бывает скучно на работе? Часто сидите два-три дня без единого намёка на трудовую деятельность…

Все, что я создавал — это просто наработки. Я брал какую-нибудь идею и реализовывал ее в Excel, но никогда не доводил до конца.

В один прекрасный момент я решил разработать полноценную игру. Я создал паблик ВК для публикации отчетов и начал писать игровой движок. Преуспел ли я в этом? Не сильно. Базовыми функциями движок обладал, но дальше этого дело не продвинулось. Моя идея умерла, едва успев родиться.

Это случилось из-за того, что я перестал видеть перспективы. Сперва я двигался вперед, чтобы изучать язык, новые алгоритмы, основы игростроения, но в какой-то момент задумался о выходе в свет и монетизации своей работы. Понятное дело, что разработанная в Excel игра никому нужна не будет, кроме создателя.

Размышления затянулись на несколько месяцев. Я постоянно возвращался к идее закончить проект в Excel, метался между различными вариантами, но не мог определиться. Так продолжалось до тех пор, пока мой друг, который никогда в жизни не программировал, сказал, что изучает Python.

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

По поводу Python скажу немного:

Это самый простой язык, с которым я имел дело. На усвоение базы у меня ушло три дня дня. Без шуток.
Для Python есть очень много легко подключаемых библиотек. Из этого следует, что многие алгоритмы не надо писать с нуля.
В языке немного странное объектно-ориентированное программирование. Например, инкапсуляция присутствует только номинально. Конечно, когда разрабатываешь в одиночку, можно обойтись и без этого, но факт есть факт.

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

1. Отображать игровой уровень.

Все уровни состоят из подуровней. Такую разбивку я сделал «смотря сегодня в завтрашний день». На данный момент слабенький ноутбук может отображать в кадре около 400 объектов без задержек, а достаточно мощный компьютер около 750. В будущем этот показатель будет ухудшаться из-за новых вычислений. При этом, если все объекты находятся на пределами кадра, то задержки отсутствуют.

Каждый объект происходит от базового объекта, который имеет ряд параметров (обязательные: тип, ширина, высота, состояние анимации, путей и т.д.; необязательные: имя анимации, направление и т.д.). Базовые объекты прописаны в файле Obj.thconf. Они едины для всей игры.

Подчиненные объекты прописаны в файлах подуровней с расширением thmap. Могу же я придумать свои собственные расширения текстовых файлов, как настоящий разработчик=). Они тоже имеют собственные параметры (положение X, положение Y, слой, параметры анимации, параметры путей и т.д.). Все параметры базового объекта можно переопределить в подчиненном объекте.

В сегодняшней статье я хотел бы, как можно подробнее, рассмотреть интеграцию приложений Python и MS Excel. Данные вопрос может возникнуть, например, при создании какой-либо системы онлайн отчетности, которая должна выгружать результаты в общепринятый формат ну или какие-либо другие задачи. Также в статье я покажу и обратную интеграцию, т.е. как использовать функцию написанную на python в Excel, что также может быть полезно для автоматизации отчетов.

Работаем с файлами MS Excel на Python

Для работы с Excel файлами из Python мне известны 2 варианта:

Использование библиотек

Итак, первый метод довольно простой и хорошо описан. Например, есть отличная статья для описания работы c xlrd, xlwt, xlutils. Поэтому в данном материале я приведу небольшой кусок кода с их использованием.

Для начала загрузим нужные библиотеки и откроем файл xls на чтение и выберем
нужный лист с данными:

Теперь давайте посмотрим, как считать значения из нужных ячеек:

Как видно чтение данных не составляет труда. Теперь запишем их в другой файл. Для этого создам новый excel файл с новой рабочей книгой:

Запишем в новый файл полученные ранее данные и сохраним изменения:

Из примера выше видно, что библиотека xlrd отвечает за чтение данных, а xlwt — за запись, поэтому нет возможности внести изменения в уже созданную книгу без ее копирования в новую. Кроме этого указанные библиотеки работают только с файлами формата xls (Excel 2003) и у них нет поддержки нового формата xlsx (Excel 2007 и выше).

Чтобы успешно работать с форматом xlsx, понадобится библиотека openpyxl. Для демонстрации ее работы проделаем действия, которые были показаны для предыдущих библиотек.

Для начала загрузим библиотеку и выберем нужную книгу и рабочий лист:

Как видно из вышеприведенного листинга сделать это не сложно. Теперь посмотрим как можно считать данные:

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

Теперь посмотрим как нам произвести запись и сохранить данные:

Из примера видно, что запись, тоже производится довольно легко. Кроме того, в коде выше, можно заметить, что openpyxl кроме имен ячеек может работать и с их индексами.

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

Как было показано выше, для более менее полноценной работы с excel файлами, в данном случае, нужно 4 библиотеки, и это не всегда удобно. Кроме этого, возможно нужен будет доступ к VBA (допустим для какой-либо последующей обработки) и с помощью этих библиотек его не получить.

Однако, работа с этими библиотеками достаточно проста и удобна для быстрого создания Excel файлов их форматирования, но если Вам надо больше возможностей, то следующий подпункт для Вас.

Работа с com-объектом

В своих отчетах я предпочитаю использовать второй способ, а именно использование файла Excel через com-объект с использованием библиотеки win32com. Его преимуществом, является то, что вы можете выполнять с файлом все операции, которые позволяет делать обычный Excel с использованием VBA.

Проиллюстрируем это на той же задаче, что и предыдущие примеры.

Для начала загрузим нужную библиотеку и создадим COM объект.

Теперь мы можем работать с помощью объекта Excel мы можем получить доступ ко всем возможностям VBA. Давайте, для начала, откроем любую книгу и выберем активный лист. Это можно сделать так:

Давайте получим значение первой ячейки и последовательности:

Как можно заметить, мы оперируем здесь функциями чистого VBA. Это очень удобно если у вас есть написанные макросы и вы хотите использовать их при работе с Python при минимальных затратах на переделку кода.

Посмотрим, как можно произвести запись полученных значений:

Из примера видно, что данные операции тоже довольно просто реализовываются. Кроме этого, можно заметить, что изменения мы сохранили в той же книге, которую открыли для чтения, что достаточно удобно.

Однако, внимательный читатель, обратит внимание на переменную i, которая инициализируется не 0, как принято python, а 1. Это связано с тем, что мы работаем с индексами ячеек как из VBA, а там нумерация начинается не с 0, а с 1.

На этом закончим разбор способов работы с excel файлами в python и перейдем к обратной задаче.

Вызываем функции Python из MS Excel

Может возникнуть такая ситуация, что у вас уже есть какой-либо функция, которая обрабатывает данные на python, и нужно перенести ее функциональность в Excel. Конечно же можно переписать ее на VBA, но зачем?

Для использования функций python в Excel есть прекрасная надстройка ExcelPython. С ее помощью вы сможете вызывать функции написанные на python прямо из Excel, правда придется еще написать небольшую обертку на VBA, и все это будет показано ниже.

Итак, предположим у нас есть функция, написанная на python, которой мы хотим воспользоваться:

На вход ей подается список, состоящий из списков, это одно из условий, которое должно выполняться для работы данной функции в Excel.

Сохраним функцию в файле plugin.py и положим его в ту же директорию, где будет лежать наш excel файл, с которым мы будем работать.

Теперь установим ExcelPython. Установка происходит через запуск exe-файла и не вызывает затруднений.

Когда все приготовления выполнены, открываем тестовый файл excel и вызовем редактор VBA (Alt+F11). Для работы с вышеуказанной надстройкой необходимо ее подключить, через Tools->References, как показано на рисунке:

Ну что же, теперь можно приступить к написанию функции-обертки для нашего Python-модуля plugin.py. Выглядеть она будет следующим образом:

Итак, что же происходит в данной функции?

Для начала, с помощью PyModule , мы подключаем нужный модуль. Для этого в качестве параметров ей передается имя модуля без расширения, и путь до папки в которой он находится. На выходе работы PyModule мы получаем объект для работы с модулем.

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

Объект модуля, полученный на предыдущем шаге
Имя вызываемой функции
Параметры, передаваемые функции (передаются в виде списка)

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

Как видно из рисунка все отработало правильно.

Надо отметить, что в данном материале используется старая версия ExcelPython, и на GitHub'e автора доступна новая версия.

Заключение

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

Также хочу заметить, что указанные пакеты не являются единственными и в статье опущено рассмотрение, таких пакетов как xlsxwriter для генерации excel файлов или xlwings, который может работать с Excel файлами «на лету», а также же PyXLL, который выполняет аналогичные функции ExcelPython.

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

Работаем с файлами MS Excel на Python

Для работы с Excel файлами из Python мне известны 2 варианта:

Использование библиотек

Для начала загрузим нужные библиотеки и откроем файл xls на чтение и выберем
нужный лист с данными:

Теперь давайте посмотрим, как считать значения из нужных ячеек:

Запишем в новый файл полученные ранее данные и сохраним изменения:

Для начала загрузим библиотеку и выберем нужную книгу и рабочий лист:

Как видно из вышеприведенного листинга сделать это не сложно. Теперь посмотрим как можно считать данные:

Теперь посмотрим как нам произвести запись и сохранить данные:

Работа с com-объектом

Проиллюстрируем это на той же задаче, что и предыдущие примеры.

Для начала загрузим нужную библиотеку и создадим COM объект.

Давайте получим значение первой ячейки и последовательности:

Посмотрим, как можно произвести запись полученных значений:

На этом закончим разбор способов работы с excel файлами в python и перейдем к обратной задаче.

Вызываем функции Python из MS Excel

Итак, предположим у нас есть функция, написанная на python, которой мы хотим воспользоваться:

Теперь установим ExcelPython. Установка происходит через запуск exe-файла и не вызывает затруднений.

Итак, что же происходит в данной функции?

Объект модуля, полученный на предыдущем шаге
Имя вызываемой функции
Параметры, передаваемые функции (передаются в виде списка)

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

Как видно из рисунка все отработало правильно.

Заключение

Уверенное владение Excel уже принято за норму, чем за исключение. И бизнес зачастую просто ни в каких других программах не работает с цифрами. Открыть excel-файл на телефоне в чате или гугл таблицах (тоже, кстати, отлично работает) намного проще, чем думать как запустить тетрадку jupyter notebook или целую программу на python.

(думается мне, после того как я разберусь с excel, напишу еще и про google sheets)

Ну, jupyter notebook это не та программа для телефона, скажете вы. Да, и вы правы, только наш бизнес сейчас весь на телефонах и совещаниях. Начальнику нужно быстренько взять открыть файл, сделать скрин и так далее.

И вот проходя множество курсов по анализу данных вы вряд ли найдете помимо экскурса в Python и SQL еще и Excel — все так стремительно хотят от него уйти, будто вы и так в нём хорошо работали.

Пока я училась поняла, что мало кто из студентов вообще понимает прелесть Excel. Они просто думают, что за Python будущее. Прежде чем мы действительно будем так думать, давайте разберемся с Excel.

Excel сейчас — это стандартная программа, которая ставится в комплекте Microsoft Office и есть на каждом ноутбуке (если её нет, найдется Libre, но смысл останется).

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

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

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

Но бывает и такое, что ты сидишь и думаешь “это можно сделать проще”, но, увы, не знаешь как.

Все эти “можно сделать проще” возможны с помощью макросов или power query, но это может быть слишком сложно или наоборот долго, ну, и я зачем мы владеем python? 😃

Прежде чем мы перейдем к конкретной задаче, расскажу, что в Python с excel-документами можно и нужно работать с помощью pandas, openpyxl, xlrd, xlutils и pyexcel.

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

Объединение нескольких excel-файлов

Да, эту задачу можно решить ручками или power query, где несколько таблиц передаете в запрос и объединяете.

Например, на работе вы можете работать с ежемесячными отчётами о продажах, и с 90% вероятностью вы будете заниматься консолидацией региональных отчётов. Однажды вас попросят подготовить не только сконсолидированный отчёт, но и найти/подсчитать общее количество продаж по всем этим отчетам — и всё как можно быстрее.

Вариант номер 1, описан выше. Excel, Power Query, добавляете отчёты, объединяете, открываете, смотрите.

Вариант номер 2. Excel, несколько таблиц, переносим вкладки в один документ, создаете еще одну вкладку с шаблоном, делаете подсчёты с помощью формул. Смотрите.

А можно Вариант номер 3. Объединить все excel-файлы в python, используя библиотеку pandas (да-да, pandas).

Устанавливаем pandas используя pip или conda в терминале:

И рассмотрим вариант работы с отчетом, который сделан по одному шаблону (например, придумаем самый простеньких отчёт о продажах фруктов).

Можно заметить, что наш отчёт начинается не с первой ячейки, а только с 4-й строчки, поэтому нам понадобятся данные для импорта, начиная с этой строки (мне кажется, здорово такоё вариант учесть, потому что в работе часто такое встречается). В приведенном ниже коде мы будем использовать функции read_excel и append .

Давайте посмотрим на то, что мы сделали здесь:

В первой части мы импортировали pandas , создали список со всеми url-адресами и сгенерировали пустой фрейм данных под названием merger
Во второй части мы просмотрели каждый элемент списка (url-адрес) в files , чтобы прочитать каждый файл, пропустив первые три строчки ( skiprows = 3 ) и добавив его в объединенный датафрейм( merger ).
В третьей части мы генерируем новый excel-файл с именем merger.xlsx , содержащий наши объединенные файлы!

Получение значений нескольких файлов

Давайте посмотрим на другой пример.

Скажем, нам нужно было получить итого только по Москве из каждого отчета о продажах и собрать их в список. Мы знаем, что сумма сохраняется в ячейке F5 в каждой книге.

Для этого примера мы будем использовать другую библиотеку — openpyxl . Вы можете установить её с помощью pip или conda, используя код ниже:

А теперь посмотрим код и что он выполняет:

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

Давайте разберем это шаг за шагом, сначала мы:

Создаем список ( files ), который содержит ссылки на все наши файлы. В Windows мы можем нажать Shift + правой кнопкой мыши и использовать Копировать как путь (или, Copy as Path), чтобы получить путь к файлу.
И создаем пустой список для хранения наших значений ( values )
Пишем цикл, который будет выполнять нужные нам манипуляции с каждым файлом:

— с помощью метода .load_workbook() загружаем файл

— и используем атрибут .value , чтобы извлечь значение ячейки и добавить его в список values методом .append()

Применение формул в книгах

В каждом из excel-файлов у нас есть итоги по строкам, но это не общая сумма продаж. Опять же, мы можем открыть каждую книгу и добавить формулу ручками, или мы можем использовать python, чтобы сделать это для нас.

Мы снова будем использовать openpyxl . Если вам нужно установить его, сделайте это по инструкции выше.

В этом фрагменте кода мы снова заполняем список файлов. Цикл for открывает каждый файл и присваивает соответствующее название листа.
Затем мы присваиваем строку = SUM(F5: F8) ячейке F9 и используем атрибут style для назначения стиля ячейки. Больше стилей ячеек можно найти в официальной документации.

А теперь я дополню эту связку, вот таким вот открытием — автоматизировать Excel, и по сути заменить VBA (в моём понимании) можно библиотекой xlwings.

Автор xlwings говорит, что библиотека “Make Excel Fly!”. Вы можете использовать xlwings + Python для следующих задач:

Автоматизируйте Excel с помощью Python, например, создавая отчеты (другой пример описала выше)
Напишите макросы на Python и запустите их из Excel, нажав на кнопку
Напишите пользовательские функции в Python и вызовите эти функции из Excel так же, как и любую другую функцию Excel

Попробуем установить и рассмотреть несложный первый пункт. Пункты 2 и 3 рассмотрим в следующей серии.

Существует две части для установки xlwings: библиотека Python и надстройка Excel. Давайте начнем с установки библиотеки Python через командную строку:

Затем загрузите надстройку Excel из официального репозитория xlwings на Github. Это xlwings.xlam файл на странице (если вы вдруг будете читать эту статью через год, берите последнюю версию)

Положите xlwings.xlam-файл в папку надстройки Excel, которая является:

C:\Users\xxxx\AppData\Roaming\Microsoft\AddIns

Xxxx — это ваше собственное имя пользователя на вашем компьютере.

У меня получилось так, что я могу сейчас показать как это работает в английской и в русской версии Excel.

Затем откройте Excel, Файл -> Параметры -> Надстройки (или File -> Options -> Add-ins), нажимаем кнопку “Перейти..” (Go..) выбирая Надстройки Excel (Excel Add-ins).

Читайте также: