Re^6: Converting Excel to Hash

[ravi179]

yes.Could you help me in this

[poj]

Try

#!perl
use strict;
use Spreadsheet::ParseExcel;
use Data::Dump 'dd';

my $filename = "Book2.xls";

my $e = new Spreadsheet::ParseExcel;
my $eBook  = $e->Parse($filename);
my $eSheet = $eBook->{Worksheet}[0];
my $maxrow = $eSheet->{MaxRow};
my $maxcol = $eSheet->{MaxCol};
print "maxrow=$maxrow maxcol=$maxcol\n";

my %data    = ();
my @header  = ();
my @datarow = ();

for my $row (0 .. $maxrow) {

  my %this_set=();
  my $master_key;
  
  for my $col (0 .. $maxcol){
    my $cell = $eSheet->{Cells}[$row][$col];
    if (defined $cell){
      $datarow[$col] = $cell->Value unless ($cell->Value eq '');
    } else {
      next;
    }
  
    # header row
    if ($row == 0){
      $header[$col] = $datarow[$col];
      $datarow[$col] = '';
    }  else {
      if ($col == 0){
        $master_key = $datarow[$col];
      } else {
        my $key   = $header[$col];
        my $value = $datarow[$col];
        $this_set{$key} = $value;
      } 
    }     
  }  
  #print join ",",@datarow,"\n";
 
  # Store our found set for that ID:
  if ($row > 0){
    push @{ $data{ $master_key }}, \%this_set;
  }
}

dd \%data;

update : clear @datarow for header line.
poj

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

}

if ($row>0)
{
}
push @{ $data{$master_key}}, \%set;

}
print Dumper \%data;

}C:\perlpractice>c.pl              

 Worksheet 0: Sheet1                   
  $VAR1 = {       '1' => [                                         {  
+                         'clz' => 'iir',                             
+     
'                         degree' => 'b.tch          
                           'name' => 'ravi' 
                     },                                        .      
+          $VAR1->{'1'}[0]        
               ],                              
   '2' => [    $VAR1->{'1'}[0]   ]                              };

Iam getting output in the above fashion.Please help .I want to store the data in a 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.

[poj]

use Data::Dumper;
$Data::Dumper::Deepcopy = 1; # add this line

Read more... (1431 Bytes)
poj