Progressive Perl for Windows: Use Microsoft Graph to Generate Charts Dynamically

Sometime during your career as an administrator, you’ll likely have to generate a chart dynamically. Regardless of whether you need to illustrate annual sales trends or the number of hits a Web site receives each month, generating a chart dynamically is easy if you have the right tool.

Several chart-generation tools for Perl are available. You can use modules such as GDChart, perl-Graph, and Chart::Graph, but these modules require C libraries that you need to compile and, in some cases, port to Win32. A better alternative is to use Microsoft Graph, a COM component that you can access from many Win32 applications.

Microsoft Graph typically finds its way onto most machines through the installation of Microsoft Excel or other Microsoft Office programs. When you create a chart in Excel, you’re using Microsoft Graph objects. Excel accesses these objects as COM objects. Perl can also access Microsoft Graph objects, which means that you can leverage from a Perl script the same rich graphing support that Excel has. To interact with Microsoft Graph, you use the objects, methods, and properties of the Microsoft Graph object model. You can find details about this object model on the Microsoft Developer Network (MSDN) Web site. In Microsoft Office XP (formerly code-named Office 10), you can also find this object model in a Help file called vbagr10.chm in the Office XP directory (usually at C:program filesmicrosoft office). Other versions of Microsoft Office have similarly named Help files in comparable locations.

Microsoft Graph’s power lies in its versatility. You can use its objects not only in scripts, Web pages, and scheduled batch jobs but also in remote applications. Using Distributed COM (DCOM), you can install Microsoft Graph on a remote machine. This setup is handy if you want to generate charts from Web-server–based scripts (e.g., .cgi scripts, .asp scripts) but don’t want to burden the Web server with the process load. You can use an application server to act as the back end and DCOM to access the Microsoft Graph application on that remote machine. Let’s look at how Microsoft Graph works and how you can use it to generate charts dynamically.

Understanding Microsoft Graph
When you create an instance of Microsoft Graph’s Application object, you can access two types of components: DataSheet objects and Chart objects. A DataSheet object represents a datasheet, which is similar to an Excel spreadsheet. A datasheet is basically a data grid that can contain values. Unlike Excel cells, datasheet cells can’t contain formulas. Datasheet cells can contain only numeric values and strings.

The Chart object is the graphical representation of the data found in the datasheet. The Chart object has many properties that you can use to customize charts. For example, you can use the properties to display a title (to which you can apply different fonts, sizes, and other attributes), a legend, horizontal and vertical gridlines, and axis labels. However, the really cool option is that you can choose the type of chart. Office XP’s Microsoft Graph comes with 14 chart types: area, bar, bubble, column, cone, cylinder, doughnut, line, pie, pyramid, radar, scatter, stock, and surface. Each chart type has several permutations, including a 3-D version. Microsoft Graph’s documentation describes all the available properties and options.

Creating the Perl Script
The script GenChart.pl, which Listing 1 shows, uses Microsoft Graph to create a 3-D line chart. GenChart.pl graphs data that the GetData() subroutine returns. This subroutine, which appears at the end of the script, returns an array of random data. Thus, the resulting chart’s data is meaningless and is for demonstration purposes only. However, you can easily change this subroutine to query information from a database containing the number of Web server hits daily, weekly stock quotes, or other data you might want to chart.

You begin GenChart.pl by loading the Win32::OLE extension, which lets you access COM components such as Microsoft Graph. Win32::OLE comes with Win32 Perl from ActiveState, so if you’ve already installed Perl, you’ll most likely have this extension installed. While loading Win32::OLE, you can import the in() and with() functions so that you can use them without having to specify their full namespaces (i.e., Win32::OLE::in and Win32::OLE::with).

Next, you load the Win32::OLE::Const extension. This extension searches for the specified type library and imports the library’s various constants so that you can use them in the script. In this case, you’re telling the extension to search for and import the constants from the type library called Microsoft Graph. (The full name of the type library is Microsoft Graph 10.0 Object Library, but the extension loads the first type library that starts with the specified string.) Importing the constants lets you refer to the constants’ names instead of their numeric values. For example, when you define the chart options in the %ChartOptions hash, you can specify xl3Dline instead of –4101 for the chart type.

