Spreadsheet::WriteExcel formulas in multi-row output

[finhagen]

I am wading into formulas within Spreadsheet::Write Excel. I am outputing rows of a database using $row++. However, I would like to use the data in certain columns using formulas thereby creating new columns in my spreadsheet output. However, the docs for Spreadsheet::WriteExcel isn't to detailed regarding this kind of formula syntax. Therefore, I am (once again) seeking the wisdom of the Perl Monks.
The Spreadsheet::WriteExcel syntax example:

    my $formula = $worksheet->store_formula('=A1 * 3 + 50');

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

I've tried a number of variations on that theme but none have worked. Currently my version is:

$worksheet->write_formula($row+2, 12, '=1-(K.($row+2)/J.($row+2))',$al
+ignPerc);

That code compiles but the output of the formula is always '1' (or 100%) which is not the right answer if I am successfully accessing the values in the adjacent columns. That implies my forumla is not correctly addressing the data in the other columns.

                        Deal Sheet Summary                            
+                        
DE Date    DE #    Ver    OP #    SO #    Rep    Customer    Deal Name
+    Sell    Book Value    Field Cost    Book Adjust    Field Margin  
+  Trade In    SW Maint    HW Maint    Services Sell    Services Cost 
+   Services Margin    Floor
2008-07-17    1001812321    0    807579    0    Sales Rep    Customer 
+1    Customer 1 deal    $5,885    $4,887    $1,228    -$93    100.00%
+    $0    $1,814    $0    $0    $0    0.00%    Breaks Price Floor 2

Here is the entire script for completeness:

#!/usr/bin/perl

#Perl script to print a Deal Sheet Report.

use strict;
use warnings;
use DBI;
use DBD::mysql;
use Spreadsheet::WriteExcel;

# DBI configuration variables
my $platform = "mysql";
my $database = "smdb";
my $host = "Sinn";
my $port = "3306";
my $tablename = "site";
my $user = "user";
my $pw = "password";

#Data source name
my $dsn = "dbi:mysql:smdb:localhost:3306";

# PERL DBI connection handle
my $dbh = DBI->connect($dsn, $user, $pw)or die "Unable to connect: $DB
+I::errstr\n";


#global variables
our $quote="1001981933";
our $version="1";
our $row;
our $sth;
our $dedate;
our $de;
our $op;
our $customer;
our $dealn;
our $cco;
our $ebv;
our $fcost;
our $fmarg;
our $bookad;
our $tir;
our $swmaint;
our $hwmaint;
our $svsums;
our $svcost;
our $svmarg;
our $floor;
our $aref;



my $workbook  = Spreadsheet::WriteExcel->new('/lebensraum/Transfer/per
+l/BPW/pq_dealsheet_summary.xls');
die "Problems creating new Excel file: $!" unless defined $workbook;

#Create new spreadsheet report
my $worksheet = $workbook->add_worksheet();

    $worksheet->set_column(0, 0,  11); # Column  A   width set to 11
    $worksheet->set_column(1, 1,  11); # Column  B   width set to 11
    $worksheet->set_column(2, 2,  6); # Column  C   width set to 6
    $worksheet->set_column(3, 3,  11); # Column  D   width set to 11
    $worksheet->set_column(4, 4,  11); # Column  E   width set to 11
    $worksheet->set_column(5, 5,  22); # Column  F   width set to 22
    $worksheet->set_column(6, 6,  35); # Column  G   width set to 35
    $worksheet->set_column(7, 7,  50); # Column  H   width set to 50
    $worksheet->set_column(8, 8,  14); # Column  I   width set to 14
    $worksheet->set_column(9, 9,  14); # Column  J   width set to 14
    $worksheet->set_column(10, 10,  14); # Column  K   width set to 14
    $worksheet->set_column(11, 11,  14); # Column  L   width set to 14
    $worksheet->set_column(12, 12,  14); # Column  M   width set to 14
    $worksheet->set_column(13, 13,  14); # Column  N   width set to 14
    $worksheet->set_column(14, 14,  14); # Column  O   width set to 14
    $worksheet->set_column(15, 15,  14); # Column  P   width set to 14
    $worksheet->set_column(16, 16,  14); # Column  Q   width set to 14
    $worksheet->set_column(17, 17,  14); # Column  R   width set to 14
    $worksheet->set_column(18, 18,  14); # Column  S   width set to 14
    $worksheet->set_column(19, 19,  20); # Column  S   width set to 20


    my $header = $workbook->add_format(
           bg_color => 43, # index for pale yellow
           center_across  => 1, #center
           size  => 12, #set font size to 12
           bold  => 1, #set bold on
                      );

    my $headerb = $workbook->add_format(
           bg_color => 51, # index for orange
           center_across  => 1,
           size  => 12,
           bold  => 1,
                      );

    my $alignc = $workbook->add_format(); #simple center only format
    $alignc->set_align('center');
    $alignc->set_align('vcenter');

    my $alignl = $workbook->add_format(); #simple align left format
    $alignl->set_align('left');
    $alignl->set_align('vcenter');

    my $alignld = $workbook->add_format(); #align left & format as $
    $alignld->set_num_format('$#,##0');
    $alignld->set_align('left');
    $alignld->set_align('vcenter');

    my $aligncd = $workbook->add_format(); #align center and format fo
+r date
    $aligncd->set_num_format('mm/dd/yyyy');
    $aligncd->set_align('center');
    $aligncd->set_align('vcenter');

    my $alignPerc = $workbook->add_format(); #align center and format 
+for percent
    $alignPerc->set_num_format('0.00%');
    $alignPerc->set_align('center');
    $alignPerc->set_align('vcenter');

    

    my $yel = $workbook->add_format(
           bg_color => 43, # index for pale yellow 
                      );

    my $ora = $workbook->add_format(
           bg_color => 51, # index for orange
                      );
    
    my $margformula= $worksheet->store_formula('=1-K/J'); #formula exp
+eriment

    $worksheet->write_blank(0, 0, $ora);#format row 0 orange
    $worksheet->write_blank(0, 1, $ora);
    $worksheet->write_blank(0, 2, $ora);
    $worksheet->write_blank(0, 3, $ora);
    $worksheet->write_blank(0, 4, $ora);
    $worksheet->write_blank(0, 5, $ora);
    $worksheet->write_string(0, 6, "Deal Sheet Summary",$headerb);
    $worksheet->write_blank(0, 7, $ora);
    $worksheet->write_blank(0, 8, $ora);
    $worksheet->write_blank(0, 9, $ora);
    $worksheet->write_blank(0, 10, $ora);
    $worksheet->write_blank(0, 11, $ora);
    $worksheet->write_blank(0, 12, $ora);
    $worksheet->write_blank(0, 13, $ora);
    $worksheet->write_blank(0, 14, $ora);
    $worksheet->write_blank(0, 15, $ora);
    $worksheet->write_blank(0, 16, $ora);
    $worksheet->write_blank(0, 17, $ora);
    $worksheet->write_blank(0, 18, $ora);
    $worksheet->write_blank(0, 19, $ora);
    $worksheet->write_string(0, 6, "Deal Sheet Summary",$headerb); #de
+fine column headers
    $worksheet->write_string(1, 0, "DE Date",$header);
    $worksheet->write_string(1, 1, "DE #",$header);
    $worksheet->write_string(1, 2, "Ver",$header);
    $worksheet->write_string(1, 3, "OP #",$header);
    $worksheet->write_string(1, 4, "SO #",$header);
    $worksheet->write_string(1, 5, "Rep",$header);
    $worksheet->write_string(1, 6, "Customer",$header);
    $worksheet->write_string(1, 7, "Deal Name",$header);
    $worksheet->write_string(1, 8, "Sell",$header);
    $worksheet->write_string(1, 9, "Book Value",$header);
    $worksheet->write_string(1, 10, "Field Cost",$header);
    $worksheet->write_string(1, 12, "Field Margin",$header);
    $worksheet->write_string(1, 11, "Book Adjust",$header);
    $worksheet->write_string(1, 13, "Trade In",$header);
    $worksheet->write_string(1, 14, "SW Maint",$header);
    $worksheet->write_string(1, 15, "HW Maint",$header);
    $worksheet->write_string(1, 16, "Services Sell",$header);
    $worksheet->write_string(1, 17, "Services Cost",$header);
    $worksheet->write_string(1, 18, "Services Margin",$header);
    $worksheet->write_string(1, 19, "Floor",$header);

#select columns from mysql quote table
$sth= $dbh->prepare("select dedate,de,den,op,so,rep,customer,dealn,cco
+,ebv,fcost,fmarg,bookad,tir,swmaint,hwmaint,svsums,svcost,svmarg,floo
+r
from quote");                          

$sth->execute();

#capture output into array and variablize the results 
 while ( my $aref = $sth->fetchrow_arrayref )
{
 my ($dedate,$de,$den,$op,$so,$rep,$customer,$dealn,$cco,$ebv,$fcost,$
+fmarg,$bookad,$tir,$swmaint,$hwmaint,$svsums,$svcost,$svmarg,$floor) 
+= @$aref;

#write data to spreadsheet row by row 
    $worksheet->write_date_time($row+2, 0, $dedate,$aligncd);
    $worksheet->write_number($row+2, 1, $de,$alignc);
    $worksheet->write_number($row+2, 2, $den,$alignc);
    $worksheet->write_number($row+2, 3, $op,$alignc);
    $worksheet->write_number($row+2, 4, $so,$alignc);
    $worksheet->write_string($row+2, 5, $rep,$alignl);
    $worksheet->write_string($row+2, 6, $customer,$alignl);
    $worksheet->write_string($row+2, 7, $dealn,$alignl);
    $worksheet->write_number($row+2, 8, $cco,$alignld);
     $worksheet->write_number($row+2, 9, $ebv,$alignld);
    $worksheet->write_number($row+2, 10, $fcost,$alignld);
    $worksheet->write_formula($row+2, 12, '=1-(K.($row+2)/J.($row+2))'
+,$alignPerc); #the code I am having trouble with
    $worksheet->write_number($row+2, 11, $bookad,$alignld);
    $worksheet->write_number($row+2, 13, $tir,$alignld);
    $worksheet->write_number($row+2, 14, $swmaint,$alignld);
    $worksheet->write_number($row+2, 15, $hwmaint,$alignld);
    $worksheet->write_number($row+2, 16, $svsums,$alignld);
    $worksheet->write_number($row+2, 17, $svcost,$alignld);
    $worksheet->write_number($row+2, 18, $svmarg,$alignPerc);
    $worksheet->write_string($row+2, 19, $floor,$alignc);
    $row++;
} 

$workbook->close();

exit;

Thanks in advance for the kind counsel of the Perl Monks - it is genuinely appreciated!
Hagen Finley Boulder, CO

PS - On my linux based Firefox browser the window for entering this kind of request just got VERY small (about 2" x 1/2"). Are we encouraging brevity or is this a bug? ;-)

[duckyd]

You're using single quotes when you attempt to set the formula, but they won't allow your $row variable to be interpolated - you're ending up with a literal '$row' in the excel formula, rather than the value of $row. Instead of:

$worksheet->write_formula($row+2, 12, '=1-(K.($row+2)/J.($row+2))',$al
+ignPerc);

You probably want:

$worksheet->write_formula($row+2, 12, '=1-(K'.($row+2).'/J'.($row+2).'
+)',$alignPerc);

or:

my $row_two_right = $row + 2;
$worksheet->write_formula($row_two_right, 12, "=1-(K$row_two_right/J$r
+ow_two_right)",$alignPerc);

Look at the actual formulas in excel and verify that they are being correctly generated.

[matze77]

Maybe the OP or some editor could use "Read More" tags on this (The first message)

@all Or did i miss something which i could do on my nodelets to not consuming the whole screen of the OPs question?
Thanks in Advance
MH

[Anonymous Monk]

I think you posted in the wrong forum, try "PerlMonks Discussion"

[Anonymous Monk]

Alternatively, you could use the "xl_rowcol_to_cell" function in Spreadsheet::WriteExcel::Utility to convert the row and column values to "A1" notation

ex:

my $jloc = xl_rowcol_to_cell($row+2, 12);
my $kloc = xl_rowcol_to_cell($row+2, 12);

$worksheet->write_formula($row+2, 12, '=1-('.$kloc.'/'.$jloc.')',$alig
+nPerc);