Converting XML to Excel With Python and Typed Cells

When the recipient lives in Excel, a workbook is kinder than a CSV. The numbers are numbers, the dates are dates, a leading zero stays where it is, and nobody has to choose an import encoding. Writing one from XML takes about twenty lines of Python, and the part that deserves attention is the type of each value.

The script

from lxml import etree
from openpyxl import Workbook

root = etree.parse("library.xml").getroot()
wb = Workbook()
ws = wb.active
ws.title = "books"
ws.append(["id", "available", "title", "authors", "price", "currency"])
for b in root.iterfind("book"):
    p = b.find("price")
    ws.append([b.get("id"),
               b.get("available") == "true",
               b.findtext("title"),
               "; ".join(a.text for a in b.iterfind("author")),
               float(p.text),
               p.get("currency")])
ws.freeze_panes = "A2"
ws.column_dimensions["C"].width = 28
for c in ws["E"][1:]:
    c.number_format = "#,##0.00"
wb.save("books.xlsx")

The row structure is the same decision as for CSV: one row per book, authors joined into one cell. What differs is that each value goes in as a Python object, and openpyxl stores the cell with the matching type. So the conversion from XML text to a type is yours to make, and it is the point of the exercise. A Python str is a text cell, a float or int is a number, a bool is a boolean, and a datetime.date is a date with a date format. The XML gave you only strings.

What came out

Reading the saved file back shows what was stored:

from openpyxl import load_workbook
wb2 = load_workbook("books.xlsx")
for r in wb2["books"].iter_rows(values_only=True):
    print(r)
print([(c.value, c.data_type, c.number_format) for c in wb2["books"]["E"][1:]])

('id', 'available', 'title', 'authors', 'price', 'currency')
('b1', True, 'Cache Patterns', 'Ana Lee; Bo Chen', 29.5, 'USD')
('b2', False, 'SQL Tuning, 2nd Edition', 'Cy Ortiz', 44, 'USD')
('b3', True, 'Indexes "Explained"', 'Di Park', 250000, 'VND')
[(29.5, 'n', '#,##0.00'), (44, 'n', '#,##0.00'), (250000, 'n', '#,##0.00')]

Three things to read here. The booleans are True and False, real boolean cells, which Excel shows as TRUE and FALSE and which a filter treats as such. The prices are numeric (data_type n), and 44.00 came back as the integer 44: the file stores the number, and the two decimals on screen come from the cell's number format #,##0.00, which I set on the column. That is how Excel works, and it matters when you compare against the source: the value is 44, the display is 44.00. The titles with the comma and the quotes are stored as they are, with no escaping, since a workbook is a set of typed cells and not a text format. This is the biggest practical advantage over CSV: the quoting problem does not exist.

Types that need care

Identifiers. b1 is plain text, but a code such as 00123 or 1E5 must be written as a Python string. openpyxl stores a string as text and Excel does not reinterpret it on open. The failure shows up in the other direction: if you build the value with int("00123"), the zeros are gone before Excel is involved. Also mark such columns with the text format ("@") if people will type into them later, otherwise Excel reinterprets what they enter.

Dates. Convert with datetime.date.fromisoformat(s) and set cell.number_format = "yyyy-mm-dd". Excel stores a date as a number of days, and the display depends on the format; an XML timestamp with a time zone needs a decision, because Excel dates have no time zone. Convert to the zone the reader expects and say so in a header note.

Money. A float in Excel is a binary double. For display that is fine. For anything that will be summed against another system, round when you write (round(x, 2)) and keep the currency in its own column, as here. A column mixing 29.50 USD and 250000 VND with no currency column is a trap whatever the cell type.

Booleans and flags. Convert to bool as above, not the strings true and false, which sort as text. If your audience prefers "Yes" and "No", write those strings, and do it deliberately.

Long text. An Excel cell holds 32,767 characters, and a longer value is truncated or refused depending on the tool. XML text fields that big (descriptions, bodies) belong elsewhere.

Control characters. Some characters that are legal in a Python string are illegal in a workbook. openpyxl raises an error for them, which is useful: it tells you the XML contained one, usually a stray vertical tab or bell character from a legacy export (invalid character in XML covers where they come from).

The one-call alternative

pandas will write a workbook in one line, and it is worth knowing what you give up:

import pandas as pd
df = pd.read_xml("library.xml", xpath="//book", parser="lxml")
df.to_excel("books_pd.xlsx", index=False)

It runs, and the data has the problems described in converting XML to CSV for spreadsheet users: in the frame I got, the first book's author was Bo Chen only and the currency was missing. The types are right (float64 price, bool flag), which is the pandas strength, so a reasonable approach is to build the DataFrame from your own list of dictionaries, as in the earlier articles, and then call to_excel. You get your mapping with pandas types and formatting hooks. Note that to_excel writes through openpyxl or xlsxwriter anyway.

Polish that costs nothing

Four lines make the workbook feel finished, and none touches the data: freeze_panes = "A2" keeps the header visible when scrolling, a column width stops a long title from being clipped, an auto filter (ws.auto_filter.ref = ws.dimensions) adds filter buttons, and a bold header row separates names from values. Resist the urge to add colors and merged cells. Merged cells in particular break sorting and filtering, which are the main reason the recipient wanted a spreadsheet.

If the same file will be produced weekly, keep the script and not the workbook, and write the output to a dated name. Do not edit the generated file by hand: the next run overwrites it, and nobody will remember which cells were fixed.

Checking the result

Open the saved workbook with the library, not by eye, and test three properties. The number of data rows equals count(//book). The sum of the numeric column equals the sum computed from the XML in a different way (xmllint --xpath 'sum(//price[@currency="USD"])' library.xml returned 73.5, which should equal the USD rows in the workbook). And the cell types are what you intended, which is the output above. The data_type check would have caught the most common failure, numbers stored as text, which Excel flags with a green triangle.

Related reading

The workbook libraries are documented at openpyxl and XlsxWriter, and the cell formatting rules at Microsoft's number format codes. The reverse direction, XML into Excel with a map, is in importing XML into Excel with an XML map, and the broader overview in converting XML to JSON, CSV, YAML and Excel. To test a path, use the XPath tester.

When a workbook is not what the recipient needs

They will load it into another system. A database loader wants CSV or the XML itself. A workbook adds a format to read and invites hand edits.

The data is large. Excel has a limit of 1,048,576 rows per sheet, and a workbook of hundreds of thousands of rows is slow to open. Give them CSV for a database, or a summary workbook plus the detail file.

Formulas and layout are required. If they want totals, charts and formatted headers, start from a template workbook and fill it, or write the formulas as strings starting with an equals sign.

The recipient wants to see the XML. Some people want to inspect the document itself; give them the file and a stylesheet that renders it (transforming XML to HTML with XSLT).