Formatting multiple worksheets using xlsxwriter

Question

How to copy the same formatting to different sheets of the same Excel file using the xlsxwriter library in Python?

The code I tried is:

import xlsxwriter

import pandas as pd
import numpy as np

from xlsxwriter.utility import xl_rowcol_to_cell

df = pd.DataFrame()
df = pd.read_excel('TrendAnalysis.xlsx')



# Create a Pandas Excel writer using XlsxWriter as the engine.
# Save the unformatted results
writer_orig = pd.ExcelWriter('TrendAnalysis.xlsx', engine='xlsxwriter')
df.to_excel(writer_orig, index=True)
writer_orig.save()

work = ["isu-wise", "ISU-BFS", "ISU-CPG", "ISU-ER", "ISU-GE", "ISU-GOV Domestic", "ISU-GOV Overseas", "ISU-HC"]
# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('TrendAnalysis.xlsx', engine='xlsxwriter')
for i in range(0,len(work)):
    df.to_excel(writer, index=True, sheet_name=work[i])

    # Get access to the workbook and sheet
    workbook = writer.book
    worksheet = writer.sheets[work[i]]

    print worksheet
    # We need the number of rows in order to place the totals
    number_rows = len(df.index)

    # Total formatting
    total_fmt = workbook.add_format({'align': 'right', 'num_format': '#,##0',
                                    'bold': True, 'bottom':6})

    # Add total rows
    for column in range(1,3):
        # Determine where we will place the formula
        cell_location = xl_rowcol_to_cell(number_rows+1, column)
        # Get the range to use for the sum formula
        start_range = xl_rowcol_to_cell(1, column)
        end_range = xl_rowcol_to_cell(number_rows, column)
        # Construct and write the formula
        formula = "=SUM({:s}:{:s})".format(start_range, end_range)
        worksheet.write_formula(cell_location, formula, total_fmt)

    # Add a total label
    worksheet.write_string(number_rows+1, 0, "Total",total_fmt)
    i+=1

writer.save()
workbook.close()

It creates the same sheet multiple times. Doesn't navigate and the sheets after the first one of the workbook. The code is error free and does the required formatting otherwise.

Answer 1

It may be too late but here is what I have done for this. In my example I have 2 DataFrame which I would like to freeze the pane, add filter to each column, and format the header of both sheets which are being saved on the same excel file.

writer = pd.ExcelWriter(path_of_excel_file+'output.xlsx')
sheets_in_writer=['Sheet1','sheet2']
data_frame_for_writer=[df1, df2]

for i,j in zip(data_frame_for_writer,sheets_in_writer):
    i.to_excel(writer,j,index=False)

### 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].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()