Excel Format:

ID  Name  Degree  CLZ
1   Teja   ph.d   nit
           ph.d   iit
2   Ravi   B.tech  iir

desired format:

$var1 ='1';
$var2=[
       { clz=>nit,
         degree=>ph.d,
         name=>teja
}
]
$var1='1'
$var2=[
     { clz=>iit,
       degree=>ph.d,
       name=>teja
     }
]
$var1='2'
$var2=[
     { clz=>iir,
       degree=>b.tech,
       name=>ravi.
     }
]
[download]

When I posted my code, I added some debug output for when lines from the input are skipped by your code. Maybe you want to look at that debugging output to find when your code skips input.

Currently your code does not handle the case of the id being missing in the input. Maybe you want to change that.

use Spreadsheet::ParseExcel;
use Data::Dumper;
$filename="Book2.xls";
$e=new Spreadsheet::ParseExcel;
$eBook=$e->Parse($filename);


$sheets = $eBook->{SheetCount};
($eSheet, $sheetName);

foreach $sheet (0 .. $sheets - 1) {
    $eSheet = $eBook->{Worksheet}[$sheet];
    $sheetName = $eSheet->{Name};
    print "Worksheet $sheet: $sheetName\n";

 %set =();
%data =();


foreach $row( 1 .. $eSheet->{MaxRow} )

{

if (defined  ($eSheet->{Cells}[$row][0] ))
{
$master_key=($eSheet->{Cells}[$row][0]->Value);
$r=$row;
}
else
{
$master_key=$master_key;
}
foreach $col(1 .. $eSheet->{MaxCol})

{
my $key=$eSheet->{Cells}[0][$col]->Value;

if (defined $eSheet->{Cells}[$row][$col])
{

 $val=($eSheet->{Cells}[$row][$col]->Value);

}
else 
{

$val=$eSheet->{Cells}[$r][$col]->Value;

}

$set{$key}=$val;

}
push @{ $data{$master_key}}, \%set;

}


}
print Dumper %data;
[download]

I think this can handle even if the data is missing.But the output is not coming.The output is coming as shown below.

Excel Format:

ID  Name  Degree  CLZ
1   Teja   ph.d   nit
           ph.d   iit
2   Ravi   B.tech  iir

output:

$var1='1'
$var2=[
     { clz=>iir,
       degree=>b.tech,
       name=>ravi.
     },
     $var2->[0];
]

$Var3='2';
$Var4=[
$var2->[0]
];
[download]