Click here to Skip to main content
15,867,453 members
Articles / Web Development / ASP.NET
Article

Excel Export Component Using XSL

Rate me:
Please Sign up or sign in to vote.
4.55/5 (34 votes)
22 Oct 2006CPOL3 min read 373.2K   12.2K   165   90
This is an article on an Excel export component (using XSL and XML) in .NET.

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 a DataTable 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 a DataTable 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 the DataTables to Excel files
  • TransformXMLDocumentToExcel - transforms the XMLDataDocuments to Excel files
  • AddExcelSheetToExcelTemplate - has four overloads, adds a Excel sheet (from an Excel Work book) to an Excel Template
  • SendExcelToClient - 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.

VB
'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:

Sample image

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.

VB
'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:

Sample image

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:

VB
'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:

Sample image

Points of Interest

  1. 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.
  2. 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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Web Developer
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionExporttoExcel dll not working in .net 4.0 Pin
Milind Panchal30-Oct-12 21:22
Milind Panchal30-Oct-12 21:22 
AnswerRe: ExporttoExcel dll not working in .net 4.0 Pin
Harry317331-Oct-12 2:32
Harry317331-Oct-12 2:32 
GeneralAlternative Pin
FilipKrnjic9-Jul-09 1:19
FilipKrnjic9-Jul-09 1:19 
GeneralRe: Alternative Pin
Almatrodi6-Aug-09 18:53
Almatrodi6-Aug-09 18:53 
GeneralSpaces on Column Name Pin
carlos cisneros22-Sep-08 13:46
carlos cisneros22-Sep-08 13:46 
GeneralRe: Spaces on Column Name Pin
Patrick.Alex26-Sep-08 11:45
Patrick.Alex26-Sep-08 11:45 
GeneralRe: Spaces on Column Name Pin
carlos cisneros26-Sep-08 12:18
carlos cisneros26-Sep-08 12:18 
QuestionHow to modify the data and convert back to dataset? Pin
laputa42229-Jul-08 17:12
laputa42229-Jul-08 17:12 
GeneralWhen i do the expamle 1 will still encouter an error Pin
Jackielu20048-Jul-08 18:08
Jackielu20048-Jul-08 18:08 
GeneralRe: When i do the expamle 1 will still encouter an error Pin
dgulliver10-Jul-08 4:17
dgulliver10-Jul-08 4:17 
GeneralError With AddExcelSheetToExcelTemplate Function Specifically COM Class Factory Pin
Emmanuel Process10-Jun-08 9:46
Emmanuel Process10-Jun-08 9:46 
Generalexport grid to a excel in html format is easy, the problem is how to export to a real Excel. Pin
wyx20008-Apr-08 11:37
wyx20008-Apr-08 11:37 
GeneralRe: export grid to a excel in html format is easy, the problem is how to export to a real Excel. Pin
FilipKrnjic12-May-08 1:09
FilipKrnjic12-May-08 1:09 
Generalsame code but in C# Pin
Walaza16-Mar-08 20:24
Walaza16-Mar-08 20:24 
GeneralRe: same code but in C# Pin
Patrick.Alex18-Mar-08 4:33
Patrick.Alex18-Mar-08 4:33 
GeneralRe: same code but in C# Pin
Walaza18-Mar-08 4:59
Walaza18-Mar-08 4:59 
QuestionUrget! Example2: XmlDataDocument(DataTable) instead of XmlDataDocument(DataSet) Pin
KhurramJalil3-Nov-07 1:14
KhurramJalil3-Nov-07 1:14 
AnswerRe: Urget! Example2: XmlDataDocument(DataTable) instead of XmlDataDocument(DataSet) Pin
Emmanuel Process10-Jun-08 9:59
Emmanuel Process10-Jun-08 9:59 
GeneralCool ! Pin
batisparrow24-Sep-07 18:57
batisparrow24-Sep-07 18:57 
GeneralRed Alert! A serious Problem Pin
ivanchain@hotmail.com13-Aug-07 17:26
ivanchain@hotmail.com13-Aug-07 17:26 
GeneralRe: Red Alert! A serious Problem Pin
Patrick.Alex14-Aug-07 8:34
Patrick.Alex14-Aug-07 8:34 
GeneralAbout String Format Pin
Yu Houfang20-Jul-07 6:13
Yu Houfang20-Jul-07 6:13 
QuestionExportToExcel.ExcelExport Pin
kingyan198518-Jun-07 22:53
kingyan198518-Jun-07 22:53 
QuestionValues with spaces Pin
Danny.Su5-Jun-07 10:17
Danny.Su5-Jun-07 10:17 
AnswerRe: Values with spaces Pin
Patrick.Alex6-Jun-07 16:16
Patrick.Alex6-Jun-07 16:16 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.