outputting a coloured excel file python

Question

I've been asked to create a program for the business I work for that will take an excel file, change any filled-out rows to have a pink background, and then save the file. I'm really having trouble with this, so if anyone has any advice or basic functions I can base my code on, that would be greatly appreciated.

Answer 1

If you already have an Excel workbook, you can read it in and make changes right there. However, until you get the hang of this, it might be preferable to maintain the original and make a new one that you can discard without losing data.

It is a multi-step process:

Create the workbook and save it into a variable:

workbook = xlsxwriter.Workbook('sample.xlsx')

Add a worksheet to the workbook

worksheet = workbook.add_worksheet()

This worksheet now lives as a sheet in the actual Excel workbook as well as an object in Python's memory that is referenced by the worksheet.

You must now enter some information on that blank page:

worksheet.write(pandas_df) # this is where you drop in the data

Set a format to apply to your selection:

formats = workbook.add_format({'bg_color': '#F9DCD6',
                               'font_color': '#095A03'})

The next part is what you need to experiment with:

worksheet.conditional_format('A1:Z100', {'type': 'cell',
                                     'criteria': '<your criteria>',
                                     'value': <your value>,
                                     'format': formats})

You can look up all the ways to identify different sorts of data here: xlsxwriter

Within this, there should be some way to create the scheme you are seeking.