Using Spreadsheet::WriteExcel

Question

I want to extract some information from one Excel sheet and re-format it into another one. The data structure looks something like:

      col1      col2
row1  School    1
row2  Dean      John
row3  No.stu.   55
row4  some irrelevant stuff
row5  School2   2
row6  Dean      Tony
row7  No. stu.  60
row8  some irrelevant stuff
row9  School    3
row10 Dean      James
row11 No.stu.   56
row12 No. teacher 20

The output I would like to achieve is:

      col1 col2 col3
row1 School Dean No.stu.  No. teacher
row2 1      John  55
row3 2      Tony  60
row4 3      James 56       20

And the code I have been advised to use to extract information from Excel is the following (thanks to hdb from PerlMonks).

use strict; use warnings;

use Spreadsheet::ParseExcel;

my ($infile) = @ARGV;

my $parser   = Spreadsheet::ParseExcel->new();
my $workbook = $parser->parse($infile);
die $parser->error unless defined $workbook;
my ($worksheet) = $workbook->worksheets();

my %data; # Accumulate data here
my $row = 0;
my $school = 0;
while (1) {
    my $cell = $worksheet->get_cell($row, 0);
    last unless defined($cell);
    my $key =  $cell->value();
    my $data = $worksheet->get_cell($row++, 1)->value();
    if( $key eq "School" ) {
        $school = $data;
    }
    else {
        $data{$school}{$key} = $data;
    }
}

Now the next step is to re-write everything into a new Excel sheet. How could I do that?

Answer 1

Your code is a bit broken. Here is mine:

use strict;
use warnings;

use Spreadsheet::ParseExcel;
use FindBin qw($Bin);

my ($infile) = @ARGV;

my $parser   = Spreadsheet::ParseExcel->new();
my $workbook = $parser->parse("$Bin/Test.xls");
die $parser->error unless defined $workbook;
my ($worksheet) = $workbook->worksheets();

my %data;
my $row    = 0;
my $school = "";
while (1) {
    my $cell = $worksheet->get_cell( $row, 0 );
    last unless defined($cell);

    my $key = $cell->value();
    my $value = $worksheet->get_cell( $row++, 1 )->value();

    if ( $key eq "School" ) {

        $school = $value;
        next;
    }

    $data{$school}->{$key} = $value;
}
sleep 1;

use Spreadsheet::WriteExcel;

$workbook = Spreadsheet::WriteExcel->new('Result.xls');

$worksheet = $workbook->add_worksheet();
my $col = 0;
$row = 0;

$worksheet->write( $row++, $col,
    [ "School", "Dean", "No.stu.", "No. teacher" ] );

foreach my $school ( sort keys %data ) {

    $worksheet->write( $row++, $col,
        [ $school, @{ $data{$school} }{ "Dean", "No.stu.", "No. teacher" } ] );
}
$workbook->close();