What it takes to merge .xlsx files without the Excel repair prompt

Merging two .xlsx files sounds like copying rows. If you want the formatting, charts and tables to survive, it's closer to linking two small programs together. I build a Mac app that does this, and Excel's "We found a problem with some content" prompt taught me most of what's below.

An .xlsx file is a ZIP of XML parts. A sheet barely holds any content itself. It holds numbers that point into other parts:

• s="14" on a cell is an index into cellXfs in styles.xml
• t="s" with <v>7</v> is an index into sharedStrings.xml
• conditional formats point into dxfs
• tables, drawings and comments hang off the sheet through relationship files

So you can't paste sheet XML from one workbook into another. Every index means something else over there, and nothing errors. You just get the wrong fonts, or the wrong text.

What has to be remapped

For one sheet moved into a new workbook:

1. Styles. Fonts, fills, borders, number formats, cellStyleXfs, cellXfs and dxfs all get merged and deduplicated, and each cell's index gets rewritten. If the two workbooks use different themes, theme and indexed colours have to become plain RGB first, or "accent 1" quietly turns into a different colour.
2. Shared strings. Keep the raw <si> elements, or you lose rich text inside a cell.
3. Tables. Names and ids must be unique across the workbook, so they get renamed. Then every structured reference to them has to follow.
4. Defined names. Same global name with the same definition can be reused. Same name with a different definition has to be suffixed. Sheet-scoped names need the new tab index.
5. Drawings and charts. Copy the parts with their relationships, then fix chart formulas, because the tab they point at may have a new name.
6. Dates. A workbook can use the 1904 date system. Merge it into a 1900 workbook without shifting the date values and everything moves by 1,462 days, with no warning at all.

What actually triggered the repair prompt

The interesting bugs weren't in the remapping. Each of these came from a fixture or an output that looked fine to my own tests:

• A theme missing its a:ea and a:cs font entries. The schema requires latin, ea and cs. Lots of generated files skip them.
• A hidden sheet marked tabSelected, and also several selected tabs at once.
• ZIP headers. Every entry claimed "version needed 45" (ZIP64) without being ZIP64, and a stored image relied on a data descriptor for its size. LibreOffice refused the whole file. In my first run of 576 outputs through it, every single one was refused. After the fix, all 576 converted.

That last one is the lesson I'd pass on. I had been testing the XML and not the container.

How I test it now

Three things that don't share my assumptions:

The ISO 29500 schemas. A strict validator runs the transitional schemas over workbook, styles, sheets, tables, comments, shared strings, drawings, charts and themes. Element order matters and required children are required. On top of that it checks rules the schema doesn't express but Excel cares about: one selected visible tab, no overlapping merges, table headers equal to column names, unique conditional format priorities, valid style ids. Before that round, 171 of 576 outputs failed it. After, 558 passed, and the 18 left came from two public sample files that break the schema themselves.

Other programs reading the result. openpyxl reads back values, formulas, formats, merges and widths. LibreOffice headless converts everything.

Real Excel. It opens the outputs through Launch Services and AppleScript dumps the values back. I treat a repair prompt as a bug and never click through it. The last pass was 237 outputs across every tool.

Also, never drop anything silently. Pivot tables, macros in merged output, things that can't be carried across: each becomes a line in a report with the file and sheet. A tool that loses a chart and says so is fine. One that loses it quietly costs you a Friday.

If you just want to do this yourself

For plain data, Power Query on Windows or a few lines of openpyxl or pandas is fine. Know that those read values and rebuild the file, so charts, images and a lot of formatting don't survive. If formatting matters, you need package-level copying like the above, or an app that does it.

Full disclosure: mine is Gridhand, a Mac app for merging, splitting, comparing and cleaning Excel files in batches. It runs offline and is free for up to 3 files per run, USD 49 once after that. It doesn't handle .xls or .numbers yet.

Written with AI assistance. The numbers come from my own test logs.