#!/usr/bin/perl
use strict;
use warnings;
use Spreadsheet::BasicRead;

# Data file
# CLASS    PUPIL    M/F    READING  PROGRESS   
my $xlsx_WSD = "C:\\Temp\\data1.xlsx";

my @header;
my %stats = ();
my $i = 0;
if ( -e $xlsx_WSD ) {
  my $ss   = new Spreadsheet::BasicRead($xlsx_WSD) or die;
  while ( my $row = $ss->getNextRow() ) {
    my $class    = $row->[0];
    my $reading  = $row->[3];
    if (++$i == 1){
      push @header,$row;
    } else {
      $stats{$class}{'count'}++;
      if ($reading < 6){
        $stats{$class}{'limit'} += 1 ;
      } else {
        $stats{$class}{'limit'} += 0;
      }
    }
  }
}    

print "\nTotals\n";
my %total = ();
my $fmt = "%-15s | %5d | %5d | %5d%%\n";
for my $class (keys %stats){
  my $rec = $stats{$class};
  my $limit = $rec->{'limit'};
  my $count = $rec->{'count'};
  my $pc = 100 * $limit / $count 
             unless ($count == 0);
                     
  printf  $fmt, $class, $count, $limit, $pc;  
  $total{'count'} += $count;
  $total{'limit'} += $limit;
}
my $pc = 100 * $total{'limit'} / $total{'count'} 
   unless ($total{'count'} == 0);
print '-'x40,"\n";
printf  $fmt, 'Total', $total{'count'}, $total{'limit'}, $pc;
[download]