Using XSL Variable and Axes to format XML data into HTML having elements grouped by a tag

Following XSL converts the xml data into a format where first tag of the element is converted into heading and all elements having the same value for first tag will appear in a table below that heading. The Xml elements must be sorted by first tag.

To understand the transformation, consider the following Xml:

<mydata>
 <contact>
   <name>Mark</name>
   <telephone>34546343</telephone>
   <description>Work</description>
 </contact>
 <contact>
   <name>Mark</name>
   <telephone>98884748</telephone>
   <description>Home</description>
 </contact>=
 <contact>
   <name>Mark</name>
   <telephone>23456123</telephone>
   <description>Mobile</description>
 </contact>
 <contact>
   <name>William</name>
   <telephone>23098764</telephone>
   <description>Mobile</description>
 </contact>
 <contact>
   <name>William</name>
   <telephone>45645356</telephone>
   <description>Work</description>
 </contact>
</mydata>

The XSL generates the following output:

Contact: Mark

Telephone Description
34546343 Work
98884748 Home
23456123 Mobile

Contact: William

Telephone Description
23098764 Mobile
45645356 Work

XSL is following:

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
 xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl"
>
 <xsl:template match="/">
 <html>
   <body>
     <xsl:for-each select="mydata/contact">
       <xsl:if test="name != preceding-sibling::contact&#91;1&#93;/name or position() = 1">

        <H3>
         Contact: <xsl:value-of select="name"/>
         </H3>

         <table border="1">
         <tr bgcolor="#9acd32">
           <th>Telephone</th>
           <th>Description</th>
         </tr>

        <xsl:apply-templates select="."/>

        <xsl:variable name="curObject" select="name"/>
         <xsl:apply-templates select="following-sibling::contact&#91;name = $curObject&#93;"></xsl:apply-templates>


         </table>
       </xsl:if>
     </xsl:for-each>

  </body>
 </html>
 </xsl:template>

 <xsl:template match="contact">
   <tr>
     <td> <xsl:value-of select="telephone"/></td>
     <td> <xsl:value-of select="description"/></td>
   </tr>
 </xsl:template>
</xsl:stylesheet>

Excel Add-in for Exporting data to XML

Introduction

ExcelExportXML is a Microsoft Excel 2010 Add-in that generates XML data from excel sheet. It is developed in Visual Studio 2010 using C#.

It is a very simple add-in. To understand how to works, consider an excel sheet having following data.

Country Capital Continent
France Paris Europe
Germany Berlin Europe
India New Delhi Asia
Indonesia Jakarta Asia

Using the add-in, above can be exported to following Xml.

<sheet1>
    <row>
        <country>France</country>
        <capital>Paris</capital>
        <continent>Europe</continent>
    </row>
    <row>
        <country>Germany</country>
        <capital>Berlin</capital>
        <continent>Europe</continent>
    </row>
    <row>
        <country>India</country>
        <capital>New Delhi</capital>
        <continent>Asia</continent>
    </row>
    <row>
        <country>Indonesia</country>
        <capital>Jakarta</capital>
        <continent>Asia</continent>
    </row>
</sheet1>

Once the Add-in is installed, you will have a ‘Generate XML’ button on Add-ins tab of Excel Ribbon as shown below. Clicking ‘Generate XML’ will pop up the save file dialog which will allow you to save the generated Xml file.

ScreenShot.png

Use Case & Assumptions

ExcelExportXml is useful when you have tabular data in excel and the first row contains the column headings. It works based on following assumptions.

  1. First row is considered as column headers and will be converted to Xml tags.
  2. After encountering the first empty cell in header row, rest of the columns to the right will be ignored.
  3. Supports columns up to ‘Z’ only i.e. maximum of 26 columns.
  4. First row for which all values are empty will be considered the end of sheet.
  5. Sheet name and column names should not have any spaces.

Why not use standard Save As Xml functionality

Excel provides more than one ways to export xml data. One of them is ‘Save As Xml Data’ available in Save As dialog. This requires Xml Mappings to be defined which I believe requires developer Add-in from Microsoft to be installed.

Another option is ‘Save As Xml Spreedsheet 2003’ which generates following Xml.

<Worksheet ss:Name="Sheet1">
  <Table ss:ExpandedColumnCount="3" ss:ExpandedRowCount="5" x:FullColumns="1"
   x:FullRows="1" ss:DefaultRowHeight="15">
   <Column ss:Width="51"/>
   <Column ss:Width="54"/>
   <Column ss:Width="51.75"/>
   <Row ss:StyleID="s64">
    <Cell><Data ss:Type="String">Country</Data></Cell>
    <Cell><Data ss:Type="String">Capital</Data></Cell>
    <Cell><Data ss:Type="String">Continent</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="String">France</Data></Cell>
    <Cell><Data ss:Type="String">Paris</Data></Cell>
    <Cell><Data ss:Type="String">Europe</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="String">Germany</Data></Cell>
    <Cell><Data ss:Type="String">Berlin</Data></Cell>
    <Cell><Data ss:Type="String">Europe</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="String">India</Data></Cell>
    <Cell><Data ss:Type="String">New Delhi</Data></Cell>
    <Cell><Data ss:Type="String">Asia</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="String">Indonesia</Data></Cell>
    <Cell><Data ss:Type="String">Jakarta</Data></Cell>
    <Cell><Data ss:Type="String">Asia</Data></Cell>
   </Row>
  </Table>
  </Worksheet>

As you can see, the above might not be what you want. It is more a representation of excel sheet than semantics of your data. ExcelExportXML is quite limited but effective in a common scenario where you have tabular data and the first row contains column headers.

Links

Writing Add-in for Microsoft Excel 2010 using Visual Studio 2010

Using Visual Studio 2010 Professional, creating Excel Add-in is pretty simple. Following are the steps I followed for writing an Add-In for exporting Xml:

Step # 1

Create new project of type ‘Excel 2010 Add-in’. The project will have a class file called ThisAddin.cs.

Step # 2

For creating a button on the Excel ribbon, right click on the project and select Add – > New Item.

Step # 3

Select ‘Ribbon (XML)’ from the list of new items and name the ribbon class, say Ribbon1. This will add two files to the project which are Ribbon1.cs and Ribbon1.xml.

Step # 4

Add the following code in ThisAddin class

protected override Microsoft.Office.Core.IRibbonExtensibility CreateRibbonExtensibilityObject()
{
    return new Ribbon1();
}
Step # 5

In Ribbon1.xml define the properties of the button that will be displayed on the Excel ribbon. The onAction attribute specifies the method which will be called from Ribbon1.cs on clicking the button (onAction="OnTextButton").

 <?xml version="1.0" encoding="UTF-8"?>
	<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui" onLoad="Ribbon_Load">
	  <ribbon>
	    <tabs>
	      <tab idMso="TabAddIns">
	        <group id="MyGroup"
	               label="Export XML">
	          <button id="textButton" label="Generate XML"
	             screentip="Export to XML" onAction="OnTextButton"
	             supertip="Export excel sheet to XML file."/>
	        </group>
	      </tab>
	    </tabs>
	  </ribbon>
	</customUI>
Step # 6

Add following method declaration to Ribbon1.cs and implement your functionality here.

public void OnTextButton(Office.IRibbonControl control)
{
    //TODO:Add your implementation here
} 

Links:

Loading and Saving a TreeView control to an XML file using XmlTextWriter and XmlTextReader

Follow the below link to see my article at CodeProject with source code.

http://www.codeproject.com/KB/cpp/TreeView_Serializer.aspx

It demonstrates how to serialize and de-serialize the contents of System.Windows.Forms.TreeView control from an XML file using forward only, non-cached XmlTextReader and XmlTextWriter.