I have an Excel File that I want to format. The first row (excluding Headers so row2) should be red and italicized.
If you want to apply styles to entire rows and columns then you must apply the style to each cell yourself
I personally thinks this stinks... Here is my workaround:
import openpyxl from openpyxl.styles import NamedStyle from openpyxl import load_workbook from openpyxl.styles.colors import RED from openpyxl.styles import Font # I normally import a lot of stuff... I'll also take suggestions here. file = 'MY_PATH' wb = load_workbook(filename=file) sheet = wb.get_sheet_by_name('Output') for row in sheet.iter_rows(): for cell in row: if '2' in cell.coordinate: # using str() on cell.coordinate to use it in sheet['Cell_here'] sheet[str(cell.coordinate)].font = Font(color='00FF0000', italic=True) wb.save(filename=file)
The first downside is that if there are more cells such as
A24 my loop will apply the formatting to it. I can fix this with a regular expression. Would that be the correct approach?
Ultimately- is there a better way to apply a format to the entire row? Also. Can anyone point me in the right direction to some good Openpyxl documentation? I only found out about
cell.coordinates on Stack.
There is no need to iterate on all of the rows if you only intend to change the colour for the second row, you can just iterate over a single row as follows:
import openpyxl from openpyxl import load_workbook from openpyxl.styles import Font file = 'input.xlsx' wb = load_workbook(filename=file) ws = wb['Output'] red_font = Font(color='00FF0000', italic=True) # Enumerate the cells in the second row for cell in ws["2:2"]: cell.font = red_font wb.save(filename=file)
Giving you something like:
Accessing multiple cells is described in the openpyxl docs: Accessing many cells
"2:2" enumerates the cells over a single row. If
"2:3" is used, this will return the cells a row at a time, i.e. row 2 then row 3 and so would need an additional loop.
Alternatively, to use a
import openpyxl from openpyxl import load_workbook from openpyxl.styles import Font, NamedStyle file = 'input.xlsx' wb = load_workbook(filename=file) ws = wb['Output'] # Create a NamedStyle (if not already defined) if 'red_italic' not in wb.named_styles: red_italic = NamedStyle(name="red_italic") red_italic.font = Font(color='00FF0000', italic=True) wb.add_named_style(red_italic) # Enumerate the cells in the second row for cell in ws["2:2"]: cell.style = 'red_italic' wb.save(filename=file)