Fix openpyxl "Value must be one of" and "Max value is" Errors

Styling code written from memory of Excel's menus fails on its first line: Side(style="bold") raises ValueError: Value must be one of {'hair', 'dashDotDot', 'thin', …}. Excel's interface says "bold border", "middle align" and "Landscape"; the file format — and openpyxl, which mirrors it — uses medium, center and landscape. openpyxl checks every such attribute against the exact set of names the format allows, case included, and prints that set in the error. Most of these errors are therefore fixed by reading the message carefully. A few are subtler: some values are not checked until the workbook is saved, which leaves a half-written, unreadable file behind. This page belongs to Troubleshooting Common Python Excel Errors.

Prerequisites

pip install openpyxl

Messages below are from openpyxl 3.1; the set inside the braces is printed in a different order on each run, because it is a Python set.

Step 1 — Read the message as a list of valid names

from openpyxl.styles import Side, Alignment, PatternFill, Font

attempts = {
    'Side(style="bold")': lambda: Side(style="bold"),
    'Alignment(horizontal="middle")': lambda: Alignment(horizontal="middle"),
    'PatternFill(fill_type="Solid")': lambda: PatternFill(fill_type="Solid", fgColor="FFFF00"),
    'Font(u="double underline")': lambda: Font(u="double underline"),
}
for code, attempt in attempts.items():
    try:
        attempt()
    except ValueError as exc:
        names = sorted(str(exc).split("{", 1)[1].rstrip("}").replace("'", "").split(", "))
        print(f"{code:32} -> {names}")

Each error message ends with the complete set of accepted values, so the fix is usually visible in it: borders are thin, medium, thick, double, dashed and friends — there is no bold; horizontal alignment is center, not middle; fill types are lower-case solid; underline styles are single, double, singleAccounting and doubleAccounting. The names are the ones written into the XML, which is why they look like identifiers rather than menu labels.

Step 2 — The usual translations

The same handful of mistakes account for most of these errors. Corrected, the code from Step 1 and its relatives look like this:

from openpyxl import Workbook
from openpyxl.styles import Border
from openpyxl.worksheet.datavalidation import DataValidation

wb = Workbook()
ws = wb.active
ws["B2"] = "Total"
ws["B2"].border = Border(bottom=Side(style="medium"))          # "bold" border
ws["B2"].alignment = Alignment(horizontal="center", vertical="center")  # "middle"
ws["B2"].fill = PatternFill(fill_type="solid", fgColor="FFFF00")         # "Solid"
ws["B2"].font = Font(u="double")                                # "double underline"
ws.page_setup.orientation = "landscape"                         # "Landscape"
dv = DataValidation(type="decimal", operator="greaterThan", formula1="0")  # "number"
ws.add_data_validation(dv)
dv.add("C2:C20")
ws.sheet_view.view = "pageLayout"                               # "pageLayoutView"
print("all accepted")

medium is the heavier line most reports use under headings and totals; thick is heavier still. Data validation has whole for integers and decimal for any number. Vertical alignment's middle is also center, while its other values are top and bottom. Sheet views are normal, pageLayout and pageBreakPreview.

Step 3 — Use openpyxl's constants instead of strings

openpyxl defines module-level constants for most of these sets. They make typos impossible and show the valid options in an editor's autocomplete:

from openpyxl.styles.borders import BORDER_MEDIUM, BORDER_THIN
from openpyxl.styles.fills import FILL_SOLID
from openpyxl.styles.alignment import horizontal_alignments, vertical_aligments

print(BORDER_MEDIUM, BORDER_THIN, FILL_SOLID)
print("horizontal:", horizontal_alignments)
print("vertical:  ", vertical_aligments)

The alignment tuples double as documentation: print them once and the valid values are in front of you. (The second name really is spelled vertical_aligments in openpyxl — copy it as is.) For a reusable set of styles built from these constants, see Apply a Reusable Style Theme Across an Excel Report.

Step 4 — "Max value is" and colour errors

Numeric attributes have ranges, and colours have a format:

for code, attempt in {
    "Alignment(indent=300)": lambda: Alignment(indent=300),
    "Alignment(text_rotation=200)": lambda: Alignment(text_rotation=200),
    'Font(color="#FF0000")': lambda: Font(color="#FF0000"),
    'PatternFill("solid", fgColor="red")': lambda: PatternFill("solid", fgColor="red"),
}.items():
    try:
        attempt()
    except ValueError as exc:
        print(f"{code:36} -> {str(exc)[:70]}")

print(Font(color="FF0000").color.rgb, Alignment(text_rotation=135).text_rotation)

indent is capped at 255 (Max value is 255). text_rotation accepts 0–180, where 0–90 rotate counter-clockwise and 91–180 rotate clockwise (an angle of −45° is written as 135); 255 means vertical stacked text — which is why the error for 200 lists every number from 0 to 180 (the printed set leaves out 255, which openpyxl nevertheless accepts). Colours must be hex without #: "FF0000" or the eight-digit "FFFF0000" with alpha, never "#FF0000" or "red" (Colors must be aRGB hex values). Rotation is covered in Rotate Text in Excel Cells with openpyxl.

