You may have seen web pages that include a horizontal bar graph showing responses to a questionaire or other numeric results without the use of a graphing tool. This article will demonstrate how to build such a graph using html and a dataset.
By: John Kilgo
It really is not difficult to create a horizontal bar graph using an HTML table. All you need to do is compute a percentage of a total for each item and then set <td width= to the value of the percentage. Use a background color for the percentage and no color for the difference between 100 and value's percentage and you have your bar graph. In this example we will graph UnitsInStock from the Northwind Products table.
We will do all the computations in a code behind file (BarGraph.aspx.vb) which means we must also build our HTML in the code behind file. To accomodate this we include on a <div>...</div> in our .aspx file shown below. In our code behind file we will, at the end, set the InnerHtml property of the Div to produce all of the HTML for the .aspx page. The code for BarGraph.aspx is shown below without any further explanation.
<%@ Page Language="vb" AutoEventWireup="false" Src="BarGraph.aspx.vb" Inherits="DotNetJohn.BarGraph"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>BarGraph</title>
<meta name="GENERATOR" content="Microsoft Visual Studio .NET 7.1">
<meta name="CODE_LANGUAGE" content="Visual Basic .NET 7.1">
<meta name=vs_defaultClientScript content="JavaScript">
<meta name=vs_targetSchema content="http://schemas.microsoft.com/intellisense/ie5">
</head>
<body MS_POSITIONING="GridLayout">
<form id="Form1" method="post" runat="server">
</form>
<div id="ShowTable" runat="server">Our table appears here</div>
</body>
</html>
To make our graph a little more interesting we will color code the bars. We will (arbitrarily) set 125 as the maximum UnitsInStock for any product and base our percentage on it. In other words, if there 25 units in stock of a certain product, the colored <td> will have a width of 20% (25 / 125) * 100. Any product with a percentage of less than 20% will be colored red, products with percentages between 20% and 80% will be blue, and products with a percentage over 80% will be in green.
The code behind file is not very long but we will display it in three sections for ease of discussion. This first section is just the declarations and the database access to obtain the data and create the DataSet.
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Namespace DotNetJohn
Public Class BarGraph
Inherits System.Web.UI.Page
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim objConn As New SqlConnection(ConfigurationSettings.AppSettings("NorthwindConnection"))
Dim strSql As String = "Select ProductName, UnitsInStock From Products Order By ProductName"
Dim da As New SqlDataAdapter(strSql, objConn)
Dim ds As New DataSet()
da.Fill(ds, "Products")
This next section just establishes our html table and defines the table header.
Dim strHTML As String
strHTML = "<table width='80%' border='1'>"
strHTML &= "<tr>"
strHTML &= "<td>ProductName</td><td>Percentage of 125 | red <20% | blue 20-80% | green >80% |"
strHTML &= "</td><td>UnitsInStock</td>"
strHTML &= "</tr>"
This last section is where most of the work gets done to produce our bar graph. intValue is the percentage of the total (125) that gets plotted in color. intBlank is the right-most portion of the graph and is the difference between 100 and intValue. We then start the body of the table. The first <td> contains the product name. Within the the next <td> we have an embedded table containing intValue, color coded, and intBlank colorless. In that section of code we check the value of intValue. If it is less than 20 we color it red. If it is greater than 80 we color it green, otherwise we color it blue. We might want to do something like this to show that the red products need to be re-stocked and that the green items may be overstocked and we might want to hold a sale on these items. We then finish out the table with the actual UnitsInStock count (the actual count, not a percentage). Finally we set ShowTable's InnerHtml to the strHTML variable that we have built withing the code behind file. Remember that "ShowTable" was the ID we gave the <Div> tag on the .aspx page. We can do this because we included the runat="server" attribute in the <Div> tag on the .aspx page.
Dim dr As DataRow
Dim intValue, intBlank As Integer
For Each dr In ds.Tables("Products").Rows
intValue = 100 * (dr("UnitsInStock") / 125)
intBlank = 100 - intValue
strHTML &= "<tr><td width='30%'>" & dr("ProductName") & "</td>" & _
"<td width='60%'><table width='100%'><tr>"
If intValue < 20 Then
strHTML &= "<td width=" & intValue.ToString & "% bgcolor=red>"
ElseIF intValue > 80 Then
strHTML &= "<td width=" & intValue.ToString & "% bgcolor=green>"
Else
strHTML &= "<td width=" & intValue.ToString & "% bgcolor=blue>"
End If
strHTML &= " </td>" & _
"<td width=" & intBlank.ToString & "% </td>" & _
"</tr></table></td>" & _
"<td width=10%>" & dr("UnitsInStock").ToString & "</td></tr>"
Next
strHTML &= "</table>"
ShowTable.InnerHtml = strHTML
End Sub
End Class
End Namespace
I hope you have learned a new techinque you can put in you bag in case you need it in the future. You may also have learned something about cycling through a DataSet using the Rows collection. Best of luck.
You may run the program here.
You may download the code here.
