Read Cell Values as Displayed in Excel with Python

Sometimes the value is not what you need. A reconciliation must compare what a report shows with what a system printed; a generated email must quote figures exactly as they appear in the attached workbook; a test must check that a column really displays (1,235) and not -1234.5. openpyxl and pandas both return the stored value, because that is what the file contains — the displayed text is created by Excel when it draws the cell, from the value and the number format together. This guide, part of Applying Number and Date Formats in Excel, shows two ways to get that text and where each one stops being exact.

Prerequisites

pip install openpyxl

LibreOffice (the soffice command) is needed for the first approach. A sample workbook with typical report formats:

import datetime as dt

from openpyxl import Workbook, load_workbook

CASES = [
    (1234.5, "#,##0"), (0.1234, "0.0%"), (1234567, '#,##0.0,,"M"'),
    (-1234.5, "#,##0_);[Red](#,##0)"), (1234.5, '"€"#,##0.00'), (42, "00000"),
    (2.675, "0.00"), (dt.datetime(2026, 3, 5, 14, 7), "dd/mm/yyyy"),
    (dt.datetime(2026, 3, 5, 14, 7), "d-mmm-yy"), (dt.datetime(2026, 3, 5, 9, 5, 3), "h:mm:ss"),
]
wb = Workbook()
ws = wb.active
for row, (value, fmt) in enumerate(CASES, start=1):
    ws.cell(row, 1, value).number_format = fmt
wb.save("formats.xlsx")

cell = load_workbook("formats.xlsx").active["A4"]
print(cell.value, cell.number_format)       # -1234.5 #,##0_);[Red](#,##0)

That last line is the whole problem: the value and the format, but not (1,235).

Approach 1 — Let LibreOffice render it

LibreOffice can open the workbook headlessly and export each sheet as CSV containing the formatted text. It applies the same number-format engine it uses on screen, so the result is what a reader of the file in LibreOffice sees:

import csv
import subprocess

subprocess.run(["soffice", "--headless", "--convert-to",
                "csv:Text - txt - csv (StarCalc):44,34,76,1,,0,false,true,true",
                "formats.xlsx"], check=True, capture_output=True, timeout=120)
shown = [row[0] for row in csv.reader(open("formats.csv", encoding="utf-8"))]
print(shown)
# ['1,235', '12.3%', '1.2M', '(1,235)', '€1,234.50', '00042', '2.68', '05/03/2026', '5-Mar-26', '9:05:03']

The filter string requests comma-separated UTF-8 output of the formatted text (the true in the eighth position); add ,false,false,-1 to get one CSV per sheet instead of only the first. This is the most faithful option without Excel, and it handles every format LibreOffice understands. Its costs are a LibreOffice installation, a second or two per file, and the fact that it shows LibreOffice's rendering — identical to Excel's for the common codes above, but not guaranteed for every exotic one. Colours such as [Red] are not part of the text.

Approach 2 — Format in Python

For a known set of formats, a Python function is faster, needs no external program and is easy to test. The one below handles the codes that make up most reports: thousands separators, fixed decimals, leading zeros, percent, scaling commas, literal text, the positive; negative; zero sections, and date and time tokens. It rounds like Excel — halves away from zero, on the value's 15-digit representation — which is essential: Python's own formatting would show 2.675 as 2.67 and 1,234.5 as 1,234.

import re
from decimal import ROUND_HALF_UP, Decimal

DATE_TOKEN = re.compile(r'yyyy|yy|mmmm|mmm|mm|m|dddd|ddd|dd|d|hh|h|ss|s|"[^"]*"|\\.|.', re.I)

def _date_text(value, fmt):
    fmt = re.sub(r"\[[^\]]*\]", "", fmt.split(";")[0])
    tokens, out, prev = DATE_TOKEN.findall(fmt), [], ""
    for i, tok in enumerate(tokens):
        low = tok.lower()
        nxt = next((t.lower() for t in tokens[i + 1:] if t.strip(":. ")), "")
        if low in ("mm", "m") and (prev in ("h", "hh") or nxt in ("s", "ss")):
            out.append(f"{value.minute:02d}" if low == "mm" else str(value.minute))
        else:
            out.append({
                "yyyy": f"{value.year:04d}", "yy": f"{value.year % 100:02d}",
                "mmmm": value.strftime("%B"), "mmm": value.strftime("%b"),
                "mm": f"{value.month:02d}", "m": str(value.month),
                "dddd": value.strftime("%A"), "ddd": value.strftime("%a"),
                "dd": f"{value.day:02d}", "d": str(value.day),
                "hh": f"{value.hour:02d}", "h": str(value.hour),
                "ss": f"{value.second:02d}", "s": str(value.second),
            }.get(low, tok.strip('"').lstrip("\\") if len(tok) > 1 else tok))
        if low.strip(":. "):
            prev = low
    return "".join(out)

