根据唯一行在xlxswriter中为行添加颜色 python

Question

我正在使用Python的xlxswriter库生成xlxs文件。我想根据唯一ID的组合生成带有颜色编码的行。下面是我想要的输出结果：

当前逻辑：

fontcolor_indexlist = [data[0] for data in df_Index.select("index").filter(col("Invoice Calculation Date") == f'{InvoiceDate}').collect()]  #Getting index from 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)

我尝试了上面的代码，想用更少的代码实现xlxs文件的生成，效果类似于条件格式化。

Answer 1

假设你想要让每个 "id" 或块的颜色 交替，我会使用 groupby 和 write_row 方法：

# for convenience, i'm using df instead of pandas_df

D_FMT = {"valign": "vcenter", "align": "center"}
C_FMT = {"fg_color": "#D3D3D3"}
H_FMT = {"bold": 1}

SR, SC = 0, 0 # start row/col

with pd.ExcelWriter(f"/tmp/{DetailsFileName}", engine="xlsxwriter") as wr:
    wb = wr.book; ws = wb.add_worksheet("Sheet1")
    
    ws.write_row(SR, SC, df.columns, wb.add_format(H_FMT))

    for n, (_, g)in enumerate(df.groupby("id", sort=False)):
        for idx, row in zip(g.index, g.to_numpy()):
            _fmt = D_FMT if not n%2 else {**D_FMT, **C_FMT}
            ws.write_row(idx+1, SC, row, wb.add_format(_fmt))

使用的输入：

df = pd.DataFrame(
    {
        "id": [2, 2, 1, 1, 1, 4, 5, 5, 6, 6, 7, 8],
        "No of times": [1, 2, 23, 12, 0, 5, 4, 1, 2, 2, 48, 10],
    }
)