|
Writing XSL file for transformation:
Right click your solution and click add new item. In the new
item dialog box select XSLT file and rename it, click add. In the opened XSL
file, find <xsl:stylesheet> tag and replace the tag with,
<xsl:stylesheet version="1.0"
xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:msxsl="urn:schemas-microsoft-com:xslt"
xmlns:user="urn:my-scripts"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
>
This contains additional namespace so that the XSLT processor
outputs corresponding XML so that it can be opened by Excel.
In XSL file, for every “<NewDataSet>" element match in the XML, the
information that should be outputted will be a <Workbook> and <WorkSheet> tag
with some office namespace information. Also, an attribute called "ss:name" should be outputted which is the worksheet name.
<xsl:attribute
name="ss:Name">Products</xsl:attribute>
Within this <Workbook> and <WorkSheet> tag the
data should be copied. I have used <xsl:for-each> statement to do this. This
will copy all the content of dataset XML to the output EXCEL XML.
The final XSL file will look like,
<xsl:stylesheet version="1.0"
xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:msxsl="urn:schemas-microsoft-com:xslt"
xmlns:user="urn:my-scripts"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
>
<xsl:template match="NewDataSet">
<Workbook
xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
<Styles>
<Style ss:ID="s21">
<Font ss:Bold="1"/>
<Alignment ss:Horizontal="Center"
ss:Vertical="Bottom"/>
</Style>
<Styles>
<Worksheet>
<xsl:attribute
name="ss:Name">Products</xsl:attribute>
<Table>
<xsl:apply-templates select="NewDataSet"/>
<Row>
<Cell ss:StyleID="s21">
<Data ss:Type="String">ProductID</Data>
</Cell>
</Row>
<xsl:for-each select="Products">
<Row>
<Cell>
<Data ss:Type="String">
<xsl:value-of select="ProductID"/>
</Data>
</Cell>
</Row>
</xsl:for-each>
</Table>
</Worksheet>
</Workbook>
</xsl:template>
</xsl:stylesheet>
Doing the Tranformation:
XslCompiledTransform object of .Netframework 2.0 contains
the transformation method called Transform(). Callling this method will output the
corresponding Excel XML.
XmlDataDocument xmldoc = new XmlDataDocument();
xmldoc.Load(XMLPath);
XslCompiledTransform xsl = new XslCompiledTransform();
xsl.Load(Server.MapPath(".")+"\\Transform.xslt");
XmlTextWriter tw = new XmlTextWriter(XLSXMLPath,
System.Text.Encoding.UTF8)
tw.WriteStartDocument();
xsl.Transform(xmldoc, null, tw);
The output will look like,
|