Python openpyxl закрыть файл

Обновлено: 05.07.2024

Уверенное владение Excel уже принято за норму, чем за исключение. И бизнес зачастую просто ни в каких других программах не работает с цифрами. Открыть excel-файл на телефоне в чате или гугл таблицах (тоже, кстати, отлично работает) намного проще, чем думать как запустить тетрадку jupyter notebook или целую программу на python.

(думается мне, после того как я разберусь с excel, напишу еще и про google sheets)

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

И вот проходя множество курсов по анализу данных вы вряд ли найдете помимо экскурса в Python и SQL еще и Excel — все так стремительно хотят от него уйти, будто вы и так в нём хорошо работали.

Пока я училась поняла, что мало кто из студентов вообще понимает прелесть Excel. Они просто думают, что за Python будущее. Прежде чем мы действительно будем так думать, давайте разберемся с Excel.

Excel сейчас — это стандартная программа, которая ставится в комплекте Microsoft Office и есть на каждом ноутбуке (если её нет, найдется Libre, но смысл останется).

Там удобно посмотреть данные сразу, прокрутить, вставить один раз формулу и “протянуть” дальше. Построить быстренько несложные диаграммы из самостоятельно выбранных данных.

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

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

Но бывает и такое, что ты сидишь и думаешь “это можно сделать проще”, но, увы, не знаешь как.

Все эти “можно сделать проще” возможны с помощью макросов или power query, но это может быть слишком сложно или наоборот долго, ну, и я зачем мы владеем python? 😃

Прежде чем мы перейдем к конкретной задаче, расскажу, что в Python с excel-документами можно и нужно работать с помощью pandas, openpyxl, xlrd, xlutils и pyexcel.

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

Объединение нескольких excel-файлов

Да, эту задачу можно решить ручками или power query, где несколько таблиц передаете в запрос и объединяете.

Например, на работе вы можете работать с ежемесячными отчётами о продажах, и с 90% вероятностью вы будете заниматься консолидацией региональных отчётов. Однажды вас попросят подготовить не только сконсолидированный отчёт, но и найти/подсчитать общее количество продаж по всем этим отчетам — и всё как можно быстрее.

Вариант номер 1, описан выше. Excel, Power Query, добавляете отчёты, объединяете, открываете, смотрите.

Вариант номер 2. Excel, несколько таблиц, переносим вкладки в один документ, создаете еще одну вкладку с шаблоном, делаете подсчёты с помощью формул. Смотрите.

А можно Вариант номер 3. Объединить все excel-файлы в python, используя библиотеку pandas (да-да, pandas).

Устанавливаем pandas используя pip или conda в терминале:

И рассмотрим вариант работы с отчетом, который сделан по одному шаблону (например, придумаем самый простеньких отчёт о продажах фруктов).

Можно заметить, что наш отчёт начинается не с первой ячейки, а только с 4-й строчки, поэтому нам понадобятся данные для импорта, начиная с этой строки (мне кажется, здорово такоё вариант учесть, потому что в работе часто такое встречается). В приведенном ниже коде мы будем использовать функции read_excel и append .

Давайте посмотрим на то, что мы сделали здесь:

В первой части мы импортировали pandas , создали список со всеми url-адресами и сгенерировали пустой фрейм данных под названием merger
Во второй части мы просмотрели каждый элемент списка (url-адрес) в files , чтобы прочитать каждый файл, пропустив первые три строчки ( skiprows = 3 ) и добавив его в объединенный датафрейм( merger ).
В третьей части мы генерируем новый excel-файл с именем merger.xlsx , содержащий наши объединенные файлы!

Получение значений нескольких файлов

Давайте посмотрим на другой пример.

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

Для этого примера мы будем использовать другую библиотеку — openpyxl . Вы можете установить её с помощью pip или conda, используя код ниже:

А теперь посмотрим код и что он выполняет:

Если вы хотите выполнить код ниже, то запускайте код после сохранения файлов, сохраните файлы по ссылкам выше на свой компьютер.

