.\" Automatically generated by Pod::Man 2.27 (Pod::Simple 3.28)
.\"
.\" Standard preamble:
.\" ========================================================================
.de Sp \" Vertical space (when we can't use .PP)
.if t .sp .5v
.if n .sp
..
.de Vb \" Begin verbatim text
.ft CW
.nf
.ne \\$1
..
.de Ve \" End verbatim text
.ft R
.fi
..
.\" Set up some character translations and predefined strings.  \*(-- will
.\" give an unbreakable dash, \*(PI will give pi, \*(L" will give a left
.\" double quote, and \*(R" will give a right double quote.  \*(C+ will
.\" give a nicer C++.  Capital omega is used to do unbreakable dashes and
.\" therefore won't be available.  \*(C` and \*(C' expand to `' in nroff,
.\" nothing in troff, for use with C<>.
.tr \(*W-
.ds C+ C\v'-.1v'\h'-1p'\s-2+\h'-1p'+\s0\v'.1v'\h'-1p'
.ie n \{\
.    ds -- \(*W-
.    ds PI pi
.    if (\n(.H=4u)&(1m=24u) .ds -- \(*W\h'-12u'\(*W\h'-12u'-\" diablo 10 pitch
.    if (\n(.H=4u)&(1m=20u) .ds -- \(*W\h'-12u'\(*W\h'-8u'-\"  diablo 12 pitch
.    ds L" ""
.    ds R" ""
.    ds C` ""
.    ds C' ""
'br\}
.el\{\
.    ds -- \|\(em\|
.    ds PI \(*p
.    ds L" ``
.    ds R" ''
.    ds C`
.    ds C'
'br\}
.\"
.\" Escape single quotes in literal strings from groff's Unicode transform.
.ie \n(.g .ds Aq \(aq
.el       .ds Aq '
.\"
.\" If the F register is turned on, we'll generate index entries on stderr for
.\" titles (.TH), headers (.SH), subsections (.SS), items (.Ip), and index
.\" entries marked with X<> in POD.  Of course, you'll have to process the
.\" output yourself in some meaningful fashion.
.\"
.\" Avoid warning from groff about undefined register 'F'.
.de IX
..
.nr rF 0
.if \n(.g .if rF .nr rF 1
.if (\n(rF:(\n(.g==0)) \{
.    if \nF \{
.        de IX
.        tm Index:\\$1\t\\n%\t"\\$2"
..
.        if !\nF==2 \{
.            nr % 0
.            nr F 2
.        \}
.    \}
.\}
.rr rF
.\" ========================================================================
.\"
.IX Title "Spreadsheet::WriteExcel::Formula 3"
.TH Spreadsheet::WriteExcel::Formula 3 "2013-11-07" "perl v5.16.3" "User Contributed Perl Documentation"
.\" For nroff, turn off justification.  Always turn off hyphenation; it makes
.\" way too many mistakes in technical documents.
.if n .ad l
.nh
.SH "NAME"
Formula \- A class for generating Excel formulas
.SH "SYNOPSIS"
.IX Header "SYNOPSIS"
See the documentation for Spreadsheet::WriteExcel
.SH "DESCRIPTION"
.IX Header "DESCRIPTION"
This module is used by Spreadsheet::WriteExcel. You do not need to use it directly.
.SH "NOTES"
.IX Header "NOTES"
The following notes are to help developers and maintainers understand the sequence of operation. They are also intended as a pro-memoria for the author. ;\-)
.PP
Spreadsheet::WriteExcel::Formula converts a textual representation of a formula into the pre-parsed binary format that Excel uses to store formulas. For example \f(CW\*(C`1+2*3\*(C'\fR is stored as follows: \f(CW\*(C`1E 01 00 1E 02 00 1E 03 00 05 03\*(C'\fR.
.PP
This string is comprised of operators and operands arranged in a reverse-Polish format. The meaning of the tokens in the above example is shown in the following table:
.PP
.Vb 6
\&    Token   Name        Value
\&    1E      ptgInt      0001   (stored as 01 00)
\&    1E      ptgInt      0002   (stored as 02 00)
\&    1E      ptgInt      0003   (stored as 03 00)
\&    05      ptgMul
\&    03      ptgAdd
.Ve
.PP
The tokens and token names are defined in the \*(L"Excel Developer's Kit\*(R" from Microsoft Press. \f(CW\*(C`ptg\*(C'\fR stands for Parse ThinG (as in \*(L"That lexer can't grok it, it's a parse thang.\*(R")
.PP
In general the tokens fall into two categories: operators such as \f(CW\*(C`ptgMul\*(C'\fR and operands such as \f(CW\*(C`ptgInt\*(C'\fR. When the formula is evaluated by Excel the operand tokens push values onto a stack. The operator tokens then pop the required number of operands off of the stack, perform an operation and push the resulting value back onto the stack. This methodology is similar to the basic operation of a reverse-Polish (\s-1RPN\s0) calculator.
.PP
Spreadsheet::WriteExcel::Formula parses a formula using a \f(CW\*(C`Parse::RecDescent\*(C'\fR parser (at a later stage it may use a \f(CW\*(C`Parse::Yapp\*(C'\fR parser or \f(CW\*(C`Parse::FastDescent\*(C'\fR).
.PP
The parser converts the textual representation of a formula into a parse tree. Thus, \f(CW\*(C`1+2*3\*(C'\fR is converted into something like the following, \f(CW\*(C`e\*(C'\fR stands for expression:
.PP
.Vb 5
\&             e
\&           / | \e
\&         1   +   e
\&               / | \e
\&             2   *   3
.Ve
.PP
The function \f(CW\*(C`_reverse_tree()\*(C'\fR recurses down through this structure swapping the order of operators followed by operands to produce a reverse-Polish tree. In other words the formula is converted from in-fix notation to post-fix. Following the above example the resulting tree would look like this:
.PP
.Vb 5
\&             e
\&           / | \e
\&         1   e   +
\&           / | \e
\&         2   3   *
.Ve
.PP
The result of the recursion is a single array of tokens. In our example the simplified form would look like the following:
.PP
.Vb 1
\&    (1, 2, 3, *, +)
.Ve
.PP
The actual return value contains some additional information to help in the secondary parsing stage:
.PP
.Vb 1
\&    (_num, 1, _num, 2, _num, 3, ptgMul, ptgAdd, _arg, 1)
.Ve
.PP
The additional tokens are:
.PP
.Vb 11
\&    Token       Meaning
\&    _num        The next token is a number
\&    _str        The next token is a string
\&    _ref2d      The next token is a 2d cell reference
\&    _ref3d      The next token is a 3d cell reference
\&    _range2d    The next token is a 2d range
\&    _range3d    The next token is a 3d range
\&    _funcV       The next token is a function
\&    _arg        The next token is the number of args for a function
\&    _class      The next token is a function name
\&    _vol        The formula contains a voltile function
.Ve
.PP
The \f(CW\*(C`_arg\*(C'\fR token is generated for all lists but is only used for functions that take a variable number of arguments.
.PP
The \f(CW\*(C`_class\*(C'\fR token indicates the start of the arguments to a function. This allows the post-processor to decide the \*(L"class\*(R" of the ref and range arguments that the function takes. The class can be reference, value or array. Since function calls can be nested, the class variable is stored on a stack in the \f(CW@class\fR array. The class of the ref or range is then read as the top element of the stack \f(CW$class[\-1]\fR. When a \f(CW\*(C`_funcV\*(C'\fR is read it pops the class value.
.PP
Certain Excel functions such as \s-1\fIRAND\s0()\fR and \s-1\fINOW\s0()\fR are designated as volatile and must be recalculated by Excel every time that a cell is updated. Any formulas that contain one of these functions has a specially formatted \f(CW\*(C`ptgAttr\*(C'\fR tag prepended to it to indicate that it is volatile.
.PP
A secondary parsing stage is carried out by \f(CW\*(C`parse_tokens()\*(C'\fR which converts these tokens into a binary string. For the \f(CW\*(C`1+2*3\*(C'\fR example this would give:
.PP
.Vb 1
\&    1E 01 00 1E 02 00 1E 03 00 05 03
.Ve
.PP
This two-pass method could probably have been reduced to a single pass through the \f(CW\*(C`Parse::RecDescent\*(C'\fR parser. However, it was easier to develop and debug this way.
.PP
The token values and formula values are stored in the \f(CW%ptg\fR and \f(CW%functions\fR hashes. These hashes and the parser object \f(CW$parser\fR are exposed as global data. This breaks the \s-1OO\s0 encapsulation, but means that they can be shared by several instances of Spreadsheet::WriteExcel called from the same program.
.PP
Non-English function names can be added to the \f(CW%functions\fR hash using the \f(CW\*(C`function_locale.pl\*(C'\fR program in the \f(CW\*(C`examples\*(C'\fR directory of the distro. The supported languages are: German, French, Spanish, Portuguese, Dutch, Finnish, Italian and Swedish. These languages are not added by default because there are conflicts between functions names in different languages.
.PP
The parser is initialised by \f(CW\*(C`_init_parser()\*(C'\fR. The initialisation is delayed until the first formula is parsed. This eliminates the overhead of generating the parser in programs that are not processing formulas. (The parser should really be pre-compiled, this is to-do when the grammar stabilises).
.SH "AUTHOR"
.IX Header "AUTHOR"
John McNamara jmcnamara@cpan.org
.SH "COPYRIGHT"
.IX Header "COPYRIGHT"
Copyright MM-MMX, John McNamara.
.PP
All Rights Reserved. This module is free software. It may be used, redistributed and/or modified under the same terms as Perl itself.