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

Hello
I would like to use a Excel file to contain some test data and I'm trying to get this to work in a "smooth" way. I can easily access the right sheet but I would like the first row to contain keywords so that I could access the right column in the same way (by its keyword). So fare i have read all the keywords in to a array but I would like to find the place/order of that keyword. Is there a way to do this our do I have to read the keywords in to a hash together with its column value? 
#!/usr/bin/perl -w
use Spreadsheet::ParseExcel;
my $excel = Spreadsheet::ParseExcel::Workbook->Parse($ARGV[0]);
$Worksheet = $excel->Worksheet("...");
foreach my $col ($Worksheet->{MinCol} ..  $Worksheet->{MaxCol}) {
   my $cell = $Worksheet->{Cells}[0][$col];
   if ($cell) {
       push @keywords, $cell->{Val};
   }
}

[download]

Replies are listed 'Best First'.
Re: Spreadsheet::ParseExcel column access
by stiller (Friar) on Feb 04, 2008 at 10:20 UTC
    If I undertand you correctly, what you want is a way to lookup the columnumber from the colomn heading? In that case this might be what you want: (untested)

    my %colnum_for_name;
$Worksheet = $excel->Worksheet("...");
foreach my $col ($Worksheet->{MinCol} ..  $Worksheet->{MaxCol}) {
   my $cell = $Worksheet->{Cells}[0][$col];
   if ($cell) {
       $colnum_for_name{ $cell->{Val} } = $col;
   }
}

# later
my $someval = $Worksheet->{Cells}[$x][$colnum_for_name{'price'}];

    [download]
    hth
[reply]
[d/l]
      Thanks that worked fine
[reply]

Back to Seekers of Perl Wisdom