Создание информативных и красивых Excel документов. XlsxWriter и Pandas
В данном материале мы пройдемся по наиболее полезным функциям, которые нам предоставляет связка 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 следует проводить одним из следующих образов:
sales_summary.to_excel('table.xlsx', engine='xlsxwriter', index=False)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 корректно применяет фильтр даже когда указан только диапазон колонок, что несколько упрощает работу. Например, как в следующем случае:
Настройка размеров ячеек
Изначально XlsxWriter предоставляет нам инструменты для установки высоты и ширины как для целых строк и столбцов, так и для их диапазонов, с некоторой оговоркой.
Чтобы установить высоту одной строки следует использовать метод:
где 0 – индекс строки, 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 или же как обыкновенное сгенерированное изображение.
Добавление сгенерированного изображения максимально просто и понятно.
- Создаём изображение
- Сохраняем его как файл
- Указываем полный или относительный путь к файлу изображения, ячейку, в которую хотим поместить изображение и дополнительные опции, если нужно (например отступ от края ячейки, масштабирование ширины, высоты изображения и т.д.. Подробнее о списке опций по ссылке
Пример добавления изображения в документ:
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() 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() 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. Их очень легко и удобно встраивать в собственные проекты. Использование средств, описанных в данной статье, не исчерпывают все возможности этих библиотек, но даже с этим скромным инструментарием в кармане, вы можете сделать ваши выгрузки намного более информативными и приятными глазу.
Python xlsxwriter: как задать ширину колонок по максимальной длине текста в ней?
У меня есть excel файл. Подскажите, как с помощью xlsxwriter раздвинуть столбцы по ширине, чтобы помещалась максимальная информация?
То есть например, в колонке ширина будет 3, в колонке ширина будет 5 и т.д.
То есть сделать то же самое, как если выделить всю таблицу вручную и кликнуть по границе столбца.
Вопрос в том, как пробежаться по всем столбцам и установить нужную ширину?
Пример покажу на картинке:
Ответы (1 шт):
worksheet.set_column(0, 0, 20) # Column A width set to 20. worksheet.set_column(1, 3, 30) # Columns B-D width set to 30. worksheet.set_column('E:E', 20) # Column E width set to 20. worksheet.set_column('F:H', 30) # Columns F-H width set to 30. Судя по документации нет простого способа «AutoFIT» (вписать размер ячейки по содержимому). Выход есть конечно, это считать длину для каждой ячейки и находить максимальную, и потом использовать её для установки ширины. Проще назначить «максимальный» и удобоваримый для глаз размер заранее. Например 20-25 знаков.
There is no way to specify “AutoFit” for a column in the Excel file format. This feature is only available at runtime from within Excel. It is possible to simulate “AutoFit” in your application by tracking the maximum width of the data in the column as your write it and then adjusting the column width at the end.