Формирование excel файла python

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

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

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

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

Использование библиотек

Итак, первый метод довольно простой и хорошо описан. Например, есть отличная статья для описания работы 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. Параметры, передаваемые функции (передаются в виде списка)

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

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

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

Заключение

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

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

Кроме этого в статье я попытался несколько обобщить разборасанный по сети материал, т.к. такие вопросы часто фигурируют на форумах и думаю некоторым будет полезно иметь, такую «шпаргалку» под рукой.

Источник

How to Create Excel File in Python

We can use the openpyxl library to create an Excel File in Python. This ability is an essential step for any Excel process automation. The best part – we don’t even need to have the Excel program installed.

Library

Open up a command prompt, and use pip install to download openpyxl library:

Create Excel Workbook and Add Worksheets using Python

For this step, we are creating an Excel file in Python and don’t need to open the Excel program. The below code creates an Excel file in memory, then adds two sheets, namely “Sheet_one” and “Sheet_two”.

1. When a Workbook is created, by default it contains at least one sheet. So this file will contain three (3) sheets in total.
2. This Excel file doesn’t exist on the hard drive yet and it doesn’t have a name.

We can change the sheet name any time by setting the ws.title attribute:

Copy A Worksheet

To copy a worksheet, call the wb.copy_worksheet() method:

Note that openpyxl has limitations and can not copy everything on a sheet. For example, Images and Charts will not be copied.

Changing Sheet/tab Color

We can use the .sheet_properties.tabColor attribute to set tab colors. The value has to be a RGB hex value, and the easiest way to get those values is from the “Hex” box on Excel’s color palette.

Write Some Data Into Excel

Let’s put some values into “Sheet_one”. As the below code shows, it’s easy to access a cell using the column-row notation.

To enter values into Sheet programmatically, it’s easier to use the cell() method, which takes two arguments: row and column, both starting from an index of 1.

Saving Excel File

Up to this point, the entire Excel file including everything we’ve done is still inside our computer’s memory. We need to save the file to our disk by calling the wb.save() method. If we don’t do this step, our Excel file will disappear after shutting down the Python environment.

This is how we can create an Excel file using Python. Check the below for more resources.

Источник