Работа с таблицами Excel в Python

Очень часто, при парсинге сайтов, нам необходимо сохранить данные в формате Execel, и здесь нам поможет работа с таблицами excel в python.

Для полноценной работы с файлами Excel нам понадобятся библиотеки.

Обзор библиотеки Openpyxl

• Есть возможность чтения EXCEL файлов;
• Два режима чтения файлов:
  • Чтение формул;
  • Чтение значений формул;
• Возможность создания EXCEL файлов 2007-2019;
• Средние возможности оформления, т.е. не очень юзабилити;
• Цвета только через темы;

Обзор библиотеки XLsxWriter

• Нет возможности чтения EXCEL файлов;
• Только создание EXCEL файлов;
• Широкие возможности оформления:
  • Написание формул;
  • Любые цвета и оформление;
  • Графики и таблицы;
  • Автофильтры и проверка данных;
  • Условное форматирование;
  • Спарклайны и группировка;
  • VBA макросы.

Считываем данные с Excel документа

План действий таков:

1. Импортировать библиотеку;
2. Подключиться к файлу в режиме чтения;
3. Подключиться к нужному нам листу;
4. Получить нужные нам данные, указав точный адрес ячейки, где находятся наши данные и применив метод value получаем их;

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

Сам файл вы можете взять здесь.

import openpyxl

book = openpyxl.open('price.xlsx', read_only=True)

sheet = book.active # Открываем активный лист, обычно это первый лист
# sheet = book.worksheets[0] # Открываем первый лист

print(sheet["A2"].value)

Также, что бы обратится к конкретной ячейке можно использовать индексы, т.е. указав номер строки и номер столбца (обратите внимание, это важно – строки нумеруются с 1, а столбцы с 0):

print(sheet[2][3].value) # 2я строка и 4й столбец

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

1. Запускаем цикл for от 1 до max_row, именно этот атрибут нам даст последнюю строку, но функция range() не берет последнее значение, по-этому будет max_row+1;
2. И в цикле можем просто прописать индексы столбцов, их обычно не много, не забываем применить метод value

for row in range(2, sheet.max_row+1): # начинаю с 2, так как первый ряд - развания столбцов
    product = sheet[row][0].value
    price = sheet[row][1].value
    discount = sheet[row][2].value
    rating = sheet[row][3].value
    print(row, product, price, discount, rating)

Считать выборочно строки и столбцы

Бывают ситуации, когда вам не нужно получать всю информацию, которая есть в файле, а достаточно конкретное количество строки и столбцов, и здесь работа с таблицами excel в python тоже достаточно проста! Рассмотрим 2 варианта, как это можно сделать:

1. Создаем переменну ячеек, которые нам нужны;
2. Циклом проходим по этим ячейкам, не забывая, что там хранятся кортежи из элементов колонок.

cells = sheet['A2': 'C8']
for product, price, discount in cells:
    print(product.value, price.value, discount.value)

И второй вариант, если воспользуемся встроенным методом iter_rows(), куда передадим несколько значений:

1. Минимальный ряд, который хотим обойти – min_row;
2. Максимальный ряд – max_row;
3. Минимальную колонку – min_col;
4. Максимальную колонку – max_col;

for elem in sheet.iter_rows(min_row=2, max_row=7, min_col=0, max_col=3):
    print(elem[0].value, elem[1].value, elem[2].value)

Если же в метод iter_rows() ничего не передать, то мы переберем абсолютно весь лист!

А также, мы понимаем, что в переменной цикла elem хранятся ряды с несколькими элементами, т.е. столбцами, следовательно мы их тоже можем обходить циклом:

for elem in sheet.iter_rows(min_row=2, max_row=7, min_col=0, max_col=3):
    for cell in elem:
        print(cell.value, end=' ')
    print()

Запись данных в Excel файл из Python

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

import openpyxl

book = openpyxl.Workbook()
book.save('product.xlsx') # Создастся документ excel

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

import openpyxl

book = openpyxl.Workbook()

sheet = book.active # Указываем в какой лист записываем - первый, он же и активный
sheet['A1'] = 'Number' # В какую ячейку пишем и что именно пишем
sheet['B1'] = 'Name'
sheet['C1'] = 'Price'
book.save('product.xlsx') # Сохраняем данные в файл
book.close() # Закрываем поток