Excellent Charts with Microsoft Excel and ASP

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:

<FORM ACTION="10MinChart.asp" METHOD="post">
<INPUT TYPE="hidden" NAME="pass" VALUE="1">
<TABLE BORDER="0" CELLPADDING="5" CELLSPACING="2" WIDTH="750">
... more code
<TR>
<TD ALIGN="CENTER" VALIGN="TOP">Stock Price for:</TD>
<TD ALIGN="LEFT" VALIGN="TOP"><INPUT TYPE="text" NAME="co" SIZE="25"
VALUE="Microsoft"></TD>
... more code
<%
For i = 1 to 12 %>
<TR>
<TD ALIGN="LEFT" VALIGN="TOP">Date:
<%= FormatDateTime(i & "/01/99", 2)%></TD>
<TD ALIGN="LEFT" VALIGN="TOP">$ :<INPUT TYPE="text" NAME="v<%= i%>"
SIZE="10" MAXLENGTH="5"></TD>
</TR>
<%
Next %>
</TABLE>
<INPUT TYPE="submit" NAME="cmd" VALUE=" Generate Chart">
</FORM>

As you can see, we display the text box for the name of the company first using the <INPUT> tag with a default value of 'Microsoft':

<INPUT TYPE="text" NAME="co" SIZE="25" VALUE="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.