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

Hello Monks,

I am looking for a way to read data from excel sheet by using either a column header name or a row header name.. What I currently use is as below:
use Win32::OLE::Const 'Microsoft Excel';
my $wks = $wkb->Worksheets(1);
my $FirstName = $wks->Cells(18,3)->{'Value'};

[download]
In the above way, I have to go by the row and column numbers. In case the column for first name changes, I need to change my script. That is why I am looking for a way to point to a cell using its column name/row name.

Regards,
J

note: I am using ActiveState perl 5.10 on WinXP

Replies are listed 'Best First'.
Re: reading data from Excel sheet using column/row names
by NetWallah (Canon) on Apr 01, 2010 at 05:32 UTC
    Assuming that the column Name is in the first row , you can determine the column containing "FirstName" by 
    my $header_Row = $wks->Range("A:A")->{'Value'};
my ($FirstNameColumn) = grep  {  $header_Row->[$_ - 1] =~  /firstname/
+i} 1..@header_Row;  

My $currrentRow="18"; Whatever row you are on now
my $FirstName = $wks->Cells($currentRow,$FirstNameColumn)->{'Value'};

    [download]
    UNTESTED.

         Syntactic sugar causes cancer of the semicolon.        --Alan Perlis

[reply]
[d/l]
Re: reading data from Excel sheet using column/row names
by BrowserUk (Patriarch) on Apr 01, 2010 at 05:02 UTC
[reply]
Re: reading data from Excel sheet using column/row names
by godsown (Novice) on Apr 01, 2010 at 04:45 UTC
    I had forgotten to login when I posted the above query.. my bad.. !!

    Regards,
    J
[reply]

Back to Seekers of Perl Wisdom