Вставить формулу в excel openpyxl

Обновлено: 08.01.2025

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

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

Для автоматизации отчетов в Excel вам не придется убеждать своего начальника перейти на Python! Можно просто использовать модуль Python openpyxl, чтобы сообщить Excel, что вы хотите работать через Python. При этом процесс создания отчетов получится автоматизировать, что значительно упростит вашу жизнь.

Набор данных

В этом руководстве мы будем использовать файл Excel с данными о продажах. Он похож на те файлы, которые используются в качестве входных данных для создания отчетов во многих компаниях. Вы можете скачать этот файл на Kaggle. Однако он имеет формат .csv , поэтому вам следует изменить расширение на .xlsx или просто загрузить его по этой ссылке на Google Диск (файл называется supermarket_sales.xlsx).

Прежде чем писать какой-либо код, внимательно ознакомьтесь с файлом на Google Drive. Этот файл будет использоваться как входные данные для создания следующего отчета на Python:

Теперь давайте сделаем этот отчет и автоматизируем его составление с помощью Python!

Создание сводной таблицы с помощью pandas

Импорт библиотек

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

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

Примечание. Если у вас не установлены эти библиотеки в Python, вы можете легко установить их, выполнив pip install pandas и pip install openpyxl в командной строке.

Марк Лутц «Изучаем Python»

Скачивайте книгу у нас в телеграм

Чтение файла Excel

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

В файле много столбцов, но для нашего отчета мы будем использовать только столбцы Gender , Product line и Total . Чтобы показать вам, как они выглядят, я выбрал их с помощью двойных скобок. Если мы выведем это в Jupyter Notebooks, увидим следующий фрейм данных, похожий на таблицу Excel:

Создание сводной таблицы

Теперь мы можем легко создать сводную таблицу из ранее созданного фрейма данных excel_file . Для этого нам просто нужно использовать метод .pivot_table() .

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

Таблица report_table должна выглядеть примерно так:

Экспорт сводной таблицы в файл Excel

Чтобы экспортировать созданную сводную таблицу, мы используем метод .to_excel() . Внутри скобок нужно написать имя выходного файла Excel. В данном случае давайте назовем этот файл report_2021.xlsx.

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

Теперь файл Excel экспортируется в ту же папку, в которой находится ваш скрипт Python.

Создание отчета с помощью openpyxl

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

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

Создание ссылки на строку и столбец

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

Чтобы получить ссылки в книге Excel, мы сначала загружаем её с помощью функции load_workbook() и находим лист, с которым хотим работать, используя wb[‘имя листа’] . Затем мы получаем доступ к активным ячейкам с помощью метода .active .

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

Откройте файл report_2021.xlsx, который мы экспортировали ранее, чтобы убедиться в этом.

Добавление диаграмм в Excel при помощи Python

Чтобы создать диаграмму в Excel на основе созданной нами сводной таблицы, нужно использовать модуль Barchart . Его мы импортировали ранее. Для определения позиций значений данных и категорий мы используем модуль Reference из openpyxl (его мы тоже импортировали в самом начале).

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

• barchart = BarChart() инициализирует переменную barchart из класса Barchart .
• data и categories – это переменные, которые показывают, где находится необходимая информация. Для автоматизации мы используем ссылки на столбцы и строки, которые определили выше. Также имейте в виду, что мы включаем заголовки в данные, но не в категории.
• Мы используем add_data() и set_categories() , чтобы добавить необходимые данные в гистограмму. Внутри add_data() добавим title_from_data = True , потому что мы включили заголовки для данных.
• Метод sheet.add_chart() используется для указания, что мы хотим добавить нашу гистограмму в лист Report. Также мы указываем, в какую ячейку мы хотим её добавить.
• Дальше мы изменяем заголовок и стиль диаграммы, используя barchart.title и barchart.style .
• И наконец, сохраняем все изменения с помощью wb.save()

Вот и всё! С помощью данного кода мы построили диаграмму в Excel.

Применение формул в Excel через Python

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

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

Довольно просто, не правда ли? Мы можем протянуть эту формулу от столбца B до G или использовать цикл for для автоматизации. Однако сначала нам нужно получить алфавит, чтобы ссылаться на столбцы в Excel (A, B, C, …). Для этого воспользуемся библиотекой строк и напишем следующий код:

