e’ve all seen Web sites that display great-looking, informative graphs generated from dynamic data. Stock market reports are one common example of these; sales and marketing information also frequently get this treatment. The graphs are not HTML-generated clunky bar graphs but are actually graphic images—GIFs or JPEGs—that use data generated from an ASP page—dynamically.

How can you do it too? In this 10-Minute Solution I describe how to generate graphs from an ASP page so you’ll never again have to jump through hoops with ASP code to generate static HTML displays. You’ll finally have the know-how to give your business data the slick, professional display that it merits.

Survey Your Options
Before I go into detail about my method, you should know all your options. There are two other ways you can go about this process. One is to go out and buy an expensive graphics rendering program or a graphics server that will do this job for you. There are some good ones out there, and if you are considering my trick for a very high volume Web site, forego my method and usea good graphics server application instead. Or you can sit down and code your own graphics server program using a tool such as Visual Basic or C++ that will accept dynamic data and spit out an awesome graphic chart. But then, if you can do that, you shouldn’t be reading this article anyway.

My method has a hidden advantage: I’ve already done all the work for you. Remember, bad programmers write code . . . good programmers steal . . . er, borrow!

The key to my method is to leverage the very robust and capable ChartWizard included in Microsoft Excel. The plan is to obtain our data from the user or from the database and then use Microsoft Excel to generate our graphs. We then display this Excel chart on our web page. Simple? Yes, and you only need know a few things about the Excel ChartWizard.

Prepare Your Environment Before You Begin
Before you get started you will need a copy of Microsoft Office on your Web server. If you have to install Office, take a moment to make sure Excel and the Excel ChartWizard are running properly. You will need to make sure that the graphic filters are installed as part of the set up to allow you to convert your chart object into graphic files.

If you are running Microsoft Internet Information Server (IIS) 4 on Windows NT you will need to change the parameters of the IIS metabase to allow out-of-process components to run on your site. This is required for your ASP page to invoke and automate the Microsoft Excel EXE. Details of this are provided by Microsoft in the article “Enabling the Use of Out-of-Process Components in ASP Using the Metabase” on MSDN Online.

Figure 1. Click here.

You can invoke the ChartWizard in Excel by clicking on the ‘ChartWizard’ toolbar button or by choosing Insert, Chart from the menu. You can then define chart types, chart source data, chart titles, etc. When you’re done, the ChartWizard returns a chart object either on your worksheet or on a chart sheet by itself. But there is a way to invoke the ChartWizard through code and through the ASP page code, and also save the Chart object as an external GIF or JPG file, and this is the very thing that you’re about to learn. Read on.

The Application in Action
For this exercise, let us first assume we are building an application that will accept a year’s worth of stock values for a single company and generate a line graph based on the values. For the sample, we are asking the user to type in the monthly stock values (this way, you can experiment with your own numbers). If I input values for Microsoft stock, the results would look like Figure 1.

When the user clicks the button ‘Generate Chart,’ the values are charted and the same page returns but this time as a chart (see Figure 2).

	Figure 2. Click here.

As you can see, there are three steps we need to perform in our application:

1. Obtain the data for the chart.
2. Generate the chart.
3. Display the chart.

You can see the complete application here.

Obtain the Data for the Chart
In our example, data is input by the user. But in the real world, you would obtain criteria from the user and then use it to grab the actual data from your database. To see how to obtain data from a database using ASP, take a look at my previous articles.

To simply obtain information from our users, we create a form with 12 text boxes to hold the 12 monthly values and another one to hold the company name. We do this within an HTML form construct as follows:

... more code... more code<%   For i = 1 to 12 %>      <%   Next  %>      
   
Stock Price for:
   
      
Date: 	  <%= FormatDateTime(i & "/01/99", 2)%>
      
$ :
      

As you can see, we display the text box for the name of the company first using the tag with a default value of ‘Microsoft’:

We then display 12 labels and 12 text boxes underneath it. Rather than hard code the tags 12 times, we loop through a counter variable and create our HTML. To generate the dates, we use the FormatDateTime function with a second argument of 2 to return a short date format. We also generate 12 text boxes with names v1 to v12 in the same loop.

You will notice in the above code a hidden variable called “pass.” The pass variable is my favorite way to determine whether this is the first time the page has been viewed or if the user is returning to the form after it has been submitted. If it is the first time, you can seed the text box with random, pre-filled values, which means the user isn’t forced to enter values.

Generate the Chart
All the magic happens when the user clicks the button that says ‘Generate Chart,’ which submits the form. At the very top of our ASP page, we determine whether the user is returning after the form has been submitted. If that is the case, we branch out to a different subroutine—HandleRepeatVisit—to generate the chart:

mintPass = Request("pass")Select Case Int(mintPass)   Case 1      HandleRepeatVisitEnd Select

