Introduction to XML capabilities in Excel

Microsoft Excel is a spreadsheet software that is part of Microsoft Office suite. By nature, spreadsheet software is extremely easy to use to do simple calculation and to present data in tabular format. Business environment almost always need some sort of spreadsheet program and usually Microsoft Excel is the top choice due to its powerful capabilities, features and programming support for heavy customization with VBA and VSTO.

Ability to export data to XML format is one of these capabilities. There are few reasons why one might want to export data to XML. One reason that I can think of right now, is for the ability to share data in a widely understood format. XML, being an open standard, is popular among developers as a format for data interchange, and there are plethora of programming libraries available that can manipulate XML. This article intends to show a simple example of exporting data from Excel to XML.

Let’s start with a simple table as below.

Here, we have a workbook named My Organization containing three separate worksheets Sheet1, Sheet2 and Sheet3. In Sheet1, we construct a table with caption Employees. The table has two columns, Name and Age, with a number of entries. If we now try to save the table as XML Data, an error message will be displayed.

The error message states that XML data cannot be saved as there are no XML mappings. Without XML maps, Excel does not know which cells to read data from, nor how to write the XML output. Hence, we have to create the appropriate XML map for the data. For this, we need to write XML Schema to define elements in an XML map. As a simple example, we will create a complex element named Employees which contains a sequence of another complex element Employee. As there are multiple Employee elements, it is a repeating element.

Each Employee element is a complex type itself, containing two simple elements, Name and Age. These two are simple elements because there are no more than a single occurrence of these elements inside each Employee. Which makes sense, because a person usually has only one name and one age.

To define these elements, the following XML Schema is written and saved as employees.xsd.

<?xml version="1.0" encoding="utf-8"?>
<xs:schema elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="Employees">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="Employee" maxOccurs="unbounded">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="Name" type="xs:string" />
              <xs:element name="Age" type="xs:integer" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

Now that we have our schema ready, we can create XML map. For this purpose, we open the XML Source Pane under Developer tab. The pane is visible on the right.

Within the source pane, we can click the button XML Maps… to add an XML map to the workbook. A dialog will appear, where we can click Add… button to select the XML Schema file we created before, which is employees.xsd. Click OK once done adding.

Maps that are added will be visible in the XML Source Pane in a form of element tree.

Now it is time to do the mapping. We may drag each element in the XML Source Pane onto the cells or we might right click them and assign range. For the latter approach, we might right click at Employee element, and assign the range that cover Name and Age table header i.e. B2:C2. Assigning a continuous range like this is possible, as Name and Age columns are next to each other, and follow the sequence defined in the XML map.

Clicking OK, all data in the table will be mapped to appropriate elements.

 

Now that we have successfully mapped XML data to appropriate elements defined in XML map, we can export the table to XML. This is done by clicking on Export button under Developer tab, or by choosing to save as XML data, as we had unsuccessfully tried previously. The resulting XML file contains the data in XML format, as below.

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Employees>
	<Employee>
		<Name>Hafiz</Name>
		<Age>23</Age>
	</Employee>
	<Employee>
		<Name>Elize</Name>
		<Age>29</Age>
	</Employee>
	<Employee>
		<Name>Chong</Name>
		<Age>44</Age>
	</Employee>
	<Employee>
		<Name>Crystal</Name>
		<Age>25</Age>
	</Employee>
	<Employee>
		<Name>Rashidan</Name>
		<Age>23</Age>
	</Employee>
</Employees>

Now we can manipulate the data in XML format In subsequent posts, I will show how to do simple manipulation of XML data and to import the data back into Excel. Hopefully this will give a clear view of XML capabilities in Excel, and the situation in which this feature will become useful.

To import data from XML file into Excel instead, proceed to Part 2.