creating a hash of hash from excel file

[dillipkumar]

hi,

i have one excel file, i want to create a hash of hash from the excel file, like:

%hash=
[
 sheet1 => [
              {
                'VALUES' => 'tryrt',
                'ROWNUM' => 13,
                'COLUMNNUM' => 1
              },
              {
                'VALUES' => 'Hyderabad',
                'ROWNUM' => 7,
                'COLUMNNUM' => 1
              },
            ],
 sheet2 => [
      {
              'VALUES' => 'tryrsdft',
              'ROWNUM' => 1sdf3,
              'COLUMNNUM' => 1sdf
             },
             {
              'VALUES' => 'Hyddserabad',
              'ROWNUM' => 7ds,
              'COLUMNNUM' => 1342
             },
    ],
....
....
];

use Spreadsheet::ParseExcel;
use Data::Dumper;
my $oBook = Spreadsheet::ParseExcel::Workbook->Parse('source.xls');
my($Row, $Col, $Wrksht, $oWkC);
foreach my $Wrksht(@{$oBook->{Worksheet}}) 
{
 my $sheetname = $Wrksht->{Name};
    for(my $Row = $Wrksht->{MinRow}; defined $Wrksht->{MaxRow} && $Row <= $Wrksht->{MaxRow} ; $Row++) 
 {
  for(my $Col = $Wrksht->{MinCol}; defined $Wrksht->{MaxCol} && $Col <= $Wrksht->{MaxCol} ; $Col++) 
  {
   $oWkC = $Wrksht->{Cells}[$Row][$Col];
            if ($oWkC) 
   {
 
    my %HoAexcel = ( SHEETNAME => $sheetname, COLUMNNUM => $Col+1, ROWNUM => $Row+1, VALUES => $oWkC->Value, );
 
    push (@loop, \%HoAexcel);
 
   }
        }
    }
}
print Dumper(@loop);

I have written a script
But i am not able to create like this.
after running that script, the result is diplaying like this:

$VAR1 = {
          'VALUES' => '1',
          'ROWNUM' => 1,
          'COLUMNNUM' => 1
        };
$VAR2 = {
          'VALUES' => '2',
          'ROWNUM' => 2,
          'COLUMNNUM' => 1
        };
$VAR3 = {
          'VALUES' => '1231',
          'ROWNUM' => 3,
          'COLUMNNUM' => 1
        };
 

Can somebody help me to creating above multi dimensional hash.

[KevinADC]

Your first example data is not a hash of hashes. It is an array of arrays of hashes. In the perl code you posted, change this line:

print Dumper(@loop);

change it to:

print Dumper \@loop;

and see what the output looks like.

[dillipkumar]

Hi KevinADC,

After changing the line from print Dumper(@loop) to print Dumper \@loop;

the o/p is like:

$VAR1 = [
         { 
           'VALUES' => '1', 
           'ROWNUM' => 1, 
           'COLUMNNUM' => 1 
         },
         { 
           'VALUES' => '2', 
           'ROWNUM' => 2, 
           'COLUMNNUM' => 1 
         }, 
         { 
           'VALUES' => '1231', 
           'ROWNUM' => 3, 
           'COLUMNNUM' => 1 
         }
 ]; 
 

But I need the the excel sheet name in this hash of hash.

Tx,
Dillip.

[numberwhun]

dillipkumar,

We have corrected your code tags a couple of times now. Please be sure and use them the next time you post code into the forums.

Regards,

Jeff

[KevinADC]

@dillipkumar
My only suggestion is to read the module documentation. Apparently this line returns a reference to an array of hashes:

my $oBook = Spreadsheet::ParseExcel::Workbook->Parse('source.xls');
 

Look over the module and see if it has a method for returning a hash of hashes if you're sure thats what you really want. The array is probably used to keep the original order of the records. If you use a hash of hashes the original order of the records will most likely not be retained.

[bazz]

what do you get if you dump the \%HoAexcel at the same point in your script as the print Dumper(@loop);

bazz

[numberwhun]

@bazz
I hate to say this, but this thread is about 8 months old. I am sure the OP either solved this or moved on. Please keep an eye on the age of the thread you are viewing.

Also, you may want to try PMing the OP who asked the question instead of looking like you are hijacking their thread.

Regards,

Jeff