At this point it makes sense for me to declare some constants and variable used in the code:

Dim xlapp       ' Our Excel AppDim wb       ' Our Workbook within the Excel AppDim ws       ' Our Worksheet within the Workbook   Dim crt       ' The chart objectDim SourceRange    ' The Source Range for the chart objectConst xlWorkSheet = -4167 Const xlLineMarkers = 65

Now we can actually begin the process of generating the chart by creating an instance of an Excel application by opening a new workbook and grabbing the first worksheet:

' -- Create an instance of Excel ApplicationSet xlapp = Server.CreateObject("Excel.Application")' -- Create a new workbookSet wb = xlapp.Workbooks.Add(xlWorksheet)' -- Grab the first worksheet of the new workbookSet ws = wb.Worksheets(1)

“ws” points to a blank worksheet in our Excel application in which we will dump the values we wish to chart:

' -- Insert the data the user requested' -- First, the titlews.Range("A1").Value = Request("co") ' -- defaults to "Microsoft"' -- Then the data in two vertical columnsFor i = 1 To 12     ws.Range("A" & i + 1).Value = FormatDateTime(i & "/01/99", 2)     ws.Range("B" & i + 1).Formula = "=" & Request("v" & i) Next

Because we are obtaining user-input values the Request object is used to call the values. First paste the company name within the cell “A1.” Underneath that, in columns A and B, we paste the date and the user-entered values, respectively. If we were obtaining data from a database, we would use very similar code, but instead of obtaining data from the Request object and placing it on the worksheet we would use a Recordset object.

At the end of this process, the Excel spreadsheet would look like the image below. Remember though, you cannot actually see this spreadsheet; it is generated in memory on your Web server (see Figure 3).

	Figure 3. Click here.

Next we prepare to generate our chart by invoking the ChartWizard through code. To do that, we need to get a handle to our range object containing the chart data and then use it to insert a chart object on the worksheet. We use the worksheet’s Range method to return a range object that we can work with and then add a new entry in the worksheet’s ChartObjects collection to obtain a new chart. The Add method of the ChartObjects collection accepts four parameters that determine the location and size of the chart in pixels:

' -- Set our source rangeSet SourceRange = ws.Range("A2:B13")' -- Create a new Chart ObjectSet crt = ws.ChartObjects.Add(20, 20, 300, 200)

Now it’s time to perform magic. But first, let’s take stock of what we’ve done so far. We placed a ChartObject object on our worksheet. The ChartObject object, in turn, has a property that returns a Chart object. The Chart object has a method we can invoke called ChartWizard. So, if we invoke the ChartWizard method of the Chart object of the ChartObject object within the worksheet, we get the equivalent of clicking the ChartWizard tool button on the toolbar. The syntax of this statement is:

Worksheet.ChartObject.Chart.ChartWizard Arguments

In our case, we will invoke it as follows:

Crt.Chart.ChartWizard Arguments

Now, the “Arguments” for the ChartWizard method are as follows:

expression.ChartWizard(Source, Gallery, Format, PlotBy, CategoryLabels, SeriesLabels, HasLegend, Title, CategoryTitle, ValueTitle, ExtraTitle)

1. expression: Required. An expression that returns a Chart object.
2. Source: Optional Variant. The range that contains the source data for the new chart. If this argument is omitted, Microsoft Excel edits the active chart sheet or the selected chart on the active worksheet.
3. Gallery: Optional Variant. The chart type. Can be one of the following XlChartType constants: xlArea, xlBar, xlColumn, xlLine, xlPie, xlRadar, xlXYScatter, xlCombination, xl3DArea, xl3DBar, xl3DColumn, xl3DLine, xl3DPie, xl3DSurface, xlDoughnut, or xlDefaultAutoFormat.
4. Format: Optional Variant. The option number for the built-in autoformats. Can be a number from 1 through 10, depending on the gallery type. If this argument is omitted, Microsoft Excel chooses a default value based on the gallery type and data source.
5. PlotBy: Optional Variant. Specifies whether the data for each series is in rows or columns. Can be one of the following XlRowCol constants: xlRows or xlColumns.
6. CategoryLabels: Optional Variant. An integer specifying the number of rows or columns within the source range that contain category labels. Legal values are from 0 (zero) through one less than the maximum number of the corresponding categories or series.
7. SeriesLabels: Optional Variant. An integer specifying the number of rows or columns within the source range that contain series labels. Legal values are from 0 (zero) through one less than the maximum number of the corresponding categories or series.
8. HasLegend: Optional Variant. True to include a legend.
9. Title: Optional Variant. The chart title text.
10. CategoryTitle: Optional Variant. The category axis title text.
11. ValueTitle: Optional Variant. The value axis title text.
12. ExtraTitle: Optional Variant. The series axis title for 3-D charts or the second value axis title for 2-D charts.

