Introduction

We have received quite a few requests from clients for the ability to export Chart's data into an Excel file format. This Add-On is built upon Chart's existing functionality of exporting its data to a DataSet. This exported DataSet is then written into an XML Stream and then a transormation is applied. The result of the transformation on the XML is an Excel data file in XML format.

The Windows Forms version of this Add-On saves files to the disk. However, the ASP.NET version of this Add-On can both save the data to disk and send the data as part of the HTTP response, where it will prompt the user to save, or open the file using Microsoft Excel. The code of this Add-On is designed for .NET 2.0.

How to use this Add-On

As this utility class is a static class, you only need to invoke its exportChart(..) method. Before this method is called, however, you also need to set its fileUrl property to a path that includes a directory location and a file name. Generally, the steps to use this Add-On are similar to the below.

1. Add the Add-On ExportToExcel file to your project. (In an ASP.NET website, this should go in the App_Code directory.)
2. Add the style sheet XMLToExcel.xslt to your project, or to the same location as your executable. In an ASP.NET website, add this file to the App_Code directory along with the class file

Once the previous steps have been completed, implementation using this utility class is fairly easy. Please note that the below code assumes that the namespace Dundas.Charting.Utilities has been referenced within the project.

Windows Forms

[C#]
  exportToExcel.fileUrl = @"C:\";
  exportToExcel.exportChart(this.chart1);

[VB.NET]
  exportToExcel.fileUrl = "C:\"
  exportToExcel.exportChart(Me.chart1)

ASP.NET

[C#]
  exportToExcel.fileUrl = @"~/temp/myExcelFile";
  exportToExcel.exportChart(this.Chart1, this,ExportToExcel.exportChartFormat.File);

[VB.NET]
  exportToExcel.fileUrl = "~/temp/myExcelFile"
  exportToExcel.exportChart(Me.Chart1,Me,ExportToExcel.exportChartFormat.File)

The signature of the method exportChart(..) differs between Windows Forms and ASP.NET. For ASP.NET, you must pass the current webpage as an argument if you would like to save the exported data in the Response. Additionally, it uses an enumeration to indicate where to save the Excel data: in the Response or in a file on the server.

Please note: In all cases you should only call this utility when Chart has been populated with data. If this utility is called when Chart does not have data the excel workbook will not be created correctly.