Интеграция MS Excel и Python
Добрый день, уважаемые читатели.В сегодняшней статье я хотел бы, как можно подробнее, рассмотреть интеграцию приложений Python и MS Excel. Данные вопрос может возникнуть, например, при создании какой-либо системы онлайн отчетности, которая должна выгружать результаты в общепринятый формат ну или какие-либо другие задачи. Также в статье я покажу и обратную интеграцию, т.е. как использовать функцию написанную на python в Excel, что также может быть полезно для автоматизации отчетов.
Работаем с файлами MS Excel на PythonДля работы с Excel файлами из Python мне известны 2 варианта: Использование библиотек, таких как xlrd, xlwt, xlutils или openpyxl Работа с com-объектом Рассмотрим работу с этими способами подробнее. В качестве примера будем использовать готовый файл 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.
Кроме этого в статье я попытался несколько обобщить разборасанный по сети материал, т.к. такие вопросы часто фигурируют на форумах и думаю некоторым будет полезно иметь, такую «шпаргалку» под рукой.