Давайте разберем это шаг за шагом, сначала мы:

Создаем список ( files ), который содержит ссылки на все наши файлы. В Windows мы можем нажать Shift + правой кнопкой мыши и использовать Копировать как путь (или, Copy as Path), чтобы получить путь к файлу.
И создаем пустой список для хранения наших значений ( values )
Пишем цикл, который будет выполнять нужные нам манипуляции с каждым файлом:

— с помощью метода .load_workbook() загружаем файл

— и используем атрибут .value , чтобы извлечь значение ячейки и добавить его в список values методом .append()

Применение формул в книгах

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

Мы снова будем использовать openpyxl . Если вам нужно установить его, сделайте это по инструкции выше.

В этом фрагменте кода мы снова заполняем список файлов. Цикл for открывает каждый файл и присваивает соответствующее название листа.
Затем мы присваиваем строку = SUM(F5: F8) ячейке F9 и используем атрибут style для назначения стиля ячейки. Больше стилей ячеек можно найти в официальной документации.

А теперь я дополню эту связку, вот таким вот открытием — автоматизировать Excel, и по сути заменить VBA (в моём понимании) можно библиотекой xlwings.

Автор xlwings говорит, что библиотека “Make Excel Fly!”. Вы можете использовать xlwings + Python для следующих задач:

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

Попробуем установить и рассмотреть несложный первый пункт. Пункты 2 и 3 рассмотрим в следующей серии.

Существует две части для установки xlwings: библиотека Python и надстройка Excel. Давайте начнем с установки библиотеки Python через командную строку:

Затем загрузите надстройку Excel из официального репозитория xlwings на Github. Это xlwings.xlam файл на странице (если вы вдруг будете читать эту статью через год, берите последнюю версию)

Положите xlwings.xlam-файл в папку надстройки Excel, которая является:

C:\Users\xxxx\AppData\Roaming\Microsoft\AddIns

Xxxx — это ваше собственное имя пользователя на вашем компьютере.

У меня получилось так, что я могу сейчас показать как это работает в английской и в русской версии Excel.

Затем откройте Excel, Файл -> Параметры -> Надстройки (или File -> Options -> Add-ins), нажимаем кнопку “Перейти..” (Go..) выбирая Надстройки Excel (Excel Add-ins).

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

Установите модуль openpyxl

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

Создать книгу

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

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

По умолчанию этот метод получает первый рабочий лист

Создайте новый рабочий лист, вы можете использовать Workbook.create_sheet() метод:

Когда создается новый рабочий лист, ему автоматически присваивается имя. Значения будут присвоены в порядке (Лист, Лист1, Лист2). Но может пройти Worksheet.title Атрибут для изменения этого имени.

Если вы хотите просмотреть имена всех листов в книге, вы можете использовать Workbook.sheetname Свойства для просмотра:

Конечно, вы также можете использовать обход цикла:

Обработка данных

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

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

Помимо использования заглавных букв (обозначающих столбцы) и чисел (обозначающих строки) для доступа к ячейкам.

Также может пройти Worksheet.cell() Метод напрямую использует нотацию строки и столбца для доступа к ячейке:

Доступ к нескольким ячейкам

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

При доступе к нескольким строкам или столбцам вы можете использовать следующие методы:

В дополнение к использованию вышеуказанного метода нарезки. Также можно использовать Worksheet.iter_rows() Доступ к методу, этот метод будет возвращать строку за строкой:

Точно так же есть Worksheet.iter_cols() Метод, этот метод будет возвращать столбец за столбцом:

Вот на что следует обратить внимание: Worksheet.iter_cols Метод недоступен в режиме только для чтения.

Если вам нужно пройти по всем строкам или столбцам в файле, вы можете рассмотреть Worksheet.rows Атрибуты:

Или Worksheet.columns Атрибуты:

Возвращает значение ячейки

Если вам просто нужно вернуть значение ячейки на листе, вы можете использовать Worksheet.values Атрибуты.

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

Сохранить данные в файл

