Click here to Skip to main content
11,436,179 members (63,426 online)
Click here to Skip to main content

Excel Export Component Using XSL

, 22 Oct 2006 CPOL
Rate this:
Please Sign up or sign in to vote.
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.

License

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

Share

About the Author

Patrick.Alex
Web Developer
United States United States
No Biography provided

Comments and Discussions

 
QuestionExporttoExcel dll not working in .net 4.0 Pin
Milind Panchal30-Oct-12 22:22
memberMilind Panchal30-Oct-12 22:22 
AnswerRe: ExporttoExcel dll not working in .net 4.0 Pin
Harry317331-Oct-12 3:32
memberHarry317331-Oct-12 3:32 
GeneralAlternative Pin
FilipKrnjic9-Jul-09 2:19
memberFilipKrnjic9-Jul-09 2:19 
GeneralRe: Alternative Pin
Almatrodi6-Aug-09 19:53
memberAlmatrodi6-Aug-09 19:53 
GeneralSpaces on Column Name Pin
carlos cisneros22-Sep-08 14:46
membercarlos cisneros22-Sep-08 14:46 
GeneralRe: Spaces on Column Name Pin
Patrick.Alex26-Sep-08 12:45
memberPatrick.Alex26-Sep-08 12:45 
GeneralRe: Spaces on Column Name Pin
carlos cisneros26-Sep-08 13:18
membercarlos cisneros26-Sep-08 13:18 
QuestionHow to modify the data and convert back to dataset? Pin
laputa42229-Jul-08 18:12
memberlaputa42229-Jul-08 18:12 
GeneralWhen i do the expamle 1 will still encouter an error Pin
Jackielu20048-Jul-08 19:08
memberJackielu20048-Jul-08 19:08 
GeneralRe: When i do the expamle 1 will still encouter an error Pin
dgulliver10-Jul-08 5:17
memberdgulliver10-Jul-08 5:17 
GeneralError With AddExcelSheetToExcelTemplate Function Specifically COM Class Factory Pin
Emmanuel Process10-Jun-08 10:46
memberEmmanuel Process10-Jun-08 10: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 12:37
memberwyx20008-Apr-08 12:37 
GeneralRe: export grid to a excel in html format is easy, the problem is how to export to a real Excel. Pin
BarbaMariolino12-May-08 2:09
memberBarbaMariolino12-May-08 2:09 
Generalsame code but in C# Pin
Walaza16-Mar-08 21:24
memberWalaza16-Mar-08 21:24 
GeneralRe: same code but in C# Pin
Patrick.Alex18-Mar-08 5:33
memberPatrick.Alex18-Mar-08 5:33 
GeneralRe: same code but in C# Pin
Walaza18-Mar-08 5:59
memberWalaza18-Mar-08 5:59 
QuestionUrget! Example2: XmlDataDocument(DataTable) instead of XmlDataDocument(DataSet) Pin
KhurramJalil3-Nov-07 2:14
memberKhurramJalil3-Nov-07 2:14 
AnswerRe: Urget! Example2: XmlDataDocument(DataTable) instead of XmlDataDocument(DataSet) Pin
Emmanuel Process10-Jun-08 10:59
memberEmmanuel Process10-Jun-08 10:59 
GeneralCool ! Pin
batisparrow24-Sep-07 19:57
memberbatisparrow24-Sep-07 19:57 
GeneralRed Alert! A serious Problem Pin
ivanchain@hotmail.com13-Aug-07 18:26
memberivanchain@hotmail.com13-Aug-07 18:26 
You do a great job and it works fine when exporting to an excel files. But I found there is a very serious problem about the files produced.

The excel File produced by your componnent IS NOT REAL excel file. It's just something like a html(I think). To check this, you could open the file by Excel and chose SAVEAS, then you will see that the default option of file type to save is HTML, not XLS.

This is seem to be no problem, but in fact it will cause a big problem when we want to import the file back to a dataset again.

You could try any way to read the file as an excel file, by JET OLEDB, or by ODBC, just like:

"Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + _strExcelFilename + ";" +
"Extended Properties=" + Convert.ToChar(34).ToString() + "Excel 8.0;"+ ExcelConnectionOptions() + Convert.ToChar(34).ToString();

something like that.

Then you will get an error: the file's type is not needed. It's NOT be seen as an EXCEL file. To solve this problem, you could just open the file by excel, and chose saveAS, chose it's type from html to xls, and save. The new file you saved could be open as an excel files well.

