Openpyxl для работы с таблицами Excel в Python

БЕСПЛАТНО СКАЧАТЬ КНИГИ по Python на русском языке можно у нас в телеграм канале "Python книги на русском"

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

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

Скачивайте книги ТОЛЬКО на русском языке у нас в телеграм канале: PythonBooksRU

Эта статья написана для разработчиков среднего уровня, которые хорошо знают структуры данных языка Python, такие как словари (dicts) и списки (lists), а также чувствуют себя комфортно в области ООП и более продвинутых тем.

Содержание

• Прежде чем начать
  • Практические примеры испоьзования
  • Изучение базовой терминологии Excel
  • Начало работы с openpyxl
• Чтение электронных таблиц Excel с помощью openpyxl
  • Набор данных для этого учебника
  • Простой подход к чтению электронной таблицы Excel
  • Импорт данных из электронной таблицы
  • Добавление новых данных
• Создание электронных таблиц Excel с помощью openpyxl
  • Создание простой электронной таблицы
  • Основные операции с электронной таблицей
  • Добавление формул
  • Добавление стилей
  • Условное форматирование
  • Добавление изображений
  • Добавление красивых диаграмм
  • Преобразование классов Python в электронную таблицу Excel
  • Бонус: Работа с Pandas
• Заключение

Прежде чем начать

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

Практические варианты использования

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

Импорт новых товаров в базу данных

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

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

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

Экспорт данных базы данных в электронную таблицу

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

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

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

Добавление информации в существующую электронную таблицу

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

Например, в том же сценарии с интернет-магазином, предположим, вы получаете таблицу Excel со списком пользователей, и вам нужно добавить к каждой строке общую сумму, которую они потратили в вашем магазине.

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

Для openpyxl все это не проблема!

Изучение некоторых основных терминов Excel

Вот краткий список основных терминов, которые вы встретите при работе с таблицами Excel:

Термин	Объяснение
Электронная таблица	Электронная таблица (Spreadsheet) – основной файл, с которым вы работаете или создаете.
Лист или вкладка	Лист (Sheet) используется для разделения разных видов контента внутри одной таблицы. Электронная таблица может содержать одну или несколько вкладок.
Колонка	Колонка (Column) – вертикальная линия, представленная заглавной буквой, например, A.
Строка	Строка (Row) – горизонтальная линия, представленная числом, например, 1.
Ячейка	Ячейка (Cell) – сочетание колонки и строки, обозначенное заглавной буквой и числом, например, A1.

Начало работы с openpyxl

Теперь, когда вы знаете о преимуществах такого инструмента, как openpyxl, давайте перейдем к делу и начнем с установки пакета. Для этого руководства вы должны использовать Python 3.7 и openpyxl 2.6.2. Чтобы установить пакет, вы можете сделать следующее:

$ pip install openpyxl

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

from openpyxl import Workbook

workbook = Workbook()
sheet = workbook.active

sheet["A1"] = "hello"
sheet["B1"] = "world!"

workbook.save(filename="hello_world.xlsx")

Приведенный выше код должен создать файл hello_world.xlsx в директории, которую вы используете для запуска кода. Если вы откроете этот файл с помощью Excel, вы увидите что-то вроде этого:

Ух ты, вы создали свою первую таблицу при помощи Python!

Чтение электронных таблиц Excel с помощью openpyxl

Начнем с самого простого – чтения таблицы.

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

Набор данных для этого руководства

Прежде чем погрузиться в примеры кода, вам следует скачать этот набор данных и сохранить его где-нибудь под именем sample.xlsx:

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

Это один из наборов данных, которые вы будете использовать в этой статье, и он представляет собой электронную таблицу с примером реальных данных из онлайн-обзоров товаров Amazon. Этот набор данных – лишь малая часть того, что предоставляет Amazon, но для целей тестирования этого более чем достаточно.

Простой подход к чтению электронных таблиц Excel.

Наконец, давайте начнем читать электронные таблицы! Для начала откройте наш образец электронной таблицы:

>>> from openpyxl import load_workbook
>>> workbook = load_workbook(filename="sample.xlsx")
>>> workbook.sheetnames
['Sheet 1']

>>> sheet = workbook.active
>>> sheet
<Worksheet "Sheet 1">

>>> sheet.title
'Sheet 1'

В приведенном выше коде вы сначала открываете электронную таблицу sample.xlsx с помощью load_workbook(), а затем можете использовать workbook.sheetnames, чтобы увидеть все вкладки, доступные для работы. После этого workbook.active выбирает первую доступную вкладку, и в данном случае вы можете видеть, что он автоматически выбирает "Sheet 1". Использование этих методов является стандартным способом открытия электронной таблицы, и вы увидите его много раз в этом учебнике.

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

>>> sheet["A1"]
<Cell 'Sheet 1'.A1>

>>> sheet["A1"].value
'marketplace'

>>> sheet["F10"].value
"G-Shock Men's Grey Sport Watch"

Чтобы вернуть фактическое значение ячейки, нужно использовать .value. В противном случае вы получите основной объект Cell. Вы также можете использовать метод .cell() для получения ячейки, используя индексную нотацию:

>>> sheet.cell(row=10, column=6)
<Cell 'Sheet 1'.F10>

>>> sheet.cell(row=10, column=6).value
"G-Shock Men's Grey Sport Watch"

Вы можете видеть, что возвращаемые результаты одинаковы, независимо от того, какой способ вы выберете. Однако в этом учебнике вы будете в основном использовать первый подход: ["A1"].

Примечание: Несмотря на то, что в Python вы привыкли к индексации, начинающейся с нуля, в электронных таблицах всегда используется индексация, начинающаяся с единицы, где первая строка или столбец всегда имеют индекс 1.

Дополнительные параметры чтения

Есть несколько аргументов, которые вы можете передать в load_workbook() и которые изменяют способ загрузки электронной таблицы. Наиболее важными из них являются следующие два булевых значения:

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

Импорт данных из электронной таблицы

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

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

Итерация

Есть несколько различных способов итерироваться по данным.

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

>>> sheet["A1:C2"]
((<Cell 'Sheet 1'.A1>, <Cell 'Sheet 1'.B1>, <Cell 'Sheet 1'.C1>),
 (<Cell 'Sheet 1'.A2>, <Cell 'Sheet 1'.B2>, <Cell 'Sheet 1'.C2>))

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

>>> # Получить все клетки из колонки A
>>> sheet["A"]
(<Cell 'Sheet 1'.A1>,
 <Cell 'Sheet 1'.A2>,
 ...
 <Cell 'Sheet 1'.A99>,
 <Cell 'Sheet 1'.A100>)

>>> # Получить все клетки из колонок от A до B
>>> sheet["A:B"]
((<Cell 'Sheet 1'.A1>,
  <Cell 'Sheet 1'.A2>,
  ...
  <Cell 'Sheet 1'.A99>,
  <Cell 'Sheet 1'.A100>),
 (<Cell 'Sheet 1'.B1>,
  <Cell 'Sheet 1'.B2>,
  ...
  <Cell 'Sheet 1'.B99>,
  <Cell 'Sheet 1'.B100>))

>>> # Получить все клетки из строки 5 
>>> sheet[5]
(<Cell 'Sheet 1'.A5>,
 <Cell 'Sheet 1'.B5>,
 ...
 <Cell 'Sheet 1'.N5>,
 <Cell 'Sheet 1'.O5>)

>>> # Получить все клетки строк с 5 по 6
>>> sheet[5:6]
((<Cell 'Sheet 1'.A5>,
  <Cell 'Sheet 1'.B5>,
  ...
  <Cell 'Sheet 1'.N5>,
  <Cell 'Sheet 1'.O5>),
 (<Cell 'Sheet 1'.A6>,
  <Cell 'Sheet 1'.B6>,
  ...
  <Cell 'Sheet 1'.N6>,
  <Cell 'Sheet 1'.O6>))

Вы можете заметить, что все приведенные выше примеры возвращают кортеж.

Существует также множество способов использования генераторов Python для перебора данных. Вот основные методы для этого:

• .iter_rows()
• .iter_cols()

Оба метода могут принимать следующие аргументы:

• min_row
• max_row
• min_col
• max_col

Эти аргументы используются для установки границ итерации:

>>> for row in sheet.iter_rows(min_row=1,
...                            max_row=2,
...                            min_col=1,
...                            max_col=3):
...     print(row)
(<Cell 'Sheet 1'.A1>, <Cell 'Sheet 1'.B1>, <Cell 'Sheet 1'.C1>)
(<Cell 'Sheet 1'.A2>, <Cell 'Sheet 1'.B2>, <Cell 'Sheet 1'.C2>)


>>> for column in sheet.iter_cols(min_row=1,
...                               max_row=2,
...                               min_col=1,
...                               max_col=3):
...     print(column)
(<Cell 'Sheet 1'.A1>, <Cell 'Sheet 1'.A2>)
(<Cell 'Sheet 1'.B1>, <Cell 'Sheet 1'.B2>)
(<Cell 'Sheet 1'.C1>, <Cell 'Sheet 1'.C2>)

Обратите внимание на то, что в первом примере при итерации по строкам с помощью .iter_rows() вы получаете один элемент кортежа на каждую выбранную строку. В то время как при использовании .iter_cols() вы получите по одному кортежу на столбец.

Одним из дополнительных аргументов, который можно передать в оба метода, является булево значение values_only. Если он имеет значение True, вместо объектов Cell будут возвращены значения ячеек:

>>> for value in sheet.iter_rows(min_row=1,
...                              max_row=2,
...                              min_col=1,
...                              max_col=3,
...                              values_only=True):
...     print(value)
('marketplace', 'customer_id', 'review_id')
('US', 3653882, 'R3O9SGZBVQBV76')

Если вы хотите прочитать весь набор данных, то вы также можете использовать атрибуты .rows или .columns напрямую. Это упрощенные версии .iter_rows() и .iter_cols() без каких-либо аргументов:

>>> for row in sheet.rows:
...     print(row)
(<Cell 'Sheet 1'.A1>, <Cell 'Sheet 1'.B1>, <Cell 'Sheet 1'.C1>
...
<Cell 'Sheet 1'.M100>, <Cell 'Sheet 1'.N100>, <Cell 'Sheet 1'.O100>)

Эти методы могут быть очень полезны при итерации по всему набору данных.

Работа с данными при помощи стандартных структур данных Python

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

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

Например, вы хотите извлечь информацию о продукте из электронной таблицы sample.xlsx в словарь, где каждый ключ – это идентификатор продукта.

Прямой способ сделать это – перебрать все строки, выбрать столбцы, которые связаны с информацией о продукте, а затем сохранить их в словаре. Давайте попробуем сделать это!

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

>>> for value in sheet.iter_rows(min_row=1,
...                              max_row=1,
...                              values_only=True):
...     print(value)
('marketplace', 'customer_id', 'review_id', 'product_id', ...)

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

• product_id
• product_parent
• product_title
• product_category

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

