Открытие и обработка XLSX файлов в Python: чтение, запись и работа с данными

Для начала

Таблица №1

Sales Date	Sales Person	Amount
12/05/18	Sila Ahmed	60000
06/12/19	Mir Hossain	50000
09/08/20	Sarmin Jahan	45000
07/04/21	Mahmudul Hasan	30000

Этот файл мы и будем читать с помощью различных библиотек Python в следующей части этого руководства.

Что такое Openpyxl и когда ее использовать

Прежде чем углубиться в работу с Excel в Python, убедитесь, что знаете базу. Если вы только начинаете путь в программировании, обратите внимание на бесплатный курс «Основы Python». Он подходит всем, кто хочет применять этот язык в работе или учебе — достаточно лишь уверенно пользоваться компьютером. Здесь разбирают ключевые понятия, от переменных к функциям. Это фундамент, после которого легче переходить к более сложной информации.

Openpyxl — это открытая библиотека «Питона» для работы с Excel -файлами формата.xlsx. Она позволяет программно читать, изменять и создавать таблицы без необходимости открывать эксель вручную. Поддерживает как простые операции (изменение значений ячеек), так и продвинутые — стили, форматирование, фильтры, объединение ячеек, работу с формулами и создание графиков. Ее используют для:

• Экспорт из Python в Excel
• Импорт из Excel в Python
• Преобразование форматов (CSV → Excel)

• Поиск ошибок в данных
• Проверка шаблонов на соответствие стандартам

• Генерация красивых таблиц с форматированием
• Добавление формул и диаграмм
• Создание многостраничных документов

• Сбор данных из многих файлов
• Создание отчетов по шаблону
• Ежемесячное обновление таблиц

Openpyxl vs Pandas vs другие библиотеки

Когда речь заходит о том, как прочитать эксель-файл в «Питоне», возникает логичный вопрос — чем пользоваться?

подходит, если нужно взаимодействовать с файлом «как есть». Например:
Openpyxl

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

• быстро отфильтровать 50 тысяч строк;
• посчитать средние показатели по отделам;
• объединить несколько таблиц в одну.

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

пригодится, когда нужно создать новый файл с нуля и сделать его максимально аккуратным. Например, если вы генерируете отчет автоматически каждое утро. Минус — библиотека не открывает существующие файлы, только создает новые.
Xlsxwriter

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

Установка Pandas

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

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

Установка модуля openpyxl в виртуальное окружение.

Модуль openpyxl размещен на PyPI, поэтому установка относительно проста.

# создаем виртуальное окружение, если нет $ python3 -m venv.venv —prompt VirtualEnv # активируем виртуальное окружение $ source.venv/bin/activate # ставим модуль openpyxl (VirtualEnv):~$ python3 -m pip install -U openpyxl

Установка Openpyxl

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

Шаг 1. Подготовка

Откройте терминал или командную строку, в зависимости от вашей операционной системы.

• Windows: нажмите Win → введите cmd → откройте Командную строку (или используйте PowerShell).
• macOS: откройте Terminal (Spotlight → Terminal).
• Linux: откройте терминал (Ctrl+Alt+T или через меню).

Шаг 2. Установите Python

Вы должны увидеть версию, например Python 3.11.4. Если команда не найдена — вернитесь к установке и убедитесь, что галочка PATH поставлена.

Шаг 3. Проверьте pip

Менеджер пакетов pip — это инструмент, с помощью которого устанавливаются библиотеки (в том числе openpyxl).

Если в системе есть и python, и python3, в командах ниже используйте ту форму, которая у вас работает.
Совет

Иногда команда pip не срабатывает, потому что система не знает, где она лежит. Это случается:

• В Windows, если PATH настроен неправильно или pip не установился.
• В macOS/Linux, если PATH не включает путь к pip.
• Если установлено несколько версий Python, и одна мешает другой.

В таких случаях лучше использовать команду, привязанную к конкретной версии Python:

