Сравнение двух файлов excel python

Обновлено: 22.01.2025

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Название_компьютера, версия и количество.

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

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

Мне нужно сравнить эти листы и создать отчет о том, совместима ли организация или нет.

Следовательно, как мне сравнить эти два файла.

Как это сделать?

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

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

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

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

Если у вас есть опыт работы с SQLite или любой другой базой данных с Python, то создайте таблицу продуктов LANdesk, а процедуры нормализации и поиска должны быть достаточно простыми, но если нет, тогда будет больше pseudocode и примеров. Дайте мне знать, если вам это нужно.

Существует несколько библиотек для работы с файлами .xls.

XLRD позволяет извлекать данные из файлов электронной таблицы Excel. Таким образом, вы можете легко сравнивать два файла. (прочитать)

XLWT позволяет создавать файлы Excel. (записи)

XLUtils требует как пакетов xlrd, так и xlwt. Таким образом, вы можете читать и писать благодаря этой библиотеке.

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

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

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

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