openpyxl writes formulas. It never evaluates them - so your generated spreadsheet can be silently wrong.

Here is a test that passes, on a workbook that is wrong. from openpyxl import Workbook, load_workbook wb = Workbook() ws = wb.active ws["A1"], ws["A2"], ws["A3"] = 10, 20, 30 ws["B1"] = "=SUM(A1:A3)" wb.save("report.xlsx") check = load_workbook("report.xlsx") assert check.active["B1"].value == "=SUM(A1:A3)" # passes That assertion is real, and it is worth nothing. It proves the string =SUM(A1:A3) was written into B1. It says nothing about whether that formula produces 60, or #DIV/0! , or the first row of a range you meant to aggregate. openpyxl writes formulas. It does not evaluate them. There is no calculation engine in the library - that's Excel's job, and Excel isn't in your build. "Just use data_only=True" The usual first answer, and it's a trap: check = load_workbook("report.xlsx", data_only=True) print(check.active["B1"].value) # None data_only=True doesn't compute anything either. It reads the value Excel cached the last time Excel saved the file. A workbook your code generated thirty milliseconds ago has never been near Excel, so there is no cached value, and you get None . The nasty version of this is a workbook that has been opened in Excel once, months ago, by someone checking it by hand. Now data_only=True returns a number - a stale one, from whatever the data looked like then. That's worse than None , because it looks like a real answer. The defects that actually ship None of these are crashes. Every one of them builds clean, opens beautifully, and is wrong. These four are real - they came out of the first run of a checker against a set of finance templates that were otherwise ready to sell: 1. An array formula returning only its first row. Written into the sheet as text, it needs Ctrl-Shift-Enter semantics to spill. It doesn't spill. It returns row one and silently drops the rest, and the total underneath looks perfectly plausible. 2. An average over empty months. AVERAGE(B2:B13) where only four months have data. AVERAGE skips blanks, so a "monthly average" quietly becomes an average of the months that happen to exist. Read: 1.6%. Truth: 3.25%. Both are believable numbers, which is exactly the problem. 3. A zero divisor dressed up by IFERROR. IFERROR(x/y, 0) renders a tidy $0.00 in the cell where a real figure belongs. The error handling is what hides the bug. 4. A #REF! in a headline cell - in the one layout nobody opened by hand. The common thread: a wrong number that looks like a right number. A crash gets caught. This doesn't, and the person who finds it is the customer. What to assert instead Recalculate the workbook in your test, with an engine that actually solves formulas. The formulas package does this - it parses the workbook into a dependency graph and computes it: import formulas xl = formulas.ExcelModel().loads("report.xlsx").finish() solution = xl.calculate() # keys look like "'[REPORT.XLSX]SHEET1'!B1" The raw output is awkward to assert against - fully-qualified upper-cased keys, values wrapped in numpy arrays - so it's worth one thin helper that turns "Dashboard!A5" into a float. Once you have that, your tests say what you actually mean: wb = load("build/report.xlsx") # recalculated once, cached assert_cell(wb, "Dashboard!A5", 16120.00, label="current MRR") assert_cell(wb, "Dashboard!E5", 0.032508, tolerance=1e-5, label="avg gross churn") assert_no_error_cells(wb) Three things I'd argue are non-negotiable once you're doing this: Hand-compute the expected value. Never record it. The tempting feature is a "record current values" mode that snapshots what the sheet says today and asserts it doesn't change tomorrow. It is the single most requested thing in this shape of tool, and it defeats the entire point: a test that asks the sheet what it thinks the answer is proves only that the sheet is self-consistent. It will happily lock in 1.6% forever. Work the number out yourself, from the inputs, and type it into the test. If that's tedious, that tediousness is the actual cost of knowing the number is right. Blank is not zero Assert absence as deliberately as presence. A dropped table row and a suppressed error both surface as an innocent empty cell, and == 0 won't tell them apart from a legitimate zero. Give yourself blank: and not_blank: assertions. Scan for error cells on every build assert_no_error_cells(wb) Cheapest high-value assertion in the whole category. It requires no knowledge of what the numbers should be, catches #REF! /#DIV/0! /#VALUE! anywhere in the workbook including sheets you forgot existed, and it's one line. Limits worth stating formulas is not Excel. It covers the ordinary surface of a generated workbook - arithmetic, the SUM /AVERAGE /COUNT family, IF /IFERROR , INDEX /MATCH , lookups, dates, text functions. It does not run macros, pivot tables or external links, and an exotic formula may fail to resolve. That last part is a feature if you set it up right: an unresolvable formula should surface as a failed check, never as a silent pass. Design the failure mode so the boring outcome is the safe one. Also: solving a large workbook is slow. Cache the recalculated model per file per process and a whole suite pays that cost once. If you generate spreadsheets in Python and have hit a defect shape that isn't one of the four above, I'd like to hear it - I suspect the list is longer than I think. Disclosure: I built and sell a packaged version of this. Everything above works with pip install formulas and about fifty lines of your own glue - that is genuinely the whole trick, and if you only needed the one assertion, stop here. I got tired of rewriting that glue on every project, so I packaged it: xlcheck - an evaluated-workbook model, tolerance-aware assertions, blank: /not_blank: so a missing number fails as loudly as a wrong one, an error-cell sweep that finds #REF! /#DIV/0! anywhere in the file, a YAML spec runner and a CLI that exits non-zero for CI. 15 tests, and the suite builds workbooks containing the real defect shapes and asserts each one is caught. $49, one-time, no subscription: https://atlasteam.gumroad.com/l/tfcxi Top comments (0)