Step 5 — Values that are only checked when you save

Not every attribute is validated when you set it. Some are accepted silently and rejected by save(), after part of the file has been written:

from pathlib import Path
import zipfile

wb = Workbook()
wb.create_sheet("Data")
wb.active.sheet_state = "hide"           # should be "hidden" — no error here
try:
    wb.save("broken.xlsx")
except ValueError as exc:
    print("save failed:", exc)
print("file left behind:", Path("broken.xlsx").exists(), "| valid zip:", zipfile.is_zipfile("broken.xlsx"))
print("has workbook.xml:", "xl/workbook.xml" in zipfile.ZipFile("broken.xlsx").namelist())

Setting sheet_state = "hide" raises nothing; save() then fails with Value must be one of {'visible', 'hidden', 'veryHidden'} — and leaves broken.xlsx on disk as a ZIP without its workbook part, which Excel cannot open and which overwrites whatever good file had that name before. Assigning a plain string as a cell's fill (cell.fill = "FFFF00") behaves the same way, failing at save with a TypeError. Two defences: save to a temporary name and rename only on success, as in Write Excel Files Atomically in Python, and keep a test that saves the workbook so these errors appear in development rather than in the nightly run.

Step 6 — Check style settings from a config file up front

Report styles often live in a configuration file so that people can change colours and borders without touching code. That moves these errors to whoever edits the file, at run time. Check the whole configuration before building anything and report every problem at once:

import re

STYLE_RULES = {
    "border": set(Side.style.values) - {None},
    "align": set(horizontal_alignments),
    "valign": set(vertical_aligments),
    "fill": set(PatternFill.patternType.values) - {None},
}
HEX = re.compile(r"^[0-9A-Fa-f]{6}([0-9A-Fa-f]{2})?$")

def check_styles(config):
    problems = []
    for name, spec in config.items():
        for key, value in spec.items():
            if key in STYLE_RULES and value not in STYLE_RULES[key]:
                close = [v for v in STYLE_RULES[key] if v.lower() == str(value).lower()]
                hint = f" (did you mean {close[0]!r}?)" if close else ""
                problems.append(f"{name}.{key}={value!r} is not one of {sorted(STYLE_RULES[key])}{hint}")
            if key.endswith("color") and not HEX.match(str(value)):
                problems.append(f"{name}.{key}={value!r} must be hex like 'FF0000'")
    return problems

config = {"header": {"fill": "Solid", "fill_color": "#4338CA", "align": "center"},
          "total": {"border": "bold", "valign": "middle"}}
for p in check_styles(config):
    print("-", p)

The descriptor objects on openpyxl's classes — Side.style.values, PatternFill.patternType.values (fill_type is an alias for it) — hold the same sets the error messages print, so the check never drifts from what openpyxl enforces. A case-insensitive comparison turns Solid into a helpful "did you mean 'solid'?", and a single list of problems beats fixing them one exception at a time. Where report settings live and how to load them is covered in Keep Excel Report Settings in a Config File.

Common pitfalls

• Menu names instead of file-format names. bold, middle, Landscape, number and hide are not valid; read the set in the error.
• Capitalisation. Solid and Center fail; every name is case-sensitive.
• Colours with # or names. Use "FF0000"; convert CSS colours first.
• Assuming validation happens on assignment. Some values fail only at save; test the save.
• Sets that include None. Several attributes also accept None (no border, no fill); the descriptor's values set contains it, which is why the checker above subtracts {None}.
• Overwriting the last good file. A failed save leaves a broken file at the target path; write to a temporary path first.

Conclusion

Value must be one of {...} is openpyxl telling you the exact names the file format accepts — use medium or thick for bold borders, center for middle alignment, lower-case solid fills, landscape, decimal or whole validations and hidden sheets, or the constants openpyxl provides. Keep numbers in range (indent ≤ 255, rotation 0–180 or 255), write colours as hex without #, and save to a temporary file, because a few values are only checked at save time and leave a broken workbook behind.

Frequently asked questions

Why does openpyxl say "Value must be one of" for my border style? openpyxl only accepts the border names the Excel file format uses: thin, medium, thick, double, dashed, dotted, hair and the dash-dot variants. There is no bold; use medium or thick.

How do I centre text vertically in openpyxl? Use Alignment(vertical="center"). The accepted vertical values are top, center, bottom, justify and distributed; middle is not one of them.

What does "Colors must be aRGB hex values" mean? The colour was given as "#FF0000", a name like "red", or another format. openpyxl needs six or eight hexadecimal digits without a hash, such as "FF0000" or "FFFF0000".

Why did openpyxl fail only when saving the workbook? Some attributes, such as sheet_state, are validated when the file is written rather than when they are set. The save raises the ValueError and leaves a partial file behind, so save to a temporary path and rename it after success.

Related

• Up one level: Troubleshooting Common Python Excel Errors — fixes for common failures.
• Fix openpyxl "TypeError: expected <class ...>" — the error for passing the wrong kind of object.
• Add Borders to a Range with openpyxl — borders with valid styles.
• Wrap Text and Align Cells with openpyxl — alignment options in practice.