Insert pandas DataFrame into existing excel worksheet with styling

Question

I've seen answers as to how to add a pandas DataFrame into an existing worksheet using openpyxl as shown below:

from openpyxl import load_workbook, Workbook
import pandas as pd

df = pd.DataFrame(data=["20-01-2018",4,9,16,25,36],columns=["Date","A","B","C","D","E"])
path = 'filepath.xlsx'

writer = pd.ExcelWriter(path, engine='openpyxl')
writer.book = load_workbook(path)
writer.sheets = dict((ws.title,ws) for ws in writer.book.worksheets)

df.to_excel(writer,sheet_name="Sheet1", startrow=2,index=False, header=False)
writer.save()

However, I need to set a highlight color to the background data. Is there a way to do this without changing the dataframe into a list - trying to maintain the date format too.

Thanks

Answer 1

You can create a function to do the highlighting in the cells you desire

def highlight_style():
    # provide your criteria for highlighting the cells here
    return ['background-color: red']

And then apply your highlighting function to your dataframe...

df.style.apply(highlight_style)

After this when you write it to an excel it should work as you want =)

Answer 2

I sorted it thanks to help from Andre. You can export the results as such:

df.style.set_properties(**{'background-color':'red'}).to_excel(writer,sheet_name="Sheet1", startrow=2,index=False, header=False)
writer.save()

Thanks!