Re: 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);
[download]

You probably want:

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

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);
[download]

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

Comment on Re: Spreadsheet::WriteExcel formulas in multi-row output Select or Download Code

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	[reply]
Re^3: Spreadsheet::WriteExcel formulas in multi-row output by Anonymous Monk on Dec 07, 2008 at 09:53 UTC
I think you posted in the wrong forum, try "PerlMonks Discussion"	[reply]
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);` [download]	[reply] [d/l]