Сохраните книгу, вы можете использовать Workbook Объект Workbook.save() метод:

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

И вставьте содержимое в таблицу, используя описанный выше метод.

Загрузить файл

При загрузке файлов вы можете использовать openpyxl.load_workbook() Способ открытия локально существующего файла:

дополнение

Время вставки

Использовать формулу

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

Объединить / разделить ячейки

Когда вам нужно объединить и разделить ячейки, вы можете использовать worksheet.merge_cells() Метод и worksheet.unmerge_cells() метод:

Загрузить изображение

Этот шаг зависит от библиотеки Pillow, поэтому вам необходимо установить библиотеку Pillow перед ее использованием.

Приведенные выше результаты использования показывают

Как вы можете видеть на этом рисунке, время, введенное А1, вступило в силу.

Результатом, отображаемым в A2, является 2. Часть, отмеченная красным, указывает, что результат получен с использованием этой формулы, что означает, что вставка формулы также эффективна.

В то же время вы можете видеть, что ячейки A2: B2 были объединены.

Последний шаг - вставка изображения.Вы видите, что в ячейку A5 изображение было успешно вставлено.

Вышеупомянутый контент посвящен простому использованию библиотеки openpyxl для обработки файлов Excel.

1) Установка модуля OpenPyXL

Мы можем установить модуль OpenPyXL с помощью команды PIP.

Pip install openpyxl.

2) Чтение файла Excel с помощью OpenPyXL

Я создал образец файла Excel (Records.xlsx) с тремя листами. Данные присутствуют в первых двух листах.

Openpyxl пример листа Excel

Мы будем использовать этот файл Excel, чтобы посмотреть на некоторые примеры для чтения данных из листа Excel.

1. Получение названий листов из файла Excel

листы Переменная Возвращает список названий рабочих листов в рабочей книге. Имена возвращаются в порядке рабочих листов в файле Excel.

2. Получение специфического листа из файла Excel

Мы можем получить доступ к конкретному листу, используя переменную индекс с объектом Workbook.

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

3. Чтение значения ячейки из листа Excel

Есть два способа получить значение клетки с листа Excel. Мы можем получить объект сотового элемента, используя функцию Cell (), или мы можем получить его, используя индекс клетки.

4. Общее количество рядов и столбцов в листе Excel

Мы можем получить общее количество строк и столбцов, используя max_row и max_column свойства рабочего листа.

5. Строка печати заголовка листа Excel

Функция ITER_ROWS () генерирует ячейки из рабочего листа по строке. Мы можем использовать его, чтобы получить клетки из определенного ряда.

6. Печать всех значений из столбца

7. Печать всех значений из строки

8. Диапазон чтения клеток от листа Excel

Мы можем пройти ассортимент ячеек для чтения нескольких ячеек одновременно.

9. Итализация клетки по рядам

Аргументы, переданные функции iter_rows (), создают двумерную таблицу, из которой значения прочитаны, по строке. В этом примере значения читаются между A2 и C4.

10. Итализаторы клетки по столбцам

Функция Iter_Cols () такая же, как iter_rows (), за исключением того, что значения читают столбца.

3) Написание файла Excel с помощью OpenPyXL

В этом разделе мы посмотрим несколько примеров записи файлов Excel и данных клеток.

1. Написание файла Excel с помощью OpenPyXL

OpenPyXL Написать файл Excel

2. Обновление сотовой стоимости

Мы можем либо использовать индекс ячейки или использовать объект ячейки для установки значения. Изменим некоторые значения в файле Excel, созданные в последнем примере.

Opennyxl Обновить значение ячейки

3. Добавление нескольких значений в лист Excel

Мы можем использовать функцию Append (), чтобы добавить последовательность значений в нижнюю часть рабочего листа.

Opennyxl добавить несколько строк в файл Excel

4) Удаление рядов и колонн из листа Excel

Мы можем использовать функции delete_cols () и delete_rows () для удаления столбцов и строк из листа Excel.

IDX Параметр предоставляет индекс строк и столбцов для удаления. Если мы хотим удалить несколько соседних строк и столбцов, мы можем предоставить аргумент суммы.