Thanks, please have a look about this, could you do some modify to create a REAL Excel File?



123

GeneralRe: Red Alert! A serious Problem Pin
Patrick.Alex14-Aug-07 9:34
memberPatrick.Alex14-Aug-07 9:34 
GeneralAbout String Format Pin
Yu Houfang20-Jul-07 7:13
memberYu Houfang20-Jul-07 7:13 
QuestionExportToExcel.ExcelExport Pin
kingyan198518-Jun-07 23:53
memberkingyan198518-Jun-07 23:53 
QuestionValues with spaces Pin
nothinking5-Jun-07 11:17
membernothinking5-Jun-07 11:17 
AnswerRe: Values with spaces Pin
Patrick.Alex6-Jun-07 17:16
memberPatrick.Alex6-Jun-07 17:16 
GeneralRe: Values with spaces Pin
nothinking12-Jul-07 7:10
membernothinking12-Jul-07 7:10 
GeneralRe: Values with spaces Pin
Patrick.Alex20-Jul-07 20:39
memberPatrick.Alex20-Jul-07 20:39 
GeneralDeletes Files Pin
Jim_Hewitt2-May-07 13:02
memberJim_Hewitt2-May-07 13:02 
QuestionC# Code Pin
pratap ankesh3-Apr-07 4:06
memberpratap ankesh3-Apr-07 4:06 
AnswerRe: C# Code Pin
Patrick.Alex3-Apr-07 8:23
memberPatrick.Alex3-Apr-07 8:23 
GeneralMultiple Sheet Pin
h_raj20022-Apr-07 11:50
memberh_raj20022-Apr-07 11:50 
GeneralRe: Multiple Sheet Pin
pratap ankesh3-Apr-07 3:57
memberpratap ankesh3-Apr-07 3:57 
GeneralExample 3 Pin
olmos28-Mar-07 11:03
memberolmos28-Mar-07 11:03 
GeneralRe: Example 3 Pin
Patrick.Alex29-Mar-07 0:25
memberPatrick.Alex29-Mar-07 0:25 
GeneralFiles on server Pin
MrHemli27-Mar-07 14:50
memberMrHemli27-Mar-07 14:50 
GeneralRe: Files on server Pin
Patrick.Alex27-Mar-07 21:55
memberPatrick.Alex27-Mar-07 21:55 
GeneralRe: Files on server Pin
MrHemli12-Apr-07 1:36
memberMrHemli12-Apr-07 1:36 
GeneralRe: Files on server Pin
Emmanuel Process10-Jun-08 10:26
memberEmmanuel Process10-Jun-08 10:26 
GeneralDisplayRightToLeft = False Pin
MrHemli24-Mar-07 14:52
memberMrHemli24-Mar-07 14:52 
GeneralRe: DisplayRightToLeft = False Pin
Patrick.Alex27-Mar-07 5:04
memberPatrick.Alex27-Mar-07 5:04 
GeneralRe: DisplayRightToLeft = False Pin
MrHemli27-Mar-07 5:42
memberMrHemli27-Mar-07 5:42 
GeneralRe: DisplayRightToLeft = False Pin
Patrick.Alex27-Mar-07 21:57
memberPatrick.Alex27-Mar-07 21:57 
Generalnull reference exception Pin
FaizulBari16-Mar-07 3:14
memberFaizulBari16-Mar-07 3:14 
GeneralRe: null reference exception Pin
Patrick.Alex19-Mar-07 20:23
memberPatrick.Alex19-Mar-07 20:23 
GeneralRe: null reference exception Pin
lastactionman9-May-07 4:07
memberlastactionman9-May-07 4:07 
GeneralRe: null reference exception Pin
Patrick.Alex9-May-07 14:45
memberPatrick.Alex9-May-07 14:45 
QuestionXPath Exception Pin
mcclurec7-Mar-07 7:31
membermcclurec7-Mar-07 7:31 
AnswerRe: XPath Exception Pin
Patrick.Alex7-Mar-07 18:46
memberPatrick.Alex7-Mar-07 18:46 
Questionhow to merge a new XML document in same XSLT Pin
Member #10604614-Mar-07 4:06
memberMember #10604614-Mar-07 4:06 
AnswerRe: how to merge a new XML document in same XSLT Pin
Patrick.Alex4-Mar-07 22:32
memberPatrick.Alex4-Mar-07 22:32 

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

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

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.150428.2 | Last Updated 23 Oct 2006
Article Copyright 2006 by Patrick.Alex
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid