In Part 1 we examined various ASP.NET charting solutions, focusing
on the Office Web Components. In this final part we'll examine the OWC programming model and see an
example Web page using OWC to create a scatter chart.
The OWC Programming Model
In order to display an OWC chart from an ASP.NET Web page, we will create a single ASP.NET Web page
whose sole duty is to display a chart. This Web page will be named getchart.aspx. In order
to display this chart in a Web page, we'll use the standard HTML <img> tag as follows:
<img src="getchart.aspx" />
The getchart.aspx page generates the OWC chart in memory on the
server, and "streams" the chart back to the client as a binary .GIF image, Thus,
the client sees getchart.aspx as a .GIF file, but "behind the scenes" the
Web server executes code that generates the .GIF file dynamically. (This technique
is used in other 4Guys articles, such as: Displaying a Scaled List of
Images.
We can pass input parameters to the getchart.aspx code either through the ordinary HTTP querystring
or through ASP.NET session variables.
Next, let us have a closer look at the getchart.aspx file. If the ASP.NET
code-behind model is used, the getchart.aspx file is just a couple of ASP.NET
directives:
Please remember, the getchart.aspx file is supposed to return a .GIF file
(that is, a binary data stream) to the client. Therefore, getchart.aspx cannot
contain any HTML. It is just a reference to the code-behind file, which generates
the binary data stream. The second line determines the cache lifetime (5 seconds
in this example). If the database contains frequently updated info (such as
weather data or stock prices), you need to set the cache lifetime low in order
to get an updated chart with each press on the "refresh" button.
Now let's take a look at the code-behind file, where all of the action takes place!
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports Microsoft.Office.Interop
Public Class getchart
Inherits System.Web.UI.Page
Protected WithEvents ChartSpace1 As Owc.ChartSpace
Private Sub Page_Load(ByVal Sender As System.Object, _
ByVal E As System.EventArgs) Handles MyBase.Load
Response.Buffer = TRUE
Response.ContentType = "image/gif"
'SQL Server connection string:
Dim ConnectionString As String = "connection string"
'SQL command to find number of datapoints:
Dim CountText As String = "SELECT COUNT(*) From OWCDATA"
'SQL command to get the datapoints:
Dim CommandText As String = "SELECT X, Y From OWCDATA ORDER BY X"
'Define the database connection object:
Dim myConnection As New SqlConnection(ConnectionString)
'Define the command object to find number of datapoints:
Dim myCount As New SqlCommand(CountText, myConnection)
'Define the command object to get the datapoints:
Dim myCommand As New SqlCommand(CommandText, myConnection)
'Define Data Reader object:
Dim DataReader1 As SqlDataReader
'i = index variable (used to populate array)
'NumPoints = integer representing number of datapoints from database
'aX = array containing X values
'aY = array containing Y values
Dim i, NumPoints, aX, aY
'Define chart and series objects, as used in OWC chart model:
Dim Chart1, Chart1_Series1
'Open the database connection
myConnection.Open()
'Step 1: Find number of datapoints and return value
'in "NumPoints" variable:
NumPoints = myCount.ExecuteScalar()
'Debug
'Response.Write(NumPoints & "#")
'Redimension arrays according to number of datapoints
ReDim aX(NumPoints - 1)
ReDim aY(NumPoints - 1)
'Step 2: Get the datapoints and return X and Y values
'in aX and aY arrays:
DataReader1 = myCommand.ExecuteReader()
i = 0
While DataReader1.Read
aX(i) = DataReader1.GetValue(0)
aY(i) = DataReader1.GetValue(1)
i = i + 1
End While
DataReader1.Close()
'Debug
'For i = 0 to NumPoints - 1
' Response.Write(aX(i) & "|" & aY(i) & "#")
'Next i
'Close the database connection
myConnection.Close()
'Create a new chartspace:
ChartSpace1 = new Owc.ChartSpace()
'Create a new chart within ChartSpace1:
Chart1 = Chartspace1.Charts.Add(0)
'Add a new dataseries within Chart1:
Chart1_Series1 = Chart1.SeriesCollection.Add(0)
'Define Chart1_Series1 as "scatter" (XY) diagram,
'with lines and markers:
Chart1_Series1.Type = _
Chartspace1.Constants.chChartTypeScatterLineMarkers
'Name the dataseries (name appears in Legend):
Chart1_Series1.SetData (OWC.ChartDimensionsEnum.chDimSeriesNames, _
OWC.ChartSpecialDataSourcesEnum.chDataLiteral, "Chart1_Series1")
'Populate the X and Y values from array:
Chart1_Series1.SetData (OWC.ChartDimensionsEnum.chDimXValues, _
OWC.ChartSpecialDataSourcesEnum.chDataLiteral, aX)
Chart1_Series1.SetData (OWC.ChartDimensionsEnum.chDimYValues, _
OWC.ChartSpecialDataSourcesEnum.chDataLiteral, aY)
'Format the chartspace elements.
With ChartSpace1
'.Border.Color = Chartspace1.Constants.chColorNone
End With
'Format the chart elements.
With Chart1
'.SeriesCollection(0).Interior.Color = "Rosybrown"
'.PlotArea.Interior.Color = "Wheat"
.HasLegend = true
.Legend.Position = _
OWC.ChartLegendPositionEnum.chLegendPositionBottom
.HasTitle = true
.Title.Caption = "Chart1"
.Axes(0).HasTitle = true
.Axes(0).Title.Caption = "Y axis"
.Axes(1).HasTitle = true
.Axes(1).Title.Caption = "X axis"
End With
'Return the new chart in GIF format.
Response.BinaryWrite(Chartspace1.GetPicture ("gif", 500, 400))
Response.End
End Sub
End Class
A screenshot of the graph from some sample data, when viewed through a browser, can be seen to the
right.
The Microsoft.Office.Interop namespace refers to the Office XP PIA, which must
be installed on the server. When compiling the source code, the Office XP PIA
OWC DLL must be referenced. If you are using Visual Studio .NET, you can simply add a reference to
the Microsoft.Office.Interop.Owc.dll file (located in the directory where you unpacked
the Office XP PIA files). If you are compiling via the command-line, you must use the /r:
switch like so:
There are a few interesting points in the code that are worth mentioning. First,
note that the data source resides in a MSSQL database named OWCDEMO, which contains a
table, OWCDATA. The table contains two numeric data fields, X and
Y. The purpose of getchart.aspx is to plot the records from the
database in a scatter (XY) chart. In the ZIP file at the end of this article you can find the complete
MS-SQL database content.
There is no mechanism to populate the data points in the OWC chart directly
from an ASP.NET DataSet. Therefore, we must first read the data from the
database into a literal array, and then populate the OWC chart data points
from the array. A great enhancement would be to develop an ASP.NET server control to produce
an XY scatter plot from some abstract DataSource, such as a DataSet, XML file, or array.
The use of a DataReader is quicker and more efficient than a DataSet. However,
we need to know the number of records returned in order to dimension our
arrays. This is accomplished by first determining the number of records by a
SQL Select count(*) query, then dimension the arrays, and finally, get the
records by a second SQL SELECT query.
Also, note that the sorting of the records is not irrelevant. If we want a scatter chart with
lines connecting the dots, the records must be sorted along the X axis. This
is accomplished with a SQL ORDER BY clause.
The OWC chart model is based on a "chartspace". A chartspace can contain one
or several charts. Each chart can contain one or several "dataseries".
When programming an OWC chart, you have to create a chartspace, add a chart to
the chartspace, choose the chart type, add the dataseries, and finally,
populate the dataseries with data. Optionally, you may specify layout options,
such as coloring, axis titles, header, and legend, to name a few. OWC has
hundreds of layout options, making it possible to tweak the chart just the way
you want.
Realize that the chart model is slightly different for different chart types. A pie chart
and a scatter chart, for instance, requires different parameters.
The OWC chart model is documented in the OWC helpfiles, which are included
in the OWC10 installation package.
Finally, the Response.BinaryWrite parameters specify the graphics file format (GIF),
and the width and height of the graphics file (in pixels). Thus, the OWC chart
image can be scaled according to your preferences.
Conclusion
The source code for the example used in this article is available for download at the end of this article.
Also, a more complete OWC chart demo is available, illustrating all the various
chart types that can be made with OWC. The source code shows the programming
differences for different chart types, and is thus usable as a "how-to" guide
for making charts. To illustrate more advanced charting options, a sample chart
with trendline and errorbars is also available.
Finally, to learn more about OWC10, consider checking out the following resources:
About the Author
Olav Lerflaten is an IT professional living in Trondheim, Norway. He has
developed several ASP applications in recent years, and is now trying to
master ASP.NET.