Шаг 4. Установите openpyxl

Появятся строки загрузки и в конце — Successfully installed openpyxl…. Если требуется sudo на Linux/macOS:

Шаг 5. Сделайте быстрый тест

Запустите команду python test_openpyxl.py или python3 test_openpyxl.py.

Шаг 6. Используйте виртуальную среду

Виртуальная среда — это «отдельная папка» с собственными библиотеками. Рекомендуется, если у вас много проектов и не хочется, чтобы они конфликтовали.

После активации в командной строке обычно появляется префикс (myenv). Все, что вы установите в активной среде, не повлияет на другие проекты.

Первые шаги: создание и загрузка файлов

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

Как создать новый Excel-файл

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

Лайфхаки для начинающих

Библиотека openpyxl не сразу записывает изменения сразу, а только в момент save(). Если что-то пропало, значит, вы забыли сохранить.

Если вы заранее знаете, какие вам нужны колонки, то заполняйте заголовки сразу:

Excel может блокировать файл, и скрипт выдаст ошибку «Permission denied».

Как открыть существующий файл

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

Лайфхак

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

Загрузка документа XLSX из файла.

Чтобы открыть существующую книгу Excel необходимо использовать функцию openpyxl.load_workbook():

Есть несколько флагов, которые можно использовать в функции openpyxl.load_workbook().

• data_only: определяет, будут ли содержать ячейки с формулами — формулу (по умолчанию) или только значение, сохраненное/посчитанное при последнем чтении листа Excel.
• keep_vba определяет, сохраняются ли какие-либо элементы Visual Basic (по умолчанию). Если они сохранены, то они не могут изменяться/редактироваться.

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

Таблица №2

	Name	Age	Overall	Potential	Positions	Club
0	L. Messi	33	93	93	RW,ST,CF	FC Barcelona
1	Cristiano Ronaldo	35	92	92	ST,LW	Juventus
2	J. Oblak	27	91	93	GK	Atlético Madrid
3	K. De Bruyne	29	91	91	CAM,CM	Manchester City
4	Neymar Jr	28	91	91	LW,CAM	Paris Saint-Germain

Pandas присваивает метку строки или числовой индекс объекту DataFrame по умолчанию при использовании функции read_excel().

Это поведение можно переписать, передав одну из колонок из файла в качестве параметра index_col:

Таблица №3

Name	Age	Overall	Potential	Positions	Club
L. Messi	33	93	93	RW,ST,CF	FC Barcelona
Cristiano Ronaldo	35	92	92	ST,LW	Juventus
J. Oblak	27	91	93	GK	Atlético Madrid
K. De Bruyne	29	91	91	CAM,CM	Manchester City
Neymar Jr	28	91	91	LW,CAM	Paris Saint-Germain

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

Чтение Excel-файла с помощью xlrd

Библиотека xlrd не устанавливается вместе с Python по умолчанию, так что ее придется установить. Последняя версия этой библиотеки, к сожалению, не поддерживает Excel-файлы с расширением.xlsx. Поэтому устанавливаем версию 1.2.0. Выполните следующую команду в терминале:

Затем используем вложенный цикл for. С его помощью мы будем перемещаться по ячейкам, перебирая строки и столбцы. Также в скрипте используются две функции range() для определения количества строк и столбцов в таблице.

Для чтения значения отдельной ячейки таблицы на каждой итерации цикла воспользуемся функцией cell_value(). Каждое поле в выводе будет разделено одним пробелом табуляции.

import xlrd # Open the Workbook workbook = xlrd.open_workbook(«») # Open the worksheet worksheet = workbook.sheet_by_index(0) # Iterate the rows and columns for i in range(0, 5): for j in range(0, 3): # Print the cell values with tab space print(worksheet.cell_value(i, j), end=’\t’) print(»)

Чтение Excel-файла с помощью openpyxl

