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?

Please provide a reproducible code. This won't work. The `fmt_func_red` uses `self` inside but it is not passed in function. Please create a reproducible version which one can directly run — Tarun Lalwani, Apr 23 '18 at 12:38
Still there are lots of errors. Please fix the code and then post when the issues you mentioned in your question are remaining. Run the code before updating — Tarun Lalwani, Apr 23 '18 at 15:23
Errors: Imports are missing, probably "import copy" + "import pandas as pd"; class name has a typo; line 17 and 19 are butchered; there is no sample dataframe to be written to the file; and save() is also never called; Jesus. — Günther Eberl, Apr 25 '18 at 21:56
@GüntherEberl , why would save() need to be called? The question is unrelated to save() — cowlinator, Apr 28 '18 at 02:20
@GüntherEberl I made the edits you suggested. Although, imports were fairly implicit, I think. — Ajit, Apr 28 '18 at 09:52
@cowlinator When using xlsxwriter as writer you just have one shot of getting your data into the worksheet, there is no altering already present worksheets. The user tries to apply formatting onto a worksheet before dumping the dataframe into it. This approach will likely not work. You just notice on save though. — Günther Eberl, May 01 '18 at 19:46

score 2 · Answer 1 · answered Apr 30 '18 at 16:48

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>

The code you posted works - as long as you don't try to save a dataframe to it using the save() function: Exception: Sheetname 'red', with case ignored, is already in use. — Günther Eberl, May 01 '18 at 19:43
For that error please refer to https://stackoverflow.com/questions/20219254/how-to-write-to-an-existing-excel-file-without-overwriting-data-using-pandas . Looks like one line before `to_excel` would work the trick. `self._writer.sheets=dict((ws.title, ws) for ws in self._writer.book.worksheets)` — kaza, May 01 '18 at 23:05

Use a method/function to format xlsx writer

1 Answers1