CODEDIGEST
Home Articles CodeDigest Tutorials FAQs
Skip Navigation LinksHome » Article » ASP.Net Article » Export dataset to Excel with XSLT in Asp.Net 2.0   You are not logged in.
Search
 

Technologies
 

Sponsors
 

CodeDigest Navigation
 

Technology News
 

Community News
No News Feeds available at this time.
 
Export dataset to Excel with XSLT in Asp.Net 2.0

By Satheesh babu
Posted On Feb 19,2008
Article Rating: (Login)
Be first to rate
this article.
No of Comments: 5
Print this article.
Category:

Subscribe to our feed!

Export dataset to Excel with XSLT in Asp.Net 2.0

There are numerous ways to export the dataset content to excel, like binding it to grid and rendering to excel by setting the MIME type of the output stream and using RenderControl method in the grid. This article will help us to export the dataset contents to excel using XSLT.

Things to know:

XSL:

It is a style sheet file that contains information for converting a XML file to other formats like HTML.

Know more about XSL and XSLT here...

http://www.w3.org/Style/XSL/WhatIsXSL.html

Excel XML:

Excel spreadsheets can be expressed as a xml file so that it can be opened and edited in Excel.

 

The whole content of a dataset can be exported to excel using XSLT without any dependency on the installed components on the server. For example, If we use Excel interop libraries then the code will have the dependency on the excel application on the server.

 

I am proceeding this article with the assumption that the reader have basic knowledge on XSLT. Unzip the example attached with this article for the complete code.
First, we need to create an XML data file from the dataset and a XSL file that contains the transformation information. We have to apply XSLT transformation over the XML file using the constructed xsl file and generate the excel XML.

 

 

Creation of XML file:

using XmlTextWriter object create a XML file with the processing information,

 

<?xml-stylesheet type='text/xsl' href='\transform.xslt'?>

 

here href refers to the XSL file that is used for the convertion.

 

string procInfo = "type='text/xsl' href='" + "\\transform.xslt" + "'";

XmlTextWriter tw = new XmlTextWriter(XMLPath ,null);

tw.WriteStartDocument();

DataSet ds = GetData();

ds.Tables[0].TableName = "Products";

tw.WriteProcessingInstruction("xml-stylesheet", procInfo);

ds.WriteXml(tw);

 

The dataset contains one table with one column "ProductID"(For easy understanding i have included only one column).The XML file will looks like,

 

<?xml version="1.0"?>

<?xml-stylesheet type='text/xsl' href='\transform.xslt'?>

<NewDataSet>

<Products>

<ProductID>1</ProductID>

</Products>

<Products>

<ProductID>2</ProductID>

</Products>

<Products>

<ProductID>3</ProductID>

</Products>

</NewDataSet>



ASP.Net Hosting

Recent Articles

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,

Refer the attachment for complete code.

 

It uses Northwind database in local. Change it accordingly before executing.

 

Download Source:

Download Source
Similar Articles
  • You can contribute to CodeDigest.Com:
    Article Feedback
    Title  
    Submitted By  
    Comment  
    Enter the verification number
     
    Comments
    Excelent article!!
    I was looking for this thing all day long!! you are great man! thanks!
    permissions
    I have tested your code and it works fine. But the problem is I dont understand why the same code does not run on the server. Throws access denied on xml file error.

    Does it require excel installation on the server?
    Multiline support
    Hi
    Great Article !
    My problem is how to handle SQL records with multi line text.
    I is posible to change the XSL to handle replace "Enviroment.Newline" to &#10;
    Problem
    Hi it doesn't work in Open Office and Excel Microsoft Viewer. Is ther any method to make it work in this application??
    Clarification Need
    Hi, I have tired with this code, but the oupt format is not like this. I havent change any code from yours. Output in Excel file is like this, ProductId,In Single cell its written all the vale like ProductID ProductName UnitPrice 1 Product1 1000 2 Product2 2000 3 Product3 3000.. Can you please suggest me where am wrong ??