Spread***::ParseExcel::SaveParser changes formula text to value

From: Christopher C LaFrancis (clafrancis_at_hotmail.com)
Date: 08/25/04

  • Next message: Mike Dunham-Wilkie: "XML-RPC server using Frontier::Daemon keeps crashing" 
    Date: 25 Aug 2004 08:52:57 -0700

    Hi,

    First, thanks in advance to anyone who might be able to offer some
    assistance.

    Here is my situation:

    Objective - to open an Excel workbook (aka spread***), delete the
    contents of a work***, populate that work*** with new data, and
    save the workbook.

    Status - using the perl module Spread***::ParseExcel::SaveParser, I
    have been able to accomplish this task, but with one significant
    problem...

    Problem - when the workbook is parsed, cells that contained formulas
    before (i.e., in the original Excel file) only contain the values of
    those formulas afterwards.

    The Excel files are version 2002. I am using Perl 5.8.3,
    Spread***-ParseExcel-0.2603, and Spread***-WriteExcel-2.04.

    The only relevant info I've been able to find on the web so far has
    been a posting by John McNamara (author of Spread***-WriteExcel) on
    2003.02.09:
    "Spread***::ParseExcel returns the calculated value of the formula
    and not the textual form of the formula. The reason for this is that
    Excel stores a formula in two parts. The first is a binary,
    pre-parsed, RPN encoding of the formula. The second is the result of
    the formula. To deparse the encoded part and return the textual form
    of the formula is a non-trivial task and currently
    Spread***::ParseExcel doesn't attempt it. Instead it returns the
    calculated value of the formula. In most cases this is sufficient."

    I really don't need to *do* anything with the formulas, they are not
    even in the work*** I am working with; I just need them to stay the
    way they are, so they can be used in the output'd workbook.

    Is it possible to simply leave them in their binary form? I began
    looking at modifying the Spread***::ParseExcel to try to skip over
    cells with formulas in them, but have been unsuccessful thus far.

    Sorry for the rather long post (my first one ever, by the way). Once
    again, I appreciate any help anyone can give.

    Kind Regards,
    Chris LaFrancis

  • Next message: Mike Dunham-Wilkie: "XML-RPC server using Frontier::Daemon keeps crashing"