Apply Gradient and Pattern Fills with openpyxl

Cell fills carry a lot of a report's meaning — header bands, input cells shaded differently from formulas, a hatched pattern for "not applicable", a gradient bar in a title. openpyxl supports all of Excel's fill types through two classes, PatternFill and GradientFill, and almost every problem people hit comes from one detail: which of the two colours a fill actually paints. A solid fill uses the foreground colour, which is counter-intuitive enough that a fill set with only bgColor comes out black. This page goes through solid, pattern and gradient fills, fills inside conditional formats, and clearing fills, and checks the result by rendering the sheet rather than trusting the code. It belongs to Styling Excel Cells with openpyxl.

Prerequisites

pip install openpyxl pillow

LibreOffice (soffice) and pdftoppm (from Poppler) are used in Step 6 to render the sheet and sample the colours; they are optional.

Step 1 — Solid fills use fgColor

from openpyxl import Workbook
from openpyxl.styles import PatternFill, Font

wb = Workbook()
ws = wb.active
ws.title = "Fills"
ws.column_dimensions["A"].width = 26
ws.column_dimensions["B"].width = 34
for r in range(1, 9):
    ws.row_dimensions[r].height = 30

ws["A1"], ws["B1"] = "solid, fgColor", 'PatternFill("solid", fgColor="4338CA")'
ws["A1"].fill = PatternFill("solid", fgColor="4338CA")
ws["A1"].font = Font(color="FFFFFF", bold=True)
ws["A2"], ws["B2"] = "solid, bgColor only", 'PatternFill("solid", bgColor="4338CA")'
ws["A2"].fill = PatternFill("solid", bgColor="4338CA")
print(ws["A2"].fill.fgColor.rgb, ws["A2"].fill.bgColor.rgb)

In Excel's file format, a pattern fill has a pattern colour (fgColor) drawn over a background colour (bgColor), and a solid pattern is 100% pattern — so it shows only fgColor. Setting just bgColor leaves fgColor at its default, 00000000, which is black: the printout shows A2's foreground as 00000000, and the cell renders as a black block. Always set fgColor (also called start_color) for solid fills. The colours are hex RGB without #; an 8-digit value adds an alpha prefix that Excel ignores.

Step 2 — Hatched and dotted patterns

The other pattern types draw lines or dots in fgColor over bgColor, which is useful for marking cells that are not applicable, estimated or locked without relying on colour alone:

patterns = ["darkTrellis", "lightUp", "gray125"]
for i, pattern in enumerate(patterns, start=3):
    ws.cell(i, 1, pattern).fill = PatternFill(pattern, fgColor="5B6780", bgColor="EEF2FF")
    ws.cell(i, 2, f'PatternFill("{pattern}", fgColor="5B6780", bgColor="EEF2FF")')
print(sorted(PatternFill.patternType.values - {None})[:6], "…")

Here both colours matter: a slate-grey pattern on a pale indigo background. openpyxl accepts eighteen pattern names, from gray0625 (sparse dots) through lightUp/darkUp (diagonal lines) to darkTrellis (cross-hatching); misspelling one raises the "Value must be one of" error explained in Fix openpyxl "Value must be one of" and "Max value is" Errors. Patterns print well in black and white, which is one good reason to use them for status marking in reports that end on paper. Be aware that LibreOffice renders pattern fills as a blended flat colour in its PDF output rather than drawing the hatching, so check patterns in Excel.

Step 3 — Gradient fills

GradientFill blends between colour stops, either along an angle (type="linear") or outwards from a point (type="path"):

from openpyxl.styles import GradientFill

ws["A6"], ws["B6"] = "linear 0°", 'GradientFill(degree=0, stop=("FFFFFF", "4338CA"))'
ws["A6"].fill = GradientFill(type="linear", degree=0, stop=("FFFFFF", "4338CA"))
ws["A7"], ws["B7"] = "linear 90°, three stops", "stop=('0F9488', 'FFFFFF', '0F9488')"
ws["A7"].fill = GradientFill(type="linear", degree=90, stop=("0F9488", "FFFFFF", "0F9488"))
ws["A8"], ws["B8"] = "path from centre", "type='path', left=right=top=bottom=0.5"
ws["A8"].fill = GradientFill(type="path", left=0.5, right=0.5, top=0.5, bottom=0.5, stop=("FFFFFF", "B4740A"))
print(ws["A7"].fill.type, ws["A7"].fill.degree, [s.color.rgb for s in ws["A7"].fill.stop])

degree=0 runs left to right, 90 top to bottom. Stops given as plain colours are spread evenly; three stops make the middle colour sit at the centre, which gives a "shine" effect. A path gradient with all four edges at 0.5 radiates from the centre of the cell. Gradients look dated if overused; a single gradient title band is usually plenty. LibreOffice's PDF export approximates gradient fills with a single blended colour, so judge gradients in Excel.

Step 4 — Fills in conditional formats

Conditional formats use the same PatternFill class, but the differential style stored for the rule has its own conventions: Excel reads the background colour for a solid fill there. Setting both colours works everywhere:

from openpyxl.formatting.rule import CellIsRule

ws["D1"], ws["D2"], ws["D3"] = "Margin", 0.12, -0.04
good = PatternFill("solid", start_color="D9F4F1", end_color="D9F4F1")
bad = PatternFill("solid", start_color="FCE9E9", end_color="FCE9E9")
ws.conditional_formatting.add("D2:D3", CellIsRule(operator="greaterThanOrEqual", formula=["0"], fill=good))
ws.conditional_formatting.add("D2:D3", CellIsRule(operator="lessThan", formula=["0"], fill=bad))
wb.save("fills.xlsx")
print("rules:", [str(r.sqref) for r in ws.conditional_formatting])

start_color and end_color are openpyxl's other names for fgColor and bgColor; setting both to the same value makes the rule's fill appear in Excel and LibreOffice alike. LibreOffice accepts either colour on its own, which is why a rule written with only fgColor can look fine in testing and come out uncoloured for Excel users. Rules in general are covered in Applying Conditional Formatting with openpyxl.

Step 5 — Remove a fill

from openpyxl.styles import PatternFill

ws["A9"] = "fill removed"
ws["A9"].fill = PatternFill("solid", fgColor="FF0000")
ws["A9"].fill = PatternFill(fill_type=None)
print("A9 fill type:", ws["A9"].fill.fill_type)
wb.save("fills.xlsx")

A PatternFill with fill_type=None means "no fill"; colours given alongside it are ignored. This is the way to clear fills selectively — for example, removing the yellow "needs review" shading once rows are checked — without touching fonts, borders or number formats. To reset every aspect of a cell's formatting at once, see Clear Cell Contents and Formatting with openpyxl.

Step 6 — Check the result by rendering it

The fastest way to catch a black-instead-of-indigo mistake is to look. Copy each fill onto a text-free swatch sheet, render it to an image and sample the colour band by band:

import subprocess
from copy import copy
from PIL import Image

swatch_wb = Workbook()
sw = swatch_wb.active
sw.column_dimensions["A"].width = 40
for r in range(1, 10):
    sw.row_dimensions[r].height = 30
    sw.cell(r, 1).fill = copy(ws.cell(r, 1).fill)
swatch_wb.save("swatches.xlsx")
subprocess.run(["soffice", "--headless", "--convert-to", "pdf", "swatches.xlsx"], check=True, capture_output=True)
subprocess.run(["pdftoppm", "-png", "-r", "50", "-f", "1", "-l", "1", "swatches.pdf", "swatches"], check=True)
img = Image.open("swatches-1.png").convert("RGB")
column = [img.getpixel((80, y)) for y in range(img.height)]
bands, start = [], 0
for y in range(1, len(column) + 1):
    if y == len(column) or column[y] != column[start]:
        if y - start >= 8:                                 # ignore gridlines and edges
            bands.append("#%02X%02X%02X" % column[start])
        start = y
print(bands[1:10])                                         # skip the white page margin

Copying the fill objects onto a fresh sheet without text keeps letters from polluting the samples. Scanning down column A and keeping only bands at least eight pixels tall skips the thin gridlines. The first band is the indigo #4338CA of the correct solid fill, the second is #000000 — the bgColor-only fill rendered black, exactly as predicted — followed by the pattern and gradient rows as blended flat colours, and white for the cleared fill. The darkTrellis, lightUp and gray125 rows come out as three different greys — the pattern's ink blended with its background in proportion to how much of the cell the pattern covers — and the three gradients as single averaged tints of indigo, teal and gold. A check like this, pinned in a test, is cheap protection for report templates where colour carries meaning: assert that the header band is the brand colour and that no fill renders as pure black.

Common pitfalls

• bgColor on solid fills. The cell turns black; use fgColor (start_color).
• Only one colour on conditional-format fills. Set start_color and end_color to the same value.
• # in colour codes. Use "4338CA", not "#4338CA".
• Assigning a string to cell.fill. cell.fill = "FFFF00" fails only when saving; always assign a fill object.
• Judging patterns and gradients in LibreOffice output. Its PDF render approximates them; check in Excel.
• Too many distinct fills. Every unique fill adds a style; reuse a few named fill objects instead of creating a new one per cell.

Conclusion

Use PatternFill("solid", fgColor=...) for plain colours — the foreground is what a solid fill paints, and a bgColor-only fill renders black. Other patterns draw fgColor over bgColor, GradientFill blends stops linearly by angle or outwards along a path, conditional-format fills should set both colours, and PatternFill(fill_type=None) clears a fill. Render the sheet and sample the colours when fills carry meaning, keeping in mind that LibreOffice approximates patterns and gradients.

Frequently asked questions

Why is my openpyxl cell fill black? You created a solid PatternFill with only bgColor. A solid fill shows the foreground colour, which defaults to black. Use PatternFill("solid", fgColor="4338CA") instead.

How do I add a gradient fill to a cell with openpyxl? Assign a GradientFill, for example cell.fill = GradientFill(type="linear", degree=90, stop=("FFFFFF", "4338CA")). Use type="path" with left, right, top and bottom for a gradient that radiates from a point.

What is the difference between fgColor and bgColor in openpyxl? fgColor is the pattern colour and bgColor the colour behind the pattern. For a solid fill only fgColor is visible; for hatched patterns both are used.

How do I remove a fill from a cell in openpyxl? Assign PatternFill(fill_type=None) to the cell's fill. Other formatting such as fonts and borders is left unchanged.

Related

• Up one level: Styling Excel Cells with openpyxl — formatting cells for reports.
• Add Alternating Row Colors with openpyxl — fills applied as banding.
• Apply a Reusable Style Theme Across an Excel Report — keeping fills consistent.
• Read Cell Colours and Font Styles as Data with openpyxl — reading fills back.