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

Greetings all, I've been palying around with Spreadsheet::WriteExcel lately (excelent module, by the way; no pun intended), and I think that I've discovered a bug of sorts. Here's a program that demonstrates the problem: 
#!/usr/local/bin/perl -w

use strict;

use Spreadsheet::WriteExcel;

my $workbook = Spreadsheet::WriteExcel->new("test.xls");
my $worksheet = $workbook->addworksheet("foo");

my $formula = $worksheet->store_formula('=SUBTOTAL(9,A1:B1)');

foreach my $row (0..99) {
        $worksheet->write($row, 0 , $row+1);
        $worksheet->write($row, 1 , $row+1);
        $worksheet->repeat_formula($row, 2, $formula, undef, 'A1', 'A'
+.($row+1), 'B1', 'A'.($row+2));
        $worksheet->repeat_formula($row, 3, $formula, undef, 'A1', 'B'
+.($row+1), 'B1', 'B'.($row+2));
        #$worksheet->repeat_formula($row, 2, $formula, undef, 'A1', 'A
+'.($row +1), 'B1', 'B'.($row+1));
        #$worksheet->repeat_formula($row, 5, $formula, undef, 'A1', 'D
+'.($row +1), 'B1', 'E'.($row+1));
}

[download]
In the resulting spreadsheet, column "C" shows up as expected, and for the most part, so does column "D", except for rows 10-19, and row 100. For those rows, the formula that is put in the cell is of the form =SUBTOTAL(9,B1: B(11*row)), where row is the row in the Excel file. Is this something that's documented that I missed, or is this a genuine bug?

thor

Replies are listed 'Best First'.
Re: Bug in Spreadsheet::WriteExcel?
by artist (Parson) on May 06, 2003 at 17:08 UTC
[reply]
Re: Bug in Spreadsheet::WriteExcel?
by dragonchild (Archbishop) on May 06, 2003 at 16:56 UTC
    I don't know if this is a cut'n'paste error, but you have repeat_formula() assign to column C twice - before and after the assign to column D.

    ------
    We are the carpenters and bricklayers of the Information Age.

    Don't go borrowing trouble. For programmers, this means Worry only about what you need to implement.

    Please remember that I'm crufty and crochety. All opinions are purely mine and all code is untested, unless otherwise specified.

[reply]
      It is indeed a cut'n'paste error...the second one is commented out. I thought I removed all of the junk, but I guess I missed two lines. Sorry for the confusion.

      thor

[reply]
Re: Bug in Spreadsheet::WriteExcel?
by cLive ;-) (Prior) on May 06, 2003 at 18:38 UTC

    excelent module, by the way; no pun intended

    Don't you mean:

    excellent module, by the way; no spelling mistake intended :)

    excelent post though :)

    cLive ;-)

[reply]
      LOL. That was completely unintentional. I spel pourly. ;)

      thor

[reply]
Re: Bug in Spreadsheet::WriteExcel?
by Mr. Muskrat (Canon) on May 07, 2003 at 15:26 UTC

    It is documented behavior.

    "Care should be taken with the values that are substituted. The formula returned by repeat_formula() contains several other tokens in addition to those in the formula and these might also match the pattern that you are trying to replace."

    Try this instead:

    #!/usr/local/bin/perl -w
use strict;
use Spreadsheet::WriteExcel;

my $workbook = Spreadsheet::WriteExcel->new("test.xls");
my $worksheet = $workbook->addworksheet("foo");

my $formula = $worksheet->store_formula('=SUBTOTAL(9,A1:B1)');

foreach my $row (0..99) {
  $worksheet->write($row,0,$row+1);
  $worksheet->write($row,1,$row+1);
  $worksheet->repeat_formula($row,2,$formula,undef,qr/A1\b/,'A'.($row+
+1),qr/B1\b/,'A'.($row+2));
  $worksheet->repeat_formula($row,3,$formula,undef,qr/A1\b/,'B'.($row+
+1),qr/B1\b/,'B'.($row+2));
}

    [download]

[reply]
[d/l]
      *slaps self on head* That seems obvious now that you say it. I'll give it a go.

      thanks,
      thor

[reply]

Back to Seekers of Perl Wisdom