Spreadsheet::XLSX Cell Access

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

Replies are listed 'Best First'.
Re: Spreadsheet::XLSX Cell Access by duelafn (Parson) on Sep 13, 2013 at 01:12 UTC
Try: `my %yield; my @mycols = ('F', 'G', 'H', 'I', 'J', 'K'); foreach (@mycols) { $yield{$_} = $sheet->{$_ . "35"}; }` [download] 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	[reply] [d/l] [select]

Replies are listed 'Best First'.

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

Try:

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

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

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom