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

Hello Monks,
I am trying to repeat a formula with Spreadsheet::WriteExcel. I get the information from querying the database with DBI. For some reason, my formula is repeating for all the rows but the first one. Technically, it is the second row but the first row is has a name- not a number- and I don't care about formulas for that row. Anyways, here is part of my code: 
use strict;
use warnings;
use Spreadsheet::WriteExcel;
use DBI;

my $file = "/path/to/file/";
my $xWB = Spreadsheet::WriteExcel->new($file);
my $xWS5 = $xWB->add_worksheet('Summary');

my $format = $xWB->add_format();
$format->set_bold();

#DBI stuff here..

my $row_count = $sth->rows;
my $multiply = $xWS5->store_formula("=E2*F2");

# write worksheet
my $rowz = 0; my $colz = 0;
$xWS5->write($rowz,$colz++, $_) for @{$sth->{NAME}};

while (my $ar = $sth->fetchrow_arrayref) {
        ++$rowz; $colz = 0;
        $xWS5->write($rowz, $colz++, $_) for @$ar;
for my $line (0..$row_count) {
#this arguments are row, column, formula, format, first cell, second c
+ell
     $xWS5->repeat_formula($line, 6, $multiply, $format, "E2", 'E'.($l
+ine + 1), "F2", 'F'.($line + 1));
     }
}

[download]
In the for loop, I am trying to repeat the formula for column G. It is doing it for all the cells in column G except G2. I tried incrementing from 1 in the for loop, taking away 1 in $line + 1, etc.. So the formula only starts repeating at G3, not G2. Any insight, as usual, is greatly appreciated. :)

Replies are listed 'Best First'.
Re: Missing first row with formula
by hdb (Monsignor) on Jan 10, 2014 at 07:33 UTC

    Not sure what's going wrong for you but this script 

    use strict;
use warnings;
use Spreadsheet::WriteExcel;

my $file = "test.xls";
my $xWB = Spreadsheet::WriteExcel->new($file);
my $xWS5 = $xWB->add_worksheet('Summary');

my $format = $xWB->add_format();
$format->set_bold();

my $row_count = 5;
my $multiply = $xWS5->store_formula("=E2*F2");

for my $line (0..$row_count) {
    $xWS5->repeat_formula($line, 6, $multiply, $format, "E2", 'E'.($li
+ne + 1), "F2", 'F'.($line + 1));
}

    [download]

    creates a new spreadsheet 'test.xls' with a 'Summary' tab and formulae

    =E1*F1
=E2*F2
=E3*F3
=E4*F4
=E5*F5
=E6*F6

    [download]

    in cells G1 to G6, which I understand is what you want.

    As an additional remark I would think that the for my $line ... loop should not be with the while (my $ar = ... loop, as it repeats the same operation each time. This should not make a difference but is rather wasteful.

[reply]
[d/l]
[select]
      Thanks hdb. It turns out that actually doing that for loop with repeat formula after the DBI while loop was causing the problem. Doing what you suggested above but putting before the writing from DBI block made it work.Thanks a bunch!!
[reply]
Re: Missing first row with formula
by erix (Prior) on Jan 09, 2014 at 22:57 UTC 
      for my $line (0..$row_count) {
     #this arguments are row, column, formula, format, first cell, sec
+ond cell
     $xWS5->repeat_formula($line, 6,  [...]

    [download]

    I didn't try the code, but I think this 0-based $line should be made 1-based, no? WriteExcel rows are 1-based.

    UPDATE: ah, I stand corrected. WriteExcel rows are indeed 0-based. I did not carefully enough parse '1-based' from the this bit of documentation:

    Row-column notation uses a zero based index for both row and column while A1 notation uses the standard Excel alphanumeric sequence of column letter and 1-based row.

[reply]
[d/l]

      It's zero-based. From the documentation:

      $worksheet->write(0, 0, 'Hi Excel!');

      [download]

      and

      for my $row ( 0 .. 99 ) {
    $worksheet->repeat_formula( $row, 1, $formula, $format, 'A1', 'A' 
+. ( $row + 1 ) );
}

      [download]
[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom