Python выгрузка в excel

Обновлено: 04.07.2024

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

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

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

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

Итак, первый метод довольно простой и хорошо описан. Например, есть отличная статья для описания работы c xlrd, xlwt, xlutils. Поэтому в данном материале я приведу небольшой кусок кода с их использованием.

Для начала загрузим нужные библиотеки и откроем файл xls на чтение и выберем
нужный лист с данными:

Теперь давайте посмотрим, как считать значения из нужных ячеек:

Как видно чтение данных не составляет труда. Теперь запишем их в другой файл. Для этого создам новый excel файл с новой рабочей книгой:

Запишем в новый файл полученные ранее данные и сохраним изменения:

Из примера выше видно, что библиотека xlrd отвечает за чтение данных, а xlwt — за запись, поэтому нет возможности внести изменения в уже созданную книгу без ее копирования в новую. Кроме этого указанные библиотеки работают только с файлами формата xls (Excel 2003) и у них нет поддержки нового формата xlsx (Excel 2007 и выше).

Чтобы успешно работать с форматом xlsx, понадобится библиотека openpyxl. Для демонстрации ее работы проделаем действия, которые были показаны для предыдущих библиотек.

Для начала загрузим библиотеку и выберем нужную книгу и рабочий лист:

Как видно из вышеприведенного листинга сделать это не сложно. Теперь посмотрим как можно считать данные:

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

Теперь посмотрим как нам произвести запись и сохранить данные:

Из примера видно, что запись, тоже производится довольно легко. Кроме того, в коде выше, можно заметить, что openpyxl кроме имен ячеек может работать и с их индексами.

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

Как было показано выше, для более менее полноценной работы с excel файлами, в данном случае, нужно 4 библиотеки, и это не всегда удобно. Кроме этого, возможно нужен будет доступ к VBA (допустим для какой-либо последующей обработки) и с помощью этих библиотек его не получить.

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

Работа с com-объектом

В своих отчетах я предпочитаю использовать второй способ, а именно использование файла Excel через com-объект с использованием библиотеки win32com. Его преимуществом, является то, что вы можете выполнять с файлом все операции, которые позволяет делать обычный Excel с использованием VBA.

Проиллюстрируем это на той же задаче, что и предыдущие примеры.

Для начала загрузим нужную библиотеку и создадим COM объект.

Теперь мы можем работать с помощью объекта Excel мы можем получить доступ ко всем возможностям VBA. Давайте, для начала, откроем любую книгу и выберем активный лист. Это можно сделать так:

Давайте получим значение первой ячейки и последовательности:

Как можно заметить, мы оперируем здесь функциями чистого VBA. Это очень удобно если у вас есть написанные макросы и вы хотите использовать их при работе с Python при минимальных затратах на переделку кода.

Посмотрим, как можно произвести запись полученных значений:

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

Однако, внимательный читатель, обратит внимание на переменную i, которая инициализируется не 0, как принято python, а 1. Это связано с тем, что мы работаем с индексами ячеек как из VBA, а там нумерация начинается не с 0, а с 1.

На этом закончим разбор способов работы с excel файлами в python и перейдем к обратной задаче.

Вызываем функции Python из MS Excel

Может возникнуть такая ситуация, что у вас уже есть какой-либо функция, которая обрабатывает данные на python, и нужно перенести ее функциональность в Excel. Конечно же можно переписать ее на VBA, но зачем?

Для использования функций python в Excel есть прекрасная надстройка ExcelPython. С ее помощью вы сможете вызывать функции написанные на python прямо из Excel, правда придется еще написать небольшую обертку на VBA, и все это будет показано ниже.

Итак, предположим у нас есть функция, написанная на python, которой мы хотим воспользоваться:

На вход ей подается список, состоящий из списков, это одно из условий, которое должно выполняться для работы данной функции в Excel.

Сохраним функцию в файле plugin.py и положим его в ту же директорию, где будет лежать наш excel файл, с которым мы будем работать.

Теперь установим ExcelPython. Установка происходит через запуск exe-файла и не вызывает затруднений.

Когда все приготовления выполнены, открываем тестовый файл excel и вызовем редактор VBA (Alt+F11). Для работы с вышеуказанной надстройкой необходимо ее подключить, через Tools->References, как показано на рисунке:

Ну что же, теперь можно приступить к написанию функции-обертки для нашего Python-модуля plugin.py. Выглядеть она будет следующим образом:

Итак, что же происходит в данной функции?

Для начала, с помощью PyModule , мы подключаем нужный модуль. Для этого в качестве параметров ей передается имя модуля без расширения, и путь до папки в которой он находится. На выходе работы PyModule мы получаем объект для работы с модулем.

Затем, с помощью PyCall , вызываем нужную нам функцию из указанного модуля. В качестве параметров PyCall получает следующее:

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

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

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

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

Заключение

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

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

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

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

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

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

Для начала загрузим нужные библиотеки и откроем файл xls на чтение и выберем
нужный лист с данными:

Теперь давайте посмотрим, как считать значения из нужных ячеек:

Запишем в новый файл полученные ранее данные и сохраним изменения:

Для начала загрузим библиотеку и выберем нужную книгу и рабочий лист:

Как видно из вышеприведенного листинга сделать это не сложно. Теперь посмотрим как можно считать данные:

Теперь посмотрим как нам произвести запись и сохранить данные:

Работа с com-объектом

Проиллюстрируем это на той же задаче, что и предыдущие примеры.

Для начала загрузим нужную библиотеку и создадим COM объект.

Давайте получим значение первой ячейки и последовательности:

Посмотрим, как можно произвести запись полученных значений:

На этом закончим разбор способов работы с excel файлами в python и перейдем к обратной задаче.

