The spreadsheet that ate my test suite

I lost half a day to a spreadsheet last month. Not to the data in it — the data was six rows of test fixtures. I lost the time to the fact that every tool I tried either could not open the file, or opened it and then quietly damaged it on the way out.

That experience is not unusual, and it points at something most developers get wrong about .xlsx files: we treat them like documents, when they are actually small databases wearing a zip archive.

A spreadsheet is not a document

Rename an .xlsx to .zip and open it. Inside you will find a handful of XML parts: a shared strings table, one part per worksheet, a styles part, and a relationships index tying them together. Excel never stores "the grid". It stores cells with type annotations, and every visual property — the column widths, the fills, the merged ranges — lives somewhere else, referenced by index.

Once you know that, the failure mode becomes obvious. A tool that reads the whole workbook into a generic representation and writes it back rebuilds the package from scratch. The values survive. Everything else is rebuilt from defaults. You open the file, and your carefully sized columns are now 64 pixels wide and your header fills are gone.

That is the good failure. The bad one is the file that will not open at all.

The three ways to edit a workbook, ranked by what they risk

There is no universally correct answer here, only tradeoffs.

Excel itself, or LibreOffice. Complete, reliable, and entirely outside your editor. LibreOffice can run headless, which makes it the right answer for a build pipeline. It is the wrong answer for "I need to change one cell in a fixture."

A library — Apache POI, openpyxl, ExcelJS. These are what every tool in this space is built on. Using one directly gives you control, and it also makes formatting preservation entirely your problem. One wrong write mode and you learn about OPC packages the hard way.

An IDE plugin. Convenient, and this category has a genuinely bad reputation. Spend twenty minutes reading reviews on the JetBrains marketplace and you will find a consistent set of complaints: files corrupted, editors stuck open, and plugins that take over file types you never asked them to.

The question that separates good from bad is not "which category". It is narrower: when this tool writes my file, what does it do to the original, and what happens if the write fails halfway?

Everything that matters happens on save

Reading a workbook is a solved problem. Writing one safely is where the damage lives, and it usually comes down to three omissions that are easy to make and hard to notice.

Writing in place. If the tool truncates the original and streams a new workbook into it, any interruption — crash, full disk, killed process — leaves you with a half-written file. There is no recovery from that except version control, and test fixtures are frequently not in version control in any useful way.

No backup. Even a correct write is destructive from the file's point of view. A tool that overwrites the only copy without keeping the previous bytes is asking for complete trust before it has earned any.

Holding the handle open. On Windows, an open file handle makes an atomic replace fail, so the tool falls back to an in-place write — and now you have the first problem again.

The fix is unglamorous and well documented: copy the original to a .bak, write the new workbook to a temporary file, then move the temporary file over the original in a single operation. If anything fails, the original is untouched. It costs one extra write and it is the entire difference between a tool you can point at a fixture and one you cannot.

A habit that catches the failures

There is one step people skip, and it is the one that finds the problems.

After saving, open the file again. Not the tab you already have — open it fresh. If it loads, the write probably succeeded. If it fails, look at the .bak next to it. A backup that opens next to a file that does not tells you exactly what happened and gives you a way out.

This takes ten seconds and it is the only real test. "It looked fine in the editor" proves nothing, because the editor was holding the parsed representation, not the file.

The CSV trap

The most common mistake in this whole area is treating .csv and .xlsx as interchangeable containers.

They are not, and the difference is not cosmetic. A CSV has no types. Everything is text, and whether 007 means seven or a product code depends entirely on whatever reads it next. An .xlsx stores the type alongside the cell, so a number remains a number.

If your fixtures hold phone numbers, postal codes, employee IDs, or anything with meaningful leading zeros, CSV will eat them, and you will not notice until a test fails for a reason that looks unrelated.

There is a second, sneakier problem with CSV: plugins that claim it. Several spreadsheet plugins register for .csv on install, and reviewers describe the resulting file association as something that survives uninstalling the plugin. If a plugin's listing does not state exactly which extensions it claims, assume it claims more than you want.

Where a tool earns its place

For a one-off edit on a file you will never touch again, open LibreOffice. The plugin case is genuinely narrow: a spreadsheet that lives in your repository, changes often, and takes small edits. Test fixtures. Seed data. Configuration workbooks maintained by someone who does not use an IDE, and consumed by code you own.

I ended up building one for exactly that case. It opens .xlsx and .xlsm in an editor tab, edits cells in place, handles row insert and delete, and moves cell blocks to and from Excel and Google Sheets as tab-separated text. It saves through the backup-and-atomic-replace path described above, and it deliberately does not claim .csv.

It is also honest about where it stops. It loads up to 5,000 rows and 200 columns per sheet. Formula cells show their computed value rather than letting you edit the formula. Formatting is preserved but not editable. If your workflow needs any of those, the tool is wrong for you, and that is worth knowing before you install it rather than after.

What to ask before installing anything

If you take one thing from this, take the question rather than the tool.

Ask what happens to your original file when a save fails. Ask which extensions the plugin claims. Ask whether it writes only the changed cells or rebuilds the package. A tool that answers those honestly, even when the answer limits what it does, is worth more than one that promises to replace Excel.

The spreadsheet in your repository is a file with a layout and a history. Treat the tool as a writer of that file, not a viewer with edit buttons, and most of the damage becomes avoidable.

Originally published on DEV Community.

Comments

Popular posts from this blog

Why plugins corrupt your .xlsx files (and how to check before it happens)

Windows 11 Is Full and It's Not Your Apps: Where the Space Actually Went

Why Task Manager Says 95% Memory When Nothing Is Running