>>> for value in sheet.iter_rows(min_row=2,
...                              min_col=4,
...                              max_col=7,
...                              values_only=True):
...     print(value)
('B00FALQ1ZC', 937001370, 'Invicta Women\'s 15150 "Angel" 18k Yellow...)
('B00D3RGO20', 484010722, "Kenneth Cole New York Women's KC4944...)

Отлично! Теперь, когда вы знаете, как получить всю нужную информацию о продукте, давайте поместим эти данные в словарь:

import json
from openpyxl import load_workbook

workbook = load_workbook(filename="sample.xlsx")
sheet = workbook.active

products = {}

# Используйте values_only потому что нам нужно получить именно значения клеток
for row in sheet.iter_rows(min_row=2,
                           min_col=4,
                           max_col=7,
                           values_only=True):
    product_id = row[0]
    product = {
        "parent": row[1],
        "title": row[2],
        "category": row[3]
    }
    products[product_id] = product

# Преобразуем в JSON, для лучшего отображения.
print(json.dumps(products))

Приведенный выше код возвращает JSON, подобный этому:

{
  "B00FALQ1ZC": {
    "parent": 937001370,
    "title": "Invicta Women's 15150 ...",
    "category": "Watches"
  },
  "B00D3RGO20": {
    "parent": 484010722,
    "title": "Kenneth Cole New York ...",
    "category": "Watches"
  }
}

Выше отображено только 2 продукта, но если вы запустите скрипт как есть, то получите 98 продуктов.

Преобразование данных в классы Python

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

Для этого нам понадобятся новые классы данных Python, которые доступны начиная с Python 3.7.

Итак, прежде всего, давайте рассмотрим имеющиеся у вас данные и решим, что и как мы хотим хранить.

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

Из имеющихся данных можно извлечь два важных элемента:

• Продукты
• Отзывы

Продукт имеет поля:

• ID
• Title
• Parent
• Category

Отзыв имеет еще несколько полей:

• ID
• Customer ID
• Stars
• Headline
• Body
• Date

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

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

import datetime
from dataclasses import dataclass

@dataclass
class Product:
    id: str
    parent: str
    title: str
    category: str

@dataclass
class Review:
    id: str
    customer_id: str
    stars: int
    headline: str
    body: str
    date: datetime.datetime

После определения классов данных необходимо преобразовать данные из таблицы в эти новые структуры.

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

>>> for value in sheet.iter_rows(min_row=1,
...                              max_row=1,
...                              values_only=True):
...     print(value)
('marketplace', 'customer_id', 'review_id', 'product_id', ...)

>>> # Or an alternative
>>> for cell in sheet[1]:
...     print(cell.value)
marketplace
customer_id
review_id
product_id
product_parent
...

Создадим файл mapping.py, в котором будет список имен всех полей и расположение их столбцов (с нулевой индексацией) в электронной таблице:

# Product fields
PRODUCT_ID = 3
PRODUCT_PARENT = 4
PRODUCT_TITLE = 5
PRODUCT_CATEGORY = 6

# Review fields
REVIEW_ID = 2
REVIEW_CUSTOMER = 1
REVIEW_STARS = 7
REVIEW_HEADLINE = 12
REVIEW_BODY = 13
REVIEW_DATE = 14

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

Наконец, давайте рассмотрим код, необходимый для разбора данных электронной таблицы в список объектов продуктов и отзывов:

from datetime import datetime
from openpyxl import load_workbook
from classes import Product, Review
from mapping import PRODUCT_ID, PRODUCT_PARENT, PRODUCT_TITLE, \
    PRODUCT_CATEGORY, REVIEW_DATE, REVIEW_ID, REVIEW_CUSTOMER, \
    REVIEW_STARS, REVIEW_HEADLINE, REVIEW_BODY

# Используем метод read_only, поскольку мы не будем редактировать таблицу.
workbook = load_workbook(filename="sample.xlsx", read_only=True)
sheet = workbook.active

products = []
reviews = []

# Используем метод values_only, поскольку мы хотим только вернуть значение ячейки.
for row in sheet.iter_rows(min_row=2, values_only=True):
    product = Product(id=row[PRODUCT_ID],
                      parent=row[PRODUCT_PARENT],
                      title=row[PRODUCT_TITLE],
                      category=row[PRODUCT_CATEGORY])
    products.append(product)

    # Нам необходимо получить дату из таблицы и преобразовать ее в формат datetime.
    spread_date = row[REVIEW_DATE]
    parsed_date = datetime.strptime(spread_date, "%Y-%m-%d")

    review = Review(id=row[REVIEW_ID],
                    customer_id=row[REVIEW_CUSTOMER],
                    stars=row[REVIEW_STARS],
                    headline=row[REVIEW_HEADLINE],
                    body=row[REVIEW_BODY],
                    date=parsed_date)
    reviews.append(review)

print(products[0])
print(reviews[0])

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

Product(id='B00FALQ1ZC', parent=937001370, ...)
Review(id='R3O9SGZBVQBV76', customer_id=3653882, ...)

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

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

Добавление новых данных

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

Вернитесь к первой созданной вами таблице (hello_world.xlsx) и попробуйте открыть ее и добавить в нее некоторые данные, как показано ниже:

from openpyxl import load_workbook

# Start by opening the spreadsheet and selecting the main sheet
workbook = load_workbook(filename="hello_world.xlsx")
sheet = workbook.active

# Write what you want into a specific cell
sheet["C1"] = "writing ;)"

# Save the spreadsheet
workbook.save(filename="hello_world_append.xlsx")

Если вы откроете новую электронную таблицу hello_world_append.xlsx, вы увидите следующие изменения:

Запись таблиц Excel с помощью openpyxl

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

Давайте начнем создавать электронные таблицы!

Создание простой таблицы.
Ранее вы видели очень быстрый пример того, как записать “Hello world!” в электронную таблицу, поэтому вы можете начать с этого:

from openpyxl import Workbook

filename = "hello_world.xlsx"

# создаем новую таблицу
workbook = Workbook()
sheet = workbook.active

# добавляем данные в таблицу
sheet["A1"] = "hello"
sheet["B1"] = "world!"

# сохраняем таблицу
workbook.save(filename=filename)

Примечание: Мы будем использовать электронную таблицу hello_world.xlsx для некоторых последующих примеров, поэтому держите ее под рукой.

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

>>> def print_rows():
...     for row in sheet.iter_rows(values_only=True):
...         print(row)

Основные операции с электронными таблицами

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

Добавление и обновление значений ячеек

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

>>> sheet["A1"] = "value"

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

>>> cell = sheet["A1"]
>>> cell
<Cell 'Sheet'.A1>

>>> cell.value
'hello'

>>> cell.value = "hey"
>>> cell.value
'hey'

Новое значение сохраняется в электронной таблице только после вызова метода workbook.save().

Openpyxl создаст ячейку при добавлении значения, если эта ячейка не существовала ранее:

>>> # Before, our spreadsheet has only 1 row
>>> print_rows()
('hello', 'world!')

>>> # Try adding a value to row 10
>>> sheet["B10"] = "test"
>>> print_rows()
('hello', 'world!')
(None, None)
(None, None)
(None, None)
(None, None)
(None, None)
(None, None)
(None, None)
(None, None)
(None, 'test')

Как видите, при попытке добавить значение в ячейку B10 вы получаете кортеж из 10 строк, только чтобы добавить в таблицу это тестовое значение.

Управление строками и столбцами

Одна из самых распространенных задач, которую приходится решать при работе с электронными таблицами, – это добавление или удаление строк и столбцов. Пакет openpyxl позволяет делать это очень простым способом, используя методы:

• .insert_rows()
• .delete_rows()
• .insert_cols()
• .delete_cols()

Каждый из этих методов может принимать два аргумента:

• idx
• amount

Снова используя наш базовый пример hello_world.xlsx, давайте посмотрим, как работают эти методы:

>>> print_rows()
('hello', 'world!')

>>> # Вставляем столбец перед существующим столбцом 1 ("A")
>>> sheet.insert_cols(idx=1)
>>> print_rows()
(None, 'hello', 'world!')

>>> # Вставляем 5 столбцов между 2 ("B") и 3 ("C")
>>> sheet.insert_cols(idx=3, amount=5)
>>> print_rows()
(None, 'hello', None, None, None, None, None, 'world!')

>>> # Удаляем вставленные столбцы.
>>> sheet.delete_cols(idx=3, amount=5)
>>> sheet.delete_cols(idx=1)
>>> print_rows()
('hello', 'world!')

>>> # Вставляем новую строку в начало.
>>> sheet.insert_rows(idx=1)
>>> print_rows()
(None, None)
('hello', 'world!')

>>> # Вставляем 3 новые строки в начало.
>>> sheet.insert_rows(idx=1, amount=3)
>>> print_rows()
(None, None)
(None, None)
(None, None)
(None, None)
('hello', 'world!')

>>> # Удаляем первые 4 строки.
>>> sheet.delete_rows(idx=1, amount=4)
>>> print_rows()
('hello', 'world!')

Единственное, что вам нужно помнить, это то, что при вставке новых данных (строк или столбцов) вставка происходит перед параметром idx.

Так, если вы выполняете insert_rows(1), то вставляете новую строку перед существующей первой строкой.

То же самое касается столбцов: когда вы вызываете insert_cols(2), он вставляет новый столбец прямо перед уже существующим вторым столбцом (B).

Однако при удалении строк или столбцов .delete_… удаляет данные, начиная с индекса, переданного в качестве аргумента.

Например, при выполнении delete_rows(2) удаляется строка 2, а при выполнении delete_cols(3) удаляется третий столбец (C).

Управление вкладками

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

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

sheet = workbook.active

Это способ выбора вкладки по умолчанию. Однако если вы открываете электронную таблицу с несколькими вкладками, вы всегда можете выбрать конкретную вкладку следующим образом:

>>> workbook.sheetnames
['Products', 'Company Sales']

>>> products_sheet = workbook["Products"]
>>> sales_sheet = workbook["Company Sales"]

Вы также можете легко изменить заголовок вкладки:

>>> workbook.sheetnames
['Products', 'Company Sales']

>>> products_sheet = workbook["Products"]
>>> products_sheet.title = "New Products"

>>> workbook.sheetnames
['New Products', 'Company Sales']

Если вы хотите создать или удалить вкладки, то вы также можете сделать это с помощью .create_sheet() и .remove():

>>> workbook.sheetnames
['Products', 'Company Sales']

>>> operations_sheet = workbook.create_sheet("Operations")
>>> workbook.sheetnames
['Products', 'Company Sales', 'Operations']

>>> # You can also define the position to create the sheet at
>>> hr_sheet = workbook.create_sheet("HR", 0)
>>> workbook.sheetnames
['HR', 'Products', 'Company Sales', 'Operations']

>>> # To remove them, just pass the sheet as an argument to the .remove()
>>> workbook.remove(operations_sheet)
>>> workbook.sheetnames
['HR', 'Products', 'Company Sales']

>>> workbook.remove(hr_sheet)
>>> workbook.sheetnames
['Products', 'Company Sales']

Еще одна вещь, которую вы можете сделать, это создать дубликаты вкладки с помощью функции copy_worksheet():

>>> workbook.sheetnames
['Products', 'Company Sales']

>>> products_sheet = workbook["Products"]
>>> workbook.copy_worksheet(products_sheet)
<Worksheet "Products Copy">

>>> workbook.sheetnames
['Products', 'Company Sales', 'Products Copy']

Если вы откроете свою электронную таблицу после сохранения приведенного выше кода, вы заметите, что вкладка Products Copy является дубликатом вкладки Products.

Закрепление строк и столбцов

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

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

Опять же, в openpyxl есть способ добиться этого, используя атрибут freeze_panes рабочего листа. Для этого примера вернитесь к нашей электронной таблице sample.xlsx и попробуйте сделать следующее:

>>> workbook = load_workbook(filename="sample.xlsx")
>>> sheet = workbook.active
>>> sheet.freeze_panes = "C2"
>>> workbook.save("sample_frozen.xlsx")

Если вы откроете таблицу sample_frozen.xlsx в своем любимом редакторе электронных таблиц, вы заметите, что строка 1 и столбцы A и B закреплены и всегда видны, независимо от того, куда вы перемещаетесь по таблице.

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

Вот как это выглядит в редакторе:

Обратите внимание, что вы находитесь в конце электронной таблицы, но при этом видите строку 1 и столбцы A и B.

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

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

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

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

>>> # Проверьте использованное пространство таблицы, используя атрибут "dimensions".
>>> sheet.dimensions
'A1:O100'

>>> sheet.auto_filter.ref = "A1:O100"
>>> workbook.save(filename="sample_with_filters.xlsx")

Теперь вы должны увидеть созданные фильтры при открытии электронной таблицы в редакторе:

Вам не обязательно использовать sheet.dimensions, если вы точно знаете, к какой части электронной таблицы вы хотите применить фильтры.

Добавление формул

Формулы – одна из самых мощных функций электронных таблиц.

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

Вы можете посмотреть список формул, поддерживаемых openpyxl:

>>> from openpyxl.utils import FORMULAE
>>> FORMULAE
frozenset({'ABS',
           'ACCRINT',
           'ACCRINTM',
           'ACOS',
           'ACOSH',
           'AMORDEGRC',
           'AMORLINC',
           'AND',
           ...
           'YEARFRAC',
           'YIELD',
           'YIELDDISC',
           'YIELDMAT',
           'ZTEST'})

Давайте добавим несколько формул в нашу электронную таблицу sample.xlsx.

Начнем с самого простого – проверим средний звездный рейтинг для 99 отзывов в таблице:

>>> # Star rating is column "H"
>>> sheet["P2"] = "=AVERAGE(H2:H100)"
>>> workbook.save(filename="sample_formulas.xlsx")

Если вы сейчас откроете электронную таблицу и перейдете к ячейке P2, вы увидите, что ее значение равно: 4.18181818181818. Посмотрите в редакторе:

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

>>> sheet["P3"] = '=COUNTIF(I2:I100, ">0")'
>>> workbook.save(filename="sample_formulas.xlsx")

Вы должны получить число 21 в ячейке электронной таблицы P3 следующим образом:

Вы должны убедиться, что строки внутри формулы всегда заключены в двойные кавычки, поэтому вы должны либо использовать одинарные кавычки вокруг формулы, как в примере выше, либо убрать двойные кавычки внутри формулы: "=COUNTIF(I2:I100, \">0\")".

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

Добавление стилей

Даже если стилизация электронной таблицы – это не то, что вы делаете каждый день, все равно полезно знать, как это делается.

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

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

Давайте для начала рассмотрим простой стиль ячеек, используя в качестве базовой таблицы наш example.xlsx:

>>> from openpyxl.styles import Font, Color, Alignment, Border, Side

>>> # Создадим несколько
>>> bold_font = Font(bold=True)
>>> big_red_text = Font(color="00FF0000", size=20)
>>> center_aligned_text = Alignment(horizontal="center")
>>> double_border_side = Side(border_style="double")
>>> square_border = Border(top=double_border_side,
...                        right=double_border_side,
...                        bottom=double_border_side,
...                        left=double_border_side)

>>> # Утилизируем некоторые клетки
>>> sheet["A2"].font = bold_font
>>> sheet["A3"].font = big_red_text
>>> sheet["A4"].alignment = center_aligned_text
>>> sheet["A5"].border = square_border
>>> workbook.save(filename="sample_styles.xlsx")

Если вы сейчас откроете свою электронную таблицу, вы увидите довольно много различных стилей в первых 5 ячейках столбца A:

Вот так. Вы получили:

• A2 с текстом, выделенным жирным шрифтом
• A3 с текстом, выделенным красным цветом и большим размером шрифта
• A4 с текстом по центру
• A5 с квадратной рамкой вокруг текста

Примечание: Для цветов можно также использовать HEX-коды, выполнив Font(color="C70E0F").

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

>>> sheet["A6"].alignment = center_aligned_text
>>> sheet["A6"].font = big_red_text
>>> sheet["A6"].border = square_border
>>> workbook.save(filename="sample_styles.xlsx")

Посмотрите на ячейку A6 здесь:

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

>>> from openpyxl.styles import NamedStyle

>>> # Давайте создадим шаблон стиля для строки заголовка.
>>> header = NamedStyle(name="header")
>>> header.font = Font(bold=True)
>>> header.border = Border(bottom=Side(border_style="thin"))
>>> header.alignment = Alignment(horizontal="center", vertical="center")

>>> # Теперь давайте применим его ко всем ячейкам первой строки (заголовкам).
>>> header_row = sheet[1]
>>> for cell in header_row:
...     cell.style = header

>>> workbook.save(filename="sample_styles.xlsx")

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

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

Условное форматирование

Эта функция – одна из моих любимых при добавлении стилей в электронную таблицу.

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

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

Например, широко распространенным вариантом использования является баланс, в котором все отрицательные итоги выделены красным цветом, а положительные – зеленым. Такое форматирование позволяет гораздо эффективнее выявлять хорошие и плохие периоды.

Без лишних слов, давайте выберем нашу любимую электронную таблицу sample.xlsx и добавим условное форматирование.

Вы можете начать с простого варианта, который добавляет красный фон ко всем отзывам с менее чем 3 звездами:

>>> from openpyxl.styles import PatternFill
>>> from openpyxl.styles.differential import DifferentialStyle
>>> from openpyxl.formatting.rule import Rule

>>> red_background = PatternFill(fgColor="00FF0000")
>>> diff_style = DifferentialStyle(fill=red_background)
>>> rule = Rule(type="expression", dxf=diff_style)
>>> rule.formula = ["$H1<3"]
>>> sheet.conditional_formatting.add("A1:O100", rule)
>>> workbook.save("sample_conditional_formatting.xlsx")

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

С точки зрения кода, единственное, что здесь является новым, это объекты DifferentialStyle и Rule:

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

Используя объект Rule, можно создать множество сценариев условного форматирования.

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

• ColorScale
• IconSet
• DataBar

ColorScale дает возможность создавать цветовые градиенты:

>>> from openpyxl.formatting.rule import ColorScaleRule
>>> color_scale_rule = ColorScaleRule(start_type="min",
...                                   start_color="00FF0000",  # Red
...                                   end_type="max",
...                                   end_color="0000FF00")  # Green

>>> #  Давайте установим цветовой градиент для колонки H
>>> sheet.conditional_formatting.add("H2:H100", color_scale_rule)
>>> workbook.save(filename="sample_conditional_formatting_color_scale.xlsx")

Теперь вы должны увидеть цветовой градиент в столбце H, от красного к зеленому, в соответствии с рейтингом звезд:

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

>>> from openpyxl.formatting.rule import ColorScaleRule
>>> color_scale_rule = ColorScaleRule(start_type="num",
...                                   start_value=1,
...                                   start_color="00FF0000",  # Red
...                                   mid_type="num",
...                                   mid_value=3,
...                                   mid_color="00FFFF00",  # Yellow
...                                   end_type="num",
...                                   end_value=5,
...                                   end_color="0000FF00")  # Green

>>> sheet.conditional_formatting.add("H2:H100", color_scale_rule)
>>> workbook.save(filename="sample_conditional_formatting_color_scale_3.xlsx")

На этот раз вы заметите, что оценки звезд от 1 до 3 имеют градиент от красного к желтому, а оценки звезд от 3 до 5 имеют градиент от желтого к зеленому:

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

>>> from openpyxl.formatting.rule import IconSetRule

>>> icon_set_rule = IconSetRule("5Arrows", "num", [1, 2, 3, 4, 5])
>>> sheet.conditional_formatting.add("H2:H100", icon_set_rule)
>>> workbook.save("sample_conditional_formatting_icon_set.xlsx")

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

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

Наконец, DataBar позволяет создавать индикаторы прогресса:

>>> from openpyxl.formatting.rule import DataBarRule

>>> data_bar_rule = DataBarRule(start_type="num",
...                             start_value=1,
...                             end_type="num",
...                             end_value="5",
...                             color="0000FF00")  # Green
>>> sheet.conditional_formatting.add("H2:H100", data_bar_rule)
>>> workbook.save("sample_conditional_formatting_data_bar.xlsx")

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

Как видите, с помощью условного форматирования можно сделать много интересного.

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

Добавление изображений

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

Чтобы иметь возможность загружать изображения в электронную таблицу с помощью openpyxl, вам необходимо установить Pillow:

$ pip install Pillow

Помимо этого, вам также понадобится изображение. Для этого примера вы можете взять любую картинку в формате .png и сохранить ее в корневую папку, в которой вы запускаете свои примеры.

После этого вот код, необходимый для импорта этого изображения в электронную таблицу hello_word.xlsx:

from openpyxl import load_workbook
from openpyxl.drawing.image import Image

# Давайте использовать нашу знакомую таблицу.
workbook = load_workbook(filename="hello_world.xlsx")
sheet = workbook.active

# Тут указываем название картинки которую хотим разместить. 
logo = Image("logo.png")

# Изменим размер изображения чтобы оно лучше подходило к таблице.
logo.height = 150
logo.width = 150

sheet.add_image(logo, "A3")
workbook.save(filename="hello_world_logo.xlsx")

Теперь у вас есть изображение в электронной таблице! Вот оно:

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

Добавление красивых диаграмм

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

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

Здесь вы увидите только пару примеров диаграмм, потому что теория, лежащая в их основе, одинакова для каждого типа диаграмм:

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

Для любой диаграммы, которую вы хотите построить, вам нужно определить тип диаграммы: BarChart, LineChart и т.д., а также данные, которые будут использоваться для построения диаграммы, которые называются Reference.

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

Давайте начнем с создания новой таблицы с некоторыми образцами данных:

from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference

workbook = Workbook()
sheet = workbook.active

# Давайте пропишем случайные данные о продажах
rows = [
    ["Product", "Online", "Store"],
    [1, 30, 45],
    [2, 40, 30],
    [3, 40, 25],
    [4, 50, 30],
    [5, 30, 25],
    [6, 25, 35],
    [7, 20, 40],
]

for row in rows:
    sheet.append(row)

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

chart = BarChart()
data = Reference(worksheet=sheet,
                 min_row=1,
                 max_row=8,
                 min_col=2,
                 max_col=3)

chart.add_data(data, titles_from_data=True)
sheet.add_chart(chart, "E2")

workbook.save("chart.xlsx")

Вот и все. Ниже вы можете увидеть очень простую гистограмму, показывающую разницу между продажами товаров через Интернет и продажами товаров в магазинах:

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

Примечание: В зависимости от того, используете ли вы Microsoft Excel или альтернативу с открытым исходным кодом (LibreOffice или OpenOffice), диаграмма может выглядеть несколько иначе.

Попробуйте вместо этого создать линейную диаграмму, немного изменив данные:

import random
from openpyxl import Workbook
from openpyxl.chart import LineChart, Reference

workbook = Workbook()
sheet = workbook.active

rows = [
    ["", "January", "February", "March", "April",
    "May", "June", "July", "August", "September",
     "October", "November", "December"],
    [1, ],
    [2, ],
    [3, ],
]

for row in rows:
    sheet.append(row)

for row in sheet.iter_rows(min_row=2,
                           max_row=4,
                           min_col=2,
                           max_col=13):
    for cell in row:
        cell.value = random.randrange(5, 100)

С помощью приведенного выше кода вы сможете сгенерировать случайные данные о продажах 3 различных товаров за целый год.

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

chart = LineChart()
data = Reference(worksheet=sheet,
                 min_row=2,
                 max_row=4,
                 min_col=1,
                 max_col=13)

chart.add_data(data, from_rows=True, titles_from_data=True)
sheet.add_chart(chart, "C6")

workbook.save("line_chart.xlsx")

Вот результат выполнения приведенного выше фрагмента кода:

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

В вашем примере данных видно, что каждый продукт имеет строку с 12 значениями (1 столбец на месяц). Вот почему вы используете from_rows. Если вы не передадите этот аргумент, по умолчанию диаграмма попытается построить график по столбцам, и вы получите сравнение продаж по месяцам.

Еще одно отличие, связанное с изменением вышеуказанного аргумента, заключается в том, что наша таблица теперь начинается с первого столбца, min_col=1, а не со второго. Это изменение необходимо потому, что диаграмма теперь ожидает, что заголовки будут находиться в первом столбце.

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

cats = Reference(worksheet=sheet,
                 min_row=1,
                 max_row=1,
                 min_col=2,
                 max_col=13)
chart.set_categories(cats)

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

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

Еще одна вещь, которую можно сделать для улучшения читабельности графика, – это добавить ось. Это можно сделать с помощью атрибутов x_axis и y_axis:

chart.x_axis.title = "Months"
chart.y_axis.title = "Sales (per unit)"

В результате будет создана электронная таблица, как показано ниже:

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

Существует также способ придать стиль диаграмме с помощью свойства ChartStyle, используемого в Excel по умолчанию. В этом случае вам нужно выбрать число от 1 до 48. В зависимости от выбора изменяются и цвета диаграммы:

# Вы можете подобрать нужный вам стиль, попробовав разные значения от 1 до 48
chart.style = 24

При выбранном выше стиле все линии имеют оранжевый оттенок:

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

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

import random
from openpyxl import Workbook
from openpyxl.chart import LineChart, Reference

workbook = Workbook()
sheet = workbook.active

rows = [
    ["", "January", "February", "March", "April",
    "May", "June", "July", "August", "September",
     "October", "November", "December"],
    [1, ],
    [2, ],
    [3, ],
]

for row in rows:
    sheet.append(row)

for row in sheet.iter_rows(min_row=2,
                           max_row=4,
                           min_col=2,
                           max_col=13):
    for cell in row:
        cell.value = random.randrange(5, 100)

# Создадим  LineChart и укажем данные 
chart = LineChart()
data = Reference(worksheet=sheet,
                           min_row=2,
                           max_row=4,
                           min_col=1,
                           max_col=13)
chart.add_data(data, titles_from_data=True, from_rows=True)

# Добавим категории к графику
cats = Reference(worksheet=sheet,
                 min_row=1,
                 max_row=1,
                 min_col=2,
                 max_col=13)
chart.set_categories(cats)

# Укажем імена осей
chart.x_axis.title = "Months"
chart.y_axis.title = "Sales (per unit)"

# Настроим свой стиль
chart.style = 24

# Сохраним 
sheet.add_chart(chart, "C6")
workbook.save("line_chart.xlsx")

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

Преобразование классов Python в электронную таблицу Excel

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

Представим, что у вас есть база данных, и вы используете объектно-реляционное отображение (ORM) для преобразования объектов базы данных в классы Python. Теперь вы хотите экспортировать эти же объекты в электронную таблицу.

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

from dataclasses import dataclass
from typing import List

@dataclass
class Sale:
    quantity: int

@dataclass
class Product:
    id: str
    name: str
    sales: List[Sale]

Теперь давайте сгенерируем несколько случайных данных, предполагая, что описанные выше классы хранятся в файле db_classes.py:

import random

# Это понадобится нам позже
from openpyxl import Workbook
from openpyxl.chart import LineChart, Reference

from db_classes import Product, Sale

products = []

# Давайте создадим 5 продуктов. 
for idx in range(1, 6):
    sales = []

    # ВОздадим данный о продажах за 5 месяцев. 
    for _ in range(5):
        sale = Sale(quantity=random.randrange(5, 100))
        sales.append(sale)

    product = Product(id=str(idx),
                      name="Product %s" % idx,
                      sales=sales)
    products.append(product)

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

Теперь, чтобы преобразовать эти данные в электронную таблицу, необходимо проитерироваться по ним и добавить их в электронную таблицу:

workbook = Workbook()
sheet = workbook.active

# Создадим заголовки
sheet.append(["Product ID", "Product Name", "Month 1",
              "Month 2", "Month 3", "Month 4", "Month 5"])

# Добавим данные
for product in products:
    data = [product.id, product.name]
    for sale in product.sales:
        data.append(sale.quantity)
    sheet.append(data)

Вот и все. Это позволит вам создать таблицу с данными, полученными из вашей базы данных.

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

Хорошо, тогда вы могли бы сделать что-то вроде этого:

chart = LineChart()
data = Reference(worksheet=sheet,
                 min_row=2,
                 max_row=6,
                 min_col=2,
                 max_col=7)

chart.add_data(data, titles_from_data=True, from_rows=True)
sheet.add_chart(chart, "B8")

cats = Reference(worksheet=sheet,
                 min_row=1,
                 max_row=1,
                 min_col=3,
                 max_col=7)
chart.set_categories(cats)

chart.x_axis.title = "Months"
chart.y_axis.title = "Sales (per unit)"

workbook.save(filename="oop_sample.xlsx")

Готово. Вот электронная таблица, с информацией из базы данных, с диаграммой и всем остальным:

На этом мы закончим с графиками.

Бонус: работа с Pandas

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

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

Но знаете что, вам не нужно беспокоиться о выборе. На самом деле, openpyxl поддерживает как преобразование данных из Pandas DataFrame в Workbook, так и наоборот, преобразование таблицы openpyxl в Pandas DataFrame.

Прежде всего, не забудьте установить пакет pandas:

$ pip install pandas

Затем создадим образец DataFrame:

import pandas as pd

data = {
    "Product Name": ["Product 1", "Product 2"],
    "Sales Month 1": [10, 20],
    "Sales Month 2": [5, 35],
}
df = pd.DataFrame(data)

Теперь, когда у вас есть некоторые данные, вы можете использовать .dataframe_to_rows(), чтобы преобразовать их из DataFrame в таблицу:

from openpyxl import Workbook
from openpyxl.utils.dataframe import dataframe_to_rows

workbook = Workbook()
sheet = workbook.active

for row in dataframe_to_rows(df, index=False, header=True):
    sheet.append(row)

workbook.save("pandas.xlsx")

Вы должны увидеть электронную таблицу, которая выглядит следующим образом:

Если вы хотите добавить индекс DataFrame, вы можете изменить index=True, и он добавит индекс для каждой строки в вашу электронную таблицу.

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

import pandas as pd
from openpyxl import load_workbook

workbook = load_workbook(filename="sample.xlsx")
sheet = workbook.active

values = sheet.values
df = pd.DataFrame(values)

В качестве альтернативы, если вы хотите добавить правильные заголовки и использовать, например, ID обзора в качестве индекса, вы можете сделать это следующим образом:

import pandas as pd
from openpyxl import load_workbook
from mapping import REVIEW_ID

workbook = load_workbook(filename="sample.xlsx")
sheet = workbook.active

data = sheet.values

# Получим первую колонку и строку
cols = next(data)
data = list(data)

# Укажем что колонка "review_id" является индексом для каждой строки
idx = [row[REVIEW_ID] for row in data]

df = pd.DataFrame(data, index=idx, columns=cols)

Использование индексов и столбцов позволяет легко получить доступ к данным из вашего DataFrame:

>>> df.columns
Index(['marketplace', 'customer_id', 'review_id', 'product_id',
       'product_parent', 'product_title', 'product_category', 'star_rating',
       'helpful_votes', 'total_votes', 'vine', 'verified_purchase',
       'review_headline', 'review_body', 'review_date'],
      dtype='object')

>>> df["star_rating"][:10]
R3O9SGZBVQBV76    5
RKH8BNC3L5DLF     5
R2HLE8WKZSU3NL    2
R31U3UH5AZ42LL    5
R2SV659OUJ945Y    4
RA51CP8TR5A2L     5
RB2Q7DLDN6TH6     5
R2RHFJV0UYBK3Y    1
R2Z6JOQ94LFHEP    5
RX27XIIWY5JPB     4
Name: star_rating, dtype: int64

>>> df.loc["R2EQL1V1L6E0C9"]
marketplace               US
customer_id         15305006
review_id     R2EQL1V1L6E0C9
product_id        B004LURNO6
product_parent     892860326
review_headline   Five Stars
review_body          Love it
review_date       2015-08-31
Name: R2EQL1V1L6E0C9, dtype: object

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

Заключение

Фух, после такого долгого чтения вы теперь знаете, как работать с электронными таблицами на Python! Вы можете положиться на openpyxl чтобы:

• Извлекать нужную информацию из таблиц на используя Python
• Создавать свои собственные электронные таблицы, независимо от уровня сложности
• Добавлять в свои электронные таблицы такие классные функции, как условное форматирование или диаграммы.

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

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

Перевод статьи Pedro Pregueiro «A Guide to Excel Spreadsheets in Python With opepyxl».