Если мы распечатаем excel_alphabet , мы получим список от A до G.

Так происходит потому, что сначала мы создали алфавитный список от A до Z, а затем взяли срез [0:max_column] , чтобы сопоставить длину этого списка с первыми 7 буквами алфавита (A-G).

Примечание. Нумерация в Python начинаются с 0, поэтому A = 0, B = 1, C = 2 и так далее. Срез [a:b] возвращает элементы от a до b-1 .

Применение формулы к нескольким ячейкам

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

мы используем ссылки на столбцы и помещаем их в цикл for :

После запуска кода мы получаем формулу суммы в строке Total для столбцов от B до G:

Посмотрим, что делает данный код:

• for i in excel_alphabet проходит по всем активным столбцам, кроме столбца A ( if i! = 'A' ), так как столбец A не содержит числовых данных
• запись sheet[f''] = f'=SUM(:' это то же самое, что и sheet['B7'] = '=SUM(B5:B6)' , только для столбцов от A до G
• строчка sheet [f ' '].style = 'Currency' задает денежный формат ячейкам с числовыми данными (т.е. тут мы опять же исключаем столбец А)
• мы добавляем запись Total в столбец А под максимальной строкой (т.е. под седьмой), используя код [f ' '] = 'Total'

Форматирование листа с отчетом

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

Вы также можете добавить другие параметры внутри Font() . В документации openpyxl можно найти список доступных стилей.

Итоговый отчет должен выглядеть следующим образом:

Автоматизация отчета с помощью функции Python

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

Примечание. Чтобы эта функция работала, имя файла должно иметь структуру «sales_month.xlsx». Кроме того, мы добавили несколько строк кода, которые используют месяц/год файла продаж в качестве переменной, чтобы мы могли повторно использовать это в итоговом файле и подзаголовке отчета.

Приведенный ниже код может показаться устрашающим, но это просто объединение всего того, что мы написали выше. Плюс новые переменные file_name , month_name и month_and_extension .

Применение функции к одному файлу Excel

Представим, что исходный файл, который мы загрузили, имеет имя sales_2021.xlsx вместо supermarket_sales.xlsx. Чтобы применить формулу к отчету, пишем следующее:

После запуска этого кода вы получите файл Excel с именем report_2021.xlsx в той же папке, где находится ваш скрипт Python.

Применение функции к нескольким файлам Excel

Представим, что теперь у нас есть только ежемесячные файлы Excel sales_january.xlsx, sales_february.xlsx и sales_march.xlsx (эти файлы можно найти на GitHub).

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

Или можно сначала объединить эти три отчета с помощью pd.concat() , а затем применить функцию только один раз.

Заключение

Код на Python, который мы написали в этом руководстве, можно запускать на вашем компьютере по расписанию. Для этого нужно просто использовать планировщик задач или crontab. Вот и все!

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

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

Модуль OpenPyXL не поставляется вместе с Python, поэтому его предварительно нужно установить:

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

А теперь небольшой скрипт:

Как получить другой лист книги:

Как сделать лист книги активным:

Как задать имя листа:

Объект Cell имеет атрибут value , который содержит значение, хранящееся в ячейке. Объект Cell также имеет атрибуты row , column и coordinate , которые предоставляют информацию о расположении данной ячейки в таблице.

К отдельной ячейке можно также обращаться с помощью метода cell() объекта Worksheet , передавая ему именованные аргументы row и column . Первому столбцу или первой строке соответствует число 1, а не 0:

Размер листа можно получить с помощью атрибутов max_row и max_column объекта Worksheet :

Чтобы преобразовать буквенное обозначение столбца в цифровое, следует вызвать функцию

Чтобы преобразовать цифровое обозначение столбуа в буквенное, следует вызвать функцию

Для вызова этих функций загружать рабочую книгу не обязательно.

Используя срезы объектов Worksheet , можно получить все объекты Cell , принадлежащие определенной строке, столбцу или прямоугольной области.

Выводим значения второй колонки:

Выводим строки с первой по третью:

Для доступа к ячейкам конкретной строки или столбца также можно воспользоваться атрибутами rows и columns объекта Worksheet .

Выводим значения всех ячеек листа:

Выводим значения второй строки (индекс 1):

