Статья Простой пример работы с Excel в Python

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

Я иногда просматриваю заказы на фрилансе. Но, просмотреть и взять заказ, это две разные вещи. Ведь для того, чтобы мне стали доступны заказы на сайте FL.ru, нужно заплатить денежку. Нет, это не плохо. Сайту тоже надо как-то зарабатывать. Но, для меня это не особо-то хорошо. Тем не менее, я смотрю заказы для того, чтобы попытаться выполнить то или иное задание и просто получить опыт, который без сомнения понадобиться мне в дальнейшем. Ведь я только начинаю программировать на Python.

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

Ссылка скрыта от гостей

. И после того, как получу данные сохранить их в Excel на разных листах, как и было в заказе. Ну, почти.


Что потребуется?

Для того, чтобы парсить страницы с таблицами необходимо будет установить библиотеки: requests, BeautifulSoup и lxml. Делается это командой:

pip install requests bs4 lxml

А также установить библиотеку openpyxl для работы с форматом Excel:

pip install openpyxl

Ну, а после импортировать это все в проект:

import requests
from bs4 import BeautifulSoup
import openpyxl as xl
from openpyxl import Workbook

Что же, давайте сходим на сайт, который будем парсить и заберем оттуда заголовки для будущих запросов. Я их сделаю глобальными, чтобы можно было использовать везде. Заходим на сайт, жмем правой кнопкой мыши по странице и выбираем пункт меню: «Исследовать элемент». Ну или «Исследовать», если вы пользуетесь FireFox. Суть тут в том, что нужно открыть «Инструменты разработчика». Далее, переходим на вкладку Network, обновляем страницу, ставим фильтр на Doc, жмем по появившемуся здесь запросу и переходим в Headers.

Заголовки запроса

Я заберу только user-agent и accept. Больше в данных запросах нам ничего не нужно. Этого достаточно. Создаем заголовки :

headers = {
    'user-agent': 'Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/96.0.4664.174 '
                  'YaBrowser/22.1.3.942 Yowser/2.5 Safari/537.36',
    'accept': 'text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,'
              'application/signed-exchange;v=b3;q=0.9 '
}

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

Псевдовыпадающий список

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

Код списка

И вот список, который у меня получился в итоге:

num_year_dict = ['443', '442', '441', '440', '439', '438', '437', '436', '435', '434', '433', '432', '431', '426',
                     '425', '1', '2', '165', '884', '1851', '3226', '4385', '4959', '5582', '6297', '6886', '7371',
                     '8071', '8671']

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

Workbook().save('Турнирная таблица ПЛ РФ.xlsx')

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

    for num in num_year_dict:
        url = f'https://www.sports.ru/rfpl/table/?s={num}&table=0&sub=table'
        get_headers(url)
        get_table_content(url)

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

wb = xl.load_workbook('Турнирная таблица ПЛ РФ.xlsx')
    if 'Sheet' in wb.sheetnames:
        del wb['Sheet']
        wb.save('Турнирная таблица ПЛ РФ.xlsx')

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

Код в собранном виде

import requests
from bs4 import BeautifulSoup
import openpyxl as xl
from openpyxl import Workbook

# заголовки запроса
headers = {
    'user-agent': 'Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/96.0.4664.174 '
                  'YaBrowser/22.1.3.942 Yowser/2.5 Safari/537.36',
    'accept': 'text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,'
              'application/signed-exchange;v=b3;q=0.9 '
}


# создание списка для подмены номера таблицы в адресе страницы
# создание книги Excel и сохранение с нужным названием
# запуск цикла для перебора ссылок и передачи их в функции
# для получения заголовков и получения данных из таблицы
# удаление листа, который создается по умолчанию в книге Excel
def main():
    num_year_dict = ['443', '442', '441', '440', '439', '438', '437', '436', '435', '434', '433', '432', '431', '426',
                     '425', '1', '2', '165', '884', '1851', '3226', '4385', '4959', '5582', '6297', '6886', '7371',
                     '8071', '8671']
    Workbook().save('Турнирная таблица ПЛ РФ.xlsx')
    for num in num_year_dict:
        url = f'https://www.sports.ru/rfpl/table/?s={num}&table=0&sub=table'
        get_headers(url)
        get_table_content(url)

    wb = xl.load_workbook('Турнирная таблица ПЛ РФ.xlsx')
    if 'Sheet' in wb.sheetnames:
        del wb['Sheet']
        wb.save('Турнирная таблица ПЛ РФ.xlsx')


if __name__ == "__main__":
    main()

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

Для начала открываем книгу Excel:

wb = xl.load_workbook('Турнирная таблица ПЛ РФ.xlsx')

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

req = requests.get(url=url, headers=headers)
    soup = BeautifulSoup(req.text, 'lxml')
    title_table = soup.find('h2', class_='titleH3').text.split("-")[2].strip().replace("/", "_")

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

Заголовок таблицы

А код, который его выводит на страницу так:

Код заголовка таблицы

Поэтому я выполняю поиск в коде страницы заголовка «h2» с классом «titleH3», после получаю из него текст, с помощью функции text и после разделяю и заменяю слеши на подчеркивания.

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

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

Сами заголовки находятся в таблице с классом «stat-table table sortable-table», которую мы и ищем изначально. Далее, в этой таблице ищем тег «thead», и после выполняем поиск всех элементов с тегом «td». В результате поиска у нас получается список, который содержит все элементы td с содержащимся в них кодом.

if not title_table in wb.sheetnames:
    wb.create_sheet(title_table)
    sheet = wb[title_table]
    headers_table = soup.find('table', class_='stat-table table sortable-table').find('thead').find_all('td')[1:]
    for num, item in enumerate(headers_table):
        if num == 0:
            header = item.text
        else:
            header = item['title']
        sheet.cell(1, num + 1).value = header
wb.save('Турнирная таблица ПЛ РФ.xlsx')

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

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

Вот полный код функции:

Код функции

# функция для получения заголовков таблицы
# создание листов с названиями турниров по годам
# и сохранение заголовков на этих листах
def get_headers(url):
    wb = xl.load_workbook('Турнирная таблица ПЛ РФ.xlsx')
    req = requests.get(url=url, headers=headers)
    soup = BeautifulSoup(req.text, 'lxml')
    title_table = soup.find('h2', class_='titleH3').text.split("-")[2].strip().replace("/", "_")
    print(title_table)
    if not title_table in wb.sheetnames:
        wb.create_sheet(title_table)
        sheet = wb[title_table]
        headers_table = soup.find('table', class_='stat-table table sortable-table').find('thead').find_all('td')[1:]
        for num, item in enumerate(headers_table):
            if num == 0:
                header = item.text
            else:
                header = item['title']
            sheet.cell(1, num + 1).value = header
    wb.save('Турнирная таблица ПЛ РФ.xlsx')

Ну и осталось дописать функцию, которая получает данные из таблиц и добавляет их на листы в книге Excel. Назовем ее def get_table_content(url). На вход она получает ссылку на страницу, на которой и будет производиться поиск данных.

Вот код данной функции:

Код функции get_table_content(url)

# функция для получения данных из тела таблицы
# сохранение данных в листы, соответствующие данным на сайте
def get_table_content(url):
    wb = xl.load_workbook('Турнирная таблица ПЛ РФ.xlsx')
    req = requests.get(url=url, headers=headers)
    soup = BeautifulSoup(req.text, 'lxml')
    title_table = soup.find('h2', class_='titleH3').text.split("-")[2].strip().replace("/", "_")
    sheet = wb[title_table]
    tbody = soup.find('tbody').find_all('tr')
    count = 2
    for tr in tbody:
        tds = tr.find_all('td')[1:][0].text
        sheet.cell(count, 1).value = tds
        tds = tr.find_all('td')[1:][1].text
        sheet.cell(count, 2).value = tds
        tds = tr.find_all('td')[1:][2].text
        sheet.cell(count, 3).value = tds
        tds = tr.find_all('td')[1:][3].text
        sheet.cell(count, 4).value = tds
        tds = tr.find_all('td')[1:][4].text
        sheet.cell(count, 5).value = tds
        tds = tr.find_all('td')[1:][5].text
        sheet.cell(count, 6).value = tds
        tds = tr.find_all('td')[1:][6].text
        sheet.cell(count, 7).value = tds
        tds = tr.find_all('td')[1:][7].text
        sheet.cell(count, 8).value = tds
        count += 1
    wb.save('Турнирная таблица ПЛ РФ.xlsx')

А теперь, небольшое описание. Открываем книгу. После этого делаем запрос по полученной ссылке и передаем данные для распарсивания в BeautifulSoup. Здесь я так же выполнил поиск названия таблицы, потому, что это показалось мне более быстрым и приемлемым вариантом, чем тусовать данные из функции в функцию. Тем более, таки незначительные. Ну или проще ). Конечно же, лишний поиск немного замедляет выполнение функции, но, совсем незначительно.

Дальше устанавливаем активным лист, с которым будем работать. Ищем в теле таблицы все теги «tr». Затем устанавливаем счетчик. Он будет нужен для того, чтобы менять номера строк и будет увеличиваться после выполнения всего кода по добавлению полученных значений. Ну, а после я просто решил схитрить, чтобы использовать поменьше циклов. Ищу все теги td в tr, после делаю срез, чтобы данные брались с 1 элемента списка. Так как в нулевом содержится картинка, а вернее ссылка на нее и она в данном случае не нужна совсем. Ну и получаю нулевой элемент из которого берется текс. Дальше он заноситься в ячейку листа книги. И так для всех блоков tr. Конечно же, если бы их было больше, я тогда воспользовался бы циклом, но в данном случае решил, что можно схитрить и обойтись малой кровью. Ну и после заполнения данных в таблице за год сохраняю ее.

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

Конечно же, думаю, что для парсинга таких вот данных работать надо с другими библиотеками. Но, с пандой я пока не особо. Могу только обещать исправиться. Так как библиотека интересная и работать с ней научиться нужно обязательно. Я говорю про pandas.

Результат работы скрипта

Полный код скрипта

"""В данном коде показана работа с таблицами Excel с помощью Python.
Иногда возникает потребность в парсинге данных, которые состоят из нескольких
таблиц, как, например таблицы на сайте: https://www.sports.ru/rfpl/table/
Здесь возможно несколько вариантов действий. Вот некоторые из них:
1. Сохранение полученных данных в JSON
2. Сохранение полученных данных в CSV
3. Сохранение данных в простые текстовые файлы.
Я решил попробовать изпользовать Excel для сохранения данных.
Чтобы в процессе парсинга создавались новые листы в книге и наполнялись
необходимыми данными"""

import requests
from bs4 import BeautifulSoup
import openpyxl as xl
from openpyxl import Workbook

# заголовки запроса
headers = {
    'user-agent': 'Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/96.0.4664.174 '
                  'YaBrowser/22.1.3.942 Yowser/2.5 Safari/537.36',
    'accept': 'text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,'
              'application/signed-exchange;v=b3;q=0.9 '
}


# функция для получения заголовков таблицы
# создание листов с названиями турниров по годам
# и сохранение заголовков на этих листах
def get_headers(url):
    wb = xl.load_workbook('Турнирная таблица ПЛ РФ.xlsx')
    req = requests.get(url=url, headers=headers)
    soup = BeautifulSoup(req.text, 'lxml')
    title_table = soup.find('h2', class_='titleH3').text.split("-")[2].strip().replace("/", "_")
    print(title_table)
    if not title_table in wb.sheetnames:
        wb.create_sheet(title_table)
        sheet = wb[title_table]
        headers_table = soup.find('table', class_='stat-table table sortable-table').find('thead').find_all('td')[1:]
        for num, item in enumerate(headers_table):
            if num == 0:
                header = item.text
            else:
                header = item['title']
            sheet.cell(1, num + 1).value = header
    wb.save('Турнирная таблица ПЛ РФ.xlsx')


# функция для получения данных из тела таблицы
# сохранение данных в листы, соответствующие данным на сайте
def get_table_content(url):
    wb = xl.load_workbook('Турнирная таблица ПЛ РФ.xlsx')
    req = requests.get(url=url, headers=headers)
    soup = BeautifulSoup(req.text, 'lxml')
    title_table = soup.find('h2', class_='titleH3').text.split("-")[2].strip().replace("/", "_")
    sheet = wb[title_table]
    tbody = soup.find('tbody').find_all('tr')
    count = 2
    for tr in tbody:
        tds = tr.find_all('td')[1:][0].text
        sheet.cell(count, 1).value = tds
        tds = tr.find_all('td')[1:][1].text
        sheet.cell(count, 2).value = tds
        tds = tr.find_all('td')[1:][2].text
        sheet.cell(count, 3).value = tds
        tds = tr.find_all('td')[1:][3].text
        sheet.cell(count, 4).value = tds
        tds = tr.find_all('td')[1:][4].text
        sheet.cell(count, 5).value = tds
        tds = tr.find_all('td')[1:][5].text
        sheet.cell(count, 6).value = tds
        tds = tr.find_all('td')[1:][6].text
        sheet.cell(count, 7).value = tds
        tds = tr.find_all('td')[1:][7].text
        sheet.cell(count, 8).value = tds
        count += 1
    wb.save('Турнирная таблица ПЛ РФ.xlsx')


# создание списка для подмены номера таблицы в адресе страницы
# создание книги Excel и сохранение с нужным названием
# запуск цикла для перебора ссылок и передачи их в функции
# для получения заголовков и получения данных из таблицы
# удаление листа, который создается по умолчанию в книге Excel
def main():
    num_year_dict = ['443', '442', '441', '440', '439', '438', '437', '436', '435', '434', '433', '432', '431', '426',
                     '425', '1', '2', '165', '884', '1851', '3226', '4385', '4959', '5582', '6297', '6886', '7371',
                     '8071', '8671']
    Workbook().save('Турнирная таблица ПЛ РФ.xlsx')
    for num in num_year_dict:
        url = f'https://www.sports.ru/rfpl/table/?s={num}&table=0&sub=table'
        get_headers(url)
        get_table_content(url)

    wb = xl.load_workbook('Турнирная таблица ПЛ РФ.xlsx')
    if 'Sheet' in wb.sheetnames:
        del wb['Sheet']
        wb.save('Турнирная таблица ПЛ РФ.xlsx')


if __name__ == "__main__":
    main()

Спасибо за внимание. Надеюсь, что данная статья будет кому-нибудь полезна

 

[Сергей Сталь]

Спасибо, полезная статья. Можно использовать в работе

 

[АНТОН ЧИРСКИЙ]

Спасибо! В чём ошибка?

Traceback (most recent call last):
File "c:\PYTHON SCRIPT\Python scriippt.py", line 101, in <module>
main()
File "c:\PYTHON SCRIPT\Python scriippt.py", line 91, in main
get_headers(url)
File "c:\PYTHON SCRIPT\Python scriippt.py", line 33, in get_headers
title_table = soup.find('h2', class_='titleH3').text.split("-")[2].strip().replace("/", "_")
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
AttributeError: 'NoneType' object has no attribute 'text'

 

[f22]

title_table = soup.find('h2', class_='titleH3').text

У вас soup не нашёл нужный тэг на странице, поэтому и результат вернул None, а у этого результата была попытка получить текст - конечно же у None его нет.
Проверьте, что именно вам вернул запрос выше и есть ли в нём эти теги.