Вызываем функции Python из MS Excel

Итак, предположим у нас есть функция, написанная на python, которой мы хотим воспользоваться:

Теперь установим ExcelPython. Установка происходит через запуск exe-файла и не вызывает затруднений.

Итак, что же происходит в данной функции?

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

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

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

Заключение

Pandas можно использовать для чтения и записи файлов Excel с помощью Python. Это работает по аналогии с другими форматами. В этом материале рассмотрим, как это делается с помощью DataFrame.

Помимо чтения и записи рассмотрим, как записывать несколько DataFrame в Excel-файл, как считывать определенные строки и колонки из таблицы и как задавать имена для одной или нескольких таблиц в файле.

Установка Pandas

Для начала Pandas нужно установить. Проще всего это сделать с помощью pip .

Если у вас Windows, Linux или macOS:

В процессе можно столкнуться с ошибками ModuleNotFoundError или ImportError при попытке запустить этот код. Например:

В таком случае нужно установить недостающие модули:

Запись в файл Excel с python

Будем хранить информацию, которую нужно записать в файл Excel, в DataFrame . А с помощью встроенной функции to_excel() ее можно будет записать в Excel.

Сначала импортируем модуль pandas . Потом используем словарь для заполнения DataFrame :

Ключи в словаре — это названия колонок. А значения станут строками с информацией.

Теперь можно использовать функцию to_excel() для записи содержимого в файл. Единственный аргумент — это путь к файлу:

Стоит обратить внимание на то, что в этом примере не использовались параметры. Таким образом название листа в файле останется по умолчанию — «Sheet1». В файле может быть и дополнительная колонка с числами. Эти числа представляют собой индексы, которые взяты напрямую из DataFrame.

Поменять название листа можно, добавив параметр sheet_name в вызов to_excel() :

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

Запись нескольких DataFrame в файл Excel

Также есть возможность записать несколько DataFrame в файл Excel. Для этого можно указать отдельный лист для каждого объекта:

Здесь создаются 3 разных DataFrame с разными названиями, которые включают имена сотрудников, а также размер их зарплаты. Каждый объект заполняется соответствующим словарем.

Объединим все три в переменной salary_sheets , где каждый ключ будет названием листа, а значение — объектом DataFrame .

Дальше используем движок xlsxwriter для создания объекта writer . Он и передается функции to_excel() .

Перед записью пройдемся по ключам salary_sheets и для каждого ключа запишем содержимое в лист с соответствующим именем. Вот сгенерированный файл:

Можно увидеть, что в этом файле Excel есть три листа: Group1, Group2 и Group3. Каждый из этих листов содержит имена сотрудников и их зарплаты в соответствии с данными в трех DataFrame из кода.

Параметр движка в функции to_excel() используется для определения модуля, который задействуется библиотекой Pandas для создания файла Excel. В этом случае использовался xslswriter , который нужен для работы с классом ExcelWriter . Разные движка можно определять в соответствии с их функциями.

В зависимости от установленных в системе модулей Python другими параметрами для движка могут быть openpyxl (для xlsx или xlsm) и xlwt (для xls). Подробности о модуле xlswriter можно найти в официальной документации.

Наконец, в коде была строка writer.save() , которая нужна для сохранения файла на диске.

Чтение файлов Excel с python

По аналогии с записью объектов DataFrame в файл Excel, эти файлы можно и читать, сохраняя данные в объект DataFrame . Для этого достаточно воспользоваться функцией read_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

Создаем папку, где будет размещен наш проект.
В этой папке создаем таблицу и сохраняем ее в .xlsx
Вносим в нашу таблицу какие-то данные на 3 столбца (А, В, С) и 3-4 строки.
Также в папку помещаем наш скрипт, который мы писали пару абзацев выше.

Как осуществляется выгрузка Python из Excel

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

wb = xw.Book('ваш файл.xlsx') — создаем переменную wb, которая открывает Эксель файл под указанным именем;
data_excel = wb.sheets['Имя листа с данными'] — создается переменная data_excel , и ей присваиваются данные переменной wb, открывая доступ к необходимому листу таблицы;
data_pd = data_excel.range('A1:C4').options(pd.DataFrame, header = 1, index = False).value — с читываем информацию из указанной области документа (А1:С4) и генерируем переменную Pandas DataFrame (pd.DataFrame).
print(data_pd) — просим вывести информацию из data_pd.

Запись информации из Python в Excel

Итак, считывать данны е из таблицы мы уже умеем. Но как сделать запись?

К примеру , мы хотим добавить один столбик в нашу таблицу. Мы можем осуществить это путем ввода следующей команды, сделав запись в DataFrame data_pd:

Вроде получилось, однако нам нужно полученную информацию вписать назад в наш документ Excel . В этом вам поможет следующая строка в скрипте:

data_excel.range('A1').options(index = False).value = data_pd

Если посмотреть полностью на код, который у вас должен оказаться в скрипте, то это примерно следующее:

import xlwings as xw

import pandas as pd

wb=xw.Book('Ваш файл.xlsx')

data_excel = wb.sheets['Имя листа файла']

data_pd = data_excel.range('A1:C4').options(pd.DataFrame, header = 1, index = False).value

data_pd['Имя нового столбца'] = ['Значение','Значение','Значение']

data_excel.range('A1').options(index = False).value = data_pd

Когда все эти правки внесены , можете смело запускать скрипт . Потом просмотр ите свою таблицу. Там должен появиться новый столбик уже с данными («Значение»).

Это простой пример, как можно использовать Python, чтобы сделать запись или корректировку таблицы Excel.

Мы будем очень благодарны

если под понравившемся материалом Вы нажмёте одну из кнопок социальных сетей и поделитесь с друзьями.

Читайте также: