Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Perl > Perl Misc > Spreadsheet::WriteExcel, Excel formula won't calculate

Reply
Thread Tools

Spreadsheet::WriteExcel, Excel formula won't calculate

 
 
Sven Jungnickel
Guest
Posts: n/a
 
      09-17-2003
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
 
Reply With Quote
 
 
 
 
Domenico Discepola
Guest
Posts: n/a
 
      09-17-2003

"Sven Jungnickel" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed) 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


 
Reply With Quote
 
 
 
 
John McNamara
Guest
Posts: n/a
 
      09-18-2003
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
 
Reply With Quote
 
John McNamara
Guest
Posts: n/a
 
      07-28-2004
Jon Hairr wrote:

> 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.
--
# Sum the numbers in the first column of a file
perl -lpe '$,+=$_}{$_=+$,' file
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel Formula =?Utf-8?B?QyBBbmRlcnNvbg==?= Microsoft Certification 1 04-12-2006 02:15 AM
Excel 2003 Formula Doug Microsoft Certification 0 02-15-2004 12:18 PM
-Excel Formula Question Katherine Colby Computer Support 3 01-12-2004 11:45 AM
Simple Excel formula required sunny Computer Support 4 01-07-2004 09:41 AM
Excel formula for this... Max Sand Computer Support 6 09-02-2003 08:19 PM



Advertisments