Spreadsheet::XLSX Cell Access

[Anonymous Monk]

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.

[runrig]

Obligatory link: http://perl.plover.com/varvarname.html.

Use a hash:

my %yield;

$yield{$_} = $sheet->{"${_}35"} for "F".."K";
#or
$yield{$_} = $sheet->{ $_ . 35 } for "F".."K";

[Hammer2001]

Runrig et el, thanks for your quick replies.

Runrig, how do declare an array of hashes? In your snippet "my %yield;" will only declare a single hash whereas I need 6 of them.

I can testify that

my %pond$_ for "F".."K";

does not work.

Thanks,

Hamid

[Hammer2001]

Runrig et el, thanks for your quick responses. Runrig, how do I declare an array/series of hashes? In your snippet "my %yield;" will only declare a single hash whereas I need 6 of them.

I can testify that

my %pond$_ for "F".."K";

does not work.

Thanks,

Hamid

PS. Sorry for erroneously starting a new thread.

[kcott]

Change "'$_35'" to "$_ . 35".

Also, I've got no idea what you're trying achieve with "$yield$_ = ...".

You should probably read perlintro before proceeding.

Here's a very rough guess at what you might need:

$ perl -Mstrict -Mwarnings -e '
    my $sheet = {F35 => 1, G35 => 2, H35 => 3, I35 => 4, J35 => 5, K35
+ => 6};
    my @mycols = "F" .. "K";
    my @cells;
    for (@mycols) {
        push @cells, $sheet->{$_ . 35};
    }
    print "@cells\n";
'
1 2 3 4 5 6

[Laurent_R]

I don't have these modules installed and haven't used an Excel Perl module for at least 3 years, so don't remember too well the details, but possibly using $sheet->{"${_}35"} instead of $sheet->{'$_35'} might enable interpolation of the $_ variable. You may also want to consider a hash for your yields.