Re^9: Converting Excel to Hash

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

#!perl
use strict;
use Spreadsheet::ParseExcel;
use Data::Dumper;
$Data::Dumper::Deepcopy = 1;

my $filename = "Book2.xls";
my $e = new Spreadsheet::ParseExcel;
my $eBook  = $e->Parse($filename);
my $sheets = $eBook->{SheetCount};

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

  my %set  =();
  my %data =();
  my ($r,$val,$master_key);

  for my $row (1 .. $eSheet->{MaxRow}){
    if (defined $eSheet->{Cells}[$row][0] ){
      $master_key = $eSheet->{Cells}[$row][0]->Value;
      $r = $row;
    } else {
      $master_key = $master_key;
    }
    for my $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;
}
[download]

poj

Comment on Re^9: Converting Excel to Hash Select or Download Code

Replies are listed 'Best First'.
Re^10: Converting Excel to Hash by ravi179 (Novice) on Jan 05, 2017 at 04:52 UTC
`$VAR1 = { '1' => [ { 'clz' => 'iir', 'degree' => 'b.tch', 'name' => 'ravi' }, { 'clz' => 'iir', 'degree' => 'b.tch', 'name' => 'ravi' } ], '2' => [ { 'clz' => 'iir', 'degree' => 'b.tch', 'name' => 'ravi' } ] };` [download] This is the output iam getting for print Dumper %data \ `1$VAR1 = { 'clz' => 'nit', 'degree' => 'ph.d', 'name' => 'teja' }; 1$VAR1 = { 'clz' => 'iit', 'degree' => 'm.tech', 'name' => 'teja' }; 2$VAR1 = { 'clz' => 'iir', 'degree' => 'b.tch', 'name' => 'ravi' };` [download] this is the output iam getting for Print Dumper %set	[reply] [d/l] [select]
Re^11: Converting Excel to Hash by poj (Abbot) on Jan 05, 2017 at 08:23 UTC
You need to create a new `%set` hash for each row `##my %set =(); <- move this line inside loop my %data =(); my ($r,$val,$master_key); for my $row (1 .. $eSheet->{MaxRow}){ my %set =(); # <- move to here ..` [download] poj	[reply] [d/l] [select]
Re^12: Converting Excel to Hash by ravi179 (Novice) on Jan 05, 2017 at 08:48 UTC
Thank you .It worked for me.	[reply]