Group cols with row number Spreadsheet::ParseExcel perl

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

I have a question in Spreadsheet::ParseExcel. I have a excel sheet where there is no unique values and may have null values in any columns. I want to build a hash of hash from the excel to have my column names for my future coding/loading. At the moment my dumper of hash variable look like this

 
    'RXX-1' => {
        'RCA 1' => '',
        'key' => 'HEMJJ',
        'ID' => '3345',
        'stem' => '',
    }
    
    '' => {
        'RCA 1' => '',
        'key' => 'HKKJ',
        'ID' => '232',
    }
[download]

my code that does this :

    for my $row(1 ..$row_max){
        my $count = 1;
        my @data = map {
            my $cell = $worksheet->get_cell($row, $_); 
            $cell ? $cell->value() : ''; 
        } @required_cols;

        foreach my $col(@values){
            warn $worksheet->get_cell($row,0)->value() . $col."\n";
            $details->{ $worksheet->get_cell($row,0)->value() }{$col} 
+= shift @data
                if defined $worksheet->get_cell($row, 1)->value(); 
        }
    }
[download]

Instead of grouping them with a column I would like to group them with the row number or any unique integer if possible.

    '1' => {
        'code'  => 'RXX-1'
        'RCA 1' => '',
        'key'   => 'HEMJJ',
        'ID'    => '3345',
        'stem'  => '',
    }
 
    '2' => {
        'RCA 1' => '',
        'key'   => 'HKKJ',
        'ID'    => '232,
        'code'  => '',
        .....
    }
[download]

Could I use a count in the for loop? Any help/suggestions please.

Comment on Group cols with row number Spreadsheet::ParseExcel perl Select or Download Code

Replies are listed 'Best First'.
Re: Group cols with row number Spreadsheet::ParseExcel perl by roboticus (Chancellor) on Jan 27, 2015 at 18:35 UTC
Anonymous Monk: Yes, you can use a counter in your loop to put a unique value in the hash. You can even put the row and column in there, if you wanted: `my $cnt_unique=0; for my $row (1 .. $row_max) { ... row prep stuff for my $col (1 .. $col_max) { ... col prep stuff ... my $hr = ... your hash for the cell ... # Add unique number: $hr->{KEY} = $cnt_unique++; # Add row & column: $hr->{ROW} = $row; $hr->{COL} = $col; } }` [download] ...roboticus When your only tool is a hammer, all problems look like your thumb.	[reply] [d/l]
Re: Group cols with row number Spreadsheet::ParseExcel perl by poj (Abbot) on Jan 27, 2015 at 20:03 UTC
Assuming the columns names are in row 1 #!perl use strict; use Spreadsheet::ParseExcel; use Data::Dump 'pp'; my $parser = Spreadsheet::ParseExcel->new(); my $workbook = $parser->parse('c:/temp/Book1.xls'); my $worksheet = $workbook->worksheet('Sheet1'); my ($row_min, $row_max) = $worksheet->row_range(); my @required_cols = (0,2..5); # columns names my @name=(); # build array of hashes my @details=(); for my $row (0..$row_max){ my $href={}; for my $col (@required_cols){ my $cell = $worksheet->get_cell($row, $col); my $value = $cell ? $cell->value() : ''; if ($row==0){ $name[$col] = $value; } else { $href->{$name[$col]} = $value; } } push @details,$href if ($row); } pp \@details; [download] poj	[reply] [d/l]