Convert Excel to XML

A grid described as a tree

Each sheet becomes a repeating element per row, with one child element per column named from the header row — which is what makes the result readable by anything expecting data XML rather than a spreadsheet. Column names have to become valid element names on the way, and XML is stricter than a spreadsheet header: a name cannot begin with a digit, cannot contain spaces or most punctuation, and is case-sensitive. So "Order Total (£)" becomes something tidier, and two headers that differ only in punctuation can end up needing to be told apart.

What travels is values, not appearance. A cell showing £1,234.50 is stored in the workbook as the number 1234.5 with a format applied, and it is the number that goes into the XML — which is almost always right, since a consuming system wants the value rather than the currency symbol. Formulas are gone in the same way: what you get is what the formula produced, because XML has nowhere to keep the formula itself. Colours, fonts, borders, conditional formatting, charts and images have no representation here at all.

Dates are the one to check. A spreadsheet stores a date as a number of days since an epoch and displays it through a format, so a date can emerge as 45356 rather than as a date, depending on how the cell was set up. If dates matter to whatever reads the XML, look at them before relying on the file — and consider formatting them as text in the workbook first, which removes the ambiguity entirely.

The five characters XML reserves — ampersand, angle brackets and quotes — are escaped in the values, so text containing them survives intact. Empty cells produce empty elements rather than being omitted, which keeps every record the same shape and makes the output far easier to process than one where missing fields simply vanish. Password-protected workbooks must have their password removed first.

When you want something else

When the XML has to match a specific schema — an industry format, a government filing, a system's import format — a generic conversion will not produce it, and no amount of configuration here will either. That is a mapping job: XSLT transforms this output into the required shape, and xmllint --schema confirms the result before you submit it.

For anything repeated, script it. Python's openpyxl and pandas read a workbook and write whatever XML you specify, with control over how dates and numbers are rendered, which is the part that matters. And it is worth asking whether XML is really the target — most systems built this decade would rather have JSON or CSV, and XML is usually a requirement inherited from something older.