SparkLines

• Download SparkLine.zip - 4.1 KB

Introduction

SparkLines is this really cool way to visualize information. They are small high resolution line charts that show you where things are heading. Note that they don't show any detailed data but only the general trend. This article explains how to create and use spark lines in your ASP.NET application.

Using the code

An ASP.NET page (sparkline.aspx) is producing binary output to generate the image. The data enters the page via URL parameters.

Dim sBgColor As String = "ffffff"
Dim sAvgLineColor As String = "gray"
Dim sLineColor As String = "#000000"
Dim sStdDevColor As String = "dcdcdc"

Dim bStdDev As Boolean = True
Dim sData As String = "1,2,3"

Dim iImageWidth As Integer = 200
Dim iImageHeight As Integer = 60

Dim iTopMargin As Integer = 5
Dim iBottomMargin As Integer = 5
Dim iLeftMargin As Integer = 5
Dim iRightMargin As Integer = 30

Dim iMax As Double = 0
Dim iMin As Double = 0
Dim iAvg As Double = 0
Dim iSum As Double = 0
Dim iStdDev As Double = 0

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    SetVars()

    Dim oData() As String = sData.Split(",")
    If oData.Length <= 1 Then
        Exit Sub
    End If

    SetAvg()

    Dim oPoints(oData.Length - 1) As Point
    Dim iScale As Double = (iImageHeight - (iTopMargin + iBottomMargin)) / Math.Abs(iMax - iMin)
    Dim iStepWidth As Double = (iImageWidth - (iLeftMargin + iRightMargin)) / (oData.Length - 1)

    If Not Double.IsInfinity(iScale) Then
        For i As Integer = 0 To oData.Length - 1
            Dim sValue As String = oData(i)
            Dim iValue As Double = 0
            If sValue <> "" And IsNumeric(sValue) Then
                iValue = CDbl(sValue)
            End If

            Dim x As Integer = (i * iStepWidth) + iLeftMargin
            Dim y As Integer = iImageHeight - (Math.Abs(iValue - iMin) * iScale) - iBottomMargin
            oPoints(i) = New Point(x, y)
        Next
    End If

    Dim oBitmap As Bitmap = New Bitmap(iImageWidth, iImageHeight)
    Dim oPen As System.Drawing.Pen = New System.Drawing.Pen(GetColor(sLineColor))
    Dim oAvgPen As System.Drawing.Pen = New System.Drawing.Pen(GetColor(sAvgLineColor))
    Dim oGraphics As Graphics = Graphics.FromImage(oBitmap)
    oGraphics.SmoothingMode = SmoothingMode.AntiAlias
    oGraphics.FillRectangle(New SolidBrush(GetColor(sBgColor)), 0, 0, iImageWidth, iImageHeight)

    Dim iMiddleY As Integer
    If Not Double.IsInfinity(iScale) Then

        iMiddleY = iImageHeight - (Math.Abs(iAvg - iMin) * iScale) - iBottomMargin

        'StdDev
        If bStdDev Then
            Dim oRect As New Rectangle
            oRect.Width = iImageWidth - (iRightMargin + iLeftMargin)
            oRect.Height = iStdDev * iScale
            oRect.X = iLeftMargin
            oRect.Y = iMiddleY - (oRect.Height / 2)
            oGraphics.FillRectangle(New SolidBrush(GetColor(sStdDevColor)), oRect)
        End If

        'Agv Line
        oGraphics.DrawLine(oAvgPen, iLeftMargin, iMiddleY, iImageWidth - iRightMargin, iMiddleY)

        'Lines
        oGraphics.DrawLines(oPen, oPoints)

        'Final Point
        Dim oLastPoint As Point = oPoints(oPoints.Length - 1)
        Dim oBrush As New SolidBrush(Color.Red)
        oGraphics.FillPie(oBrush, oLastPoint.X - 2, oLastPoint.Y - 2, 4, 4, 0, 360)

        'Final Value
        Dim drawString As String = oData(oData.Length - 1)
        Dim drawFont As New Font("Arial", 8)
        Dim drawBrush As New SolidBrush(Color.Black)
        oGraphics.DrawString(drawString, drawFont, drawBrush, oLastPoint.X + 2, oLastPoint.Y - 6)
    Else
        iMiddleY = iImageHeight / 2
        oGraphics.DrawLine(oAvgPen, iLeftMargin, iMiddleY, iImageWidth - iRightMargin, iMiddleY)
    End If

    Response.ContentType = "image/jpeg"
    oBitmap.Save(Response.OutputStream, ImageFormat.Jpeg)
    oGraphics.Dispose()
    oBitmap.Dispose()

End Sub

Private Sub SetAvg()

    Dim oData() As String = sData.Split(",")

    For i As Integer = 0 To oData.Length - 1
        Dim sValue As String = oData(i)
        Dim iValue As Double = 0
        If sValue <> "" And IsNumeric(sValue) Then
            iValue = CDbl(sValue)
        End If

        iSum += iValue
        If i = 0 Then
            iMax = iValue
            iMin = iValue
        Else
            If iMax < iValue Then iMax = iValue
            If iMin > iValue Then iMin = iValue
        End If
    Next

    iAvg = iSum / oData.Length

    Dim iVar As Double

    If bStdDev Then

        For i As Integer = 0 To oData.Length - 1
            Dim sValue As String = oData(i)
            Dim iValue As Double = 0
            If sValue <> "" And IsNumeric(sValue) Then
                iValue = CDbl(sValue)
            End If

            iVar += Math.Pow(iValue - iAvg, 2)
        Next

        iStdDev = Math.Sqrt(iVar / oData.Length)

    End If

