« Voter Registration Fraud: Outfit Destroying Democrat Registrations | Main | Just how limited are my broadcasting skills? »

October 15, 2004

Perl and Spreadsheet::WriteExcel Saves the Week

Every now and then I do something that fills my heart with the joy of Perl. This week I was asked to sift through 5 years of usage data and generate some meaningful data (in Excel format) to be used for a poster we're doing for the November meeting of the AAMC.

Although MySQL did most of the heavy computational lifting, the queries and results were complex enough to warrant a series of Perl scripts to build, run and organize results (including calculations like mean, mode, standard deviation etc).

At first I was spitting everything out into a file with tab-separated values, scp it to my desktop, import it into Excel and mail it off to the right folks. After the second time I went through that process I took a poke around CPAN and found Spreadsheet::WriteExcel which allowed me to do something simple like:

use Spreadsheet::WriteExcel;
my $workbook = Spreadsheet::WriteExcel->new("/some_web_dir/user_search.xls");
my $worksheet = $workbook->add_worksheet();
$worksheet->write(0,0,"data");

Very slick, I created a simple counter to increment the columns and rows as I wrote chunks of data, and had the file created in a web-accessible folder and eliminated 10 minutes of work for every iteration (which turned out to be a lot).

Update: the $worksheet->write() method's first two arguments are row, column. That's helpful in making sure your data flows the right way.

Posted by mike at October 15, 2004 3:41 PM