Beefy Boxes and Bandwidth Generously Provided by pair Networks
Do you know where your variables are?
 
PerlMonks  

Spreadsheet::XLSX Cell Access

by Hammer2001 (Novice)
on Sep 13, 2013 at 00:16 UTC ( #1053817=perlquestion: print w/ replies, xml ) Need Help??
Hammer2001 has asked for the wisdom of the Perl Monks concerning the following question:

Oh Venerables Keepers of Perl Wisdom, I have a question with regard to accessing Excel 2010 cells through Spreadsheet::XLSX and Spreadsheet::Read modules, though the answer may be more generally relevant.

I am currently accessing a cell in a sheet within the workbook using syntax:

$yield = $sheet->{'F35'};

with no issue. What I would like to do is something like:

my @mycols = ('F', 'G', 'H', 'I', 'J', 'K');

foreach (@mycols) {

$yield$_ = $sheet->{'$_35'};

}

so I can access cells F35, G35, H35, I35, J35 and K35. I have tried many variations on '$_35' to no avail and loath the existence of near identical lines in my code, and cannot come to terms with "Perl can't do it".

Does anyone have insight any insight on how to do this?

Best,

Hamid.

Comment on Spreadsheet::XLSX Cell Access
Re: Spreadsheet::XLSX Cell Access
by duelafn (Priest) on Sep 13, 2013 at 01:12 UTC

    Try:

    my %yield; my @mycols = ('F', 'G', 'H', 'I', 'J', 'K'); foreach (@mycols) { $yield{$_} = $sheet->{$_ . "35"}; }

    Could also use $sheet->{"${_}35"} on the right hand side, its a stylistic preference there. The key is use a hash for the yield, use double-quotes when including a variable, and you need a way do distinguish $_ concatenated with 35 from the single variable $_35 (that's what the braces in "${_}35" are doing - telling perl that just _ is the variable not _35).

    Good Day,
        Dean

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1053817]
Approved by kevbot
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others romping around the Monastery: (14)
As of 2014-10-30 17:43 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    For retirement, I am banking on:










    Results (208 votes), past polls