Spreadsheet::WriteExcel, Excel formula won't calculate

[Sven Jungnickel]

I'm using the module Spreadsheet::WriteExcel to write an Excel file
from a Perl script which gets its date from a database. Some columns
of the worksheet should contain formulas. In general the writing of
formulas functions. When I open the Excel file the corresponding
columns have been calculated. But in columns where I use the function
SUMIF to calculate a mean value I'm having a problem. The formula is
written to the corresponding cells, but when I open the Excel file the
result is not calculated.

In order to get the result I have to click in the cells formula
editing field and then click onto the worksheet again. Each cell is
formatted as a number. I really don't have a clue what is going wrong
and what can I do against it.

Maybe anyone has encountered the same problem...Any hints are welcome.

Thanks in advance,

Sven

 

[Domenico Discepola]

I'm using the module Spreadsheet::WriteExcel to write an Excel file
from a Perl script which gets its date from a database. Some columns
of the worksheet should contain formulas. In general the writing of
formulas functions. When I open the Excel file the corresponding
columns have been calculated. But in columns where I use the function
SUMIF to calculate a mean value I'm having a problem. The formula is
written to the corresponding cells, but when I open the Excel file the
result is not calculated.


Maybe anyone has encountered the same problem...Any hints are welcome.

I've used this module for a few months now and found no problems of that
sort. Please post your code (or a link to it)...

Dom

 

[John McNamara]

I'm using the module Spreadsheet::WriteExcel to write an Excel file
from a Perl script which gets its date from a database.
...
But in columns where I use the function
SUMIF to calculate a mean value I'm having a problem. The formula is
written to the corresponding cells, but when I open the Excel file the
result is not calculated.

Spreadsheet::WriteExcel's formula parser doesn't always parse complex formulas
correctly. Specifically it can incorrectly assign the class of certain reference
tokens used internally by Excel.

This will be fixed at a later stage when I get time to rewrite the formula
parser.

In the meantime, it is possible to post-process the output from the parser to
correct this. Send me a short example program that demonstrates the problem
and I'll let you know how to fix it.

John.

 

[John McNamara]

I would be interested in the post-parser workaround

Thanks for the detailed bug report.

The best way to workaround this is to use store_formula() and
repeat_formula() and massage the parsed tokens.

So using your example, adding the following substitution to the end
will fix the problem:

...

$worksheet->repeat_formula(
$ref_row
,$column+6
,$formula
,$format
,"A1"
,"A".$formula_row
,'_ref2d' => '_ref2dV'
);

...


Hopefully, I will get the parser fixed soon so that this type of
hackery isn't required.

Drop me a line if you need further information.

John.