Figure 4. Click here.

Pay special attention to Item 2. If Source is omitted and either the selection isn’t an embedded chart on the active worksheet or the active sheet isn’t an existing chart, this method fails and an error occurs.

How did I know all that? Ah, it’s good to be a genius . No, actually all of the above can be found in the Microsoft Excel Visual Basic online help and documentation.

Now that we know what the syntax for the ChartWizard method is, we can use it in our code. In our case, we can either create a reference to Excel to be able to use intrinsic constants or we can simply replace the constants with our own or its numeric equivalents. So, our code is as follows:

crt.Chart.ChartWizard SourceRange, 4, , 2, 1, 0, 2, Request("co")      & " Stock Value"' -- Configure the Chart crt.Chart.ChartType = xlLineMarkerscrt.Chart.SeriesCollection(1).Name = "=Sheet1!R1C1"crt.Chart.HasTitle = Truecrt.Chart.Axes(1, 1).HasTitle = Truecrt.Chart.Axes(1, 1).AxisTitle.Characters.Text = "Months"crt.Chart.Axes(2, 1).HasTitle = Truecrt.Chart.Axes(2, 1).AxisTitle.Characters.Text = "Stock Price"

This code illustrates how to create a chart and then configure its settings. We could have also condensed all eight lines of code above manipulating the “crt.Chart” object into a single line of code by utilizing all the arguments in the ChartWizard method. At this point, our invisible Excel spreadsheet looks like Figure 4.

Display the Chart
The final part of the process is to save the chart as a graphics file that can be displayed in the browser, either a GIF or a JPEG. For our example, we’ll use a JPEG. If you prefer GIF, please remember that Unisys holds the patent on the LZW compression used in generating a GIF and requires you to pay them each time you create a GIF dynamically.

Suppose we save the chart as Test.jpg. We can then ask the browser to open up the chart by using the tag in HTML. But on a Web site there will be multiple users, and we don’t want them overwriting each other’s graphics. That means each user that accesses the chart will need a unique chart name, which in turn will quickly eat up disk space. To prevent that we need to come up with some technique to manage the deletion of these “used” charts. We could run a garbage collection routine each day, but a far better and more efficient way is to create a simple way of reusing graph names.

For our sample application we will build a reuse trigger based on time. The name of each graph generated will differ based on the time (in seconds) that it was requested. After 60 seconds the names are reused. This may not be the best technique available for reusing names, but it is fast, has no component overhead, and is sufficient for demonstration purposes. Other techniques would include using a unique name for each graph based on a GUID or a database key and then figuring out a technique to automatically delete the graphic when the user’s session expires:

' -- Determine the name to save this chart as. Use the current' --  Seconds value, overwriting previous ones   mstrFileName =  "junk" & Second(Now()) & ".jpg"

Now you can save the chart object as a JPEG using the ChartObject’s Export method:

' -- Save the chart on web server crt.Chart.Export Server.Mappath("/asppro/junk/") &      "" & mstrFileName, "jpg"

The Server.MapPath method is used to translate a virtual directory into an absolute physical path. The second argument of the Export method specifies the format to use for the graphic file. At the end of this process we will have a JPEG file on disk and its name within the variable mstrFileName.

You’ll need to do some detail work at this point, starting with the cleanup of our Excel application. We want to quit Excel without saving the worksheet; otherwise Excel will wait forever for a filename to be provided and hang the system.:

' -- Fool Excel into thinking the Workbook is savedwb.Saved = True' -- Set all objects back to nothingSet crt = NothingSet wb = Nothing' -- Quit Excel to conserve resourcesxlapp.QuitSet xlapp = Nothing

Finally, we want to make sure that when the user’s browser requests this image, it does not use an older copy of the image in its cache but, instead, reloads the image. To do this, we send some headers to our browser before sending actual HTML:

' -- Make sure the Image is not cached but is loaded fresh ' -- from the web serverResponse.AddHeader "expires","0"Response.AddHeader "pragma", "no-cache"Response.AddHeader "cache-control","no-cache"

We then send the same page back to the user. But this time, we modify the page and include the graphic file by using the HTML tag:

<% If mstrFileName <> "" Then      Response.write  ""    Else      Response.write ""   End If%>

And there you have it. You can generate professional-quality charts using ASP. Before you go crazy with this technique, let me warn you: it is very resource-intensive. Use it only in situations where a limited number of users generate charts infrequently. If you have a very-high-volume Web site, or just have a need to display lots of dynamic charts, loading Excel into memory and using it to generate charts is not ideal. In these cases, look for a Charting COM component that can accept your data and generate charts on the fly. You can hunt on the Web for ASP Chart Components to find third-party solutions.

To see the complete application in action and to download the entire source code, go here.