Openpyxl – это еще одна библиотека Python для чтения файла.xlsx, и она также не идет по умолчанию вместе со стандартным пакетом Python. Чтобы установить этот модуль, выполните в терминале следующую команду:

После завершения процесса установки можно начинать писать код для чтения файла.

Функцию range() используем для чтения строк таблицы, а функцию iter_cols() — для чтения столбцов. Каждое поле в выводе будет разделено двумя пробелами табуляции.

import openpyxl # Define variable to load the wookbook wookbook = openpyxl.load_workbook(«») # Define variable to read the active sheet: worksheet = # Iterate the loop to read the cell values for i in range(0, worksheet.max_row): for col in worksheet.iter_cols(1, worksheet.max_column): print(col[i].value, end=»\t\t») print(»)

Чтение Excel-файла с помощью pandas

Если вы не пользовались библиотекой pandas ранее, вам необходимо ее установить. Как и остальные рассматриваемые библиотеки, она не поставляется вместе с Python. Выполните следующую команду, чтобы установить pandas из терминала.

После завершения процесса установки создаем файл Python и начинаем писать следующий скрипт для чтения файла.

import pandas as pd # Load the xlsx file excel_data = pd.read_excel(») # Read the values of the file in the dataframe data = (excel_data, columns=[‘Sales Date’, ‘Sales Person’, ‘Amount’]) # Print the content print(«The content of the file is:\n», data)

Результат работы этого скрипта отличается от двух предыдущих примеров. В первом столбце печатаются номера строк, начиная с нуля. Значения даты выравниваются по центру. Имена продавцов выровнены по правому краю, а сумма — по левому.

Чтение определенных колонок из файла Excel

Это делается с помощью функции read_excel() и параметра usecols. Например, можно ограничить функцию, чтобы она читала только определенные колонки. Добавим параметр, чтобы он читал колонки, которые соответствуют значениям «Name», «Overall» и «Potential».

Таблица №4

	Name	Overall	Potential
0	L. Messi	93	93
1	Cristiano Ronaldo	92	92
2	J. Oblak	91	93
3	K. De Bruyne	91	91
4	Neymar Jr	91	91

В DataFrame много встроенных возможностей. Легко изменять, добавлять и агрегировать данные. Даже можно строить сводные таблицы. И все это сохраняется в Excel одной строкой кода.

Работа с листами

В openpyxl лист — это объект Worksheet, и с ним можно делать практически все: создавать новые страницы, переименовывать существующие, перемещать, копировать и удалять. Ниже — основные операции, которые пригодятся в любой автоматизации.

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

Создание книги Excel.

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

>>> from openpyxl import Workbook # создаем книгу >>> wb = Workbook() # делаем единственный лист активным >>> ws =

Новый рабочий лист книги Excel.

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

# вставить рабочий лист в конец (по умолчанию) >>> ws1 = wb.create_sheet(«Mysheet») # вставить рабочий лист в первую позицию >>> ws2 = wb.create_sheet(«Mysheet», 0) # вставить рабочий лист в предпоследнюю позицию >>> ws3 = wb.create_sheet(«Mysheet», -1)

Листам автоматически присваивается имя при создании. Они нумеруются последовательно (Sheet, Sheet1, Sheet2, …). Эти имена можно изменить в любое время с помощью свойства:

Цвет фона вкладки с этим заголовком по умолчанию белый. Можно изменить этот цвет, указав цветовой код RRGGBB для атрибута листа Worksheet.sheet_properties.tabColor:

Рабочий лист можно получить, используя его имя в качестве ключа экземпляра созданной книги Excel:

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

Копирование рабочего листа книги Excel.

Для создания копии рабочих листов в одной книге, необходимо воспользоваться методом Workbook.copy_worksheet():

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

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

Удаление рабочего листа книги Excel.

Очевидно, что встает необходимость удалить лист электронной таблицы, который уже существует. Модуль openpyxl дает возможность удалить лист по его имени. Следовательно, сначала необходимо выяснить, какие листы присутствуют в книге, а потом удалить ненужный. За удаление листов книги отвечает метод ().

# выясним, названия листов присутствуют в книге >>> name_list = >>> name_list # [‘Mysheet1’, ‘NewPage’, ‘Mysheet2’, ‘Mysheet’, ‘Mysheet1 Copy’] # допустим, что нам не нужны первый и последний # удаляем первый лист по его имени с проверкой # существования такого имени в книге >>> if ‘Mysheet1’ in: # Если лист с именем `Mysheet1` присутствует # в списке листов экземпляра книги, то удаляем… (wb[‘Mysheet1’])… >>> # [‘NewPage’, ‘Mysheet2’, ‘Mysheet’, ‘Mysheet1 Copy’] # удаляем последний лист через оператор # `del`, имя листа извлечем по индексу # полученного списка `name_list` >>> del wb[name_list[-1]] >>> # [‘NewPage’, ‘Mysheet2’, ‘Mysheet’]

Чтение данных из ячеек

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

Доступ к ячейке и ее значению.

Если объект ячейки присвоить переменной, то этой переменной, также можно присваивать значение:

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

# создаст в памяти 100×100=10000 пустых объектов # ячеек, просто так израсходовав оперативную память. >>> for x in range(1,101):… for y in range(1,101):… (row=x, column=y)

Доступ к диапазону ячеек листа электронной таблицы.

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

>>> cell_range = ws[‘A1′:’C2’] >>> cell_range # ((<Cell ‘NewPage’.A1>, <Cell ‘NewPage’.B1>, <Cell ‘NewPage’.C1>), # (<Cell ‘NewPage’.A2>, <Cell ‘NewPage’.B2>, <Cell ‘NewPage’.C2>))

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

# Все доступные ячейки в колонке `C` >>> colC = ws[‘C’] # Все доступные ячейки в диапазоне колонок `C:D` >>> col_range = ws[‘C:D’] # Все доступные ячейки в строке 10 >>> row10 = ws[10] # Все доступные ячейки в диапазоне строк `5:10` >>> row_range = ws[5:10]

>>> for row in ws.iter_rows(min_row=1, max_col=3, max_row=2):… for cell in row:… print(cell) # <Cell Sheet1.A1> # <Cell Sheet1.B1> # <Cell Sheet1.C1> # <Cell Sheet1.A2> # <Cell Sheet1.B2> # <Cell Sheet1.C2>

>>> for col in ws.iter_cols(min_row=1, max_col=3, max_row=2):… for cell in col:… print(cell) # <Cell Sheet1.A1> # <Cell Sheet1.A2> # <Cell Sheet1.B1> # <Cell Sheet1.B2> # <Cell Sheet1.C1> # <Cell Sheet1.C2>

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

>>> ws = >>> ws[‘C9’] = ‘hello world’ >>> tuple() # ((<Cell Sheet.A1>, <Cell Sheet.B1>, <Cell Sheet.C1>), # (<Cell Sheet.A2>, <Cell Sheet.B2>, <Cell Sheet.C2>), # (<Cell Sheet.A3>, <Cell Sheet.B3>, <Cell Sheet.C3>), #… # (<Cell Sheet.A7>, <Cell Sheet.B7>, <Cell Sheet.C7>), # (<Cell Sheet.A8>, <Cell Sheet.B8>, <Cell Sheet.C8>), # (<Cell Sheet.A9>, <Cell Sheet.B9>, <Cell Sheet.C9>))

>>> tuple() # ((<Cell Sheet.A1>, # <Cell Sheet.A2>, #… # <Cell Sheet.B8>, # <Cell Sheet.B9>), # (<Cell Sheet.C1>, # <Cell Sheet.C2>, #… # <Cell Sheet.C8>, # <Cell Sheet.C9>))

Получение только значений ячеек активного листа.

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

Для возврата только значения ячейки, методы Worksheet.iter_rows() и Worksheet.iter_cols(), представленные выше, могут принимать аргумент values_only:

Чтение конкретной ячейки

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

Таблица №5

Менеджер	Неделя	Выручка
Иванов	3	156000

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

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

Чтение столбца целиком

Используется, когда нужно обработать один параметр, например «Выручка». Обратите внимание, эти способы читают все ячейки в строке или столбце, включая пустые, вплоть до максимально возможных границ листа. Например, до строки 1 048 576. Это может быть неэффективно для больших файлов. Чтобы работать только с заполненной частью таблицы, лучше использовать ws.iter_rows() без указания границ или вместе с.

Полезно знать

• Даты читаются как объекты datetime.
• Пустые ячейки возвращают None.
• Формулы возвращают сами себя.

Если файл был сохранен Excel и открыт с data_only=True, то в.value будет результат вычисления формулы на момент последнего сохранения.

Если файл создан openpyxl и в него записаны формулы, то при открытии с data_only=True вы, скорее всего, получите None, так как движок формул openpyxl их не вычисляет.

Так вы не загрузите в память все сразу, что критично для файлов 50–200 МБ.

Запись и изменение данных

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

Добавление данных в ячейки листа списком.

• Если это список: все значения добавляются по порядку, начиная с первого столбца.
• Если это словарь: значения присваиваются столбцам, обозначенным ключами (цифрами или буквами).

• добавление списка:.append([‘ячейка A1’, ‘ячейка B1’, ‘ячейка C1’])
• добавление словаря:вариант 1:.append({‘A’: ‘ячейка A1’, ‘C’: ‘ячейка C1’}), в качестве ключей используются буквы столбцов.вариант 2:.append({1: ‘ячейка A1’, 3: ‘ячейка C1’}), в качестве ключей используются цифры столбцов.

• вариант 1:.append({‘A’: ‘ячейка A1’, ‘C’: ‘ячейка C1’}), в качестве ключей используются буквы столбцов.
• вариант 2:.append({1: ‘ячейка A1’, 3: ‘ячейка C1’}), в качестве ключей используются цифры столбцов.

# существующие листы рабочей книги >>> # [‘NewPage’, ‘Mysheet2’, ‘Mysheet’] # добавим данные в лист с именем `Mysheet2` >>> ws = wb[«Mysheet2»] # создадим произвольные данные, используя # вложенный генератор списков >>> data = [[row*col for col in range(1, 10)] for row in range(1, 31)] >>> data # [# [1, 2, 3, 4, 5, 6, 7, 8, 9], # [2, 4, 6, 8, 10, 12, 14, 16, 18], #… #… # [30, 60, 90, 120, 150, 180, 210, 240, 270] #] # добавляем данные в выбранный лист >>> for row in data:… (row)…

Добавление строки

Команда () добавляет строку в первую пустую строку после имеющихся данных. Если у вас есть отступы или пустые строки внутри таблицы, это может сработать некорректно. Метод ищет самую нижнюю заполненную строку в столбце A (или в первом столбце, если A пуст) и добавляет данные ниже.

Массовое изменение данных

Таблица №6

Менеджер	Выручка	Бонус
Иванов	350000	25000
Петров	120000	10000

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

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

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

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

import pandas as pd df = ({‘Name’: [‘Manchester City’, ‘Real Madrid’, ‘Liverpool’, ‘FC Bayern München’, ‘FC Barcelona’, ‘Juventus’], ‘League’: [‘English Premier League (1)’, ‘Spain Primera Division (1)’, ‘English Premier League (1)’, ‘German 1. Bundesliga (1)’, ‘Spain Primera Division (1)’, ‘Italian Serie A (1)’], ‘TransferBudget’: [176000000, 188500000, 90000000, 100000000, 180500000, 105000000]})

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

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

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

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

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

