How to Create PieChart from Database and Export in Excel in Asp.Net

Description:-

Chart Control is used full for drawing char in Webpage we can fill data in chart control and display a graph in page so user can understand easily. Here we will draw chart in webpage from database so data will be from Sql server and draw a graph in page using AjaxControlToolKit and chart controls. Here we will create Pie Chart from Database and Export on button click event in Excel file. It’s pretty easy to Bind Chart from database and Export in Excel file. So let’s Start How to achieve this functionality in dot net.

Create publishers table in your database and fill some data.

CREATE TABLE [dbo].[publishers](
      [pub_id] [int] IDENTITY(1,1) NOT NULL,
      [pub_name] [varchar](50) NOT NULL,
      [state] [varchar](50) NOT NULL,
      [country] [varchar](50) NOT NULL,
      [Amounts] [bigint] NULL,
 CONSTRAINT [PK_publishers] PRIMARY KEY CLUSTERED
(
      [pub_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY]

GO

Default.aspx:-

<div>
   <asp:Chart ID="Chart1" runat="server" Height="421px" Width="542px">
      <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"/>
</div>

Now register AjaxControlToolKit in your Webpage.

<%@ RegisterAssembly="AjaxControlToolkit"Namespace="AjaxControlToolkit"TagPrefix="cc1"%>

now create a Connection String Web.Config File so we can use on every page when we want data connection from database. It is secure to use connection string in Web.Config file.

<connectionStrings>
   <add connectionString="Data Source= ServerName;Initial Catalog= DatabaseName;Integrated Security=True" providerName="System.Data.SqlClient" name="DBCS"/>
</connectionStrings>

One more thing has to keep in mind to Create handler in Web.Config file in that System. Web tab.

<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>

And we have to add reference from .net System.Web.UI.DataVisualization in Reference node. Here keep in mind namespace created in Web.Config file or not.

<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>

Now bind Chart control from database in Page_load () event so when page load first time then we can See data in Chart Controls.

protected void Page_Load(object sender, EventArgs e)
{
  string query = string.Format("select distinct(country),sum(Amounts) from publishers group by country");
  DataTable dt = GetData(query);
  string[] x = new string[dt.Rows.Count];
  decimal[] y = new decimal[dt.Rows.Count];
  for (int i = 0; i < dt.Rows.Count; i++)
  {
    x[i] = dt.Rows[i][0].ToString();
    y[i] = Convert.ToInt32(dt.Rows[i][1]);
  }
  Chart1.Series[0].Points.DataBindXY(x, y);
  Chart1.Series[0].ChartType = SeriesChartType.Pie;
  Chart1.ChartAreas["ChartArea1"].Area3DStyle.Enable3D = true;
  if (x.Length > 3)
  {
    Chart1.Width = (x.Length * 75);
  }
  Chart1.Visible = true;
  Chart1.Legends[0].Enabled = true;
}

Create GetData() method for getting data from database and return DataTable and bind dropdownlist controls.  

private static DataTable GetData(string query)
{
    DataTable dt = new DataTable();
    string constr = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand(query))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                cmd.CommandType = CommandType.Text;
                cmd.Connection = con;
                sda.SelectCommand = cmd;
                sda.Fill(dt);
            }
        }
        return dt;
    }
}

Now Generate button click event and code for create ExcelSheet Dynamically and Code for How to Pass Pie Chart in ExcelSheet so When we Open Excel File then we can easily see data in Pie Chart.

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 = "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();
}

Now run in any browser and click on button to generate excel sheet here you can see Pie Chart created from Database and Generated Excel Sheet Dynamically. 

Author : Umesh Patel

Related Posts