How to use the $worksheet-> repeat_formula option???

I'm not sure how to use the $worksheet-> repeat_formula
option.

How would I use the $worksheet->repeat_formula option to
save lines of
code instead of coding it the way i have below??

$worksheet->write(1, 3,
'=IF(AND(F2="",H2="",J2=""),&q
uot;",MAX(F2,H2,J2))', $format);
$worksheet->write(2, 3,
'=IF(AND(F3="",H3="",J3=""),&q
uot;",MAX(F3,H3,J3))', $format);
$worksheet->write(3, 3,
'=IF(AND(F4="",H4="",J4=""),&q
uot;",MAX(F4,H4,J4))', $format);
$worksheet->write(4, 3,
'=IF(AND(F5="",H5="",J5=""),&q
uot;",MAX(F5,H5,J5))', $format);
$worksheet->write(5, 3,
'=IF(AND(F6="",H6="",J6=""),&q
uot;",MAX(F6,H6,J6))', $format);
$worksheet->write(6, 3,
'=IF(AND(F7="",H7="",J7=""),&q
uot;",MAX(F7,H7,J7))', $format);
$worksheet->write(7, 3,
'=IF(AND(F8="",H8="",J8=""),&q
uot;",MAX(F8,H8,J8))', $format);
$worksheet->write(8, 3,
'=IF(AND(F9="",H9="",J9=""),&q
uot;",MAX(F9,H9,J9))', $format);
$worksheet->write(9, 3,
'=IF(AND(F10="",H10="",J10="")
,"",MAX(F10,H10,J10))', $format);



thanks


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the
Google Groups "Spreadsheet::WriteExcel" group.
To post to this group, send email to
spreadsheet-writeexcelgooglegroups.com
To unsubscribe from this group, send email to
spreadsheet-writeexcel-unsubscribegooglegroups.com
For more options, visit this group at http://groups.google.com/group/spreadsheet-writeexcel
?hl=en
-~----------~----~----~----~------~----~------~--~---

On Aug 12, 10:29 pm, ricky2 <richardkre...northwesternmutual.com>
wrote:
> I'm not sure how to use the $worksheet->
repeat_formula option.
>
> How would I use the $worksheet->repeat_formula
option to save lines of
> code instead of coding it the way i have below??



Hi,

Here is an example of a general approach:


    #!/usr/bin/perl -w

    use strict;
    use Spreadsheet::WriteExcel;


    my $workbook  =
Spreadsheet::WriteExcel->new('if.xls');
    my $worksheet = $workbook->add_worksheet();
    my $format    = $workbook->add_format(bold => 1);


    my $formula   =
'=IF(AND(F1="",H1="",J1=""),&q
uot;",MAX(F1,H1,J1))';
    my $if        = $worksheet->store_formula($formula);


    for my $row (1 .. 9) {
        $worksheet->repeat_formula($row, 3, $if,
$format,
                                   qr/^F1$/, 'F' . ($row
+1),
                                   qr/^H1$/, 'H' . ($row
+1),
                                   qr/^J1$/, 'J' . ($row
+1),
                                   qr/^F1$/, 'F' . ($row
+1),
                                   qr/^H1$/, 'H' . ($row
+1),
                                   qr/^J1$/, 'J' . ($row
+1),
        );
    }


    __END__


The repeat formula can be simplified a little due to the
repeated
substitutions:

    $worksheet->repeat_formula($row, 3, $if, $format,
                               (qr/^F1$/, 'F' . ($row +1),
                                qr/^H1$/, 'H' . ($row +1),
                                qr/^J1$/, 'J' . ($row +1)) x
2,
    );


If you are feeling brave and familiar with perlre you can
simplify it
a little further:

    $worksheet->repeat_formula($row, 3, $if, $format,
                               (qr/(?<=^[FHJ])1$/, ($row
+1)) x 6,
    );

The latter is probably not a good maintenance option though.


John.
--


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the
Google Groups "Spreadsheet::WriteExcel" group.
To post to this group, send email to
spreadsheet-writeexcelgooglegroups.com
To unsubscribe from this group, send email to
spreadsheet-writeexcel-unsubscribegooglegroups.com
For more options, visit this group at http://groups.google.com/group/spreadsheet-writeexcel
?hl=en
-~----------~----~----~----~------~----~------~--~---