pandas 将状态设置为通过,如果绿色,则在比较两个excel时失败

lo8azlld  于 2023-05-21  发布在  其他
关注(0)|答案(1)|浏览(109)

我想根据单元格颜色在Excel中追加一列(状态)。
如果该行的所有单元格均为绿色,则状态应为“通过”,否则为“未通过”。
我正在使用下面的代码

import pandas as pd

from openpyxl import load_workbook

filename = "Excel_Diff.xlsx"

wb = load_workbook(filename)

sheet_name = "Sheet1"

ws = wb[sheet_name]

data = ws.values

columns = next(data)[1:]

df = pd.DataFrame(data, columns=columns)

status_column = []

for row in ws.iter_rows(values_only=True):

    green = all(cell.fill.fgColor.rgb == "FF00FF00" for cell in row)
    
    status_column.append("pass" if green else "fail")

df["status"] = status_column

但这样不行

hjzp0vay

hjzp0vay1#

在循环中,使用ws.iter_rows(values_only=True)。这个kwarg意味着只取所有单元格的值,所以它们是字符串。如果你删除它,你可以看到返回的内容的差异:

[row for row in ws.iter_rows(values_only=True)]
#Out[]: 
#[('Col1', 'Col2', 'Col3', 'Col4'),
# ('Red', 1, 0.95396020076544, 0.29105030184105374),
# ('Green', 2, 0.35323937340945766, 0.32459389966476326),
# ('Red', 3, 0.3648347692956413, 0.6750402635446109),
# ('Blue', 4, 0.562487643312757, 0.35390177206328044),
# ('Red', 5, 0.8456327417471485, 0.08121976975519996)]

[row for row in ws.iter_rows()]
#Out[]: 
#[(<Cell 'Sheet1'.A1>, <Cell 'Sheet1'.B1>, <Cell 'Sheet1'.C1>, <Cell 'Sheet1'.D1>),
# (<Cell 'Sheet1'.A2>, <Cell 'Sheet1'.B2>, <Cell 'Sheet1'.C2>, <Cell 'Sheet1'.D2>),
# (<Cell 'Sheet1'.A3>, <Cell 'Sheet1'.B3>, <Cell 'Sheet1'.C3>, <Cell 'Sheet1'.D3>),
# (<Cell 'Sheet1'.A4>, <Cell 'Sheet1'.B4>, <Cell 'Sheet1'.C4>, <Cell 'Sheet1'.D4>),
# (<Cell 'Sheet1'.A5>, <Cell 'Sheet1'.B5>, <Cell 'Sheet1'.C5>, <Cell 'Sheet1'.D5>),
# (<Cell 'Sheet1'.A6>, <Cell 'Sheet1'.B6>, <Cell 'Sheet1'.C6>, <Cell 'Sheet1'.D6>)]

然后,由于您现在有***单元格***而不是***单元格值***,因此可以使用代码:

status_column = []
for row in ws.iter_rows():
    green = all(cell.fill.fgColor.rgb == "FF00FF00" for cell in row)
    status_column.append("pass" if green else "fail")
status_column
#Out[]: ['fail', 'fail', 'pass', 'fail', 'fail', 'fail']

相关问题