[Omaha.pm] Spreadsheet::WriteExcel hack

["Jay Hannah" <jhannah@omnihotels.com>]

Given a text file like this:

ALERT 1003417647|2005-05-06 13:07:03| |AMELIA|PITDTN|
ALERT 1003502312|2005-05-26 15:21:27| |BG|CLTDTN|

Separate the data into N new MS-Excel spreadsheets -- one per the value of column 4 ("PITDTN", etc.). 

So, you should end up with a PITDTN.xls Excel spreadsheet, a CLTDTN.xls spreadsheet, etc.

(In this case, using a reference for $row was silly and caused unnecessary debug time.)

j


#!/usr/bin/perl

use strict;
use Spreadsheet::WriteExcel;

my %xlss;
open (IN, "alerts_only");
while (<IN>) {
   # ALERT 1003417647|2005-05-06 13:07:03| |AMELIA|PITDTN|
   # ALERT 1003502312|2005-05-26 15:21:27| |BG|CLTDTN|
   chomp;
   s/^ALERT //;
   my @l = split /\|/;
   my $prop = $l[4];
   unless ($xlss{$prop}) {
      my $wkb = Spreadsheet::WriteExcel->new("$prop.xls");
      my $wks = $wkb->add_worksheet();
      $xlss{$prop}{wkb} = $wkb;
      $xlss{$prop}{wks} = $wks;
      my $row = 0;
      $xlss{$prop}{row} = \$row;
   }
   my $wkb = $xlss{$prop}{wkb};
   my $wks = $xlss{$prop}{wks};
   my $row = $xlss{$prop}{row};

   for (0..4) {
      $wks->write($$row, $_, $l[$_]);
   }
   $$row++;
}
close IN;

foreach my $prop (keys %xlss) {
   $xlss{$prop}{wkb}->close;
}