Интеграция MS Excel и Python
В сегодняшней статье я хотел бы, как можно подробнее, рассмотреть интеграцию приложений Python и MS Excel. Данные вопрос может возникнуть, например, при создании какой-либо системы онлайн отчетности, которая должна выгружать результаты в общепринятый формат ну или какие-либо другие задачи. Также в статье я покажу и обратную интеграцию, т.е. как использовать функцию написанную на python в Excel, что также может быть полезно для автоматизации отчетов.
Работаем с файлами MS Excel на Python
Для работы с Excel файлами из Python мне известны 2 варианта:
Рассмотрим работу с этими способами подробнее. В качестве примера будем использовать готовый файл excel из которого мы сначала считаем данные из первой ячейки, а затем запишем их во вторую. Таких простых примеров будет достаточно для первого ознакомления.
Использование библиотек
Итак, первый метод довольно простой и хорошо описан. Например, есть отличная статья для описания работы c xlrd, xlwt, xlutils. Поэтому в данном материале я приведу небольшой кусок кода с их использованием.
Для начала загрузим нужные библиотеки и откроем файл xls на чтение и выберем нужный лист с данными:
import xlrd, xlwt #открываем файл rb = xlrd.open_workbook('../ArticleScripts/ExcelPython/xl.xls',formatting_info=True) #выбираем активный лист sheet = rb.sheet_by_index(0) Теперь давайте посмотрим, как считать значения из нужных ячеек:
#получаем значение первой ячейки A1 val = sheet.row_values(0) #получаем список значенний из всех записей vals = [sheet.row_values(rownum) for rownum in range(sheet.nrows)] Как видно чтение данных не составляет труда. Теперь запишем их в другой файл.
Для этого создами новый excel файл с новой рабочей книгой:
wb = xlwt.Workbook() ws = wb.add_sheet('Test') Теперь мы можем записать в новый файл полученные ранее данные и сохраним изменения:
#в A1 записываем значение из ячейки A1 прошлого файла ws.write(0, 0, val[0]) #в столбец B запишим нашу последовательность из столбца A исходного файла i = 0 for rec in vals: ws.write(i,1,rec[0]) i =+ i #сохраем рабочию книгу wb.save('../ArticleScripts/ExcelPython/xl_rec.xls') Из примера выше видно, что библиотка xlrd отвечает за чтение данных, а xlwt — за запись, поэтому нет возможности внести изменения в уже созданную книгу без ее копирования в новую. Кроме этого указанные библиотеки работают только с файлами формата xls (Excel 2003) и у них нет поддержки нового формата xlsx (Excel 2007 и выше).
Чтобы успешно работать с форматом xlsx, понадобится библиотека openpyxl. Для демонстрации ее работы проделаем действия, которые были показаны для проедыдущих библиотек.
Для начала загрузим библиотеку и выберем нужную книгу и рабочий лист:
import openpyxl wb = openpyxl.load_workbook(filename = '../ArticleScripts/ExcelPython/openpyxl.xlsx') sheet = wb['test'] Как видно из вышеприведенного листинга сделать этьо не сложно. Теперь посмотрим как можно считать данные:
#считываем значение определенной ячейки val = sheet['A1'].value #считываем заданный диапазон vals = [v[0].value for v in sheet.range('A1:A2')] Отличие от прошлых библиотек в том, что openpyxl дает возможность ображатся к ячейкам и последовательностям через их имена, что довольно удобно и понятно при чтении программы.
Тепереь посмотрим как нам поизвести запись и сохранить данные:
#записываем значение в определенную ячейку sheet['B1'] = val #записываем последовательность i = 0 for rec in vals: sheet.cell(row=i, column=2).value = rec i =+ 1 # сохраняем данные wb.save('../ArticleScripts/ExcelPython/openpyxl.xlsx') Как видно из примера запись, тоже производиться довольно легко. Кроме того, в коде выше, можно заметить, что openpyxl кроме имен ячеек может работать и с их индексами.
К недостаткам данной библиотеки можно отнести, то что, как и в предыдущем примере, нет возможности сохранить изменения без создания новой книги.
Как видно из показанных примеров, для более менее полноценной работы с excel файлами, в данном случае, нужно 4 библиотеки, и это не всегда удобно. Кроме этого, возможно нужен будет доступ к VBA (допустим для какой либо последующей обработки) и с помощью этих библиотек его не получить.
Однако, работа с этими библиоткеками достаточно проста и удобна для быстрого создания Excel файлов их форматирования, но если Вам надо больше возможностей, то следующий подпункт для Вас.
Работа с com-объектом
В своих отчетах я предпочитаю использовать второй способ, а именно использование файла Excel через com-объект с использованием библиотеки win32com. Его преимуществом, является то, что выможете выполнять с файлом все опреции, которые позволяет делать обычный Excel с использованием VBA.
Проиллюстирируем это на той же задаче, что и предыдущие примеры.
Для начала загрузим нужную библиотеку и создадим COM объект:
import win32com.client Excel = win32com.client.Dispatch("Excel.Application") Теперь мы можем работать с помощью объекта Excel мы можем получить доступ ко всем возможностям VBA. Давайте, для начала, откроем любую книгу и выберем активный лист. Это можно сделать так:
wb = Excel.Workbooks.Open(u'D:\\Scripts\\DataScience\\ArticleScripts\\ExcelPython\\xl.xls') sheet = wb.ActiveSheet Теперь давайте получим значение первой ячейки и последовательности:
#получаем значение первой ячейки 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 получает следующее:
- Объект модуля, полученный на предыдущем шаге
- Имя вызываемой функции
- Параметры, передаваемые функции (передаются в виде списка)
Функция PyTuple , получает на вход какие-либо значения и преобразует их в объект tuple языка Python.
Ну и, соответственно, PyVar выполняет операцию пребразования результата функции python, к типу понятному Excel.
Теперь, чтобы убедиться в работоспособности нашей связки, вызовем нашу свежеиспеченую функцию на листе в Excel:
Как видно из рисунка все отработало правильно.
Надо отметить, что в данном материале используется старая версия ExcelPython, и на GitHub’e автора доступна новая версия.
Заключение
В качестве заключения, надо отметить, примеры в данной статье самые простые и для более глубоко изучения данных методов, я рекомендую обратиться к документации по соответсвующим пакетам.
Также хочу заметить, что указанные пакеты не являются единственными и в статье опущено рассмотрение, таких пакетов как xlwings, который может работать с Excel файлами “на лету” или же PyXLL, который выполняет аналогичные функции ExcelPython.
Кроме этого в статье я попвтался несколько обобщить разборасанный по сети материал, т.к. такие вопросы часто фигурируют на формух и думаю некоторым будет полезно иметь, такую “шпаргалку” под рукой.