Python в excel вместо vba
Уверенное владение 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() загружаем файл
— используем ['Sheet1'] и ['F5'] для ссылки на имя листа, так и на ссылки на ячейки таблицы (на нужном нам листе в рабочей книге)
Если у вас Excel на русском языке, то вместо 'Sheet1' указывайте 'Лист1' .
— и используем атрибут .value , чтобы извлечь значение ячейки и добавить его в список values методом .append()
Применение формул в книгах
В каждом из excel-файлов у нас есть итоги по строкам, но это не общая сумма продаж. Опять же, мы можем открыть каждую книгу и добавить формулу ручками, или мы можем использовать python, чтобы сделать это для нас.
Мы снова будем использовать openpyxl . Если вам нужно установить его, сделайте это по инструкции выше.
- В этом фрагменте кода мы снова заполняем список файлов. Цикл for открывает каждый файл и присваивает соответствующее название листа.
- Затем мы присваиваем строку = SUM(F5: F8) ячейке F9 и используем атрибут style для назначения стиля ячейки. Больше стилей ячеек можно найти в официальной документации.
Если у вас Excel на русском языке, то вместо 'Sheet1' указывайте 'Лист1' и записывайте формулы соответственно на русском, например, =СУММ(F5:F8) .
А теперь я дополню эту связку, вот таким вот открытием — автоматизировать 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).
Когда появится окно надстроек, нажмите на кнопку “Обзор” (Browse..).
Это приведет вас непосредственно к папке Addins (куда вы добавили файл), просто выберите xlwings.xlam файл из папки и нажмите кнопку “ОК”. Надстройка xlwings теперь добавлена в ваш Excel, она будет автоматически включена всякий раз, когда вы запускаете приложение Excel.
Теперь вкладка xlwings должна появиться на ленте Excel.
На этом настройка завершена!
С интерпретатором кода Python можно работать через командную строку/терминал. Если в консоли выполнить команду python (или python3 , в зависимости от того как у вас настроено) без параметров, то интерпретатор Python запустится здесь и сейчас.
Теперь можно вводить в командную строку любые скрипты python — и они будут выполняться прямо в терминале. Это похоже на работу командной строки, но вместо команд для работы с файлами выполняется программный код, строчка за строчкой.
Символы >>> — это приглашение для ввода команд, то же, что и знак в командной строке.
Запустите следующий скрипт Python, который откроет новый экземпляр Excel.
Затем у вас откроется новый файл.
Здесь, wb относится к новому (и открытому) файлу Excel, и это также объект Python в то же время, что означает, что мы можем манипулировать им (файлом Excel) в Python!
Попробуйте следующий код, который позволит вам вводить значения из Python в Excel.
Если у вас Excel на русском языке, то вместо 'Sheet1' указывайте 'Лист1' .
Мы также будем использовать .range((x,y)) для обозначения отдельных ячеек в Excel. Где x относится к строке, а y относится к столбцу.
Так .range((3, 2)) значит клетка B3 (это на случай если вы отключили стиль ссылок R1C1) или R3C2.
Кроме того, можно писать формулы в Excel с помощью Python. В основном мы записываем строку в ячейку. Здесь мы хотим вычислить экспоненциальные значения оси x в другом столбце. В приведенном ниже коде мы используем “f-string”, который является улучшенным синтаксисом форматирования строк, начиная с Python 3.6.
Сохранение данных из Excel в датафрейм так же просто, как и само создание документа выше. Прочитаем данные Excel в Python в виде списка.
Попробуйте следующий код, если вы хотите прочитать данные excel в python в pandas виде датафрейма. .expand() автоматически определяет размер данных и .options() указывает, что нам нужен pandas .
Мы сбрасываем индекс в конце, так что ось x будет рассматриваться как столбец, а не в качестве индекса.
Теперь можно построить и график! Поскольку данные уже в python, мы можем создать график, а затем поместить его в excel-файл.
Для построения графика мы будем использовать matplotlib .
Собственно как и всегда, мы должны сохранить и закрыть файл! Вспоминаем, что wb относится к рабочей книге.
В заключение
Python делает работу с excel-файлами довольно простой. Мы рассмотрели, как объединять различные excel-файлы, получать конкретные значения и добавлять формулы сразу в несколько файлов одновременно, а также поставили надстройку в excel и попробовали создать простенький файл.
Несмотря на то, что на работе порой вы можете застрять в excel на целый день, а то и вся рутина у вас в нём, python поможет автоматизировать какие-нибудь простенькие задачки (для начала).
В следующий раз постараюсь разобраться с вами как работать с VBA и писать функции в Python, а затем пользоваться ими в excel.
Поводом для заметки послужила статья на Хабре, в которой автор описывал, как он решал на Python задачу сбора и анализа метаданных из файлов Excel.
Эта заметка более подробно раскрывает всем известный тезис: Под конкретную задачу надо выбирать наиболее подходящий инструмент применимо к офисной автоматизации.
VBA и Python
VBA (Visual Basic for Applications), де-факто, самый популярный язык для автоматизации Microsoft Office. Доступен из коробки, помимо Excel, работает в PowerPoint, Outlook, Access, Project и других приложениях.
Если задать вопрос: «Какой язык программирования выбрать первым», то где-то в 90% всех случаев будет предложен Python. На практике здесь может быть и любой другой язык, но, исходя из популярности языка и своего опыта, буду сравнивать с ним.
В общем виде можно описать ситуацию через подобный график:
Детального сравнения не будет, рассмотрим основные killer-фичи, в ситуации, когда junior-программист/офисный сотрудник хочет автоматизировать что-либо, связанное с MS Office, и у него есть возможность выбора между языками.
Если в силу разных причин возможности выбора нет, то и сравнивать нечего.
В пользу VBA
- Отличная работа с объектной моделью Excel и других приложений MS Office. Написание кода на VBA для большинства внутренних операций тривиально. У Python, в сравнении с VBA, поддержка объектной модели Office очень слабая.
- Поддержка разных форматов MS Office. Самая большая проблема для внешних языков — это работа с разными форматами файлов MS Office. Например, xls, xlsx, xlsm файлы могут требовать разных библиотек, так как каждая хорошо работает только со своим форматом файла. Для VBA — это все "файл Excel", работа с которыми в целом одинаково хороша.
- Работа с MS Exchange. Если необходимо обеспечить работу с корпоративной почтой/календарем на Exchange, то далеко не каждом языке есть нормальная библиотека для работы протоколом Exchange. В VBA это решается относительно просто через использование в макросе объектной модели MS Outlook.
- Легкая установка и дистрибуция. К уже установленному офису не надо ничего устанавливать. Чтобы коллега мог воспользоваться программой, достаточно передать ему файл с макросом. Легко сделать надстройку, которая позволит "установить" модель макроса в фон офиса.
- Интерактивность внутри приложений MS Office. Внутри офисных программ можно как просто поставить кнопки запуска макросов, так и (чуть сложнее) сделать целый отдельный UI. Сюда же относится написание своих формул в Excel и то, что макросы могут воздействовать на объекты внутри документов Office в реальном времени.
- Запись макросов. Удобный инструмент, который позволяет записать действия человека в готовый код, для последующего редактирования использования.
В пользу Python (и других внешних языков программирования)
- Приятный синтаксис и синтаксический сахар. Если коротко, то VBA не отличается выразительностью и удобством. Это вопрос личного вкуса, но для меня Python намного удобнее.
- Богатая экосистема библиотек. Огромный выбор готовых библиотек для работы с внешним миром. Пытаться сделать на VBA программу, взаимодействующую с каким-нибудь внешним API, та еще боль. Занимательно, что как раз для работы с файлами Office библиотеки того же Python — откровенно "на троечку".
- Хорошие средства разработки. Можно выбрать из огромного выбор программ, которые облегчают процесс разработки. Стандартный редактор VBA из Office предлагает очень бедный функционал и, в сравнении с альтернативами из мира Python, откровенно неудобен. Писать код VBA в внешнем редакторе, а потом копировать внутрь офиса для отладки — тоже неудобно.
- Скорость работы. Не проверял скорость однопоточной работы, но, предположу, что в случае однопоточной работы преимущество будет за Python. В любом случае, достаточно тривиально организуется многопоточная обработка данных/файлов, что позволяет говорить в большей достижимой скорости.
Кейсы
Далее приведены конкретные задачи, которые я сам решал или автоматизировал, и когда мне надо было выбрать стек: VBA или Python. Для каждой задачи указан выбранный стек и даны пояснения почему:
- Задача: Программа для проверки всех файлов Excel в директории на предмет наличия скрытых листов
- Мой выбор: VBA. Причины: простота работы с разными форматами файлов Excel и отсутствие внешних взаимодействий.
- Сервис был реализован как почтовый бот, на адрес которого пользователь может переслать файлы Office, а в ответ пользователю по почте приходит ответ с файлами PDF.
- Мой выбор: Логика VBA + Python для мониторинга
- Во-первых, внутренние функции гарантированно сохраняли PDF, аутентичный файлу PowerPoint (внешние библиотеки плохо справляются с рендером PowerPoint).
- Во-вторых, реализация почтового бота, как макроса в MS Outlook решала проблемы работы с корпоративной Exchange почтой. Так, в Python нет нормальной библиотеки для работы с MS Exchange.
- Python использовался для организации мониторинга работы сервиса и нотификации о возможных проблемах
- Мой выбор: VBA. Задача решалась через конвертацию двух файлов в PDF и их объединением с Riffle Shuffle. Так как важно качество конвертации в PDF, то использовались встроенные функции офиса для экспорта в PDF.
- Мой выбор: Python.
- Чтобы вытаскивать из html карточек данные пригодилась библиотека для парсинга html BeautifulSoup.
- Excel-файл создает программа, поэтому мы сами можем решать, какую аналитику рассчитывать уже в нем, а какую еще на стадии подготовки данных в Python.
- Мой выбор: VBA. Важно было аккуратно работать с текстом во внутренних объектах файла PowerPoint. Для перевода использовался API от Яндекса, так как он бесплатен для небольших объемов и прост в подклюении. Например, API переводчика Bing я так и не смог заставить работать в VBA, так как там для работы нужен OAuth со своими заморочками. Если бы пришлось работать с Bing, то, наверное, я бы делал сервис-посредник на Python.
- Мой выбор: Python. Хотя API — простой (не требует какой-либо подписи запросов или авторизации) и выдает данные в CSV, выбран Python, так как нет причин выбирать VBA, а на Python писать удобнее.
- Здесь я выбирал очень долго, так как есть много альтернатив:
- Сторонняя готовая система поручений
- База данных с каким-то обработчиком
- Access
- Excel
- Во-первых, Excel сам по себе является готовым UI для работы
- Во-вторых, VBA решает задачу работы с корпоративным Exchange через подключение MS Outlook
- В-третьих, это решение делалось с прицелом на коллег, которым Excel был более понятен, чем что-либо совсем новое
Заключение
Надеюсь, для кого-то заметка будет полезна и позволит сэкономить время на выборе стека для решения своих задач.
В сегодняшней статье я хотел бы, как можно подробнее, рассмотреть интеграцию приложений 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.
Кроме этого в статье я попытался несколько обобщить разборасанный по сети материал, т.к. такие вопросы часто фигурируют на форумах и думаю некоторым будет полезно иметь, такую «шпаргалку» под рукой.
Эта библиотека пригодится, если вы хотите читать и редактировать файлы .xlsx, xlsm, xltx и xltm.
Установите openpyxl using pip. Общие рекомендации по установке этой библиотеки — сделать это в виртуальной среде Python без системных библиотек. Вы можете использовать виртуальную среду для создания изолированных сред Python: она создает папку, содержащую все необходимые файлы, для использования библиотек, которые потребуются для Python.
Перейдите в директорию, в которой находится ваш проект, и повторно активируйте виртуальную среду venv. Затем перейдите к установке openpyxl с помощью pip, чтобы убедиться, что вы можете читать и записывать с ним файлы:
Теперь, когда вы установили openpyxl, вы можете начать загрузку данных. Но что именно это за данные? Например, в книге с данными, которые вы пытаетесь получить на Python, есть следующие листы:
На первый взгляд, с этими объектами Worksheet мало что можно сделать. Однако, можно извлекать значения из определенных ячеек на листе книги, используя квадратные скобки [], к которым нужно передавать точную ячейку, из которой вы хотите получить значение.Обратите внимание, это похоже на выбор, получение и индексирование массивов NumPy и Pandas DataFrames, но это еще не все, что нужно сделать, чтобы получить значение. Нужно еще добавить значение атрибута:
Помимо value, есть и другие атрибуты, которые можно использовать для проверки ячейки, а именно row, column и coordinate:Атрибут row вернет 2;
Добавление атрибута column к “С” даст вам «B»;
coordinate вернет «B2».Вы также можете получить значения ячеек с помощью функции cell (). Передайте аргументы row и column, добавьте значения к этим аргументам, которые соответствуют значениям ячейки, которые вы хотите получить, и, конечно же, не забудьте добавить атрибут value:
Обратите внимание: если вы не укажете значение атрибута value, вы получите , который ничего не говорит о значении, которое содержится в этой конкретной ячейке.Вы используете цикл с помощью функции range (), чтобы помочь вам вывести значения строк, которые имеют значения в столбце 2. Если эти конкретные ячейки пусты, вы получите None.
Более того, существуют специальные функции, которые вы можете вызвать, чтобы получить другие значения, например get_column_letter () и column_index_from_string.В двух функциях уже более или менее указано, что вы можете получить, используя их. Но лучше всего сделать их явными: пока вы можете получить букву прежнего столбца, можно сделать обратное или получить индекс столбца, перебирая букву за буквой. Как это работает:
Вы уже получили значения для строк, которые имеют значения в определенном столбце, но что нужно сделать, если нужно вывести строки файла, не сосредотачиваясь только на одном столбце?Конечно, использовать другой цикл.
Обратите внимание, что выбор области очень похож на выбор, получение и индексирование списка и элементы NumPy, где вы также используете квадратные скобки и двоеточие чтобы указать область, из которой вы хотите получить значения. Кроме того, вышеприведенный цикл также хорошо использует атрибуты ячейки!Чтобы визуализировать описанное выше, возможно, вы захотите проверить результат, который вернет вам завершенный цикл:
Наконец, есть некоторые атрибуты, которые вы можете использовать для проверки результата импорта, а именно max_row и max_column. Эти атрибуты, конечно, являются общими способами обеспечения правильной загрузки данных, но тем не менее в данном случае они могут и будут полезны.
Это все очень классно, но мы почти слышим, что вы сейчас думаете, что это ужасно трудный способ работать с файлами, особенно если нужно еще и управлять данными.
Должно быть что-то проще, не так ли? Всё так!Openpyxl имеет поддержку Pandas DataFrames. И можно использовать функцию DataFrame () из пакета Pandas, чтобы поместить значения листа в DataFrame:
Затем вы можете начать управлять данными при помощи всех функций, которые есть в Pandas. Но помните, что вы находитесь в виртуальной среде, поэтому, если библиотека еще не подключена, вам нужно будет установить ее снова через pip.Чтобы записать Pandas DataFrames обратно в файл Excel, можно использовать функцию dataframe_to_rows () из модуля utils:
Но это определенно не все! Библиотека openpyxl предлагает вам высокую гибкость в отношении того, как вы записываете свои данные в файлы Excel, изменяете стили ячеек или используете режим только для записи. Это делает ее одной из тех библиотек, которую вам точно необходимо знать, если вы часто работаете с электронными таблицами.И не забудьте деактивировать виртуальную среду, когда закончите работу с данными!
Теперь давайте рассмотрим некоторые другие библиотеки, которые вы можете использовать для получения данных в электронной таблице на Python.
Готовы узнать больше?
Чтение и форматирование Excel файлов xlrd
Эта библиотека идеальна, если вы хотите читать данные и форматировать данные в файлах с расширением .xls или .xlsx.
Если вы не хотите рассматривать всю книгу, можно использовать такие функции, как sheet_by_name () или sheet_by_index (), чтобы извлекать листы, которые необходимо использовать в анализе.
Наконец, можно получить значения по определенным координатам, обозначенным индексами.
О том, как xlwt и xlutils, соотносятся с xlrd расскажем дальше.Запись данных в Excel файл при помощи xlrd
Если нужно создать электронные таблицы, в которых есть данные, кроме библиотеки XlsxWriter можно использовать библиотеки xlwt. Xlwt идеально подходит для записи и форматирования данных в файлы с расширением .xls.
Когда вы вручную хотите записать в файл, это будет выглядеть так:
Если нужно записать данные в файл, то для минимизации ручного труда можно прибегнуть к циклу for. Это позволит немного автоматизировать процесс. Делаем скрипт, в котором создается книга, в которую добавляется лист. Далее указываем список со столбцами и со значениями, которые будут перенесены на рабочий лист.Цикл for будет следить за тем, чтобы все значения попадали в файл: задаем, что с каждым элементом в диапазоне от 0 до 4 (5 не включено) мы собираемся производить действия. Будем заполнять значения строка за строкой. Для этого указываем row элемент, который будет “прыгать” в каждом цикле. А далее у нас следующий for цикл, который пройдется по столбцам листа. Задаем условие, что для каждой строки на листе смотрим на столбец и заполняем значение для каждого столбца в строке. Когда заполнили все столбцы строки значениями, переходим к следующей строке, пока не заполним все имеющиеся строки.
В качестве примера скриншот результирующего файла:Теперь, когда вы видели, как xlrd и xlwt взаимодействуют вместе, пришло время посмотреть на библиотеку, которая тесно связана с этими двумя: xlutils.
Коллекция утилит xlutils
Эта библиотека в основном представляет собой набор утилит, для которых требуются как xlrd, так и xlwt. Включает в себя возможность копировать и изменять/фильтровать существующие файлы. Вообще говоря, оба этих случая подпадают теперь под openpyxl.
Использование pyexcel для чтения файлов .xls или .xlsx
Еще одна библиотека, которую можно использовать для чтения данных таблиц в Python — pyexcel. Это Python Wrapper, который предоставляет один API для чтения, обработки и записи данных в файлах .csv, .ods, .xls, .xlsx и .xlsm.
Чтобы получить данные в массиве, можно использовать функцию get_array (), которая содержится в пакете pyexcel:
Однако, если вы хотите вернуть в словарь двумерные массивы или, иными словами, получить все листы книги в одном словаре, стоит использовать функцию get_book_dict ().Имейте в виду, что обе упомянутые структуры данных, массивы и словари вашей электронной таблицы, позволяют создавать DataFrames ваших данных с помощью pd.DataFrame (). Это упростит обработку ваших данных!
Наконец, вы можете просто получить записи с pyexcel благодаря функции get_records (). Просто передайте аргумент file_name функции и обратно получите список словарей:
Записи файлов при помощи pyexcelТак же, как загрузить данные в массивы с помощью этого пакета, можно также легко экспортировать массивы обратно в электронную таблицу. Для этого используется функция save_as () с передачей массива и имени целевого файла в аргумент dest_file_name:
Обратите внимание: если указать разделитель, то можно добавить аргумент dest_delimiter и передать символ, который хотите использовать, в качестве разделителя между “”.Однако, если у вас есть словарь, нужно будет использовать функцию save_book_as (). Передайте двумерный словарь в bookdict и укажите имя файла, и все ОК:
Помните, что когда используете код, который напечатан в фрагменте кода выше, порядок данных в словаре не будет сохранен!Чтение и запись .csv файлов
Если вы все еще ищете библиотеки, которые позволяют загружать и записывать данные в CSV-файлы, кроме Pandas, рекомендуем библиотеку csv:
Обратите внимание, что NumPy имеет функцию genfromtxt (), которая позволяет загружать данные, содержащиеся в CSV-файлах в массивах, которые затем можно помещать в DataFrames.Финальная проверка данных
Когда данные подготовлены, не забудьте последний шаг: проверьте правильность загрузки данных. Если вы поместили свои данные в DataFrame, вы можете легко и быстро проверить, был ли импорт успешным, выполнив следующие команды:
Note: Используйте DataCamp Pandas Cheat Sheet, когда вы планируете загружать файлы в виде Pandas DataFrames.Если данные в массиве, вы можете проверить его, используя следующие атрибуты массива: shape, ndim, dtype и т.д.:
Поздравляем, теперь вы знаете, как читать файлы Excel в Python :) Но импорт данных — это только начало рабочего процесса в области данных. Когда у вас есть данные из электронных таблиц в вашей среде, вы можете сосредоточиться на том, что действительно важно: на анализе данных.
Если вы хотите глубже погрузиться в тему — знакомьтесь с PyXll, которая позволяет записывать функции в Python и вызывать их в Excel.
Everything you can write in VBA can be done in Python. This page contains information that will help you translate your VBA code into Python.
Please note that the Excel Object Model is part of Excel and documented by Microsoft. The classes and methods from that API used in this documentation are not part of PyXLL, and so please refer to the Excel Object Model documentation for more details about their use.
The Excel Object Model ¶
When programming in VBA you interact with the Excel Object Model. For example, when writing
what you are doing is constructing a Range object and calling the Select method on it. The Range object is part of the Excel Object Model.
Most of what people talk about in reference to VBA in Excel is actually the Excel Object Model, rather than the VBA language itself. Once you understand how to interact with the Excel Object Model from Python then replacing your VBA code with Python code becomes straightforward.
The Excel Object Model is well documented by Microsoft as part of the Office VBA Reference.
The first hurdle people often face when starting to write Excel macros in Python is finding documentation for the Excel Python classes. Once you realise that the Object Model is the same across Python and VBA you will see that the classes documented in the Office VBA Reference are the exact same classes that you use from Python, and so you can use the same documentation even though the example code may be written in VBA.
Accessing the Excel Object Model in Python ¶
The Excel Object Model is made available to all languages using COM. Python has a couple of packages that make calling COM interfaces very easy. If you know nothing about COM then there’s no need to worry as you don’t need to in order to call the Excel COM API from Python.
The top-level object in the Excel Object Model is the Application object. This represents the Excel application, and all other objects are accessed via this object.
PyXLL provides a helper function, xl_app , for retrieving the Excel Application object. By default, it uses the Python package win32com , which is part of the pywin32 package [1].
If you don’t already have the pywin32 package installed you can do so using pip :
Or if you are using Anaconda you can use conda :
You can use xl_app to access the Excel Application object from an Excel macro. The following example shows how to re-write the Macro1 VBA code sample from the section above.
Note that in VBA there is an implicit object, which related to where the VBA Sub (macro) was written. Commonly, VBA code is written directly on a sheet, and the sheet is implied in various calls. In the Macro1 example above, the Range is actually a method on the sheet that macro was written on. In Python, we need to explicitly get the current active sheet instead.
You can call into Excel using the Excel Object Model from macros and menu functions, and use a sub-set of the Excel functionality from worksheet functions, where more care must be taken because the functions are called during Excel’s calculation process.
You can remove these restrictions by calling the PyXLL schedule_call function to schedule a Python function to be called in a way that lets you use the Excel Object Model safely. For example, it’s not possible to update worksheet cell values from a worksheet function, but it is possible to schedule a call using schedule_call and have that call update the worksheet after Excel has finished calculating.
For testing, it can also be helpful to call into Excel from a Python prompt (or a Jupyter notebook). This can also be done using xl_app , and in that case the first open Excel instance found will be returned.
You might try this using win32com directly rather than xl_app . We do not advise this when calling your Python code from Excel however, as it may return an Excel instance other than the one you expect.
Differences between VBA and Python ¶
Case Sensitivity ¶
Python is case sensitive. This means that code fragments like r.Value and r.value are different (note the capital V in the first case. In VBA they would be treated the same, but in Python you have to pay attention to the case you use in your code.
If something is not working as expected, check the PyXLL log file. Any uncaught exceptions will be logged there, and if you have attempted to access a property using the wrong case then you will probably see an AttributeError exception.
Calling Methods ¶
In Python, parentheses ( () ) are always used when calling a method. In VBA, they may be omitted. Neglecting to add parentheses in Python will result in the method not being called, so it’s important to be aware of which class attributes are methods (and must therefore be called) and which are properties (whose values are available by reference).
For example, the method Select on the Range type is a method and so must be called with parentheses in Python, but in VBA they can be, and usually are, omitted.
Keyword arguments may be passed in both VBA and Python, but in Python keyword arguments use = instead of the := used in VBA.
Accessing properties does not require parentheses, and doing so will give unexpected results! For example, the range.Value property will return the value of the range. Adding () to it will attempt to call that value, and as the value will not be callable it will result in an error.
Named Arguments ¶
In VBA, named arguments are passed using Name := Value . In Python, the syntax is slightly different and only the equals sign is used. One other important difference is that VBA is not case-sensitive but Python is. This applies to argument names as well as method and property names.
In VBA, you might write
If you look at the documentation for Application.InputBox you will see that the argument names are cased different from this, and are actually ‘Prompt’ and ‘Type’. In Python, you can’t get away with getting the case wrong like you can in VBA.
In Python, this same method would be called as
Properties ¶
Both VBA and Python support properties. Accessing a property from an object is similar in both languages. For example, to fetch ActiveSheet property from the Application object you would do the following in VBA:
In Python, the syntax used is identical:
Properties with Arguments ¶
In VBA, the distinction between methods and properties is somewhat blurred as properties in VBA can take arguments. In Python, a property never takes arguments. To get around this difference, the win32com Excel classes have Get and Set methods for properties that take arguments, in addition to the property.
The Range.Offset property is an example of a property that takes optional arguments. If called with no arguments it simply returns the same Range object. To call it with arguments in Python, the GetOffset method must be used instead of the Offset property.
The following code activates the cell three columns to the right of and three rows down from the active cell on Sheet1:
To convert this to Python we must make the following changes:
- Replace the Offset property with the GetOffset method in order to pass the arguemnts.
- Replace rowOffset and columnOffsetRowOffset and ColumnOffset as specified in the Range.Offset documentation.
- Call the Activate method by adding parentheses in both places it’s used.
You may wonder, what would happen if you were to use the Offset property in Python? As you may by now expect, it would fail - but not perhaps in the way you might think.
If you were to call xl.ActiveCell.Offset(RowOffset=3, ColumnOffset=3) the the result would be that the parameter RowOffset is invalid. What’s actually happening is that when xl.ActiveCell.Offset is evaluated, the Offset property returns a Range equivalent to ActiveCell, and that Range is then called.
Range has a default method. In Python this translates to the Range class being callable, and calling it calls the default method.
The default method for Range is Item, and so this bit of code is actually equivalent to xl.ActiveCell.Offset.Item(RowOffset=3, ColumnOffset=3) . The Item method doesn’t expect a RowOffset argument, and so that’s why it fails in this way.
Implicit Objects and ‘With’ ¶
When writing VBA code, the code is usually written ‘on’ an object like a WorkBook or a Sheet. That object is used implicitly when writing VBA code.
If using a ‘With..End’ statement in VBA, the target of the ‘With’ statement becomes the implicit object.
If a property is not found on the current implicit object (e.g. the one specified in a ‘With..End’ statement) then the next one is tried (e.g. the Worksheet the Sub routine is associated with). Finally, the Excel Application object is implicitly used.
In Python there is no implicit object and the object you want to reference must be specified explicitly.
For example, the following VBA code selects a range and alters the column width.
To write the same code in Python each object has to be referenced explicitly.
Indexing Collections ¶
VBA uses parentheses ( () ) for calling methods and for indexing into collections.
In Python, square braces ( [] ) are used for indexing into collections.
Care should be taken when indexing into Excel collections, as Excel uses an index offset of 1 whereas Python uses 0. This means that to get the first item in a normal Python collection you would use index 0, but when accessing collections from the Excel Object Model you would use 1.
Enums and Constant Values ¶
When writing VBA enum values are directly accessible in the global scope. For example, you can write
In Python, these enum values are available as constants in the win32com.client.constants package. The code above would be re-written in Python as follows
Excel and Threading ¶
In VBA everything always runs on Excel’s main thread. In Python we have multi-threading support and sometimes to perform a long running task you may want to run code on a background thread.
The standard Python threading module is a convenient way to run code on a background thread in Python. However, we have to be careful about how we call back into Excel from a background thread. As VBA has no ability to use threads the Excel objects are not written in a such a way that they can be used across different threads. Attempting to do so may result in serious problems and even cause Excel to crash!
In order to be able to work with multiple threads and still call back into Excel PyXLL has the schedule_call function. This is used to schedule a Python function to run on Excel’s main thread in such a way that the Excel objects can be used safely. Whenever you are working with threads and need to use the Excel API you should use schedule_call .
For example, you might use an Excel macro to start a long running task and when that task is complete write the result back to Excel. Instead of writing the result back to Excel from the background thread, use schedule_call instead.
Notes on Debugging ¶
The Excel VBA editor has integrating debugging so you can step through the code and see what’s happening at each stage.
When writing Python code it is sometimes easier to write the code outside of Excel in your Python IDE before adapting it to be called from Excel as a macro or menu function etc.
When calling your code from Excel, remember that any uncaught exceptions will be printed to the PyXLL log file and so that should always be the first place you look to find what’s going wrong.
If you find that you need to be able to step through your Python code as it is being executed in Excel you will need a Python IDE that supports remote debugging. Remote debugging is how debuggers connect to an external process that they didn’t start themselves.
You can find instructions for debugging Python code running in Excel in this blog post Debugging Your Python Excel Add-In.
Читайте также: