How do I format all the cells in an excel to a single style using openpyxl?

Question

I'm using python package openpyxl to write data to excel sheet. I have an excel file with three columns. I'm going to dump thousands of rows of data into it. I want headers in a specific format in one style and all the data in other style. I don't want to iterate over all the cells and set the style specifically, because whenever I add more data, I'll have to set styles for them too. So I want to create an excel template in one shot, by applying 'style1' for headers row and 'style2' for the rest of the rows, so I wont't have to bother about their styles and just dump data. Is there anyway to set predefine to predefine all the styles into sheet ??

Answer 1

Providing an example of how you could format a row with a particular format and then the rest of the cells with a different format.

Please note that you will have to call a saved workbook for the styles I've used to work. That is, in this example I create a workbook, save it, then load it and format the cells, and then save again.

There are a bunch of examples of different formats you can use in the openpyxl docs section on styles.

This is a fully reproducible example using data from pandas_datareader module.

import pandas as pd
from openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl.styles import Font
from openpyxl.utils.dataframe import dataframe_to_rows
import pandas_datareader.data as web #optional for example only

#pull in your own dataframe and remove the next two lines if you want to
df = web.DataReader("AMZN", 'google')
df = df.tail(180)

wb = Workbook()
ws = wb.active

for r in dataframe_to_rows(df, index=False, header=True):
    ws.append(r)

wb.save("test.xlsx")

wb = load_workbook('test.xlsx')
ws = wb['Sheet']
style_1 = Font(color='0000FF00', bold=True, underline="single", size=15)
style_2 = Font(color='000000FF', italic=True, size=10)

for cell in ws["1:1"]:
    cell.font = style_1

for row in ws.iter_rows(min_row=2, max_col=5, max_row=len(df)+2):
    for cell in row:
        cell.font = style_2

#commented out my original snippet - preferred method recommended by Charlie Clark is ws.iter_rows() - above
'''

for i in range(2, len(df)+2):
    current = "%d:%d" % (i, i)
    for cell in ws[current]:
        cell.font = style_2
'''

wb.save("test.xlsx")

**UPDATE (Based on a comment by the OP asking about formatting cells appended to existing worksheet):

If you're adding more to this sheet later you can do the formatting at the same time for the new cells. Add the snippet below to the original answer.

wb = load_workbook('test.xlsx')
ws = wb['Sheet']
ws['A182'] = "New String"

for cell in ws["182:182"]:
    cell.font = style_1
wb.save("test.xlsx")