line |
stmt |
bran |
cond |
sub |
pod |
time |
code |
1
|
|
|
|
|
|
|
package Spreadsheet::WriteExcel::Formula; |
2
|
|
|
|
|
|
|
|
3
|
|
|
|
|
|
|
############################################################################### |
4
|
|
|
|
|
|
|
# |
5
|
|
|
|
|
|
|
# Formula - A class for generating Excel formulas. |
6
|
|
|
|
|
|
|
# |
7
|
|
|
|
|
|
|
# |
8
|
|
|
|
|
|
|
# Used in conjunction with Spreadsheet::WriteExcel |
9
|
|
|
|
|
|
|
# |
10
|
|
|
|
|
|
|
# Copyright 2000-2010, John McNamara, jmcnamara@cpan.org |
11
|
|
|
|
|
|
|
# |
12
|
|
|
|
|
|
|
# Documentation after __END__ |
13
|
|
|
|
|
|
|
# |
14
|
|
|
|
|
|
|
|
15
|
32
|
|
|
32
|
|
229
|
use Exporter; |
|
32
|
|
|
|
|
61
|
|
|
32
|
|
|
|
|
1603
|
|
16
|
32
|
|
|
32
|
|
194
|
use strict; |
|
32
|
|
|
|
|
59
|
|
|
32
|
|
|
|
|
881
|
|
17
|
32
|
|
|
32
|
|
408
|
use Carp; |
|
32
|
|
|
|
|
50
|
|
|
32
|
|
|
|
|
2055
|
|
18
|
|
|
|
|
|
|
|
19
|
|
|
|
|
|
|
|
20
|
|
|
|
|
|
|
|
21
|
|
|
|
|
|
|
|
22
|
|
|
|
|
|
|
|
23
|
|
|
|
|
|
|
|
24
|
32
|
|
|
32
|
|
177
|
use vars qw($VERSION @ISA); |
|
32
|
|
|
|
|
62
|
|
|
32
|
|
|
|
|
159972
|
|
25
|
|
|
|
|
|
|
@ISA = qw(Exporter); |
26
|
|
|
|
|
|
|
|
27
|
|
|
|
|
|
|
$VERSION = '2.40'; |
28
|
|
|
|
|
|
|
|
29
|
|
|
|
|
|
|
############################################################################### |
30
|
|
|
|
|
|
|
# |
31
|
|
|
|
|
|
|
# Class data. |
32
|
|
|
|
|
|
|
# |
33
|
|
|
|
|
|
|
my $parser; |
34
|
|
|
|
|
|
|
my %ptg; |
35
|
|
|
|
|
|
|
my %functions; |
36
|
|
|
|
|
|
|
|
37
|
|
|
|
|
|
|
|
38
|
|
|
|
|
|
|
############################################################################### |
39
|
|
|
|
|
|
|
# |
40
|
|
|
|
|
|
|
# For debugging. |
41
|
|
|
|
|
|
|
# |
42
|
|
|
|
|
|
|
my $_debug = 0; |
43
|
|
|
|
|
|
|
|
44
|
|
|
|
|
|
|
|
45
|
|
|
|
|
|
|
############################################################################### |
46
|
|
|
|
|
|
|
# |
47
|
|
|
|
|
|
|
# new() |
48
|
|
|
|
|
|
|
# |
49
|
|
|
|
|
|
|
# Constructor |
50
|
|
|
|
|
|
|
# |
51
|
|
|
|
|
|
|
sub new { |
52
|
|
|
|
|
|
|
|
53
|
61
|
|
|
61
|
0
|
172
|
my $class = $_[0]; |
54
|
|
|
|
|
|
|
|
55
|
61
|
|
|
|
|
537
|
my $self = { |
56
|
|
|
|
|
|
|
_byte_order => $_[1], |
57
|
|
|
|
|
|
|
_workbook => "", |
58
|
|
|
|
|
|
|
_ext_sheets => {}, |
59
|
|
|
|
|
|
|
_ext_refs => {}, |
60
|
|
|
|
|
|
|
_ext_ref_count => 0, |
61
|
|
|
|
|
|
|
_ext_names => {}, |
62
|
|
|
|
|
|
|
}; |
63
|
|
|
|
|
|
|
|
64
|
61
|
|
|
|
|
204
|
bless $self, $class; |
65
|
61
|
|
|
|
|
234
|
return $self; |
66
|
|
|
|
|
|
|
} |
67
|
|
|
|
|
|
|
|
68
|
|
|
|
|
|
|
|
69
|
|
|
|
|
|
|
############################################################################### |
70
|
|
|
|
|
|
|
# |
71
|
|
|
|
|
|
|
# _init_parser() |
72
|
|
|
|
|
|
|
# |
73
|
|
|
|
|
|
|
# There is a small overhead involved in generating the parser. Therefore, the |
74
|
|
|
|
|
|
|
# initialisation is delayed until a formula is required. |
75
|
|
|
|
|
|
|
# TODO: use a pre-compiled grammar. |
76
|
|
|
|
|
|
|
# |
77
|
|
|
|
|
|
|
# Porters take note, a recursive descent parser isn't mandatory. A future |
78
|
|
|
|
|
|
|
# version of this module may use a YACC based parser instead. |
79
|
|
|
|
|
|
|
# |
80
|
|
|
|
|
|
|
sub _init_parser { |
81
|
|
|
|
|
|
|
|
82
|
4
|
|
|
4
|
|
10
|
my $self = shift; |
83
|
|
|
|
|
|
|
|
84
|
|
|
|
|
|
|
# Delay loading Parse::RecDescent to reduce the module dependencies. |
85
|
4
|
|
|
|
|
8
|
eval { require Parse::RecDescent }; |
|
4
|
|
|
|
|
15598
|
|
86
|
4
|
50
|
|
|
|
249879
|
die "The Parse::RecDescent module must be installed in order ". |
87
|
|
|
|
|
|
|
"to write an Excel formula\n" if $@; |
88
|
|
|
|
|
|
|
|
89
|
4
|
|
|
|
|
32
|
$self->_initialize_hashes(); |
90
|
|
|
|
|
|
|
|
91
|
|
|
|
|
|
|
# The parsing grammar. |
92
|
|
|
|
|
|
|
# |
93
|
|
|
|
|
|
|
# TODO: Add support for international versions of Excel |
94
|
|
|
|
|
|
|
# |
95
|
4
|
|
|
|
|
70
|
$parser = Parse::RecDescent->new(<<'EndGrammar'); |
96
|
|
|
|
|
|
|
|
97
|
|
|
|
|
|
|
expr: list |
98
|
|
|
|
|
|
|
|
99
|
|
|
|
|
|
|
# Match arg lists such as SUM(1,2, 3) |
100
|
|
|
|
|
|
|
list: |
101
|
|
|
|
|
|
|
{ [ $item[1], '_arg', scalar @{$item[1]} ] } |
102
|
|
|
|
|
|
|
|
103
|
|
|
|
|
|
|
addition: |
104
|
|
|
|
|
|
|
|
105
|
|
|
|
|
|
|
# TODO: The add_op operators don't have equal precedence. |
106
|
|
|
|
|
|
|
add_op: add | sub | concat |
107
|
|
|
|
|
|
|
| eq | ne | le | ge | lt | gt # Order is important |
108
|
|
|
|
|
|
|
|
109
|
|
|
|
|
|
|
add: '+' { 'ptgAdd' } |
110
|
|
|
|
|
|
|
sub: '-' { 'ptgSub' } |
111
|
|
|
|
|
|
|
concat: '&' { 'ptgConcat' } |
112
|
|
|
|
|
|
|
eq: '=' { 'ptgEQ' } |
113
|
|
|
|
|
|
|
ne: '<>' { 'ptgNE' } |
114
|
|
|
|
|
|
|
le: '<=' { 'ptgLE' } |
115
|
|
|
|
|
|
|
ge: '>=' { 'ptgGE' } |
116
|
|
|
|
|
|
|
lt: '<' { 'ptgLT' } |
117
|
|
|
|
|
|
|
gt: '>' { 'ptgGT' } |
118
|
|
|
|
|
|
|
|
119
|
|
|
|
|
|
|
|
120
|
|
|
|
|
|
|
multiplication: |
121
|
|
|
|
|
|
|
|
122
|
|
|
|
|
|
|
mult_op: mult | div |
123
|
|
|
|
|
|
|
mult: '*' { 'ptgMul' } |
124
|
|
|
|
|
|
|
div: '/' { 'ptgDiv' } |
125
|
|
|
|
|
|
|
|
126
|
|
|
|
|
|
|
# Left associative (apparently) |
127
|
|
|
|
|
|
|
exponention: |
128
|
|
|
|
|
|
|
|
129
|
|
|
|
|
|
|
exp_op: '^' { 'ptgPower' } |
130
|
|
|
|
|
|
|
|
131
|
|
|
|
|
|
|
factor: number # Order is important |
132
|
|
|
|
|
|
|
| string |
133
|
|
|
|
|
|
|
| range2d |
134
|
|
|
|
|
|
|
| range3d |
135
|
|
|
|
|
|
|
| true |
136
|
|
|
|
|
|
|
| false |
137
|
|
|
|
|
|
|
| ref2d |
138
|
|
|
|
|
|
|
| ref3d |
139
|
|
|
|
|
|
|
| function |
140
|
|
|
|
|
|
|
| name |
141
|
|
|
|
|
|
|
| '(' expr ')' { [$item[2], 'ptgParen'] } |
142
|
|
|
|
|
|
|
|
143
|
|
|
|
|
|
|
# Match a string. |
144
|
|
|
|
|
|
|
# Regex by merlyn. See http://www.perlmonks.org/index.pl?node_id=330280 |
145
|
|
|
|
|
|
|
# |
146
|
|
|
|
|
|
|
string: /"([^"]|"")*"/ #" For editors |
147
|
|
|
|
|
|
|
{ [ '_str', $item[1]] } |
148
|
|
|
|
|
|
|
|
149
|
|
|
|
|
|
|
# Match float or integer |
150
|
|
|
|
|
|
|
number: /([+-]?)(?=\d|\.\d)\d*(\.\d*)?([Ee]([+-]?\d+))?/ |
151
|
|
|
|
|
|
|
{ ['_num', $item[1]] } |
152
|
|
|
|
|
|
|
|
153
|
|
|
|
|
|
|
# Note: The highest column values is IV. The following regexes match |
154
|
|
|
|
|
|
|
# up to IZ. Out of range values are caught in the code. |
155
|
|
|
|
|
|
|
# |
156
|
|
|
|
|
|
|
# Note: sheetnames with whitespace, commas, or parentheses must be in |
157
|
|
|
|
|
|
|
# single quotes. Applies to ref3d and range3d |
158
|
|
|
|
|
|
|
# |
159
|
|
|
|
|
|
|
|
160
|
|
|
|
|
|
|
# Match A1, $A1, A$1 or $A$1. |
161
|
|
|
|
|
|
|
ref2d: /\$?[A-I]?[A-Z]\$?\d+/ |
162
|
|
|
|
|
|
|
{ ['_ref2d', $item[1]] } |
163
|
|
|
|
|
|
|
|
164
|
|
|
|
|
|
|
# Match an external sheet reference: Sheet1!A1 or 'Sheet (1)'!A1 |
165
|
|
|
|
|
|
|
ref3d: /[^!(,]+!\$?[A-I]?[A-Z]\$?\d+/ |
166
|
|
|
|
|
|
|
{ ['_ref3d', $item[1]] } |
167
|
|
|
|
|
|
|
| /'[^']+'!\$?[A-I]?[A-Z]\$?\d+/ |
168
|
|
|
|
|
|
|
{ ['_ref3d', $item[1]] } |
169
|
|
|
|
|
|
|
|
170
|
|
|
|
|
|
|
# Match A1:C5, $A1:$C5 or A:C etc. |
171
|
|
|
|
|
|
|
range2d: /\$?[A-I]?[A-Z]\$?(\d+)?:\$?[A-I]?[A-Z]\$?(\d+)?/ |
172
|
|
|
|
|
|
|
{ ['_range2d', $item[1]] } |
173
|
|
|
|
|
|
|
|
174
|
|
|
|
|
|
|
# Match an external sheet range. 'Sheet 1:Sheet 2'!B2:C5 |
175
|
|
|
|
|
|
|
range3d: /[^!(,]+!\$?[A-I]?[A-Z]\$?(\d+)?:\$?[A-I]?[A-Z]\$?(\d+)?/ |
176
|
|
|
|
|
|
|
{ ['_range3d', $item[1]] } |
177
|
|
|
|
|
|
|
| /'[^']+'!\$?[A-I]?[A-Z]\$?(\d+)?:\$?[A-I]?[A-Z]\$?(\d+)?/ |
178
|
|
|
|
|
|
|
{ ['_range3d', $item[1]] } |
179
|
|
|
|
|
|
|
|
180
|
|
|
|
|
|
|
# Match a function name. |
181
|
|
|
|
|
|
|
function: /[A-Z0-9À-Ü_.]+/ '()' |
182
|
|
|
|
|
|
|
{ ['_funcV', $item[1]] } |
183
|
|
|
|
|
|
|
| /[A-Z0-9À-Ü_.]+/ '(' expr ')' |
184
|
|
|
|
|
|
|
{ ['_class', $item[1], $item[3], '_funcV', $item[1]] } |
185
|
|
|
|
|
|
|
| /[A-Z0-9À-Ü_.]+/ '(' list ')' |
186
|
|
|
|
|
|
|
{ ['_class', $item[1], $item[3], '_funcV', $item[1]] } |
187
|
|
|
|
|
|
|
|
188
|
|
|
|
|
|
|
# Match a defined name. |
189
|
|
|
|
|
|
|
name: /[A-Za-z_]\w+/ |
190
|
|
|
|
|
|
|
{ ['_name', $item[1]] } |
191
|
|
|
|
|
|
|
|
192
|
|
|
|
|
|
|
# Boolean values. |
193
|
|
|
|
|
|
|
true: 'TRUE' { [ 'ptgBool', 1 ] } |
194
|
|
|
|
|
|
|
|
195
|
|
|
|
|
|
|
false: 'FALSE' { [ 'ptgBool', 0 ] } |
196
|
|
|
|
|
|
|
|
197
|
|
|
|
|
|
|
EndGrammar |
198
|
|
|
|
|
|
|
|
199
|
4
|
50
|
|
|
|
882658
|
print "Init_parser.\n\n" if $_debug; |
200
|
|
|
|
|
|
|
} |
201
|
|
|
|
|
|
|
|
202
|
|
|
|
|
|
|
|
203
|
|
|
|
|
|
|
|
204
|
|
|
|
|
|
|
############################################################################### |
205
|
|
|
|
|
|
|
# |
206
|
|
|
|
|
|
|
# parse_formula() |
207
|
|
|
|
|
|
|
# |
208
|
|
|
|
|
|
|
# Takes a textual description of a formula and returns a RPN encoded byte |
209
|
|
|
|
|
|
|
# string. |
210
|
|
|
|
|
|
|
# |
211
|
|
|
|
|
|
|
sub parse_formula { |
212
|
|
|
|
|
|
|
|
213
|
78
|
|
|
78
|
0
|
127
|
my $self= shift; |
214
|
|
|
|
|
|
|
|
215
|
|
|
|
|
|
|
# Initialise the parser if this is the first call |
216
|
78
|
100
|
|
|
|
205
|
$self->_init_parser() if not defined $parser; |
217
|
|
|
|
|
|
|
|
218
|
78
|
|
|
|
|
183
|
my $formula = shift @_; |
219
|
78
|
|
|
|
|
127
|
my $tokens; |
220
|
|
|
|
|
|
|
|
221
|
78
|
50
|
|
|
|
247
|
print $formula, "\n" if $_debug; |
222
|
|
|
|
|
|
|
|
223
|
|
|
|
|
|
|
# Build the parse tree for the formula |
224
|
78
|
|
|
|
|
841
|
my $parsetree =$parser->expr($formula); |
225
|
|
|
|
|
|
|
|
226
|
|
|
|
|
|
|
# Check if parsing worked. |
227
|
78
|
50
|
|
|
|
556790
|
if (defined $parsetree) { |
228
|
78
|
|
|
|
|
1748
|
my @tokens = $self->_reverse_tree(@$parsetree); |
229
|
|
|
|
|
|
|
|
230
|
|
|
|
|
|
|
# Add a volatile token if the formula contains a volatile function. |
231
|
|
|
|
|
|
|
# This must be the first token in the list |
232
|
|
|
|
|
|
|
# |
233
|
78
|
50
|
|
|
|
366
|
unshift @tokens, '_vol' if $self->_check_volatile(@tokens); |
234
|
|
|
|
|
|
|
|
235
|
|
|
|
|
|
|
# The return value depends on which Worksheet.pm method is the caller |
236
|
78
|
100
|
|
|
|
268
|
if (wantarray) { |
237
|
|
|
|
|
|
|
# Parse formula to see if it throws any errors and then |
238
|
|
|
|
|
|
|
# return raw tokens to Worksheet::store_formula() |
239
|
|
|
|
|
|
|
# |
240
|
77
|
|
|
|
|
279
|
$self->parse_tokens(@tokens); |
241
|
77
|
|
|
|
|
1021
|
return @tokens; |
242
|
|
|
|
|
|
|
} |
243
|
|
|
|
|
|
|
else{ |
244
|
|
|
|
|
|
|
# Return byte stream to Worksheet::write_formula() |
245
|
1
|
|
|
|
|
8
|
return $self->parse_tokens(@tokens); |
246
|
|
|
|
|
|
|
} |
247
|
|
|
|
|
|
|
} |
248
|
|
|
|
|
|
|
else { |
249
|
0
|
|
|
|
|
0
|
die "Couldn't parse formula: =$formula\n"; |
250
|
|
|
|
|
|
|
} |
251
|
|
|
|
|
|
|
} |
252
|
|
|
|
|
|
|
|
253
|
|
|
|
|
|
|
|
254
|
|
|
|
|
|
|
############################################################################### |
255
|
|
|
|
|
|
|
# |
256
|
|
|
|
|
|
|
# parse_tokens() |
257
|
|
|
|
|
|
|
# |
258
|
|
|
|
|
|
|
# Convert each token or token pair to its Excel 'ptg' equivalent. |
259
|
|
|
|
|
|
|
# |
260
|
|
|
|
|
|
|
sub parse_tokens { |
261
|
|
|
|
|
|
|
|
262
|
152
|
|
|
152
|
0
|
260
|
my $self = shift; |
263
|
152
|
|
|
|
|
260
|
my $parse_str = ''; |
264
|
152
|
|
|
|
|
225
|
my $last_type = ''; |
265
|
152
|
|
|
|
|
276
|
my $modifier = ''; |
266
|
152
|
|
|
|
|
205
|
my $num_args = 0; |
267
|
152
|
|
|
|
|
237
|
my $class = 0; |
268
|
152
|
|
|
|
|
422
|
my @class = 1; |
269
|
152
|
|
|
|
|
445
|
my @tokens = @_; |
270
|
|
|
|
|
|
|
|
271
|
|
|
|
|
|
|
|
272
|
|
|
|
|
|
|
# A note about the class modifiers used below. In general the class, |
273
|
|
|
|
|
|
|
# "reference" or "value", of a function is applied to all of its operands. |
274
|
|
|
|
|
|
|
# However, in certain circumstances the operands can have mixed classes, |
275
|
|
|
|
|
|
|
# e.g. =VLOOKUP with external references. These will eventually be dealt |
276
|
|
|
|
|
|
|
# with by the parser. However, as a workaround the class type of a token |
277
|
|
|
|
|
|
|
# can be changed via the repeat_formula interface. Thus, a _ref2d token can |
278
|
|
|
|
|
|
|
# be changed by the user to _ref2dA or _ref2dR to change its token class. |
279
|
|
|
|
|
|
|
# |
280
|
152
|
|
|
|
|
459
|
while (@_) { |
281
|
314
|
|
|
|
|
504
|
my $token = shift @_; |
282
|
|
|
|
|
|
|
|
283
|
314
|
100
|
|
|
|
1595
|
if ($token eq '_arg') { |
|
|
50
|
|
|
|
|
|
|
|
50
|
|
|
|
|
|
|
|
50
|
|
|
|
|
|
|
|
100
|
|
|
|
|
|
|
|
100
|
|
|
|
|
|
|
|
100
|
|
|
|
|
|
|
|
100
|
|
|
|
|
|
|
|
100
|
|
|
|
|
|
|
|
50
|
|
|
|
|
|
|
|
50
|
|
|
|
|
|
|
|
50
|
|
|
|
|
|
|
|
50
|
|
|
|
|
|
|
|
50
|
|
|
|
|
|
284
|
152
|
|
|
|
|
451
|
$num_args = shift @_; |
285
|
|
|
|
|
|
|
} |
286
|
|
|
|
|
|
|
elsif ($token eq '_class') { |
287
|
0
|
|
|
|
|
0
|
$token = shift @_; |
288
|
0
|
|
|
|
|
0
|
$class = $functions{$token}[2]; |
289
|
|
|
|
|
|
|
# If $class is undef then it means that the function isn't valid. |
290
|
0
|
0
|
|
|
|
0
|
die "Unknown function $token() in formula\n" unless defined $class; |
291
|
0
|
|
|
|
|
0
|
push @class, $class; |
292
|
|
|
|
|
|
|
} |
293
|
|
|
|
|
|
|
elsif ($token eq '_vol') { |
294
|
0
|
|
|
|
|
0
|
$parse_str .= $self->_convert_volatile(); |
295
|
|
|
|
|
|
|
} |
296
|
|
|
|
|
|
|
elsif ($token eq 'ptgBool') { |
297
|
0
|
|
|
|
|
0
|
$token = shift @_; |
298
|
0
|
|
|
|
|
0
|
$parse_str .= $self->_convert_bool($token); |
299
|
|
|
|
|
|
|
} |
300
|
|
|
|
|
|
|
elsif ($token eq '_num') { |
301
|
134
|
|
|
|
|
283
|
$token = shift @_; |
302
|
134
|
|
|
|
|
383
|
$parse_str .= $self->_convert_number($token); |
303
|
|
|
|
|
|
|
} |
304
|
|
|
|
|
|
|
elsif ($token eq '_str') { |
305
|
8
|
|
|
|
|
18
|
$token = shift @_; |
306
|
8
|
|
|
|
|
30
|
$parse_str .= $self->_convert_string($token); |
307
|
|
|
|
|
|
|
} |
308
|
|
|
|
|
|
|
elsif ($token =~ /^_ref2d/) { |
309
|
4
|
|
|
|
|
19
|
($modifier = $token) =~ s/_ref2d//; |
310
|
4
|
|
|
|
|
8
|
$class = $class[-1]; |
311
|
4
|
50
|
|
|
|
14
|
$class = 0 if $modifier eq 'R'; |
312
|
4
|
50
|
|
|
|
14
|
$class = 1 if $modifier eq 'V'; |
313
|
4
|
|
|
|
|
8
|
$token = shift @_; |
314
|
4
|
|
|
|
|
16
|
$parse_str .= $self->_convert_ref2d($token, $class); |
315
|
|
|
|
|
|
|
} |
316
|
|
|
|
|
|
|
elsif ($token =~ /^_ref3d/) { |
317
|
3
|
|
|
|
|
48
|
($modifier = $token) =~ s/_ref3d//; |
318
|
3
|
|
|
|
|
9
|
$class = $class[-1]; |
319
|
3
|
50
|
|
|
|
11
|
$class = 0 if $modifier eq 'R'; |
320
|
3
|
50
|
|
|
|
14
|
$class = 1 if $modifier eq 'V'; |
321
|
3
|
|
|
|
|
6
|
$token = shift @_; |
322
|
3
|
|
|
|
|
15
|
$parse_str .= $self->_convert_ref3d($token, $class); |
323
|
|
|
|
|
|
|
} |
324
|
|
|
|
|
|
|
elsif ($token =~ /^_range2d/) { |
325
|
8
|
|
|
|
|
36
|
($modifier = $token) =~ s/_range2d//; |
326
|
8
|
|
|
|
|
16
|
$class = $class[-1]; |
327
|
8
|
100
|
|
|
|
27
|
$class = 0 if $modifier eq 'R'; |
328
|
8
|
50
|
|
|
|
25
|
$class = 1 if $modifier eq 'V'; |
329
|
8
|
|
|
|
|
16
|
$token = shift @_; |
330
|
8
|
|
|
|
|
33
|
$parse_str .= $self->_convert_range2d($token, $class); |
331
|
|
|
|
|
|
|
} |
332
|
|
|
|
|
|
|
elsif ($token =~ /^_range3d/) { |
333
|
0
|
|
|
|
|
0
|
($modifier = $token) =~ s/_range3d//; |
334
|
0
|
|
|
|
|
0
|
$class = $class[-1]; |
335
|
0
|
0
|
|
|
|
0
|
$class = 0 if $modifier eq 'R'; |
336
|
0
|
0
|
|
|
|
0
|
$class = 1 if $modifier eq 'V'; |
337
|
0
|
|
|
|
|
0
|
$token = shift @_; |
338
|
0
|
|
|
|
|
0
|
$parse_str .= $self->_convert_range3d($token, $class); |
339
|
|
|
|
|
|
|
} |
340
|
|
|
|
|
|
|
elsif ($token =~ /^_name/) { |
341
|
0
|
|
|
|
|
0
|
($modifier = $token) =~ s/_name//; |
342
|
0
|
|
|
|
|
0
|
$class = $class[-1]; |
343
|
0
|
0
|
|
|
|
0
|
$class = 0 if $modifier eq 'R'; |
344
|
0
|
0
|
|
|
|
0
|
$class = 1 if $modifier eq 'V'; |
345
|
0
|
|
|
|
|
0
|
$token = shift @_; |
346
|
0
|
|
|
|
|
0
|
$parse_str .= $self->_convert_name($token, $class); |
347
|
|
|
|
|
|
|
} |
348
|
|
|
|
|
|
|
elsif ($token eq '_funcV') { |
349
|
0
|
|
|
|
|
0
|
$token = shift @_; |
350
|
0
|
|
|
|
|
0
|
$parse_str .= $self->_convert_function($token, $num_args); |
351
|
0
|
|
|
|
|
0
|
pop @class; |
352
|
0
|
|
|
|
|
0
|
$num_args = 0; # Reset after use |
353
|
|
|
|
|
|
|
} |
354
|
|
|
|
|
|
|
elsif ($token eq '_func') { |
355
|
0
|
|
|
|
|
0
|
$token = shift @_; |
356
|
0
|
|
|
|
|
0
|
$parse_str .= $self->_convert_function($token, $num_args, 1); |
357
|
0
|
|
|
|
|
0
|
pop @class; |
358
|
0
|
|
|
|
|
0
|
$num_args = 0; # Reset after use |
359
|
|
|
|
|
|
|
} |
360
|
|
|
|
|
|
|
elsif (exists $ptg{$token}) { |
361
|
5
|
|
|
|
|
18
|
$parse_str .= pack("C", $ptg{$token}); |
362
|
|
|
|
|
|
|
} |
363
|
|
|
|
|
|
|
else { |
364
|
|
|
|
|
|
|
# Unrecognised token |
365
|
0
|
|
|
|
|
0
|
return undef; |
366
|
|
|
|
|
|
|
} |
367
|
|
|
|
|
|
|
} |
368
|
|
|
|
|
|
|
|
369
|
|
|
|
|
|
|
|
370
|
152
|
50
|
|
|
|
406
|
if ($_debug) { |
371
|
0
|
|
|
|
|
0
|
print join(" ", map { sprintf "%02X", $_ } unpack("C*",$parse_str)); |
|
0
|
|
|
|
|
0
|
|
372
|
0
|
|
|
|
|
0
|
print "\n\n"; |
373
|
0
|
|
|
|
|
0
|
print join(" ", @tokens), "\n\n"; |
374
|
|
|
|
|
|
|
} |
375
|
|
|
|
|
|
|
|
376
|
152
|
|
|
|
|
658
|
return $parse_str; |
377
|
|
|
|
|
|
|
} |
378
|
|
|
|
|
|
|
|
379
|
|
|
|
|
|
|
|
380
|
|
|
|
|
|
|
############################################################################### |
381
|
|
|
|
|
|
|
# |
382
|
|
|
|
|
|
|
# _reverse_tree() |
383
|
|
|
|
|
|
|
# |
384
|
|
|
|
|
|
|
# This function descends recursively through the parse tree. At each level it |
385
|
|
|
|
|
|
|
# swaps the order of an operator followed by an operand. |
386
|
|
|
|
|
|
|
# For example, 1+2*3 would be converted in the following sequence: |
387
|
|
|
|
|
|
|
# 1 + 2 * 3 |
388
|
|
|
|
|
|
|
# 1 + (2 * 3) |
389
|
|
|
|
|
|
|
# 1 + (2 3 *) |
390
|
|
|
|
|
|
|
# 1 (2 3 *) + |
391
|
|
|
|
|
|
|
# 1 2 3 * + |
392
|
|
|
|
|
|
|
# |
393
|
|
|
|
|
|
|
sub _reverse_tree |
394
|
|
|
|
|
|
|
{ |
395
|
476
|
|
|
476
|
|
727
|
my $self = shift; |
396
|
|
|
|
|
|
|
|
397
|
476
|
|
|
|
|
670
|
my @tokens; |
398
|
476
|
|
|
|
|
1178
|
my @expression = @_; |
399
|
476
|
|
|
|
|
620
|
my @stack; |
400
|
|
|
|
|
|
|
|
401
|
476
|
|
|
|
|
1269
|
while (@expression) { |
402
|
716
|
|
|
|
|
1239
|
my $token = shift @expression; |
403
|
|
|
|
|
|
|
|
404
|
|
|
|
|
|
|
# If the token is an operator swap it with the following operand |
405
|
716
|
50
|
66
|
|
|
20771
|
if ( $token eq 'ptgAdd' || |
|
|
|
66
|
|
|
|
|
|
|
|
66
|
|
|
|
|
|
|
|
66
|
|
|
|
|
|
|
|
66
|
|
|
|
|
|
|
|
33
|
|
|
|
|
|
|
|
33
|
|
|
|
|
|
|
|
33
|
|
|
|
|
|
|
|
33
|
|
|
|
|
|
|
|
33
|
|
|
|
|
|
|
|
33
|
|
|
|
|
406
|
|
|
|
|
|
|
$token eq 'ptgSub' || |
407
|
|
|
|
|
|
|
$token eq 'ptgConcat' || |
408
|
|
|
|
|
|
|
$token eq 'ptgMul' || |
409
|
|
|
|
|
|
|
$token eq 'ptgDiv' || |
410
|
|
|
|
|
|
|
$token eq 'ptgPower' || |
411
|
|
|
|
|
|
|
$token eq 'ptgEQ' || |
412
|
|
|
|
|
|
|
$token eq 'ptgNE' || |
413
|
|
|
|
|
|
|
$token eq 'ptgLE' || |
414
|
|
|
|
|
|
|
$token eq 'ptgGE' || |
415
|
|
|
|
|
|
|
$token eq 'ptgLT' || |
416
|
|
|
|
|
|
|
$token eq 'ptgGT') |
417
|
|
|
|
|
|
|
{ |
418
|
3
|
|
|
|
|
7
|
my $operand = shift @expression; |
419
|
3
|
|
|
|
|
8
|
push @stack, $operand; |
420
|
|
|
|
|
|
|
} |
421
|
|
|
|
|
|
|
|
422
|
716
|
|
|
|
|
5791
|
push @stack, $token; |
423
|
|
|
|
|
|
|
} |
424
|
|
|
|
|
|
|
|
425
|
|
|
|
|
|
|
# Recurse through the parse tree |
426
|
476
|
|
|
|
|
853
|
foreach my $token (@stack) { |
427
|
719
|
100
|
|
|
|
1409
|
if (ref($token)) { |
428
|
398
|
|
|
|
|
1222
|
push @tokens, $self->_reverse_tree(@$token); |
429
|
|
|
|
|
|
|
} |
430
|
|
|
|
|
|
|
else { |
431
|
321
|
|
|
|
|
734
|
push @tokens, $token; |
432
|
|
|
|
|
|
|
} |
433
|
|
|
|
|
|
|
} |
434
|
|
|
|
|
|
|
|
435
|
476
|
|
|
|
|
2032
|
return @tokens; |
436
|
|
|
|
|
|
|
} |
437
|
|
|
|
|
|
|
|
438
|
|
|
|
|
|
|
|
439
|
|
|
|
|
|
|
############################################################################### |
440
|
|
|
|
|
|
|
# |
441
|
|
|
|
|
|
|
# _check_volatile() |
442
|
|
|
|
|
|
|
# |
443
|
|
|
|
|
|
|
# Check if the formula contains a volatile function, i.e. a function that must |
444
|
|
|
|
|
|
|
# be recalculated each time a cell is updated. These formulas require a ptgAttr |
445
|
|
|
|
|
|
|
# with the volatile flag set as the first token in the parsed expression. |
446
|
|
|
|
|
|
|
# |
447
|
|
|
|
|
|
|
# Examples of volatile functions: RAND(), NOW(), TODAY() |
448
|
|
|
|
|
|
|
# |
449
|
|
|
|
|
|
|
sub _check_volatile { |
450
|
|
|
|
|
|
|
|
451
|
78
|
|
|
78
|
|
174
|
my $self = shift; |
452
|
78
|
|
|
|
|
316
|
my @tokens = @_; |
453
|
78
|
|
|
|
|
243
|
my $volatile = 0; |
454
|
|
|
|
|
|
|
|
455
|
78
|
|
|
|
|
331
|
for my $i (0..@tokens-1) { |
456
|
|
|
|
|
|
|
# If the next token is a function check if it is volatile. |
457
|
321
|
50
|
33
|
|
|
1085
|
if ($tokens[$i] =~ m/^_func/ and $functions{$tokens[$i+1]}[3]) { |
458
|
0
|
|
|
|
|
0
|
$volatile = 1; |
459
|
0
|
|
|
|
|
0
|
last; |
460
|
|
|
|
|
|
|
} |
461
|
|
|
|
|
|
|
} |
462
|
|
|
|
|
|
|
|
463
|
78
|
|
|
|
|
514
|
return $volatile; |
464
|
|
|
|
|
|
|
} |
465
|
|
|
|
|
|
|
|
466
|
|
|
|
|
|
|
|
467
|
|
|
|
|
|
|
############################################################################### |
468
|
|
|
|
|
|
|
# |
469
|
|
|
|
|
|
|
# _convert_volatile() |
470
|
|
|
|
|
|
|
# |
471
|
|
|
|
|
|
|
# Convert _vol to a ptgAttr tag formatted to indicate that the formula contains |
472
|
|
|
|
|
|
|
# a volatile function. See _check_volatile() |
473
|
|
|
|
|
|
|
# |
474
|
|
|
|
|
|
|
sub _convert_volatile { |
475
|
|
|
|
|
|
|
|
476
|
0
|
|
|
0
|
|
0
|
my $self = shift; |
477
|
|
|
|
|
|
|
|
478
|
|
|
|
|
|
|
# Set bitFattrSemi flag to indicate volatile function, "w" is set to zero. |
479
|
0
|
|
|
|
|
0
|
return pack("CCv", $ptg{ptgAttr}, 0x1, 0x0); |
480
|
|
|
|
|
|
|
} |
481
|
|
|
|
|
|
|
|
482
|
|
|
|
|
|
|
|
483
|
|
|
|
|
|
|
############################################################################### |
484
|
|
|
|
|
|
|
# |
485
|
|
|
|
|
|
|
# _convert_bool() |
486
|
|
|
|
|
|
|
# |
487
|
|
|
|
|
|
|
# Convert a boolean token to ptgBool |
488
|
|
|
|
|
|
|
# |
489
|
|
|
|
|
|
|
sub _convert_bool { |
490
|
|
|
|
|
|
|
|
491
|
0
|
|
|
0
|
|
0
|
my $self = shift; |
492
|
0
|
|
|
|
|
0
|
my $bool = shift; |
493
|
|
|
|
|
|
|
|
494
|
0
|
|
|
|
|
0
|
return pack("CC", $ptg{ptgBool}, $bool); |
495
|
|
|
|
|
|
|
} |
496
|
|
|
|
|
|
|
|
497
|
|
|
|
|
|
|
|
498
|
|
|
|
|
|
|
############################################################################### |
499
|
|
|
|
|
|
|
# |
500
|
|
|
|
|
|
|
# _convert_number() |
501
|
|
|
|
|
|
|
# |
502
|
|
|
|
|
|
|
# Convert a number token to ptgInt or ptgNum |
503
|
|
|
|
|
|
|
# |
504
|
|
|
|
|
|
|
sub _convert_number { |
505
|
|
|
|
|
|
|
|
506
|
134
|
|
|
134
|
|
493
|
my $self = shift; |
507
|
134
|
|
|
|
|
236
|
my $num = shift; |
508
|
|
|
|
|
|
|
|
509
|
|
|
|
|
|
|
# Integer in the range 0..2**16-1 |
510
|
134
|
100
|
66
|
|
|
1164
|
if (($num =~ /^\d+$/) && ($num <= 65535)) { |
511
|
124
|
|
|
|
|
957
|
return pack("Cv", $ptg{ptgInt}, $num); |
512
|
|
|
|
|
|
|
} |
513
|
|
|
|
|
|
|
else { # A float |
514
|
10
|
|
|
|
|
56
|
$num = pack("d", $num); |
515
|
10
|
50
|
|
|
|
60
|
$num = reverse $num if $self->{_byte_order}; |
516
|
10
|
|
|
|
|
66
|
return pack("C", $ptg{ptgNum}) . $num; |
517
|
|
|
|
|
|
|
} |
518
|
|
|
|
|
|
|
} |
519
|
|
|
|
|
|
|
|
520
|
|
|
|
|
|
|
|
521
|
|
|
|
|
|
|
############################################################################### |
522
|
|
|
|
|
|
|
# |
523
|
|
|
|
|
|
|
# _convert_string() |
524
|
|
|
|
|
|
|
# |
525
|
|
|
|
|
|
|
# Convert a string to a ptg Str. |
526
|
|
|
|
|
|
|
# |
527
|
|
|
|
|
|
|
sub _convert_string { |
528
|
|
|
|
|
|
|
|
529
|
8
|
|
|
8
|
|
16
|
my $self = shift; |
530
|
8
|
|
|
|
|
14
|
my $str = shift; |
531
|
8
|
|
|
|
|
12
|
my $encoding = 0; |
532
|
|
|
|
|
|
|
|
533
|
8
|
|
|
|
|
39
|
$str =~ s/^"//; # Remove leading " |
534
|
8
|
|
|
|
|
36
|
$str =~ s/"$//; # Remove trailing " |
535
|
8
|
|
|
|
|
18
|
$str =~ s/""/"/g; # Substitute Excel's escaped double quote "" for " |
536
|
|
|
|
|
|
|
|
537
|
8
|
|
|
|
|
15
|
my $length = length($str); |
538
|
|
|
|
|
|
|
|
539
|
|
|
|
|
|
|
# Handle utf8 strings in perl 5.8. |
540
|
8
|
50
|
|
|
|
27
|
if ($] >= 5.008) { |
541
|
8
|
|
|
|
|
61
|
require Encode; |
542
|
|
|
|
|
|
|
|
543
|
8
|
100
|
|
|
|
40
|
if (Encode::is_utf8($str)) { |
544
|
2
|
|
|
|
|
13
|
$str = Encode::encode("UTF-16LE", $str); |
545
|
2
|
|
|
|
|
4991
|
$encoding = 1; |
546
|
|
|
|
|
|
|
} |
547
|
|
|
|
|
|
|
} |
548
|
|
|
|
|
|
|
|
549
|
8
|
50
|
|
|
|
21
|
die "String in formula has more than 255 chars\n" if $length > 255; |
550
|
|
|
|
|
|
|
|
551
|
8
|
|
|
|
|
68
|
return pack("CCC", $ptg{ptgStr}, $length, $encoding) . $str; |
552
|
|
|
|
|
|
|
} |
553
|
|
|
|
|
|
|
|
554
|
|
|
|
|
|
|
|
555
|
|
|
|
|
|
|
############################################################################### |
556
|
|
|
|
|
|
|
# |
557
|
|
|
|
|
|
|
# _convert_ref2d() |
558
|
|
|
|
|
|
|
# |
559
|
|
|
|
|
|
|
# Convert an Excel reference such as A1, $B2, C$3 or $D$4 to a ptgRefV. |
560
|
|
|
|
|
|
|
# |
561
|
|
|
|
|
|
|
sub _convert_ref2d { |
562
|
|
|
|
|
|
|
|
563
|
4
|
|
|
4
|
|
6
|
my $self = shift; |
564
|
4
|
|
|
|
|
7
|
my $cell = shift; |
565
|
4
|
|
|
|
|
7
|
my $class = shift; |
566
|
4
|
|
|
|
|
5
|
my $ptgRef; |
567
|
|
|
|
|
|
|
|
568
|
|
|
|
|
|
|
# Convert the cell reference |
569
|
4
|
|
|
|
|
46
|
my ($row, $col) = $self->_cell_to_packed_rowcol($cell); |
570
|
|
|
|
|
|
|
|
571
|
|
|
|
|
|
|
# The ptg value depends on the class of the ptg. |
572
|
4
|
50
|
|
|
|
19
|
if ($class == 0) { |
|
|
50
|
|
|
|
|
|
|
|
0
|
|
|
|
|
|
573
|
0
|
|
|
|
|
0
|
$ptgRef = pack("C", $ptg{ptgRef}); |
574
|
|
|
|
|
|
|
} |
575
|
|
|
|
|
|
|
elsif ($class == 1) { |
576
|
4
|
|
|
|
|
15
|
$ptgRef = pack("C", $ptg{ptgRefV}); |
577
|
|
|
|
|
|
|
} |
578
|
|
|
|
|
|
|
elsif ($class == 2) { |
579
|
0
|
|
|
|
|
0
|
$ptgRef = pack("C", $ptg{ptgRefA}); |
580
|
|
|
|
|
|
|
} |
581
|
|
|
|
|
|
|
else{ |
582
|
0
|
|
|
|
|
0
|
die "Unknown function class in formula\n"; |
583
|
|
|
|
|
|
|
} |
584
|
|
|
|
|
|
|
|
585
|
4
|
|
|
|
|
18
|
return $ptgRef . $row . $col; |
586
|
|
|
|
|
|
|
} |
587
|
|
|
|
|
|
|
|
588
|
|
|
|
|
|
|
|
589
|
|
|
|
|
|
|
############################################################################### |
590
|
|
|
|
|
|
|
# |
591
|
|
|
|
|
|
|
# _convert_ref3d |
592
|
|
|
|
|
|
|
# |
593
|
|
|
|
|
|
|
# Convert an Excel 3d reference such as "Sheet1!A1" or "Sheet1:Sheet2!A1" to a |
594
|
|
|
|
|
|
|
# ptgRef3dV. |
595
|
|
|
|
|
|
|
# |
596
|
|
|
|
|
|
|
sub _convert_ref3d { |
597
|
|
|
|
|
|
|
|
598
|
3
|
|
|
3
|
|
8
|
my $self = shift; |
599
|
3
|
|
|
|
|
7
|
my $token = shift; |
600
|
3
|
|
|
|
|
4
|
my $class = shift; |
601
|
3
|
|
|
|
|
8
|
my $ptgRef; |
602
|
|
|
|
|
|
|
|
603
|
|
|
|
|
|
|
# Split the ref at the ! symbol |
604
|
3
|
|
|
|
|
29
|
my ($ext_ref, $cell) = split '!', $token; |
605
|
|
|
|
|
|
|
|
606
|
|
|
|
|
|
|
# Convert the external reference part |
607
|
3
|
|
|
|
|
14
|
$ext_ref = $self->_pack_ext_ref($ext_ref); |
608
|
|
|
|
|
|
|
|
609
|
|
|
|
|
|
|
# Convert the cell reference part |
610
|
3
|
|
|
|
|
14
|
my ($row, $col) = $self->_cell_to_packed_rowcol($cell); |
611
|
|
|
|
|
|
|
|
612
|
|
|
|
|
|
|
# The ptg value depends on the class of the ptg. |
613
|
3
|
50
|
|
|
|
15
|
if ($class == 0) { |
|
|
50
|
|
|
|
|
|
|
|
0
|
|
|
|
|
|
614
|
0
|
|
|
|
|
0
|
$ptgRef = pack("C", $ptg{ptgRef3d}); |
615
|
|
|
|
|
|
|
} |
616
|
|
|
|
|
|
|
elsif ($class == 1) { |
617
|
3
|
|
|
|
|
16
|
$ptgRef = pack("C", $ptg{ptgRef3dV}); |
618
|
|
|
|
|
|
|
} |
619
|
|
|
|
|
|
|
elsif ($class == 2) { |
620
|
0
|
|
|
|
|
0
|
$ptgRef = pack("C", $ptg{ptgRef3dA}); |
621
|
|
|
|
|
|
|
} |
622
|
|
|
|
|
|
|
else{ |
623
|
0
|
|
|
|
|
0
|
die "Unknown function class in formula\n"; |
624
|
|
|
|
|
|
|
} |
625
|
|
|
|
|
|
|
|
626
|
3
|
|
|
|
|
17
|
return $ptgRef . $ext_ref. $row . $col; |
627
|
|
|
|
|
|
|
} |
628
|
|
|
|
|
|
|
|
629
|
|
|
|
|
|
|
|
630
|
|
|
|
|
|
|
############################################################################### |
631
|
|
|
|
|
|
|
# |
632
|
|
|
|
|
|
|
# _convert_range2d() |
633
|
|
|
|
|
|
|
# |
634
|
|
|
|
|
|
|
# Convert an Excel range such as A1:D4 or A:D to a ptgRefV. |
635
|
|
|
|
|
|
|
# |
636
|
|
|
|
|
|
|
sub _convert_range2d { |
637
|
|
|
|
|
|
|
|
638
|
8
|
|
|
8
|
|
14
|
my $self = shift; |
639
|
8
|
|
|
|
|
16
|
my $range = shift; |
640
|
8
|
|
|
|
|
13
|
my $class = shift; |
641
|
8
|
|
|
|
|
14
|
my $ptgArea; |
642
|
|
|
|
|
|
|
|
643
|
|
|
|
|
|
|
# Split the range into 2 cell refs |
644
|
8
|
|
|
|
|
33
|
my ($cell1, $cell2) = split ':', $range; |
645
|
|
|
|
|
|
|
|
646
|
|
|
|
|
|
|
# A range such as A:D is equivalent to A1:D65536, so add rows as required |
647
|
8
|
50
|
|
|
|
61
|
$cell1 .= '1' if $cell1 !~ /\d/; |
648
|
8
|
50
|
|
|
|
33
|
$cell2 .= '65536' if $cell2 !~ /\d/; |
649
|
|
|
|
|
|
|
|
650
|
|
|
|
|
|
|
# Convert the cell references |
651
|
8
|
|
|
|
|
34
|
my ($row1, $col1) = $self->_cell_to_packed_rowcol($cell1); |
652
|
8
|
|
|
|
|
28
|
my ($row2, $col2) = $self->_cell_to_packed_rowcol($cell2); |
653
|
|
|
|
|
|
|
|
654
|
|
|
|
|
|
|
# The ptg value depends on the class of the ptg. |
655
|
8
|
100
|
|
|
|
31
|
if ($class == 0) { |
|
|
50
|
|
|
|
|
|
|
|
0
|
|
|
|
|
|
656
|
4
|
|
|
|
|
15
|
$ptgArea = pack("C", $ptg{ptgArea}); |
657
|
|
|
|
|
|
|
} |
658
|
|
|
|
|
|
|
elsif ($class == 1) { |
659
|
4
|
|
|
|
|
22
|
$ptgArea = pack("C", $ptg{ptgAreaV}); |
660
|
|
|
|
|
|
|
} |
661
|
|
|
|
|
|
|
elsif ($class == 2) { |
662
|
0
|
|
|
|
|
0
|
$ptgArea = pack("C", $ptg{ptgAreaA}); |
663
|
|
|
|
|
|
|
} |
664
|
|
|
|
|
|
|
else{ |
665
|
0
|
|
|
|
|
0
|
die "Unknown function class in formula\n"; |
666
|
|
|
|
|
|
|
} |
667
|
|
|
|
|
|
|
|
668
|
8
|
|
|
|
|
50
|
return $ptgArea . $row1 . $row2 . $col1. $col2; |
669
|
|
|
|
|
|
|
} |
670
|
|
|
|
|
|
|
|
671
|
|
|
|
|
|
|
|
672
|
|
|
|
|
|
|
############################################################################### |
673
|
|
|
|
|
|
|
# |
674
|
|
|
|
|
|
|
# _convert_range3d |
675
|
|
|
|
|
|
|
# |
676
|
|
|
|
|
|
|
# Convert an Excel 3d range such as "Sheet1!A1:D4" or "Sheet1:Sheet2!A1:D4" to |
677
|
|
|
|
|
|
|
# a ptgArea3dV. |
678
|
|
|
|
|
|
|
# |
679
|
|
|
|
|
|
|
sub _convert_range3d { |
680
|
|
|
|
|
|
|
|
681
|
0
|
|
|
0
|
|
0
|
my $self = shift; |
682
|
0
|
|
|
|
|
0
|
my $token = shift; |
683
|
0
|
|
|
|
|
0
|
my $class = shift; |
684
|
0
|
|
|
|
|
0
|
my $ptgArea; |
685
|
|
|
|
|
|
|
|
686
|
|
|
|
|
|
|
# Split the ref at the ! symbol |
687
|
0
|
|
|
|
|
0
|
my ($ext_ref, $range) = split '!', $token; |
688
|
|
|
|
|
|
|
|
689
|
|
|
|
|
|
|
# Convert the external reference part |
690
|
0
|
|
|
|
|
0
|
$ext_ref = $self->_pack_ext_ref($ext_ref); |
691
|
|
|
|
|
|
|
|
692
|
|
|
|
|
|
|
# Split the range into 2 cell refs |
693
|
0
|
|
|
|
|
0
|
my ($cell1, $cell2) = split ':', $range; |
694
|
|
|
|
|
|
|
|
695
|
|
|
|
|
|
|
# A range such as A:D is equivalent to A1:D65536, so add rows as required |
696
|
0
|
0
|
|
|
|
0
|
$cell1 .= '1' if $cell1 !~ /\d/; |
697
|
0
|
0
|
|
|
|
0
|
$cell2 .= '65536' if $cell2 !~ /\d/; |
698
|
|
|
|
|
|
|
|
699
|
|
|
|
|
|
|
# Convert the cell references |
700
|
0
|
|
|
|
|
0
|
my ($row1, $col1) = $self->_cell_to_packed_rowcol($cell1); |
701
|
0
|
|
|
|
|
0
|
my ($row2, $col2) = $self->_cell_to_packed_rowcol($cell2); |
702
|
|
|
|
|
|
|
|
703
|
|
|
|
|
|
|
# The ptg value depends on the class of the ptg. |
704
|
0
|
0
|
|
|
|
0
|
if ($class == 0) { |
|
|
0
|
|
|
|
|
|
|
|
0
|
|
|
|
|
|
705
|
0
|
|
|
|
|
0
|
$ptgArea = pack("C", $ptg{ptgArea3d}); |
706
|
|
|
|
|
|
|
} |
707
|
|
|
|
|
|
|
elsif ($class == 1) { |
708
|
0
|
|
|
|
|
0
|
$ptgArea = pack("C", $ptg{ptgArea3dV}); |
709
|
|
|
|
|
|
|
} |
710
|
|
|
|
|
|
|
elsif ($class == 2) { |
711
|
0
|
|
|
|
|
0
|
$ptgArea = pack("C", $ptg{ptgArea3dA}); |
712
|
|
|
|
|
|
|
} |
713
|
|
|
|
|
|
|
else{ |
714
|
0
|
|
|
|
|
0
|
die "Unknown function class in formula\n"; |
715
|
|
|
|
|
|
|
} |
716
|
|
|
|
|
|
|
|
717
|
0
|
|
|
|
|
0
|
return $ptgArea . $ext_ref . $row1 . $row2 . $col1. $col2; |
718
|
|
|
|
|
|
|
} |
719
|
|
|
|
|
|
|
|
720
|
|
|
|
|
|
|
|
721
|
|
|
|
|
|
|
############################################################################### |
722
|
|
|
|
|
|
|
# |
723
|
|
|
|
|
|
|
# _pack_ext_ref() |
724
|
|
|
|
|
|
|
# |
725
|
|
|
|
|
|
|
# Convert the sheet name part of an external reference, for example "Sheet1" or |
726
|
|
|
|
|
|
|
# "Sheet1:Sheet2", to a packed structure. |
727
|
|
|
|
|
|
|
# |
728
|
|
|
|
|
|
|
sub _pack_ext_ref { |
729
|
|
|
|
|
|
|
|
730
|
3
|
|
|
3
|
|
6
|
my $self = shift; |
731
|
3
|
|
|
|
|
4
|
my $ext_ref = shift; |
732
|
3
|
|
|
|
|
5
|
my $sheet1; |
733
|
|
|
|
|
|
|
my $sheet2; |
734
|
|
|
|
|
|
|
|
735
|
3
|
|
|
|
|
12
|
$ext_ref =~ s/^'//; # Remove leading ' if any. |
736
|
3
|
|
|
|
|
14
|
$ext_ref =~ s/'$//; # Remove trailing ' if any. |
737
|
|
|
|
|
|
|
|
738
|
|
|
|
|
|
|
# Check if there is a sheet range eg., Sheet1:Sheet2. |
739
|
3
|
50
|
|
|
|
13
|
if ($ext_ref =~ /:/) { |
740
|
0
|
|
|
|
|
0
|
($sheet1, $sheet2) = split ':', $ext_ref; |
741
|
|
|
|
|
|
|
|
742
|
0
|
|
|
|
|
0
|
$sheet1 = $self->_get_sheet_index($sheet1); |
743
|
0
|
|
|
|
|
0
|
$sheet2 = $self->_get_sheet_index($sheet2); |
744
|
|
|
|
|
|
|
|
745
|
|
|
|
|
|
|
# Reverse max and min sheet numbers if necessary |
746
|
0
|
0
|
|
|
|
0
|
if ($sheet1 > $sheet2) { |
747
|
0
|
|
|
|
|
0
|
($sheet1, $sheet2) = ($sheet2, $sheet1); |
748
|
|
|
|
|
|
|
} |
749
|
|
|
|
|
|
|
} |
750
|
|
|
|
|
|
|
else { |
751
|
|
|
|
|
|
|
# Single sheet name only. |
752
|
3
|
|
|
|
|
9
|
($sheet1, $sheet2) = ($ext_ref, $ext_ref); |
753
|
|
|
|
|
|
|
|
754
|
3
|
|
|
|
|
16
|
$sheet1 = $self->_get_sheet_index($sheet1); |
755
|
3
|
|
|
|
|
8
|
$sheet2 = $sheet1; |
756
|
|
|
|
|
|
|
} |
757
|
|
|
|
|
|
|
|
758
|
3
|
|
|
|
|
12
|
my $key = "$sheet1:$sheet2"; |
759
|
3
|
|
|
|
|
4
|
my $index; |
760
|
|
|
|
|
|
|
|
761
|
3
|
50
|
|
|
|
11
|
if (exists $self->{_ext_refs}->{$key}) { |
762
|
0
|
|
|
|
|
0
|
$index = $self->{_ext_refs}->{$key}; |
763
|
|
|
|
|
|
|
} |
764
|
|
|
|
|
|
|
else { |
765
|
3
|
|
|
|
|
9
|
$index = $self->{_ext_ref_count}; |
766
|
3
|
|
|
|
|
9
|
$self->{_ext_refs}->{$key} = $index; |
767
|
3
|
|
|
|
|
6
|
$self->{_ext_ref_count}++; |
768
|
|
|
|
|
|
|
} |
769
|
|
|
|
|
|
|
|
770
|
3
|
|
|
|
|
19
|
return pack("v",$index); |
771
|
|
|
|
|
|
|
} |
772
|
|
|
|
|
|
|
|
773
|
|
|
|
|
|
|
|
774
|
|
|
|
|
|
|
############################################################################### |
775
|
|
|
|
|
|
|
# |
776
|
|
|
|
|
|
|
# _get_sheet_index() |
777
|
|
|
|
|
|
|
# |
778
|
|
|
|
|
|
|
# Look up the index that corresponds to an external sheet name. The hash of |
779
|
|
|
|
|
|
|
# sheet names is updated by the add_worksheet() method of the Workbook class. |
780
|
|
|
|
|
|
|
# |
781
|
|
|
|
|
|
|
sub _get_sheet_index { |
782
|
|
|
|
|
|
|
|
783
|
3
|
|
|
3
|
|
5
|
my $self = shift; |
784
|
3
|
|
|
|
|
7
|
my $sheet_name = shift; |
785
|
|
|
|
|
|
|
|
786
|
|
|
|
|
|
|
# Handle utf8 sheetnames in perl 5.8. |
787
|
3
|
50
|
|
|
|
14
|
if ($] >= 5.008) { |
788
|
3
|
|
|
|
|
30
|
require Encode; |
789
|
|
|
|
|
|
|
|
790
|
3
|
50
|
|
|
|
21
|
if (Encode::is_utf8($sheet_name)) { |
791
|
0
|
|
|
|
|
0
|
$sheet_name = Encode::encode("UTF-16BE", $sheet_name); |
792
|
|
|
|
|
|
|
} |
793
|
|
|
|
|
|
|
} |
794
|
|
|
|
|
|
|
|
795
|
|
|
|
|
|
|
|
796
|
3
|
50
|
|
|
|
23
|
if (not exists $self->{_ext_sheets}->{$sheet_name}) { |
797
|
0
|
|
|
|
|
0
|
die "Unknown sheet name $sheet_name in formula\n"; |
798
|
|
|
|
|
|
|
} |
799
|
|
|
|
|
|
|
else { |
800
|
3
|
|
|
|
|
11
|
return $self->{_ext_sheets}->{$sheet_name}; |
801
|
|
|
|
|
|
|
} |
802
|
|
|
|
|
|
|
} |
803
|
|
|
|
|
|
|
|
804
|
|
|
|
|
|
|
|
805
|
|
|
|
|
|
|
############################################################################### |
806
|
|
|
|
|
|
|
# |
807
|
|
|
|
|
|
|
# set_ext_sheets() |
808
|
|
|
|
|
|
|
# |
809
|
|
|
|
|
|
|
# This semi-public method is used to update the hash of sheet names. It is |
810
|
|
|
|
|
|
|
# updated by the add_worksheet() method of the Workbook class. |
811
|
|
|
|
|
|
|
# |
812
|
|
|
|
|
|
|
sub set_ext_sheets { |
813
|
|
|
|
|
|
|
|
814
|
133
|
|
|
133
|
0
|
220
|
my $self = shift; |
815
|
133
|
|
|
|
|
198
|
my $worksheet = shift; |
816
|
133
|
|
|
|
|
185
|
my $index = shift; |
817
|
|
|
|
|
|
|
|
818
|
|
|
|
|
|
|
# The _ext_sheets hash is used to translate between worksheet names |
819
|
|
|
|
|
|
|
# and their index |
820
|
133
|
|
|
|
|
723
|
$self->{_ext_sheets}->{$worksheet} = $index; |
821
|
|
|
|
|
|
|
|
822
|
|
|
|
|
|
|
} |
823
|
|
|
|
|
|
|
|
824
|
|
|
|
|
|
|
|
825
|
|
|
|
|
|
|
############################################################################### |
826
|
|
|
|
|
|
|
# |
827
|
|
|
|
|
|
|
# get_ext_sheets() |
828
|
|
|
|
|
|
|
# |
829
|
|
|
|
|
|
|
# This semi-public method is used to get the worksheet references that were |
830
|
|
|
|
|
|
|
# used in formulas for inclusion in the EXTERNSHEET Workbook record. |
831
|
|
|
|
|
|
|
# |
832
|
|
|
|
|
|
|
sub get_ext_sheets { |
833
|
|
|
|
|
|
|
|
834
|
78
|
|
|
78
|
0
|
175
|
my $self = shift; |
835
|
|
|
|
|
|
|
|
836
|
78
|
|
|
|
|
324
|
return %{$self->{_ext_refs}}; |
|
78
|
|
|
|
|
464
|
|
837
|
|
|
|
|
|
|
} |
838
|
|
|
|
|
|
|
|
839
|
|
|
|
|
|
|
|
840
|
|
|
|
|
|
|
############################################################################### |
841
|
|
|
|
|
|
|
# |
842
|
|
|
|
|
|
|
# get_ext_ref_count() |
843
|
|
|
|
|
|
|
# |
844
|
|
|
|
|
|
|
# This semi-public method is used to update the hash of sheet names. It is |
845
|
|
|
|
|
|
|
# updated by the add_worksheet() method of the Workbook class. |
846
|
|
|
|
|
|
|
# |
847
|
|
|
|
|
|
|
sub get_ext_ref_count { |
848
|
|
|
|
|
|
|
|
849
|
0
|
|
|
0
|
0
|
0
|
my $self = shift; |
850
|
|
|
|
|
|
|
|
851
|
0
|
|
|
|
|
0
|
return $self->{_ext_ref_count}; |
852
|
|
|
|
|
|
|
} |
853
|
|
|
|
|
|
|
|
854
|
|
|
|
|
|
|
|
855
|
|
|
|
|
|
|
############################################################################### |
856
|
|
|
|
|
|
|
# |
857
|
|
|
|
|
|
|
# _get_name_index() |
858
|
|
|
|
|
|
|
# |
859
|
|
|
|
|
|
|
# Look up the index that corresponds to an external defined name. The hash of |
860
|
|
|
|
|
|
|
# defined names is updated by the define_name() method in the Workbook class. |
861
|
|
|
|
|
|
|
# |
862
|
|
|
|
|
|
|
sub _get_name_index { |
863
|
|
|
|
|
|
|
|
864
|
0
|
|
|
0
|
|
0
|
my $self = shift; |
865
|
0
|
|
|
|
|
0
|
my $name = shift; |
866
|
|
|
|
|
|
|
|
867
|
0
|
0
|
|
|
|
0
|
if (not exists $self->{_ext_names}->{$name}) { |
868
|
0
|
|
|
|
|
0
|
die "Unknown defined name $name in formula\n"; |
869
|
|
|
|
|
|
|
} |
870
|
|
|
|
|
|
|
else { |
871
|
0
|
|
|
|
|
0
|
return $self->{_ext_names}->{$name}; |
872
|
|
|
|
|
|
|
} |
873
|
|
|
|
|
|
|
} |
874
|
|
|
|
|
|
|
|
875
|
|
|
|
|
|
|
|
876
|
|
|
|
|
|
|
############################################################################### |
877
|
|
|
|
|
|
|
# |
878
|
|
|
|
|
|
|
# set_ext_name() |
879
|
|
|
|
|
|
|
# |
880
|
|
|
|
|
|
|
# This semi-public method is used to update the hash of defined names. |
881
|
|
|
|
|
|
|
# |
882
|
|
|
|
|
|
|
sub set_ext_name { |
883
|
|
|
|
|
|
|
|
884
|
0
|
|
|
0
|
0
|
0
|
my $self = shift; |
885
|
0
|
|
|
|
|
0
|
my $name = shift; |
886
|
0
|
|
|
|
|
0
|
my $index = shift; |
887
|
|
|
|
|
|
|
|
888
|
0
|
|
|
|
|
0
|
$self->{_ext_names}->{$name} = $index; |
889
|
|
|
|
|
|
|
} |
890
|
|
|
|
|
|
|
|
891
|
|
|
|
|
|
|
|
892
|
|
|
|
|
|
|
############################################################################### |
893
|
|
|
|
|
|
|
# |
894
|
|
|
|
|
|
|
# _convert_function() |
895
|
|
|
|
|
|
|
# |
896
|
|
|
|
|
|
|
# Convert a function to a ptgFuncV or ptgFuncVarV depending on the number of |
897
|
|
|
|
|
|
|
# args that it takes. |
898
|
|
|
|
|
|
|
# |
899
|
|
|
|
|
|
|
sub _convert_function { |
900
|
|
|
|
|
|
|
|
901
|
0
|
|
|
0
|
|
0
|
my $self = shift; |
902
|
0
|
|
|
|
|
0
|
my $token = shift; |
903
|
0
|
|
|
|
|
0
|
my $num_args = shift; |
904
|
0
|
|
|
|
|
0
|
my $non_var = shift; |
905
|
|
|
|
|
|
|
|
906
|
0
|
0
|
|
|
|
0
|
die "Unknown function $token() in formula\n" |
907
|
|
|
|
|
|
|
unless defined $functions{$token}[0]; |
908
|
|
|
|
|
|
|
|
909
|
0
|
|
|
|
|
0
|
my $args = $functions{$token}[1]; |
910
|
|
|
|
|
|
|
|
911
|
|
|
|
|
|
|
# Fixed number of args eg. TIME($i,$j,$k). |
912
|
0
|
0
|
|
|
|
0
|
if ($args >= 0) { |
913
|
|
|
|
|
|
|
# Check that the number of args is valid. |
914
|
0
|
0
|
|
|
|
0
|
if ($args != $num_args) { |
915
|
0
|
|
|
|
|
0
|
die "Incorrect number of arguments for $token() in formula\n"; |
916
|
|
|
|
|
|
|
} |
917
|
|
|
|
|
|
|
else { |
918
|
0
|
0
|
|
|
|
0
|
if ($non_var) { |
919
|
0
|
|
|
|
|
0
|
return pack("Cv", $ptg{ptgFunc}, $functions{$token}[0]); |
920
|
|
|
|
|
|
|
} |
921
|
|
|
|
|
|
|
else { |
922
|
0
|
|
|
|
|
0
|
return pack("Cv", $ptg{ptgFuncV}, $functions{$token}[0]); |
923
|
|
|
|
|
|
|
} |
924
|
|
|
|
|
|
|
} |
925
|
|
|
|
|
|
|
} |
926
|
|
|
|
|
|
|
|
927
|
|
|
|
|
|
|
# Variable number of args eg. SUM($i,$j,$k, ..). |
928
|
0
|
0
|
|
|
|
0
|
if ($args == -1) { |
929
|
0
|
0
|
|
|
|
0
|
if ($non_var) { |
930
|
0
|
|
|
|
|
0
|
return pack "CCv", $ptg{ptgFuncVar}, |
931
|
|
|
|
|
|
|
$num_args, $functions{$token}[0]; |
932
|
|
|
|
|
|
|
} |
933
|
|
|
|
|
|
|
else { |
934
|
0
|
|
|
|
|
0
|
return pack "CCv", $ptg{ptgFuncVarV}, |
935
|
|
|
|
|
|
|
$num_args, $functions{$token}[0]; |
936
|
|
|
|
|
|
|
} |
937
|
|
|
|
|
|
|
} |
938
|
|
|
|
|
|
|
} |
939
|
|
|
|
|
|
|
|
940
|
|
|
|
|
|
|
|
941
|
|
|
|
|
|
|
############################################################################### |
942
|
|
|
|
|
|
|
# |
943
|
|
|
|
|
|
|
# _convert_name() |
944
|
|
|
|
|
|
|
# |
945
|
|
|
|
|
|
|
# Convert a symbolic name into a name reference. |
946
|
|
|
|
|
|
|
# |
947
|
|
|
|
|
|
|
sub _convert_name { |
948
|
|
|
|
|
|
|
|
949
|
0
|
|
|
0
|
|
0
|
my $self = shift; |
950
|
0
|
|
|
|
|
0
|
my $name = shift; |
951
|
0
|
|
|
|
|
0
|
my $class = shift; |
952
|
|
|
|
|
|
|
|
953
|
0
|
|
|
|
|
0
|
my $ptgName; |
954
|
|
|
|
|
|
|
|
955
|
0
|
|
|
|
|
0
|
my $name_index = $self->_get_name_index($name); |
956
|
|
|
|
|
|
|
|
957
|
|
|
|
|
|
|
# The ptg value depends on the class of the ptg. |
958
|
0
|
0
|
|
|
|
0
|
if ($class == 0) { |
|
|
0
|
|
|
|
|
|
|
|
0
|
|
|
|
|
|
959
|
0
|
|
|
|
|
0
|
$ptgName = $ptg{ptgName}; |
960
|
|
|
|
|
|
|
} |
961
|
|
|
|
|
|
|
elsif ($class == 1) { |
962
|
0
|
|
|
|
|
0
|
$ptgName = $ptg{ptgNameV}; |
963
|
|
|
|
|
|
|
} |
964
|
|
|
|
|
|
|
elsif ($class == 2) { |
965
|
0
|
|
|
|
|
0
|
$ptgName = $ptg{ptgNameA}; |
966
|
|
|
|
|
|
|
} |
967
|
|
|
|
|
|
|
|
968
|
|
|
|
|
|
|
|
969
|
0
|
|
|
|
|
0
|
return pack 'CV', $ptgName, $name_index; |
970
|
|
|
|
|
|
|
} |
971
|
|
|
|
|
|
|
|
972
|
|
|
|
|
|
|
|
973
|
|
|
|
|
|
|
############################################################################### |
974
|
|
|
|
|
|
|
# |
975
|
|
|
|
|
|
|
# _cell_to_rowcol($cell_ref) |
976
|
|
|
|
|
|
|
# |
977
|
|
|
|
|
|
|
# Convert an Excel cell reference such as A1 or $B2 or C$3 or $D$4 to a zero |
978
|
|
|
|
|
|
|
# indexed row and column number. Also returns two boolean values to indicate |
979
|
|
|
|
|
|
|
# whether the row or column are relative references. |
980
|
|
|
|
|
|
|
# TODO use function in Utility.pm |
981
|
|
|
|
|
|
|
# |
982
|
|
|
|
|
|
|
sub _cell_to_rowcol { |
983
|
|
|
|
|
|
|
|
984
|
23
|
|
|
23
|
|
46
|
my $self = shift; |
985
|
23
|
|
|
|
|
37
|
my $cell = shift; |
986
|
|
|
|
|
|
|
|
987
|
23
|
|
|
|
|
100
|
$cell =~ /(\$?)([A-I]?[A-Z])(\$?)(\d+)/; |
988
|
|
|
|
|
|
|
|
989
|
23
|
100
|
|
|
|
101
|
my $col_rel = $1 eq "" ? 1 : 0; |
990
|
23
|
|
|
|
|
52
|
my $col = $2; |
991
|
23
|
100
|
|
|
|
73
|
my $row_rel = $3 eq "" ? 1 : 0; |
992
|
23
|
|
|
|
|
56
|
my $row = $4; |
993
|
|
|
|
|
|
|
|
994
|
|
|
|
|
|
|
# Convert base26 column string to a number. |
995
|
|
|
|
|
|
|
# All your Base are belong to us. |
996
|
23
|
|
|
|
|
74
|
my @chars = split //, $col; |
997
|
23
|
|
|
|
|
37
|
my $expn = 0; |
998
|
23
|
|
|
|
|
37
|
$col = 0; |
999
|
|
|
|
|
|
|
|
1000
|
23
|
|
|
|
|
61
|
while (@chars) { |
1001
|
23
|
|
|
|
|
47
|
my $char = pop(@chars); # LS char first |
1002
|
23
|
|
|
|
|
69
|
$col += (ord($char) - ord('A') + 1) * (26**$expn); |
1003
|
23
|
|
|
|
|
73
|
$expn++; |
1004
|
|
|
|
|
|
|
} |
1005
|
|
|
|
|
|
|
|
1006
|
|
|
|
|
|
|
# Convert 1-index to zero-index |
1007
|
23
|
|
|
|
|
53
|
$row--; |
1008
|
23
|
|
|
|
|
42
|
$col--; |
1009
|
|
|
|
|
|
|
|
1010
|
23
|
|
|
|
|
83
|
return $row, $col, $row_rel, $col_rel; |
1011
|
|
|
|
|
|
|
} |
1012
|
|
|
|
|
|
|
|
1013
|
|
|
|
|
|
|
|
1014
|
|
|
|
|
|
|
############################################################################### |
1015
|
|
|
|
|
|
|
# |
1016
|
|
|
|
|
|
|
# _cell_to_packed_rowcol($row, $col, $row_rel, $col_rel) |
1017
|
|
|
|
|
|
|
# |
1018
|
|
|
|
|
|
|
# pack() row and column into the required 3 byte format. |
1019
|
|
|
|
|
|
|
# |
1020
|
|
|
|
|
|
|
sub _cell_to_packed_rowcol { |
1021
|
|
|
|
|
|
|
|
1022
|
32
|
|
|
32
|
|
326
|
use integer; # Avoid << shift bug in Perl 5.6.0 on HP-UX |
|
32
|
|
|
|
|
73
|
|
|
32
|
|
|
|
|
273
|
|
1023
|
|
|
|
|
|
|
|
1024
|
23
|
|
|
23
|
|
40
|
my $self = shift; |
1025
|
23
|
|
|
|
|
44
|
my $cell = shift; |
1026
|
|
|
|
|
|
|
|
1027
|
23
|
|
|
|
|
72
|
my ($row, $col, $row_rel, $col_rel) = $self->_cell_to_rowcol($cell); |
1028
|
|
|
|
|
|
|
|
1029
|
23
|
50
|
|
|
|
69
|
die "Column $cell greater than IV in formula\n" if $col >= 256; |
1030
|
23
|
50
|
|
|
|
76
|
die "Row $cell greater than 65536 in formula\n" if $row >= 65536; |
1031
|
|
|
|
|
|
|
|
1032
|
|
|
|
|
|
|
# Set the high bits to indicate if row or col are relative. |
1033
|
23
|
|
|
|
|
42
|
$col |= $col_rel << 14; |
1034
|
23
|
|
|
|
|
33
|
$col |= $row_rel << 15; |
1035
|
|
|
|
|
|
|
|
1036
|
23
|
|
|
|
|
77
|
$row = pack('v', $row); |
1037
|
23
|
|
|
|
|
44
|
$col = pack('v', $col); |
1038
|
|
|
|
|
|
|
|
1039
|
23
|
|
|
|
|
74
|
return ($row, $col); |
1040
|
|
|
|
|
|
|
} |
1041
|
|
|
|
|
|
|
|
1042
|
|
|
|
|
|
|
|
1043
|
|
|
|
|
|
|
############################################################################### |
1044
|
|
|
|
|
|
|
# |
1045
|
|
|
|
|
|
|
# _initialize_hashes() |
1046
|
|
|
|
|
|
|
# |
1047
|
|
|
|
|
|
|
sub _initialize_hashes { |
1048
|
|
|
|
|
|
|
|
1049
|
|
|
|
|
|
|
# The Excel ptg indices |
1050
|
4
|
|
|
4
|
|
329
|
%ptg = ( |
1051
|
|
|
|
|
|
|
'ptgExp' => 0x01, |
1052
|
|
|
|
|
|
|
'ptgTbl' => 0x02, |
1053
|
|
|
|
|
|
|
'ptgAdd' => 0x03, |
1054
|
|
|
|
|
|
|
'ptgSub' => 0x04, |
1055
|
|
|
|
|
|
|
'ptgMul' => 0x05, |
1056
|
|
|
|
|
|
|
'ptgDiv' => 0x06, |
1057
|
|
|
|
|
|
|
'ptgPower' => 0x07, |
1058
|
|
|
|
|
|
|
'ptgConcat' => 0x08, |
1059
|
|
|
|
|
|
|
'ptgLT' => 0x09, |
1060
|
|
|
|
|
|
|
'ptgLE' => 0x0A, |
1061
|
|
|
|
|
|
|
'ptgEQ' => 0x0B, |
1062
|
|
|
|
|
|
|
'ptgGE' => 0x0C, |
1063
|
|
|
|
|
|
|
'ptgGT' => 0x0D, |
1064
|
|
|
|
|
|
|
'ptgNE' => 0x0E, |
1065
|
|
|
|
|
|
|
'ptgIsect' => 0x0F, |
1066
|
|
|
|
|
|
|
'ptgUnion' => 0x10, |
1067
|
|
|
|
|
|
|
'ptgRange' => 0x11, |
1068
|
|
|
|
|
|
|
'ptgUplus' => 0x12, |
1069
|
|
|
|
|
|
|
'ptgUminus' => 0x13, |
1070
|
|
|
|
|
|
|
'ptgPercent' => 0x14, |
1071
|
|
|
|
|
|
|
'ptgParen' => 0x15, |
1072
|
|
|
|
|
|
|
'ptgMissArg' => 0x16, |
1073
|
|
|
|
|
|
|
'ptgStr' => 0x17, |
1074
|
|
|
|
|
|
|
'ptgAttr' => 0x19, |
1075
|
|
|
|
|
|
|
'ptgSheet' => 0x1A, |
1076
|
|
|
|
|
|
|
'ptgEndSheet' => 0x1B, |
1077
|
|
|
|
|
|
|
'ptgErr' => 0x1C, |
1078
|
|
|
|
|
|
|
'ptgBool' => 0x1D, |
1079
|
|
|
|
|
|
|
'ptgInt' => 0x1E, |
1080
|
|
|
|
|
|
|
'ptgNum' => 0x1F, |
1081
|
|
|
|
|
|
|
'ptgArray' => 0x20, |
1082
|
|
|
|
|
|
|
'ptgFunc' => 0x21, |
1083
|
|
|
|
|
|
|
'ptgFuncVar' => 0x22, |
1084
|
|
|
|
|
|
|
'ptgName' => 0x23, |
1085
|
|
|
|
|
|
|
'ptgRef' => 0x24, |
1086
|
|
|
|
|
|
|
'ptgArea' => 0x25, |
1087
|
|
|
|
|
|
|
'ptgMemArea' => 0x26, |
1088
|
|
|
|
|
|
|
'ptgMemErr' => 0x27, |
1089
|
|
|
|
|
|
|
'ptgMemNoMem' => 0x28, |
1090
|
|
|
|
|
|
|
'ptgMemFunc' => 0x29, |
1091
|
|
|
|
|
|
|
'ptgRefErr' => 0x2A, |
1092
|
|
|
|
|
|
|
'ptgAreaErr' => 0x2B, |
1093
|
|
|
|
|
|
|
'ptgRefN' => 0x2C, |
1094
|
|
|
|
|
|
|
'ptgAreaN' => 0x2D, |
1095
|
|
|
|
|
|
|
'ptgMemAreaN' => 0x2E, |
1096
|
|
|
|
|
|
|
'ptgMemNoMemN' => 0x2F, |
1097
|
|
|
|
|
|
|
'ptgNameX' => 0x39, |
1098
|
|
|
|
|
|
|
'ptgRef3d' => 0x3A, |
1099
|
|
|
|
|
|
|
'ptgArea3d' => 0x3B, |
1100
|
|
|
|
|
|
|
'ptgRefErr3d' => 0x3C, |
1101
|
|
|
|
|
|
|
'ptgAreaErr3d' => 0x3D, |
1102
|
|
|
|
|
|
|
'ptgArrayV' => 0x40, |
1103
|
|
|
|
|
|
|
'ptgFuncV' => 0x41, |
1104
|
|
|
|
|
|
|
'ptgFuncVarV' => 0x42, |
1105
|
|
|
|
|
|
|
'ptgNameV' => 0x43, |
1106
|
|
|
|
|
|
|
'ptgRefV' => 0x44, |
1107
|
|
|
|
|
|
|
'ptgAreaV' => 0x45, |
1108
|
|
|
|
|
|
|
'ptgMemAreaV' => 0x46, |
1109
|
|
|
|
|
|
|
'ptgMemErrV' => 0x47, |
1110
|
|
|
|
|
|
|
'ptgMemNoMemV' => 0x48, |
1111
|
|
|
|
|
|
|
'ptgMemFuncV' => 0x49, |
1112
|
|
|
|
|
|
|
'ptgRefErrV' => 0x4A, |
1113
|
|
|
|
|
|
|
'ptgAreaErrV' => 0x4B, |
1114
|
|
|
|
|
|
|
'ptgRefNV' => 0x4C, |
1115
|
|
|
|
|
|
|
'ptgAreaNV' => 0x4D, |
1116
|
|
|
|
|
|
|
'ptgMemAreaNV' => 0x4E, |
1117
|
|
|
|
|
|
|
'ptgMemNoMemN' => 0x4F, |
1118
|
|
|
|
|
|
|
'ptgFuncCEV' => 0x58, |
1119
|
|
|
|
|
|
|
'ptgNameXV' => 0x59, |
1120
|
|
|
|
|
|
|
'ptgRef3dV' => 0x5A, |
1121
|
|
|
|
|
|
|
'ptgArea3dV' => 0x5B, |
1122
|
|
|
|
|
|
|
'ptgRefErr3dV' => 0x5C, |
1123
|
|
|
|
|
|
|
'ptgAreaErr3d' => 0x5D, |
1124
|
|
|
|
|
|
|
'ptgArrayA' => 0x60, |
1125
|
|
|
|
|
|
|
'ptgFuncA' => 0x61, |
1126
|
|
|
|
|
|
|
'ptgFuncVarA' => 0x62, |
1127
|
|
|
|
|
|
|
'ptgNameA' => 0x63, |
1128
|
|
|
|
|
|
|
'ptgRefA' => 0x64, |
1129
|
|
|
|
|
|
|
'ptgAreaA' => 0x65, |
1130
|
|
|
|
|
|
|
'ptgMemAreaA' => 0x66, |
1131
|
|
|
|
|
|
|
'ptgMemErrA' => 0x67, |
1132
|
|
|
|
|
|
|
'ptgMemNoMemA' => 0x68, |
1133
|
|
|
|
|
|
|
'ptgMemFuncA' => 0x69, |
1134
|
|
|
|
|
|
|
'ptgRefErrA' => 0x6A, |
1135
|
|
|
|
|
|
|
'ptgAreaErrA' => 0x6B, |
1136
|
|
|
|
|
|
|
'ptgRefNA' => 0x6C, |
1137
|
|
|
|
|
|
|
'ptgAreaNA' => 0x6D, |
1138
|
|
|
|
|
|
|
'ptgMemAreaNA' => 0x6E, |
1139
|
|
|
|
|
|
|
'ptgMemNoMemN' => 0x6F, |
1140
|
|
|
|
|
|
|
'ptgFuncCEA' => 0x78, |
1141
|
|
|
|
|
|
|
'ptgNameXA' => 0x79, |
1142
|
|
|
|
|
|
|
'ptgRef3dA' => 0x7A, |
1143
|
|
|
|
|
|
|
'ptgArea3dA' => 0x7B, |
1144
|
|
|
|
|
|
|
'ptgRefErr3dA' => 0x7C, |
1145
|
|
|
|
|
|
|
'ptgAreaErr3d' => 0x7D, |
1146
|
|
|
|
|
|
|
); |
1147
|
|
|
|
|
|
|
|
1148
|
|
|
|
|
|
|
# Thanks to Michael Meeks and Gnumeric for the initial arg values. |
1149
|
|
|
|
|
|
|
# |
1150
|
|
|
|
|
|
|
# The following hash was generated by "function_locale.pl" in the distro. |
1151
|
|
|
|
|
|
|
# Refer to function_locale.pl for non-English function names. |
1152
|
|
|
|
|
|
|
# |
1153
|
|
|
|
|
|
|
# The array elements are as follow: |
1154
|
|
|
|
|
|
|
# ptg: The Excel function ptg code. |
1155
|
|
|
|
|
|
|
# args: The number of arguments that the function takes: |
1156
|
|
|
|
|
|
|
# >=0 is a fixed number of arguments. |
1157
|
|
|
|
|
|
|
# -1 is a variable number of arguments. |
1158
|
|
|
|
|
|
|
# class: The reference, value or array class of the function args. |
1159
|
|
|
|
|
|
|
# vol: The function is volatile. |
1160
|
|
|
|
|
|
|
# |
1161
|
4
|
|
|
|
|
1518
|
%functions = ( |
1162
|
|
|
|
|
|
|
# ptg args class vol |
1163
|
|
|
|
|
|
|
'COUNT' => [ 0, -1, 0, 0 ], |
1164
|
|
|
|
|
|
|
'IF' => [ 1, -1, 1, 0 ], |
1165
|
|
|
|
|
|
|
'ISNA' => [ 2, 1, 1, 0 ], |
1166
|
|
|
|
|
|
|
'ISERROR' => [ 3, 1, 1, 0 ], |
1167
|
|
|
|
|
|
|
'SUM' => [ 4, -1, 0, 0 ], |
1168
|
|
|
|
|
|
|
'AVERAGE' => [ 5, -1, 0, 0 ], |
1169
|
|
|
|
|
|
|
'MIN' => [ 6, -1, 0, 0 ], |
1170
|
|
|
|
|
|
|
'MAX' => [ 7, -1, 0, 0 ], |
1171
|
|
|
|
|
|
|
'ROW' => [ 8, -1, 0, 0 ], |
1172
|
|
|
|
|
|
|
'COLUMN' => [ 9, -1, 0, 0 ], |
1173
|
|
|
|
|
|
|
'NA' => [ 10, 0, 0, 0 ], |
1174
|
|
|
|
|
|
|
'NPV' => [ 11, -1, 1, 0 ], |
1175
|
|
|
|
|
|
|
'STDEV' => [ 12, -1, 0, 0 ], |
1176
|
|
|
|
|
|
|
'DOLLAR' => [ 13, -1, 1, 0 ], |
1177
|
|
|
|
|
|
|
'FIXED' => [ 14, -1, 1, 0 ], |
1178
|
|
|
|
|
|
|
'SIN' => [ 15, 1, 1, 0 ], |
1179
|
|
|
|
|
|
|
'COS' => [ 16, 1, 1, 0 ], |
1180
|
|
|
|
|
|
|
'TAN' => [ 17, 1, 1, 0 ], |
1181
|
|
|
|
|
|
|
'ATAN' => [ 18, 1, 1, 0 ], |
1182
|
|
|
|
|
|
|
'PI' => [ 19, 0, 1, 0 ], |
1183
|
|
|
|
|
|
|
'SQRT' => [ 20, 1, 1, 0 ], |
1184
|
|
|
|
|
|
|
'EXP' => [ 21, 1, 1, 0 ], |
1185
|
|
|
|
|
|
|
'LN' => [ 22, 1, 1, 0 ], |
1186
|
|
|
|
|
|
|
'LOG10' => [ 23, 1, 1, 0 ], |
1187
|
|
|
|
|
|
|
'ABS' => [ 24, 1, 1, 0 ], |
1188
|
|
|
|
|
|
|
'INT' => [ 25, 1, 1, 0 ], |
1189
|
|
|
|
|
|
|
'SIGN' => [ 26, 1, 1, 0 ], |
1190
|
|
|
|
|
|
|
'ROUND' => [ 27, 2, 1, 0 ], |
1191
|
|
|
|
|
|
|
'LOOKUP' => [ 28, -1, 0, 0 ], |
1192
|
|
|
|
|
|
|
'INDEX' => [ 29, -1, 0, 1 ], |
1193
|
|
|
|
|
|
|
'REPT' => [ 30, 2, 1, 0 ], |
1194
|
|
|
|
|
|
|
'MID' => [ 31, 3, 1, 0 ], |
1195
|
|
|
|
|
|
|
'LEN' => [ 32, 1, 1, 0 ], |
1196
|
|
|
|
|
|
|
'VALUE' => [ 33, 1, 1, 0 ], |
1197
|
|
|
|
|
|
|
'TRUE' => [ 34, 0, 1, 0 ], |
1198
|
|
|
|
|
|
|
'FALSE' => [ 35, 0, 1, 0 ], |
1199
|
|
|
|
|
|
|
'AND' => [ 36, -1, 1, 0 ], |
1200
|
|
|
|
|
|
|
'OR' => [ 37, -1, 1, 0 ], |
1201
|
|
|
|
|
|
|
'NOT' => [ 38, 1, 1, 0 ], |
1202
|
|
|
|
|
|
|
'MOD' => [ 39, 2, 1, 0 ], |
1203
|
|
|
|
|
|
|
'DCOUNT' => [ 40, 3, 0, 0 ], |
1204
|
|
|
|
|
|
|
'DSUM' => [ 41, 3, 0, 0 ], |
1205
|
|
|
|
|
|
|
'DAVERAGE' => [ 42, 3, 0, 0 ], |
1206
|
|
|
|
|
|
|
'DMIN' => [ 43, 3, 0, 0 ], |
1207
|
|
|
|
|
|
|
'DMAX' => [ 44, 3, 0, 0 ], |
1208
|
|
|
|
|
|
|
'DSTDEV' => [ 45, 3, 0, 0 ], |
1209
|
|
|
|
|
|
|
'VAR' => [ 46, -1, 0, 0 ], |
1210
|
|
|
|
|
|
|
'DVAR' => [ 47, 3, 0, 0 ], |
1211
|
|
|
|
|
|
|
'TEXT' => [ 48, 2, 1, 0 ], |
1212
|
|
|
|
|
|
|
'LINEST' => [ 49, -1, 0, 0 ], |
1213
|
|
|
|
|
|
|
'TREND' => [ 50, -1, 0, 0 ], |
1214
|
|
|
|
|
|
|
'LOGEST' => [ 51, -1, 0, 0 ], |
1215
|
|
|
|
|
|
|
'GROWTH' => [ 52, -1, 0, 0 ], |
1216
|
|
|
|
|
|
|
'PV' => [ 56, -1, 1, 0 ], |
1217
|
|
|
|
|
|
|
'FV' => [ 57, -1, 1, 0 ], |
1218
|
|
|
|
|
|
|
'NPER' => [ 58, -1, 1, 0 ], |
1219
|
|
|
|
|
|
|
'PMT' => [ 59, -1, 1, 0 ], |
1220
|
|
|
|
|
|
|
'RATE' => [ 60, -1, 1, 0 ], |
1221
|
|
|
|
|
|
|
'MIRR' => [ 61, 3, 0, 0 ], |
1222
|
|
|
|
|
|
|
'IRR' => [ 62, -1, 0, 0 ], |
1223
|
|
|
|
|
|
|
'RAND' => [ 63, 0, 1, 1 ], |
1224
|
|
|
|
|
|
|
'MATCH' => [ 64, -1, 0, 0 ], |
1225
|
|
|
|
|
|
|
'DATE' => [ 65, 3, 1, 0 ], |
1226
|
|
|
|
|
|
|
'TIME' => [ 66, 3, 1, 0 ], |
1227
|
|
|
|
|
|
|
'DAY' => [ 67, 1, 1, 0 ], |
1228
|
|
|
|
|
|
|
'MONTH' => [ 68, 1, 1, 0 ], |
1229
|
|
|
|
|
|
|
'YEAR' => [ 69, 1, 1, 0 ], |
1230
|
|
|
|
|
|
|
'WEEKDAY' => [ 70, -1, 1, 0 ], |
1231
|
|
|
|
|
|
|
'HOUR' => [ 71, 1, 1, 0 ], |
1232
|
|
|
|
|
|
|
'MINUTE' => [ 72, 1, 1, 0 ], |
1233
|
|
|
|
|
|
|
'SECOND' => [ 73, 1, 1, 0 ], |
1234
|
|
|
|
|
|
|
'NOW' => [ 74, 0, 1, 1 ], |
1235
|
|
|
|
|
|
|
'AREAS' => [ 75, 1, 0, 1 ], |
1236
|
|
|
|
|
|
|
'ROWS' => [ 76, 1, 0, 1 ], |
1237
|
|
|
|
|
|
|
'COLUMNS' => [ 77, 1, 0, 1 ], |
1238
|
|
|
|
|
|
|
'OFFSET' => [ 78, -1, 0, 1 ], |
1239
|
|
|
|
|
|
|
'SEARCH' => [ 82, -1, 1, 0 ], |
1240
|
|
|
|
|
|
|
'TRANSPOSE' => [ 83, 1, 1, 0 ], |
1241
|
|
|
|
|
|
|
'TYPE' => [ 86, 1, 1, 0 ], |
1242
|
|
|
|
|
|
|
'ATAN2' => [ 97, 2, 1, 0 ], |
1243
|
|
|
|
|
|
|
'ASIN' => [ 98, 1, 1, 0 ], |
1244
|
|
|
|
|
|
|
'ACOS' => [ 99, 1, 1, 0 ], |
1245
|
|
|
|
|
|
|
'CHOOSE' => [ 100, -1, 1, 0 ], |
1246
|
|
|
|
|
|
|
'HLOOKUP' => [ 101, -1, 0, 0 ], |
1247
|
|
|
|
|
|
|
'VLOOKUP' => [ 102, -1, 0, 0 ], |
1248
|
|
|
|
|
|
|
'ISREF' => [ 105, 1, 0, 0 ], |
1249
|
|
|
|
|
|
|
'LOG' => [ 109, -1, 1, 0 ], |
1250
|
|
|
|
|
|
|
'CHAR' => [ 111, 1, 1, 0 ], |
1251
|
|
|
|
|
|
|
'LOWER' => [ 112, 1, 1, 0 ], |
1252
|
|
|
|
|
|
|
'UPPER' => [ 113, 1, 1, 0 ], |
1253
|
|
|
|
|
|
|
'PROPER' => [ 114, 1, 1, 0 ], |
1254
|
|
|
|
|
|
|
'LEFT' => [ 115, -1, 1, 0 ], |
1255
|
|
|
|
|
|
|
'RIGHT' => [ 116, -1, 1, 0 ], |
1256
|
|
|
|
|
|
|
'EXACT' => [ 117, 2, 1, 0 ], |
1257
|
|
|
|
|
|
|
'TRIM' => [ 118, 1, 1, 0 ], |
1258
|
|
|
|
|
|
|
'REPLACE' => [ 119, 4, 1, 0 ], |
1259
|
|
|
|
|
|
|
'SUBSTITUTE' => [ 120, -1, 1, 0 ], |
1260
|
|
|
|
|
|
|
'CODE' => [ 121, 1, 1, 0 ], |
1261
|
|
|
|
|
|
|
'FIND' => [ 124, -1, 1, 0 ], |
1262
|
|
|
|
|
|
|
'CELL' => [ 125, -1, 0, 1 ], |
1263
|
|
|
|
|
|
|
'ISERR' => [ 126, 1, 1, 0 ], |
1264
|
|
|
|
|
|
|
'ISTEXT' => [ 127, 1, 1, 0 ], |
1265
|
|
|
|
|
|
|
'ISNUMBER' => [ 128, 1, 1, 0 ], |
1266
|
|
|
|
|
|
|
'ISBLANK' => [ 129, 1, 1, 0 ], |
1267
|
|
|
|
|
|
|
'T' => [ 130, 1, 0, 0 ], |
1268
|
|
|
|
|
|
|
'N' => [ 131, 1, 0, 0 ], |
1269
|
|
|
|
|
|
|
'DATEVALUE' => [ 140, 1, 1, 0 ], |
1270
|
|
|
|
|
|
|
'TIMEVALUE' => [ 141, 1, 1, 0 ], |
1271
|
|
|
|
|
|
|
'SLN' => [ 142, 3, 1, 0 ], |
1272
|
|
|
|
|
|
|
'SYD' => [ 143, 4, 1, 0 ], |
1273
|
|
|
|
|
|
|
'DDB' => [ 144, -1, 1, 0 ], |
1274
|
|
|
|
|
|
|
'INDIRECT' => [ 148, -1, 1, 1 ], |
1275
|
|
|
|
|
|
|
'CALL' => [ 150, -1, 1, 0 ], |
1276
|
|
|
|
|
|
|
'CLEAN' => [ 162, 1, 1, 0 ], |
1277
|
|
|
|
|
|
|
'MDETERM' => [ 163, 1, 2, 0 ], |
1278
|
|
|
|
|
|
|
'MINVERSE' => [ 164, 1, 2, 0 ], |
1279
|
|
|
|
|
|
|
'MMULT' => [ 165, 2, 2, 0 ], |
1280
|
|
|
|
|
|
|
'IPMT' => [ 167, -1, 1, 0 ], |
1281
|
|
|
|
|
|
|
'PPMT' => [ 168, -1, 1, 0 ], |
1282
|
|
|
|
|
|
|
'COUNTA' => [ 169, -1, 0, 0 ], |
1283
|
|
|
|
|
|
|
'PRODUCT' => [ 183, -1, 0, 0 ], |
1284
|
|
|
|
|
|
|
'FACT' => [ 184, 1, 1, 0 ], |
1285
|
|
|
|
|
|
|
'DPRODUCT' => [ 189, 3, 0, 0 ], |
1286
|
|
|
|
|
|
|
'ISNONTEXT' => [ 190, 1, 1, 0 ], |
1287
|
|
|
|
|
|
|
'STDEVP' => [ 193, -1, 0, 0 ], |
1288
|
|
|
|
|
|
|
'VARP' => [ 194, -1, 0, 0 ], |
1289
|
|
|
|
|
|
|
'DSTDEVP' => [ 195, 3, 0, 0 ], |
1290
|
|
|
|
|
|
|
'DVARP' => [ 196, 3, 0, 0 ], |
1291
|
|
|
|
|
|
|
'TRUNC' => [ 197, -1, 1, 0 ], |
1292
|
|
|
|
|
|
|
'ISLOGICAL' => [ 198, 1, 1, 0 ], |
1293
|
|
|
|
|
|
|
'DCOUNTA' => [ 199, 3, 0, 0 ], |
1294
|
|
|
|
|
|
|
'ROUNDUP' => [ 212, 2, 1, 0 ], |
1295
|
|
|
|
|
|
|
'ROUNDDOWN' => [ 213, 2, 1, 0 ], |
1296
|
|
|
|
|
|
|
'RANK' => [ 216, -1, 0, 0 ], |
1297
|
|
|
|
|
|
|
'ADDRESS' => [ 219, -1, 1, 0 ], |
1298
|
|
|
|
|
|
|
'DAYS360' => [ 220, -1, 1, 0 ], |
1299
|
|
|
|
|
|
|
'TODAY' => [ 221, 0, 1, 1 ], |
1300
|
|
|
|
|
|
|
'VDB' => [ 222, -1, 1, 0 ], |
1301
|
|
|
|
|
|
|
'MEDIAN' => [ 227, -1, 0, 0 ], |
1302
|
|
|
|
|
|
|
'SUMPRODUCT' => [ 228, -1, 2, 0 ], |
1303
|
|
|
|
|
|
|
'SINH' => [ 229, 1, 1, 0 ], |
1304
|
|
|
|
|
|
|
'COSH' => [ 230, 1, 1, 0 ], |
1305
|
|
|
|
|
|
|
'TANH' => [ 231, 1, 1, 0 ], |
1306
|
|
|
|
|
|
|
'ASINH' => [ 232, 1, 1, 0 ], |
1307
|
|
|
|
|
|
|
'ACOSH' => [ 233, 1, 1, 0 ], |
1308
|
|
|
|
|
|
|
'ATANH' => [ 234, 1, 1, 0 ], |
1309
|
|
|
|
|
|
|
'DGET' => [ 235, 3, 0, 0 ], |
1310
|
|
|
|
|
|
|
'INFO' => [ 244, 1, 1, 1 ], |
1311
|
|
|
|
|
|
|
'DB' => [ 247, -1, 1, 0 ], |
1312
|
|
|
|
|
|
|
'FREQUENCY' => [ 252, 2, 0, 0 ], |
1313
|
|
|
|
|
|
|
'ERROR.TYPE' => [ 261, 1, 1, 0 ], |
1314
|
|
|
|
|
|
|
'REGISTER.ID' => [ 267, -1, 1, 0 ], |
1315
|
|
|
|
|
|
|
'AVEDEV' => [ 269, -1, 0, 0 ], |
1316
|
|
|
|
|
|
|
'BETADIST' => [ 270, -1, 1, 0 ], |
1317
|
|
|
|
|
|
|
'GAMMALN' => [ 271, 1, 1, 0 ], |
1318
|
|
|
|
|
|
|
'BETAINV' => [ 272, -1, 1, 0 ], |
1319
|
|
|
|
|
|
|
'BINOMDIST' => [ 273, 4, 1, 0 ], |
1320
|
|
|
|
|
|
|
'CHIDIST' => [ 274, 2, 1, 0 ], |
1321
|
|
|
|
|
|
|
'CHIINV' => [ 275, 2, 1, 0 ], |
1322
|
|
|
|
|
|
|
'COMBIN' => [ 276, 2, 1, 0 ], |
1323
|
|
|
|
|
|
|
'CONFIDENCE' => [ 277, 3, 1, 0 ], |
1324
|
|
|
|
|
|
|
'CRITBINOM' => [ 278, 3, 1, 0 ], |
1325
|
|
|
|
|
|
|
'EVEN' => [ 279, 1, 1, 0 ], |
1326
|
|
|
|
|
|
|
'EXPONDIST' => [ 280, 3, 1, 0 ], |
1327
|
|
|
|
|
|
|
'FDIST' => [ 281, 3, 1, 0 ], |
1328
|
|
|
|
|
|
|
'FINV' => [ 282, 3, 1, 0 ], |
1329
|
|
|
|
|
|
|
'FISHER' => [ 283, 1, 1, 0 ], |
1330
|
|
|
|
|
|
|
'FISHERINV' => [ 284, 1, 1, 0 ], |
1331
|
|
|
|
|
|
|
'FLOOR' => [ 285, 2, 1, 0 ], |
1332
|
|
|
|
|
|
|
'GAMMADIST' => [ 286, 4, 1, 0 ], |
1333
|
|
|
|
|
|
|
'GAMMAINV' => [ 287, 3, 1, 0 ], |
1334
|
|
|
|
|
|
|
'CEILING' => [ 288, 2, 1, 0 ], |
1335
|
|
|
|
|
|
|
'HYPGEOMDIST' => [ 289, 4, 1, 0 ], |
1336
|
|
|
|
|
|
|
'LOGNORMDIST' => [ 290, 3, 1, 0 ], |
1337
|
|
|
|
|
|
|
'LOGINV' => [ 291, 3, 1, 0 ], |
1338
|
|
|
|
|
|
|
'NEGBINOMDIST' => [ 292, 3, 1, 0 ], |
1339
|
|
|
|
|
|
|
'NORMDIST' => [ 293, 4, 1, 0 ], |
1340
|
|
|
|
|
|
|
'NORMSDIST' => [ 294, 1, 1, 0 ], |
1341
|
|
|
|
|
|
|
'NORMINV' => [ 295, 3, 1, 0 ], |
1342
|
|
|
|
|
|
|
'NORMSINV' => [ 296, 1, 1, 0 ], |
1343
|
|
|
|
|
|
|
'STANDARDIZE' => [ 297, 3, 1, 0 ], |
1344
|
|
|
|
|
|
|
'ODD' => [ 298, 1, 1, 0 ], |
1345
|
|
|
|
|
|
|
'PERMUT' => [ 299, 2, 1, 0 ], |
1346
|
|
|
|
|
|
|
'POISSON' => [ 300, 3, 1, 0 ], |
1347
|
|
|
|
|
|
|
'TDIST' => [ 301, 3, 1, 0 ], |
1348
|
|
|
|
|
|
|
'WEIBULL' => [ 302, 4, 1, 0 ], |
1349
|
|
|
|
|
|
|
'SUMXMY2' => [ 303, 2, 2, 0 ], |
1350
|
|
|
|
|
|
|
'SUMX2MY2' => [ 304, 2, 2, 0 ], |
1351
|
|
|
|
|
|
|
'SUMX2PY2' => [ 305, 2, 2, 0 ], |
1352
|
|
|
|
|
|
|
'CHITEST' => [ 306, 2, 2, 0 ], |
1353
|
|
|
|
|
|
|
'CORREL' => [ 307, 2, 2, 0 ], |
1354
|
|
|
|
|
|
|
'COVAR' => [ 308, 2, 2, 0 ], |
1355
|
|
|
|
|
|
|
'FORECAST' => [ 309, 3, 2, 0 ], |
1356
|
|
|
|
|
|
|
'FTEST' => [ 310, 2, 2, 0 ], |
1357
|
|
|
|
|
|
|
'INTERCEPT' => [ 311, 2, 2, 0 ], |
1358
|
|
|
|
|
|
|
'PEARSON' => [ 312, 2, 2, 0 ], |
1359
|
|
|
|
|
|
|
'RSQ' => [ 313, 2, 2, 0 ], |
1360
|
|
|
|
|
|
|
'STEYX' => [ 314, 2, 2, 0 ], |
1361
|
|
|
|
|
|
|
'SLOPE' => [ 315, 2, 2, 0 ], |
1362
|
|
|
|
|
|
|
'TTEST' => [ 316, 4, 2, 0 ], |
1363
|
|
|
|
|
|
|
'PROB' => [ 317, -1, 2, 0 ], |
1364
|
|
|
|
|
|
|
'DEVSQ' => [ 318, -1, 0, 0 ], |
1365
|
|
|
|
|
|
|
'GEOMEAN' => [ 319, -1, 0, 0 ], |
1366
|
|
|
|
|
|
|
'HARMEAN' => [ 320, -1, 0, 0 ], |
1367
|
|
|
|
|
|
|
'SUMSQ' => [ 321, -1, 0, 0 ], |
1368
|
|
|
|
|
|
|
'KURT' => [ 322, -1, 0, 0 ], |
1369
|
|
|
|
|
|
|
'SKEW' => [ 323, -1, 0, 0 ], |
1370
|
|
|
|
|
|
|
'ZTEST' => [ 324, -1, 0, 0 ], |
1371
|
|
|
|
|
|
|
'LARGE' => [ 325, 2, 0, 0 ], |
1372
|
|
|
|
|
|
|
'SMALL' => [ 326, 2, 0, 0 ], |
1373
|
|
|
|
|
|
|
'QUARTILE' => [ 327, 2, 0, 0 ], |
1374
|
|
|
|
|
|
|
'PERCENTILE' => [ 328, 2, 0, 0 ], |
1375
|
|
|
|
|
|
|
'PERCENTRANK' => [ 329, -1, 0, 0 ], |
1376
|
|
|
|
|
|
|
'MODE' => [ 330, -1, 2, 0 ], |
1377
|
|
|
|
|
|
|
'TRIMMEAN' => [ 331, 2, 0, 0 ], |
1378
|
|
|
|
|
|
|
'TINV' => [ 332, 2, 1, 0 ], |
1379
|
|
|
|
|
|
|
'CONCATENATE' => [ 336, -1, 1, 0 ], |
1380
|
|
|
|
|
|
|
'POWER' => [ 337, 2, 1, 0 ], |
1381
|
|
|
|
|
|
|
'RADIANS' => [ 342, 1, 1, 0 ], |
1382
|
|
|
|
|
|
|
'DEGREES' => [ 343, 1, 1, 0 ], |
1383
|
|
|
|
|
|
|
'SUBTOTAL' => [ 344, -1, 0, 0 ], |
1384
|
|
|
|
|
|
|
'SUMIF' => [ 345, -1, 0, 0 ], |
1385
|
|
|
|
|
|
|
'COUNTIF' => [ 346, 2, 0, 0 ], |
1386
|
|
|
|
|
|
|
'COUNTBLANK' => [ 347, 1, 0, 0 ], |
1387
|
|
|
|
|
|
|
'ROMAN' => [ 354, -1, 1, 0 ], |
1388
|
|
|
|
|
|
|
); |
1389
|
|
|
|
|
|
|
|
1390
|
|
|
|
|
|
|
} |
1391
|
|
|
|
|
|
|
|
1392
|
|
|
|
|
|
|
|
1393
|
|
|
|
|
|
|
|
1394
|
|
|
|
|
|
|
|
1395
|
|
|
|
|
|
|
1; |
1396
|
|
|
|
|
|
|
|
1397
|
|
|
|
|
|
|
|
1398
|
|
|
|
|
|
|
__END__ |