Exporting Excel Data as XML With a Schema and What Blocks It

Reading XML into Excel gets the attention. The reverse, turning a worksheet into an XML file a partner can load, is the job people actually dread, because the receiver has a schema and Excel does not make a schema optional. This article explains how the export works, what blocks it and how to do the same job with a script when Excel will not.

How the export works

Excel does not export a sheet. It exports an XML map: a schema plus the links from its elements to cells (importing XML into Excel with an XML map). To produce XML you add the schema as a map, drag its elements onto the sheet so they map to columns, fill in the data, and use Export on the Developer tab. If the workbook has several maps and no mapped table is selected, Excel asks which map to export.

The output is shaped by the schema, not by the sheet's layout: Excel walks the map, takes the repeating elements' rows from the table and writes the document in the schema's order. Columns that are not mapped are not exported, and mapped cells that are empty become absent or empty elements depending on the element's definition.

What Microsoft says it will not do

The documentation lists limits that decide whether your job is possible.

Row cap. Export saves a maximum of 65,536 rows. The article suggests a workaround for larger data that I would not rely on without trying it: its formula and its steps disagree about 65,536 versus 65,537. The plain fix is to split the data across several exports or to use a script.

Schema constructs. A map whose mapped element sits in a sequence with maxOccurs other than 1 can fail the export. A map that contains a choice construct cannot be exported. Recursive structures deeper than one level and mixed content are also out. These are properties of the schema, so an industry schema built for programs, with unions and choices, may be impossible to drive from a grid.

Validation. Export validates the data against the schema by default, and refuses on error. You can turn it off in XML Map Properties, and doing so produces files that the receiver will reject.

Encoding. The file is written as UTF-8.

Platform. The XML tools are not available in Excel for Mac or the web, and the documentation calls XML maps a legacy feature, recommending Power Query for import scenarios. There is no Power Query equivalent for export to a custom schema.

A schema that fits

The schema must describe a flat repeating record for the export to be simple:

<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified">
  <xs:element name="orders"><xs:complexType><xs:sequence>
    <xs:element name="order" maxOccurs="unbounded"><xs:complexType><xs:sequence>
      <xs:element name="id" type="xs:integer"/>
      <xs:element name="customer" type="xs:string"/>
      <xs:element name="total" type="xs:decimal"/>
    </xs:sequence></xs:complexType></xs:element>
  </xs:sequence></xs:complexType></xs:element>
</xs:schema>

One repeating order, three simple children, no attributes, no choice. This is the shape that maps cleanly: the table's three columns are id, customer and total. If the receiver's schema has attributes, nesting or optional blocks, you can often still map them, and you will find out which parts Excel refuses when you try. A useful first step with any schema you are given is to open the XML Source pane and see which elements Excel lets you drag.

The same job in a script

A script has none of those limits and gives you control of the output. It reads the sheet, writes elements and lets the schema validate the result:

import openpyxl
from lxml import etree
ws = openpyxl.load_workbook("orders.xlsx")["orders"]
root = etree.Element("orders")
for id_, customer, total in ws.iter_rows(min_row=2, values_only=True):
    o = etree.SubElement(root, "order")
    etree.SubElement(o, "id").text = str(id_)
    etree.SubElement(o, "customer").text = customer
    etree.SubElement(o, "total").text = f"{total:.2f}"
etree.ElementTree(root).write("orders.xml", encoding="UTF-8", xml_declaration=True, pretty_print=True)

I ran this on a three-row sheet that included customers named Ana & Co and Cy <Ortiz>. The library escaped the ampersand and the angle brackets (Ana & Co, Cy <Ortiz>), the output carried the declaration, and xmllint --noout --schema orders.xsd orders.xml reported that the file validates. The script formats the total with two decimals, which is a decision Excel makes through the cell's number format and the schema type. A script makes the decisions visible in code.

Cell types are the one place the worksheet leaks into the output. openpyxl returns a number for a numeric cell, a string for text and a date object for a date. A ZIP code typed as a number is already 2134 when you read it, and the zero is gone before any XML is written. If the receiver needs 02134, the column has to be text in the workbook, or the script has to pad it with a format. The same is true in Excel's own export, which writes what the cell holds.

Choosing the type of every column

The worksheet has types only by accident, and the schema has them by design, so the export is where the two meet. Before you build the map, write the target type next to each column: identifier (string, to keep leading zeros), integer, decimal with a fixed number of digits, date, boolean. Then make the cells agree. Text columns formatted as Text, numbers formatted with the digits the schema allows, dates as real dates and not strings that look like dates. A mismatch shows up either as a validation error on export, which is the good case, or as a file that validates and says something slightly different from the sheet, which is the bad one. In the script, the same decision is the f"{total:.2f}" line: two decimals, as text, by choice.

Blank cells need a decision too. An empty cell can become an empty element, an absent element or a failure if the element is required. Look at the minOccurs of each element in the schema, and test with a row that has a blank in every optional column.

Checking an export

Whichever route produced the file, validate it against the receiver's schema before you send it (validating XML against XSD with xmllint), count the rows against the sheet, and compare one total. The check that catches Excel's quiet problems is the count: a table over 65,536 rows exports without the rest, and the file looks fine.

Related reading

For the cluster see moving XML into and out of spreadsheets; for the XML to Excel direction with a script, converting XML to Excel with Python; for writing XML safely, sanitizing data you put into an XML document. Test a path in the XPath tester.

When a script should write the XML

The sheet has more than 65,536 rows. Use a script or a database export.

The schema uses choices, mixed content or deep nesting. Excel cannot export it. Build the document in code.

The export repeats weekly. A script in version control is repeatable and reviewable. A workbook with a hand-built map is neither.

The data needs cleaning on the way out. Trim, pad, validate and reject in code, then write.