Article by - Sheela E.N, Sonata Software Limited

The XML mapping feature enables us to add arbitrary XML data structures and XML schema definitions to the workbook and map XML nodes to cells and cell ranges in the workbook. Once the mapping is set up, the application will be able to import and export XML data according to the schema.

Here, we will be discussing two methods of XML mapping in SpreadsheetML.  For these samples we will use Excel 2007 to set up the mapping. The first method is through the Developer Tab and the second method is through the Data tab of Excel 2007.

1. Open Microsoft Excel 2007 and go to Developer tab à Click on Source tab.

Click on Developer tab à Click on Source tab

In the XML Source window, click button - “XML Maps”. This will open a window, which will guide you to select the required XML and mapping it to the spreadsheet.

Click on Add and select a XML file.

XML maps in the workbook.

At this point, spreadsheet will have the schema of the selected xml file.

The following XML files will be generated –

connections.xml: This will have the connection details.

<connections xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">

<connection id="1" interval="1" name="books" type="4" refreshedVersion="3" minRefreshableVersion="1" background="1" refreshOnLoad="1" saveData="1">

<webPr xml="1" sourceData="1" parsePre="1" consecutive="1" xl2000="1" url="C:\test\books.xml" htmlTables="1"/>

                   </connection>

</connections>

xmlMaps.xml: This file will have the schema of the xml file attached.

<MapInfo xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" SelectionNamespaces="">

  <Schema ID="Schema1">

    <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="">

………..

………

    </xsd:schema>

  </Schema>

  <Map ID="1" Name="bookstore_Map" RootElement="bookstore" SchemaID="Schema1" ShowImportExportValidationErrors="false" AutoFit="true" Append="false" PreserveSortAFLayout="true" PreserveFormat="true">

    <DataBinding FileBinding="true" ConnectionID="1" DataBindingLoadMode="1"/>

  </Map>

</MapInfo>

Drag and drop the required xml elements to XLSX file from xml source. Click on the “Import” or “Refresh Data” button under Developer tab.

Now, drag and drop required XML elements to Spreadsheet from XML source and click on the “Import” button under Developer tab. This will get whole of the data from the XML file and bind it to the Spreadsheet.

Now, the following XML parts will be generated –

table1.xml: This will contain the details about the table, i.e. the connection ID used, Table column, XPATH of each column.

<table xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" id="1" name="Table1" displayName="Table1" ref="A1:H4" tableType="xml" totalsRowShown="0" connectionId="1">

  <autoFilter ref="A1:H4"/>

  <tableColumns count="8">

    <tableColumn id="1" uniqueName="genre" name="genre">

<xmlColumnPr mapId="1" xpath="/bookstore/book/@genre" xmlDataType="string"/>

    </tableColumn>

    …….

    ………

    <tableColumn id="8" uniqueName="price" name="price">

<xmlColumnPr mapId="1" xpath="/bookstore/book/price" xmlDataType="double"/>

    </tableColumn>

  </tableColumns>

 <tableStyleInfo name="TableStyleMedium9" showFirstColumn="0"  showLastColumn="0" showRowStripes="1" showColumnStripes="0"/>

</table>

sheet1.xml.rels: This will contain the relationship reference.

<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/table" Target="../tables/table1.xml"/>

1. Open Microsoft Excel 2007 and go to Data tab à Click “From other sources” à Click From XML Data Import as the option.

Here, checking the “XML table in existing worksheet” will generate the data as shown below and the XML generated internally are same as the ones specified in the first method.

XML Map Properties shown on selection of “XML table in existing worksheet” will be as follows –

If suppose at the ‘Import Data’, when the other selection is made, the properties to be selected are different and are illustrated as follows –

Select the “Existing worksheet” or “New worksheet”

This has an option to refresh the data for every time interval specified and also at the time of opening the file. On selecting “Refresh Every 2 minutes” option, data changes in the specified XML will be shown every 2 minutes.

On checking “Refresh data when opening the file” will query the XML file for the data when opening the Spreadsheet file and displays the refreshed data.

Data in Spreadsheet will look like below –

This will create “QueryTable” part in the Spreadsheet Open XML file and the following XML files will be generated –

connections.xml: this will contain the connection details with intervals, refresh and URL details.

<connection id="1" interval="2" name="books" type="4" refreshedVersion="3" minRefreshableVersion="1" background="1" refreshOnLoad="1" saveData="1">

    <webPr xml="1" sourceData="1" parsePre="1" consecutive="1" xl2000="1" url="D:\Project\DotnetProj\Test\xl_XML_Mapping\books.xml" htmlTables="1"/>

  </connection>

queryTable1.xml: This will contain the details of the connection used for the query.

<queryTable xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" name="books" refreshOnLoad="1" connectionId="1" autoFormatId="16" applyNumberFormats="0" applyBorderFormats="0" applyFontFormats="1" applyPatternFormats="1" applyAlignmentFormats="0" applyWidthHeightFormats="0"/>

sheet1.xml.rels: This will contain the relationship reference for the sheet and “QueryTable”.

<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/queryTable" Target="../queryTables/queryTable1.xml"/>

By this method, using Query Table we can trigger a query at regular intervals as well as at the time of opening of the Spreadsheet. This will in turn refresh the data on the Excel Spreadsheet.

These two methods help in mapping external XML to an Excel Spreadsheet file.