In this article I will explain with an example, how we can create charts in Excel File programmatically from code using ASP.Net Chart Control with C# and VB.Net.
The Chart control can be easily populated from database using DataTable.
HTML Markup
Below is the HTML Markup of the page in which I have an ASP.Net Chart control using which I’ll create 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
You will need to do the following Web.Config file modifications marked in Yellow.
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
Below is the code to populate a Pie chart from a DataTable which has been filled with 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
Below is the event handler that will export the ASP.Net Chart control to Excel Sheet using ASP.Net. Firstly I need to render the Chart control and fetch the URL of the Chart Image. Then I convert the Relative URL of the chart image as Absolute (This is very necessary as otherwise the Excel file will not show chart image).
In the next step I make use of ASP.Net table control to which I add a Label control as Caption of the Chart and the HTML string of the chart image that I have fetched from the Chart control.
Finally the table is rendered as HTML string and is exported to Excel sheet.
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
![Dynamically Create Charts in Excel File using ASP.Net Chart Control using C# and VB.Net]()
Demo
Downloads
