petecm99 has asked for the wisdom of the Perl Monks concerning the following question:

UPDATE: I had a bug in the code, ParseExcel actually pulled the results of the formula very nicely, then I accidentally over-wrote it - D'OH!

Most holy of holies,

I have a program that reads in an Excel Spreadsheet, massages some data, and creates a new spreadsheet. I just noticed that when I read in a cell that contains a formula (SUM of some cells), it appears to be blank (or null). Is there a way to pull the formula from the cell? Preferably would like the evaluated answer, but the formula itself will suffice in a pinch. Some code snippets below...

my $inputfile = Spreadsheet::ParseExcel::Workbook->Parse($ExcelFileIn)
+;
my $insheet = $inputfile->Worksheet(0);
foreach $row ($insheet->{MinRow} .. $insheet->{MaxRow}) {
    foreach $col ($insheet->{MinCol} ..  $insheet->{MaxCol}) {
        my $cell = $insheet->{Cells}[$row][$col];
        ... do some stuff with $cell ...
    }
}

[download]
Many thanks for your insight...

Pete

Replies are listed 'Best First'.
Re: Can Spreadsheet::ParseExcel extract formulas?
by jmcnamara (Monsignor) on Oct 07, 2010 at 23:08 UTC
    It isn't possible to extract a formula from an Excel file using Spreadsheet::ParseExcel. The documentation should be clearer on this.

    The main reason is that formulas are stored in Excel as parsed symbols in an RPN structure. In theory it would be possible to deparse this back into the textual representation of the formula but it is one of those large time consuming features that I and the previous maintainers never got around to.

    --
    John.

[reply]
      Thanks for clarifying that, John!
[reply]
Re: Can Spreadsheet::ParseExcel extract formulas?
by Corion (Patriarch) on Oct 07, 2010 at 19:29 UTC
[reply]
      I did a search in the CPAN module documentation for 'formula' - nothing jumped out at me. There was a blurb about reading formulas created by WriteExcel -

      "This module cannot read the values of formulas from files created with Spreadsheet::WriteExcel unless the user specified the values when creating the file (which is generally not the case). The reason for this is that Spreadsheet::WriteExcel writes the formula but not the formula result since it isn't in a position to calculate arbitrary Excel formulas without access to Excel's formula engine."

      My input spreadsheet was not created by WriteExcel, so I assume this comment does not apply. I also looked in my site/lib install folder for examples, there were none. There are plenty for WriteExcel, but ParseExcel is rather sparse.
[reply]

        Quoting Spreadsheet::ParseExcel

        TODO

        • ...
        • Add Formula support, Hyperlink support, Named Range support.

        I would assume from that that formulae are currently unsupported.

[reply]
Re: Can Spreadsheet::ParseExcel extract formulas?
by dasgar (Priest) on Oct 07, 2010 at 21:11 UTC

    If you're on a Windows box with Excel installed, you do have another route available to: Win32::OLE. With this module, you can get the following items from a cell: value, formatted value, and formula. However, if you're on a *nix or Mac, I'm not sure what to suggest as an alternative.

    You'll definitely have a steeper learning with Win32::OLE, but it's advantage is that you can do more in an Excel file than you can with Spreadsheet::ParseExcel. However, Spreadsheet::ParseExcel has the advantage when you don't have Excel for Perl to manipulate through Win32::OLE, such as working in a *nix environment. Overall, which one is better depends on the programmer, his/her environment and what he/she needs to do.

[reply]
      Much appreciated, I'll dig further...
[reply]

Back to Seekers of Perl Wisdom