End Sub

Private Sub SetVars()
    If Request.QueryString("data") <> "" Then
        sData = Request.QueryString("data")
    End If

    If Request.QueryString("StdDev") = "0" Then
        bStdDev = False
    End If

    If Request.QueryString("bgcolor") <> "" Then
        sBgColor = Request.QueryString("bgcolor")
    End If

    If Request.QueryString("avgcolor") <> "" Then
        sAvgLineColor = Request.QueryString("avgcolor")
    End If

    If Request.QueryString("linecolor") <> "" Then
        sLineColor = Request.QueryString("linecolor")
    End If

    If Request.QueryString("top") <> "" Then
        iTopMargin = Request.QueryString("top")
    End If

    If Request.QueryString("bottom") <> "" Then
        iBottomMargin = Request.QueryString("bottom")
    End If

    If Request.QueryString("left") <> "" Then
        iLeftMargin = Request.QueryString("left")
    End If

    If Request.QueryString("right") <> "" Then
        iRightMargin = Request.QueryString("right")
    End If

    If Request.QueryString("width") <> "" Then
        iImageWidth = Request.QueryString("width")
    End If

    If Request.QueryString("height") <> "" Then
        iImageHeight = Request.QueryString("height")
    End If

End Sub

Private Function GetColor(ByVal sColor As String) As System.Drawing.Color
    sColor = sColor.Replace("#", "")

    Dim oColor As Color = Color.FromName(sColor)
    Dim bColorEmpty As Boolean = oColor.R = 0 And oColor.G = 0 And oColor.B = 0
    If (bColorEmpty = False) Then Return oColor

    If sColor.Length <> 6 Then
        'On Error Return White
        Return Color.White
    End If

    Dim sRed As String = sColor.Substring(0, 2)
    Dim sGreen As String = sColor.Substring(2, 2)
    Dim sBlue As String = sColor.Substring(4, 2)

    oColor = System.Drawing.Color.FromArgb(HexToInt(sRed), HexToInt(sGreen), HexToInt(sBlue))
    Return oColor
End Function

Function HexToInt(ByVal hexString As String) As Integer
    Return Integer.Parse(hexString, System.Globalization.NumberStyles.HexNumber, Nothing)
End Function

The table below shows the list of parameters that image page (sparkline.aspx) can accept.

Parameter	Description	Sample Data
data	Comma delimited data to be visualized	1,10,10,1
StdDev	Standard Deviation Band	1-show, 0-hide
bgcolor	Background color	yellow
avgcolor	Average line color	red
linecolor	Line color	red
top	Top margin	5
bottom	Bottom margin	5
left	left margin	5
right	right margin	5
width	Chart width	200
height	Chart height	60

Another ASP.NET page (SparklineTest.aspx) is reading SQL Server 2000 Northwind database and is building a dynamic table based on the data. It passes the sales information for each day to the Sparkline image page (sparkline.aspx).

Dim sConnectionString As String = "Provider=SQLOLEDB.1;Password=test;" & _
"User ID=test;Initial Catalog=Northwind;Data Source=(local)"

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    Response.Expires = 0
End Sub

Public Sub ShowSparklines()
    Response.Write("<table border=1 cellspacing=0>")

    Response.Write("<tr>")
    Response.Write("<th>Category</th>")
    Response.Write("<th>1998 Sales</th>")
    Response.Write("</tr>")

    Dim sSql As String = "SELECT CategoryID, CategoryName FROM Categories"
    Dim dr As OleDbDataReader = GetDataReader(sSql)
    While dr.Read
        Response.Write("<tr>")
        Response.Write("<td>" & dr.GetValue(dr.GetOrdinal("CategoryName")) & "</td>")
        Dim sCategoryID As String = dr.GetValue(dr.GetOrdinal("CategoryID")) & ""
        Response.Write("<td>" & GetSparkLine(sCategoryID, 1998) & "</td>")
        Response.Write("</tr>")
    End While
    dr.Close()

    Response.Write("</table>")
End Sub

Private Function GetSparkLine(ByVal sCategoryID As String, ByVal sYear As String) As String
    Dim sSql As String = "SELECT o.OrderDate, SUM(od.UnitPrice * od.Quantity) AS Sales" & _
    " FROM Orders o INNER JOIN " & _
    " [Order Details] od ON o.OrderID = od.OrderID INNER JOIN" & _
    " Products p ON od.ProductID = p.ProductID" & _
    " WHERE p.CategoryID = " & sCategoryID & " AND YEAR(o.OrderDate) = 1998" & _
    " GROUP BY o.OrderDate" & _
    " ORDER BY o.OrderDate"

    Dim dr As OleDbDataReader = GetDataReader(sSql)
    Dim sData As String
    While dr.Read
        If sData <> "" Then
            sData += ","
        End If
        sData += dr.GetValue(dr.GetOrdinal("Sales")) & ""
    End While
    dr.Close()

    Dim iWidth As Integer = 150
    Dim iHeight As Integer = 50

    Return "<img width=" & iWidth & " height=" & iHeight & _
        " src='sparkline.aspx?width=" & iWidth & _
                            "&height=" & iHeight & _
                            "&data=" & sData & "'>"
End Function

Friend Function GetDataReader(ByVal sSql As String) As OleDb.OleDbDataReader
    Dim cn As New OleDb.OleDbConnection(sConnectionString)
    cn.Open()
    Dim cm As New OleDb.OleDbCommand(sSql, cn)
    Return cm.ExecuteReader(CommandBehavior.CloseConnection)
End Function

Points of Interest

You can read more about SparkLines on Edward Tufte's website.

History

Keep a running update of any changes or improvements you've made here.