В сегодняшней статье я хотел бы, как можно подробнее, рассмотреть интеграцию приложений Python и MS Excel. Данные вопрос может возникнуть, например, при создании какой-либо системы онлайн отчетности, которая должна выгружать результаты в общепринятый формат ну или какие-либо другие задачи. Также в статье я покажу и обратную интеграцию, т.е. как использовать функцию написанную на python в Excel, что также может быть полезно для автоматизации отчетов.
Работаем с файлами MS Excel на Python
Для работы с Excel файлами из Python мне известны 2 варианта:
- Использование библиотек, таких как xlrd, xlwt, xlutils или openpyxl
- Работа с com-объектом
Использование библиотек
Итак, первый метод довольно простой и хорошо описан. Например, есть отличная статья для описания работы c xlrd, xlwt, xlutils. Поэтому в данном материале я приведу небольшой кусок кода с их использованием.
Для начала загрузим нужные библиотеки и откроем файл xls на чтение и выберем
Уроки Python / Работа с файлами Excel считываем данные и формулы
нужный лист с данными:
Теперь давайте посмотрим, как считать значения из нужных ячеек:
#получаем значение первой ячейки A1 val = sheet.row_values(0)[0] #получаем список значений из всех записей vals = [sheet.row_values(rownum) for rownum in range(sheet.nrows)]
Как видно чтение данных не составляет труда. Теперь запишем их в другой файл. Для этого создам новый excel файл с новой рабочей книгой:
wb = xlwt.Workbook() ws = wb.add_sheet(‘Test’)
Запишем в новый файл полученные ранее данные и сохраним изменения:
Из примера выше видно, что библиотека xlrd отвечает за чтение данных, а xlwt — за запись, поэтому нет возможности внести изменения в уже созданную книгу без ее копирования в новую. Кроме этого указанные библиотеки работают только с файлами формата xls (Excel 2003) и у них нет поддержки нового формата xlsx (Excel 2007 и выше).
Чтобы успешно работать с форматом xlsx, понадобится библиотека openpyxl. Для демонстрации ее работы проделаем действия, которые были показаны для предыдущих библиотек.
Для начала загрузим библиотеку и выберем нужную книгу и рабочий лист:
Как видно из вышеприведенного листинга сделать это не сложно. Теперь посмотрим как можно считать данные:
#считываем значение определенной ячейки val = sheet[‘A1’].value #считываем заданный диапазон vals = [v[0].value for v in sheet.range(‘A1:A2’)]
Отличие от прошлых библиотек в том, что openpyxl дает возможность отображаться к ячейкам и последовательностям через их имена, что довольно удобно и понятно при чтении программы.
Теперь посмотрим как нам произвести запись и сохранить данные:
Из примера видно, что запись, тоже производится довольно легко. Кроме того, в коде выше, можно заметить, что openpyxl кроме имен ячеек может работать и с их индексами.
К недостаткам данной библиотеки можно отнести, то что, как и в предыдущем примере, нет возможности сохранить изменения без создания новой книги.
Python + OpenPyXl = Excel, электронные таблицы на питоне. Как создать xlsx, изменить и стилизовать
Как было показано выше, для более менее полноценной работы с excel файлами, в данном случае, нужно 4 библиотеки, и это не всегда удобно. Кроме этого, возможно нужен будет доступ к VBA (допустим для какой-либо последующей обработки) и с помощью этих библиотек его не получить.
Однако, работа с этими библиотеками достаточно проста и удобна для быстрого создания Excel файлов их форматирования, но если Вам надо больше возможностей, то следующий подпункт для Вас.
Работа с com-объектом
В своих отчетах я предпочитаю использовать второй способ, а именно использование файла Excel через com-объект с использованием библиотеки win32com. Его преимуществом, является то, что вы можете выполнять с файлом все операции, которые позволяет делать обычный Excel с использованием VBA.
Проиллюстрируем это на той же задаче, что и предыдущие примеры.
Для начала загрузим нужную библиотеку и создадим COM объект.
import win32com.client Excel = win32com.client.Dispatch(«Excel.Application»)
Теперь мы можем работать с помощью объекта Excel мы можем получить доступ ко всем возможностям VBA. Давайте, для начала, откроем любую книгу и выберем активный лист. Это можно сделать так:
Давайте получим значение первой ячейки и последовательности:
#получаем значение первой ячейки val = sheet.Cells(1,1).value #получаем значения цепочки A1:A2 vals = [r[0].value for r in sheet.Range(«A1:A2»)]
Как можно заметить, мы оперируем здесь функциями чистого VBA. Это очень удобно если у вас есть написанные макросы и вы хотите использовать их при работе с Python при минимальных затратах на переделку кода.
Посмотрим, как можно произвести запись полученных значений:
#записываем значение в определенную ячейку sheet.Cells(1,2).value = val #записываем последовательность i = 1 for rec in vals: sheet.Cells(i,3).value = rec i = i + 1 #сохраняем рабочую книгу wb.Save() #закрываем ее wb.Close() #закрываем COM объект Excel.Quit()
Из примера видно, что данные операции тоже довольно просто реализовываются. Кроме этого, можно заметить, что изменения мы сохранили в той же книге, которую открыли для чтения, что достаточно удобно.
Однако, внимательный читатель, обратит внимание на переменную i, которая инициализируется не 0, как принято python, а 1. Это связано с тем, что мы работаем с индексами ячеек как из VBA, а там нумерация начинается не с 0, а с 1.
На этом закончим разбор способов работы с excel файлами в python и перейдем к обратной задаче.
Вызываем функции Python из MS Excel
Может возникнуть такая ситуация, что у вас уже есть какой-либо функция, которая обрабатывает данные на python, и нужно перенести ее функциональность в Excel. Конечно же можно переписать ее на VBA, но зачем?
Для использования функций python в Excel есть прекрасная надстройка ExcelPython. С ее помощью вы сможете вызывать функции написанные на python прямо из Excel, правда придется еще написать небольшую обертку на VBA, и все это будет показано ниже.
Итак, предположим у нас есть функция, написанная на python, которой мы хотим воспользоваться:
def get_unique(lists): sm = 0 for i in lists: sm = sm + int(i.pop()) return sm
На вход ей подается список, состоящий из списков, это одно из условий, которое должно выполняться для работы данной функции в Excel.
Сохраним функцию в файле plugin.py и положим его в ту же директорию, где будет лежать наш excel файл, с которым мы будем работать.
Теперь установим ExcelPython. Установка происходит через запуск exe-файла и не вызывает затруднений.
Когда все приготовления выполнены, открываем тестовый файл excel и вызовем редактор VBA (Alt+F11). Для работы с вышеуказанной надстройкой необходимо ее подключить, через Tools->References, как показано на рисунке:
Ну что же, теперь можно приступить к написанию функции-обертки для нашего Python-модуля plugin.py. Выглядеть она будет следующим образом:
Function sr(lists As Range) On Error GoTo do_error Set plugin = PyModule(«plugin», AddPath:=ThisWorkbook.Path) Set result = PyCall(plugin, «get_unique», PyTuple(lists.Value2)) sr = WorksheetFunction.Transpose(PyVar(result)) Exit Function do_error: sr = Err.Description End Function
Итак, что же происходит в данной функции?
Для начала, с помощью PyModule , мы подключаем нужный модуль. Для этого в качестве параметров ей передается имя модуля без расширения, и путь до папки в которой он находится. На выходе работы PyModule мы получаем объект для работы с модулем.
Затем, с помощью PyCall , вызываем нужную нам функцию из указанного модуля. В качестве параметров PyCall получает следующее:
- Объект модуля, полученный на предыдущем шаге
- Имя вызываемой функции
- Параметры, передаваемые функции (передаются в виде списка)
Теперь, чтобы убедиться в работоспособности нашей связки, вызовем нашу свежеиспеченую функцию на листе в Excel:
Как видно из рисунка все отработало правильно.
Надо отметить, что в данном материале используется старая версия ExcelPython, и на GitHub’e автора доступна новая версия.
В качестве заключения, надо отметить, примеры в данной статье самые простые и для более глубоко изучения данных методов, я рекомендую обратиться к
документации по нужным пакетам.
Также хочу заметить, что указанные пакеты не являются единственными и в статье опущено рассмотрение, таких пакетов как xlsxwriter для генерации excel файлов или xlwings, который может работать с Excel файлами «на лету», а также же PyXLL, который выполняет аналогичные функции ExcelPython.
Кроме этого в статье я попытался несколько обобщить разборасанный по сети материал, т.к. такие вопросы часто фигурируют на форумах и думаю некоторым будет полезно иметь, такую «шпаргалку» под рукой.
- Python
- Visual Basic for Applications
Источник: habr.com
Python Excel: примеры реализации и подробные инструкции к ним
Перед тем как рассказать , как при помощи Python сделать запись в Excel и что такое выгрузка в Ex c el, хотелось бы коротко остановиться на теории.
Python — это современный язык программирования, который в целом оптимизирован к работе с огромными массивами данных. Это как раз и позволяет ему ускорять работу больших и нагруженных таблиц Excel более чем в 10 раз и делать то, что сама по себе таблица не может , — проводить углубленный анализ данных и автоматизировать сложные вещи.
Что такое таблица Excel вы , скорее всего , знаете, потому что везде , где нужна эффективная работа с таблицами и диаграммами, используют Mi c rosoft Excel.
Устанавливаем Python на компьютер
- язык программирования «Питон»;
- и необходимые архивы для работы в Эксель — Pandas, Xlwings, NumPy и др.
- Запустит ь Anaconda Prompt (терминал в среде Анаконда);
- Пропи сать там простую команду «python»;
- Если все сделано верно, то у вас должна высветиться установивш ая ся версия Python.
Как выбрать IDE для написания кода
Для тех , кто не знает, IDE — это программа разработчиков, где собственно происходит создание кода на выбранном языке. В принципе , код возможно писать и в самом простейшем текстовом редакторе, однако это не очень удобно. IDEшки имеют ряд профессиональных преимуществ, которые делают их обязательными.
Специально для Python придумано множество таких программ. Так же есть множество программ для разных языков программирования, куда включен и наш. Возможно , у вас уже есть любимая IDE. Если не утруждать себя выбором, то можно остановиться на тех, которые уже есть внутри самой Anaconda. Это небезызвестный в узких кругах Jupyter Notebook или Spyder.
И та , и та IDE спокойно поможет делать запись или выгрузку в Excel. Они обе обладают нужным набором инструментов для успешной и быстрой разработк и (подсвечива ние синтаксис а , работ а автозаполнени я и автозавершени я , удобные при осуществлении отладки кода и т.д.). Выбор за вами.
Дополнения Python, требующиеся для выгрузки данных в Excel
- получать и сохранять данные из таблицы;
- оформлять ячейки таблицы своими собственными стилями;
- включать скрипты Питона прямо в файлах Эксель.
Делаем импорт Pandas и Xlwings
- Запускаем IDE, допустим , Spyder.
- Нужно очистить окно от кода, который прописан по умолчанию.
- Вставляем следующий код:
Import xlwings as xw
Import pandas as pd
print(xw.__version__)
print(pd.__version__)
Короткое объяснение тому , что мы сделали : д анными командами мы импортировали необходимые нам дополнения, а потом вывели их состояние в консоль Python. Мы сделали это, чтобы проверить корректность настройки и работы этих дополнений.
Чтение и запись информации в Excel при помощи Python
Как осуществляется выгрузка Python из Excel
Разберем простой пример. Допустим , мы хотим прочитать какую-то информацию из нашей таблицы Excel (или любой другой вашей, суть шагов остается та же) при помощи Python. Для этого нам нужно внести кое-какие корректировки в наш скрипт:
Запись информации из Python в Excel
Итак, считывать данны е из таблицы мы уже умеем. Но как сделать запись?
К примеру , мы хотим добавить один столбик в нашу таблицу. Мы можем осуществить это путем ввода следующей команды, сделав запись в DataFrame data_pd:
data_pd[‘Имя столбца’] = [«Значение», «Значение», «Значение»]
Вроде получилось, однако нам нужно полученную информацию вписать назад в наш документ Excel . В этом вам поможет следующая строка в скрипте:
data_excel.range(‘A1’).options(index = False).value = data_pd
Если посмотреть полностью на код, который у вас должен оказаться в скрипте, то это примерно следующее:
Когда все эти правки внесены , можете смело запускать скрипт . Потом просмотр ите свою таблицу. Там должен появиться новый столбик уже с данными («Значение»).
Это простой пример, как можно использовать Python, чтобы сделать запись или корректировку таблицы Excel.
Excel и Python. Основы взаимодействия.
Добрый день. Прочитав эту статью Вы узнаете, как можно выгружать данные из Excel в Python, их обрабатывать в нем и сохранять назад в Excel файлы.
Перед тем как начнем, хотелось бы сказать пару слов о том, зачем нам понадобился Python. Этот язык программирования очень неплохо оптимизирован для работы с большими массивами данных, что позволяет нам ускорить обработку больших Excel таблиц в десятки раз, а также применять имеющиеся в Python пакеты для углубленного анализа данных, что Excel делать не умеет.
Итак, начнем по этапам:
1. Устанавливаем Python.
Для этого нам понадобится замечательный продукт Anaconda, который включает в себя как сам Python, так и необходимы нам для работы пакеты, такие как Pandas, Xlwings, NumPy и другие.
Для того, что бы установить Anaconda, заходим на официальный сайт в раздел Download и скачиваем инсталлятор для Вашей операционной системы.
Настоятельно рекомендую брать дистрибутив с версией Python 3.*, так как Python 2.* морально устарел и все реже используется в новых продуктах. Ссылка на Anaconda здесь.
После того, как Вы скачали инсталлятор, запускаете и устанавливаете его. Процесс очень простой, подробно останавливаться на нем не буду. После того как Вы установили Anaconda, в меню должен появиться соответствующий раздел. Давайте проверим, что Python установлен и работает. Для этого запускаем Anaconda Prompt из соответствующего меню.
Прописываем команду python . Если у Вас все получилось, то должна отобразиться версия установленного у вас интерпритатора Python, наподобие сообщения ниже.
Поздравляю, Python Вы установили, можно двигаться дальше.
2. Выбираем IDE для написания кода.
IDE — это интерактивная среда для написания кода. Код можно писать хоть в блокноте, хоть в командной строке интерпритатора, но это очень неудобно. Поэтому все используют IDE. Для Python их создано множество, но я остановлюсь на двух из них, а именно Jupyter Notebook и Spider, так как они идут в комплекте поставки Anaconda.
Jupyter Notebook — это интерактивная среда разработки, которая позволяет выполнять код программы построчно и тем самым наблюдать за тем, что делает программа и какие выдает результаты.
Spyder — эта IDE, которая обладает необходимым функционалом, который помогает при написании кода, таким как подсветка синтаксиса, автозаполнения кода и т.п. Так же позволяет просматривать содержимое переменных после выполнения программы, что очень удобно при отладке и вылавливании багов.
Мне больше понравился Spyder и дальше примеры будут с его использованием, но выбор IDE — это личное дело каждого пользователя и настаивать на своем выборе я не буду.
3. Импорт необходимых пакетов Python.
Для экспорта данных из Excel в Python и их дальнейшей обработки, нам понадобятся два пакета: Pandas и Xlwings. Они оба уже включены в состав Anaconda, поэтому никаких дополнительных действий по их установки от Вас не потребуется.
Pandas — это дополнительный пакет к Python, который позволяет работать с данными, как с двумерными таблицами (Excel — это как раз и есть такая таблица).
Xlwings — также дополнительный пакет к Python, который отвечает за получение/сохранение данных из Excel файлов. Отличительными особенностями данного пакета является возможность не только редактировать данные в Excel, но также стиль оформления ячеек, создавать дополнительные листы и т.п. Супер-фишкой является возможность запускать Python скрипты прямо из Excel файлов, но об этой особенности поговорим в других уроках.
Итак, что бы импортировать Pandas и Xlwings, проделаем следующие операции: 1. Запустить Spyder. 2. Удаляем в появившемся окне кода, все что туда программа написала по умолчанию, так как это нам не нужно. 3. Вставляем слудеющий код:
import xlwings as xw
import pandas as pd
print (xw.__version__)
print (pd.__version__)
Объясню, что делает этот фрагмент кода. Первые две строки, которые начинаются со слова import, импортируют нужные нам пакеты, вторые две строки выводят их текущие версии в консоль Python. Это нужно, что бы проверить что все корректно работает.
Сохраняем скрипт в удобное Вам место и запускаем, если все сделали правильно в консоли будет выведены версии пакетов. В моем случае это 0.15.10, 0.25.1.
Все получилось, отлично, едем дальше. Если нет — задавайте вопросы к комментариях под статьей, буду стараться оперативно отвечать.
4. Чтение данных из Excel файла Python.
Для того, что бы прочитать эти данные, давайте внесем в скрипт изменения. Теперь наш скрипт будет выглядеть следующим образом:
import xlwings as xw
import pandas as pd
data_excel = wb.sheets[‘Данные’] — Получаем доступ к листу Данные из файла, записанного в переменную wb и присваиваем его переменной data_excel.
data_pd = data_excel.range(‘A1:C4’).options(pd.DataFrame, header = 1, index = False).value — Читаем данные с области листа A1:C4 и создаем переменную типа Pandas DataFrame (pd.DataFrame), при этом говорим что первая строка таблицы содержит заголовки (header = 1), а номеров по порядку или так называемых индексов у строк нет (index = False).
print(data_pd) — Выводим в консоль Python содержимого созданого нами DataFrame с именем data_pd.
5. Сохранение данных из Python в Excel.
Великолепно! Данные мы прочитали, теперь давайте их немного отредактируем и сохраним назад в Excel файл.
Сначало добавим в наш DataFrame data_pd еще один столбец Пол. Это можно сделать при помощи следующей команды:
Источник: www.excelguide.ru