5. Заключение

Модуль Python OuterPyXL является идеальным выбором для работы с листами Excel. Мы также можем добавить изображения в лист Excel с помощью библиотеки подушки с ней. Но, не защищает нас против квадратичного взрыва или миллиарда смех XML атаки. Итак, если вы получаете значения от пользователя и сохраняете его, то попробуйте подтвердить и санировать его.

Эта библиотека пригодится, если вы хотите читать и редактировать файлы .xlsx, xlsm, xltx и xltm.

Установите openpyxl using pip. Общие рекомендации по установке этой библиотеки — сделать это в виртуальной среде Python без системных библиотек. Вы можете использовать виртуальную среду для создания изолированных сред Python: она создает папку, содержащую все необходимые файлы, для использования библиотек, которые потребуются для Python.

Перейдите в директорию, в которой находится ваш проект, и повторно активируйте виртуальную среду venv. Затем перейдите к установке openpyxl с помощью pip, чтобы убедиться, что вы можете читать и записывать с ним файлы:

Теперь, когда вы установили openpyxl, вы можете начать загрузку данных. Но что именно это за данные? Например, в книге с данными, которые вы пытаетесь получить на Python, есть следующие листы:

На первый взгляд, с этими объектами Worksheet мало что можно сделать. Однако, можно извлекать значения из определенных ячеек на листе книги, используя квадратные скобки [], к которым нужно передавать точную ячейку, из которой вы хотите получить значение.

Обратите внимание, это похоже на выбор, получение и индексирование массивов NumPy и Pandas DataFrames, но это еще не все, что нужно сделать, чтобы получить значение. Нужно еще добавить значение атрибута:

Помимо value, есть и другие атрибуты, которые можно использовать для проверки ячейки, а именно row, column и coordinate:

Атрибут row вернет 2;
Добавление атрибута column к “С” даст вам «B»;
coordinate вернет «B2».

Вы также можете получить значения ячеек с помощью функции cell (). Передайте аргументы row и column, добавьте значения к этим аргументам, которые соответствуют значениям ячейки, которые вы хотите получить, и, конечно же, не забудьте добавить атрибут value:

Обратите внимание: если вы не укажете значение атрибута value, вы получите <Cell Sheet3.B1>, который ничего не говорит о значении, которое содержится в этой конкретной ячейке.

Вы используете цикл с помощью функции range (), чтобы помочь вам вывести значения строк, которые имеют значения в столбце 2. Если эти конкретные ячейки пусты, вы получите None.
Более того, существуют специальные функции, которые вы можете вызвать, чтобы получить другие значения, например get_column_letter () и column_index_from_string.

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

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

Конечно, использовать другой цикл.

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

Чтобы визуализировать описанное выше, возможно, вы захотите проверить результат, который вернет вам завершенный цикл:

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

Это все очень классно, но мы почти слышим, что вы сейчас думаете, что это ужасно трудный способ работать с файлами, особенно если нужно еще и управлять данными.
Должно быть что-то проще, не так ли? Всё так!

Openpyxl имеет поддержку Pandas DataFrames. И можно использовать функцию DataFrame () из пакета Pandas, чтобы поместить значения листа в DataFrame:

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

Чтобы записать Pandas DataFrames обратно в файл Excel, можно использовать функцию dataframe_to_rows () из модуля utils:

Но это определенно не все! Библиотека openpyxl предлагает вам высокую гибкость в отношении того, как вы записываете свои данные в файлы Excel, изменяете стили ячеек или используете режим только для записи. Это делает ее одной из тех библиотек, которую вам точно необходимо знать, если вы часто работаете с электронными таблицами.

И не забудьте деактивировать виртуальную среду, когда закончите работу с данными!

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

Готовы узнать больше?

Чтение и форматирование Excel файлов xlrd
Эта библиотека идеальна, если вы хотите читать данные и форматировать данные в файлах с расширением .xls или .xlsx.

