vxp has asked for the wisdom of the Perl Monks concerning the following question:

First of all, I have been trying to figure out how to do a formula that has variables in it. They say that 1 line of code is better than 1,000 words, so: 
$row = (localtime)[3] - 1;
$worksheet->write_formula($row, 6, '=SUM(C$row:E$row)'   );

[download]
is what i'm trying to do. (the above doesnt work, but if it'd work, thatd be EXACTLY what I need) And the second question is.. As a temp fix for the above problem, i tried doing it the "normal" way: 
$row = (localtime)[3] - 1;
$worksheet->write_formula($row, 6, '=SUM(C20:E20)'   );

[download]
However, that also doesnt work. It runs fine, it just produces a zero in the cell, even though C20, D20, and E20 have numeric values in them (G20 is empty, however). Help would be greatly appreciated.

Replies are listed 'Best First'.
Re: Couple of questions about Spreadsheet::WriteExcel
by Mr. Muskrat (Canon) on Aug 20, 2002 at 19:17 UTC
    It looks like you are using the wrong value for the row. Try this example: 
    #!/usr/bin/perl
use strict;
use warnings;
use Spreadsheet::WriteExcel;
my $workbook = Spreadsheet::WriteExcel->new("test.xls");
my $worksheet = $workbook->addworksheet();
$worksheet->write('C20', 10);
$worksheet->write('D20', 20);
$worksheet->write('E20', 30);
my $row = 20;
# Both of the following methods work
# this first one you have to subtract 1 from $row
#$worksheet->write($row-1, 6, "=SUM(C$row:E$row)");
# this one you don't
$worksheet->write("G$row", "=SUM(C$row:E$row)");
$workbook->close();

    [download]
[reply]
[d/l]
Re: Couple of questions about Spreadsheet::WriteExcel
by RMGir (Prior) on Aug 20, 2002 at 18:21 UTC
    Your problem is that you're using single quotes, so "$row" doesn't get replaced with its value. 
    # -1 per Mr. Muskrat
$worksheet->write($row-1, 6, "=SUM(C$row:E$row)"   );

    [download]
    I replaced "write_formula" with "write", since using that method results in formulas that work for me (although looking at the docs, that wasn't the problem. See Mr. Muskrat's excellent answer).

    Oh, and if this doesn't work, you're in luck. The author of SS:WE hangs out here, so he'll probably know exactly what you need.
    --
    Mike

    Edit: D'oh! Mr. Muskrat's right; the row is 0 based in the write_formula call, but 1-based in the formula.

[reply]
[d/l]
      Thanks! Now I'm using 
      $worksheet->write($row, 6, "=SUM(C$row:E$row)"   );

      [download]
      however, it still produces a zero. even though like I said, the values in C$row through E$row _are_ there to be added!
[reply]
[d/l]
        Sorry, I've never run into anything like that, unless I was putting the formula to the left of the cells it references, and even then, it got updated when I hit F9 in Excel...

        If you look at the contents of the cell in Excel, is it really 0, or is the formula there?
        --
        Mike

[reply]

Back to Seekers of Perl Wisdom