Chart Generation
Volume Number: 17
Issue Number: 3
Column Tag: Programming Techniques
Chart Generation with AppleScript & Excel
By Rich Allen
Edited by Cal Simone
A friend of mine likes to say "everything is easy once you know how", Updating an
Excel spreadsheet and generating an accompanying chart is easy using a little
AppleScript, and Microsoft Excel, once you know how!
People like charts. Charts are an easy way of seeing a lot of data and understanding it
with a quick glance. Using AppleScript to update an existing workbook, a simple
VisualBasic macro for exporting a chart as a JPEG file and the iDo Script Scheduler
included with OS 9 is all you need to create a regularly updated chart. This article will
explain how to
• Use AppleScript to parse a data file for required values
• Transmit those values to Excel
• Tell Excel to export a chart as a JPEG file for use as part of a web page
• How to setup iDo so the chart is updated regularly
A note on conventions used in the AppleScript source listing; those words that have an
underline are application keywords. These terms have a specific meaning to either the
enclosing tell block or to AppleScript itself. See the preferences panel of your script
editor for implementing this option.
First let's look at using AppleScript to open a file of raw data and parse out the values
that we really want. For our raw data file, we will use a tab-delimited text file as
shown in Listing 1.
Listing 1: Sample raw data
Date Hour PhoneNum Type Members Peg Busy Usage
12/20/00 09 5551234 DNH 3 7 0 17
12/20/00 09 5552345 DNH 4 9 0 34
12/20/00 09 5553456 DNH 2 1 0 3
12/20/00 09 5554488 DNH 4 5 0 6
For this project, we require the values under the headings labeled "Date", "Hour",
"Peg" (number of telephone calls), and "Usage" (connect time of all calls) for
telephone number 5554488. We will assume that this raw file is updated once each
hour, although we will want to verify this, and that our chart will track peg values
versa usage value count on an hourly basis. The Excel workbook will be pre-built with
two worksheets; one containing the chart (chart) and another with the values that feed
that chart (data). See Figures 1 and 2 for examples of these worksheets.
Figure 1. Sample chart output.
Figure 2. Property declaration.
OK, let's start writing a script! The script will use three properties, values that will
be used throughout the script. These properties designate the path to the folder
containing our files, the name of the raw data file, and the telephone number to find.
See Listing 2.
Listing 2: Sample of worksheet data
property folderPath : "MacHD:Desktop Folder:MacTech:
property fileName : "rawdata
property phoneNumber : "5554488
It is always a good idea to have a try block around scripting statements to catch any
error that may arise. The main portion of the script, Listing 3, is completely enclosed
in a single try block. Any error encountered will be handled by the on error section.
For example, if the raw data file can not be found when AppleScript tries to open it, an
error will occur, passing control to the on error routine.
The script starts by setting fileRef to an empty string. By doing this, if an error does
occur, the error handler will be able to determine if the data file is open, if so, closing
it. If the error routine is invoked and fileRef is the empty string, then the error was
generated while attempting to open the file thus there will be no need to execute the
close access statement.
Next the script will attempt to open the raw data file, concatenating the folderPath and
fileName properties into a single string as input for the open for access statement and
assigning the resulting file reference number to the variable fileRef. From this point
on, we can access data from the file by referring to the fileRef variable. We know that
the data file is tab-delimited (a tab character seperates each value in the file), so
next, the script is set to use the tab character as its text item delimiter. (Normally
AppleScript's text item delimiter is set to an empty character (""). Since we already
know that each value of interest per line is separated by the tab character, by setting
AppleScript's text item delimiters to a tab we can easily refer to any value by its
position.)
The sample data file contains eight items per line; in this case the date, the hour,
phone number, phone number type, number of members associated with the phone
number, a peg count, number of busies and the usage. The variable num will be set to
the empty string and will later be set to the telephone number from each line that is
read from the data file and then compared to the property phoneNumber to determine a
positive match.
At this point the script is ready to start reading each line of the data file until it finds a
match of the phone number read and the desired number. Reading a line of the data file
is done with the standard read statement; reading from the current file position until
the next encountered return character. If the two telephone numbers do not match, the
next line is read and phone numbers compared. If the desired phone number is not
found before the end of the file (eof) is reached then an error will be created by
AppleScript and handled by the on error portion of the run handler, otherwise the
close statement will be executed to close the data file.
Listing 3: The run handler
try
set oldDelimiter to AppleScript's text item delimiters
set AppleScript's text item delimiters to tab
set fileRef to
set fileRef to open for access (folderPath & fileName)
set num to
repeat until num is phoneNumber
set dLine to read fileRef before return
set num to text item 3 of dLine
if (phoneNumber is num) then
UpdateWorkbook(dLine)
end if
end repeat
close access fileRef
set AppleScript's text item delimiters to oldDelimiter
on error errMsg number errNum
if fileRef is not "" then
close access fileRef
end if
set AppleScript's text item delimiters to oldDelimiter
end try
Once a match has been found, the UpdateWorkbook handler (Listing 4) is called. (A
handler is basically equivalent to a subroutine.) The script starts with a tell statement
that will direct any statements following towards Excel. For any statement within the
tell block that are not directly understood by Excel, those statements will be handled
by AppleScript itself (e.g. if).
The first statement to Excel will open the required workbook by combining two of the
properties set initially and adding the ".xls" suffix to match the exact file name and
path of the required workbook. Since the workbook contains more then one worksheet
(chart and data), the script selects the data worksheet to assure the values from the
raw data file will be inserted into the proper cells.
For this workbook and chart we are tracking 24 hours of data. The data worksheet
contains a header in the first row with the following 24 rows containing the last 24
hours worth of data. Refer back to Figure 2 for an example.
We will want to verify that the raw data we have read is new before we update the
worksheet. By comparing the hour from the line of raw data with the last hour on the
data worksheet (cell B25), we can be sure that the raw data has been changed.
Since we want our chart to show peg versa usage information on an hourly basis, we
will want to drop the oldest hour's data from our chart and add the newest data. By
copying the last 23 hours data values (rows 3 through 25) and then pasting those
values into the first 23 hours data (rows 2 through 24), the script has effectively
dropped the oldest hour. (Note that the copyobject keyword, used here, comes from
Excel's Custom Suite - unfortunately Excel does not have the standard copy verb as
many other applications do.) Rows 24 and 25 are now the same. To add the newest data,
the script will simply set the new values in row 25, overwriting the extraneous
values.
Now that all the values have been updated, it's time to export a copy of the chart for
use on the web page. Excel has an export function using VisualBasic that can
accomplish this (the macro is contained within the workbook). We will not delve into
Excel VisualBasic macros in this article but will show the macro we're using here in
Listing 5. AppleScript uses the evaluate statement to communicate with Excel to run
the macro. Be sure to note the syntax here, evaluate requires the file name of the
workbook concatenated with the name of the macro with the exclamation point
character separating them.
The last job for the UpdateWorkbook handler is to save the changes made to the
workbook and close the workbook file.
Listing 4: The UpdateWorkbook handler
on UpdateWorkbook(dataLine)
tell application "Microsoft Excel
open (folderPath & phoneNumber & ".xls")
select sheet "data
if (text item 2 of dataLine != value of cell "$B$25") then
select range ("$A$3:$D$25")
CopyObject selection
select range ("$A$2:$D$24")
paste
set value of cell "$A$25" to text item 1 of dataLine
set value of cell "$B$25" to text item 2 of dataLine
set value of cell "$C$25" to text item 6 of dataLine
set value of cell "$D$25" to text item 8 of dataLine
evaluate (phoneNumber & ".xls!ExportChart()")
close ActiveWorkbook saving yes
end if
end tell
end UpdateWorkbook
Listing 5: VisualBasic macro to export chart as JPG
Sub ExportChart()
Charts("Chart").Export _
FileName:="pb0:Desktop Folder:MacTech:5554488.jpg
End Sub
Each time the script is run it will update the workbook and export a new chart for use
on a web page. This script does not provide a method of performing this task on a
regular basis so how do we get it to run on a schedule? One solution is to use the iDo
Script Scheduler that is included with the Mac OS. If it is not already in your control
panels folder, you will find it on the Mac OS 9 CD in the CD Extras folder in the
AppleScript Extras folder.
Open the iDo Script Scheduler control panel and click on the New button. Give this
event a name, set the trigger to "repeating", and select the start time and date. All that
is left is to click the Choose button, select the script (which we've saved as a compiled
script), and save the newly created event. Your chart will now be updated every hour
at the specified time. See Figure 3 for an example of the iDo event.
Figure 3. iDo event.
Although this is a short script with only limited error checking, it does demonstrate
the ease of integrating AppleScript with Microsoft Excel to create charts suitable for
use on any web page!
My friend also has another thing he likes to say, "Now that I know how, it's easy!
______________________________
Rich Allen currently works for a local telephone company in Alaska in the
traffic-engineering department. He has held a variety of telecommunications positions
since 1984 and has designed a number of Macintosh based data systems. His email
address is g3pb@alaska.net.