Use a method/function to format xlsx writer

Question

I am trying to style and write excel files dynamically. Here is my code

import pandas as pd
import copy

class OutputWriter(object):

    def __init__(self, fmt_func, sheet_name='data'):
        '''
        Initializing...
        '''
        # NOTICE: Initialising with path set None since I do not know path yet
        wrt = pd.ExcelWriter(None, engine='xlsxwriter')
        self._writer = fmt_func(wrt, sheet_name)
        self._sheet_name = sheet_name

    def save(self, df, o_path):
        '''
        Save the file to a path
        '''
        # setting path in writer before saving
        self._writer.path = o_path
        df.to_excel(self._writer, sheet_name=self._sheet_name)
        self._writer.save()

# Change first row color to blue
def fmt_func_blue(wrt, sheet_name):
    # ERROR Cannot clone `wrt` path is not set
    writer = copy.deepcopy(wrt)
    sheet = writer.sheets[sheet_name]
    workbook = writer.book

    # Proceed to color first row blue
    header_fmt = workbook.add_format({
        'text_wrap': True,
        'bg_color': '#191970',
        'font_color': '#FFFFFF',
    })
    header_fmt.set_align('center')
    header_fmt.set_align('vcenter')
    sheet.set_row(0, None, header_fmt)
    return writer

# Change first row color to red 
def fmt_func_red(wrt, sheet_name):
    writer = copy.deepcopy(wrt)
    # I haven't saved the excel file so there are no sheets
    sheet = writer.sheets[sheet_name]
    workbook = writer.book

    # Proceed to color first row red
    header_fmt = workbook.add_format({
        'text_wrap': True,
        'bg_color': '#FF2200',
        'font_color': '#FFFFFF',
    })
    header_fmt.set_align('center')
    header_fmt.set_align('vcenter')
    sheet.set_row(0, None, header_fmt)
    return writer

writer_red = OutputWriter(fmt_func_red, sheet_name='red')
writer_blue = OutputWriter(fmt_func_blue, sheet_name='blue')

I have two issues:

1) I can't clone the xlwriter object in my styling function

2) There are no sheets in my workbook at the time I try to style the excel files.

Is there any way I can make this work?

Answer 1

1) I can't clone the xlwriter object in my styling function

One wouldn't be able to clone a workbook that doesn't exist yet(or just created with nothing worth in it, here worthiness is checked via if path exts). Let's act on the error that is being thrown, change:-

def fmt_func_blue(wrt, sheet_name):
    # ERROR Cannot clone `wrt` path is not set
    writer = copy.deepcopy(wrt)

to

def fmt_func_blue(wrt, sheet_name):
    # ERROR Cannot clone `wrt` path is not set
    writer=wrt
    if writer.book.filename:
      writer = copy.deepcopy(wrt)
    else:
      # Any changes to the new workbook will be reflected on the new 
      # workbook of the writer(created in init) not on a copy.
      # If you want your class init writer untouched, 
      # and want to format a completely new instance of workbook, recreate 
      # a new writer and assign it to the local writer here.
      print('Warning: Working with new workbook')#or use log

This should take care of it, remember it is ok to not clone a perfectly empty workbook. But if for some design you need to do so, then create a clone yourself, that is create a perfectly empty workbook yourself like you did in init.

2) There are no sheets in my workbook at the time I try to style the excel files.

Well one cannot format sheets that do not exist yet. So just create one and populate with dataframe(or any other) data later into the already formatted sheet. I suggest a change as below:-

# I haven't saved the excel file so there are no sheets
sheet = writer.sheets[sheet_name]

to

sheet=None
if sheet_name in writer.sheets:
  sheet = writer.sheets[sheet_name]
else:
  print('Warning: Creating new sheet for formatting <'+sheet_name+'>') # or use log
  sheet= workbook.add_worksheet(sheet_name)

Working code is here. It will take sometime to install the libs when you to try to run it.I made a few changes for fail safety and altered methods a bit in my example code.

Output from my example code is as below:-

Warning: Working with new workbook
Warning: Creating new sheet for formatting <red>
Warning: Working with new workbook
Warning: Creating new sheet for formatting <blue>