Excel Export Component Using XSL






4.55/5 (32 votes)
This is an article on an Excel export component (using XSL and XML) in .NET.
- Download DLL (.NET 2.0) - 7.25 Kb
- Download source code (.NET 2.0) - 13.6 Kb
- Download demo source code (.NET 2.0) - 36.7 Kb
- Download DLL (.NET 1.1) - 5.74 Kb
- Download source code (.NET 1.1) - 12 Kb
Introduction
I have created an Excel Export component using XSL and XML which can produce very well formatted Excel files. This doesn't require any Excel library to create Excel files without charts and macros, but if you want charts or macros in the Excel, then you have to have Excel installed on the machine. I will explain this using three examples.
- Export a
DataTable
into an Excel sheet which will not have any formatting. - Export an
XMLDataDocument
(has data from aDataTable
and has a few tags added to it) into an Excel sheet which will have a lot of cool formatting. - Export an
XMLDataDocument
(has data from aDataTable
and has a few tags added to it) into an Excel sheet which will have charts and a lot of cool formatting.
Using the code
Before we go into the examples, let me tell you the functions and properties that are exposed as public in this library.
Functions
TransformDataTableToExcel
- has three overloads, it transforms theDataTable
s to Excel filesTransformXMLDocumentToExcel
- transforms theXMLDataDocument
s to Excel filesAddExcelSheetToExcelTemplate
- has four overloads, adds a Excel sheet (from an Excel Work book) to an Excel TemplateSendExcelToClient
- Sends the file to the client as an attachment.CleanUpTemporaryFiles
- Cleans up the temporary files created in the previous requests.
Properties
TempFolder
- Path of the folder in which the temporary Excel files would be created.TemplateFolder
- Path of the folder in which the template Excel files are saved.XSLStyleSheetFolder
- Path of the folder in which the XSL stylesheets are saved.
Let us see the examples
We would be using VB.NEt to write our code.Example - 1
In this example, we export a DataTable
into an Excel sheet which will not have any formatting.
'The Excel object is declared at the page level
Dim objExport As ExportToExcel.ExcelExport
Protected Sub Page_Load(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles Me.Load
'The Excel object is initialized
objExport = New ExportToExcel.ExcelExport
'The Folders the excel obejct would be using is set
objExport.TempFolder = "\Excel\Temp\"
objExport.TemplateFolder = "\Excel\Template\"
objExport.XSLStyleSheetFolder = "\Excel\XSLStyleSheet\"
'The CleanUpTemporaryFiles is called so that
'files created previously are destroyed.
objExport.CleanUpTemporaryFiles()
End Sub
Protected Sub cmdExport_Click(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles cmdExport.Click
Dim strSql As String
Dim strCon As String
Dim strExcelFile As String
Dim dsOrders As DataSet
Try
'Get the data from the database
strSql = "select ord.orderid,ord.EmployeeID, " & _
"ordDet.ProductID, ordDet.UnitPrice, " & _
"ordDet.Quantity, "
strSql = strSql & " ordDet.Discount from " & _
"orders ord inner join ""order details"" ordDet "
strSql = strSql & "on ord.orderid = " & _
"ordDet.orderid where customerID" & _
" = 'ALFKI' order by ord.orderid"
strCon = _
System.Configuration.ConfigurationManager.AppSettings("ConStr")
dsOrders = SqlHelper.ExecuteDataset(strCon, _
CommandType.Text, strSql)
'Transform the data from the datatable into excel.
'This function would return the name of
'the Excel file created.
strExcelFile = _
objExport.TransformDataTableToExcel(_
dsOrders.Tables(0), True)
'send the excel file to the client
objExport.SendExcelToClient(strExcelFile)
Catch ex As Threading.ThreadAbortException
'Do nothing
Catch ex As Exception
Response.Write(ex.ToString)
End Try
End Sub
The Excel file generated from this example is as shown below:
Example - 2
In this example, we export an XMLDataDocument
(has the data from a DataSet
and has a few tags added to it) into an Excel sheet which will have a lot of cool formatting.
'The Excel object is declared at the page level
Dim objExport As ExportToExcel.ExcelExport
Protected Sub Page_Load(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles Me.Load
'The Excel object is initialized
objExport = New ExportToExcel.ExcelExport
'The Folders the excel obejct would be using is set
objExport.TempFolder = "\Excel\Temp\"
objExport.TemplateFolder = "\Excel\Template\"
objExport.XSLStyleSheetFolder = "\Excel\XSLStyleSheet\"
'The CleanUpTemporaryFiles is called so
'that files created previously are destroyed.
objExport.CleanUpTemporaryFiles()
End Sub
Protected Sub cmdExport_Click(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles cmdExport.Click
Dim strSql As String
Dim strCon As String
Dim strExcelFile As String
Dim dsOrders As DataSet
Dim XMLDoc As XmlDataDocument
Dim objNewNode As XmlNode
Dim objFrstNode As XmlNode
Try
'Get the data from the database
strSql = "select ord.orderid,ord.EmployeeID, " & _
"ordDet.ProductID, ordDet.UnitPrice, " & _
"ordDet.Quantity, "
strSql = strSql & " ordDet.Discount from " & _
"orders ord inner join ""order details"" ordDet "
strSql = strSql & "on ord.orderid = " & _
"ordDet.orderid where customerID" & _
" = 'ALFKI' order by ord.orderid"
strCon = _
System.Configuration.ConfigurationManager.AppSettings("ConStr")
dsOrders = SqlHelper.ExecuteDataset(strCon, _
CommandType.Text,strSql)
'Create the XML Data Document from the dataset.
XMLDoc = New XmlDataDocument(dsOrders)
'Add Additional information that has to be
'displayed in the Excel into the XML Document.
objNewNode = XMLDoc.CreateElement("CustomerDetails")
objNewNode.InnerXml = "<CustomerId>ALFKI</" & _
"CustomerId><CustomerNm>Alfreds " & _
"Futterkiste</CustomerNm> " & _
"<ContactNm>Maria Anders" & _
"</ContactNm><City>Berlin</City>"
XMLDoc.DataSet.EnforceConstraints = False
objFrstNode = XMLDoc.DocumentElement.FirstChild
XMLDoc.DocumentElement.InsertBefore(objNewNode, _
objFrstNode)
'Transform the data from the datatable into excel.
'This function would return the name of
'the Excel file created. Here we are using a XSL
'file to define the structure of the Excel file.
strExcelFile = _
objExport.TransformXMLDocumentToExcel(XMLDoc, _
"Example2.xsl")
'send the excel file to the client
objExport.SendExcelToClient(strExcelFile)
Catch ex As Threading.ThreadAbortException
'Do nothing
Catch ex As Exception
Response.Write(ex.ToString)
End Try
End Sub
In this example, we are using an XSL file to define the structure of the Excel file. Click here to download the zipped XSL file. Explaining XSL is out of the scope of this article. If you want to learn more about it, click here.
If you know how to create tables in HTML, you can easily understand it. It is as simple as that.
The Excel file generated from this example is as below:
Example - 3
In this example, we export an XMLDataDocument
(has data from a DataTable
and has a few tags added to it) into an Excel sheet which will have charts and a lot of cool formatting.
Code in the web page:
'The Excel object is declared at the page level
Dim objExport As ExportToExcel.ExcelExport
Protected Sub Page_Load(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles Me.Load
'The Excel object is initialized
objExport = New ExportToExcel.ExcelExport
'The Folders the excel obejct would be using is set
objExport.TempFolder = "\Excel\Temp\"
objExport.TemplateFolder = "\Excel\Template\"
objExport.XSLStyleSheetFolder = "\Excel\XSLStyleSheet\"
'The CleanUpTemporaryFiles is called so that
'files created previously are destroyed.
objExport.CleanUpTemporaryFiles()
End Sub
Protected Sub cmdExport_Click(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles cmdExport.Click
Dim strSql As String
Dim strCon As String
Dim strExcelFile As String
Dim dsOrders As DataSet
Dim XMLDoc As XmlDataDocument
Try
'Get the data from the database
strSql = "select ord.customerID, sum((ordDet.UnitPrice" & _
" * (ordDet.Discount / 100)) * ordDet.Quantity)"
strSql = strSql & " as Order_Amount from orders " & _
"ord inner join ""order details"""
strSql = strSql & "ordDet on ord.orderid = _
ordDet.orderid group by ord.customerID "
strCon = _
System.Configuration.ConfigurationManager.AppSettings("ConStr")
dsOrders = SqlHelper.ExecuteDataset(strCon, _
CommandType.Text, strSql)
'Create the XML Data Document from the dataset.
XMLDoc = New XmlDataDocument(dsOrders)
'Transform the data from the datatable into excel.
'This function would return the name of the
'Excel file created. Here we are using a XSL file
'to define the structure of the Excel file.
strExcelFile = _
objExport.TransformXMLDocumentToExcel(XMLDoc, _
"Example3.xsl")
'add the excel sheet in the work book returned
'from the TransformXMLDocumentToExcel function
'to the Excel Template.
strExcelFile = _
objExport.AddExcelSheetToExcelTemplate(strExcelFile, _
"Example3.xls")
'send the excel file to the client
objExport.SendExcelToClient(strExcelFile)
Catch ex As Threading.ThreadAbortException
'Do nothing
Catch ex As Exception
Response.Write(ex.ToString)
End Try
End Sub
In this example, we are using an XSL file to define the structure of the Excel file. Click here to download the zipped XSL file. Once we get the Excel file from the TransformXMLDocumentToExcel
function, we add the Excel sheet (say Temp) in this Workbook to the Excel Template and the send it to the client. The Excel macros that are there in the template would populate the chart and copy the data from the Temp sheet to the sheet that contains the chart.
We are also using an Excel template in which the chart object is saved.
The Excel file generated from this example is as below:
Points of Interest
- When you want to use this in an application which does not use impersonation, your default ASP.NET account has to have "write" access to the "Temp" folder, in which the Excel files would be created.
- The following link has the Hex code for Excel colors: Excel colors.
History
- 2006-08-02: Article created.
- 2006-10-23: Added source code and DLL for .NET 1.1.