Format Excel file using XSLT, XslCompiledTransform

See the sample code for creating an Excel file formatted with the help of XSLT file.
------------------------------------------------------------------------------------


private void button1_Click(object sender, EventArgs e)
{
ExportToExcel(GetDataSet(), "Employees.xslt", "c:\\Employees.xls");
}

Method to create a dataset for writing to excel
---------------------------------------------------------

private DataSet GetDataSet()
{
DataSet ds = new DataSet();
DataTable dtEmployees = new DataTable("Employees");
DataColumn colEmpID = new DataColumn("EmpID");
DataColumn colEmpName = new DataColumn("EmpName");
DataColumn colAge = new DataColumn("Age");
DataColumn colLocation = new DataColumn("Location");
DataColumn colDesignation = new DataColumn("Designation");
dtEmployees.Columns.AddRange(new DataColumn[] { colEmpID, colEmpName, colAge, colLocation, colDesignation });

DataRow dr = dtEmployees.NewRow();
dr[colEmpID] = "E001";
dr[colEmpName] = "George";
dr[colAge] = "45";
dr[colLocation] = "USA";
dr[colDesignation] = "PM";
dtEmployees.Rows.Add(dr);

dr = dtEmployees.NewRow();
dr[colEmpID] = "E002";
dr[colEmpName] = "James";
dr[colAge] = "30";
dr[colLocation] = "INDIA";
dr[colDesignation] = "TL";
dtEmployees.Rows.Add(dr);

dr = dtEmployees.NewRow();
dr[colEmpID] = "E002";
dr[colEmpName] = "Mary";
dr[colAge] = "35";
dr[colLocation] = "UK";
dr[colDesignation] = "Developer";
dtEmployees.Rows.Add(dr);

ds.Tables.Add(dtEmployees);
return ds;
}


Method to create a XSLT formatted excel file
---------------------------------------------------------

public bool ExportToExcel(DataSet ds, string xsltPath, string pathToSave)
{
try
{
XmlDataDocument xmlDoc = new XmlDataDocument();
xmlDoc.LoadXml(ds.GetXml());
XslCompiledTransform xsl = new XslCompiledTransform();
xsl.Load(xsltPath);
StreamWriter strWriter = new StreamWriter(pathToSave);
xsl.Transform(xmlDoc, null, strWriter);
strWriter.Flush();
return true;
}
catch (Exception ex)
{
throw ex;
}
}


Save the below mentioned XSLT in a file with name "Employees.xslt" in "C" drive to run the sample
---------------------------------------------------------

<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"
xmlns:ms="urn:schemas-microsoft-com:xslt"
>
<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:FontName="Arial" ss:Size="10" ss:Bold="1"/>
<Alignment ss:Horizontal="Left" ss:Vertical="Bottom"/>
</Style>
<Style ss:ID="s22">
<Font ss:FontName="MS Sans Serif" ss:Size="10"/>
<Alignment ss:Horizontal="Left" ss:Vertical="Bottom"/>
</Style>
<Style ss:ID="s25">
<Font ss:Color="Red" ss:FontName="MS Sans Serif" ss:Size="10"/>
<Alignment ss:Horizontal="Left" ss:Vertical="Bottom"/>
</Style>
</Styles>
<Worksheet>
<xsl:attribute name="ss:Name">Employees List</xsl:attribute>
<Table>
<Column ss:Width="50"/>
<Column ss:Width="50"/>
<Column ss:Width="30"/>
<Column ss:Width="90"/>
<xsl:apply-templates select="NewDataSet"/>
<Row>
<Cell ss:StyleID="s21">
<Data ss:Type="String">Name</Data>
</Cell>
<Cell ss:StyleID="s21">
<Data ss:Type="String">Employee ID</Data>
</Cell>
<Cell ss:StyleID="s21">
<Data ss:Type="String">Age</Data>
</Cell>
<Cell ss:StyleID="s21">
<Data ss:Type="String">Location</Data>
</Cell>
<Cell ss:StyleID="s21">
<Data ss:Type="String">Designation</Data>
</Cell>
</Row>
<xsl:for-each select="Employees">
<Row>
<Cell ss:StyleID="s22">
<Data ss:Type="String">
<xsl:value-of select="EmpID"/>
</Data>
</Cell>
<Cell ss:StyleID="s22">
<Data ss:Type="String">
<xsl:value-of select="EmpName"/>
</Data>
</Cell>
<Cell ss:StyleID="s22">
<Data ss:Type="String">
<xsl:value-of select="Age"/>
</Data>
</Cell>
<Cell ss:StyleID="s22">
<Data ss:Type="String">
<xsl:value-of select="Location"/>
</Data>
</Cell>
<xsl:choose>
<xsl:when test="Designation='PM'">
<Cell ss:StyleID="s25">
<Data ss:Type="String">
<xsl:value-of select="Designation"/>
</Data>
</Cell>
</xsl:when>
<xsl:otherwise>
<Cell ss:StyleID="s22">
<Data ss:Type="String">
<xsl:value-of select="Designation"/>
</Data>
</Cell>
</xsl:otherwise>
</xsl:choose>
</Row>
</xsl:for-each>
</Table>
</Worksheet>
</Workbook>
</xsl:template>
</xsl:stylesheet>