Spreadsheet::WriteExcel question

vlilich has asked for the wisdom of the Perl Monks concerning the following question:

I have a question regarding the repeat_formula function... I have a formula that looks like this: =(12*1)+MONTH(L$39-1)-MONTH($A41)+1 where that first (12*1) could be a (12*2) or (12*3) and is a number I calculate as I pump rows. Is there a way to substitute a number via repeat_formula and not a proper cell reference?

my $formula1 = $ws->store_formula('=(12*A91)+MONTH(L$39-1)-MONTH(A92)+
+1');
[download]

A91 and A92 are the tokens I will be replacing. $diff is a result of a calculation and can change such as $diff=1 or $diff=2 etc...

$ws->repeat_formula($row, $col, $formula1, $fmt->{'number'},
                                     qw/^A91$/, $diff,
                                     qw/^A92$/, '$A' . $current_row
                                );
[download]

Substituting the A92 token works perfectly but sticking in $diff where A91 resides is not working, what gets in there is IV65536 instead. Thanks

Comment on Spreadsheet::WriteExcel question Select or Download Code

Replies are listed 'Best First'.
Re: Spreadsheet::WriteExcel question by pc88mxer (Vicar) on Jul 01, 2008 at 02:40 UTC
I don't think you can do this with `repeat_formula`. I believe you can only replace cell references with other cell references. Some evidence for this: `IV65536` is the cell reference for the last possible addressable cell. It seems that this is used whenever the replacement string cannot be parsed as a cell reference. In the documentation for the module it states that `repeat_formula()` "does not modify the tokens." I suspect this means that the type of a token cannot be modified, only their names can be modified. So you can't change a cell reference into a constant. Note that the most likely use of the `repeat_formula` feature of the Excel spreadsheet format is to accommodate the fill-up/down and fill-left/right operations. In those cases you are only replacing cell references with other cell references. I would ask the maintainer of the module for confirmation of these ideas. As a workaround, you probably have to store the result of the calculation (`$diff`) in a cell and reference the cell.	[reply] [d/l] [select]
Re^2: Spreadsheet::WriteExcel question by vlilich (Initiate) on Jul 01, 2008 at 20:59 UTC
figured it out... you can do number to number or cell reference to cell reference substitutions so that the following will work: `my $formula1 = $ws->store_formula('=(12*9999)+MONTH($L39-1)-MONTH(A92) ++1'); my $diff=111; $ws->repeat_formula($row, $col, $formula1, $fmt->{'number'}, qw/^9999$/, $diff, qw/^A92$/, 'A' . $current_row );` [download]	[reply] [d/l]