Spreadsheet::WriteExcel, Excel formula won't calculate

Discussion in 'Perl Misc' started by Sven Jungnickel, Sep 17, 2003.

  1. 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
     
    Sven Jungnickel, Sep 17, 2003
    #1
    1. Advertising

  2. "Sven Jungnickel" <> wrote in message
    news:...
    > 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
     
    Domenico Discepola, Sep 17, 2003
    #2
    1. Advertising

  3. 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
     
    John McNamara, Sep 18, 2003
    #3
  4. 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
     
    John McNamara, Jul 29, 2004
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Fabio

    Spreadsheet::WriteExcel

    Fabio, Mar 2, 2004, in forum: Perl
    Replies:
    2
    Views:
    890
    Tom Niesytto
    Mar 4, 2004
  2. Erica
    Replies:
    2
    Views:
    111
    John M. Gamble
    Sep 5, 2003
  3. Zhidian Du
    Replies:
    1
    Views:
    139
    A. Sinan Unur
    Dec 11, 2003
  4. Sean Berry

    Spreadsheet::WriteExcel

    Sean Berry, May 8, 2004, in forum: Perl Misc
    Replies:
    11
    Views:
    261
    Sean Berry
    May 9, 2004
  5. Replies:
    2
    Views:
    109
    Jim Gibson
    Jul 8, 2009
Loading...

Share This Page