XP is just a number PerlMonks

### List or Scalar?

by hagen (Friar)
 on Aug 27, 2003 at 07:08 UTC Need Help??
hagen has asked for the wisdom of the Perl Monks concerning the following question:

Learned Monastic Denizens

I've tried everything I know, consulted perlop on Range Operators, checked Perl in a Nutshell (Camel book at home) but this has me puzzled.

I'm trying to calculate the end column of a range of cols in an Excel s/sheet. I know where the range starts, and I know how many more cols I need to `add' to the start to get to the end. Ah hah! I thought, time for the `magic strings' and eventually came up with this snippet to convince me I was on track:-

```#!/usr/bin/perl
use strict;
use warnings;

#
#       test the magic strings
#

my \$col = 'p';
my \$limit = 120;
for (-5..\$limit) {
\$col++;
print "\$col,";
}

Success! Works as advertised and I end up with column ref `el'. So I moved the same, so I thought, approach into the real program...

```
#!/usr/bin/perl
use strict;
use warnings;
use Win32::OLE;
use Win32::OLE         qw(in with);
use Win32::OLE::Const 'Microsoft Excel';
use Win32::OLE::Variant;
use Win32::OLE::NLS    qw(:LOCALE :DATE);
#
#    Based on `Using Win32::OLE and Excel - Tips and Tricks'
#    by cacharbe - Mar 22, 2002 - node_id=153486.. with thanks 8-)
#
\$Win32::OLE::Warn = 3;

my (\$ex,        # this will be the ref to the Excel applicatio
+n
\$book,       # ref to the entire workbook
\$sheet,      # a particular worksheet
\$sheetcount) # count of all sheets in workbook
= undef;

#       Check that Excel is running - if not, start it
eval {\$ex = Win32::OLE->GetActiveObject('Excel.Application')};
die "Excel not installed" if \$@;

unless (defined \$ex) {     # if \$ex is undef here, need to sta
+rt Excel
\$ex = Win32::OLE->new('Excel.Application', sub {\$_[0]->Quit;
+})
or die "Oops, cannot start Excel";
}

my \$excelfile = "D:/murray/cut_test.xls";

# open existing workbook
\$book = \$ex->Workbooks->Open(\$excelfile);
\$sheetcount = \$book->Worksheets->Count();

#       open an output file to take csv formatted results
open CURR, ">", "cut_extract.csv"
or die "Can't open Extract for writing \$!";

#       get the "one-off" key information from the top of the Total Su
+mmary sheet
\$sheet = \$book->Worksheets("Total Summary");
my \$key_values  = \$sheet->Range("M1:M8")->{Value};

my (\$project, \$term, \$local, \$currency, \$segment, \$LOB, \$first
+_month, \$owner) = @{\$key_values};

for (@\$key_values) {
print CURR "@\$_,";                    # values out to .csv a
+s expected.
}
print CURR "\n";

\$sheet = \$book->Worksheets("FTE");        # get focus on the FTE w
+orksheet

#       calculate the end column of months - month -6 starts in col P
#       so we need \$term + 6 cols added to P.  The \$range for Excel is
+ specified
#       as "CR:cr" where CR is the top, left cell of the range, and cr
+ the bottom
#       right.
#
#       Determine the right-most column = 'P' + 6 + \$term

my \$col = 'P';
print "\\$term is @{\$term}\n";   # prints 120 as required
for(-5..@{\$term}) {             # but `..' seems to be in scalar c
+ontext!
\$col++;
print "\$col,";
}

undef \$book;
undef \$ex;
close CURR;

But here I only end up with `W'... as if once -5 gets to zero the `..' flip-flops, then @{\$term} is TRUE, and the iteration stops.

I'd appreciate some enlightenment in this branch of our theosophy... 8-)

... with humble thanks hagen

Replies are listed 'Best First'.
Re: List or Scalar?
by antirice (Priest) on Aug 27, 2003 at 07:29 UTC

Data::Dumper is your friend. If your print line is really printing that, then \$temp should look as follows when you use print Dumper \$temp;

```\$VAR1 = [
120
];

If that's the case, then you should be using \$temp->[0] instead of @{\$temp} as it returns the length of your array (which is 1) in scalar context.

If this is entirely wrong, the reason I think \$temp is an arrayref is because using @{\$temp} doesn't result in strict going on how 120 isn't an arrayref at line blah. Also, the OP said he was able to print "\\$temp = @{\$temp}\n"; and it prints out 120 which would be expected behavior for an arrayref.

Hope this helps.

antirice
The first rule of Perl club is - use Perl
The
ith rule of Perl club is - follow rule i - 1 for i > 1

Re: List or Scalar?
by shenme (Priest) on Aug 27, 2003 at 07:25 UTC
Ummm, if \$term has the right value in it, why did you say
```  for( -5..@{\$term} ) {
Why not just
```  for( -5..\$term ) {
I think you got carried away with the specialized idiom of how to force values to be interpolated within strings, such as you used in
```  print "\\$term is @{\$term}\n";
But even here you could have just said
```  print "\\$term is \$term\n";

The value @{\$term} is evaluating to an undefined value, which is somehow getting converted into a value of zero. If I say

```  for( -5..undef ) {
directly I get nasty warnings "Use of uninitialized value in foreach loop entry at hagen.pl line 36." Also if I use a variable with an undef value.

Why so?

Thanks shenme... in the actual program the value is in a reference (as returned by the Win32::OLE module) - and is deref'ed (as I understand it) by the @{\$term} syntax - it's come from a (ref to an) array of refs.

What makes me think that this is correct is that the

print "\\$term is @{\$term}\n"

prints the value I want. However when I use the same @{\$term} syntax in the range operator it doesn't seem to deref correctly - or I'm making some other sort of mistake.

Re: List or Scalar?
by NetWallah (Canon) on Aug 27, 2003 at 14:20 UTC
I think this snippet explains your predicament.
```>perl -e "\$a=[120]; for (-1..@{\$a}){print qq(\$_\n)};
print qq(  \\$a=\$a    \@a=@{\$a}  value=\$a->[0]);"

OUTPUT:
-1
0
1
\$a=ARRAY(0x224f44)    @a=120  value=120
Note that \$a is an array ref, and @a in a print context returns the equivalent of join (' ',@a), which is a single element of 120.

To get your "for" right, you need to use

```  for(-5..\$term->[0])
Re: List or Scalar?
by hagen (Friar) on Aug 27, 2003 at 23:28 UTC

Many thanks to antirice and NetWallah - their diagnosis is absolutely correct; and I have now a (little) better understanding of Perl's references

Using the \$term->[0] syntax is the answer and I now assume that the print command was kinda "DWIM"ing the ref for me, but in the for loop it wasn't. A bit subtle for my present level of Perlishness 8-)... but on further reflection this would be because print is in list context - and of course the (-1..@{\$term}) is scalar - Insight!

antirice... Data::Dumper printed as follows

```\$term is 120
\$VAR1 = [
'120'
];

... are the single quotes trying to tell me something?

and NetWallah... Thanks for that very revealing code snippet... I'll keep a copy of that to remind me of the various syntactic options re references. I've just re-read MJD's perlreftut and I think I see where I misunderstood the syntax.

cheers and thanks again... what a great resource Perl Monks is..... hagen.

Create A New User
Node Status?
node history
Node Type: perlquestion [id://286956]
Approved by weini
help
Chatterbox?
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others contemplating the Monastery: (7)
As of 2017-12-16 01:01 GMT
Sections?
Information?
Find Nodes?
Leftovers?
Voting Booth?
What programming language do you hate the most?

Results (447 votes). Check out past polls.

Notices?