Pandas, Автоматизация, Обработка документов

Создание информативных и красивых Excel документов. XlsxWriter и Pandas

Время прочтения: 6 мин.

В данном материале мы пройдемся по наиболее полезным функциям, которые нам предоставляет связка pandas и XlsxWriter для записи данных.

Для начала загружаем зависимости и создаём DataFrame:

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
sales_df = pd.read_excel('https://github.com/chris1610/pbpython/blob/master/data/sample-salesv3.xlsx?raw=true')
sales_summary = sales_df.groupby(['name'])['ext price'].agg(['sum', 'mean']).reset_index()
sales_summary

Сохранение данных с использованием библиотеки XlsxWriter следует проводить одним из следующих образов:

1-й способ

sales_summary.to_excel('table.xlsx', engine='xlsxwriter', index=False)

2-й способ

with pd.ExcelWriter('table.xlsx', engine='xlsxwriter') as wb:
    sales_summary.to_excel(wb, sheet_name='Summary', index=False)

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

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

Автофильтрация

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

with pd.ExcelWriter('table.xlsx', engine='xlsxwriter') as wb:
    sales_summary.to_excel(wb, sheet_name='Sheet1', index=False)
    sheet = wb.sheets['Sheet1']
    sheet.autofilter('A1:C'+str(sales_summary.shape[0]))

Возможно применение и индексной нотации:

sheet.autofilter(0, 0, sales_summary.shape[0], 2)

(Более подробно о autofilter по ссылке )

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

sheet.autofilter(0, 0, 0, 2)

Настройка размеров ячеек

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

Чтобы установить высоту одной строки следует использовать метод:

sheet.set_row(0, 20)

где 0 – индекс строки, 20 – высота строки.

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

sheet.set_default_row(20)

Для установки ширины столбца есть такой метод:

sheet.set_column(0, 0, 30) # Установить ширину одного столбца A в 30
sheet.set_column(0, 2, 30) # Установить ширину столбцов A, B, C в 30
sheet.set_column('A:C', 30) # Установить ширину столбцов A, B, C в 30

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

with pd.ExcelWriter('table.xlsx', engine='xlsxwriter') as wb:
    sales_summary.to_excel(wb, sheet_name='Sheet1', index=False)
    sheet = wb.sheets['Sheet1']

    sheet.autofilter(0, 0, 0, 2)
    sheet.set_column('A:C', 30)
    sheet.set_column('B:B', 8)

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

Форматирование текста

Форматирование текста, такое как изменение размера, шрифта, цвета и т.д. так же делается с использованием уже известных нам функций: set_column и set_row.

with pd.ExcelWriter('table.xlsx', engine='xlsxwriter') as wb:    
    sales_summary.to_excel(wb, sheet_name='Sheet1', index=False)
    sheet = wb.sheets['Sheet1']

    cell_format = wb.book.add_format()
    cell_format.set_bold()
    cell_format.set_font_color('red')
    sheet.set_row(1, 40, cell_format) # Установка стиля для строки 2 и высоты 40
    
    cell_format = wb.book.add_format()
    cell_format.set_bold()
    cell_format.set_font_color('green')
    sheet.set_column(2, 2, 20, cell_format) # Установка стиля для столбца C и ширины 20
    
    cell_format = wb.book.add_format()
    cell_format.set_bold()
    cell_format.set_font_color('blue')
    sheet.set_column('A:B', 20, cell_format) # Установка стиля для столбцов A и B и ширины 20

В результате получаем следующий файл:

Из таблицы виден важный факт:

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

Если есть сильное желание придать свой собственный формат строке с заголовками таблицы, то можно сделать так:

with pd.ExcelWriter('table.xlsx', engine='xlsxwriter') as wb:    
    sales_summary.to_excel(wb, sheet_name='Sheet1', index=False, header=False, startrow=1)
    sheet = wb.sheets['Sheet1']

    cell_format = wb.book.add_format()
    cell_format.set_font_color('purple')
    cell_format.set_bg_color('#AAAAAA')
    cell_format.set_font_size(18)  

    sheet.write_row(0, 0, sales_summary.columns, cell_format) # сразу пишем целую строку данных
    # аналогично
    #for col, name in enumerate(sales_summary.columns):
    #    sheet.write(0, col, name, cell_format)

Добавление графиков

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

Добавление сгенерированного изображения максимально просто и понятно.

  1. Создаём изображение
  2. Сохраняем его как файл
  3. Указываем полный или относительный путь к файлу изображения, ячейку, в которую хотим поместить изображение и дополнительные опции, если нужно (например отступ от края ячейки, масштабирование ширины, высоты изображения и т.д.. Подробнее о списке опций по ссылке
    Пример добавления изображения в документ:
with pd.ExcelWriter('table.xlsx', engine='xlsxwriter') as wb:    
    sales_summary.to_excel(wb, sheet_name='Sheet1', index=False)
    sheet = wb.sheets['Sheet1']
    sheet.set_column('A:C', 12)

    plt.pie(sales_summary['sum'], labels=sales_summary['name'], radius=1.4)
    plt.savefig('pie.jpeg', dpi=200, bbox_inches='tight')
    sheet.insert_image('E2', 'pie.jpeg')

С другой стороны, для добавления графиков используется непосредственно средствами библиотеки XlsxWriter метод add_chart объекта типа worksheet, в параметрах которого можно указать тип графика (pie в данном случае). После этого нужно заполнить списки категорий и значений через метод add_series. Данный метод принимает ссылки в буквенной и в численной нотации.

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

with pd.ExcelWriter('table.xlsx', engine='xlsxwriter') as wb:    
    sales_summary.to_excel(wb, sheet_name='Sheet1', index=False)
    sheet = wb.sheets['Sheet1']
    sheet.set_column('A:C', 12)

    chart = wb.book.add_chart({'type': 'pie'})
    chart.add_series({
        'categories': '=Sheet1!$A$2:$A$'+str(sales_summary.shape[0]+1),
        'values':     '=Sheet1!$B$2:$B$'+str(sales_summary.shape[0]+1),
    })
    # Аналогично
    #chart.add_series({
    #    'categories': ['Sheet1', 1, 0, sales_summary.shape[0], 0],
    #    'values':     ['Sheet1', 1, 1, sales_summary.shape[0], 1],
    #})
    chart.set_legend({'position': 'bottom'})

    sheet.insert_chart('E2', chart, {
        'x_scale': 2, 'y_scale': 2
    })
    #Аналогично
    #sheet.insert_chart(1, 4, chart, {
    #    'x_scale': 1.5, 'y_scale': 2
    #})

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

Советуем почитать