将格式应用于整个行Openpyxl [英] Applying Format to Entire Row Openpyxl

问题描述

我有一个要格式化的Excel文件.第一行(不包括标题"，因此第二行)应为红色，并以斜体显示. .

I have an Excel File that I want to format. The first row (excluding Headers so row2) should be red and italicized.

Openpyxl文档状态:

如果要将样式应用于整个行和整个列，则必须自己将样式应用于每个单元格

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)

第一个缺点是，如果有更多单元格(例如A24)，我的循环将对其应用格式.我可以用正则表达式解决此问题.那是正确的方法吗?

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?

最终-是否有更好的方法将格式应用于整个行?另外.谁能向我指出一些正确的Openpyxl文档的正确方向?我只在堆栈上找到有关sheet.iter_rows()和cell.coordinates的信息.

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.

推荐答案

如果只打算更改第二行的颜色，则无需对所有行进行迭代，您可以对单个行进行迭代，如下所示:如下:

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)

给你类似的东西

openpyxl文档中描述了访问多个单元格:访问很多细胞

Accessing multiple cells is described in the openpyxl docs: Accessing many cells

格式"2:2"枚举单行中的单元格.如果使用"2:3"，这将一次返回一行单元格，即第2行然后是第3行，因此将需要一个附加循环.

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.

或者，使用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)

这篇关于将格式应用于整个行Openpyxl的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！