Beefy Boxes and Bandwidth Generously Provided by pair Networks
"be consistent"
 
PerlMonks  

Re: Spreadsheet::WriteExcel formulas in multi-row output

by duckyd (Hermit)
on Dec 06, 2008 at 23:01 UTC ( [id://728610] : note . print w/replies, xml ) Need Help??


in reply to Spreadsheet::WriteExcel formulas in multi-row output

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.

Replies are listed 'Best First'.
Re^2: Spreadsheet::WriteExcel formulas in multi-row output
by matze77 (Friar) on Dec 07, 2008 at 09:21 UTC

    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"
Re^2: Spreadsheet::WriteExcel formulas in multi-row output
by Anonymous Monk on Jan 21, 2009 at 19:16 UTC
    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);