Introduction
Definition of: XSLT:
(eXtensible Stylesheet Language Transformation) converts an XML document into another format such HTML, PDF, Text, or XL. It may also be used to convert an XML document to another XML document with a different set of XML tags (different schema).
This article contains the concepts of XMLSS and its transformation to XL using XML and XSLT. XSL is a language for formatting XML documents. The basic idea behind this article is to generate an Excel Report through XMLSS transformation without much effort. Excel report formatting using the Excel Object Library is a tedious work. But here we can generate any Excel Report format by simply changing the XSLT file. Very interesting concept, right??.
Background
In my current project, I needed to generate an Excel report in a specified format. Initially, I tried using the Excel Object Library. But it was a tedious job and was not that successful. After extensive research, I came across the XMLSS transformation concept. Through XMLSS, we can generate Excel in any specified format. The concept is very simple:
XML (Data)+ XSLT (Style Formatter) >XMLSS ->[Transformed ]- >XL [Excel]
Using the code
The core functionality of the project is described below. The GetTransformedXL()
method is used for the Excel file generation. We have to pass two parameters for this method: one is the file name and the other is the dtReport DataTable
. First of all, we need to set all the temporary file storage paths and then create a DataSet
for creating the the XML file (data). Set the DataSet
and DataTable
names same as the name specified in the Transform.xslt file. Format the XML file by proper indentation and other things. Load the XML file in the XmlDataDocument
object. Next, we have to load the XSLT file Transform.xslt in the XslCompiledTransform
object. After that, we have to perform the XSLT transform process and get the proposed XMLSS. Inject the XMLSS to the FileStream
. Finally, download the proposed XL from the FileStream
.
public void GetTransformedXL(string fileName, DataTable dtReport)
{
string procInfo = "type='text/xsl' href='" + "\\transform.xslt" +
"'";
string TempPath = Environment.GetFolderPath(Environment.SpecialFolder.InternetCache);
string XMLPath = TempPath + "\\Transform.xml";
string XLSPath = TempPath + "\\Transform.xls";
DataSet dsReport = new DataSet("ExcelDS");
dtReport.TableName = "ExcelTbl";
dsReport.Tables.Add(dtReport.Copy());
using (XmlTextWriter tw = new XmlTextWriter(XMLPath, null))
{
tw.Formatting = Formatting.Indented;
tw.Indentation = 3;
tw.WriteStartDocument();
DataSet ds = dsReport;
tw.WriteProcessingInstruction("xml-stylesheet", procInfo);
ds.WriteXml(tw);
}
XmlDataDocument xmldoc = new XmlDataDocument();
xmldoc.Load(XMLPath);
XslCompiledTransform xsl = new XslCompiledTransform();
xsl.Load(XSLTFile);
using (XmlTextWriter tw = new XmlTextWriter(XLSPath, System.Text.Encoding.UTF8))
{
tw.Formatting = Formatting.Indented;
tw.Indentation = 3;
tw.WriteStartDocument();
xsl.Transform(xmldoc, null, tw);
}
byte[] Buffer = null;
using (FileStream MyFileStream = new FileStream(XLSPath, FileMode.Open))
{
long size;
size = MyFileStream.Length;
Buffer = new byte[size];
MyFileStream.Read(Buffer, 0, int.Parse(MyFileStream.Length.ToString()));
}
HttpContext.Current.Response.ContentType = "application/xls";
string header = "attachment; filename=" + fileName;
HttpContext.Current.Response.AddHeader("content-disposition", header);
HttpContext.Current.Response.BinaryWrite(Buffer);
HttpContext.Current.Response.End();
HttpContext.Current.Response.Flush();
}
XSLT Part
Creating the XSLT file is one of the difficult jobs. Especially, the style setting part in the XSLT. I found a method to easily generate the Excel template by simply exporting the Excel dile [proposed template] to the XML spreadsheet file. In order to do advanced XSLT formatting, you have to learn the syntax well.
<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="ExcelDS">
<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">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
<Author>Sujith</Author>
<LastAuthor>Sujith</LastAuthor>
<LastPrinted>2007-08-31T16:24:29Z</LastPrinted>
<Created>2007-08-31T16:12:42Z</Created>
<LastSaved>2007-11-02T04:11:41Z</LastSaved>
<Company>Company</Company>
<Version>11.5606</Version>
</DocumentProperties>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
<WindowHeight>7935</WindowHeight>
<WindowWidth>15195</WindowWidth>
<WindowTopX>0</WindowTopX>
<WindowTopY>75</WindowTopY>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Bottom"/>
<Borders/>
<Font/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
<Style ss:ID="m22638596">
<Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
<Borders>
<Border ss:Position="Bottom"
ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left"
ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right"
ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top"
ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<Font ss:FontName="Tahoma" x:Family="Swiss"
ss:Size="8" ss:Bold="1"/>
<Interior ss:Color="#C0C0C0" ss:Pattern="Solid"/>
</Style>
<Style ss:ID="s64">
<Alignment ss:Horizontal="Center"
ss:Vertical="Bottom" ss:WrapText="1"/>
<Borders>
<Border ss:Position="Bottom"
ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left"
ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right"
ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top"
ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<Font ss:FontName="Tahoma" x:Family="Swiss"
ss:Size="8" ss:Bold="1"/>
<Interior ss:Color="#C0C0C0" ss:Pattern="Solid"/>
</Style>
<Style ss:ID="s65">
<Alignment ss:Horizontal="Center"
ss:Vertical="Center" ss:WrapText="1"/>
<Borders/>
<Font ss:FontName="Tahoma"
x:Family="Swiss" ss:Size="9"/>
</Style>
<Style ss:ID="s66">
<Borders/>
<Font ss:FontName="Tahoma"
x:Family="Swiss" ss:Size="9"/>
</Style>
<Style ss:ID="s67">
<Borders/>
<Font ss:FontName="Tahoma"
x:Family="Swiss" ss:Size="9"/>
<NumberFormat ss:Format="Fixed"/>
</Style>
<Style ss:ID="s68">
<Alignment ss:Horizontal="Center"
ss:Vertical="Center" ss:WrapText="1"/>
<Borders/>
<Font ss:FontName="Tahoma" x:Family="Swiss" ss:Size="9"/>
<Interior ss:Color="#C0C0C0" ss:Pattern="Solid"/>
</Style>
<Style ss:ID="s69">
<Borders/>
<Font ss:FontName="Tahoma" x:Family="Swiss" ss:Size="9"/>
<Interior ss:Color="#C0C0C0" ss:Pattern="Solid"/>
</Style>
<Style ss:ID="s70">
<Borders/>
<Font ss:FontName="Tahoma" x:Family="Swiss" ss:Size="9"/>
<Interior ss:Color="#C0C0C0" ss:Pattern="Solid"/>
<NumberFormat ss:Format="Fixed"/>
</Style>
<Style ss:ID="s81">
<Alignment ss:Horizontal="Center"
ss:Vertical="Center" ss:WrapText="1"/>
<Borders/>
<Font ss:FontName="Tahoma" x:Family="Swiss" ss:Size="9"/>
<NumberFormat/>
</Style>
<Style ss:ID="s82">
<Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
<Borders/>
<Font ss:FontName="Tahoma" x:Family="Swiss" ss:Size="9"/>
</Style>
<Style ss:ID="s83">
<Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
<Borders/>
<Font ss:FontName="Tahoma" x:Family="Swiss" ss:Size="9"/>
<NumberFormat ss:Format="Fixed"/>
</Style>
<Style ss:ID="s87">
<Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
<Borders>
<Border ss:Position="Top"
ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<Font ss:FontName="Tahoma" x:Family="Swiss"
ss:Size="8" ss:Bold="1"/>
</Style>
<Style ss:ID="s89">
<Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
<Borders>
<Border ss:Position="Bottom"
ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left"
ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right"
ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top"
ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<Font ss:FontName="Tahoma" x:Family="Swiss"
ss:Size="8" ss:Bold="1"/>
<Interior ss:Color="#C0C0C0" ss:Pattern="Solid"/>
</Style>
</Styles>
<Worksheet>
<xsl:attribute name="ss:Name">ExcelTbl</xsl:attribute>
<Table>
<xsl:apply-templates select="ExcelTbl"/>
<Column ss:AutoFitWidth="0" ss:Width="18"/>
<Row ss:Index="2">
<Cell ss:Index="2" ss:MergeAcross="16" ss:StyleID="m22638596"/>
<Cell ss:MergeAcross="1" ss:StyleID="s89">
<Data ss:Type="String">Q1</Data>
</Cell>
<Cell ss:MergeAcross="1" ss:StyleID="s89">
<Data ss:Type="String">Q2</Data>
</Cell>
<Cell ss:MergeAcross="1" ss:StyleID="s89">
<Data ss:Type="String">Q3</Data>
</Cell>
<Cell ss:MergeAcross="1" ss:StyleID="s89">
<Data ss:Type="String">Q4</Data>
</Cell>
<Cell ss:MergeAcross="2" ss:StyleID="s89">
<Data ss:Type="String">Customer</Data>
</Cell>
</Row>
<Row ss:AutoFitHeight="0">
<Cell ss:Index="2" ss:StyleID="s64">
<Data ss:Type="String">Lane #</Data>
</Cell>
<Cell ss:StyleID="s64">
<Data ss:Type="String">O-City</Data>
</Cell>
<Cell ss:StyleID="s64">
<Data ss:Type="String">O-State</Data>
</Cell>
<Cell ss:StyleID="s64">
<Data ss:Type="String">O-ZIP</Data>
</Cell>
<Cell ss:StyleID="s64">
<Data ss:Type="String">D-City</Data>
</Cell>
<Cell ss:StyleID="s64">
<Data ss:Type="String">D-State</Data>
</Cell>
<Cell ss:StyleID="s64">
<Data ss:Type="String">D-ZIP</Data>
</Cell>
<Cell ss:StyleID="s64">
<Data ss:Type="String">Miles</Data>
</Cell>
<Cell ss:StyleID="s64"/>
<Cell ss:StyleID="s64">
<Data ss:Type="String">RPM</Data>
</Cell>
<Cell ss:StyleID="s64">
<Data ss:Type="String">Flat</Data>
</Cell>
<Cell ss:StyleID="s64">
<Data ss:Type="String">Flat All-in</Data>
</Cell>
<Cell ss:StyleID="s64">
<Data ss:Type="String">All-in RPM</Data>
</Cell>
<Cell ss:StyleID="s64">
<Data ss:Type="String">Min.</Data>
</Cell>
<Cell ss:StyleID="s64">
<Data ss:Type="String">1 yr. Lds</Data>
</Cell>
<Cell ss:StyleID="s64">
<Data ss:Type="String">Margin - RPM</Data>
</Cell>
<Cell ss:StyleID="s64">
<Data ss:Type="String">Margin - Flat</Data>
</Cell>
<Cell ss:StyleID="s64">
<Data ss:Type="String">Margin - RPM - Allin</Data>
</Cell>
<Cell ss:StyleID="s64">
<Data ss:Type="String">Margin - Flat - Allin</Data>
</Cell>
<Cell ss:StyleID="s64">
<Data ss:Type="String"># Loads</Data>
</Cell>
<Cell ss:StyleID="s64">
<Data ss:Type="String">High Cost</Data>
</Cell>
<Cell ss:StyleID="s64">
<Data ss:Type="String"># Loads</Data>
</Cell>
<Cell ss:StyleID="s64">
<Data ss:Type="String">High Cost</Data>
</Cell>
<Cell ss:StyleID="s64">
<Data ss:Type="String"># Loads</Data>
</Cell>
<Cell ss:StyleID="s64">
<Data ss:Type="String">High Cost</Data>
</Cell>
<Cell ss:StyleID="s64">
<Data ss:Type="String"># Loads</Data>
</Cell>
<Cell ss:StyleID="s64">
<Data ss:Type="String">High Cost</Data>
</Cell>
<Cell ss:StyleID="s64">
<Data ss:Type="String">Line Haul</Data>
</Cell>
<Cell ss:StyleID="s64">
<Data ss:Type="String">Fuel</Data>
</Cell>
<Cell ss:StyleID="s64">
<Data ss:Type="String">Exp. Date</Data>
</Cell>
</Row>
<xsl:for-each select="ExcelTbl">
<Row >
<Cell ss:Index="2" ss:StyleID="s65" ss:MergeDown="1">
<Data ss:Type="Number">
<xsl:value-of select="Laneno"/>
</Data>
</Cell>
<Cell ss:StyleID="s65" ss:MergeDown="1">
<Data ss:Type="String">
<xsl:value-of select="Ocity"/>
</Data>
</Cell>
<Cell ss:StyleID="s65" ss:MergeDown="1">
<Data ss:Type="String">
<xsl:value-of select="Ostate"/>
</Data>
</Cell>
<Cell ss:StyleID="s81" ss:MergeDown="1">
<Data ss:Type="Number">
<xsl:value-of select="Ozip"/>
</Data>
</Cell>
<Cell ss:StyleID="s65" ss:MergeDown="1">
<Data ss:Type="String">
<xsl:value-of select="Dcity"/>
</Data>
</Cell>
<Cell ss:StyleID="s65" ss:MergeDown="1">
<Data ss:Type="String">
<xsl:value-of select="Dstate"/>
</Data>
</Cell>
<Cell ss:StyleID="s81" ss:MergeDown="1">
<Data ss:Type="Number">
<xsl:value-of select="Dzip"/>
</Data>
</Cell>
<Cell ss:StyleID="s65" ss:MergeDown="1">
<Data ss:Type="String">
<xsl:value-of select="Miles"/>
</Data>
</Cell>
<Cell ss:StyleID="s65">
<Data ss:Type="String">Price</Data>
</Cell>
<Cell ss:StyleID="s66">
<Data ss:Type="Number">
<xsl:value-of select="PRPM"/>
</Data>
</Cell>
<Cell ss:StyleID="s66">
<Data ss:Type="Number">
<xsl:value-of select="Pflat"/>
</Data>
</Cell>
<Cell ss:StyleID="s67">
<Data ss:Type="Number">
<xsl:value-of select="Pflatallin"/>
</Data>
</Cell>
<Cell ss:StyleID="s67">
<Data ss:Type="Number">
<xsl:value-of select="PRPMallin"/>
</Data>
</Cell>
<Cell ss:MergeDown="1" ss:StyleID="s82">
<Data ss:Type="Number">
<xsl:value-of select="Minimum"/>
</Data>
</Cell>
<Cell ss:MergeDown="1" ss:StyleID="s82">
<Data ss:Type="Number">
<xsl:value-of select="Lcount"/>
</Data>
</Cell>
<Cell ss:MergeDown="1" ss:StyleID="s82">
<Data ss:Type="Number">
<xsl:value-of select="MarginRPM"/>
</Data>
</Cell>
<Cell ss:MergeDown="1" ss:StyleID="s83">
<Data ss:Type="Number">
<xsl:value-of select="Mflat"/>
</Data>
</Cell>
<Cell ss:MergeDown="1" ss:StyleID="s82">
<Data ss:Type="Number">
<xsl:value-of select="MarginRPMallin"/>
</Data>
</Cell>
<Cell ss:MergeDown="1" ss:StyleID="s83">
<Data ss:Type="Number">
<xsl:value-of select="Mflatallin"/>
</Data>
</Cell>
<Cell ss:MergeDown="1" ss:StyleID="s82">
<Data ss:Type="Number">
<xsl:value-of select="Q1Lcount"/>
</Data>
</Cell>
<Cell ss:MergeDown="1" ss:StyleID="s82">
<Data ss:Type="Number">
<xsl:value-of select="Q1Hcost"/>
</Data>
</Cell>
<Cell ss:MergeDown="1" ss:StyleID="s82">
<Data ss:Type="Number">
<xsl:value-of select="Q2Lcount"/>
</Data>
</Cell>
<Cell ss:MergeDown="1" ss:StyleID="s82">
<Data ss:Type="Number">
<xsl:value-of select="Q2Hcost"/>
</Data>
</Cell>
<Cell ss:MergeDown="1" ss:StyleID="s82">
<Data ss:Type="Number">
<xsl:value-of select="Q3Lcount"/>
</Data>
</Cell>
<Cell ss:MergeDown="1" ss:StyleID="s82">
<Data ss:Type="Number">
<xsl:value-of select="Q3Hcost"/>
</Data>
</Cell>
<Cell ss:MergeDown="1" ss:StyleID="s82">
<Data ss:Type="Number">
<xsl:value-of select="Q4Lcount"/>
</Data>
</Cell>
<Cell ss:MergeDown="1" ss:StyleID="s82">
<Data ss:Type="Number">
<xsl:value-of select="Q4Hcost"/>
</Data>
</Cell>
<Cell ss:MergeDown="1" ss:StyleID="s82">
<Data ss:Type="Number">
<xsl:value-of select="Linehaul"/>
</Data>
</Cell>
<Cell ss:MergeDown="1" ss:StyleID="s82">
<Data ss:Type="Number">
<xsl:value-of select="Fuel"/>
</Data>
</Cell>
<Cell ss:MergeDown="1" ss:StyleID="s82">
<Data ss:Type="String">
<xsl:value-of select="Expiredate"/>
</Data>
</Cell>
</Row>
<Row>
<Cell ss:Index="10" ss:StyleID="s68">
<Data ss:Type="String">Cost</Data>
</Cell>
<Cell ss:StyleID="s69">
<Data ss:Type="Number">
<xsl:value-of select="CRPM"/>
</Data>
</Cell>
<Cell ss:StyleID="s69">
<Data ss:Type="Number">
<xsl:value-of select="Cflat"/>
</Data>
</Cell>
<Cell ss:StyleID="s70">
<Data ss:Type="Number">
<xsl:value-of select="Cflatallin"/>
</Data>
</Cell>
<Cell ss:StyleID="s70">
<Data ss:Type="Number">
<xsl:value-of select="CRPMallin"/>
</Data>
</Cell>
</Row>
</xsl:for-each>
<Row>
<Cell ss:Index="2" ss:MergeAcross="27" ss:StyleID="s87">
<Data ss:Type="String"
x:Ticked="1">-- End of Report --</Data>
</Cell>
</Row>
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<PageSetup>
<Layout x:Orientation="Landscape"/>
<PageMargins x:Left="0.17" x:Right="0.23"/>
</PageSetup>
<FitToPage/>
<Print>
<ValidPrinterInfo/>
<Scale>92</Scale>
<HorizontalResolution>600</HorizontalResolution>
<VerticalResolution>600</VerticalResolution>
</Print>
<Selected/>
<LeftColumnVisible>6</LeftColumnVisible>
<Panes>
<Pane>
<Number>3</Number>
<ActiveRow>29</ActiveRow>
<ActiveCol>10</ActiveCol>
</Pane>
</Panes>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
</Workbook>
</xsl:template>
</xsl:stylesheet>
Creating the Report DS
The method below will return a DataSet
for dynamic XML generation. First of all, you have to create a new DataSet
. Assign the connection string from the web.config. Set the connection object and execute the command through the Stored Procedure "usp_GetReportDetails
". Fill the DataSet ds
and return the DataSet
.
public DataSet GetReportDS()
{
DataSet ds = new DataSet();
string connectionString =
System.Configuration.ConfigurationManager.ConnectionStrings[1].ConnectionString;
try
{
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand command = new SqlCommand();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "usp_GetReportDetails";
command.Connection = connection;
SqlDataAdapter adapter = new SqlDataAdapter(command);
adapter.Fill(ds);
}
catch { }
return ds;
}
Problems and Solutions
- Tokenizer error
Sometimes you will get a tokenizer error. This is because of an invalid XSLT file or due to a syntax error in the XSLT file.
- Error occurred in the following object: Table
This occurs due to XSLT syntax errors.
Running the Demo Project
Download and extract the project zip file. In order to run the project, you have to install Visual Studio 2005 and SQL Server 2005. Restore the database back up and set the connection string in the web.config file. Now the project is ready to run. Happy coding.