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






2.98/5 (20 votes)
May 23, 2006
3 min read

62782
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
-
Create another ASPX Page like(testintvsoffer.aspx)
On this page Drag-Drop one datagrid and one ButtonName of the button is
btnTransferToXls
.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
-
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.
'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 " '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
-
Create another ASPX Page like(testintvsoffer.aspx)
On this page Drag-Drop one datagrid and one ButtonName of the button is
btnTransferToXls
: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
- Multiple users can have access at the same time
- Generate the images (graph) according to the user sessions
- Export the GIF images with the data in the Excel file from report point of view
- Generate the dynamic graph for each user request
- 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.