Если вы не хотите рассматривать всю книгу, можно использовать такие функции, как sheet_by_name () или sheet_by_index (), чтобы извлекать листы, которые необходимо использовать в анализе.

Наконец, можно получить значения по определенным координатам, обозначенным индексами.
О том, как xlwt и xlutils, соотносятся с xlrd расскажем дальше.

Запись данных в Excel файл при помощи xlrd

Если нужно создать электронные таблицы, в которых есть данные, кроме библиотеки XlsxWriter можно использовать библиотеки xlwt. Xlwt идеально подходит для записи и форматирования данных в файлы с расширением .xls.

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

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

Цикл for будет следить за тем, чтобы все значения попадали в файл: задаем, что с каждым элементом в диапазоне от 0 до 4 (5 не включено) мы собираемся производить действия. Будем заполнять значения строка за строкой. Для этого указываем row элемент, который будет “прыгать” в каждом цикле. А далее у нас следующий for цикл, который пройдется по столбцам листа. Задаем условие, что для каждой строки на листе смотрим на столбец и заполняем значение для каждого столбца в строке. Когда заполнили все столбцы строки значениями, переходим к следующей строке, пока не заполним все имеющиеся строки.

В качестве примера скриншот результирующего файла:

Теперь, когда вы видели, как xlrd и xlwt взаимодействуют вместе, пришло время посмотреть на библиотеку, которая тесно связана с этими двумя: xlutils.

Коллекция утилит xlutils

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

Использование pyexcel для чтения файлов .xls или .xlsx

Еще одна библиотека, которую можно использовать для чтения данных таблиц в Python — pyexcel. Это Python Wrapper, который предоставляет один API для чтения, обработки и записи данных в файлах .csv, .ods, .xls, .xlsx и .xlsm.

Чтобы получить данные в массиве, можно использовать функцию get_array (), которая содержится в пакете pyexcel:

Однако, если вы хотите вернуть в словарь двумерные массивы или, иными словами, получить все листы книги в одном словаре, стоит использовать функцию get_book_dict ().

Имейте в виду, что обе упомянутые структуры данных, массивы и словари вашей электронной таблицы, позволяют создавать DataFrames ваших данных с помощью pd.DataFrame (). Это упростит обработку ваших данных!

Наконец, вы можете просто получить записи с pyexcel благодаря функции get_records (). Просто передайте аргумент file_name функции и обратно получите список словарей:

Записи файлов при помощи pyexcel

Так же, как загрузить данные в массивы с помощью этого пакета, можно также легко экспортировать массивы обратно в электронную таблицу. Для этого используется функция save_as () с передачей массива и имени целевого файла в аргумент dest_file_name:

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

Однако, если у вас есть словарь, нужно будет использовать функцию save_book_as (). Передайте двумерный словарь в bookdict и укажите имя файла, и все ОК:

Помните, что когда используете код, который напечатан в фрагменте кода выше, порядок данных в словаре не будет сохранен!

Чтение и запись .csv файлов

Если вы все еще ищете библиотеки, которые позволяют загружать и записывать данные в CSV-файлы, кроме Pandas, рекомендуем библиотеку csv:

Обратите внимание, что NumPy имеет функцию genfromtxt (), которая позволяет загружать данные, содержащиеся в CSV-файлах в массивах, которые затем можно помещать в DataFrames.

Финальная проверка данных

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

Note: Используйте DataCamp Pandas Cheat Sheet, когда вы планируете загружать файлы в виде Pandas DataFrames.

Если данные в массиве, вы можете проверить его, используя следующие атрибуты массива: shape, ndim, dtype и т.д.:

Поздравляем, теперь вы знаете, как читать файлы Excel в Python :) Но импорт данных — это только начало рабочего процесса в области данных. Когда у вас есть данные из электронных таблиц в вашей среде, вы можете сосредоточиться на том, что действительно важно: на анализе данных.

Если вы хотите глубже погрузиться в тему — знакомьтесь с PyXll, которая позволяет записывать функции в Python и вызывать их в Excel.

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