Spreadsheet::Read reads whole spreadsheets in a single hash of lists (sheets) of lists (columns) of lists (rows), including meta-information my $ss = ReadData ("file.xls"); using Spreadsheet::ParseExcel (or any other supported spreadsheet parser).

The first sheet is my $sheet = $ss->[1];.

The formatted value of cell B3 can be accessed by $sheet->{"B3"}. The unformatted value by $sheet->{cell}[2][3].

Does that help?


Enjoy, Have FUN! H.Merijn

I was a given the following code that was to help with the data crunching.

    my %vals;
                                                
                                 
while (<DATA>){
    chomp;
    my ($key, $val) = split;
    push @{$vals{$key}}, $val;
}

for my $key (sort {$a <=> $b} keys %vals) {
    my $max = max @{$vals{$key}};
    print "$key $max\n";
}            

I figured that , since my hint for a successful functioning of the cod
+e included parsing of excel using Spreadsheet::ParseExcel, I went ahe
+ad and added the code to the parsing one as such:
<code>
 #!/usr/bin/perl -w

    use strict;
    use warnings;
    use Spreadsheet::ParseExcel;
    use List::Util qw(max);
    

    my $parser   = Spreadsheet::ParseExcel->new();
    my $workbook = $parser->parse('4_19_10_Zrc01a-2.xls');

    if ( !defined $workbook ) {
        die $parser->error(), ".\n";
    }
   
    for my $worksheet ( $workbook->worksheets() ) {

        my ( $row_min, $row_max ) = $worksheet->row_range();
        my ( $col_min, $col_max ) = $worksheet->col_range();
        

        for my $row ( $row_min .. $row_max ) {
            for my $col ( $col_min .. $col_max ) {

     
         my $cell = $worksheet->get_cell( $row, $col );
                next unless $cell;
        
                        my %vals;
                                                
                                 
while (<$col eq 5 and $col eq 8>){
    chomp;
    my ($key, $val) = split;
    push @{$vals{$key}}, $val;
}

for my $key (sort {$a <=> $b} keys %vals) {
    my $max = max @{$vals{$key}};
    print "$key $max\n";
}                                                                     
+  
                                                                  }
                                                 
                                                  }
                                              }
[download]

I changed the required DATA to the columns that I was specifically interested, which is that of column 5 and 8. For some reason, I have a big time error list when I run it. I must be missing something here. Any help would be appreciated. Thank you.

Yes, you have all the resources you need to accomplish your goal. Just use Spreadsheet::ParseExcel to parse the spreadsheet, and use the code I demonstrated to process the data.

You're going to have to do some learning to finish the project, but you have everything you need.

Would it be alright with you to go into detail as to how your code is crunching the data?, I do not follow it fully esp. with the instance of how my parsed data is being fed into the code. Thank you.

use strict;
use warnings;

use Spreadsheet::Read;

my $ss = ReadData ("4_19_10_Zrc01a-2.xls");
my $s  = $ss->[1];

my %max;
foreach my $r (1 .. $s->{maxrow}) {
    my ($A, $B) = ($s->{cell}[1][$r], $s->{cell}[2][$r]);
    $max{$A} //= $B;
    $max{$A} < $B and $max{$A} = $B;
    }

print "$_\t$max{$_}\n" for sort { $a <=> $b } keys %max;
[download]

$ perl test.pl
1       4
2       7
3       10
$
[download]

use strict;
use warnings;

use Spreadsheet::ParseExcel;
use Spreadsheet::Read;


my $ss = ReadData ("4_19_10_Zrc01a-2.xls");
my $s  = $ss->[1];

my %max;
foreach my $r (1 .. $s->{maxrow}) {
    my ($A, $B) = ($s->{cell}[5][$r], $s->{cell}[8][$r]);
    $max{$A} //= $B;
    $max{$A} < $B and $max{$A} = $B;
    }

print "$_\t$max{$_}\n" for sort { $a <=> $b } keys %max;
[download]

Use of uninitialized value $A in hash element at D:\Programming\Perl\R
+ead.plx li
ne 14.
Use of uninitialized value $A in hash element at D:\Programming\Perl\R
+ead.plx li
ne 15.
Use of uninitialized value $B in numeric lt (<) at D:\Programming\Perl
+\Read.plx
line 15.
Use of uninitialized value in numeric lt (<) at D:\Programming\Perl\Re
+ad.plx lin
e 15.
Use of uninitialized value $A in hash element at D:\Programming\Perl\R
+ead.plx li
ne 14.
Use of uninitialized value $A in hash element at D:\Programming\Perl\R
+ead.plx li
ne 15.
Use of uninitialized value $B in numeric lt (<) at D:\Programming\Perl
+\Read.plx
line 15.
Use of uninitialized value in numeric lt (<) at D:\Programming\Perl\Re
+ad.plx lin
e 15.
Use of uninitialized value $A in hash element at D:\Programming\Perl\R
+ead.plx li
ne 14.
Use of uninitialized value $A in hash element at D:\Programming\Perl\R
+ead.plx li
ne 15.
Argument "         Mapped_Aux_Number" isn't numeric in numeric lt (<) 
+at D:\Prog
ramming\Perl\Read.plx line 15.
Argument "         Mapped_Aux_Number" isn't numeric in numeric lt (<) 
+at D:\Prog
ramming\Perl\Read.plx line 15.
Argument "Volt" isn't numeric in numeric lt (<) at D:\Programming\Perl
+\Read.plx
line 15.
Argument "Volt" isn't numeric in numeric lt (<) at D:\Programming\Perl
+\Read.plx
line 15.
Argument "Comments" isn't numeric in sort at D:\Programming\Perl\Read.
+plx line 1
8.
Argument "" isn't numeric in sort at D:\Programming\Perl\Read.plx line
+ 18.
Argument "After Conference 3rd test for series.\r\nmass .0037" isn't n
+umeric in
sort at D:\Programming\Perl\Read.plx line 18.
Comments        Volt
                 Mapped_Aux_Number
After Conference 3rd test for series.
mass .0037      0
[download]

foreach my $r (1 .. $s->{maxrow}) {
    my ($A, $B) = ($s->{cell}[5][$r] // 0, $s->{cell}[8][$r] // 0);
    $max{$A} //= $B;
    $max{$A} < $B and $max{$A} = $B;
    }
[download]

will take away all those warnings. I bet you don't want columns "A" and "B" then, now do you? You still have to select the correct columns! Let's take columns "C" and "T" with the alternative approach. Then you start reading the documentation.

foreach my $r (1 .. $s->{maxrow}) {
    my ($key, $value) = ($s->{"C$r"} // 0, $s->{"T$r"} // 0);
    $max{$key} //= $value;
    $max{$key} < $value and $max{$key} = $value;
    }
[download]


Enjoy, Have FUN! H.Merijn