Beefy Boxes and Bandwidth Generously Provided by pair Networks
more useful options
 
PerlMonks  

Spreadsheet::WriteExcel formulas in multi-row output

by finhagen (Sexton)
on Dec 06, 2008 at 22:01 UTC ( #728600=perlquestion: print w/ replies, xml ) Need Help??
finhagen has asked for the wisdom of the Perl Monks concerning the following question:

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? ;-)

Comment on Spreadsheet::WriteExcel formulas in multi-row output
Select or Download Code
Re: Spreadsheet::WriteExcel formulas in multi-row output
by duckyd (Hermit) on Dec 06, 2008 at 23:01 UTC
    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.

      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
        I think you posted in the wrong forum, try "PerlMonks Discussion"
      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);

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://728600]
Approved by Perlbotics
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others cooling their heels in the Monastery: (6)
As of 2014-12-29 00:56 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (183 votes), past polls