Subject: | Patch against v0.33 enabling Excel formula parsing and evaluation |

(This is *not* a bug report)
I put together a small patch against Spreadsheet::ParseExcel v0.33
enabling formula parsing and evaluation.
Instructions apply for an installed v0.33:
- Make a backup of ParseExcel.pm in case something goes wrong
$ cp ParseExcel.pm ParseExcel.pm.orig
- Patch Spreadsheet/ParseExcel.pm with the attached diff:
$ patch ParseExcel.pm <ParseExcel.pm.diff
- Put the attached Formula.pm into the Spreadsheet directory
- Test with the attached "spetest formulatest.xls"
See Formula.html generated from the module's POD for more information.
Use at your own risk, no warranties express or implied.
Have fun!
-Franz

Subject: | spetest |

Subject: | Formula.pm |

Subject: | formulatest.xls |

Message body not shown because it is not plain text.

Subject: | ParseExcel.pm.diff |

555a556,560
> #FFbegin - additional code for formula BIFF parsing
> my ($formula_length) = unpack("v", substr($sWk, 20, 2));
> my $formula_hexstring = substr($sWk, 22, $formula_length);
> #FFend
>
565c570
< Kind => 'Formulra Bool',
---
> Kind => 'Formula Bool', # FFcorr 'Formulra'
569a575
> Formula => $formula_hexstring, #FFadd
574c580,596
< else { # Result (Reserve Only)
---
> #FFbeg - string formula
> elsif ( $iKind==0 ) {
> _NewCell (
> $oBook, $iR, $iC,
> Kind => 'Formula String',
> Val => '', # FFrem - string follows formula tokens!
> FormatNo=> $iF,
> Format => $oBook->{Format}[$iF],
> Numeric => 0,
> Formula => $formula_hexstring, #FFadd
> Code => undef, #FFrem - set later by _subString()
> Book => $oBook,
> );
> $oBook->{_PrevPos} = [$iR, $iC, $iF];
> }
> #FFend
> else { # Result (Reserve Only)
586a609
> Formula => $formula_hexstring, #FFadd
621a645,654
> #FFbeg
> if ( defined(my $cell =
> $oBook->{Worksheet}[$oBook->{_CurSheet}]->{Cells}[$iR][$iC])
> ) {
> # cell already set (probably by a previous string formula)
> # just augment with string value
> $cell->{Val} = $cell->{_Value} = $sTxt;
> $cell->{Code} = $sCode;
> } else {
> #FFend
631a665
> } #FFadd
1740a1775
> Formula => $rhKey{Formula} || '', #FFadd
1754a1790,1796
> #FFbeg - add formula parsing code
> if ( $oCell->{Formula} ) {
> push @{$oBook->{Formulae}}, [$oBook->{_CurSheet}, $iR, $iC];
> $oCell->parse_formula($oBook, $oBook->{_CurSheet}, $iR, $iC);
> }
> #FFend
>

Subject: | Formula.html |