Charting with Office Web Components (OWC), Part 2 - 4GuysFromRolla.comBy Bret Hern
In Part 1 I introduced Office Web Components (OWCs) and we looked at how to setup the Web server to start using OWCs. In this part we'll step through an example application that renders the information from a database table into a pretty line graph!
Step by Step Through the Solution
There are two VBScript functions involved in the solution that we will cover at the back end of this walkthrough, as we encounter their use. In the meantime, we have the usual variable declaration/dimensioning, followed by a couple of value definitions:
The absolute and relative path definitions help us in some of the file creation/deletion activities that we perform later on -- we could use an absolute reference in all cases, but I don't like to render absolute paths in HTML, feeling it gives a user a bit too much information about my server setup.
This is followed by some basic object creation steps:
The charting performed in OWC takes place in a chartspace; a chartspace can consist of one
or more charts containing one or more series consisting of one or more datapoints. Whew! So
we create a chartspace object, and use its
Add method to add a chart to the
chartspace charts collection. The third line is mostly for convenience -- there is a
Constants property of the chartspace object that contains the enumerated constants for all
of the relevant charting functionality. Typing
c instead of
objChartSpace.Constants every time we want to reference a constant is a bit
more efficient, timewise at least. (Unfortunately, the documentation of these constants
is maddeningly random. Programming
Office 2000 Web Components from Microsoft Press is the only significant book on
the subject, and it is far from comprehensive where object descriptions are concerned.
Embedded in the online
doc for Office Web Components you will find more complete coverage of the constants.)
We use those constants on the very next line, where we define the type of chart to be created, a basic line chart:
In the next few lines we set up the data access to my database and return a recordset of
the relevant data. The database is a single table with three columns:
A key item in the above is the setting of a client side cursor for the recordset -- to work with OWC, a recordset must have a client side, non-forward-only cursor. Having established a recordset, we now attach it to our chart and set up the series of data to display:
DataSource property of the chartspace object is straightforward enough,
and is followed by the statements which establish chart's contents. First, the series are
defined at the chart level -- the "student" parameter in the
call refers to the field name in the recordset -- we will have one series for each
individual student in the database. The middle
parameter of each of the
SetData calls refers to the
DataSource -- the
chartspace datasource is actually a collection, and multiple datasources --
i.e., multiple recordsets -- could be used. The
0 in our
indicates we are using the first (and only) data source in the collection.
Once the series are set, we now loop through the series collection for the chart, and set
the actual datapoints themselves. Here, the
SetData method is executed against
the series object, not the chart; this allows us to distinguish the results for one student
vs. another. The first of the two series
SetData calls maps the test
(test number) field from the database to the category (x) axis, and the second call maps
the test scores from the database to the value (y) axis.
Don't underestimate the elegance of the above; in just a few short lines we've organized our output into multiple series with the proper ordered pairs for the datapoints, neatly separated from any presentation logic.
After establishing the chart makeup and the data sources, we decorate our
little chart with some basic captions for the axes. The
Axes collection is
unordered, which may seem a bit odd until you realize (OK, so it took me
awhile, anyway) that you can stick axes all over the place such that relying
on axes to be in x-y-z order wouldn't hold up very well in more complex
situations. So while you can trial and error your way to captioning the
axes, a better approach would be to enumerate the collection and use the
constants for axis type to check:
Finally, we take our chart and export it to a graphic format (using one of our two functions), display it on our page, and clean up (using the other of our functions described below).
Below you can see the graph produced by the Office Web Components representing the values in our database. Note that this image is saved on the Web server as a GIF image by the OWC, so any browser can view the image.
Hmmmm. That yellow wouldn't be my first choice, and it's not real clear where the datapoints actually are. Let's make a couple of quick modifications to our chart. Instead of the plain line chart, we'll go with the LineMarkers version to give our datapoints a bit more visibility. Replacing the original chart type assignment with:
will have the desired effect, we hope. Then, right before we export the chart, let's change the color of that third series:
The color assignments in OWC can accept either RGB values or standard
16-color HTML string representations; the two values we are setting are the
interior color of the marker in the third series in the chart's series
Charlie), and the line color of that same series. (The
downloadable source for this example includes these modifications.)
That's more like it! Now that we've looked at how to generate a GIF representation of a graph using Office Web Components, we still are faced with one problem: removing the graph GIFs once we've displayed them to the visitor! In Part 3 we'll examine how to remove out-of-date GIF graphs quickly and easily!