Выводим значения второй колонки (индекс 1):

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

Метод create_sheet() возвращает новый объект Worksheet , который по умолчанию становится последним листом книги. С помощью именованных аргументов title и index можно задать имя и индекс нового листа.

Метод remove() принимает в качестве аргумента не строку с именем листа, а объект Worksheet . Если известно только имя листа, который надо удалить, используйте wb[sheetname] . Еще один способ удалить лист — использовать инструкцию del wb[sheetname] .

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

Запись значений в ячейки напоминает запись значений в ключи словаря:

Заполняем таблицу 3x3:

Можно добавлять строки целиком:

Стилевое оформление

Для настройки шрифтов, используемых в ячейках, необходимо импортировать функцию Font() из модуля openpyxl.styles :

Ниже приведен пример создания новой рабочей книги, в которой для шрифта, используемого в ячейке A1 , устанавливается шрифт Arial , красный цвет, курсивное начертание и размер 24 пункта:

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

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

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

Эта инструкция сохранит =SUM(B1:B8) в качестве значения в ячейке B9 . Тем самым для ячейки B9 задается формула, которая суммирует значения, хранящиеся в ячейках от B1 до B8 .

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

Использование ссылок на ячейки позволяет пересчитывать результат по формулам, когда происходят изменения содержимого ячеек, включенных в формулы. Формулы Excel начинаются со знака = . Скобки () могут использоваться для определения порядка математических операции.

Примеры формул Excel: =27+36 , =А1+А2-АЗ , =SUM(А1:А5) , =MAX(АЗ:А5) , =(А1+А2)/АЗ .

Хранящуюся в ячейке формулу можно читать, как любое другое значение. Однако, если нужно получить результат расчета по формуле, а не саму формулу, то при вызове функции load_workbook() ей следует передать именованный аргумент data_only со значением True .

Настройка строк и столбцов

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

Настройка высоты строк и ширины столбцов

Объекты Worksheet имеют атрибуты row_dimensions и column_dimensions , которые управляют высотой строк и шириной столбцов.

Атрибуты row_dimension s и column_dimensions представляют собой значения, подобные словарю. Атрибут row_dimensions содержит объекты RowDimensions , а атрибут column_dimensions содержит объекты ColumnDimensions . Доступ к объектам в row_dimensions осуществляется с использованием номера строки, а доступ к объектам в column_dimensions — с использованием буквы столбца.

Для указания высоты строки разрешено использовать целые или вещественные числа в диапазоне от 0 до 409. Для указания ширины столбца можно использовать целые или вещественные числа в диапазоне от 0 до 255. Столбцы с нулевой шириной и строки с нулевой высотой невидимы для пользователя.

Объединение ячеек

Ячейки, занимающие прямоугольную область, могут быть объединены в одну ячейку с помощью метода merge_cells() рабочего листа:

Чтобы отменить слияние ячеек, надо вызвать метод unmerge_cells() :

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

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

У объекта Worksheet имеется атрибут freeze_panes , значением которого может служить объект Cell или строка с координатами ячеек. Все строки и столбцы, расположенные выше и левее, будут заблокированы.

Значение атрибута freeze_panes	Заблокированные строки и столбцы
sheet.freeze_panes = 'A2'	Строка 1
sheet.freeze_panes = 'B1'	Столбец A
sheet.freeze_panes = 'C1'	Столбцы A и B
sheet.freeze_panes = 'C2'	Строка 1 и столбцы A и B
sheet.freeze_panes = None	Закрепленные области отсутствуют

Диаграммы

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

1. создать объект Reference на основе ячеек в пределах выделенной прямоугольной области;
2. создать объект Series , передав функции Series() объект Reference ;
3. создать объект Chart;
4. дополнительно можно установить значения переменных drawing.top , drawing.left , drawing.width , drawing.height объекта Chart , определяющих положение и размеры диаграммы;
5. добавить объект Chart в объект Worksheet .

1. Объект Worksheet , содержащий данные диаграммы.
2. Два целых числа, представляющих верхнюю левую ячейку выделенной прямоугольной области, в которых содержатся данные диаграммы: первое число задает строку, второе — столбец; первой строке соответствует 1, а не 0.
3. Два целых числа, представляющих нижнюю правую ячейку выделенной прямоугольной области, в которых содержатся данные диаграммы: первое число задает строку, второе — столбец.