salaries1 = ({‘Name’: [‘L. Messi’, ‘Cristiano Ronaldo’, ‘J. Oblak’], ‘Salary’: [560000, 220000, 125000]}) salaries2 = ({‘Name’: [‘K. De Bruyne’, ‘Neymar Jr’, ‘R. Lewandowski’], ‘Salary’: [370000, 270000, 240000]}) salaries3 = ({‘Name’: [‘Alisson’, ‘M. ter Stegen’, ‘M. Salah’], ‘Salary’: [160000, 260000, 250000]}) salary_sheets = {‘Group1’: salaries1, ‘Group2’: salaries2, ‘Group3’: salaries3} writer = (‘./’, engine=’xlsxwriter’) for sheet_name in salary_sheets.keys(): salary_sheets[sheet_name].to_excel(writer, sheet_name=sheet_name, index=False) ()

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

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

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

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

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

Сохранение созданной книги в файл Excel.

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

Внимание. Эта операция перезапишет существующий файл без предупреждения!!!

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

Примечание. Расширение имени файла не обязательно должно быть xlsx или xlsm, хотя могут возникнуть проблемы с его открытием непосредственно в другом приложении. Поскольку файлы OOXML в основном представляют собой ZIP-файлы, их также можно открыть с помощью своего любимого менеджера ZIP-архивов.

Сохранение данных книги в виде потока.

Если необходимо сохранить файл в поток, например, при использовании веб-приложения, такого как Flask или Django, то можно просто предоставить ():

from tempfile import NamedTemporaryFile from openpyxl import Workbook wb = Workbook() with NamedTemporaryFile() as tmp: () (0) stream = ()

Примечание. Атрибут по умолчанию имеет значение False, это означает — сохранить как документ.

>>> from openpyxl import load_workbook >>> wb = load_workbook(») # Необходимо сохранить с расширением *.xlsx >>> (‘new_test.xlsm’) # MS Excel не может открыть документ # Нужно указать атрибут `keep_vba=True` >>> wb = load_workbook(») >>> (‘new_test.xlsm’) >>> wb = load_workbook(», keep_vba=True) # Если нужен шаблон документа, то необходимо указать расширение *.xltm. >>> (‘new_test.xlsm’) # MS Excel не может открыть документ

Продвинутые техники работы с openpyxl

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

Например, HR-отдел формирует данные по сотрудникам: библиотека помогает автоматически раскрасить KPI-показатели в зависимости от результатов, добавить формулы расчета бонусов и построить график эффективности по месяцам.

Работа со стилями и форматированием

Можно программно управлять практически каждым аспектом внешнего вида документа: шрифтами, выравниванием, цветами, границами, заливками, форматами чисел и структурой таблиц. К примеру, автоматически подсвечивать товары с низкой маржой красным, а топы продаж — зеленым. Это позволяет руководителю за секунду понять структуру ассортимента без детального анализа строк.

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

Работа с формулами

Допустим, отдел продаж часто формирует еженедельный отчет по выручке. С помощью кода легко:

• выгрузить данные;
• проставить формулы подсчета маржи, НДС, комиссий;
• добавить формулы сравнения с предыдущей неделей;
• автоматически вычислять процентное изменение.

Openpyxl позволяет вставлять в клетки формулы в их естественном синтаксисе, а эксель затем автоматически пересчитывает их при открытии файла. Можно создавать динамические расчеты: суммирование, средние значения, работу с датами, VLOOKUP/XLOOKUP, IF, агрегатные функции, формулы с диапазонами и многое другое.

Добавление фильтров и работа с ними

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

• сортировать конкретный диапазон;
• превращать диапазон в полноценную Excel-таблицу;
• сохранять пользовательские фильтры при генерации файлов;
• комбинировать фильтры с форматированием.

Создание простых графиков

Это библиотека Python поддерживает создание основных типов диаграмм: линейных, столбчатых, круговых, гистограмм, комбинированных и др. А также допускает:

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

Чем полезен OpenPyxl

При работе с OpenPyxl мы не запускаем Excel, а создаем и манипулируем файлами Excel (.xlsx) через Python. После создания файла можно открыть его в Excel, но сам процесс манипуляции файлами выполняется без него.

