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.
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" />
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
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
Please remember, the
getchart.aspx file is supposed to return a
(that is, a binary data stream) to the client. Therefore,
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!
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
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
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
OWCDATA. The table contains two numeric data fields,
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
Select count(*) query, then dimension the arrays, and finally, get the
records by a second SQL
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.
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.
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
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.