Beefy Boxes and Bandwidth Generously Provided by pair Networks
The stupid question is the question not asked
 
PerlMonks  

Spreadsheet::XLSX Cell Access

by Anonymous Monk
on Sep 12, 2013 at 22:01 UTC ( #1053801=perlquestion: print w/replies, xml ) Need Help??

Anonymous Monk 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.

Replies are listed 'Best First'.
Re: Spreadsheet::XLSX Cell Access
by runrig (Abbot) on Sep 12, 2013 at 22:05 UTC
      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

        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.

Re: Spreadsheet::XLSX Cell Access
by kcott (Archbishop) on Sep 12, 2013 at 22:24 UTC

    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

    -- Ken

Re: Spreadsheet::XLSX Cell Access
by Laurent_R (Canon) on Sep 12, 2013 at 22:15 UTC

    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.

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others cooling their heels in the Monastery: (2)
As of 2022-11-27 09:02 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Notices?