Python – Insert a pandas DataFrame into an existing Excel worksheet with styles

I’ve seen an answer on how to add pandas DataFrame to an existing worksheet using openpyxl as follows:

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 the highlight color for the background data. Is there a way to do this without changing the data frame to a list – try maintaining the date format as well.

Thanks

You can create a function to highlight in the cells you want

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

Then apply the highlighting feature to the data frame….

df.style.apply(highlight_style)

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