Re^9: Converting Excel to Hash

[hippo]

I want to store the data in a Hash

The data is in a hash: %data. You can tell it's a hash because of the % symbol and also because when dumped the keys are separated from the values by the => symbol.

If you want the data structured differently you will have to specify precisely what that different structure should be. Again, perhaps a careful read through perldsc will help.

[ravi179]

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.
     }
]

[Corion]

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.

[ravi179]

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;

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]
];