To read the article online, visit http://www.4GuysFromRolla.com/webtech/022101-1.2.shtml

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.

    The graph produced by the Office Web Components and database values.

    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.)

    This graph is a bit easier to read....

    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


  • Article Information
    Article Title: 4GuysFromRolla.com - Charting with Office Web Components (OWC), Part 2
    Article Author: Bret Hern
    Published Date: Wednesday, February 21, 2001
    Article URL: http://www.4GuysFromRolla.com/webtech/022101-1.2.shtml


    Copyright 2017 QuinStreet Inc. All Rights Reserved.
    Legal Notices, Licensing, Permissions, Privacy Policy.
    Advertise | Newsletters | E-mail Offers