Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / web / ASP.NET
 

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

2.98/5 (20 votes)
12 Oct 20063 min read 1  
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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here



Permalink

Privacy
Cookies
Terms of Use
Layout: fixed | fluid

Article Copyright 2006 by DEEPCHAND KOSTA
Everything else Copyright © CodeProject, 1999-2024

Web01 2.8:2024-10-20:1