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