Python 将格式应用于整行 Openpyxl
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/42980241/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Applying Format to Entire Row Openpyxl
提问by MattR
I have an Excel File that I want to format. The first row (excluding Headers so row2) should be red and italicized.
我有一个要格式化的 Excel 文件。第一行（不包括 Headers 所以 row2）应该是红色和斜体。
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 A24my loop will apply the formatting to it. I can fix this with a regular expression. Would that be the correct approach?
第一个缺点是，如果有更多的单元格，例如A24我的循环，则会对其应用格式。我可以用正则表达式解决这个问题。那会是正确的方法吗？
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 goodOpenpyxl documentation? I only found out about sheet.iter_rows()and cell.coordinateson Stack.
最终 - 是否有更好的方法将格式应用于整行？还。任何人都可以向我指出一些好的Openpyxl 文档的正确方向吗？我只在 Stack 上sheet.iter_rows()和cell.coordinatesStack 上发现过。
回答by Martin Evans
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
openpyxl 文档中描述了访问多个单元格：访问多个单元格
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.
该格式"2:2"枚举单行上的单元格。如果"2:3"使用，这将一次返回一行单元格，即第 2 行然后是第 3 行，因此需要一个额外的循环。
Alternatively, to use a NamedStyle:
或者，使用一个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)
