The Synopsis of https://metacpan.org/pod/Spreadsheet::ParseExcel gives you an example.

How did that not help?

Or is it that Perl hashes are new to you? Read up on perlintro and expecially in the part about hashes in Variable types.

Once the first and second part are understood, you will find a third layer or dimension is needed. You have a multitude of ways to do that. I'd suggest you use a hash of hashes for that to start with.

Cheers, Sören

use strict;
use Spreadsheet::ParseExcel;
use Data::Dumper;
use warnings;

my $parser=Spreadsheet::ParseExcel->new();
my $workbook=$parser->parse('C:\Perl\perl_tests\Sample.xls');
if(!defined $workbook)
{
die $parser->error(),".\n";
}

my $worksheet=$workbook->worksheet('Sheet1');
my %student_data;

for my $row(1..$worksheet->row_range)
{
my $super_key=$worksheet->get_cell($row,0)->value;
    for my $col(0..4)
    {
    my $key=$worksheet->get_cell(0,$col)->value;
    my $value=$worksheet->get_cell($row,$col)->value;
##here I am getting a problem##
    $student_data{$super_key}->{$key}=$value;
    }
}

foreach my $student_name(sort keys %student_data)
{
    foreach my $attribute (keys%{$student_data{$student_name}})
    {
        if($student_data{$student_name}{$attribute} eq "x")
        {
        print("$student_name-->$attribute\n");          }
    }
    print("\n");
}
[download]

it shows an error "can't call method 'value' on an undefined value. I guess it is because in some cells 'x' is not written and hence that cell is empty so it is showing me this error. I tried it when all the cells contain 'x' then it is working. What should I do in this case?

Hello ankit.tayal560,

Are you sure the error is coming from the code shown? When I run your code, accessing an Excel file created according to the table in the OP, it works without errors:

17:20 >perl 1704_SoPW.pl
Azhar-->Academic
Azhar-->IQ
Azhar-->Music
Azhar-->sports

Gurmeet-->Academic
Gurmeet-->IQ
Gurmeet-->Music
Gurmeet-->sports

Jason-->IQ
Jason-->Music

Vishal-->Academic
Vishal-->IQ
Vishal-->sports


17:21 >
[download]

It may help if you detail your platform, Perl version, and Spreadsheet::ParseExcel version. Here are mine:

• Windows 8.1, 64-bit
• Strawberry Perl 5.22.1
• Spreadsheet::ParseExcel 0.65

Also, please copy and paste the exact error message you receive (within <code> ... </code> tags).

Cheers,

Athanasius <°(((>< contra mundum

Iustus alius egestas vitae, eros Piratica,

for my $row (1 .. $worksheet->row_range) {
    my $super_key = $worksheet->get_cell ($row, 0)->value;
    for my $col (0 .. 4) {
        my $key   = $worksheet->get_cell (0,    $col)->value;
        my $value = $worksheet->get_cell ($row, $col)->value;
        # here I am getting a problem #
        $student_data{$super_key}->{$key} = $value;
        }
    }
[download]

I am pretty sure the error if from either of the two lines above that. get_cell (...) will return undef for undefined cells, for which you cannot use the value method.


Enjoy, Have FUN! H.Merijn

Hi Laurent_R could you please look at other comments posted by me and tell me a suitable solution for the above problem. I've enlisted the code which I've tried as well. thanks!

The point being made is that you should be forthcoming with your attempt so far without having to be asked. We've been round this loop a couple of times before, please read and understand How do I post a question effectively? and PerlMonks for the Absolute Beginner.

Hi ankit.tayal560,

You need to break up your get_cell subroutine calls to avoid processing blank cells i.e. change every instance of:

$key_var = $worksheet->get_cell($x,$y)->value
[download]

to something like

$cell = $worksheet->get_cell($x,$y);
next unless $cell;
$key_var = $cell->value;
[download]

Additionally, I would change for my $row(1..$worksheet->row_range) to for my $row(1..($worksheet->row_range)[1]) - the row_range subroutine returns a list, not a scalar

I hope that helps, good luck!
Shadowsong

thanks for the guidance. it did work! thanks again