Click here to Skip to main content
15,867,308 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

 
Questiondoesn't work Pin
Ahmed.mb2-Mar-07 2:28
Ahmed.mb2-Mar-07 2:28 
AnswerRe: doesn't work Pin
Patrick.Alex4-Mar-07 21:38
Patrick.Alex4-Mar-07 21:38 
GeneralRe: doesn't work Pin
Ahmed.mb12-Mar-07 15:30
Ahmed.mb12-Mar-07 15:30 
QuestionExcel Gridlines Pin
abentov9-Jan-07 4:51
abentov9-Jan-07 4:51 
QuestionAny solution for : Unexpected token '' in the expression error? Pin
k_peter29-Dec-06 20:04
k_peter29-Dec-06 20:04 
AnswerRe: Any solution for : Unexpected token '' in the expression error? Pin
k_peter29-Dec-06 20:22
k_peter29-Dec-06 20:22 
Questionthe image could not be show Pin
leejs41320-Dec-06 13:53
leejs41320-Dec-06 13:53 
GeneralProblem objExport.TransformDataTableToExcel(objDataSet_QDMP.Tables(0), True) Pin
willlins200616-Dec-06 0:46
willlins200616-Dec-06 0:46 
GeneralRe: Problem objExport.TransformDataTableToExcel(objDataSet_QDMP.Tables(0), True) Pin
Patrick.Alex17-Dec-06 21:27
Patrick.Alex17-Dec-06 21:27 
Generalwithout temp file/folder. Pin
zawmn8315-Dec-06 19:50
zawmn8315-Dec-06 19:50 
GeneralRe: without temp file/folder. Pin
Patrick.Alex17-Dec-06 21:39
Patrick.Alex17-Dec-06 21:39 
GeneralExport excel data to XML Pin
mitragotri17-Nov-06 1:33
mitragotri17-Nov-06 1:33 
GeneralRe: Export excel data to XML Pin
mitragotri17-Nov-06 20:00
mitragotri17-Nov-06 20:00 
GeneralRe: Export excel data to XML Pin
mitragotri17-Nov-06 20:09
mitragotri17-Nov-06 20:09 
GeneralColumn headers vs ColumnName Pin
mmilo7514-Nov-06 0:42
mmilo7514-Nov-06 0:42 
Generalexport to excel problem Pin
miladgaibar18-Sep-06 22:15
miladgaibar18-Sep-06 22:15 
GeneralDate Field issue Pin
Jossao4-Sep-06 23:02
Jossao4-Sep-06 23:02 
GeneralRe: Date Field issue Pin
Patrick.Alex6-Sep-06 21:00
Patrick.Alex6-Sep-06 21:00 
GeneralRe: Date Field issue Pin
Jossao12-Sep-06 6:49
Jossao12-Sep-06 6:49 
GeneralRe: Date Field issue [modified] Pin
Patrick.Alex14-Sep-06 23:50
Patrick.Alex14-Sep-06 23:50 
QuestionUnexpected token '' in the expression. Pin
MikeHollibaugh28-Aug-06 11:41
MikeHollibaugh28-Aug-06 11:41 
AnswerRe: Unexpected token '' in the expression. Pin
Patrick.Alex30-Aug-06 22:00
Patrick.Alex30-Aug-06 22:00 
Questionwhen the colum name contains blank?? Pin
steven_wong23-Aug-06 4:47
steven_wong23-Aug-06 4:47 
AnswerRe: when the colum name contains blank?? Pin
Patrick.Alex23-Aug-06 19:35
Patrick.Alex23-Aug-06 19:35 
GeneralNet 1.1 version Pin
imurphy21-Aug-06 21:51
imurphy21-Aug-06 21:51 

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.