基于单元格值的 Pandas XLSX 导出带背景颜色

此示例脚本使用 openpyxl 将 False 单元格的背景颜色设置为红色，将 True 单元格设置为绿色。注意我们基于之前文章如何将 Pandas bool 列以 True/False 而不是 1/0 写入 XLSX中概述的方法将布尔值转换为 "True" 或 "False" 字符串。有关如何在 OpenPyXL 中仅设置背景颜色的更多详细信息，请参见我们关于如何在 OpenPyXL 中设置单元格背景颜色的文章。

pandas_color_example.py

import pandas as pd
from openpyxl.styles import PatternFill

df = pd.DataFrame([
    {"a": True},
    {"a": False},
    {"a": True},
    {"a": False},
    {"a": False},
])
df["a"] = df["a"].map({True: "True", False: "False"})
with pd.ExcelWriter("out.xlsx", engine="openpyxl") as writer:
    sheet_name = "Bool"
    # 导出 DataFrame 内容
    df.to_excel(writer, sheet_name=sheet_name)
    # 根据单元格值设置背景颜色
    sheet = writer.sheets[sheet_name]
    for cell, in sheet[f'B2:B{len(df) + 1}']: # Skip header row, process as many rows as there are DataFrames
        value = df["a"].iloc[cell.row - 2] # value is "True" or "False"
        cell.fill = PatternFill("solid", start_color=("5cb800" if value == "True" else 'ff2800'))

import pandas as pd
from openpyxl.styles import PatternFill

df = pd.DataFrame([
    {"a": True},
    {"a": False},
    {"a": True},
    {"a": False},
    {"a": False},
])
df["a"] = df["a"].map({True: "True", False: "False"})
with pd.ExcelWriter("out.xlsx", engine="openpyxl") as writer:
    sheet_name = "Bool"
    # 导出 DataFrame 内容
    df.to_excel(writer, sheet_name=sheet_name)
    # 根据单元格值设置背景颜色
    sheet = writer.sheets[sheet_name]
    for cell, in sheet[f'B2:B{len(df) + 1}']: # Skip header row, process as many rows as there are DataFrames
        value = df["a"].iloc[cell.row - 2] # value is "True" or "False"
        cell.fill = PatternFill("solid", start_color=("5cb800" if value == "True" else 'ff2800'))

此脚本的输出如下所示：

True 单元格绿色背景和 False 单元格红色背景的 Pandas XLSX 导出

Check out similar posts by category: OpenPyXL, Python

If this post helped you, please consider buying me a coffee or donating via PayPal to support research & publishing of new posts on TechOverflow

Buy me a coffee