Create a Dynamic Pie Graph with Export in the Excel file Without Creating an Excel Object

Introduction

Now that you know how to create a simple image from an ASP.NET Web page, you can create more complex (and useful) images. For the remainder of this article I'll look at how to use the .NET Framework drawing classes to create a pie chart from database information. I'll build all of this functionality into a set of functions in an ASP.NET Web page that will end up streaming the dynamically created pie chart's binary content to the Response object's OutputStream.

While creating a set of page-level functions to display a pie chart will accomplish the task at hand, a more reusable solution would be to encapsulate this functionality into a custom-defined ASP.NET Web control or compiled custom control. One disadvantage of such an approach, though, would be that the custom-defined ASP.NET Web control or compiled custom control would have to save the image's file to the Web server's file system and then render it from an appropriate img tag. While this isn't difficult to accomplish, you'll have to deal with the disadvantages I mentioned earlier, including the fact that each time a chart is generated you'll keep adding to the list of images on the Web server's file system.

The CreatePieChart Function for That create ASPX Page

1. Create another ASPX Page like(testintvsoffer.aspx)
   On this page Drag-Drop one datagrid and one Button

   Name of the button is btnTransferToXls.

   VB.NET

   Region "On Click Button Transfer To Xls "
   
   Private Sub btnTransferToXls_Click(ByVal sender As System.Object, _
           ByVal e As System.EventArgs) Handles btnTransferToXls.Click
   
       Try
           Dim strDateFrom, strDateTo As String
           strDateFrom = txtDateFrom.Text
           strDateTo = txtDateTo.Text
           Dim dtStart As Date
           Dim dtEnd As Date
   
           Session("Dtbl") = dtblIntVoffer
       Catch ex As Exception
           lblMessage.Text = ex.Message
       End Try
   
       Dim TblRcount As Int64
       'Dim dg As New DataGrid
       Dim dtblExport As New DataTable
   
       If Not IsNothing(Session("Dtbl")) Then
           dtblExport = CType(Session("Dtbl"), DataTable)
           TblRcount = dtblExport.Rows.Count
       End If
   
       Try
           'dtblIntVoffer = GetData()
           If TblRcount > 0 Then
               lblMessage.Text = ""
               Dg2.Dispose()
               Dg2.DataSource = dtblExport
               Dg2.DataBind()
   
   
               'Here we add code for call
               'delegates same handle dg_itembound()
               'AddHandler Dg2.ItemDataBound,
               ' AddressOf Dg2_ItemDataBound
   
               Dg2.HeaderStyle.BackColor = Color.LightGray
               Dg2.HeaderStyle.Font.Bold = True
   
               Dg2.ShowFooter = True
               Dg2.ItemStyle.BackColor = Color.White
   
               ' Set the content type to Excel.
               Response.ContentType = "application/vnd.ms-excel"
               Response.AddHeader("content-disposition", _
                 "attachment;filename=InterviewVsOffer.xls")
               Response.Charset = ""
   
               ' Turn off the view state.
               Me.EnableViewState = False
   
               Dim tw As New System.IO.StringWriter
               Dim hw As New System.Web.UI.HtmlTextWriter(tw)
   
               ' Get the HTML for the control.
               Dg2.RenderControl(hw)
               ' Write the HTML back to the browser.
               Response.Clear()
               Response.Write(tw.ToString())
               'End the response.
               Response.End()
               'Server.Execute("frmcharts.aspx")
           Else
               lblMessage.Text = "No Records Found"
               Exit Sub
           End If
       Catch ex As Exception
           lblMessage.Text = "Error in Exporting to Excel"
       Finally
           Dg2.Dispose()
           Dg2.DataSource = Nothing
       End Try
   End Sub
   
   #End Region
   
   #Region "On Item Data Bound Of DataGrid 2 "
    Private Sub Dg2_ItemDataBound(ByVal sender As Object, _
           ByVal e As System.Web.UI.WebControls.DataGridItemEventArgs) _
           Handles Dg2.ItemDataBound
       Dim rColor, gColor, bColor As Int16
       rColor = 255
       gColor = 0
       bColor = 255
   
       If e.Item.ItemType = ListItemType.Header Then
           Dim HedText As Int32
           For HedText = 18 To 49
               If HedText <= 33 Then
                   e.Item.Cells(HedText).Text = _
                     e.Item.Cells(HedText).Text.Replace("1", "")
               Else
                   e.Item.Cells(HedText).Text = _
                     e.Item.Cells(HedText).Text.Replace("2", "")
               End If
           Next
   
           e.Item.Cells(1).Text = e.Item.Cells(1).Text.Replace("1", "")
           Dim dgItem As DataGridItem
           Dim dgCell As TableCell
           dgItem = New DataGridItem(0, 0, ListItemType.Footer)
           dgCell = New TableCell
           dgCell.ColumnSpan = 2
           dgItem.Cells.Add(dgCell)
           dgCell.Text = "Months"
           dgCell.BackColor = _
             System.Drawing.ColorTranslator.FromOle(RGB(rColor, _
             gColor, bColor))
           dgCell.ForeColor = _
             System.Drawing.ColorTranslator.FromOle(RGB(r1Color, _
             g1Color, b1Color))
           dgCell.HorizontalAlign = HorizontalAlign.Center
           'Dg1.Controls(0).Controls.AddAt(0, dgItem)
   
           'dgItem = New DataGridItem(0, 0, ListItemType.Footer)
           dgCell = New TableCell
           dgCell.ColumnSpan = 16
           dgItem.Cells.Add(dgCell)
           dgCell.Text = "Interview"
           dgCell.BackColor = _
             System.Drawing.ColorTranslator.FromOle(RGB(rColor, _
             gColor, bColor))
           dgCell.ForeColor = _
             System.Drawing.ColorTranslator.FromOle(RGB(r1Color, _
             g1Color, b1Color))
           dgCell.HorizontalAlign = HorizontalAlign.Center
   
           dgCell = New TableCell
           dgCell.ColumnSpan = 16
           dgItem.Cells.Add(dgCell)
           dgCell.Text = "Offers"
           dgCell.BackColor = _
             System.Drawing.ColorTranslator.FromOle(RGB(rColor, _
             gColor, bColor))
           dgCell.ForeColor = _
             System.Drawing.ColorTranslator.FromOle(RGB(r1Color, _
             g1Color, b1Color))
           dgCell.HorizontalAlign = HorizontalAlign.Center
   
           dgCell = New TableCell
           dgCell.ColumnSpan = 16
           dgItem.Cells.Add(dgCell)
           dgCell.Text = "Joinees"
           dgCell.BackColor = _
             System.Drawing.ColorTranslator.FromOle(RGB(rColor, _
             gColor, bColor))
           dgCell.ForeColor = _
             System.Drawing.ColorTranslator.FromOle(RGB(r1Color, _
             g1Color, b1Color))
           dgCell.HorizontalAlign = HorizontalAlign.Center
   
           Dg2.Controls(0).Controls.AddAt(0, dgItem)
   
       End If
   
       Dim intColCounter, ColTotal, CfinalTotal As Integer
       Dim intRowCounter As Integer
       Dim Itotal, Ototal, Jtotal As Integer
       Dim i As Int64
       Dim PerTotal As Double
       i = 1
       Dim cnt As Integer = Dg2.Items.Count + 3
       If e.Item.ItemType = ListItemType.Footer Then
           Dim dgItem2 As DataGridItem
           Dim dgItem3 As DataGridItem
           Dim dgCell1 As TableCell
           Dim dgCell2 As TableCell
           Dim dgCell3 As TableCell
   
           dgItem2 = New DataGridItem(cnt, cnt, ListItemType.Footer)
   
           dgCell2 = New TableCell
           dgCell2.ColumnSpan = 2
           dgItem2.Cells.Add(dgCell2)
           dgCell2.Text = "Pie-Graph"
           dgCell2.ForeColor = Color.Red
           dgCell2.Font.Bold = True
   
           Dim Intviewtoatal As Double
           Dim Offertoatal As Double
           Dim Joineestoatal As Double
   
           For intRowCounter = 0 To Dg2.Items.Count - 1
   
               Intviewtoatal = Intviewtoatal + _
                 CInt(Dg2.Items(intRowCounter).Cells(17).Text)
               Offertoatal = Offertoatal + _
                 CInt(Dg2.Items(intRowCounter).Cells(33).Text)
               Joineestoatal = Joineestoatal + _
                 CInt(Dg2.Items(intRowCounter).Cells(49).Text)
   
           Next
   
           For intColCounter = 2 To 49
               CfinalTotal = 0
   
               For intRowCounter = 0 To Dg2.Items.Count - 1
                   ColTotal = _
                     CInt(Dg2.Items(intRowCounter).Cells(intColCounter).Text)
                   CfinalTotal = CfinalTotal + ColTotal
               Next
   
               If (intColCounter <= 17 And Intviewtoatal > 0) Then
   
                   ' PerTotal = CInt((CfinalTotal / Intviewtoatal) * 100)
                   PerTotal = Convert.ToDouble_
                   ((CfinalTotal / Intviewtoatal) * 100)
                   PerTotal = (PerTotal.ToString("N", nfi))
               ElseIf (intColCounter <= 33 And Offertoatal > 0) Then
   
                   ' PerTotal = CInt((CfinalTotal / Offertoatal) * 100)
                   PerTotal = Convert.ToDouble_
                   ((CfinalTotal / Offertoatal) * 100)
                   PerTotal = (PerTotal.ToString("N", nfi))
               ElseIf (intColCounter > 33 And Joineestoatal > 0) Then
                   ' PerTotal = CInt((CfinalTotal / Joineestoatal) _
                   '  * 100) PerTotal = Convert.ToDouble((CfinalTotal _
                   '  / Joineestoatal) * 100)
                   PerTotal = (PerTotal.ToString("N", nfi))
               Else
                   PerTotal = 0
               End If
   
               dgCell = New TableCell
               dgcell.Text = CfinalTotal
               dgItem.Cells.Add(dgCell)
               Dg2.Controls(0).Controls.AddAt(cnt, dgItem)
   
               dgCell1 = New TableCell
               dgCell1.Text = PerTotal
               dgItem1.Cells.Add(dgCell1)
               Dg2.Controls(0).Controls.AddAt(cnt + 1, dgItem1)
   
           Next
   
           dgCell3 = New TableCell
           'dgCell3.Text = PerTotal
           dgCell3.ColumnSpan = 10
           dgItem3.Cells.Add(dgCell3)
           Dg2.Controls(0).Controls.AddAt(cnt + 2, dgItem3)
   
           'This code for Get the Graph from another page and
           'integrate with datagrid that together export to Excel
           'New Code Add for Maintain Global Tem Table
           'for same session or conn for one user
           'clsCon.OpenCon_
           '(ConfigurationSettings.AppSettings("strConnection").ToString)
           'SQLSTR = "select RefType,total from CanEnter "
   
           Dim sqlstr As String
           Dim oleAdap As OleDbDataAdapter
           Dim ds1 As DataSet
           Dim Int As Int64
   
           sqlstr = "select ROWNUM Rowcnt,RefType,total," & _
                    "totalper from TABLE order by ROWNO "
   
           oleAdap = New OleDbDataAdapter(sqlstr, clsCon.Con)
           ds1 = New DataSet
           oleAdap.Fill(ds1)
           Session("ds1") = ds1
   
           clsCon.closeCon()
   
           'End New Code Add for Maintain Global Tem Table
           'for same session or conn for one user
   
           Dim Img As New System.Web.UI.WebControls.Image
           ' This code of line executes the page on the
           ' server side and gets the output to this page
           Server.Execute("frmCharts.aspx")
           Dim strFilePath As String
           'strFilePath = Server.MapPath(".") & "/Images/Graph.gif"
           'Response.Write(Server.MapPath("."))
           'strFilePath = "http://RemotServer IP Address
           '            /ProjectName/Images/Graph.gif"
           Dim strPath, struser As String
           strPath = "http://" & Request.ServerVariables("server_name") _
                     & Request.ServerVariables("script_name").ToLower().Trim
           struser = Session("login")
           strPath = Strings.Replace(strPath, ("testintvsoffer.aspx"), _
                     "Graphs/") & struser & ".gif"
   
           Img.ImageUrl = strPath
           ' Img.ImageUrl = "d:/deep/test1.gif"
           dgCell2 = New TableCell
           dgCell2.Controls.Add(Img)
           dgItem2.Cells.Add(dgCell2)
           Dg2.Controls(0).Controls.AddAt(cnt + 3, dgItem2)
   
           'End This code for getting the Graph from another page
           'and integrate with datagrid that together exports to Excel
   
           'Dim lc As LiteralControl
           'lc.Text = "Deepchand"
           'Dg2.Controls.Add(lc)
           'Dg2.Controls(0).Controls.AddAt(cnt + 2, dgItem1)
   
           i = i + 1
       End If
   End Sub
   
   #End Region

