How to Generate Excel File from XML with XSL
Extensible Stylesheet Language (XSL), a family of transformation languages, allows one to describe how to format or transform files encoded in the XML standard. The XSL language itself uses valid XML syntax, with constructs such as:
<xsl:if test="@author='Jones'">Hello Mrs. Jones!</xsl:if>
The start-tag and end-tag of every statement echo the syntax of the opening and closing
parenthesis of Lisp. The designers of XSL wanted a data driven language: it strongly encourages the inversion of control design pattern. The language assumes the processing of an XML file as a tree to produce a text-based output document, generally HTML, XML, plain-text, or PDF. XSL programmers can declare variables, but not change their values. The language
provides several data-driven looping constructs, but programmers can still construct
arbitrary loops without altering any variables by using recursion
In-line Code
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="GenerateExcelFromXMLFile.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Generate Excel from XML with the help of XSL</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Label ID="label1" Text="XML Files" runat="server"></asp:Label>
<asp:DropDownList id="List" runat="server"></asp:DropDownList>
<hr />
<br />
<asp:Button ID="Generate" Text="Generate Excel" runat="server"
onclick="Generate_Click" />
</div>
</form>
</body>
</html>
Code-behind
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Xml;
using System.Xml.Xsl;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
BindXMLFileToDropDown(); // Binding XML Files into DropDown
}
/// <summary>
/// Get XML Files from [XML Files] Folder
/// </summary>
private void BindXMLFileToDropDown()
{
string[] dirs = System.IO.Directory.GetFiles(Server.MapPath("XML Files"));
foreach(string str in dirs)
{
List.Items.Add(System.IO.Path.GetFileName(str));
}
}
protected void Generate_Click(object sender, EventArgs e)
{
Response.ContentType = "application/vnd.ms-excel";
Response.Charset = "";
DataSet ds = new DataSet();
ds.ReadXml(System.IO.Path.Combine(Server.MapPath("XML Files"), List.SelectedItem.Text.Trim()));
XmlDataDocument xdd = new XmlDataDocument(ds);
XslTransform xt = new XslTransform();
xt.Load(Server.MapPath("ExcelTemplate.xsl"));
xt.Transform(xdd, null, Response.OutputStream);
Response.End();
}
}
Download Source
Download Document