Here is a version that is more dynamic but assumes data is columns A and B starting from row 1:

School    1
Dean    John
No.stu.    55
School    2
Dean    Tony
No. Students    60
School    3
Dean    James
No.stu.    56
No. Teacher    20

[download]

Most of the code is taken from amon's example on stackoverflow.com

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;
    }
}
# see what we got
foreach my $s (sort keys %data) {
    print "School $s:\n";
    foreach my $fact (sort keys %{$data{$s}}) {
    print "\t$fact: $data{$s}{$fact}\n";
    }
}

[download]

which will print

School 1:
        Dean: John
        No.stu.: 55
School 2:
        Dean: Tony
        No. Students: 60
School 3:
        Dean: James
        No. Teacher: 20
        No.stu.: 56

[download]

If you now add more facts underneath a school it will automatically add it to the hash.

In reply to Re: content triggered parsing in Spreadsheet-ParseExcel by hdb
in thread content triggered parsing in Spreadsheet-ParseExcel by qingxia

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.