How to color each 3 consecutive rows of dataframe with the same color and write output to excel file

Question

After getting a dataframe, I want to color the background of each three consecutive rows with the same color using xlsxwriter library or any other library I have tried the next code but unfortunately it gave me a single color yellow

def highlight_cells():
   return ['background-color: yellow']

Answer 1

You can highlight the whole row or just the cells with values, I assume the later is preferred.
This example writes the dataframe to Excel as is then uses Openpyxl to change the fill colour on the cells used to avoid reading/re-writing with Xlsxwriter.

import pandas as pd
from openpyxl.styles import PatternFill

### Sample dataframe
df = pd.DataFrame([['banana', 1, 'sheep', 2, 'mon', 3],
                   ['apple', 10, 'lion', 20, 'son', 30],
                   ['mango', 100, 'tiger', 200, 'father', 300],
                   ['pear', 45, 'wolf', 220, 'aunt', 302]
                   ])


sheet_name = 'Sheet1'
with pd.ExcelWriter("foo.xlsx",
                    engine='openpyxl'
                    ) as writer:
    df.to_excel(writer,
                sheet_name=sheet_name,
                header=False,
                index=False
                )

    wb = writer.book
    ws = wb[sheet_name]

    ### Set fill colour for the 3 rows to yellow
    for row in ws.iter_rows(max_row=3):
        for cell in row:
            cell.fill = PatternFill(start_color="FFFF00",
                                    end_color="FFFF00",
                                    fill_type="solid"
                                    )

Example output

If you meant you want to colour every alternate 3 rows then you can delete the code from this line

### Set fill colour for the 3 rows to yellow

and change to the following sample code;

Note If you want the fill to be 3 no fill, 3 fill, 3 no fill rather than 3 fill, 3 no fill, 3 fill then change the line.

colour = True

colour = False

Code sample

...
    ### Set fill colour for every 3 rows to yellow
    colour = True
    for row in ws.iter_rows():
        if colour:
            for cell in row:
                cell.fill = PatternFill(start_color="FFFF00",
                                        end_color="FFFF00",
                                        fill_type="solid")
        if row[0].row % 3 == 0:
            colour = not colour

The above code example uses the following dataframe

df = pd.DataFrame([['banana', 1, 'sheep', 2, 'mom', 3],
                   ['apple', 10, 'lion', 20, 'son', 30],
                   ['mango', 100, 'tiger', 210, 'daughter', 301],
                   ['pear', 45, 'wolf', 220, 'aunt', 302],
                   ['mandarin', 56, 'goat', 230, 'uncle', 303],
                   ['orange', 12, 'horse', 240, 'grandfather', 304],
                   ['peach', 73, 'hare', 250, 'grandmother', 305],
                   ['nectarine', 91, 'emu', 260, 'sister', 306],
                   ['coconut', 134, 'piglet', 270, 'brother', 307]
                   ])