Adding row colors based on unique rows in xlxswriter python

Question

I was generating xlxs file from python python xlxswriter package. I want to generate rows with color coded based on the combination unique id. Please find the below output

Current Logic:

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)

I tried the above code and wants to achieve the xlxs file with low code, similar to conditional formatting

Answer 1

Assuming you want to alternate the color of each "id"/block, I would use 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))

Used input :

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],
    }
)