Интеграция MS Excel и Python

    Добрый день, уважаемые читатели.

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

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


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

    1. Использование библиотек, таких как xlrd, xlwt, xlutils или openpyxl
    2. Работа с 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)[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 получает следующее:

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

    Функция PyTuple, получает на вход какие-либо значения и преобразует их в объект tuple языка Python.
    Ну и, соответственно, PyVar выполняет операцию преобразования результата функции python, к типу понятному Excel.

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



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

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

    Заключение


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

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

    Кроме этого в статье я попытался несколько обобщить разборасанный по сети материал, т.к. такие вопросы часто фигурируют на форумах и думаю некоторым будет полезно иметь, такую «шпаргалку» под рукой.
    • +33
    • 103k
    • 8
    Поделиться публикацией
    Похожие публикации
    AdBlock похитил этот баннер, но баннеры не зубы — отрастут

    Подробнее
    Реклама
    Комментарии 8
    • +1
      А win32com платформо-независимый? Помню работать с COM из РНР можно было только если на магине MS Office был установлен…
      • +1
        можно еще добавить проверку на количества строк перед записью, и если оно превышает допустимое количество строк в листе, то создавать новый лист и писать в него уже.
        Как может быть win32com платформо-независимым? COM-это технология мс, соответственно будет работать только на Windows. По разрядность насколько помню есть и под 32/64.
        А в целом, по кому Office можно просто открыть документацию и никакая шпаргалка не понадобиться.
        • 0
          > можно еще добавить проверку на количества строк перед записью, и если оно превышает допустимое количество строк в листе, то создавать новый лист и писать в него уже.

          В xlwt удобно оборачивать запись в какой-нибудь объект или корутину, делающую flush_row_data() через N строк и автоматически разбивающую ввод на листы. Странно, что подобного хелпера нет в основном дереве.
          • 0
            В качестве платформо-независимого решения можно использовать pyUNO и OpenOffice. Но все равно требуется установленный OpenOffice.
          • +1
            Для работы с табличными данными также рекомендую обратить внимание на pandas. На мой взгляд, зачастую удобно прочитать данные из таблицы при помощи pandas, провести необходимые манипуляции с полученным DataFrame и потом опять экспортировать в xls.
            • +1
              Да, можно через pandas, но это только импорт/экспорт и без форматирования самого excel файла. В одной из прошлых статей, я об этом писал.
            • +1
              Буквально пару дней назад ковырялся с интеграцией excel, так как очень частенько бывают ситуации когда слышно вот такое: «ну ты конечно клевую админку сделал, но можно мы в экселе все вобьем, вышлем тебе, а ты импортнеш», думаю многие с таким сталкивались.

              github.com/mac2000/ExcelPostJsonAddIn

              Вот такая штука получилась, это addin позволяющий слать выбранную в экселе табличку на указанный URL в виде post запроса, с json представлением таблички

              Возможно кому нибудь пригодиться
              • 0
                А что у вас за тема в Excel установлена, и менюшки сдвинуты?

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