Аналогично можно создавать графики, точечные и круговые диаграммы, вызывая методы:

Диаграммы состоят как минимум из одной серии из одной или нескольких точек данных. Сами серии состоят из ссылок на диапазоны ячеек. Давайте посмотрим, как построить Scatter, Bubble, Pie, 3D Pie Chart на листе Excel с использованием openpyxl.

Для построения диаграмм на листе Excel, во-первых, создайте объект диаграммы определенного класса диаграммы (например, ScatterChart, PieChart и т. Д.). После создания объектов диаграммы вставьте в него данные и, наконец, добавьте этот объект диаграммы в объект листа. Давайте посмотрим, как строить различные графики, используя данные в реальном времени.

Код № 1: нанесите пузырьковую диаграмму.

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

( "Number of Products" , "Sales in USD" , "Market share" ),

for row in rows:

xvalues = Reference(sheet, min_col = 1 ,

min_row = 2 , max_row = 5 )

yvalues = Reference(sheet, min_col = 2 ,

min_row = 2 , max_row = 5 )

size = Reference(sheet, min_col = 3 ,

min_row = 2 , max_row = 5 )

series = Series(values = yvalues, xvalues = xvalues,

zvalues = size, title = "2013" )

Выход:

Код № 2 : построение точечной диаграммы

( "Number of Products" , "Sales in USD" , "Market share" ),

for row in rows:

xvalues = Reference(sheet, min_col = 1 ,

min_row = 2 , max_row = 5 )

yvalues = Reference(sheet, min_col = 2 ,

min_row = 2 , max_row = 5 )

size = Reference(sheet, min_col = 3 ,

min_row = 2 , max_row = 5 )

series = Series(values = yvalues, xvalues = xvalues,

zvalues = size, title = "2013" )

Выход:

Код № 3 : Составьте круговую диаграмму

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

for row in datas:

labels = Reference(sheet, min_col = 1 ,

min_row = 2 , max_row = 5 )

data = Reference(sheet, min_col = 2 ,

min_row = 1 , max_row = 5 )

chart.add_data(data, titles_from_data = True )

Выход:

Код № 4 : нанесите гистограмму

for row in datas:

labels = Reference(sheet, min_col = 1 ,

min_row = 2 , max_row = 5 )

data = Reference(sheet, min_col = 2 ,

min_row = 1 , max_row = 5 )

chart.add_data(data, titles_from_data = True )

.xlsx – это расширение документа Excel, который может хранить большой объем данных в табличной форме. Более того, в электронной таблице Excel можно легко выполнять многие виды арифметических и логических вычислений.

Иногда программистам требуется прочитать данные из документа Excel. В Python для этого есть множество различных библиотек, например, xlrd , openpyxl и pandas . Сегодня мы поговорим о том, как читать excel-файлы (xlsx) при помощи Python, и рассмотрим примеры использования различных библиотек для этих целей.

Для начала

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

sales.xlsx

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 в следующей части этого руководства.

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

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

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

Воспользуемся функцией open_workbook() для открытия файла xlsx для чтения. Этот файл Excel содержит только одну таблицу. Поэтому функция workbook.sheet_by_index() используется в скрипте со значением аргумента 0.

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

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

Запустим наш код и получим следующий результат.

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

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

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

Как и модуль xlrd, модуль openpyxl имеет функцию load_workbook() для открытия excel-файла для чтения. В качестве значения аргумента этой функции используется файл sales.xlsx.

Объект wookbook.active служит для чтения значений свойств max_row и max_column . Эти свойства используются во вложенных циклах for для чтения содержимого файла sales.xlsx.

Запустив наш скрипт, получим следующий вывод.

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

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

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

В библиотеке pandas есть функция read_excel() , которую можно использовать для чтения .xlsx -файлов. Ею мы и воспользуемся в нашем скрипте для чтения файла sales.xlsx.

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

После запуска кода мы получим следующий вывод.

Заключение

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

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

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

  
• Почему вкладки в браузере белые
  
• Программа для открытия папок и файлов на компьютере
  
• Как увеличить рост в фотошопе
  
• Как в ворде убрать разлиновку в линейку
  
• Удалил explorer exe что делать