def _number_text(value, fmt):
    sections = fmt.split(";")
    if value < 0 and len(sections) > 1:
        fmt, value = sections[1], -value
    elif value == 0 and len(sections) > 2:
        fmt = sections[2]
    else:
        fmt = sections[0]
    fmt = re.sub(r"_.", " ", re.sub(r"\[[^\]]*\]", "", fmt)).replace("*", "")
    m = re.search(r"[#0][#0,.]*", fmt)
    if not m:
        return fmt.replace('"', "")
    code, prefix, suffix = m.group(0), fmt[:m.start()], fmt[m.end():]
    scale = len(code) - len(code.rstrip(","))
    int_part, _, dec_part = code.rstrip(",").partition(".")
    value = value / 1000 ** scale * (100 if "%" in suffix else 1)
    number = Decimal(format(value, ".15g")).quantize(Decimal(1).scaleb(-len(dec_part)), ROUND_HALF_UP)
    whole, _, frac = f"{number:{',' if ',' in int_part else ''}f}".partition(".")
    if len(whole.replace(",", "")) < int_part.count("0"):
        whole = whole.replace(",", "").zfill(int_part.count("0"))
    return (prefix + whole + ("." + frac if frac else "") + suffix).replace('"', "").replace("\\", "")

def displayed(value, fmt):
    if value is None:
        return ""
    if isinstance(value, dt.time):                     # a time-only cell
        value = dt.datetime.combine(dt.date(1899, 12, 31), value)
    elif isinstance(value, dt.date) and not isinstance(value, dt.datetime):
        value = dt.datetime.combine(value, dt.time())
    if isinstance(value, dt.datetime):
        return _date_text(value, fmt)
    if isinstance(value, (int, float)) and fmt not in (None, "General"):
        return _number_text(value, fmt)
    return format(value, ".10g") if isinstance(value, float) else str(value)

ws = load_workbook("formats.xlsx").active
print([displayed(c.value, c.number_format) for (c,) in ws.iter_rows(max_col=1)])
# ['1,235', '12.3%', '1.2M', '(1,235)', '€1,234.50', '00042', '2.68', '05/03/2026', '5-Mar-26', '9:05:03']

Both approaches produce the same ten strings. The function reads cell.value and cell.number_format straight from openpyxl, so it works anywhere Python runs.

Step 3 — Test the formatter against the renderer

A hand-written formatter is only trustworthy if it is checked. Keep a list of the formats your reports use, write them to a workbook with awkward values — halves, negatives, zeros, small numbers — and compare the function's output with LibreOffice's export in a test:

def mismatches(path, rendered):
    ws = load_workbook(path).active
    ours = [displayed(c.value, c.number_format) for (c,) in ws.iter_rows(max_col=1)]
    return [(i + 1, a, b) for i, (a, b) in enumerate(zip(ours, rendered)) if a != b]

print(mismatches("formats.xlsx", shown))       # []

The formatter in this guide was checked this way against 25 value-and-format pairs, including 0.125 and 1.005 at two decimals, −0.4 showing as (0), zero sections, scaled millions and times, with no differences. When a report adopts a new format, add it to the list first; a mismatch then fails the test rather than a figure in a sent report.

Where the displayed text is not enough

The text a cell displays also depends on things outside the file. Excel shows #### when a number is too wide for its column, and the General format switches to scientific notation in narrow cells; neither shows up in a CSV export or a Python formatter. Month and day names follow the reader's language — mmm is "Mar" for an English reader but a different abbreviation for a German or French one — and decimal and thousands separators follow their regional settings, so 1,234.50 reads as 1.234,50 in much of Europe. And colours from [Red] sections or conditional formatting are not text at all. If you need to reproduce exactly what one person sees on one screen, only that application can tell you; on Windows with Excel installed, the Text property of a cell through COM returns it — see Save a Workbook in Another Format with win32com for driving Excel from Python.

Common pitfalls

• Formatting with f-strings. f"{2.675:.2f}" gives 2.67 and f"{1234.5:,.0f}" gives 1,234 — banker's rounding on the binary value. Round with Decimal and ROUND_HALF_UP.
• Numbers stored as text. A cell holding the string "1234.5" displays as typed, whatever its format says. Check cell.data_type first.
• Assuming every date cell is a datetime. openpyxl returns datetime only when the format is a date format; a date typed as text stays a string.

Conclusion

The displayed text of a cell is the stored value run through its number format by the application that draws it. Without Excel, get it by exporting the formatted text with LibreOffice, or by formatting cell.value with cell.number_format in Python using Excel's rounding. Test any Python formatter against LibreOffice's output for the formats your reports use, and remember that column width, locale and colour are outside what either approach can capture.

Frequently asked questions

Why does openpyxl return 1234.5 when Excel shows $1,234.50? An .xlsx file stores the value and the number format separately; the displayed text is produced by the application when it draws the cell. openpyxl gives you both parts — cell.value and cell.number_format — but not the combined text.

Is there a way to read the exact text Excel displays? Not from the file alone. The most faithful option without Excel is to let LibreOffice render the workbook and export the formatted text as CSV. With Excel installed on Windows, cell.Text through COM returns exactly what Excel shows.

Can I format the values myself in Python? For common codes, yes. A small function that handles thousands separators, decimals, percent, scaling commas, sign sections, literal text and date tokens covers most report formats, provided it rounds halves away from zero like Excel.

Why does Python round my displayed values differently? Python's formatting rounds halves to even and works on the binary value, so 2.675 formats as 2.67. Excel shows 2.68. Round with Decimal and ROUND_HALF_UP on the value's 15-digit representation to match.

Related

• Up one level: Applying Number and Date Formats in Excel — number and date formatting.
• Write Custom Number Format Codes in Excel with Python — the code syntax the formatter reads.
• Show Negative Numbers in Red with Parentheses in Excel — the sign sections in practice.
• Match Excel Rounding in pandas — the rounding rule behind the formatter.