In this article I will explain with an example, how to export ASP.Net Chart Control (Pie Chart) to Excel File with Caption using C# and VB.Net.
HTML Markup
The HTML Markup consists of an ASP.Net Chart control for creating a Pie Chart and a Button to trigger the Export to Excel process.
<asp:Label ID="Label1" runat="server" Text="Fruits Distribution (India)" ForeColor = "Red"></asp:Label>
<br />
<asp:Chart ID="Chart1" runat="server" Height="300px" Width="400px">
<Titles>
<asp:Title ShadowOffset="3" Name="Items" />
</Titles>
<Legends>
<asp:Legend Alignment="Center" Docking="Bottom" IsTextAutoFit="False" Name="Default"
LegendStyle="Row" />
</Legends>
<Series>
<asp:Series Name="Default" />
</Series>
<ChartAreas>
<asp:ChartArea Name="ChartArea1" BorderWidth="0" />
</ChartAreas>
</asp:Chart>
<br />
<asp:Button ID="btnExportExcel" runat="server" Text="Export to Excel" OnClick="btnExportExcel_Click" />
Web.Config File Modifications
The following changes marked in Yellow needs to be done in the Web.Config file.
Note: The properties deleteAfterServicing and privateImages are most important in case of export to Excel as otherwise in Excel File you will not see the Chart Image. Thus make sure these properties are always set to false.
<configuration>
<appSettings>
<add key="ChartImageHandler" value="storage=file;timeout=20;deleteAfterServicing=false;privateImages=false" />
</appSettings>
<system.web>
<compilation debug="true" targetFramework="4.0">
<assemblies>
<add assembly="System.Web.DataVisualization, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
</assemblies>
</compilation>
<httpHandlers>
<add path="ChartImg.axd" verb="GET,HEAD,POST" type="System.Web.UI.DataVisualization.Charting.ChartHttpHandler, System.Web.DataVisualization, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" validate="false"/>
</httpHandlers>
<pages>
<controls>
<add tagPrefix="asp" namespace="System.Web.UI.DataVisualization.Charting" assembly="System.Web.DataVisualization, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/>
</controls>
</pages>
</system.web>
<system.webServer>
<handlers>
<remove name="ChartImageHandler" />
<add name="ChartImageHandler" preCondition="integratedMode" verb="GET,HEAD,POST"
path="ChartImg.axd" type="System.Web.UI.DataVisualization.Charting.ChartHttpHandler, System.Web.DataVisualization, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />
</handlers>
<validation validateIntegratedModeConfiguration="false" />
</system.webServer>
</configuration>
Namespaces
You will need to import the following namespaces.
C#
using System.IO;
using System.Drawing;
using System.Text.RegularExpressions;
using System.Web.UI.DataVisualization.Charting;
VB.Net
Imports System.IO
Imports System.Drawing
Imports System.Text.RegularExpressions
Imports System.Web.UI.DataVisualization.Charting
Populating the Pie Chart from DataTable using ASP.Net Chart Control
Inside the Page Load event, the Pie Chart will be created using some sample data.
C#
protected void Page_Load(object sender, EventArgs e)
{
string[] x = new string[4] { "Mango", "Apple", "Orange", "Banana" };
int[] y = new int[4] { 200, 112, 55, 96 };
Chart1.Series[0].Points.DataBindXY(x, y);
Chart1.Series[0].ChartType = SeriesChartType.Pie;
Chart1.ChartAreas["ChartArea1"].Area3DStyle.Enable3D = true;
Chart1.Legends[0].Enabled = true;
}
VB.Net
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
Dim x() As String = New String() {"Mango", "Apple", "Orange", "Banana"}
Dim y() As Integer = New Integer() {200, 112, 55, 96}
Chart1.Series(0).Points.DataBindXY(x, y)
Chart1.Series(0).ChartType = SeriesChartType.Pie
Chart1.ChartAreas("ChartArea1").Area3DStyle.Enable3D = True
Chart1.Legends(0).Enabled = True
End Sub
Export ASP.Net Chart to Excel Worksheet in ASP.Net
When the Export Button is clicked, first the Chart is rendered as HTML and then the Image URL of the Chart Image is extracted from the HTML using Regular Expressions.
Then a dynamic ASP.Net Table is created and a Label for displaying Caption is added to it. Then an HTML Image element is added to the Table using Literal control.
Finally the ASP.Net Table is rendered as HTML string is exported and downloaded as Excel file.
C#
protected void btnExportExcel_Click(object sender, EventArgs e)
{
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition", "attachment;filename=ChartExport.xls");
Response.ContentType = "application/vnd.ms-excel";
Response.Charset = "";
StringWriter sw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(sw);
Chart1.RenderControl(hw);
string src = Regex.Match(sw.ToString(), "<img.+?src=[\"'](.+?)[\"'].+?>", RegexOptions.IgnoreCase).Groups[1].Value;
string img = string.Format("<img src = '{0}{1}' />", Request.Url.GetLeftPart(UriPartial.Authority), src);
Table table = new Table();
TableRow row = new TableRow();
row.Cells.Add(new TableCell());
row.Cells[0].Width = 200;
row.Cells[0].HorizontalAlign = HorizontalAlign.Center;
row.Cells[0].Controls.Add(new Label { Text = "Fruits Distribution (India)", ForeColor = Color.Red });
table.Rows.Add(row);
row = new TableRow();
row.Cells.Add(new TableCell());
row.Cells[0].Controls.Add(new Literal { Text = img });
table.Rows.Add(row);
sw = new StringWriter();
hw = new HtmlTextWriter(sw);
table.RenderControl(hw);
Response.Write(sw.ToString());
Response.Flush();
Response.End();
}
VB.Net
Protected Sub btnExportExcel_Click(sender As Object, e As EventArgs)
Response.Clear()
Response.Buffer = True
Response.AddHeader("content-disposition", "attachment;filename=ChartExport.xls")
Response.ContentType = "application/vnd.ms-excel"
Response.Charset = ""
Dim sw As New StringWriter()
Dim hw As New HtmlTextWriter(sw)
Chart1.RenderControl(hw)
Dim src As String = Regex.Match(sw.ToString(), "<img.+?src=[""'](.+?)[""'].+?>", RegexOptions.IgnoreCase).Groups(1).Value
Dim table As String = "<table><tr><td><img src='{0}' /></td></tr></table>"
table = String.Format(table, Request.Url.GetLeftPart(UriPartial.Authority) + src)
Response.Write(table)
Response.Flush()
Response.End()
End Sub
Screenshot
![Export ASP.Net Chart Control to Excel File with Caption using C# and VB.Net]()
Demo
Downloads
