Free tool
workbook-scan — what a spreadsheet actually does, read out of the file
A workbook a business runs on is undocumented software. workbook-scan is a free, read-only audit tool that opens the .xlsx as what it is — a zip of XML — and reports eleven things that are in the bytes: formulas saved holding an error, links into files that may not exist any more, saved queries to one person's mapped drive, approximate VLOOKUPs, hidden sheets, rules nothing protects. It also has three ways of saying it cannot read the file at all, which is the honest answer often enough to be worth naming. One Python file, standard library only, and nothing in it that writes to anything or opens a socket.
- Excel .xlsx and .xls
- 11 rules
- JSON out
- Exit 1 on findings
- Read-only
- MIT
Install and run
There is no install. One file, standard library only — zipfile,
xml.etree and struct, all three in the Python already on the
machine. No pip, no openpyxl, no virtual environment. It was written and
run here on Python 3.12, which is the only version it has been executed on.
# download it next to wherever the workbook is
curl -O https://plantroomlabs.com/tools/workbook-scan.py
# what does this sheet actually do?
python3 workbook-scan.py pricing-model.xlsx
# several at once, or a whole directory
python3 workbook-scan.py *.xlsx
# the same findings as JSON, for a build or a ticket
python3 workbook-scan.py --json pricing-model.xlsxIt exits 1 when it finds something and 0 when it does not, which is the convention ede-check follows and the reason either can sit in a build step beside a linter. Nothing is uploaded and nothing is written: the workbook is opened for reading and the only output is the report.
What one run looks like
The repository ships two workbooks it generates itself — one deliberately broken, one clean — so the output can be read before anybody sends a real file. This is the broken one, and the run below is pasted by the script that publishes this page rather than retyped.
$ python3 workbook-scan.py fixtures/dirty.xlsx
======================================================================
dirty.xlsx 3503 bytes, 3 sheets, 9 cells, 7 formulas
======================================================================
cached-error 2
hidden-sheet 1
defined-name-broken 1
external-link 1
data-connection 1
volatile 1
fragile-reference 1
approximate-lookup 1
unprotected-formulas 1
hidden-sheet Old rates
the sheet is marked hidden, so it does not appear in the tab bar and its rules are invisible to whoever uses the book
defined-name-broken margin
the named range points at #REF!, so every formula using the name is already wrong
external-link /old-server/finance/rates-2014.xlsx
the workbook reads cells out of another file, so the answer depends on a file that may not exist any more
data-connection RatesTemplate -> N:\_Templates\rates\RatesTemplate.xml
the workbook carries a saved query to a path on one machine - a mapped drive or a share - so it refreshes for whoever set it up and for nobody else
volatile Pricing!B4
the formula uses a volatile function, so the sheet answers differently tomorrow with no input changed
fragile-reference Pricing!B5
the formula uses INDIRECT or OFFSET, which breaks silently when a row is inserted
approximate-lookup Pricing!B6
VLOOKUP is set to approximate match, so on unsorted data it returns a neighbouring row instead of no answer
cached-error Pricing
1 cell in this sheet was saved holding #REF!, so the book was last used in that state
unprotected-formulas Pricing
the sheet carries formulas and has no protection, so anyone can type over a rule and nothing says so
$ echo $?
1
Every line names a sheet, a cell or a path, because “this workbook has
problems” is not a finding and cannot be acted on. The clean fixture prints
nothing this scanner knows how to find and exits 0.
What three of those rules actually catch
A cached error is not cosmetic
When a formula's last computed value was #REF!, Excel writes the error
into the file and types the cell e. The book was saved in that
state, so somebody has been looking at a hole in it since. This program was written
after finding exactly that in a published engineering calculator: the broken cell was
the check the calculator exists to perform, and the file's last revision was nine
years old. Nobody had asked whether it still worked, because nothing asks.
An approximate VLOOKUP is the format's most expensive default
VLOOKUP(x, range, col) with the fourth argument left off, or set to
TRUE, does not answer “not found” on unsorted data. It
returns a neighbouring row. Detecting it needs real argument counting rather than
counting commas — a lookup whose third argument is itself a
MATCH(...) call has four commas and three arguments — so the
scanner splits at the top level only.
A saved data connection refreshes for one person
An external link reads cells out of another file. A data connection is different: it re-runs a query, and when its path is a mapped drive or a share it refreshes for whoever built it and silently for nobody else. Both are in the bytes, with the path, and the path is usually the whole explanation.
It reads the old binary .xls too, and says what it cannot do there
A pre-2007 .xls is not a zip of XML: it is an OLE compound file holding a
stream of BIFF records, and both layers are parsed here. Hidden sheets, cached errors,
unprotected formulas, external links and VBA all work on one, and being a
.xls at all is itself reported — no browser-based tool opens one and
every reader of the format is working from a reverse-engineered layout.
$ python3 workbook-scan.py fixtures/dirty.xls
======================================================================
dirty.xls 2048 bytes, 2 sheets, 1 cell, 1 formula
======================================================================
legacy-binary 1
hidden-sheet 1
cached-error 1
unprotected-formulas 1
legacy-binary dirty.xls
this is the pre-2007 binary .xls format, so no browser-based tool opens it, newer Excel warns before it will, and every reader of it is working from a reverse engineered layout
hidden-sheet Old rates
the sheet is marked hidden, so it does not appear in the tab bar and its rules are invisible to whoever uses the book
cached-error Prices
1 cell in this sheet was saved holding #REF!, so the book was last used in that state
unprotected-formulas Prices
the sheet carries 1 formula and has no protection, so anyone can type over a rule and nothing says so
$ echo $?
1
Three rules are missing from that output on purpose. Volatile functions,
INDIRECT and OFFSET, and approximate lookups all need the
formula text, and BIFF stores formulas as a token stream keyed by function
index. Guessing those indices would mean reporting findings the tool cannot stand
behind, so on a .xls it does not claim them rather than claiming them
badly.
What it does not tell you
It reports what is in the file, not whether it is a defect. A volatile function is correct in a sheet meant to answer differently today; an unprotected formula is fine in a workbook one person uses; a hidden sheet can be deliberate. The findings are the places worth a question, and the question is for somebody who knows what the sheet is for.
It also reads only what the format stores. A rule that is wrong in a way the file cannot express — a factor typed into the wrong cell, a threshold nobody updated — looks exactly like a correct one from here. That part is the audit, and it is a person reading formulas, not a program.
It has been run on the two fixtures above, on their .xls twin, and on a
handful of real published workbooks — which is where the cached-error finding
came from. It has never been run against a thousand-sheet model, and a workbook that
large may well find a case this parser handles badly. The output names the rule and
the cell, so it is checkable rather than something to take on trust.
The repository
The same file, MIT licensed, at github.com/UsamaIqbal0304/workbook-scan. It ships the fixture generator as well as the fixtures, so the broken workbook scanned above can be rebuilt from scratch rather than trusted. Issues and pull requests are read.
The file you are downloading
Published here so the download is checkable rather than trusted. Both figures
are read off the file served at
/tools/workbook-scan.py when this page is built, so they cannot
disagree with it.
| Property | Value |
|---|---|
| File | workbook-scan.py |
| Size | 24,561 bytes |
| SHA-256 | ac8a1be0e8d93b9b64c085e2260c51e358bb50da283776f8246d1aa7f4a71a9d |
| Licence | MIT — LICENSE.txt |
| Source | github.com/UsamaIqbal0304/workbook-scan |
To check it, on Linux sha256sum workbook-scan.py, on macOS
shasum -a 256 workbook-scan.py, on Windows
certutil -hashfile workbook-scan.py SHA256. A different digest means a
different file — not necessarily a hostile one, but not this one.
The repository holds the same file, byte for byte, together with everything needed to re-run the checks this page's claims rest on — so they can be run rather than read about. Issues and pull requests there are read.
bacnet-sweep
Broadcasts a BACnet/IP Who-Is, tables the devices that answer, and dumps a named device's object list — object name, present value and units — to a table or to CSV. It can encode two BACnet services and no others: Who-Is and ReadProperty.
- BACnet/IP
- Who-Is
- CSV out
mqtt-tap
Subscribes to a broker and prints what is actually on it: the topic tree with a count, a rate, a payload-type guess and the last value per topic, plus the retained topics that stopped updating. It sends five packet types and none of them is PUBLISH.
- MQTT
- Topic tree
- Retained
decoder-check
Runs a LoRaWAN device vendor's payload decoder against your frames in a sealed vm context and reports what a station would actually get back: crashes on a short frame, types that change between uplinks, units glued into values, keys a station has to escape. It reads frames and nothing else - no network, no broker, no network server.
- LoRaWAN
- Decoder
- Sandboxed
modbus-address-scan
Reads modbusCore-rt.jar out of a Niagara installation with javap and prints the register a point of each address format actually asks for — including the four band boundaries that all resolve to the same one.
- Modbus
- Shipped jars
- No station
ede-check
Reads the EDE import rules out of bacnetEDE-wb.jar with javap, then reports line by line what the shipped parser would reject in your point file and what it would silently default.
- EDE
- Point lists
- Line by line
module-sign-scan
Reads the verification code out of a Niagara installation and prints the four modes, the signature state each one accepts or refuses, and the exact log line a station writes — including the warning that only becomes a refusal when a certificate expires.
- Module signing
- Shipped jars
- No station
bacnet-priority-scan
Reads bacnet-rt.jar with javap and prints the object types Niagara writes through the priority array without asking, the ones it probes with a single ReadProperty, and what a failed probe does to the point for the life of that configuration.
- BACnet
- Shipped jars
- No station
alarm-route-scan
Reads alarm-rt.jar and baja.jar with javap and prints what happens to an alarm between the source and the recipient: one queue, one worker thread, the coalesce key that decides which duplicate is dropped, and why the invocation that lost that collision still reports success.
- Alarms
- Shipped jars
- No station
alarm-recipient-scan
Reads the recipient side of alarm-rt.jar with javap and prints why returning false from sendAlarm drops the alarm silently, what throwing does instead, how long the retry loop runs, and which four properties are the only evidence a site can send you.
- Alarms
- Retry
- No station
schedule-scan
Reads schedule-rt.jar with javap and prints the 90-day scanLimit horizon that turns a far-off change into no change at all, why nextCov steps over a boundary whose value matches, and the one serial uncapped queue every control schedule shares.
- Schedules
- Shipped jars
- No station
poll-scheduler-scan
Reads the poll scheduler out of driver-rt.jar and prints the arithmetic: three rate defaults, one point polled per pass, and the bucket size at which the thread stops sleeping and the real cycle time stretches.
- Niagara
- javap
- Read-only
tuning-stale-scan
Reads the tuning policy and the stale branch out of the shipped jars: the default staleTime of zero, the clock it measures, and why an unsolicited device can sit dead with an ok status for as long as the station runs.
- Niagara
- javap
- Read-only
Next step
Send the workbook, or the workbook with the numbers blanked.
The rules are in the formulas, so blanking the values costs the read nothing. What comes back is what the sheet encodes and which parts of it disagree with each other - in writing, whether or not anything gets built afterwards.