List or Scalar?

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,";
  }
[download]

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);
  #
</readmore>
  #    Based on `Using Win32::OLE and Excel - Tips and Tricks'
  #    by cacharbe - Mar 22, 2002 - node_id=153486.. with thanks 8-)
  #
<readmore>
  $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

</readmore>
#       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;
[download]

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

Comment on List or Scalar? Select or Download Code
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} ) {` [download] Why not just `for( -5..$term ) {` [download] 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";` [download] But even here you could have just said `print "\$term is $term\n";` [download] 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 ) {` [download] 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?	[reply] [d/l] [select]
Re: Re: List or Scalar? by hagen (Friar) on Aug 27, 2003 at 08:20 UTC
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.	[reply] [d/l]
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 ];` [download] 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	[reply] [d/l]
Re: List or Scalar? by NetWallah (Monsignor) 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` [download] 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])` [download]	[reply] [d/l] [select]
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' ];` [download] ... 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.	[reply] [d/l] [select]


The stupid question is the question not asked
	PerlMonks

List or Scalar?

The best material for plates (tableware) is: