Applying formatting to multiple Excel sheets using Python and XLSXWriter

user_2781105 0
Viewed 1652

I have two dataframes as follows:

import pandas as pd 
import numpy as np
from datetime import date

df = pd.DataFrame({'Data': [10, 22, 31, 43, 57, 99, 65, 74, 88],
                  'Data2':[10, 22, 31, 43, 57, 99, 65, 74, 88],
                  'Data3':[10, 22, 31, 43, 57, 99, 65, 74, 88]})

df2 = pd.DataFrame({'df2_Data': ['blue', 'yellow', 'purple', 'orange', 'green', 'brown', 'gray', 'white', 'red'],
                  'df2_Data2':['bike', 'car', 'bus', 'train', 'boat', 'truck', 'plane', 'scooter', 'skateboard'],
                  'df2_Data3':['chicken', 'cow', 'dog', 'crocodile', 'snake', 'pig', 'rat', 'mouse', 'monkey']})

I can export df, with the desired formatting, as a single sheet in an Excel with the following code:

today = date.today()
d2 = today.strftime("%B %d, %Y")



writer = pd.ExcelWriter('ExcelExample{}.xlsx'.format(d2), engine='xlsxwriter')

df.to_excel(writer, sheet_name='Sheet1')
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

header_format = workbook.add_format({
    'bold': True,
    'text_wrap': True,
    'valign': 'top',
    'fg_color': '#38C4F1',
    'font_color': 'FFFFFF',
    'border': 1})

for col_num, value in enumerate(df.columns.values):
    worksheet.write(0, col_num + 1, value, header_format)
    
writer.save()

Giving this output

enter image description here

OR, I can export both dataframes as separate sheets withno formatting using this code:

writer = pd.ExcelWriter('ExcelExample{}.xlsx'.format(d2), engine='xlsxwriter')

# Write each dataframe to a different worksheet.
df.to_excel(writer, sheet_name='Sheet1')
df2.to_excel(writer, sheet_name='Sheet2')

# Close the Pandas Excel writer and output the Excel file.
writer.save()

How can I apply the formatting to all sheets either manually or recursively?

xlsxwriter pandas excel python
3 Answers

There is no easy way in the XlsxWriter Library to do this and this has been an issue since 2014. (https://github.com/jmcnamara/XlsxWriter/issues/111) You can either go and work with the worksheet.write() approach (as you already did) or alternatively work with a helper function. I just found this library:

https://github.com/webermarcolivier/xlsxpandasformatter

The xlsxpandasformatter library provides a couple of helper function for XlsxWriter and pd.to_excel().
You might go and do something like this:

from xlsxpandasformatter import FormatedWorksheet
pd.formats.format.header_style = None

with pd.ExcelWriter("output_file.xlsx", engine="xlsxwriter", datetime_format="%B %d, %Y") as writer:
    workbook = writer.book

    header_format = workbook.add_format({
        'bold': True,
        'text_wrap': True,
        'text_v_align': 'top',
        'fg_color': '#38C4F1',
        'font_color': 'FFFFFF',
        'border': 1})
    
    all_df = [df1, df2]
    sheet_num = 1

    for df in all_df:
        sheetname = 'Sheet' + str(sheet_num)
        sheet_num += 1
        df.to_excel(writer, sheet_name = sheetname , index=False)

        worksheet = writer.sheets[sheetname]

        formattedWorksheet = FormatedWorksheet(worksheet, workbook, df)

        formattedWorksheet.format_header(headerFormat=header_format)
        formattedWorksheet.MoreMethodsThatYouCanApply()

        formattedWorksheet.apply_format_table()
user_104812690

The below code from this [post][1] allows me to achieve my aim of applying formatting to multiple sheets:

writer = pd.ExcelWriter('ExcelExample{}.xlsx'.format(d2), engine='xlsxwriter')
sheets_in_writer=['Sheet1','sheet2']

data_frame_for_writer=[df, df2]

for i,j in zip(data_frame_for_writer,sheets_in_writer):
    i.to_excel(writer,j,index=False)
    
#(max_row, max_col) = df.shape
#column_settings = [{'header': column} for column in df.columns]


### Assign WorkBook
workbook=writer.book
# Add a header format
header_format = workbook.add_format({'bold': True,'text_wrap': True,'size':10,
                                                      'valign': 'top','fg_color': '#c7e7ff','border': 1})


### Apply same format on each sheet being saved
for i,j in zip(data_frame_for_writer,sheets_in_writer):
    for col_num, value in enumerate(i.columns.values):
        writer.sheets[j].set_column(0, max_col - 1, 12)
#       writer.sheets[j].add_table(0, 0, max_row, max_col - 1, {'columns': column_settings,'autofilter': True})
        writer.sheets[j].write(0, col_num, value, header_format)
        writer.sheets[j].autofilter(0,0,0,i.shape[1]-1)
        writer.sheets[j].freeze_panes(1,0)
writer.save()


  [1]: https://stackoverflow.com/a/57350467/2781105
user_27811050

I used the following function to format the two tabs in Excel using Xlsxwriter. I have two dataframes and this worked exactly as I had intended.

def format_excel(writer):
""" Add Excel specific formatting to the workbooks for main report
"""
    workbook = writer.book
    worksheet = writer.sheets['Networks Selected']
    worksheet.set_zoom(120)
    worksheet.autofilter('$A$1:$G$1')
    worksheet2 = writer.sheets['No Primary Selected']
    worksheet2.set_zoom(120)
    worksheet2.autofilter('$A$1:$G$1')

    # Add some cell formats for columns with numbers
    format1 = workbook.add_format({'num_format': '00.00%'})
    format2 = workbook.add_format({'num_format': '0.00'})
    format3 = workbook.add_format({'num_format': 'mm/dd/yyyy'})

    # Set the column width and format. First Sheet
    worksheet.set_column('A:A', 15)
    worksheet.set_column('B:B', 25)
    worksheet.set_column('C:E', 30)
    worksheet.set_column('F:G', 15)
    worksheet.set_column('H:H', 15, format3)

    # Set the column width and format. Second Sheet
    worksheet2.set_column('A:A', 15)
    worksheet2.set_column('B:B', 25)
    worksheet2.set_column('C:G', 30)
    worksheet2.set_column('F:G', 15)
    worksheet2.set_column('H:H', 15, format3)

I then wrote the output like this and called the function:

writer = pd.ExcelWriter(f"Networks Selected List v2.xlsx", engine='xlsxwriter')
df_primary.to_excel(writer, sheet_name='Networks Selected', index = False)
df_no_primary.to_excel(writer, sheet_name='No Primary Selected', index = False)
format_excel(writer)
writer.save()
user_83157860
Related