ASP.NET Charting Using Office Web Components (OWC)

By Olav Lerflaten

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.

- continued -

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:

<%@ Page Language="vb" AutoEventWireup="false" Codebehind="getchart.aspx.vb" Inherits="getchart"%> <%@ OutputCache Duration="5" VaryByParam="none" %>

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:

vbc /t:library /out:bin\getchart.dll /r:System.dll /r:System.Web.dll /r:System.Data.dll /r:C:\oxppia\Microsoft.Office.Interop.Owc.dll getchart.aspx.vb

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:

Peter Bromberg's article, Live ASP.NET Charts with Office Web Components
OWC Spreadsheet Example in ASP.NET

Happy programming!

By Olav Lerflaten

Attachments

Download the complete source code and SQL data (ZIP)

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.