0
1.2K
VisualizaçõesApplying Format to Entire Row Openpyxl
I have an Excel File that I want to format. The first row (excluding Headers so row2) should be red and italicized.
the Openpyxl Documentation states:
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 sheet.iter_rows() and cell.coordinates on Stack.
1 Respostas
Responde à pergunta0
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
The format "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 NamedStyle:
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)