Opennyxl - Работа с Microsoft Excel с помощью Python

Автор оригинала: Mike Driscoll.

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

Электронная таблица или рабочая тетрадь – сам файл (.xls или .xlsx).
Рабочий лист или лист – один лист контента в рабочей книге. Электронные таблицы могут содержать несколько рабочих листов.
Столбец – вертикальная линия данных, помеченная буквами, начиная с «A».
Ряд – горизонтальная линия данных, помеченная цифрами, начиная с 1.
Клетки – сочетание колонны и ряда, как “A1”.

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

Пакеты Python Excel
Получение листов из рабочей книги
Чтение ячейки данных
Итализация по рядам и колонкам
Написание электронных таблиц Excel
Добавление и удаление листов
Добавление и удаление строк и столбцов

Excel используется большинством компаний и университетов. Его можно использовать во многих отношениях и улучшаться с использованием Visual Basic для приложений (VBA). Тем не менее, VBA является своего рода Clunky – почему приятно участвовать, как использовать Excel с Python.

Давайте узнаем, как работать с электронными таблицами Microsoft Excel, используя язык программирования Python сейчас!

Пакеты Python Excel

Вы можете использовать Python для создания, чтения и записи электронных таблиц Excel. Тем не менее, стандартная библиотека Python не имеет поддержки для работы с Excel; Для этого вам нужно будет установить 3-й партийный пакет. Самый популярный – Openpyxl Отказ Вы можете прочитать его документацию здесь:

https://openpyxl.readthedocs.io/en/stable/

Opennyxl не является вашим единственным выбором. Есть несколько других пакетов, которые поддерживают Microsoft Excel:

XLRD – для чтения старых документов Excel (.xls)
XLWT – для написания старых документов Excel (.xls)
xlwings – работает с новыми форматами Excel и имеет макросъемки

Пару лет назад первые два раньше были самыми популярными библиотеками для использования с документами Excel. Однако автор этих пакетов перестал их поддерживать. Упаковка XLWINGS имеет много обещаний, но не работает на всех платформах и требует, чтобы Microsoft Excel установлена.

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

Вы можете установить OpenPyXL, используя Пип :

$ python -m pip install openpyxl

После завершения установки давайте узнаем, как использовать OpenPyXL, чтобы прочитать электронную таблицу Excel!

Получение листов из рабочей книги

Первый шаг – найти файл Excel для использования с OuterPyxl. Есть books.xlsx Файл, который предусмотрен для вас в репозитории Github в этой книге. Вы можете скачать его, перейдем к этому URL:

https://github.com/driscollis/python101code/tree/master/chapter38_excel

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

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

# open_workbook.py

from openpyxl import load_workbook

def open_workbook(path):
    workbook = load_workbook(filename=path)
    print(f'Worksheet names: {workbook.sheetnames}')
    sheet = workbook.active
    print(sheet)
    print(f'The title of the Worksheet is: {sheet.title}')

if __name__ == '__main__':
    open_workbook('books.xlsx')

В этом примере вы импортируете load_workbook () от openpyxl а потом создайте Open_Workook () который принимает путь к вашей электронной таблице Excel. Далее вы используете load_workbook () создать opennyxl.workbook.workbook.workbook объект. Этот объект позволяет получить доступ к листам и ячейке в вашей электронной таблице. И да, у него действительно есть двойная Рабочая книга в его названии. Это не опечатка!

Остальные Open_Workook () Функция демонстрирует, как распечатать все текущие определенные листы в вашей электронной таблице, получите текущий активный лист и распечатайте название этого листа.

Когда вы запускаете этот код, вы увидите следующий вывод:

Worksheet names: ['Sheet 1 - Books']

The title of the Worksheet is: Sheet 1 - Books

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

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

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

Создать новый файл с именем Workbook_cells.py И добавьте этот код к нему:

# workbook_cells.py

from openpyxl import load_workbook

def get_cell_info(path):
    workbook = load_workbook(filename=path)
    sheet = workbook.active
    print(sheet)
    print(f'The title of the Worksheet is: {sheet.title}')
    print(f'The value of {sheet["A2"].value=}')
    print(f'The value of {sheet["A3"].value=}')
    cell = sheet['B3']
    print(f'{cell.value=}')

if __name__ == '__main__':
    get_cell_info('books.xlsx')

Этот код загрузит файл Excel в рабочей книге OuterPyXL. Вы будете захватывать активный лист, а затем распечатать его Название и пара разных клеточных ценностей. Вы можете получить доступ к ячейке, используя объект листа, а затем квадратные скобки с именем столбца и номером строки внутри него. Например, лист ["A2"] Получите вам ячейку в столбце «А», строка 2. Чтобы получить значение этой ячейки, вы используете ценность атрибут.

Примечание: Этот код использует новую функцию, которая была добавлена в F-струны в Python 3.8. Если вы запустите это с более ранней версией, вы получите ошибку.

Когда вы запускаете этот код, вы получите этот вывод:


The title of the Worksheet is: Sheet 1 - Books
The value of sheet["A2"].value='Title'
The value of sheet["A3"].value='Python 101'
cell.value='Mike Driscoll'

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

def get_info_by_coord(path):
    workbook = load_workbook(filename=path)
    sheet = workbook.active
    cell = sheet['A2']
    print(f'Row {cell.row}, Col {cell.column} = {cell.value}')
    print(f'{cell.value=} is at {cell.coordinate=}')

if __name__ == '__main__':
    get_info_by_coord('books.xlsx')

В этом примере вы используете ряд и Колонка атрибуты клетки Объект для получения информации о строке и столбце. Обратите внимание, что столбец «А» отображается на «1», «B» на «2», Etcetera. Если бы вы были повторить документ Excel, вы можете использовать координировать атрибут, чтобы получить имя ячейки.

Когда вы запускаете этот код, вывод будет выглядеть так:

Row 2, Col 1 = Title
cell.value='Title' is at cell.coordinate='A2'

Говоря о итерации, давайте узнаем, как это сделать следующим!

Итализация по рядам и колонкам

Иногда вам нужно будет повторять все электронные таблицы или порции электронной таблицы Excel. OpenPyXL позволяет сделать это несколькими разными способами. Создать новый файл с именем iTerating_over_cells.py и добавьте следующий код:

# iterating_over_cells.py

from openpyxl import load_workbook

def iterating_range(path):
    workbook = load_workbook(filename=path)
    sheet = workbook.active
    for cell in sheet['A']:
        print(cell)

if __name__ == '__main__':
    iterating_range('books.xlsx')

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

Это то, что вы получаете от выполнения этого кода:











# output truncated for brevity

Вывод усечен, поскольку он будет распечатан довольно много ячейки по умолчанию. OpeAnPyXL предоставляет другие способы повторения рядов и столбцов, используя Iter_rows () и ITER_COLS () Функции. Эти методы принимают несколько аргументов:

min_row.
max_row.
min_col.
Max_Col.

Вы также можете добавить на values_only Аргумент, который говорит OwnerPyXL для возврата значения ячейки вместо элемента клеток. Продолжайте и создайте новый файл с именем iTerating_over_cell_values.py И добавьте этот код к нему:

# iterating_over_cell_values.py

from openpyxl import load_workbook

def iterating_over_values(path):
    workbook = load_workbook(filename=path)
    sheet = workbook.active
    for value in sheet.iter_rows(
            min_row=1, max_row=3,
            min_col=1, max_col=3,
            values_only=True,
        ):
        print(value)

if __name__ == '__main__':
    iterating_over_values('books.xlsx')

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

('Books', None, None)
('Title', 'Author', 'Publisher')
('Python 101', 'Mike Driscoll', 'Mouse vs Python')

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

Написание электронных таблиц Excel

Создание электронной таблицы Excel с использованием OpenPyXL не принимает много кода. Вы можете создать электронную таблицу, используя Workbook () класс. Продолжайте и создайте новый файл с именем write_hello.py И добавьте этот код к нему:

# writing_hello.py

from openpyxl import Workbook

def create_workbook(path):
    workbook = Workbook()
    sheet = workbook.active
    sheet['A1'] = 'Hello'
    sheet['A2'] = 'from'
    sheet['A3'] = 'OpenPyXL'
    workbook.save(path)

if __name__ == '__main__':
    create_workbook('hello.xlsx')

Вот вы создали Workbook () и получить активный лист. Затем вы устанавливаете первые три ряда в столбцу «A» на разные строки. Наконец, вы звоните Сохранить () и пропустите это путь чтобы сохранить новый документ. Поздравляю! Вы только что создали электронную таблицу Excel с Python.

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

Добавление и удаление листов

Многие люди любят организовывать свои данные на нескольких рабочих таблицах в рабочей книге. OpenPyxl поддерживает возможность добавлять новые листы в Workbook () объект через его create_sheet () метод.

Создать новый файл с именем crosse_shiets.py И добавьте этот код к нему:

# creating_sheets.py

import openpyxl

def create_worksheets(path):
    workbook = openpyxl.Workbook()
    print(workbook.sheetnames)
    # Add a new worksheet
    workbook.create_sheet()
    print(workbook.sheetnames)
    # Insert a worksheet
    workbook.create_sheet(index=1,
                          title='Second sheet')
    print(workbook.sheetnames)
    workbook.save(path)

if __name__ == '__main__':
    create_worksheets('sheets.xlsx')

Здесь вы используете create_sheet () дважды, чтобы добавить две новые рабочие листы к рабочей книге. Второй пример показывает, как установить заголовок листа и при каком индексе вставить лист. Аргумент индекс = 1 означает, что рабочий лист будет добавлен после первого существующего листа, поскольку они проиндексируются, начиная с 0 Отказ

Когда вы запускаете этот код, вы увидите следующий вывод:

['Sheet']
['Sheet', 'Sheet1']
['Sheet', 'Second sheet', 'Sheet1']

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

После этого автоматического процесса создания рабочего листа вы вдруг получили слишком много листов, поэтому давайте избавимся от некоторых. Есть два способа удалить лист. Идти вперед и создать delete_shiets.py Чтобы увидеть, как использовать Python’s дель Ключевое слово для удаления рабочих листов:

# delete_sheets.py

import openpyxl

def create_worksheets(path):
    workbook = openpyxl.Workbook()
    workbook.create_sheet()
    # Insert a worksheet
    workbook.create_sheet(index=1,
                          title='Second sheet')
    print(workbook.sheetnames)
    del workbook['Second sheet']
    print(workbook.sheetnames)
    workbook.save(path)

if __name__ == '__main__':
    create_worksheets('del_sheets.xlsx')

Этот код создаст новую рабочую книгу, а затем добавить две новые рабочие листы к нему. Затем он использует Python’s дель Ключевое слово для удаления рабочая тетрадь [«второй лист»] Отказ Вы можете убедиться, что он работал, как ожидалось, посмотрев на распечатка списка листов до и после дель команда:

['Sheet', 'Second sheet', 'Sheet1']
['Sheet', 'Sheet1']

Другой способ удалить лист из рабочей книги – использовать Удалить () метод. Создайте новый файл под названием Remove_shiets.py И введите этот код, чтобы узнать, как это работает:

# remove_sheets.py

import openpyxl

def remove_worksheets(path):
    workbook = openpyxl.Workbook()
    sheet1 = workbook.create_sheet()
    # Insert a worksheet
    workbook.create_sheet(index=1,
                          title='Second sheet')
    print(workbook.sheetnames)
    workbook.remove(sheet1)
    print(workbook.sheetnames)
    workbook.save(path)

if __name__ == '__main__':
    remove_worksheets('remove_sheets.xlsx')

На этот раз вы держите ссылку на первый рабочий лист, который вы создаете, назначив результат к Лист1 Отказ Затем вы удалите его позже в коде. В качестве альтернативы, вы также можете удалить этот лист, используя тот же синтаксис, как и раньше, как это:

workbook.remove(workbook['Sheet1'])

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

['Sheet', 'Second sheet', 'Sheet1']
['Sheet', 'Second sheet']

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

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

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

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

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

IDX – индекс для вставки строки или столбца
сумма – количество рядов или столбцов для добавления

Чтобы увидеть, как это работает, создайте файл с именем insert_demo.py . и добавьте следующий код:

# insert_demo.py

from openpyxl import Workbook

def inserting_cols_rows(path):
    workbook = Workbook()
    sheet = workbook.active
    sheet['A1'] = 'Hello'
    sheet['A2'] = 'from'
    sheet['A3'] = 'OpenPyXL'
    # insert a column before A
    sheet.insert_cols(idx=1)
    # insert 2 rows starting on the second row
    sheet.insert_rows(idx=2, amount=2)
    workbook.save(path)

if __name__ == '__main__':
    inserting_cols_rows('inserting.xlsx')

Здесь вы создаете лист и вставьте новый столбец перед столбцом «A». Столбцы индексируются с 1 при контрастности, рабочие листы начнут на 0. Это эффективно перемещает все клетки в столбце A в столбец B. Затем вы вставляете два новых строки, начиная с рядка 2.

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

Чтобы узнать, как удалить столбцы или строки, создайте новый файл с именем delete_demo.py и добавить этот код:

# delete_demo.py

from openpyxl import Workbook

def deleting_cols_rows(path):
    workbook = Workbook()
    sheet = workbook.active
    sheet['A1'] = 'Hello'
    sheet['B1'] = 'from'
    sheet['C1'] = 'OpenPyXL'
    sheet['A2'] = 'row 2'
    sheet['A3'] = 'row 3'
    sheet['A4'] = 'row 4'
    # Delete column A
    sheet.delete_cols(idx=1)
    # delete 2 rows starting on the second row
    sheet.delete_rows(idx=2, amount=2)
    workbook.save(path)

if __name__ == '__main__':
    deleting_cols_rows('deleting.xlsx')

Этот код создает текст в нескольких клетках, а затем удаляет столбец A используя delete_cols () Отказ Это также удаляет два строках, начиная с 2-го ряда через delete_rows () Отказ Возможность добавления и удаления колонн и строки может быть весьма полезным, когда речь идет о организации ваших данных.

Обертывание

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

Пакеты Python Excel
Получение листов из рабочей книги
Чтение ячейки данных
Итализация по рядам и колонкам
Написание электронных таблиц Excel
Добавление и удаление листов
Добавление и удаление строк и столбцов

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