Перед работой с OpenPyxl в среде разработки нужно импортировать библиотеку:

Работа с файлами

Чтобы создать новый Excel-файл, нужно сначала создать рабочую книгу (Workbook), а затем добавлять листы и записывать данные в ячейки.

Workbook в библиотеке openpyxl — это класс, который используется для создания нового Excel-файла.

Можно обойтись и импортом библиотеки openpyxl, но тогда перед функциями придется писать openpyxl. Вместо load_workbook будет openpyxl.load_workbook.

from openpyxl import Workbook # Создаем новую рабочую книгу (файл) wb = () # Получаем активный лист (по умолчанию создается один лист) sheet = # Устанавливаем имя листа = «Лист1» # Записываем данные в ячейки sheet[‘A1’] = «Привет» sheet[‘A2’] = «Мир» # Сохраняем файл на диск (‘новый_файл.xlsx’)

В методе save() можно указать либо только имя файла, либо полный путь к нему.

— Если указано только имя файла, как в примере («новый_файл.xlsx»), файл будет сохранен в текущей рабочей директории программы (папке, из которой запускается скрипт).

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

Символ r перед строкой пути используют, чтобы избежать проблем с символами обратного слэша (\).

Итак, мы создали новый файл new_file.xlsx, добавили лист с данными в ячейки A1 и A2 и сохранили файл.

Работа с листами

Активный лист — это тот, который открыт по умолчанию, когда вы загружаете или создаете файл. Получить его можно с помощью метода active.

from openpyxl import Workbook # Создаем новую книгу wb = Workbook() # Или загружаем готовую wb = load_workbook(») # Добавляем новый лист new_sheet = wb.create_sheet(title=»Новый Лист») # Сохраняем изменения («file_with_new_sheet.xlsx»)

Работа с данными

from openpyxl import Workbook # Создаем новую книгу wb = Workbook() sheet = # Записываем текст и числа sheet[«A1»] = «Текст» # Записываем текст sheet[«B1»] = 123 # Записываем число sheet[«C1»] = 45.67 # Записываем дробное число # Сохраняем файл («file_with_data.xlsx»)

from import Font, Alignment # Применяем жирный шрифт sheet[«A1»].font = Font(bold=True) # Изменяем цвет текста (например, синий) sheet[«B1″].font = Font(color=»0000FF») # Выравнивание текста по центру sheet[«C1″].alignment = Alignment(horizontal=»center», vertical=»center»)

Работа с таблицами и формулами

from openpyxl import Workbook from import Table, TableStyleInfo # Создаем новую книгу и активный лист wb = Workbook() sheet = # Заполняем данные data = [[«Имя», «Возраст», «Город»], [«Иван», 25, «Москва»], [«Анна», 30, «Санкт-Петербург»], [«Петр», 35, «Новосибирск»]] for row in data: (row) # Определяем диапазон для таблицы table = Table(displayName=»MyTable», ref=»A1:C4″) # Настраиваем стиль таблицы style = TableStyleInfo(name=»TableStyleMedium9″, showFirstColumn=False, showLastColumn=False, showRowStripes=True, showColumnStripes=True) = style # Добавляем таблицу на лист sheet.add_table(table) # Сохраняем файл («file_with_table.xlsx»)

from openpyxl import Workbook # Создаем новую книгу wb = Workbook() sheet = # Заполняем данные sheet[«A1»] = 10 sheet[«A2»] = 20 sheet[«A3»] = 30 # Добавляем формулу sheet[«A4»] = «=SUM(A1:A3)» # Сумма ячеек A1, A2 и A3 # Сохраняем файл («file_with_formula.xlsx»)

Генерация отчета с динамическими данными

Используем Workbook для создания нового файла Excel. Активный лист выбирается автоматически с помощью sheet =.

Документация по OpenPyxl и частые ошибки

