Published: Wednesday, February 21, 2001
4GuysFromRolla.com - Charting with Office Web Components (OWC), Part 2
By Bret Hern
Read Part 1
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:
...
strChartAbsPath = Server.MapPath("/testpad/chart/temp")
strChartRelPath = "temp"
...
|
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:
...
set objChartSpace = Server.CreateObject("OWC.Chart")
set objChart = objChartSpace.Charts.Add()
set c = objChartSpace.Constants
...
|
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:
...
objChart.Type = c.chChartTypeLine
objChart.HasLegend = True
...
|
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: student,
test, and score:
...
set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "provider=sqloledb;data source=myserver;" & _
"initial catalog=testdb;user id=myuid;password=mypswd;"
set objRS = Server.CreateObject("ADODB.Recordset")
set objRS.ActiveConnection = objConn
objRS.CursorType = adOpenStatic
objRS.CursorLocation = adUseClient
objRS.Open "select * from testscore order by test"
...
|
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:
...
set objChartSpace.DataSource = objRS
objChart.SetData c.chDimSeriesNames, 0, "student"
for each objSeries in objChart.SeriesCollection
objSeries.SetData c.chDimCategories, 0, "test"
objSeries.SetData c.chDimValues, 0, "score"
next
...
|
The 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 objChart.SetData
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 SetData calls
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:
...
for each axis in objChart.Axes
axis.HasTitle = True
if axis.Type = c.chCategoryAxis then
axis.Title.Caption = "Test"
else
axis.Title.Caption = "Score"
end if
next...
|
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).
...
strChartFile = ExportChartToGIF(objChartSpace, strChartAbsPath, strChartRelPath)
Response.Write "<IMG SRC=""" & strChartFile & """>" & "<P>"
CleanUpGIF strChartAbsPath
...
|
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:
objChart.Type = c.chChartTypeLineMarkers
|
will have the desired effect, we hope. Then, right before we export the
chart, let's change the color of that third series:
objChart.SeriesCollection(2).Interior.Color = "red"
objChart.SeriesCollection(2).Line.Color = "red"
|
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
collection (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!
Part 3
