Perl to Applescript to Excel

[gimme_this_gimme_that]

This example will get you started on fetching data from MySQL and
inserting it into a MS Excel Worksheet.

Since this example uses Perl's DBI and DBD modules, the code for
fetching the data from Oracle or DB2 would be nearly identical.

To get charts and pivot tables would require that Perl/Applescript
open an Excel Template and insert the data into that. (I'm working on
a better example. I'm also working on simple VBA to Applescript
translations.)

#!/usr/bin/perl

use DBI;
use DBI:BD;
use Mac::AppleScript qw(RunAppleScript);

$dbname="employee";
$dbuser = 'fugi';
$dbpw = 'apple';
$drh = "";
$dbh = "";
sub db_connect
{
$drh = DBI->install_driver('mysql') || die("Cannot install driver:
$DBI::errstr\n");
$dbh = $drh->connect($dbname,$dbuser,$dbpw) || die("Cannot connect:
$DBI::errstr\n");
}

sub db_disconnect
{
$dbh->disconnect || die("Disconnect: $DBI::errstr\n");
}

sub fetch_data
{
db_connect();
# employee table has columns employeeID,name,job,departmentID
$sql ="select * from employee";
$stmt = $dbh->prepare( $sql) || die( "Prepare failed: $DBI::errstr
\n" );
$rc = $stmt->execute() || die( "Can't execute statement:
$DBI::errstr\n" );
$s = "";
while ( $record = $stmt->fetchrow_hashref() ) {
$s .= "{" . $record->{employeeID} . ",\"" . $record->{name} . "\",
\"" .
$record->{job} . "\"," . $record->{departmentID} . "}, ";
}
db_disconnect();
$s =~ s/,\s+$//;
$s = "{" . $s . "}";
}

$data_list = fetch_data();

## list1 looks something like {{20, 5, 60}, {10, 13, 12}}
## A14 is hard coded for now
$script=<<EOF;
set list1 to $data_list
on doinsert(list1)
tell application "Microsoft Excel"
tell active workbook
tell sheet "Sheet1"
set value of range "A14" to list1
end tell
end tell
end tell
end doinsert
doinsert(list1)
EOF

RunAppleScript($script) or die "Didn't work!";