White Papers                  Home |  White Papers  |  Message Board |  Search |  Products |  Purchase | News |  

Using the Office Web Components
to create dynamic charts


By Rick Strahl


Last update: 11/16/2001


Source code to this article:




Graphing can add dramatic visual impact to your applications. For many application that use complex data graphing is an indispensible tool for providing a consolidated view of the data. In this article I'll show you how you can use the Microsoft Office Web Components to build graphs on the fly both in GUI applications and Web apps, and introduce a class that you can use to simplify using those components for easy graphing of data from common Visual FoxPro data structures.


Although I wouldn't consider graphing one of the essential features of a typical application, clean graphs can add significant impact and visualization to applications, especially Web applications. Creating graphs in Web applications generally requires third party tools that have the ability to create graphs dynamically. There are a

Where to find the Office Web Components

number of tools available from third party vendors, but most of these tools are fairly expensive to implement on a Web server and not always straightforward for generating and presenting dynamic graphs. In this article I'll discuss how to use the Office Web Components (OWC) to generate graphs for your GUI and Web applications. Web applications need to deal with graphics a little bit differently than GUI applications because the graphs are generated as files and need to be cleaned up quickly in order to avoid overloading the Web Server's disk drive. I'll also provide a wrapper class and a couple of examples that makes it easy to test the various graph types available.


Microsoft includes an extremely useful set of COM components in the Office Web Components. The components do much more than just graphing, which is just one of the components. They also include a spreadsheet component, a pivot table creation tool all of which are features that you probably have used in one form or another from inside of Office. These components are toned down versions of their full-blown counter parts in the actual Office product, but they are fully compatible with their big brother. OWC is large and there's a lot to it and I'm only going to focus on the Chart control in this article.

OWC 101

The Office Web Components follow a typical Microsoft collection based model. For graph generation you start out with a base document (the ChartSpace as Microsoft calls it) to which you can add one or more graphs. In general you'll want to create only a single graph and even if you end up creating more than one graph, you'll typically end up generating separate graphs, using separate graph objects and outputs. Using the standard collection based model here's how you create a chart from an array:


*** Data to render

DIMENSION laLabels[3]


DIMENSION laData2[3]


laLabels[1] = "Data Item 1"

laLabels[2] = "Data Item 2"

laLabels[3] = "Data Item 3"


laData[1] = 100

laData[2] = 255

laData[3] = 159


laData2[1] = 200

laData2[2] = 250

laData2[3] = 195



*** If you have Office 2000/XP use OWC.Chart instead

*** The latest download version uses OWC10.ChartSpace

loChartSpace = CREATEOBJECT("OWC10.ChartSpace")

oConst = loChartSpace.Constants


loGraph = loChartSpace.Charts.Add()

loGraph.Type = 0   && Column Graph


*** the following are optional

loGraph.HasLegend = .T.

loGraph.PlotArea.Interior.Color = "LightYellow"


loChartSpace.HasChartSpaceTitle = .T.

loChartSpace.ChartSpaceTitle.Caption = "Sample Data"

loChartSpace.ChartSpaceTitle.Font.Bold = .T.


*** Create the value arrays and presize them each


loGraph.SeriesCollection(0).Caption = "Legend Series 1"

loGraph.SeriesCollection(0).SetData(oConst.chDimCategories, oConst.chDataLiteral, @laLabels)

loGraph.SeriesCollection(0).SetData(oConst.chDimValues, oConst.chDataLiteral, @laData)



loGraph.SeriesCollection(1).Caption = "Legend Series 2"

loGraph.SeriesCollection(1).SetData(oConst.chDimValues, oConst.chDataLiteral, @laData2)


lcFile = SYS(2023) + "\" + SYS(2015) + ".gif"

