Login | Register   
RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Excellent Charts with Microsoft Excel and ASP-3 : Page 3

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 <IMG> 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 saved wb.Saved = True ' -- Set all objects back to nothing Set crt = Nothing Set wb = Nothing ' -- Quit Excel to conserve resources xlapp.Quit Set 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 server Response.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 <IMG> tag:

<% If mstrFileName <> "" Then Response.write "<IMG SRC=""/asppro/junk/" & mstrFileName & """>" Else Response.write "<IMG SRC=""spacer.gif"" WIDTH=""100"" HEIGHT=""1"" BORDER=""0"">" 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.

Rama Ramachandran is the Vice President of Technology with Imperium Solutions and is a Microsoft Certified Solution Developer and Site Builder. He has extensive experience with building database systems and has co-authored several books including "Professional Visual InterDev 6 Programming" and "Professional Data Access" (Wrox). Rama teaches Visual Basic and Web development at Fairfield University and University of Connecticut.
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date