如何在将DataFrame导出至Excel前为指定列特定值设置加粗格式？

我需要用Python为以下DataFrame中Product列的Fruits和Vegetables值设置加粗格式，导出至Excel后这些值需保持加粗状态，恳请协助！

给出的DataFrame：

Code    Product     Limit   Value
0   3A68185     Fruits  0.6     0.000000
1   3A68185     Apple   0.6     0.000000
3   3B22979     Fruits  3.5     0.430146
2   3B22979     Apple   3.5     0.430146
4   3B22979     Orange  0.0     0.000000
6   3C67260     Fruits  3.0     1.123774
5   3C67260     Apple   3.0     1.123774
7   3C71601     Vegetables  15.0    0.000000
8   3C71601     Tomato  15.0    0.000000
14  3C78910     Fruits  2.0     1.187282
15  3C78910     Apple   2.0     1.187282
16  3C82861     Fruits  64.0    0.560864
17  3C82861     Apple   15.0    0.000000
18  3C82861     Orange  49.0    0.560864
21  3D11357     Vegetables  26.0    0.000000
19  3D11357     Tomato  25.5    0.000000
20  3D11357     Onion   0.5     0.000000
23  3D51126     Vegetables  15.0    0.000000
24  3D51126     Tomato  14.5    0.000000
22  3D51126     Onion   0.5     0.000000
26  3E20062     Vegetables  1.0     0.000000
25  3E20062     Onion   1.0     0.000000
10  3E45212     Fruits  5.0     0.000000
9   3E45212     Apple   5.0     0.000000
13  3E45212     Vegetables  36.0    0.000000
11  3E45212     Tomato  35.5    0.000000
12  3E45212     Onion   0.5     0.000000

预期效果：导出Excel后，Product列的Fruits和Vegetables显示为加粗格式。

实现思路 #

Excel无法识别文本中的HTML加粗标签，必须通过设置单元格字体样式实现加粗。这里使用pandas处理DataFrame，openpyxl操作Excel文件并设置样式。

代码实现 #

1. 安装依赖库（未安装时执行）：

pip install pandas openpyxl

2. 完整代码：

import pandas as pd
from openpyxl.styles import Font
from openpyxl import load_workbook

# 构造示例DataFrame（实际使用时替换为你的DataFrame）
data = [
    ["3A68185", "Fruits", 0.6, 0.000000],
    ["3A68185", "Apple", 0.6, 0.000000],
    ["3B22979", "Fruits", 3.5, 0.430146],
    ["3B22979", "Apple", 3.5, 0.430146],
    ["3B22979", "Orange", 0.0, 0.000000],
    ["3C67260", "Fruits", 3.0, 1.123774],
    ["3C67260", "Apple", 3.0, 1.123774],
    ["3C71601", "Vegetables", 15.0, 0.000000],
    ["3C71601", "Tomato", 15.0, 0.000000],
    ["3C78910", "Fruits", 2.0, 1.187282],
    ["3C78910", "Apple", 2.0, 1.187282],
    ["3C82861", "Fruits", 64.0, 0.560864],
    ["3C82861", "Apple", 15.0, 0.000000],
    ["3C82861", "Orange", 49.0, 0.560864],
    ["3D11357", "Vegetables", 26.0, 0.000000],
    ["3D11357", "Tomato", 25.5, 0.000000],
    ["3D11357", "Onion", 0.5, 0.000000],
    ["3D51126", "Vegetables", 15.0, 0.000000],
    ["3D51126", "Tomato", 14.5, 0.000000],
    ["3D51126", "Onion", 0.5, 0.000000],
    ["3E20062", "Vegetables", 1.0, 0.000000],
    ["3E20062", "Onion", 1.0, 0.000000],
    ["3E45212", "Fruits", 5.0, 0.000000],
    ["3E45212", "Apple", 5.0, 0.000000],
    ["3E45212", "Vegetables", 36.0, 0.000000],
    ["3E45212", "Tomato", 35.5, 0.000000],
    ["3E45212", "Onion", 0.5, 0.000000]
]
df = pd.DataFrame(data, columns=["Code", "Product", "Limit", "Value"])

# 导出DataFrame到Excel
excel_path = "output.xlsx"
df.to_excel(excel_path, index=False, engine="openpyxl")

# 加载Excel文件并设置样式
wb = load_workbook(excel_path)
ws = wb.active

# 定义加粗字体
bold_font = Font(bold=True)

# 定位Product列
product_col = None
for col in ws.iter_cols(min_row=1, max_row=1):
    if col[0].value == "Product":
        product_col = col[0].column_letter
        break

# 遍历单元格设置加粗
for row in range(2, ws.max_row + 1):
    cell = ws[f"{product_col}{row}"]
    if cell.value in ["Fruits", "Vegetables"]:
        cell.font = bold_font

# 保存修改后的文件
wb.save(excel_path)
print(f"文件已保存至 {excel_path}")

代码说明 #

• 先将DataFrame导出为Excel文件，指定openpyxl为引擎。
• 加载文件后，找到Product列的位置。
• 遍历Product列的所有单元格，当值为Fruits或Vegetables时，设置该单元格字体为加粗。
• 最后保存文件，打开后即可看到目标值已加粗显示。

内容的提问来源于stack exchange，提问作者SGC