loChartSpace.ExportPicture(lcFile, "gif",;




GoUrl(lcFile)           && Show Gif image


The code starts out with the Chartspace which can be thought of as the Canvas. Multiple charts can live in a single canvas, but generally you'll want to create a single graph. You then add a new graph object and tell it the type of graph to create and the title to set for it. The Graph object has a large number of properties and methods you can set and call to customize the look of the graph itself. For example, the background color, whether there's grid, the size and so on. Use Intellisense to discover all the properties available. Once the graph is configured we can add one or more data series to it. A series is a set of data points and each data point is provided either as an array or a string. Arrays tend to be much cleaner, so that's what I'll use here. If you use strings they should contain tab or comma delimited data. One of the data series also must provide a label series which is the label for each of the data points. In addition each series can also have a Caption which becomes the legend for the series in the graph. At this point you can further configure the way the graph is formatted.


To actually render the graph you call the ExportPicture method with a filename to store the GIF file. Figure 1 which shows the result from the rendered image displayed in a browser.


Figure 1 – The result from the first hand rendering of the OWC Chart Conntrol shows the typical elements that you use in your graphs.


The Chart object provides about 40 different graph types you can utilize and most will work


with this very same syntax above. A few of the more esoteric graphs require specific number of series series set. The graph types are defined in a special Constants object (stored in the oConst member in the code above). I've provided method in the class below that retrieves all the graph types into a cursor so you can cycle through them easily.


Easy street with a class wrapper

While the above code is pretty straight forward, I like to make life easy on me when creating graphs, so I can create a graph with a few lines of code. To do this, I created a FoxPro wrapper class for the Chart control that allows rendering directly from a FoxPro cursor, a set of arrays or from a single multi-dimensional array. The class basically abstracts the base configuration of a chart and provides a simpler mechanism for dealing with the graphing so you can create common VFP data structures and render the data from there.


Using the wwWebGraphs class with cursors looks something like this:




DO wwWebGraphs && Load libs


loGraph = CREATEOBJECT("wwWebGraphs")

loGraph.cPhysicalPath = SYS(2023) + "\"


loGraph.nGraphType = 10

* or loGraph.nGraphType = "LINE"


*** Generate our graph data

CREATE CURSOR TEMP (Label c(20), Year_1998 I, Year_1999 I, Year_2000 I)


INSERT INTO temp (Label, Year_1998, Year_1999, Year_2000)
  Values ("Income",90000,105000,118300 )

INSERT INTO temp (Label, Year_1998, Year_1999, Year_2000)
   Values ("Expenses",32200,55000,65800 )

INSERT INTO temp (Label, Year_1998, Year_1999, Year_2000)
  Values ("Taxes",20400,31500,29900 )

INSERT INTO temp (Label, Year_1998, Year_1999, Year_2000)
  Values ("Travel",10000,10800,22200 )

INSERT INTO temp (Label, Year_1998, Year_1999, Year_2000)
  Values ("Business",40100,49100,53200 )


*** Set up the graph



*** Generate the file and return link information



*** Display the graph in a Fox window



*** or: GoUrl( loGraph.cPhysicalPath + loGraph.cImageFilename )


The concept behind the class is essentially to use simple Fox data structures like a cursor with a specific structure to render the graph. This scenario maps very closely to most common cross tab reports. With cursors the following rules apply:



The class also contains methods for formatting the graph such as cTitle, cBackgroundColor, cSeriesColor and so on, which are applied to the graphs. There's also some logic that deals with special graph types like Pie charts which behave differently than other charts.


There are three methods that do the work for all of this: GraphSetup which is basically used for common graph setup that creates the actual graph object and is used by all the graph generation methods (Cursor, Array and Multi-Dimensional Array).


* wwWebGraphs :: GraphSetup


***  Function: Configures the basic Graph Layout

***    Assume: Basic layout used for all graph renderers

FUNCTION GraphSetup()

LOCAL loGraph


loGraph = THIS.oOWC.Charts.Add()

loGraph.Type = THIS.nGraphType


IF THIS.nShowLegend > 0

  loGraph.HasLegend = .T.



loGraph.PlotArea.Interior.Color = THIS.cBackColor


IF !EMPTY(THIS.cCaption)

   THIS.oOWC.HasChartSpaceTitle = .T.

   THIS.oOWC.ChartSpaceTitle.Caption = THIS.cCaption

   THIS.oOWC.ChartSpaceTitle.Font.Bold = .T.



RETURN loGraph


The method returns a graph object which is then further customized by the actual data managing methods like ShowGraphFromCursor, ShowGraphFromArray and ShowGraphFromMultiDimensionalArray. These methods basically deal with mapping their data format to the arrays that the Chart object needs to display the data. Here's the code for ShowGraphFromCursor:



* wwWebGraphics :: ShowGraphFromCursor


***  Function: Graphs a chart from a cursor.

***    Assume: Col 1   - Label column

***            Col 2-n - Data columns

***    Return: nothing


FUNCTION ShowGraphFromCursor()

LOCAL x, y, loGraph, lnFields, lnRows, lnSeries, ;

      lcArray, oConst, lcElement


lnFields = AFIELDS(laFields,ALIAS())

lnRows = RECCOUNT()

lnSeries = lnFields - 1


*** Create labal array from the first column of the table

DIMENSION laLabels[lnRows]


*** Create the value arrays and presize them each

FOR x = 1 TO lnSeries

  lcArray = "DIMENSION laValues" + TRANSFORM(x) + "[" +;
            TRANSFORM(lnRows) + "]"




x = 0


   x = x + 1

   laLabels[x] = TRIM(EVALUATE(FIELD(1)))


   FOR y = 1 TO lnSeries

      lcElement = "laValues" + TRANSFORM(y)+ "[" + ;

                  TRANSFORM(x) + "]"

      &lcElement = EVALUATE(FIELD(y+1))




oConst = THIS.oOWC.Constants

loGraph = THIS.GraphSetup()


FOR x = 1 TO lnSeries


  loSeries =  loGraph.SeriesCollection(x-1)


  IF !INLIST(THIS.nGraphType,18,19,58,59)  && Pie filter

     *** Pies can't use interior colors well

     loSeries.Interior.Color = ;
             EVAL("THIS.cSeries" + TRANSFORM(x) +"Color")


     IF x > 1

        EXIT   && PIes can't render more than one series




  lcArray = "@laValues" + TRANSFORM(x)

  loSeries.Caption =

         PROPER(STRTRAN(TRIM(laFields[x+1,1]),"_"," "))

         oConst.chDataLiteral, @laLabels)


         oConst.chDataLiteral, &lcArray)




