C# - Office XML: Using XSLT to generate an excel document

October 20, 2010 by Christoff Truter C#   Microsoft Office  

In my previous post I swiftly explained how to create an excel document in PHP using Office XML (OpenXML's older not so clever brother).

In this post we're going to do the same thing (Excel via Office XML) using C#, but instead of simply "translating" the PHP code, I am going to "introduce" another technology into the equation - a technology called XSLT (Extensible Stylesheet Language Transformations).

The basic idea is to define a XSLT file which will produce the following Office XML spreadsheet as output:

<?xml version="1.0" ?> 
<?mso-application progid="Excel.Sheet"?> 
<ss:Workbook xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
	<ss:Worksheet ss:Name="Sheet1">
		<ss:Table>
			<ss:Row>
				<ss:Cell>
					<ss:Data ss:Type="String">Firstname</ss:Data> 
				</ss:Cell>
				<ss:Cell>
					<ss:Data ss:Type="String">Lastname</ss:Data> 
				</ss:Cell>
			</ss:Row>
		</ss:Table>
	</ss:Worksheet>
</ss:Workbook>

Firstly we need to rewrite the preceding XML sheet into XSLT format/schema, which essentially becomes our template, which we'll populate using a DataSet, observe:

workbook.xslt

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
                xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" 
                version="1.0">
  <xsl:output encoding="utf-8" />
  <xsl:template match="NewDataSet">
    <xsl:text disable-output-escaping="yes">
    <![CDATA[<?mso-application progid="Excel.Sheet"?> ]]>    
    </xsl:text>
    <ss:Workbook>
      <ss:Worksheet ss:Name="Sheet1">
        <ss:Table>
          <xsl:apply-templates select="Table1"/>
        </ss:Table>
      </ss:Worksheet>
    </ss:Workbook>
  </xsl:template>
  <xsl:template match="Table1">
    <ss:Row>
      <ss:Cell>
        <ss:Data ss:Type="String">
          <xsl:value-of select="Firstname"/>
        </ss:Data>
      </ss:Cell>
      <ss:Cell>
        <ss:Data ss:Type="String">
          <xsl:value-of select="Lastname"/>
        </ss:Data>
      </ss:Cell>
    </ss:Row>
  </xsl:template>
</xsl:stylesheet>

(I am not going to explain all of the XML above - it pretty much contains the basics used in XSLT, you can read more about XSLT by clicking here.)

Notice the match & select attributes "NewDataSet", "Table1", "Firstname" and "Lastname" - these are values we're going to extract from the XML generated from our DataSet - the following XML will be generated via the WriteXml method used in the GetDocument method in this post.

<NewDataSet>
	<Table1>
		<Firstname>Jason</Firstname> 
		<Lastname>Smith</Lastname> 
	</Table1>
	<Table1>
		<Firstname>Wayne</Firstname> 
		<Lastname>Kleynhans</Lastname> 
	</Table1>
</NewDataSet>

(Note that NewDataSet & Table1 are default element names that will be rendered if we don't name our DataSet/DataTables etc)

XML generated by our DataSet??? Well... We will need to get a XML representation of our DataSet in order to bind it to the XSLT sheet.

In the following snippet we transform the XSLT file into an Office XML Spreadsheet:

using System.Data;
using System.IO;
using System.Xml.Xsl;
using System.Xml.XPath;

class Program
{	
	// Some fake Data 
    static DataSet GetData()
    {
        DataSet ds = new DataSet();
        DataTable dt = new DataTable();
        dt.Columns.AddRange(new DataColumn[] { 
                new DataColumn("Firstname"), 
                new DataColumn("Lastname") });
        dt.Rows.Add(new object[] { "Jason", "Smith" });
        dt.Rows.Add(new object[] { "Wayne", "Kleynhans" });
        ds.Tables.Add(dt);
        return ds;
    }

    static XPathDocument GetDocument(DataSet ds)
    {
        using (StringWriter sw = new StringWriter())
        {
            ds.WriteXml(sw);
            using (StringReader sr = new StringReader(sw.ToString()))
            {
                return new XPathDocument(sr);
            }
        }
    }

    static void Main(string[] args)
    {
        DataSet ds = GetData();
        XPathDocument input = GetDocument(ds);

        using (FileStream output = new FileStream(@"c:\xls.xml", FileMode.CreateNew))
        {
            XslCompiledTransform xslt = new XslCompiledTransform();
            xslt.Load("workbook.xslt");
            xslt.Transform(input, null, output);
        }
    }
}

The end result is a file containing the Office XML spreadsheet.