尝试使用 openpyxl 设置大量行的样式，有没有'faster'的方法？

Question

我有一个函数，用于根据与openpyxl匹配的标准检查电子表格的行并为其设置样式。虽然这对于几乎所有较小的工作表都是有效的，但当我逐渐使用大型行集时，速度大大减慢了。

我正在努力寻找加快速度的方法，希望有人能帮我弄清楚我错过了什么。

我认为我的问题是我循环遍历每一行，但然后循环遍历目标行的每个单元格以匹配特定的列值。虽然我不确定我能做些什么来解决这个问题(如下所示):

for row in sheet.iter_rows():
for cell in row:
cell.alignment = Alignment(wrapText=True)
if row[type_row].value in group1:

函数的完整代码和我实际用于格式化单元格的另一个函数:

import openpyxl as opxl
from openpyxl.styles import Font, Border, Side, PatternFill, Alignment, NamedStyle
def format_cell(cell, fill, font, border):
cell.fill = fill
cell.font = font
cell.border = border
def set_style_base(sheet, type_row, col_for_other, col_for_step):
group1 = ['Temp1', 'Temp2', 'Temp3']
group2 = ['ACTIVITY']
group3 = ['ACTIVITY2']
group4 = ['OPEN']
group5 = ['BREAK']
group6 = ['WARNING']
group7 = ['DIAGNOSTIC']
pass_start_fill = PatternFill(start_color='00FF00', end_color='00FF00', fill_type='solid')
pass_end_fill = PatternFill(start_color='99FF99', end_color='99FF99', fill_type='solid')
error_start_fill = PatternFill(start_color='FF0000', end_color='FF0000', fill_type='solid')
error_end_fill = PatternFill(start_color='FF6666', end_color='FF6666', fill_type='solid')
fail_start_fill = PatternFill(start_color='FF8000', end_color='FF8000', fill_type='solid')
fail_end_fill = PatternFill(start_color='FFB266', end_color='FFB266', fill_type='solid')
group2_fill = PatternFill(start_color='FFA500', end_color='FFA500', fill_type='solid')
group3_fill = PatternFill(start_color='000000', end_color='000000', fill_type='solid')
group4_fill = PatternFill(start_color='00BFFF', end_color='00BFFF', fill_type='solid')
group5_fill = PatternFill(start_color='800080', end_color='800080', fill_type='solid')
group6_fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')
thick = Side(border_style="thick")
border_thick = Border(top=thick, left=thick, right=thick, bottom=thick)
bold_font = Font(bold=True)
def_font = Font(bold=False)
for row in sheet.iter_rows():
for cell in row:
cell.alignment = Alignment(wrapText=True)
if row[type_row].value in group1:
if row[col_for_step].data_type == "s":
if 'START:' in row[col_for_step].value:
if row[col_for_other].data_type == "s":
if row[col_for_other].value == 'PASS':
format_cell(cell, pass_start_fill, bold_font, border_thick)
if row[col_for_other].value == 'FAIL':
format_cell(cell, fail_start_fill, bold_font, border_thick)
if row[col_for_other].value == 'ERROR':
format_cell(cell, error_start_fill, bold_font, border_thick)
if 'END:' in row[col_for_step].value:
if row[col_for_other].data_type == "s":
if row[col_for_other].value == 'PASS':
format_cell(cell, pass_end_fill, bold_font, border_thick)
if row[col_for_other].value == 'FAIL':
format_cell(cell, fail_end_fill, bold_font, border_thick)
if row[col_for_other].value == 'ERROR':
format_cell(cell, error_end_fill, bold_font, border_thick)
if row[type_row].value in group2:
format_cell(cell, group2_fill, bold_font, border_thick)
if row[type_row].value in group3:
format_cell(cell, group3_fill, def_font, border_thick)
cell.font = Font(color = 'FFA500') #Font color needs to be set
if row[type_row].value in group4:
format_cell(cell, group4_fill, bold_font, border_thick)
if row[type_row].value in group5:
format_cell(cell, group5_fill, bold_font, border_thick)
if row[type_row].value in group6:
format_cell(cell, group6_fill, def_font, border_thick)
cell.font = Font(color = '000000') #Font color needs to be set
if row[type_row].value in group7: #special case as 'DIAGNOSTIC' has no fill
cell.border = Border(top=thick, left=thick, right=thick, bottom=thick)
cell.font = Font(bold=True)
if row[type_row].value not in [group1, group2, group3, group4, group5, group6, group7]:
if row[col_for_step].data_type == "s":
if 'START:' in row[col_for_step].value:
if row[col_for_other].data_type == "s":
if row[col_for_other].value == 'PASS':
format_cell(cell, pass_start_fill, bold_font, border_thick)
if row[col_for_other].value == 'FAIL':
format_cell(cell, fail_start_fill, bold_font, border_thick)
if row[col_for_other].value == 'ERROR':
format_cell(cell, error_start_fill, bold_font, border_thick)
if 'END:' in row[col_for_step].value:
if row[col_for_other].data_type == "s":
if row[col_for_other].value == 'PASS':
format_cell(cell, pass_end_fill, bold_font, border_thick)
if row[col_for_other].value == 'FAIL':
format_cell(cell, fail_end_fill, bold_font, border_thick)
if row[col_for_other].value == 'ERROR':
format_cell(cell, error_end_fill, bold_font, border_thick)

任何方向或建议将是惊人的。由于

Answer 1

VBA也不以速度著称。在我看来，会有一个问题，处理相同的细胞多次，但结构是不清楚的。调度在这里会有一点帮助。看起来您每行只触摸两个单元格，如果是这种情况，我会使用它们作为min_col和max_col。

这将给你类似这样的东西。

conditions = {"PASS": {"start": pass_start_fill, "end": pass_end_fill}, "FAIL": {"start": fail_start_fill, "end", fail_end_fill}
for row in ws.iter_rows(min_col=col_for_step, max_col=col_for_other):
step, other = row[0], row[-1]
if step.value == "START":
format_cell(other, fill=conditions[other.value]["start"], font=bold_font, border=border_thick])
elif step.value == "END":
format_cell(other, fill=conditions[other.value]["end"], font=bold_font, border=border_thick])

这需要扩展，但如果你能简化代码，它应该更容易识别瓶颈。