C# - Office XML: Using XSLT to generate an excel document
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>
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.
Posted by - Christoff Truter
Date - 2010-10-20 21:33:39
Comments - 12
Date - 2010-10-20 21:33:39
Comments - 12
PHP - Office XML: Creating an excel document
Last month (2010/9) I wrote a post about how to generate an excel document using OpenXML and C#. In this post we're going to have a look at how to do something similar using PHP.
However instead of using OpenXML, I am going to demonstrate how to achieve this using another method called Office XML (not to be confused with OpenXML), which is one of the easiest methods to generate Office documents.
The basic layout for a spreadsheet using Office XML looks something like this:
<?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">ID</ss:Data> </ss:Cell> <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>
What we've got here is a simple workbook containing a single worksheet with three cells on it.
There is a number of ways to generate XML using PHP e.g. DOMDocument, SimpleXML, XMLWriter etc, the following snippet demonstrates using SimpleXML (not the best option in PHP for generating xml e.g. flexibility wise - awesome for reading XML though)
$xmlns = "urn:schemas-microsoft-com:office:spreadsheet"; $xml = '<?mso-application progid="Excel.Sheet"?>'. '<ss:Workbook xmlns:ss="'.$xmlns.'" />'; $Workbook = new SimpleXMLElement($xml); $Worksheet = $Workbook->addChild('ss:Worksheet'); $Worksheet->addAttribute('ss:Name', 'Sheet1', $xmlns); $Table = $Worksheet->addChild('ss:Table'); $Row = $Table->addChild('ss:Row'); $Cell = $Row->addChild('ss:Cell'); $Data = $Cell->addChild('ss:Data', 'ID', $xmlns); $Data->addAttribute('ss:Type', 'String', $xmlns); $Cell = $Row->addChild('ss:Cell'); $Data = $Cell->addChild('ss:Data', 'Firstname', $xmlns); $Data->addAttribute('ss:Type', 'String', $xmlns); $Cell = $Row->addChild('ss:Cell'); $Data = $Cell->addChild('ss:Data', 'Lastname', $xmlns); $Data->addAttribute('ss:Type', 'String', $xmlns); echo $Workbook->asXml();
Now instead of writing a thousand lines of code to simply build a sheet, I wrote classes to simplify the preceding snippet, observe:
$Workbook = new Workbook(); $Worksheet = $Workbook->addWorksheet('Sheet1'); $Table = $Worksheet->addTable(); $Row = $Table->addRow(array('ID', 'Firstname', 'Lastname')); $Workbook->Output();
This makes it rather simple to output the results of a SQL query to excel for example, but what about styling the sheet? The XML output for styling a row of data will look something like this:
<?xml version="1.0" ?> <?mso-application progid="Excel.Sheet"?> <ss:Workbook xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"> <ss:Styles> <ss:Style ss:ID="A"> <ss:Font ss:FontName="Times" ss:Size="16" ss:Color="Red" ss:Bold="1" /> </ss:Style> </ss:Styles> <ss:Worksheet ss:Name="Sheet1"> <ss:Table> <ss:Row ss:StyleID="A"> <ss:Cell> <ss:Data ss:Type="String">ID</ss:Data> </ss:Cell> <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>
Which will look something like this (using the wrapper classes I wrote):
$Workbook = new Workbook(); $Styles = new Styles($Workbook); $Worksheet = $Workbook->addWorksheet('Sheet1'); $Table = $Worksheet->addTable(); $Row = $Table->addRow(array('ID', 'Firstname', 'Lastname')); $Style = $Styles->setStyle('A', $Row); $Style->SetFont('Times', '16', 'Red', true); $Workbook->Output();
And thats pretty much it, the classes are by no means a comprehensive implementation of the Office XML standard, but it should give you an idea and get you on your way.
Additional reading
http://www.cstruter.com/downloads/download.php?downloadid=32
http://blogs.msdn.com/b/brian_jones/archive/2005/06/27/433152.aspx
http://msdn.microsoft.com/en-us/library/aa140062(office.10).aspx
http://www.microsoft.com/downloads/en/details.aspx?FamilyId=FE118952-3547-420A-A412-00A2662442D9&displaylang=en
Posted by - Christoff Truter
Date - 2010-10-16 00:47:59
Comments - 1
Date - 2010-10-16 00:47:59
Comments - 1
First 11 12 13 14 15 16 17 18 19 20 Last / 62 Pages (124 Entries)