使用Python XlsxWriter按唯一ID组合为Excel行添加颜色标记

我正在用Python的XlsxWriter库生成Excel文件，希望依据唯一ID的组合为数据行添加颜色编码（效果类似条件格式的分组着色）。目前尝试了以下代码，但还没实现预期的低代码类条件格式效果：

fontcolor_indexlist = [data[0] for data in df_Index.select("index").filter(col("Invoice Calculation Date") == f'{InvoiceDate}').collect()]  # 从PySpark DataFrame获取索引

pandas_df = df_InvoiceDetails.drop("cellformat").toPandas()
 
writer = pd.ExcelWriter(f"/tmp/{DetailsFileName}", engine="xlsxwriter")

cell_format = workbook.add_format({'valign': 'vcenter', 'align': 'center','fg_color': '#D3D3D3'})

if row_num in fontcolor_indexlist:
   worksheet.write(row_num, col_num, value, cell_format)

解决方案 #

1. 先给唯一ID组合分配配色 #

首先从PySpark数据中提取所有唯一ID组合，给每个组合绑定专属颜色（可循环使用一组配色避免颜色过多）：

# 假设唯一ID列名为"unique_id"，按实际替换
unique_id_groups = df_Index.groupBy("unique_id").agg(collect_list("index").alias("row_indices")).collect()

# 定义循环使用的配色方案
color_palette = ['#D3D3D3', '#FFE4E1', '#E6F7FF', '#FFFACD', '#E8F5E9']

# 建立唯一ID到颜色的映射
id_color_map = {}
for idx, group in enumerate(unique_id_groups):
    id_color_map[group["unique_id"]] = color_palette[idx % len(color_palette)]

2. 低代码实现：用整行格式或条件格式 #

方式一：直接设置整行格式（适合离散行）

不需要逐单元格写入，直接给目标行设置整行格式：

# 将DataFrame写入Excel，不含索引
pandas_df.to_excel(writer, sheet_name='Sheet1', index=False)

# 获取workbook和worksheet对象
workbook = writer.book
worksheet = writer.sheets['Sheet1']

# 遍历每个ID组，给对应行设置格式
for group in unique_id_groups:
    row_indices = group["row_indices"]
    color = id_color_map[group["unique_id"]]
    # 创建格式对象
    row_format = workbook.add_format({'valign': 'vcenter', 'align': 'center', 'fg_color': color})
    
    # 转换为Excel行号（PySpark数据行索引从0开始，对应Excel第2行及以后）
    for idx in row_indices:
        excel_row = idx + 2
        # 设置整行格式，高度保持默认
        worksheet.set_row(excel_row, None, row_format)

# 保存并关闭文件
writer.close()

方式二：批量条件格式（适合连续/离散行，更高效）

利用XlsxWriter的条件格式功能，直接匹配ID值批量着色整行：

# 确保pandas_df中保留唯一ID列（列名"unique_id"）
pandas_df.to_excel(writer, sheet_name='Sheet1', index=False)
workbook = writer.book
worksheet = writer.sheets['Sheet1']

total_rows = len(pandas_df) + 1  # 包含表头的总行数
total_cols = len(pandas_df.columns)

# 遍历每个ID，设置条件格式规则
for unique_id, color in id_color_map.items():
    fmt = workbook.add_format({'valign': 'vcenter', 'align': 'center', 'fg_color': color})
    # 条件范围：数据行（第2行到最后一行）、所有列
    worksheet.conditional_format(1, 0, total_rows-1, total_cols-1, {
        'type': 'formula',
        # 假设唯一ID在A列，根据实际列调整列号（A=0, B=1...）
        'criteria': f'=${chr(65 + 0)}{2}="{unique_id}"',
        'format': fmt
    })

writer.close()

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