1Spreadsheet::WriteExcelU:s:eFrorCmounltar(i3b)uted PerlSDporceuamdesnhteaetti:o:nWriteExcel::Formula(3)
2
6 Formula - A class for generating Excel formulas
7
9 See the documentation for Spreadsheet::WriteExcel
10
12 This module is used by Spreadsheet::WriteExcel. You do not need to use
13 it directly.
14
16 The following notes are to help developers and maintainers understand
17 the sequence of operation. They are also intended as a pro-memoria for
18 the author. ;-)
19 Spreadsheet::WriteExcel::Formula converts a textual representation of a
21 formula into the pre-parsed binary format that Excel uses to store for‐
22 mulas. For example "1+2*3" is stored as follows: "1E 01 00 1E 02 00 1E
23 03 00 05 03".
24 This string is comprised of operators and operands arranged in a
26 reverse-Polish format. The meaning of the tokens in the above example
27 is shown in the following table:
28 Token Name Value
30 1E ptgInt 0001 (stored as 01 00)
31 1E ptgInt 0002 (stored as 02 00)
32 1E ptgInt 0003 (stored as 03 00)
33 05 ptgMul
34 03 ptgAdd
35 The tokens and token names are defined in the "Excel Developer's Kit"
37 from Microsoft Press. "ptg" stands for Parse ThinG (as in "That lexer
38 can't grok it, it's a parse thang.")
39 In general the tokens fall into two categories: operators such as "ptg‐
41 Mul" and operands such as "ptgInt". When the formula is evaluated by
42 Excel the operand tokens push values onto a stack. The operator tokens
43 then pop the required number of operands off of the stack, perform an
44 operation and push the resulting value back onto the stack. This
45 methodology is similar to the basic operation of a reverse-Polish (RPN)
46 calculator.
47 Spreadsheet::WriteExcel::Formula parses a formula using a
49 "Parse::RecDescent" parser (at a later stage it may use a "Parse::Yapp"
50 parser or "Parse::FastDescent").
51 The parser converts the textual representation of a formula into a
53 parse tree. Thus, "1+2*3" is converted into something like the follow‐
54 ing, "e" stands for expression:
55 e
57 / ⎪ \
58 1 + e
59 / ⎪ \
60 2 * 3
61 The function "_reverse_tree()" recurses down through this structure
63 swapping the order of operators followed by operands to produce a
64 reverse-Polish tree. In other words the formula is converted from in-
65 fix notation to post-fix. Following the above example the resulting
66 tree would look like this:
67 e
69 / ⎪ \
70 1 e +
71 / ⎪ \
72 2 3 *
73 The result of the recursion is a single array of tokens. In our example
75 the simplified form would look like the following:
76 (1, 2, 3, *, +)
78 The actual return value contains some additional information to help in
80 the secondary parsing stage:
81 (_num, 1, _num, 2, _num, 3, ptgMul, ptgAdd, _arg, 1)
83 The additional tokens are:
85 Token Meaning
87 _num The next token is a number
88 _str The next token is a string
89 _ref2d The next token is a 2d cell reference
90 _ref3d The next token is a 3d cell reference
91 _range2d The next token is a 2d range
92 _range3d The next token is a 3d range
93 _func The next token is a function
94 _arg The next token is the number of args for a function
95 _class The next token is a function name
96 _vol The formula contains a voltile function
97 The "_arg" token is generated for all lists but is only used for func‐
99 tions that take a variable number of arguments.
100 The "_class" token indicates the start of the arguments to a function.
102 This allows the post-processor to decide the "class" of the ref and
103 range arguments that the function takes. The class can be reference,
104 value or array. Since function calls can be nested, the class variable
105 is stored on a stack in the @class array. The class of the ref or range
106 is then read as the top element of the stack $class[-1]. When a "_func"
107 is read it pops the class value.
108 Certain Excel functions such as RAND() and NOW() are designated as
110 volatile and must be recalculated by Excel every time that a cell is
111 updated. Any formulas that contain one of these functions has a spe‐
112 cially formatted "ptgAttr" tag prepended to it to indicate that it is
113 volatile.
114 A secondary parsing stage is carried out by "parse_tokens()" which con‐
116 verts these tokens into a binary string. For the "1+2*3" example this
117 would give:
118 1E 01 00 1E 02 00 1E 03 00 05 03
120 This two-pass method could probably have been reduced to a single pass
122 through the "Parse::RecDescent" parser. However, it was easier to
123 develop and debug this way.
124 The token values and formula values are stored in the %ptg and %func‐
126 tions hashes. These hashes and the parser object $parser are exposed as
127 global data. This breaks the OO encapsulation, but means that they can
128 be shared by several instances of Spreadsheet::WriteExcel called from
129 the same program.
130 Non-English function names can be added to the %functions hash using
132 the "function_locale.pl" program in the "examples" directory of the
133 distro. The supported languages are: German, French, Spanish, Por‐
134 tuguese, Dutch, Finnish, Italian and Swedish. These languages are not
135 added by default because there are conflicts between functions names in
136 different languages.
137 The parser is initialised by "_init_parser()". The initialisation is
139 delayed until the first formula is parsed. This eliminates the overhead
140 of generating the parser in programs that are not processing formulas.
141 (The parser should really be pre-compiled, this is to-do when the gram‐
142 mar stabilises).
143
145 John McNamara jmcnamara@cpan.org
146
148 © MM-MMVI, John McNamara.
149 All Rights Reserved. This module is free software. It may be used,
151 redistributed and/or modified under the same terms as Perl itself.
152perl v5.8.8 2006-05-20Spreadsheet::WriteExcel::Formula(3)