The $VISIBLE variable before the chart-options code specifies whether users will see the Microsoft Graph application open and create the chart. For demonstration purposes, I set $VISIBLE to 1 to make the application visible. However, the creation process is much faster if the application is invisible (i.e., you set $VISIBLE to 0).

At this point, you call the Win32::OLE extension’s new() method to create an instance of Microsoft Graph’s Application object, as the code at callout A in Listing 1 shows. Typically, calling Win32::OLE’s GetActiveObject() method to reuse an existing instance of an application is good practice because it reduces memory utilization and processor load. However, with Microsoft Graph, you can have only one chart active per instance, which means that if you run two copies of your script, you need to create two new instances of the application. Otherwise, your scripts would modify each other’s charts.

If you want a remote machine to create the chart, you can use DCOM to connect to that remote machine. You just need to make one simple modification. In the code at callout A, you need to modify the first parameter that you pass to the new() method. You change the "MSGraph.Application" parameter to an anonymous array, such as ["appserver.mydomain.com", "MSGraph.Application"]. The first element in the array is the remote machine’s name or IP address; the second element is the class name.

The code at callout B is key to the script’s success. This code retrieves the DataSheet and Chart objects. With these objects, you manipulate how the chart looks and what it contains. For example, the foreach loop after callout B uses the Chart object to apply the chart options you defined in the %ChartOptions hash. The code at callout C uses the DataSheet object to add the data in the @Data array to the chart’s datasheet. While the script adds the data to the datasheet, you’ll see the chart being modified in realtime (assuming that you set $VISIBLE to 1). For each entry that the script adds to the datasheet, the script also plots a corresponding data point in the chart. In this case, each data point in the chart represents the data for a particular day.

The code between callout C and callout D configures the chart’s X and Y axes. This code specifies whether to display gridlines for each axis, the color of the tick marks, the font of the labels, and so forth. Note that this code uses the with() function that you imported at the beginning of the script.

The code at callout D configures the labels for the data points in the chart. This code sets the data-point labels’ font, location, style, and background. In addition, if multiple consecutive data points have the same value, the code at callout D hides the subsequent labels so that the chart displays only the label of the first data point. This arrangement makes the chart easier to read, especially when it contains many data points. Note that this code uses the in() function that you imported earlier.

When the chart is complete, you can export it to a file. As the code at callout E shows, you specify the file format as the second parameter of the Export() method. In this case, you’re specifying a .gif file, but you can specify any valid file extension for which you’ve installed a graphic export filter. Typically, graphic export filters for .gif, .jpg, and .tif files are installed by default and work for exporting charts.

The line of code after callout E uses Perl’s back tick (`) to initiate the start command. The start command creates the process needed to read the specified file type. Because you’re specifying the exported .gif file’s path, the Win32 system creates a process that’s registered to read .gif files. Note the empty quotes ("") that precede the $File variable. Although the empty quotes are odd, they’re necessary because of a quirk in the command processor (cmd.exe). The command processor expects the first string enclosed in quotes to be the title of the new process. Because the $File variable is enclosed in quotes to account for possible embedded spaces in the path, the command processor thinks that the path is the title. Therefore, passing in empty quotes prevents the start command from failing.

An Easy-to-Use, Convenient Tool
As you can see, Microsoft Graph is a handy tool that you can use to quickly and efficiently produce charts. Whether you need to create charts as part of an automated reporting system or as part of a Web interface for a database, Microsoft Graph is an ideal way to make sophisticated charts with a relatively small amount of work. Although more powerful charting utilities are available, you probably already have Microsoft Graph on your machine. If Microsoft Graph is on another machine in your network, you can use DCOM to easily access the remote machine. Thus, Microsoft Graph is not only easy to use but also convenient.