65.9K
CodeProject is changing. Read more.
Home

Excel Export Component Using XSL

Patrick.Alex
starIconstarIconstarIconstarIcon
emptyStarIcon
starIcon

4.55/5 (32 votes)

Aug 2, 2006

CPOL

3 min read

viewsIcon

389829

downloadIcon

12266

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. 

'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.

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

'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.