This code converts the columns of the cursor into array columns and fixes them up for use in the Chart object. This method doesn't actually render the graph yet, but it assigns all the data. To get the graph generated the GetOutput method is used. Although this may seem a little odd naming, it stays with my convention for using GetOutput as the name of rendering functions that generate output. In this case the output is a disk file that contains a GIF image of the graph and Web adjusted path to the image in the form of an image tag (for example: <img src="/webpath/img_123123.gif">) that is returned as the result. I'll talk more about this in a minute when I'll show you how to use these generated graphs on the Web. For now, here’s the generation code:



* wwWebGraphics :: GetOutput


***  Function: This method actually creates the graph on

***            disk and returns a Web adjusted IMG tag

FUNCTION GetOutput()


*** Post Config Event for Graphic Layout



*** Delete graph files that have timed out



THIS.cImageName = "IMG" + SYS(2015) + ;

                TRANSFORM(Application.ProcessId) + ".gif"


*** Now actually create the image on disk                 

THIS.oOWC.ExportPicture(THIS.cPhysicalPath + ;


RETURN [<img src="] + THIS.cLogicalPath + ;
       THIS.cImageName + [">]


The first thing that happens is that the SetGraphicsOptions 'event' is called. This is a hook method that can be implemented in a subclass to allow customizing the graph before it is actually rendered to disk. It passes the Graph object so you can modify anything at all about the graph before rendering in this event method.


The wwWebGraphs class supports automatic timeout of graph files generated by setting the nImageTimeout method which specifies the number of seconds that an image is to stick around. Whenever an image is rendered the DeleteFiles function call checks for timed out image files and deletes them. This is sort of a self-maintaining garbage collection mechanism that makes sure that old images get cleaned up. This is of course important for Web Applications which can potentially generate huge number of image requests. More on that in a minute though.


For now let me just add that there are also several helper methods such as ShowGraphinForm, which pops up a preview form for the graph and GetGraphTypes that returns a cursor of all the graph types supported. 

Caution: This demo is graphic

To demonstrate this class in action I thought I'll create a fun example that pulls pulls historical stock data off the Web and graphs this data in a Visual FoxPro form. You can then cruise through the different graph types available. Figure 2 shows what the form looks like.


Figure 2 – the sample GUI form using the wwWebGraphs class pulls historical stock data from the Yahoo Web site, merges the data into a cursor and graphs it.


The sample form retrieves historical stock data from the Yahoo Web and stores the data into a cursor. I created a StockHistory class that performs the data retrieval and the code to merge the data into a cursor that's ready for rendering. The key code to pull the data from the Web is in the GetMonthlyHistory method:


FUNCTION GetMonthlyHistory(lcSymbol,ldFromDate,ldToDate)


IF "," $ lcSymbol






lcSymbol = ALLTRIM(lcSymbol)  


lcUrl = "http://chart.yahoo.com/table.csv?" + ;

        "s=" + lcSymbol +;

        "&a=" + TRANSFORM(MONTH(ldFromDate)) + ;

        "&b=" + TRANSFORM(Day(ldFromDate)) + ;

        "&c=" + TRANSFORM(Year(ldFromDate)) + ;

        "&d=" + TRANSFORM(MONTH(ldToDate)) + ;

        "&e=" + TRANSFORM(Day(ldToDate)) + ;

        "&f=" + TRANSFORM(Year(ldToDate)) +;

        "&z=" + lcSymbol +;






lcHTML = oHTTP.responseText


IF lcHTML # "Date,"




lcFile = SYS(2015) + ".csv"



CREATE CURSOR ("Stock_" + lcSymbol ) ;

   (Date C(10), Open N(12,4), High N(12,4), ;

    Low N(12,4), Close N(12,4), Volume I)




ERASE (lcFile)




This method calls into the Yahoo Web site using the XMLHTTP component that's part of the Microsoft XML parser (version 2 and later). The code basically creates a URL containing a number of query string parameters that pass the date info and format of the query to the Yahoo search page. This request asks for a response in CSV format which VFP can directly import. Note that you can get data in a variety of formats from this Url (check out: http://chart.yahoo.com/d) – I'm choosing monthly data here, but you could also get daily or weekly data if you choose.


The resulting string is simply dumped to a temporary file and then imported into a temporary cursor which is created with a specific name that is STOCK_MSFT where the second part is the symbol name.


Another method in the class MergeCursors can then merge multiple cursors into a single crosstab like cursor that looks something like this:






Jan '01




Feb '01




Mar '01





with one column for each stock. The high level GetMonthlyHistoryMulti demonstrates how this works with multiple symbols which are specified in a comma delimited list:


FUNCTION GetMonthlyHistoryMulti(lcSymbols,ldFromDate,ldToDate)


lnSymbols = ALINES(laSymbols,lcSymbols,",")


FOR x = 1 TO lnSymbols






The sample form then puts all these pieces together using it's Graph method which pulls the inputs from the form and renders the graph and assigns it to a picture box control.


* Form ChartDemo::Init

DO wwWebGraphs



loGraph = CREATEOBJECT("wwWebGraphs")


*** Retrieve all the graphing types



THISFORM.cmbGraphType.RowSourceType = 2  && Alias

THISFORM.cmbGraphType.RowSource = "TGraphTypes.Name"

THISFORM.cmbGraphType.Value = 10   && Ticked line graph

THISFORM.lblGraphType.Caption = "10"


THISFORm.txtFromDate.Value = {01/01/2001}

THISFORm.txtToDate.Value = DATE()



* Form ChartDemo::Graph



lcSymbols = THISFORM.txtSymbols.Value

ldFromDate = THISFORM.txtFromDate.Value

ldToDate = THISFORM.txtToDate.Value

lnType = TGraphTypes.ID


IF EMPTY(lcSymbols)

   WAIT WINDOW "Make sure you select some symbols" NOWAIT




IF !llNoRefetch

   oHist = CREATEOBJECT("StockHistory")

   IF !oHist.GetMonthlyHistory(lcSymbols,;


      WAIT WINDOW "Failed to retrieve stock info"







oGraph = CREATEOBJECT("wwWebGraphs")

oGraph.nGraphType = lnType

oGraph.nImageHeight = THISFORM.picGraph.Height

oGraph.nImageWidth = THISFORM.picGraph.Width


*** Create the graph

IF !oGraph.ShowGraphFromCursor()

   WAIT WINDOW "Couldn't create the graph..."






*** Assign the graph to the Picture box control

THISFORM.picGraph.Picture = oGraph.cPhysicalPath + ;



*** Resize the form so the graph becomes visible

THISFORM.Height = THISFORM.picGraph.Top + ;




When you run the form you put in your symbols as a comma delimited string and click the graph button to go out to Yahoo and fetch the data. The data then comes back and is merged into the cursor structure described a minute ago containing columns for each symbol along with the date value in the first column. The graph is then generated off this cursor.


When you click on the graph type combo box the chart is redrawn without going back to the server to retrieve the data because all we need to do is recreate the graph. You can browse through the 40 or so graphs available to check out the various types available.

Moving it to the Web

Creating charts on the Web is not all that different than it is creating local graphs – in theory. In practice there are a few gotchas you have to be aware of. First off is the issue of how to actually server the images created. The OWC require that output be created on disk so you probably want to serve the image from disk through a Web link.


When you serve a Web Page with an image on it you can't serve both the page and the image or images at the same time. Instead embedded IMG tags cause another trip to the Web Server to load and display the images. How you serve those images is up to you, but with dynamic images like charts created on the fly things are a little tricky because you have to deal with the lifetime of the image. There are two ways to serve dynamic images:




Both have advantages and disadvantages. File serving is easy because all the processing an happen in a single request, but it requires managing the temporary files that are generated and linked to the client. Since images are created on disk they must stick around long enough for users to see their page and possibly refresh the page, but they can't stick around permanently lest they clog the Web Server's physical storage. Binary image serving has more overhead and might require state passing between requests which means you might have to duplicate functinality in the HTML and image serving requests. But once you've served the image you're done.


I prefer the former approach because generally there's much less overhead in it. wwWebGraphs actually helps with this by providing two properties: cPhysicalPath and cLogicalPath which specify the path references to the image. The physical path we've used already when generating the image in the GUI application – it specifies the physical directory on the hard disk where the image file is to be created. The logical path should be the path that the Web Server can see that image at. Typically this will be a mapped virtual directory path. Figure 3 shows an example of a Web application that's using the same class I showed in the GUI form to generate its graphs.


The difference here of course is that that the graphs are generated on the Web Server by the Web backend application.


Figure 3 – Two graphs rendered on the Web Server and displayed as part of this stock portfolio application. You can check out this sample at: http://www.west-wind.com/wconnect/soap/soapstockportfolio.wwd.


The following code using Web Connection looks like this:

IF llGraph

   *** Query the portfolio data for this user

   SELECT Symbol, Qty * Price as Position ;

         FROM Portfolio ;

         WHERE UserId = lcUserID ;

         ORDER by 1 ;

         INTO CURSOR TGraphData


   IF _TALLY > 0     

      lcSymbols = ""


         lcSymbols = lcSymbols + TRIM(Symbol) + ","






      *** Use the same StockHistory class used before
      *** to create crosstab like cursor of dates to symbols

      oHist = CREATEOBJECT("StockHistory")



      *** Fix up to not allow Office date formatting



      *** Generate the first graph

      oGraph = CREATEOBJECT("wwWebGraphs")

      oGraph.cCaption = "Monthly Performance"

      oGraph.nImageHeight = 300

      oGraph.nImageWidth = 550

      oGraph.nGraphType = "LINEPOINTS"


      *** These are the key differences!

      oGraph.cPhysicalPath = Config.cHTMLPagePath + "temp\"

      oGraph.cLogicalPath = "/wconnect/temp/"




      *** Store the image link: <img src="/wconnect/temp/img_01.gif">

      pcGraph1Image = oGraph.GetOutput()


      *** Now graph the PIE chart of percentages

      SELECT TGraphData

      oGraph.Clear() && Clear the series data

      oGraph.cCaption = "Portfolio Breakdown"

      oGraph.nGraphType = "PIE3D"

      oGraph.nImageWidth = 250



      *** Save the second image link

      pcGraph2Image = oGraph.GetOutput()



   pcGraph1Image = ""  

   pcGraph2Image = ""



The key things that are different here than in the GUI app are the cPhysicalPath and cLogicalPath assignments. In this app I generate the images to a special Web accessible temp directory sitting off my virtual root for the application. I point both the physical disk path and the mapped virtual path to it. That's "d:\inetpub\wwwroot\wconnect\temp\img_01.gif" and "/wconnect/temp/img_01.gif" respectively. The GetOutput() call returns a full image link like this:


<img src="/wconnect/temp/img_01.gif">


These image links are stored in variables that are then embedded into an external template like this:



<%= pcGraph1Image %><%= pcGraph2Image %>



to achieve that side by side look. Notice that both images are sized with the same image height which makes the butt up against each other looking like a single consistent graph.


I'm using Web Connection for the example above. If you're using Active Server Page you'd be calling this same code out of a VFP COM component and there would be a method responsible for running the above code. The only difference in coding there would be that you'd have to pass the image links back to the ASP page somehow, probably with custom properties on the COM component, so instead of using the PRIVATE variables I used you'd have maybe a cImageTag1 and cImageTag2 property that you would use and then embed those with <%= oVFPCOM.cImageTag1 %>.


With ASP you will also need to make sure that Web Server account (IUSR_ or IWAM_) has rights to write the file in the cPhysicalPath directory, since the COM component will run under these low permission accounts that by default won't have write access anywhere. Alternately, you can add your component to COM+ and set the Impersonation to an Administrative user that does have access.


Note that wwWebGraphs will take care of cleaning up the generated image files everytime you create a new graph. Whenever a new graph is created GetOutput() checks for timed out images and cleans them up. This should leave you with only a few image files sitting around at any given point in time. Make sure you keep the nImageTimeout to a short value – the busier the site the shorter the interval. In reality this timeout doesn't need to be long because it needs to last only as long as it takes for the client to pick up the image from disk. After that refreshes will come from cache anyway in most cases so a refresh shouldn't be a problem. For busy sites 1 minute or less is probably a good idea.

And off you go!

Well, there you have it – Web graphing made easy using tools that you probably already have installed on your system. I hope this article has given you some ideas of how you can integrate graphing into your apps with very little programming effort. Graphing is one of those features that can make a professional impression for your app and it can add that one last touch to make your app more useful when representing complex data. With the right tools adding graphing capabilities to your app is quick and easy. The hardest part likely will be formatting your data so that it can be readily graphed. I think you'll find that with simple graphing features at hand you'll find lots of places where a graphical view of data can come in handy. So, get with it and have some fun sprucing up your user display interface...




Source Code

This article includes the wwWebGraphics class, an HTML Help File for the class and the StockHistory sample class and form. You can download the code from:



Online Web Stock Portfolio Example:



Office Web Components



For comments, you can post a message at:




Rick Strahl is president of West Wind Technologies on Maui, Hawaii. The company specializes in Web and distributed application development and tools with focus on Windows 2000, ISAPI, .Net and Visual Studio. Rick is author of West Wind Web Connection, a powerful and widely used Web application framework for Visual FoxPro and West Wind HTML Help Builder. He's also a Microsoft Most Valuable Professional, and a frequent contributor to magazines and books. He is co-publisher and co-editor of CoDe magazine, and his book, "Internet Applications with Visual FoxPro 6.0", is published by Hentzenwerke Publishing. For more information please visit: http://www.west-wind.com/.


Amazon Honor System Click Here to Pay Learn More





  White Papers                  Home |  White Papers  |  Message Board |  Search |  Products |  Purchase | News |