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]

"Sven Jungnickel" <Sven.Jungnickel@bmw.de> wrote in message
news:f1b78cf3.0309170308.c984a1e@posting.google.co m...

> 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]

Sven Jungnickel wrote:

> 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.
--
perl -MCPAN -e 'install jmcnamara & _ x ord $ ;' | tail -1

[Liviu]

Hello,
I realize it's a really old discussion but I'm having the same issue as Sven. I'm reading a CSV file with formulas in it, using $worksheet->write() function.
example CSV:
=========csv start
a,b,c
1,2,3
1,4,6
,,=SUMIF(A2:A3, 1, C2:C3)
==========csv end

the resulted xls opens just fine in OpenOffice but it does not calculate automatically in M$ Excel, as Sven described.

@John
You mentioned something about post-processing...
I did a search but found nothing of that. I'm also very new to perl... I started learning yesterday for this task only - that was a compliment for the documentation of the module; as a beginner I managed to do a pretty complex xls file in no time.
Thanks
Liviu