Документация OpenPyxl — основной источник информации о функциях, методах и возможностях библиотеки. Ее разделы включают:

Частые ошибки

Если Excel-файл открыт в другой программе (например, MS Excel), то при попытке открыть его с помощью библиотеки OpenPyxl, вы можете столкнуться с ошибками, связанными с доступом к файлу. Поэтому закройте все сторонние программы, которые могут использовать файл.

После внесения изменений не забывайте вызвать метод save(), чтобы сохранить эти изменения.

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

Если вы пытаетесь открыть файл в старом формате Excel.xls с помощью библиотеки OpenPyxl, которая поддерживает только формат.xlsx (и.xlsm для файлов с макросами), то возникнет ошибка, поскольку OpenPyxl не поддерживает работу с форматом.xls.

• Откройте файл.xls в Excel или другой программе, поддерживающей этот формат.
• Сохраните его как.xlsx.

Аналитики влияют на рост бизнеса. Они выясняют, какой товар и в какое время больше покупают. Считают юнит-экономику. Оценивают окупаемость рекламной кампании. Поэтому компании ищут и переманивают таких специалистов.

Проверьте, что установлен Python

Перед работой с OpenPyxl убедитесь, что у вас на компьютере установлен Python. Гайд по его установке вы можете найти здесь.

Проверить, что Python установлен можно через командную строку (cmd) или PowerShell в Windows и через терминал в Linux. Введите команду:

Возможная ошибка: Python version mismatch — ошибка несовместимости. Может возникнуть, если у вас установлены несколько версий Python.

Устанавливаем pip

Далее нужно выяснить, установлен ли pip. Это менеджер пакетов Python, который нужен для установки OpenPyxl.

pip not recognized as an internal or external command — не найдена команда. Это может означать, что pip не установлен или не добавлен в переменную окружения PATH.

Переходим к установке OpenPyxl

Для установки библиотеки OpenPyxl в Python введите в командной строке (Windows) или терминале (macOS, Linux или в самой среде разработки) команду:

Виртуальное окружение — это изолированная среда для работы с Python-проектами. Это позволяет избежать конфликтов зависимостей между проектами.

Если библиотека установлена правильно, отобразится номер версии OpenPyxl.

Часто задаваемые вопросы по работе с XLSX в Python

Вопрос: Какая библиотека Python лучше всего подходит для чтения XLSX файлов?
Ответ: Для простого чтения подойдут openpyxl и xlrd, для анализа данных — pandas, для создания сложных отчетов — openpyxl.

Вопрос: Как установить openpyxl?
Ответ: Используйте команду pip install openpyxl в терминале или командной строке.

Вопрос: Как прочитать конкретный лист из файла XLSX?
Ответ: Используйте метод workbook[‘ИмяЛиста’] или workbook.worksheets[индекс].

Вопрос: Как записать данные в ячейку Excel с помощью Python?
Ответ: Присвойте значение ячейке, например: worksheet[‘A1’] = ‘Текст’.

Вопрос: Как сохранить изменения в существующем файле XLSX?
Ответ: Используйте метод workbook.save(‘имя_файла.xlsx’).

Вопрос: В чем разница между openpyxl и pandas для работы с Excel?
Ответ: Openpyxl дает полный контроль над структурой файла, а pandas оптимизирован для анализа и трансформации данных.

Вопрос: Как прочитать только определенные столбцы из Excel файла?
Ответ: В pandas используйте параметр usecols, в openpyxl — итерацию по нужным колонкам.

Вопрос: Как создать новый лист в книге Excel?
Ответ: Используйте метод workbook.create_sheet(title=’Новый лист’).

Вопрос: Как обработать ошибку, если файл XLSX не найден?
Ответ: Используйте конструкцию try-except для перехвата исключения FileNotFoundError.

Вопрос: Можно ли работать с формулами в openpyxl?
Ответ: Да, openpyxl поддерживает запись и чтение формул Excel.