2. Create the other ASPX Page for it that will create the Pie Graph according to user Sessions

   Create the One frmCharts.aspx file and write the code given below:

   This code will generate the Pie Chart according to your fetched Data Values from the database.

   VB.NET

   'Imports these Namespaces
   Imports System.Data.OleDb
   Imports System.Data
   Imports System.Drawing
   Imports System.Drawing.Imaging
   Imports System.Math
   
   '******************************************
   Public Class frmCharts Inherits System.Web.UI.Page
   
   #Region "Global VAriable Declarations "
        Dim clsCon As New conn
       Dim SQLSTR As String
       Dim ds1 As New DataSet
       Dim oleAdap As New OleDbDataAdapter
       Dim Int As Integer
       Dim Total As Double
   
   #End Region
   
   #Region " On Page Load "
       Dim ds2 As DataSet
       ds2 = New DataSet
       Try
           ds1.Clear()
           If Not IsNothing(Session("ds1")) Then
               ds2 = CType(Session("ds1"), DataSet)
               Int = ds2.Tables(0).Rows.Count
               Session("ds1") = Nothing
           End If
       Catch ex As Exception
       Finally
           clsCon.closeCon()
       End Try
   
       'Build an array of values for the bar and pie chart.
       'These values could also be pulled from a database.
       Dim arrValues(4) As Integer
       Dim str1, str2 As String
       'Declare your object variables
       'Build a BitMap that will act as the pallet
       'and container for the bar graph. Here 600 is
       'the width and 300 is the height.
       'These values could also be passed as parameters.
       Dim i As Integer
       Dim objBitMap As New Bitmap(960, 660)
   
       'Declare your Graphics objects for painting
       'graphics on your newly created bitmap.
       Dim objGraphics As Graphics
       objGraphics = Graphics.FromImage(objBitMap)
       'Set the background color to LightGray
       objGraphics.Clear(Color.LightGray)
       'Write out a title for your bar and pie chart.
       objGraphics.DrawString("Interview Vs Offer Report", _
         New Font("Verdana", 16), Brushes.Black, _
         New PointF(280, 5))
       objGraphics.DrawString("Interview Graph", _
         New Font("Verdana", 12, FontStyle.Bold), _
         Brushes.Black, New PointF(5, 35))
       objGraphics.DrawString("Interview Sources", _
         New Font("Verdana", 12, FontStyle.Bold), _
         Brushes.Black, New PointF(360, 45))
       ' objGraphics.DrawString("Offer Graph", _
       '    New Font("Verdana", 12, FontStyle.Bold), _
       '    Brushes.Black, New PointF(5, 250))
       ' objGraphics.DrawString("Joinees Graph", _
       '    New Font("Verdana", 12, FontStyle.Bold), _
       '    Brushes.Black, New PointF(5, 450))
       objGraphics.DrawString("Joinees Sources", _
       '    New Font("Verdana", 12, FontStyle.Bold), _
       '    Brushes.Black, New PointF(360, 345))
       objGraphics.DrawString("Offer Sources", _
            New Font("Verdana", 12, FontStyle.Bold), _
            Brushes.Black, New PointF(640, 45))
       ' objGraphics.DrawRectangle(Pens.Red, 2, 2, 900, 925)
       ' objGraphics.DrawLine(Pens.YellowGreen, 270, 40, 270, 700)
       'Inter Rectangle
       objGraphics.DrawRectangle(Pens.Red, 280, 40, 340, 300)
       objGraphics.FillRectangle(Brushes.WhiteSmoke, _
                                 281, 65, 338, 265)
       'Offer Rectangle
       objGraphics.DrawRectangle(Pens.Red, 280, 340, 340, 300)
       objGraphics.FillRectangle(Brushes.WhiteSmoke, _
                                 281, 365, 338, 265)
       'Joinees Rectangle
       objGraphics.DrawRectangle(Pens.Red, 600, 40, 340, 300)
       objGraphics.FillRectangle(Brushes.WhiteSmoke, _
                                 601, 65, 338, 265)
       'Create a legend Text to describe your bar and chart.
   
       Dim symbolLeg As PointF = New PointF(300, 80)
       Dim descLeg As PointF = New PointF(335, 76)
       Dim descLeg1 As PointF = New PointF(540, 76)
       Dim symbolLeg1 As PointF = New PointF(300, 380)
       Dim descLeg2 As PointF = New PointF(335, 376)
       Dim descLeg3 As PointF = New PointF(540, 376)
       Dim symbolLeg2 As PointF = New PointF(620, 80)
       Dim descLeg4 As PointF = New PointF(655, 76)
       Dim descLeg5 As PointF = New PointF(860, 76)
   
       Try
           For i = 0 To 46
               str1 = ds2.Tables(0).Rows(i).Item(1)
               str2 = ds2.Tables(0).Rows(i).Item(3) & "%"
               If i = 0 Or i = 16 Or i = 32 Then
                   str1 = "Consultant"
               ElseIf i = 1 Or i = 17 Or i = 33 Then
                   str1 = "Employee Referral"
               ElseIf i = 2 Or i = 18 Or i = 34 Then
                   str1 = "Management Referral"
               ElseIf i = 3 Or i = 19 Or i = 35 Then
                   str1 = "Ex Employee Referral"
               ElseIf i = 4 Or i = 20 Or i = 36 Then
                   str1 = "Walk In"
               ElseIf i = 5 Or i = 21 Or i = 37 Then
                   str1 = "Institution"
               ElseIf i = 6 Or i = 22 Or i = 38 Then
                   str1 = "Web"
               ElseIf i = 7 Or i = 23 Or i = 39 Then
                   str1 = "Advertising"
               ElseIf i = 8 Or i = 24 Or i = 40 Then
                   str1 = "Recruitment Team Effort"
               ElseIf i = 9 Or i = 25 Or i = 41 Then
                   str1 = "Telecalling "
               ElseIf i = 10 Or i = 26 Or i = 42 Then
                   str1 = "Campus"
                   If i <= 14 Then
                       objGraphics.FillRectangle(New SolidBrush(GetColor(i)),_
                                   symbolLeg.X, symbolLeg.Y, 20, 10)
                       objGraphics.DrawRectangle(Pens.Black, _
                              symbolLeg.X, symbolLeg.Y, 20, 10)
                       objGraphics.DrawString(str1.ToString, _
                         New Font("Verdana", 10, FontStyle.Bold), _
                         Brushes.Black, descLeg)
                       objGraphics.DrawString(str2.ToString, _
                         New Font("Verdana", 10, FontStyle.Bold), _
                         Brushes.Blue, descLeg1)
                       symbolLeg.Y += 15
                       descLeg.Y += 15
                       descLeg1.Y += 15
                   ElseIf i <= 30 And i > 15 Then
                       objGraphics.FillRectangle(New SolidBrush(GetColor(i)), _
                         symbolLeg2.X, symbolLeg2.Y, 20, 10)
                       objGraphics.DrawRectangle(Pens.Black, _
                         symbolLeg2.X, symbolLeg2.Y, 20, 10)
                       str1 = Replace(str1, "1", "")
                       objGraphics.DrawString(str1.ToString, _
                         New Font("Verdana", 10, FontStyle.Bold), _
                         Brushes.Black, descLeg4)
                       objGraphics.DrawString(str2.ToString, _
                         New Font("Verdana", 10, FontStyle.Bold), _
                         Brushes.Blue, descLeg5)
                       symbolLeg2.Y += 15
                       descLeg4.Y += 15
                       descLeg5.Y += 15
                   ElseIf i > 31 Then
                       objGraphics.FillRectangle(New _
                         SolidBrush(GetColor(i)), symbolLeg1.X, _
                         symbolLeg1.Y, 20, 10)
                       objGraphics.DrawRectangle(Pens.Black, _
                         symbolLeg1.X, symbolLeg1.Y, 20, 10)
                       str1 = Replace(str1, "2", "")
                       objGraphics.DrawString(str1.ToString, _
                         New Font("Verdana", 10, FontStyle.Bold), _
                         Brushes.Black, descLeg2)
                       objGraphics.DrawString(str2.ToString, _
                         New Font("Verdana", 10, FontStyle.Bold), _
                         Brushes.Blue, descLeg3)
                       symbolLeg1.Y += 15
                       descLeg2.Y += 15
                       descLeg3.Y += 15
               End If
           Next
   
           'Loop through the values to create the Pie Chart.
   
           Dim sglCurrentAngle As Single = 0
           Dim sglTotalAngle As Single = 0
           Dim sglCurrentAngle1 As Single = 0
           Dim sglTotalAngle1 As Single = 200
           i = 0
   
           For i = 0 To 14
               str1 = ds2.Tables(0).Rows(i).Item(2)
               str2 = ds2.Tables(0).Rows(i).Item(2)
               Dim Value As Double
               Value = Convert.ToDouble(str2)
               Total = Total + Value
           Next
   
           Dim xAxis As Double, yAxis As Double
               'For i = 0 To ds2.Tables(0).Rows.Count - 1
               For i = 0 To 14
                   str1 = ds2.Tables(0).Rows(i).Item(3)
                   str2 = ds2.Tables(0).Rows(i).Item(2)
                   Dim Value As Double
                   Dim Value1 As Double
                   Value = Convert.ToDouble(str2)
                   Value1 = Convert.ToDouble(str1)
                   'Current Value / (sum of all the Values) _
                   '        * 360 degree angle
                   sglCurrentAngle = Convert.ToSingle(Value / Total * 360)
                   objGraphics.FillPie(New SolidBrush(GetColor(i)), _
                     100, 80, 100, 100, sglTotalAngle, sglCurrentAngle)
                   sglTotalAngle += sglCurrentAngle
                   sglTotalAngle1 += sglCurrentAngle1
               Next i
               '*** End This loop only for those into
               sglTotalAngle = 0
               sglCurrentAngle = 0
               Total = 0
   
               For i = 32 To 46
                   str1 = ds2.Tables(0).Rows(i).Item(2)
                   str2 = ds2.Tables(0).Rows(i).Item(2)
                   Dim Value As Double
                   Value = Convert.ToDouble(str2)
                   Total = Total + Value
               Next
   
           Catch ex As Exception
       Finally
           ds2.Clear()
       End Try
       'This Old code line responsed output as image save as output
   
       'objBitMap.Save(Response.OutputStream, ImageFormat.Gif)
       'Start This code line save output as an image in specified Location
   
       Dim strFilePath, strUser As String
       Dim strPath As String
       strUser = Session("login")
       'strPath = "http://" & _
       '  Request.ServerVariables("server_name") & _
       '  Request.ServerVariables("script_name")
       'strPath = Strings.Replace(strPath, _
       '   "TestIntVsOffer.aspx", "Graphs/") & strUser & ".gif"
       Try
           strFilePath = Server.MapPath(".") & "/Graphs/" & strUser & ".gif"
           objBitMap.Save(strFilePath, ImageFormat.Gif)
       Catch ex As Exception
           Response.Write("error:" & ex.Message.ToString)
       End Try
   
       'End This code line save output as an image in specified Location
       objBitMap.Dispose()
   End Sub
   #End Region
   
   #Region " Function TO Display Different Colours "
    __strong__^'This function returns a color for the bar and pie charts.
   Private Function GetColor(ByVal itemIndex As Integer) As Color
       Dim objColor As Color
       Select Case itemIndex
           Case 0
               objColor = Color.FromArgb(0, 0, 255)
           Case 1
               objColor = Color.FromArgb(32, 111, 11)
           Case 2
               objColor = Color.FromArgb(255, 255, 0)
           Case 3
               objColor = Color.FromArgb(24, 145, 208)
           Case 4
               objColor = Color.FromArgb(255, 153, 0)
           Case 5
               objColor = Color.FromArgb(246, 79, 23)
           Case 6
               objColor = Color.FromArgb(192, 130, 250)
           Case 7
               objColor = Color.FromArgb(102, 0, 0)
           Case 8
               objColor = Color.FromArgb(255, 204, 153)
           Case 9
               objColor = Color.FromArgb(255, 0, 255)
           Case 10
               objColor = Color.FromArgb(179, 179, 179)
           Case Else
               objColor = Color.Red
       End Select
       Return objColor
   End Function
   #End Region
   End Class

3. Create another ASPX Page like(testintvsoffer.aspx)
   On this page Drag-Drop one datagrid and one Button

   Name of the button is btnTransferToXls:

   VB.NET

   Region "On Click Button Transfer To Xls "
   
   Private Sub btnTransferToXls_Click(ByVal sender As _
           System.Object, ByVal e As System.EventArgs) _
           Handles btnTransferToXls.Click
       Try
           Dim strDateFrom, strDateTo As String
           strDateFrom = txtDateFrom.Text
           strDateTo = txtDateTo.Text
           Dim dtStart As Date
           Dim dtEnd As Date
           If Trim(strDateFrom) <> "" Then
               dtStart = ISDateValid(strDateFrom)
               If IsNothing(dtStart) Then
                   lblMessage.Text = "From Date is not valid"
                   Exit Sub
               End If
               strDateFrom = Format(dtStart, "dd-MMM-yyyy")
           Else
               strDateFrom = Format(DateTime.Now, "dd-MMM-yyyy")
               txtDateFrom.Text = Format(DateTime.Now, "dd/MM/yyyy")
           End If
           If Trim(strDateTo) <> "" Then
               dtEnd = ISDateValid(strDateTo)
               If IsNothing(dtEnd) Then
                   lblMessage.Text = "To Date is not valid"
                   Exit Sub
               End If
               strDateTo = Format(dtEnd, "dd-MMM-yyyy")
           Else
               strDateTo = Format(DateTime.Now.Date, "dd-MMM-yyyy")
               txtDateTo.Text = Format(DateTime.Now.Date, "dd/MM/yyyy")
           End If
   
           If dtStart > dtEnd Then
               lblMessage.Text = "From date should not be greater than To Date"
               Exit Sub
           End If
           If dtEnd > Format(DateTime.Now.Date, "dd-MMM-yyyy") Then
               lblMessage.Text = "To date should not be greater than _
                                   system date"
               Exit Sub
           End If
           ViewDetails()
           Session("Dtbl") = dtblIntVoffer
       Catch ex As Exception
           lblMessage.Text = ex.Message
       End Try
   
       Dim TblRcount As Int64
       'Dim dg As New DataGrid
       Dim dtblExport As New DataTable
       If Not IsNothing(Session("Dtbl")) Then
           dtblExport = CType(Session("Dtbl"), DataTable)
           TblRcount = dtblExport.Rows.Count
       End If
       Try
           'dtblIntVoffer = GetData()
           If TblRcount > 0 Then
               lblMessage.Text = ""
               Dg2.Dispose()
               Dg2.DataSource = dtblExport
               Dg2.DataBind()
               Dg2.HeaderStyle.BackColor = Color.LightGray
               Dg2.HeaderStyle.Font.Bold = True
               Dg2.ShowFooter = True
               Dg2.ItemStyle.BackColor = Color.White
               Dg2.AlternatingItemStyle.BackColor = Color.White
               Dg2.FooterStyle.BackColor = Color.LightGray
               Dg2.FooterStyle.ForeColor = Color.White
               Dg2.FooterStyle.Font.Bold = True
               ' Set the content type to Excel.
               Response.ContentType = "application/vnd.ms-excel"
               Response.AddHeader("content-disposition", _
                  "attachment;filename=InterviewVsOffer.xls")
               Response.Charset = ""
               ' Turn off the view state.
               Me.EnableViewState = False
               Dim tw As New System.IO.StringWriter
               Dim hw As New System.Web.UI.HtmlTextWriter(tw)
               ' Get the HTML for the control.
               Dg2.RenderControl(hw)
               ' Write the HTML back to the browser.
               Response.Clear()
               Response.Write(tw.ToString())
               'End the response.
               Response.End()
               'Server.Execute("frmcharts.aspx")
           Else
               lblMessage.Text = "No Records Found"
               Exit Sub
           End If
       Catch ex As Exception
           lblMessage.Text = "Error in Exporting to Excel"
       Finally
           Dg2.Dispose()
           Dg2.DataSource = Nothing
       End Try
   End Sub
   #End Region
   
   #Region "On Item Data Bound Of DataGrid 2 "
    Private Sub Dg2_ItemDataBound(ByVal sender As Object, _
           ByVal e As System.Web.UI.WebControls.DataGridItemEventArgs) _
           Handles Dg2.ItemDataBound
   
       Dim rColor, gColor, bColor As Int16
       Dim r1Color, g1Color, b1Color As Int16
       'rColor = 150
       'gColor = 150
       'bColor = 150
       rColor = 255
       gColor = 0
       bColor = 255
       r1Color = 0
       g1Color = 0
       b1Color = 0
   
       If e.Item.ItemType = ListItemType.Header Then
           Dim HedText As Int32
           For HedText = 18 To 49
               If HedText <= 33 Then
               e.Item.Cells(HedText).Text = _
                  e.Item.Cells(HedText).Text.Replace("1", "")
               Else
               e.Item.Cells(HedText).Text = _
                  e.Item.Cells(HedText).Text.Replace("2", "")
               End If
           Next
   
           e.Item.Cells(1).Text = _
              e.Item.Cells(1).Text.Replace("1", "")
           Dim dgItem As DataGridItem
           Dim dgCell As TableCell
           dgItem = New DataGridItem(0, 0, ListItemType.Footer)
           dgCell = New TableCell
           dgCell.ColumnSpan = 2
           dgItem.Cells.Add(dgCell)
           dgCell.Text = "Months"
           dgCell.BackColor = _
              System.Drawing.ColorTranslator.FromOle(RGB(rColor, _
              gColor, bColor))
           dgCell.ForeColor = _
             System.Drawing.ColorTranslator.FromOle(RGB(r1Color, _
             g1Color, b1Color))
           dgCell.HorizontalAlign = HorizontalAlign.Center
           'Dg1.Controls(0).Controls.AddAt(0, dgItem)
           'dgItem = New DataGridItem(0, 0, ListItemType.Footer)
           dgCell = New TableCell
           dgCell.ColumnSpan = 16
           dgItem.Cells.Add(dgCell)
           dgCell.Text = "Interview"
           dgCell.BackColor = _
             System.Drawing.ColorTranslator.FromOle(RGB(rColor, _
             gColor, bColor))
           dgCell.ForeColor = _
             System.Drawing.ColorTranslator.FromOle(RGB(r1Color, _
             g1Color, b1Color))
           dgCell.HorizontalAlign = HorizontalAlign.Center
           dgCell = New TableCell
           dgCell.ColumnSpan = 16
           dgItem.Cells.Add(dgCell)
           dgCell.Text = "Offers"
           dgCell.BackColor = _
             System.Drawing.ColorTranslator.FromOle(RGB(rColor, _
             gColor, bColor))
           dgCell.ForeColor = _
             System.Drawing.ColorTranslator.FromOle(RGB(r1Color, _
             g1Color, b1Color))
           dgCell.HorizontalAlign = HorizontalAlign.Center
           dgCell = New TableCell
           dgCell.ColumnSpan = 16
           dgItem.Cells.Add(dgCell)
           dgCell.Text = "Joinees"
           dgCell.BackColor = _
             System.Drawing.ColorTranslator.FromOle(RGB(rColor, _
             gColor, bColor))
           dgCell.ForeColor = _
             System.Drawing.ColorTranslator.FromOle(RGB(r1Color, _
             g1Color, b1Color))
           dgCell.HorizontalAlign = HorizontalAlign.Center
           Dg2.Controls(0).Controls.AddAt(0, dgItem)
       End If
   
       Dim intColCounter, ColTotal, CfinalTotal As Integer
       Dim intRowCounter As Integer
       Dim Itotal, Ototal, Jtotal As Integer
       Dim i As Int64
       Dim PerTotal As Double
       i = 1
       Dim cnt As Integer = Dg2.Items.Count + 3
   
       If e.Item.ItemType = ListItemType.Footer Then
           Dim dgItem As DataGridItem
           Dim dgCell As TableCell
           Dim dgItem1 As DataGridItem
           Dim dgItem2 As DataGridItem
           Dim dgItem3 As DataGridItem
           Dim dgCell1 As TableCell
           Dim dgCell2 As TableCell
           Dim dgCell3 As TableCell
           dgItem = New DataGridItem(cnt, cnt, ListItemType.Footer)
           dgItem1 = New DataGridItem(cnt, cnt, ListItemType.Footer)
           dgItem2 = New DataGridItem(cnt, cnt, ListItemType.Footer)
           dgItem3 = New DataGridItem(cnt, cnt, ListItemType.Footer)
           dgCell = New TableCell
           dgCell.ColumnSpan = 2
           dgItem.Cells.Add(dgCell)
           dgCell.Text = "Total"
           dgCell1 = New TableCell
           dgCell1.ColumnSpan = 2
           dgItem1.Cells.Add(dgCell1)
           dgCell1.Text = "% /Total"
           dgCell3 = New TableCell
           dgCell3.ColumnSpan = 2
           dgItem3.Cells.Add(dgCell3)
           dgCell3.Text = "*"
           dgCell2 = New TableCell
           dgCell2.ColumnSpan = 2
           dgItem2.Cells.Add(dgCell2)
           dgCell2.Text = "Pie-Graph"
           dgCell2.ForeColor = Color.Red
           dgCell2.Font.Bold = True
           Dim Intviewtoatal As Double
           Dim Offertoatal As Double
           Dim Joineestoatal As Double
   
           For intRowCounter = 0 To Dg2.Items.Count - 1
               Intviewtoatal = Intviewtoatal + _
                 CInt(Dg2.Items(intRowCounter).Cells(17).Text)
               Offertoatal = Offertoatal + _
                 CInt(Dg2.Items(intRowCounter).Cells(33).Text)
               Joineestoatal = Joineestoatal + _
                 CInt(Dg2.Items(intRowCounter).Cells(49).Text)
           Next
           For intColCounter = 2 To 49
               CfinalTotal = 0
               For intRowCounter = 0 To Dg2.Items.Count - 1
                   ColTotal = _
                     CInt(Dg2.Items(intRowCounter).Cells(intColCounter).Text)
                   CfinalTotal = _
                     CfinalTotal + ColTotal
               Next
               If (intColCounter <= 17 And Intviewtoatal > 0) Then
                   ' PerTotal = CInt((CfinalTotal / Intviewtoatal) * 100)
                   PerTotal = Convert.ToDouble((CfinalTotal / _
                              Intviewtoatal) * 100)
                   PerTotal = (PerTotal.ToString("N", nfi))
                   ElseIf (intColCounter <= 33 And Offertoatal > 0) Then
                   ' PerTotal = CInt((CfinalTotal / Offertoatal) * 100)
                   PerTotal = Convert.ToDouble((CfinalTotal / _
                              Offertoatal) * 100)
                   PerTotal = (PerTotal.ToString("N", nfi))
                  Else
                   PerTotal = 0
               End If
               dgCell = New TableCell
               dgcell.Text = CfinalTotal
               dgItem.Cells.Add(dgCell)
               Dg2.Controls(0).Controls.AddAt(cnt, dgItem)
               dgCell1 = New TableCell
               dgCell1.Text = PerTotal
               dgItem1.Cells.Add(dgCell1)
               Dg2.Controls(0).Controls.AddAt(cnt + 1, dgItem1)
           Next
   
           dgCell3 = New TableCell
           'dgCell3.Text = PerTotal
           dgCell3.ColumnSpan = 10
           dgItem3.Cells.Add(dgCell3)
           Dg2.Controls(0).Controls.AddAt(cnt + 2, dgItem3)
           'This code for getting the Graph from another page
           'and integrate with datagrid that together exports to Excel
           'New Code Add for Maintain Global Tem Table
           'for same session or conn for one user
           'clsCon.OpenCon(ConfigurationSettings.
           '      AppSettings("strConnection").ToString)
           'SQLSTR = "select RefType,total from CanEnter "
           Dim sqlstr As String
           Dim oleAdap As OleDbDataAdapter
           Dim ds1 As DataSet
           Dim Int As Int64
           sqlstr = "select ROWNUM Rowcnt,RefType,total," & _
                    "totalper from CANINTERVOFFER order by ROWNO "
           oleAdap = New OleDbDataAdapter(sqlstr, clsCon.Con)
           ds1 = New DataSet
           oleAdap.Fill(ds1)
           Session("ds1") = ds1
           clsCon.closeCon()
           'End New Code Add for Maintain Global Tem
           'Table for same session or conn for one user
           Dim Img As New System.Web.UI.WebControls.Image
           ' This code of line Execute the page
           ' on server side get the output this page
           Server.Execute("frmCharts.aspx")
           Dim strFilePath As String
           'strFilePath = Server.MapPath(".") & "/Images/Graph.gif"
           'Response.Write(Server.MapPath("."))
            Dim strPath, struser As String
           strPath = "http://" & Request.ServerVariables("server_name") _
                     & Request.ServerVariables("script_name").ToLower().Trim
           struser = Session("login")
           strPath = Strings.Replace(strPath, _
                     ("testintvsoffer.aspx"), "Graphs/") & _
                     struser & ".gif"
           Img.ImageUrl = strPath
           ' Img.ImageUrl = "d:/deep/test1.gif"
           dgCell2 = New TableCell
           dgCell2.Controls.Add(Img)
           dgItem2.Cells.Add(dgCell2)
           Dg2.Controls(0).Controls.AddAt(cnt + 3, dgItem2)
           'End This code for Get the Graph from another page and
           'integrate with datagrid that together export to Excel
           'Dim lc As LiteralControl
           'lc.Text = "Deepchand"
           'Dg2.Controls.Add(lc)
           'Dg2.Controls(0).Controls.AddAt(cnt + 2, dgItem1)
           i = i + 1
       End If
   End Sub
   #End Region

Advantages

1. Multiple users can have access at the same time
2. Generate the images (graph) according to the user sessions
3. Export the GIF images with the data in the Excel file from report point of view
4. Generate the dynamic graph for each user request
5. Graphical representation of the data so that user has an easier way of understanding the output of the system data

Possible Enhancements

The CreatePieChart function lacks the bang and pizzazz that a third-party graphing component may provide, but this function was created in less than 15 minutes, costs nothing (except my time), and, best of all, has the source code readily available for any future changes or enhancements you may be interested in making.

One possible enhancement for the CreatePieChart function would be to add the ability to pass in an SQL string as opposed to a database table name. In its current state, the CreatePieChart graph can only create pie charts for databases that have very simple data models. Being able to specify an SQL string means you could create graphs where the data comes from multiple tables, or graph only certain rows from a database table by specifying a WHERE clause.

Conclusion

Because ASP.NET allows you to use the classes from the .NET Framework, with a little bit of code you can create your own dynamic images from a Web page. These images can either be saved to the Web server's file system or streamed directly to the browser. All of the image-generation routines you will ever need are included in the .NET Framework. The charts and graphs you can create are limited only by your imagination.