Source code

Download source archive ↓

src/xlrd/formula.py

1# -*- coding: utf-8 -*-
2# Copyright (c) 2005-2012 Stephen John Machin, Lingfo Pty Ltd
3# This module is part of the xlrd package, which is released under a
4# BSD-style licence.
5# No part of the content of this file was derived from the works of
6# David Giffin.
7"""
8Module for parsing/evaluating Microsoft Excel formulas.
9"""
10
11from __future__ import print_function
12
13import copy
14import operator as opr
15from struct import unpack
16
17from .biffh import (
18    BaseObject, XLRDError, error_text_from_code, hex_char_dump,
19    unpack_string_update_pos, unpack_unicode_update_pos,
20)
21from .timemachine import *
22
23__all__ = [
24    'oBOOL', 'oERR', 'oNUM', 'oREF', 'oREL', 'oSTRG', 'oUNK',
25    'decompile_formula',
26    'dump_formula',
27    'evaluate_name_formula',
28    'okind_dict',
29    'rangename3d', 'rangename3drel', 'cellname', 'cellnameabs', 'colname',
30    'FMLA_TYPE_CELL',
31    'FMLA_TYPE_SHARED',
32    'FMLA_TYPE_ARRAY',
33    'FMLA_TYPE_COND_FMT',
34    'FMLA_TYPE_DATA_VAL',
35    'FMLA_TYPE_NAME',
36    'Operand', 'Ref3D',
37]
38
39FMLA_TYPE_CELL = 1
40FMLA_TYPE_SHARED = 2
41FMLA_TYPE_ARRAY = 4
42FMLA_TYPE_COND_FMT = 8
43FMLA_TYPE_DATA_VAL = 16
44FMLA_TYPE_NAME = 32
45ALL_FMLA_TYPES = 63
46
47
48FMLA_TYPEDESCR_MAP = {
49    1 : 'CELL',
50    2 : 'SHARED',
51    4 : 'ARRAY',
52    8 : 'COND-FMT',
53    16: 'DATA-VAL',
54    32: 'NAME',
55}
56
57_TOKEN_NOT_ALLOWED = {
58    0x01:   ALL_FMLA_TYPES - FMLA_TYPE_CELL, # tExp
59    0x02:   ALL_FMLA_TYPES - FMLA_TYPE_CELL, # tTbl
60    0x0F:   FMLA_TYPE_SHARED + FMLA_TYPE_COND_FMT + FMLA_TYPE_DATA_VAL, # tIsect
61    0x10:   FMLA_TYPE_SHARED + FMLA_TYPE_COND_FMT + FMLA_TYPE_DATA_VAL, # tUnion/List
62    0x11:   FMLA_TYPE_SHARED + FMLA_TYPE_COND_FMT + FMLA_TYPE_DATA_VAL, # tRange
63    0x20:   FMLA_TYPE_SHARED + FMLA_TYPE_COND_FMT + FMLA_TYPE_DATA_VAL, # tArray
64    0x23:   FMLA_TYPE_SHARED, # tName
65    0x39:   FMLA_TYPE_SHARED + FMLA_TYPE_COND_FMT + FMLA_TYPE_DATA_VAL, # tNameX
66    0x3A:   FMLA_TYPE_SHARED + FMLA_TYPE_COND_FMT + FMLA_TYPE_DATA_VAL, # tRef3d
67    0x3B:   FMLA_TYPE_SHARED + FMLA_TYPE_COND_FMT + FMLA_TYPE_DATA_VAL, # tArea3d
68    0x2C:   FMLA_TYPE_CELL + FMLA_TYPE_ARRAY, # tRefN
69    0x2D:   FMLA_TYPE_CELL + FMLA_TYPE_ARRAY, # tAreaN
70    # plus weird stuff like tMem*
71}.get
72
73oBOOL = 3
74oERR =  4
75oMSNG = 5 # tMissArg
76oNUM =  2
77oREF = -1
78oREL = -2
79oSTRG = 1
80oUNK =  0
81
82okind_dict = {
83    -2: "oREL",
84    -1: "oREF",
85    0 : "oUNK",
86    1 : "oSTRG",
87    2 : "oNUM",
88    3 : "oBOOL",
89    4 : "oERR",
90    5 : "oMSNG",
91}
92
93listsep = ',' #### probably should depend on locale
94
95
96# sztabN[opcode] -> the number of bytes to consume.
97# -1 means variable
98# -2 means this opcode not implemented in this version.
99# Which N to use? Depends on biff_version; see szdict.
100sztab0 = [-2, 4, 4, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, -1, -2, -1, 8, 4, 2, 2, 3, 9, 8, 2, 3, 8, 4, 7, 5, 5, 5, 2, 4, 7, 4, 7, 2, 2, -2, -2, -2, -2, -2, -2, -2, -2, 3, -2, -2, -2, -2, -2, -2, -2]
101sztab1 = [-2, 5, 5, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, -1, -2, -1, 11, 5, 2, 2, 3, 9, 9, 2, 3, 11, 4, 7, 7, 7, 7, 3, 4, 7, 4, 7, 3, 3, -2, -2, -2, -2, -2, -2, -2, -2, 3, -2, -2, -2, -2, -2, -2, -2]
102sztab2 = [-2, 5, 5, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, -1, -2, -1, 11, 5, 2, 2, 3, 9, 9, 3, 4, 11, 4, 7, 7, 7, 7, 3, 4, 7, 4, 7, 3, 3, -2, -2, -2, -2, -2, -2, -2, -2, -2, -2, -2, -2, -2, -2, -2, -2]
103sztab3 = [-2, 5, 5, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, -1, -2, -1, -2, -2, 2, 2, 3, 9, 9, 3, 4, 15, 4, 7, 7, 7, 7, 3, 4, 7, 4, 7, 3, 3, -2, -2, -2, -2, -2, -2, -2, -2, -2, 25, 18, 21, 18, 21, -2, -2]
104sztab4 = [-2, 5, 5, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, -1, -1, -1, -2, -2, 2, 2, 3, 9, 9, 3, 4, 5, 5, 9, 7, 7, 7, 3, 5, 9, 5, 9, 3, 3, -2, -2, -2, -2, -2, -2, -2, -2, -2, 7, 7, 11, 7, 11, -2, -2]
105
106szdict = {
107    20 : sztab0,
108    21 : sztab0,
109    30 : sztab1,
110    40 : sztab2,
111    45 : sztab2,
112    50 : sztab3,
113    70 : sztab3,
114    80 : sztab4,
115}
116
117# For debugging purposes ... the name for each opcode
118# (without the prefix "t" used on OOo docs)
119onames = ['Unk00', 'Exp', 'Tbl', 'Add', 'Sub', 'Mul', 'Div', 'Power', 'Concat', 'LT', 'LE', 'EQ', 'GE', 'GT', 'NE', 'Isect', 'List', 'Range', 'Uplus', 'Uminus', 'Percent', 'Paren', 'MissArg', 'Str', 'Extended', 'Attr', 'Sheet', 'EndSheet', 'Err', 'Bool', 'Int', 'Num', 'Array', 'Func', 'FuncVar', 'Name', 'Ref', 'Area', 'MemArea', 'MemErr', 'MemNoMem', 'MemFunc', 'RefErr', 'AreaErr', 'RefN', 'AreaN', 'MemAreaN', 'MemNoMemN', '', '', '', '', '', '', '', '', 'FuncCE', 'NameX', 'Ref3d', 'Area3d', 'RefErr3d', 'AreaErr3d', '', '']
120
121func_defs = {
122    # index: (name, min#args, max#args, flags, #known_args, return_type, kargs)
123    0  : ('COUNT',            0, 30, 0x04,  1, 'V', 'R'),
124    1  : ('IF',               2,  3, 0x04,  3, 'V', 'VRR'),
125    2  : ('ISNA',             1,  1, 0x02,  1, 'V', 'V'),
126    3  : ('ISERROR',          1,  1, 0x02,  1, 'V', 'V'),
127    4  : ('SUM',              0, 30, 0x04,  1, 'V', 'R'),
128    5  : ('AVERAGE',          1, 30, 0x04,  1, 'V', 'R'),
129    6  : ('MIN',              1, 30, 0x04,  1, 'V', 'R'),
130    7  : ('MAX',              1, 30, 0x04,  1, 'V', 'R'),
131    8  : ('ROW',              0,  1, 0x04,  1, 'V', 'R'),
132    9  : ('COLUMN',           0,  1, 0x04,  1, 'V', 'R'),
133    10 : ('NA',               0,  0, 0x02,  0, 'V', ''),
134    11 : ('NPV',              2, 30, 0x04,  2, 'V', 'VR'),
135    12 : ('STDEV',            1, 30, 0x04,  1, 'V', 'R'),
136    13 : ('DOLLAR',           1,  2, 0x04,  1, 'V', 'V'),
137    14 : ('FIXED',            2,  3, 0x04,  3, 'V', 'VVV'),
138    15 : ('SIN',              1,  1, 0x02,  1, 'V', 'V'),
139    16 : ('COS',              1,  1, 0x02,  1, 'V', 'V'),
140    17 : ('TAN',              1,  1, 0x02,  1, 'V', 'V'),
141    18 : ('ATAN',             1,  1, 0x02,  1, 'V', 'V'),
142    19 : ('PI',               0,  0, 0x02,  0, 'V', ''),
143    20 : ('SQRT',             1,  1, 0x02,  1, 'V', 'V'),
144    21 : ('EXP',              1,  1, 0x02,  1, 'V', 'V'),
145    22 : ('LN',               1,  1, 0x02,  1, 'V', 'V'),
146    23 : ('LOG10',            1,  1, 0x02,  1, 'V', 'V'),
147    24 : ('ABS',              1,  1, 0x02,  1, 'V', 'V'),
148    25 : ('INT',              1,  1, 0x02,  1, 'V', 'V'),
149    26 : ('SIGN',             1,  1, 0x02,  1, 'V', 'V'),
150    27 : ('ROUND',            2,  2, 0x02,  2, 'V', 'VV'),
151    28 : ('LOOKUP',           2,  3, 0x04,  2, 'V', 'VR'),
152    29 : ('INDEX',            2,  4, 0x0c,  4, 'R', 'RVVV'),
153    30 : ('REPT',             2,  2, 0x02,  2, 'V', 'VV'),
154    31 : ('MID',              3,  3, 0x02,  3, 'V', 'VVV'),
155    32 : ('LEN',              1,  1, 0x02,  1, 'V', 'V'),
156    33 : ('VALUE',            1,  1, 0x02,  1, 'V', 'V'),
157    34 : ('TRUE',             0,  0, 0x02,  0, 'V', ''),
158    35 : ('FALSE',            0,  0, 0x02,  0, 'V', ''),
159    36 : ('AND',              1, 30, 0x04,  1, 'V', 'R'),
160    37 : ('OR',               1, 30, 0x04,  1, 'V', 'R'),
161    38 : ('NOT',              1,  1, 0x02,  1, 'V', 'V'),
162    39 : ('MOD',              2,  2, 0x02,  2, 'V', 'VV'),
163    40 : ('DCOUNT',           3,  3, 0x02,  3, 'V', 'RRR'),
164    41 : ('DSUM',             3,  3, 0x02,  3, 'V', 'RRR'),
165    42 : ('DAVERAGE',         3,  3, 0x02,  3, 'V', 'RRR'),
166    43 : ('DMIN',             3,  3, 0x02,  3, 'V', 'RRR'),
167    44 : ('DMAX',             3,  3, 0x02,  3, 'V', 'RRR'),
168    45 : ('DSTDEV',           3,  3, 0x02,  3, 'V', 'RRR'),
169    46 : ('VAR',              1, 30, 0x04,  1, 'V', 'R'),
170    47 : ('DVAR',             3,  3, 0x02,  3, 'V', 'RRR'),
171    48 : ('TEXT',             2,  2, 0x02,  2, 'V', 'VV'),
172    49 : ('LINEST',           1,  4, 0x04,  4, 'A', 'RRVV'),
173    50 : ('TREND',            1,  4, 0x04,  4, 'A', 'RRRV'),
174    51 : ('LOGEST',           1,  4, 0x04,  4, 'A', 'RRVV'),
175    52 : ('GROWTH',           1,  4, 0x04,  4, 'A', 'RRRV'),
176    56 : ('PV',               3,  5, 0x04,  5, 'V', 'VVVVV'),
177    57 : ('FV',               3,  5, 0x04,  5, 'V', 'VVVVV'),
178    58 : ('NPER',             3,  5, 0x04,  5, 'V', 'VVVVV'),
179    59 : ('PMT',              3,  5, 0x04,  5, 'V', 'VVVVV'),
180    60 : ('RATE',             3,  6, 0x04,  6, 'V', 'VVVVVV'),
181    61 : ('MIRR',             3,  3, 0x02,  3, 'V', 'RVV'),
182    62 : ('IRR',              1,  2, 0x04,  2, 'V', 'RV'),
183    63 : ('RAND',             0,  0, 0x0a,  0, 'V', ''),
184    64 : ('MATCH',            2,  3, 0x04,  3, 'V', 'VRR'),
185    65 : ('DATE',             3,  3, 0x02,  3, 'V', 'VVV'),
186    66 : ('TIME',             3,  3, 0x02,  3, 'V', 'VVV'),
187    67 : ('DAY',              1,  1, 0x02,  1, 'V', 'V'),
188    68 : ('MONTH',            1,  1, 0x02,  1, 'V', 'V'),
189    69 : ('YEAR',             1,  1, 0x02,  1, 'V', 'V'),
190    70 : ('WEEKDAY',          1,  2, 0x04,  2, 'V', 'VV'),
191    71 : ('HOUR',             1,  1, 0x02,  1, 'V', 'V'),
192    72 : ('MINUTE',           1,  1, 0x02,  1, 'V', 'V'),
193    73 : ('SECOND',           1,  1, 0x02,  1, 'V', 'V'),
194    74 : ('NOW',              0,  0, 0x0a,  0, 'V', ''),
195    75 : ('AREAS',            1,  1, 0x02,  1, 'V', 'R'),
196    76 : ('ROWS',             1,  1, 0x02,  1, 'V', 'R'),
197    77 : ('COLUMNS',          1,  1, 0x02,  1, 'V', 'R'),
198    78 : ('OFFSET',           3,  5, 0x04,  5, 'R', 'RVVVV'),
199    82 : ('SEARCH',           2,  3, 0x04,  3, 'V', 'VVV'),
200    83 : ('TRANSPOSE',        1,  1, 0x02,  1, 'A', 'A'),
201    86 : ('TYPE',             1,  1, 0x02,  1, 'V', 'V'),
202    92 : ('SERIESSUM',        4,  4, 0x02,  4, 'V', 'VVVA'),
203    97 : ('ATAN2',            2,  2, 0x02,  2, 'V', 'VV'),
204    98 : ('ASIN',             1,  1, 0x02,  1, 'V', 'V'),
205    99 : ('ACOS',             1,  1, 0x02,  1, 'V', 'V'),
206    100: ('CHOOSE',           2, 30, 0x04,  2, 'V', 'VR'),
207    101: ('HLOOKUP',          3,  4, 0x04,  4, 'V', 'VRRV'),
208    102: ('VLOOKUP',          3,  4, 0x04,  4, 'V', 'VRRV'),
209    105: ('ISREF',            1,  1, 0x02,  1, 'V', 'R'),
210    109: ('LOG',              1,  2, 0x04,  2, 'V', 'VV'),
211    111: ('CHAR',             1,  1, 0x02,  1, 'V', 'V'),
212    112: ('LOWER',            1,  1, 0x02,  1, 'V', 'V'),
213    113: ('UPPER',            1,  1, 0x02,  1, 'V', 'V'),
214    114: ('PROPER',           1,  1, 0x02,  1, 'V', 'V'),
215    115: ('LEFT',             1,  2, 0x04,  2, 'V', 'VV'),
216    116: ('RIGHT',            1,  2, 0x04,  2, 'V', 'VV'),
217    117: ('EXACT',            2,  2, 0x02,  2, 'V', 'VV'),
218    118: ('TRIM',             1,  1, 0x02,  1, 'V', 'V'),
219    119: ('REPLACE',          4,  4, 0x02,  4, 'V', 'VVVV'),
220    120: ('SUBSTITUTE',       3,  4, 0x04,  4, 'V', 'VVVV'),
221    121: ('CODE',             1,  1, 0x02,  1, 'V', 'V'),
222    124: ('FIND',             2,  3, 0x04,  3, 'V', 'VVV'),
223    125: ('CELL',             1,  2, 0x0c,  2, 'V', 'VR'),
224    126: ('ISERR',            1,  1, 0x02,  1, 'V', 'V'),
225    127: ('ISTEXT',           1,  1, 0x02,  1, 'V', 'V'),
226    128: ('ISNUMBER',         1,  1, 0x02,  1, 'V', 'V'),
227    129: ('ISBLANK',          1,  1, 0x02,  1, 'V', 'V'),
228    130: ('T',                1,  1, 0x02,  1, 'V', 'R'),
229    131: ('N',                1,  1, 0x02,  1, 'V', 'R'),
230    140: ('DATEVALUE',        1,  1, 0x02,  1, 'V', 'V'),
231    141: ('TIMEVALUE',        1,  1, 0x02,  1, 'V', 'V'),
232    142: ('SLN',              3,  3, 0x02,  3, 'V', 'VVV'),
233    143: ('SYD',              4,  4, 0x02,  4, 'V', 'VVVV'),
234    144: ('DDB',              4,  5, 0x04,  5, 'V', 'VVVVV'),
235    148: ('INDIRECT',         1,  2, 0x0c,  2, 'R', 'VV'),
236    162: ('CLEAN',            1,  1, 0x02,  1, 'V', 'V'),
237    163: ('MDETERM',          1,  1, 0x02,  1, 'V', 'A'),
238    164: ('MINVERSE',         1,  1, 0x02,  1, 'A', 'A'),
239    165: ('MMULT',            2,  2, 0x02,  2, 'A', 'AA'),
240    167: ('IPMT',             4,  6, 0x04,  6, 'V', 'VVVVVV'),
241    168: ('PPMT',             4,  6, 0x04,  6, 'V', 'VVVVVV'),
242    169: ('COUNTA',           0, 30, 0x04,  1, 'V', 'R'),
243    183: ('PRODUCT',          0, 30, 0x04,  1, 'V', 'R'),
244    184: ('FACT',             1,  1, 0x02,  1, 'V', 'V'),
245    189: ('DPRODUCT',         3,  3, 0x02,  3, 'V', 'RRR'),
246    190: ('ISNONTEXT',        1,  1, 0x02,  1, 'V', 'V'),
247    193: ('STDEVP',           1, 30, 0x04,  1, 'V', 'R'),
248    194: ('VARP',             1, 30, 0x04,  1, 'V', 'R'),
249    195: ('DSTDEVP',          3,  3, 0x02,  3, 'V', 'RRR'),
250    196: ('DVARP',            3,  3, 0x02,  3, 'V', 'RRR'),
251    197: ('TRUNC',            1,  2, 0x04,  2, 'V', 'VV'),
252    198: ('ISLOGICAL',        1,  1, 0x02,  1, 'V', 'V'),
253    199: ('DCOUNTA',          3,  3, 0x02,  3, 'V', 'RRR'),
254    204: ('USDOLLAR',         1,  2, 0x04,  2, 'V', 'VV'),
255    205: ('FINDB',            2,  3, 0x04,  3, 'V', 'VVV'),
256    206: ('SEARCHB',          2,  3, 0x04,  3, 'V', 'VVV'),
257    207: ('REPLACEB',         4,  4, 0x02,  4, 'V', 'VVVV'),
258    208: ('LEFTB',            1,  2, 0x04,  2, 'V', 'VV'),
259    209: ('RIGHTB',           1,  2, 0x04,  2, 'V', 'VV'),
260    210: ('MIDB',             3,  3, 0x02,  3, 'V', 'VVV'),
261    211: ('LENB',             1,  1, 0x02,  1, 'V', 'V'),
262    212: ('ROUNDUP',          2,  2, 0x02,  2, 'V', 'VV'),
263    213: ('ROUNDDOWN',        2,  2, 0x02,  2, 'V', 'VV'),
264    214: ('ASC',              1,  1, 0x02,  1, 'V', 'V'),
265    215: ('DBCS',             1,  1, 0x02,  1, 'V', 'V'),
266    216: ('RANK',             2,  3, 0x04,  3, 'V', 'VRV'),
267    219: ('ADDRESS',          2,  5, 0x04,  5, 'V', 'VVVVV'),
268    220: ('DAYS360',          2,  3, 0x04,  3, 'V', 'VVV'),
269    221: ('TODAY',            0,  0, 0x0a,  0, 'V', ''),
270    222: ('VDB',              5,  7, 0x04,  7, 'V', 'VVVVVVV'),
271    227: ('MEDIAN',           1, 30, 0x04,  1, 'V', 'R'),
272    228: ('SUMPRODUCT',       1, 30, 0x04,  1, 'V', 'A'),
273    229: ('SINH',             1,  1, 0x02,  1, 'V', 'V'),
274    230: ('COSH',             1,  1, 0x02,  1, 'V', 'V'),
275    231: ('TANH',             1,  1, 0x02,  1, 'V', 'V'),
276    232: ('ASINH',            1,  1, 0x02,  1, 'V', 'V'),
277    233: ('ACOSH',            1,  1, 0x02,  1, 'V', 'V'),
278    234: ('ATANH',            1,  1, 0x02,  1, 'V', 'V'),
279    235: ('DGET',             3,  3, 0x02,  3, 'V', 'RRR'),
280    244: ('INFO',             1,  1, 0x02,  1, 'V', 'V'),
281    247: ('DB',               4,  5, 0x04,  5, 'V', 'VVVVV'),
282    252: ('FREQUENCY',        2,  2, 0x02,  2, 'A', 'RR'),
283    261: ('ERROR.TYPE',       1,  1, 0x02,  1, 'V', 'V'),
284    269: ('AVEDEV',           1, 30, 0x04,  1, 'V', 'R'),
285    270: ('BETADIST',         3,  5, 0x04,  1, 'V', 'V'),
286    271: ('GAMMALN',          1,  1, 0x02,  1, 'V', 'V'),
287    272: ('BETAINV',          3,  5, 0x04,  1, 'V', 'V'),
288    273: ('BINOMDIST',        4,  4, 0x02,  4, 'V', 'VVVV'),
289    274: ('CHIDIST',          2,  2, 0x02,  2, 'V', 'VV'),
290    275: ('CHIINV',           2,  2, 0x02,  2, 'V', 'VV'),
291    276: ('COMBIN',           2,  2, 0x02,  2, 'V', 'VV'),
292    277: ('CONFIDENCE',       3,  3, 0x02,  3, 'V', 'VVV'),
293    278: ('CRITBINOM',        3,  3, 0x02,  3, 'V', 'VVV'),
294    279: ('EVEN',             1,  1, 0x02,  1, 'V', 'V'),
295    280: ('EXPONDIST',        3,  3, 0x02,  3, 'V', 'VVV'),
296    281: ('FDIST',            3,  3, 0x02,  3, 'V', 'VVV'),
297    282: ('FINV',             3,  3, 0x02,  3, 'V', 'VVV'),
298    283: ('FISHER',           1,  1, 0x02,  1, 'V', 'V'),
299    284: ('FISHERINV',        1,  1, 0x02,  1, 'V', 'V'),
300    285: ('FLOOR',            2,  2, 0x02,  2, 'V', 'VV'),
301    286: ('GAMMADIST',        4,  4, 0x02,  4, 'V', 'VVVV'),
302    287: ('GAMMAINV',         3,  3, 0x02,  3, 'V', 'VVV'),
303    288: ('CEILING',          2,  2, 0x02,  2, 'V', 'VV'),
304    289: ('HYPGEOMDIST',      4,  4, 0x02,  4, 'V', 'VVVV'),
305    290: ('LOGNORMDIST',      3,  3, 0x02,  3, 'V', 'VVV'),
306    291: ('LOGINV',           3,  3, 0x02,  3, 'V', 'VVV'),
307    292: ('NEGBINOMDIST',     3,  3, 0x02,  3, 'V', 'VVV'),
308    293: ('NORMDIST',         4,  4, 0x02,  4, 'V', 'VVVV'),
309    294: ('NORMSDIST',        1,  1, 0x02,  1, 'V', 'V'),
310    295: ('NORMINV',          3,  3, 0x02,  3, 'V', 'VVV'),
311    296: ('NORMSINV',         1,  1, 0x02,  1, 'V', 'V'),
312    297: ('STANDARDIZE',      3,  3, 0x02,  3, 'V', 'VVV'),
313    298: ('ODD',              1,  1, 0x02,  1, 'V', 'V'),
314    299: ('PERMUT',           2,  2, 0x02,  2, 'V', 'VV'),
315    300: ('POISSON',          3,  3, 0x02,  3, 'V', 'VVV'),
316    301: ('TDIST',            3,  3, 0x02,  3, 'V', 'VVV'),
317    302: ('WEIBULL',          4,  4, 0x02,  4, 'V', 'VVVV'),
318    303: ('SUMXMY2',          2,  2, 0x02,  2, 'V', 'AA'),
319    304: ('SUMX2MY2',         2,  2, 0x02,  2, 'V', 'AA'),
320    305: ('SUMX2PY2',         2,  2, 0x02,  2, 'V', 'AA'),
321    306: ('CHITEST',          2,  2, 0x02,  2, 'V', 'AA'),
322    307: ('CORREL',           2,  2, 0x02,  2, 'V', 'AA'),
323    308: ('COVAR',            2,  2, 0x02,  2, 'V', 'AA'),
324    309: ('FORECAST',         3,  3, 0x02,  3, 'V', 'VAA'),
325    310: ('FTEST',            2,  2, 0x02,  2, 'V', 'AA'),
326    311: ('INTERCEPT',        2,  2, 0x02,  2, 'V', 'AA'),
327    312: ('PEARSON',          2,  2, 0x02,  2, 'V', 'AA'),
328    313: ('RSQ',              2,  2, 0x02,  2, 'V', 'AA'),
329    314: ('STEYX',            2,  2, 0x02,  2, 'V', 'AA'),
330    315: ('SLOPE',            2,  2, 0x02,  2, 'V', 'AA'),
331    316: ('TTEST',            4,  4, 0x02,  4, 'V', 'AAVV'),
332    317: ('PROB',             3,  4, 0x04,  3, 'V', 'AAV'),
333    318: ('DEVSQ',            1, 30, 0x04,  1, 'V', 'R'),
334    319: ('GEOMEAN',          1, 30, 0x04,  1, 'V', 'R'),
335    320: ('HARMEAN',          1, 30, 0x04,  1, 'V', 'R'),
336    321: ('SUMSQ',            0, 30, 0x04,  1, 'V', 'R'),
337    322: ('KURT',             1, 30, 0x04,  1, 'V', 'R'),
338    323: ('SKEW',             1, 30, 0x04,  1, 'V', 'R'),
339    324: ('ZTEST',            2,  3, 0x04,  2, 'V', 'RV'),
340    325: ('LARGE',            2,  2, 0x02,  2, 'V', 'RV'),
341    326: ('SMALL',            2,  2, 0x02,  2, 'V', 'RV'),
342    327: ('QUARTILE',         2,  2, 0x02,  2, 'V', 'RV'),
343    328: ('PERCENTILE',       2,  2, 0x02,  2, 'V', 'RV'),
344    329: ('PERCENTRANK',      2,  3, 0x04,  2, 'V', 'RV'),
345    330: ('MODE',             1, 30, 0x04,  1, 'V', 'A'),
346    331: ('TRIMMEAN',         2,  2, 0x02,  2, 'V', 'RV'),
347    332: ('TINV',             2,  2, 0x02,  2, 'V', 'VV'),
348    336: ('CONCATENATE',      0, 30, 0x04,  1, 'V', 'V'),
349    337: ('POWER',            2,  2, 0x02,  2, 'V', 'VV'),
350    342: ('RADIANS',          1,  1, 0x02,  1, 'V', 'V'),
351    343: ('DEGREES',          1,  1, 0x02,  1, 'V', 'V'),
352    344: ('SUBTOTAL',         2, 30, 0x04,  2, 'V', 'VR'),
353    345: ('SUMIF',            2,  3, 0x04,  3, 'V', 'RVR'),
354    346: ('COUNTIF',          2,  2, 0x02,  2, 'V', 'RV'),
355    347: ('COUNTBLANK',       1,  1, 0x02,  1, 'V', 'R'),
356    350: ('ISPMT',            4,  4, 0x02,  4, 'V', 'VVVV'),
357    351: ('DATEDIF',          3,  3, 0x02,  3, 'V', 'VVV'),
358    352: ('DATESTRING',       1,  1, 0x02,  1, 'V', 'V'),
359    353: ('NUMBERSTRING',     2,  2, 0x02,  2, 'V', 'VV'),
360    354: ('ROMAN',            1,  2, 0x04,  2, 'V', 'VV'),
361    358: ('GETPIVOTDATA',     2,  2, 0x02,  2, 'V', 'RV'),
362    359: ('HYPERLINK',        1,  2, 0x04,  2, 'V', 'VV'),
363    360: ('PHONETIC',         1,  1, 0x02,  1, 'V', 'V'),
364    361: ('AVERAGEA',         1, 30, 0x04,  1, 'V', 'R'),
365    362: ('MAXA',             1, 30, 0x04,  1, 'V', 'R'),
366    363: ('MINA',             1, 30, 0x04,  1, 'V', 'R'),
367    364: ('STDEVPA',          1, 30, 0x04,  1, 'V', 'R'),
368    365: ('VARPA',            1, 30, 0x04,  1, 'V', 'R'),
369    366: ('STDEVA',           1, 30, 0x04,  1, 'V', 'R'),
370    367: ('VARA',             1, 30, 0x04,  1, 'V', 'R'),
371    368: ('BAHTTEXT',         1,  1, 0x02,  1, 'V', 'V'),
372    369: ('THAIDAYOFWEEK',    1,  1, 0x02,  1, 'V', 'V'),
373    370: ('THAIDIGIT',        1,  1, 0x02,  1, 'V', 'V'),
374    371: ('THAIMONTHOFYEAR',  1,  1, 0x02,  1, 'V', 'V'),
375    372: ('THAINUMSOUND',     1,  1, 0x02,  1, 'V', 'V'),
376    373: ('THAINUMSTRING',    1,  1, 0x02,  1, 'V', 'V'),
377    374: ('THAISTRINGLENGTH', 1,  1, 0x02,  1, 'V', 'V'),
378    375: ('ISTHAIDIGIT',      1,  1, 0x02,  1, 'V', 'V'),
379    376: ('ROUNDBAHTDOWN',    1,  1, 0x02,  1, 'V', 'V'),
380    377: ('ROUNDBAHTUP',      1,  1, 0x02,  1, 'V', 'V'),
381    378: ('THAIYEAR',         1,  1, 0x02,  1, 'V', 'V'),
382    379: ('RTD',              2,  5, 0x04,  1, 'V', 'V'),
383}
384
385tAttrNames = {
386    0x00: "Skip??", # seen in SAMPLES.XLS which shipped with Excel 5.0
387    0x01: "Volatile",
388    0x02: "If",
389    0x04: "Choose",
390    0x08: "Skip",
391    0x10: "Sum",
392    0x20: "Assign",
393    0x40: "Space",
394    0x41: "SpaceVolatile",
395}
396
397error_opcodes = set([0x07, 0x08, 0x0A, 0x0B, 0x1C, 0x1D, 0x2F])
398
399tRangeFuncs = (min, max, min, max, min, max)
400tIsectFuncs = (max, min, max, min, max, min)
401
402def do_box_funcs(box_funcs, boxa, boxb):
403    return tuple(
404        func(numa, numb)
405        for func, numa, numb in zip(box_funcs, boxa.coords, boxb.coords)
406    )
407
408def adjust_cell_addr_biff8(rowval, colval, reldelta, browx=None, bcolx=None):
409    row_rel = (colval >> 15) & 1
410    col_rel = (colval >> 14) & 1
411    rowx = rowval
412    colx = colval & 0xff
413    if reldelta:
414        if row_rel and rowx >= 32768:
415            rowx -= 65536
416        if col_rel and colx >= 128:
417            colx -= 256
418    else:
419        if row_rel:
420            rowx -= browx
421        if col_rel:
422            colx -= bcolx
423    return rowx, colx, row_rel, col_rel
424
425def adjust_cell_addr_biff_le7(
426        rowval, colval, reldelta, browx=None, bcolx=None):
427    row_rel = (rowval >> 15) & 1
428    col_rel = (rowval >> 14) & 1
429    rowx = rowval & 0x3fff
430    colx = colval
431    if reldelta:
432        if row_rel and rowx >= 8192:
433            rowx -= 16384
434        if col_rel and colx >= 128:
435            colx -= 256
436    else:
437        if row_rel:
438            rowx -= browx
439        if col_rel:
440            colx -= bcolx
441    return rowx, colx, row_rel, col_rel
442
443def get_cell_addr(data, pos, bv, reldelta, browx=None, bcolx=None):
444    if bv >= 80:
445        rowval, colval = unpack("<HH", data[pos:pos+4])
446        # print "    rv=%04xh cv=%04xh" % (rowval, colval)
447        return adjust_cell_addr_biff8(rowval, colval, reldelta, browx, bcolx)
448    else:
449        rowval, colval = unpack("<HB", data[pos:pos+3])
450        # print "    rv=%04xh cv=%04xh" % (rowval, colval)
451        return adjust_cell_addr_biff_le7(
452                    rowval, colval, reldelta, browx, bcolx)
453
454def get_cell_range_addr(data, pos, bv, reldelta, browx=None, bcolx=None):
455    if bv >= 80:
456        row1val, row2val, col1val, col2val = unpack("<HHHH", data[pos:pos+8])
457        # print "    rv=%04xh cv=%04xh" % (row1val, col1val)
458        # print "    rv=%04xh cv=%04xh" % (row2val, col2val)
459        res1 = adjust_cell_addr_biff8(row1val, col1val, reldelta, browx, bcolx)
460        res2 = adjust_cell_addr_biff8(row2val, col2val, reldelta, browx, bcolx)
461        return res1, res2
462    else:
463        row1val, row2val, col1val, col2val = unpack("<HHBB", data[pos:pos+6])
464        # print "    rv=%04xh cv=%04xh" % (row1val, col1val)
465        # print "    rv=%04xh cv=%04xh" % (row2val, col2val)
466        res1 = adjust_cell_addr_biff_le7(
467                    row1val, col1val, reldelta, browx, bcolx)
468        res2 = adjust_cell_addr_biff_le7(
469                    row2val, col2val, reldelta, browx, bcolx)
470        return res1, res2
471
472def get_externsheet_local_range(bk, refx, blah=0):
473    try:
474        info = bk._externsheet_info[refx]
475    except IndexError:
476        print("!!! get_externsheet_local_range: refx=%d, not in range(%d)"
477            % (refx, len(bk._externsheet_info)), file=bk.logfile)
478        return (-101, -101)
479    ref_recordx, ref_first_sheetx, ref_last_sheetx = info
480    if ref_recordx == bk._supbook_addins_inx:
481        if blah:
482            print("/// get_externsheet_local_range(refx=%d) -> addins %r" % (refx, info), file=bk.logfile)
483        assert ref_first_sheetx == 0xFFFE == ref_last_sheetx
484        return (-5, -5)
485    if ref_recordx != bk._supbook_locals_inx:
486        if blah:
487            print("/// get_externsheet_local_range(refx=%d) -> external %r" % (refx, info), file=bk.logfile)
488        return (-4, -4) # external reference
489    if ref_first_sheetx == 0xFFFE == ref_last_sheetx:
490        if blah:
491            print("/// get_externsheet_local_range(refx=%d) -> unspecified sheet %r" % (refx, info), file=bk.logfile)
492        return (-1, -1) # internal reference, any sheet
493    if ref_first_sheetx == 0xFFFF == ref_last_sheetx:
494        if blah:
495            print("/// get_externsheet_local_range(refx=%d) -> deleted sheet(s)" % (refx, ), file=bk.logfile)
496        return (-2, -2) # internal reference, deleted sheet(s)
497    nsheets = len(bk._all_sheets_map)
498    if not(0 <= ref_first_sheetx <= ref_last_sheetx < nsheets):
499        if blah:
500            print("/// get_externsheet_local_range(refx=%d) -> %r" % (refx, info), file=bk.logfile)
501            print("--- first/last sheet not in range(%d)" % nsheets, file=bk.logfile)
502        return (-102, -102) # stuffed up somewhere :-(
503    xlrd_sheetx1 = bk._all_sheets_map[ref_first_sheetx]
504    xlrd_sheetx2 = bk._all_sheets_map[ref_last_sheetx]
505    if not(0 <= xlrd_sheetx1 <= xlrd_sheetx2):
506        return (-3, -3) # internal reference, but to a macro sheet
507    return xlrd_sheetx1, xlrd_sheetx2
508
509def get_externsheet_local_range_b57(
510        bk, raw_extshtx, ref_first_sheetx, ref_last_sheetx, blah=0):
511    if raw_extshtx > 0:
512        if blah:
513            print("/// get_externsheet_local_range_b57(raw_extshtx=%d) -> external" % raw_extshtx, file=bk.logfile)
514        return (-4, -4) # external reference
515    if ref_first_sheetx == -1 and ref_last_sheetx == -1:
516        return (-2, -2) # internal reference, deleted sheet(s)
517    nsheets = len(bk._all_sheets_map)
518    if not(0 <= ref_first_sheetx <= ref_last_sheetx < nsheets):
519        if blah:
520            print("/// get_externsheet_local_range_b57(%d, %d, %d) -> ???"
521                % (raw_extshtx, ref_first_sheetx, ref_last_sheetx), file=bk.logfile)
522            print("--- first/last sheet not in range(%d)" % nsheets, file=bk.logfile)
523        return (-103, -103) # stuffed up somewhere :-(
524    xlrd_sheetx1 = bk._all_sheets_map[ref_first_sheetx]
525    xlrd_sheetx2 = bk._all_sheets_map[ref_last_sheetx]
526    if not(0 <= xlrd_sheetx1 <= xlrd_sheetx2):
527        return (-3, -3) # internal reference, but to a macro sheet
528    return xlrd_sheetx1, xlrd_sheetx2
529
530class FormulaError(Exception):
531    pass
532
533
534class Operand(object):
535    """
536    Used in evaluating formulas.
537    The following table describes the kinds and how their values
538    are represented.
539
540    .. raw:: html
541
542        <table border="1" cellpadding="7">
543        <tr>
544        <th>Kind symbol</th>
545        <th>Kind number</th>
546        <th>Value representation</th>
547        </tr>
548        <tr>
549        <td>oBOOL</td>
550        <td align="center">3</td>
551        <td>integer: 0 => False; 1 => True</td>
552        </tr>
553        <tr>
554        <td>oERR</td>
555        <td align="center">4</td>
556        <td>None, or an int error code (same as XL_CELL_ERROR in the Cell class).
557        </td>
558        </tr>
559        <tr>
560        <td>oMSNG</td>
561        <td align="center">5</td>
562        <td>Used by Excel as a placeholder for a missing (not supplied) function
563        argument. Should *not* appear as a final formula result. Value is None.</td>
564        </tr>
565        <tr>
566        <td>oNUM</td>
567        <td align="center">2</td>
568        <td>A float. Note that there is no way of distinguishing dates.</td>
569        </tr>
570        <tr>
571        <td>oREF</td>
572        <td align="center">-1</td>
573        <td>The value is either None or a non-empty list of
574        absolute Ref3D instances.<br>
575        </td>
576        </tr>
577        <tr>
578        <td>oREL</td>
579        <td align="center">-2</td>
580        <td>The value is None or a non-empty list of
581        fully or partially relative Ref3D instances.
582        </td>
583        </tr>
584        <tr>
585        <td>oSTRG</td>
586        <td align="center">1</td>
587        <td>A Unicode string.</td>
588        </tr>
589        <tr>
590        <td>oUNK</td>
591        <td align="center">0</td>
592        <td>The kind is unknown or ambiguous. The value is None</td>
593        </tr>
594        </table>
595    """
596
597    #: None means that the actual value of the operand is a variable
598    #: (depends on cell data), not a constant.
599    value = None
600
601    #: oUNK means that the kind of operand is not known unambiguously.
602    kind = oUNK
603
604    #: The reconstituted text of the original formula. Function names will be
605    #: in English irrespective of the original language, which doesn't seem
606    #: to be recorded anywhere. The separator is ",", not ";" or whatever else
607    #: might be more appropriate for the end-user's locale; patches welcome.
608    text = '?'
609
610    def __init__(self, akind=None, avalue=None, arank=0, atext='?'):
611        if akind is not None:
612            self.kind = akind
613        if avalue is not None:
614            self.value = avalue
615        self.rank = arank
616        # rank is an internal gizmo (operator precedence);
617        # it's used in reconstructing formula text.
618        self.text = atext
619
620    def __repr__(self):
621        kind_text = okind_dict.get(self.kind, "?Unknown kind?")
622        return "Operand(kind=%s, value=%r, text=%r)" \
623            % (kind_text, self.value, self.text)
624
625
626class Ref3D(tuple):
627    """
628    Represents an absolute or relative 3-dimensional reference to a box
629    of one or more cells.
630
631    The ``coords`` attribute is a tuple of the form::
632
633      (shtxlo, shtxhi, rowxlo, rowxhi, colxlo, colxhi)
634
635    where ``0 <= thingxlo <= thingx < thingxhi``.
636
637    .. note::
638      It is quite possible to have ``thingx > nthings``; for example
639      ``Print_Titles`` could have ``colxhi == 256`` and/or ``rowxhi == 65536``
640      irrespective of how many columns/rows are actually used in the worksheet.
641      The caller will need to decide how to handle this situation.
642      Keyword: :class:`IndexError` :-)
643
644    The components of the coords attribute are also available as individual
645    attributes: ``shtxlo``, ``shtxhi``, ``rowxlo``, ``rowxhi``, ``colxlo``, and
646    ``colxhi``.
647
648    The ``relflags`` attribute is a 6-tuple of flags which indicate whether
649    the corresponding (sheet|row|col)(lo|hi) is relative (1) or absolute (0).
650
651    .. note::
652      There is necessarily no information available as to what cell(s)
653      the reference could possibly be relative to. The caller must decide what
654      if any use to make of ``oREL`` operands.
655
656    .. note:
657      A partially relative reference may well be a typo.
658      For example, define name ``A1Z10`` as ``$a$1:$z10`` (missing ``$`` after
659      ``z``) while the cursor is on cell ``Sheet3!A27``.
660
661      The resulting :class:`Ref3D` instance will have
662      ``coords = (2, 3, 0, -16, 0, 26)``
663      and ``relflags = (0, 0, 0, 1, 0, 0).<br>
664
665      So far, only one possibility of a sheet-relative component in
666      a reference has been noticed: a 2D reference located in the
667      "current sheet".
668
669      This will appear as ``coords = (0, 1, ...)`` and
670      ``relflags = (1, 1, ...)``.
671
672    .. versionadded:: 0.6.0
673    """
674
675    def __init__(self, atuple):
676        self.coords = atuple[0:6]
677        self.relflags = atuple[6:12]
678        if not self.relflags:
679            self.relflags = (0, 0, 0, 0, 0, 0)
680        (self.shtxlo, self.shtxhi,
681        self.rowxlo, self.rowxhi,
682        self.colxlo, self.colxhi) = self.coords
683
684    def __repr__(self):
685        if not self.relflags or self.relflags == (0, 0, 0, 0, 0, 0):
686            return "Ref3D(coords=%r)" % (self.coords, )
687        else:
688            return "Ref3D(coords=%r, relflags=%r)" \
689                % (self.coords, self.relflags)
690
691tAdd = 0x03
692tSub = 0x04
693tMul = 0x05
694tDiv = 0x06
695tPower = 0x07
696tConcat = 0x08
697tLT, tLE, tEQ, tGE, tGT, tNE = range(0x09, 0x0F)
698
699
700def nop(x):
701    return x
702
703def _opr_pow(x, y): return x ** y
704
705def _opr_lt(x, y): return x <  y
706def _opr_le(x, y): return x <= y
707def _opr_eq(x, y): return x == y
708def _opr_ge(x, y): return x >= y
709def _opr_gt(x, y): return x >  y
710def _opr_ne(x, y): return x != y
711
712def num2strg(num):
713    """
714    Attempt to emulate Excel's default conversion from number to string.
715    """
716    s = str(num)
717    if s.endswith(".0"):
718        s = s[:-2]
719    return s
720
721_arith_argdict = {oNUM: nop,     oSTRG: float}
722_cmp_argdict =   {oNUM: nop,     oSTRG: nop}
723# Seems no conversions done on relops; in Excel, "1" > 9 produces TRUE.
724_strg_argdict =  {oNUM:num2strg, oSTRG:nop}
725binop_rules = {
726    tAdd:   (_arith_argdict, oNUM, opr.add,  30, '+'),
727    tSub:   (_arith_argdict, oNUM, opr.sub,  30, '-'),
728    tMul:   (_arith_argdict, oNUM, opr.mul,  40, '*'),
729    tDiv:   (_arith_argdict, oNUM, opr.truediv,  40, '/'),
730    tPower: (_arith_argdict, oNUM, _opr_pow, 50, '^',),
731    tConcat:(_strg_argdict, oSTRG, opr.add,  20, '&'),
732    tLT:    (_cmp_argdict, oBOOL, _opr_lt,   10, '<'),
733    tLE:    (_cmp_argdict, oBOOL, _opr_le,   10, '<='),
734    tEQ:    (_cmp_argdict, oBOOL, _opr_eq,   10, '='),
735    tGE:    (_cmp_argdict, oBOOL, _opr_ge,   10, '>='),
736    tGT:    (_cmp_argdict, oBOOL, _opr_gt,   10, '>'),
737    tNE:    (_cmp_argdict, oBOOL, _opr_ne,   10, '<>'),
738}
739
740unop_rules = {
741    0x13: (lambda x: -x,        70, '-', ''), # unary minus
742    0x12: (lambda x: x,         70, '+', ''), # unary plus
743    0x14: (lambda x: x / 100.0, 60, '',  '%'),# percent
744}
745
746LEAF_RANK = 90
747FUNC_RANK = 90
748
749STACK_ALARM_LEVEL = 5
750STACK_PANIC_LEVEL = 10
751
752def evaluate_name_formula(bk, nobj, namex, blah=0, level=0):
753    if level > STACK_ALARM_LEVEL:
754        blah = 1
755    data = nobj.raw_formula
756    fmlalen = nobj.basic_formula_len
757    bv = bk.biff_version
758    reldelta = 1 # All defined name formulas use "Method B" [OOo docs]
759    if blah:
760        print("::: evaluate_name_formula %r %r %d %d %r level=%d"
761            % (namex, nobj.name, fmlalen, bv, data, level), file=bk.logfile)
762        hex_char_dump(data, 0, fmlalen, fout=bk.logfile)
763    if level > STACK_PANIC_LEVEL:
764        raise XLRDError("Excessive indirect references in NAME formula")
765    sztab = szdict[bv]
766    pos = 0
767    stack = []
768    any_rel = 0
769    any_err = 0
770    any_external = 0
771    unk_opnd = Operand(oUNK, None)
772    error_opnd = Operand(oERR, None)
773    spush = stack.append
774
775    def do_binop(opcd, stk):
776        assert len(stk) >= 2
777        bop = stk.pop()
778        aop = stk.pop()
779        argdict, result_kind, func, rank, sym = binop_rules[opcd]
780        otext = ''.join([
781            '('[:aop.rank < rank],
782            aop.text,
783            ')'[:aop.rank < rank],
784            sym,
785            '('[:bop.rank < rank],
786            bop.text,
787            ')'[:bop.rank < rank],
788        ])
789        resop = Operand(result_kind, None, rank, otext)
790        try:
791            bconv = argdict[bop.kind]
792            aconv = argdict[aop.kind]
793        except KeyError:
794            stk.append(resop)
795            return
796        if bop.value is None or aop.value is None:
797            stk.append(resop)
798            return
799        bval = bconv(bop.value)
800        aval = aconv(aop.value)
801        result = func(aval, bval)
802        if result_kind == oBOOL:
803            result = 1 if result else 0
804        resop.value = result
805        stk.append(resop)
806
807    def do_unaryop(opcode, result_kind, stk):
808        assert len(stk) >= 1
809        aop = stk.pop()
810        val = aop.value
811        func, rank, sym1, sym2 = unop_rules[opcode]
812        otext = ''.join([
813            sym1,
814            '('[:aop.rank < rank],
815            aop.text,
816            ')'[:aop.rank < rank],
817            sym2,
818        ])
819        if val is not None:
820            val = func(val)
821        stk.append(Operand(result_kind, val, rank, otext))
822
823    def not_in_name_formula(op_arg, oname_arg):
824        msg = "ERROR *** Token 0x%02x (%s) found in NAME formula" \
825              % (op_arg, oname_arg)
826        raise FormulaError(msg)
827
828    if fmlalen == 0:
829        stack = [unk_opnd]
830
831    while 0 <= pos < fmlalen:
832        op = BYTES_ORD(data[pos])
833        opcode = op & 0x1f
834        optype = (op & 0x60) >> 5
835        if optype:
836            opx = opcode + 32
837        else:
838            opx = opcode
839        oname = onames[opx] # + [" RVA"][optype]
840        sz = sztab[opx]
841        if blah:
842            print("Pos:%d Op:0x%02x Name:t%s Sz:%d opcode:%02xh optype:%02xh"
843                % (pos, op, oname, sz, opcode, optype), file=bk.logfile)
844            print("Stack =", stack, file=bk.logfile)
845        if sz == -2:
846            msg = 'ERROR *** Unexpected token 0x%02x ("%s"); biff_version=%d' \
847                % (op, oname, bv)
848            raise FormulaError(msg)
849        if not optype:
850            if 0x00 <= opcode <= 0x02: # unk_opnd, tExp, tTbl
851                not_in_name_formula(op, oname)
852            elif 0x03 <= opcode <= 0x0E:
853                # Add, Sub, Mul, Div, Power
854                # tConcat
855                # tLT, ..., tNE
856                do_binop(opcode, stack)
857            elif opcode == 0x0F: # tIsect
858                if blah: print("tIsect pre", stack, file=bk.logfile)
859                assert len(stack) >= 2
860                bop = stack.pop()
861                aop = stack.pop()
862                sym = ' '
863                rank = 80 ########## check #######
864                otext = ''.join([
865                    '('[:aop.rank < rank],
866                    aop.text,
867                    ')'[:aop.rank < rank],
868                    sym,
869                    '('[:bop.rank < rank],
870                    bop.text,
871                    ')'[:bop.rank < rank],
872                ])
873                res = Operand(oREF)
874                res.text = otext
875                if bop.kind == oERR or aop.kind == oERR:
876                    res.kind = oERR
877                elif bop.kind == oUNK or aop.kind == oUNK:
878                    # This can happen with undefined
879                    # (go search in the current sheet) labels.
880                    # For example =Bob Sales
881                    # Each label gets a NAME record with an empty formula (!)
882                    # Evaluation of the tName token classifies it as oUNK
883                    # res.kind = oREF
884                    pass
885                elif bop.kind == oREF == aop.kind:
886                    if aop.value is not None and bop.value is not None:
887                        assert len(aop.value) == 1
888                        assert len(bop.value) == 1
889                        coords = do_box_funcs(
890                            tIsectFuncs, aop.value[0], bop.value[0])
891                        res.value = [Ref3D(coords)]
892                elif bop.kind == oREL == aop.kind:
893                    res.kind = oREL
894                    if aop.value is not None and bop.value is not None:
895                        assert len(aop.value) == 1
896                        assert len(bop.value) == 1
897                        coords = do_box_funcs(
898                            tIsectFuncs, aop.value[0], bop.value[0])
899                        relfa = aop.value[0].relflags
900                        relfb = bop.value[0].relflags
901                        if relfa == relfb:
902                            res.value = [Ref3D(coords + relfa)]
903                else:
904                    pass
905                spush(res)
906                if blah: print("tIsect post", stack, file=bk.logfile)
907            elif opcode == 0x10: # tList
908                if blah: print("tList pre", stack, file=bk.logfile)
909                assert len(stack) >= 2
910                bop = stack.pop()
911                aop = stack.pop()
912                sym = ','
913                rank = 80 ########## check #######
914                otext = ''.join([
915                    '('[:aop.rank < rank],
916                    aop.text,
917                    ')'[:aop.rank < rank],
918                    sym,
919                    '('[:bop.rank < rank],
920                    bop.text,
921                    ')'[:bop.rank < rank],
922                ])
923                res = Operand(oREF, None, rank, otext)
924                if bop.kind == oERR or aop.kind == oERR:
925                    res.kind = oERR
926                elif bop.kind in (oREF, oREL) and aop.kind in (oREF, oREL):
927                    res.kind = oREF
928                    if aop.kind == oREL or bop.kind == oREL:
929                        res.kind = oREL
930                    if aop.value is not None and bop.value is not None:
931                        assert len(aop.value) >= 1
932                        assert len(bop.value) == 1
933                        res.value = aop.value + bop.value
934                else:
935                    pass
936                spush(res)
937                if blah: print("tList post", stack, file=bk.logfile)
938            elif opcode == 0x11: # tRange
939                if blah: print("tRange pre", stack, file=bk.logfile)
940                assert len(stack) >= 2
941                bop = stack.pop()
942                aop = stack.pop()
943                sym = ':'
944                rank = 80 ########## check #######
945                otext = ''.join([
946                    '('[:aop.rank < rank],
947                    aop.text,
948                    ')'[:aop.rank < rank],
949                    sym,
950                    '('[:bop.rank < rank],
951                    bop.text,
952                    ')'[:bop.rank < rank],
953                ])
954                res = Operand(oREF, None, rank, otext)
955                if bop.kind == oERR or aop.kind == oERR:
956                    res.kind = oERR
957                elif bop.kind == oREF == aop.kind:
958                    if aop.value is not None and bop.value is not None:
959                        assert len(aop.value) == 1
960                        assert len(bop.value) == 1
961                        coords = do_box_funcs(
962                            tRangeFuncs, aop.value[0], bop.value[0])
963                        res.value = [Ref3D(coords)]
964                elif bop.kind == oREL == aop.kind:
965                    res.kind = oREL
966                    if aop.value is not None and bop.value is not None:
967                        assert len(aop.value) == 1
968                        assert len(bop.value) == 1
969                        coords = do_box_funcs(
970                            tRangeFuncs, aop.value[0], bop.value[0])
971                        relfa = aop.value[0].relflags
972                        relfb = bop.value[0].relflags
973                        if relfa == relfb:
974                            res.value = [Ref3D(coords + relfa)]
975                else:
976                    pass
977                spush(res)
978                if blah: print("tRange post", stack, file=bk.logfile)
979            elif 0x12 <= opcode <= 0x14: # tUplus, tUminus, tPercent
980                do_unaryop(opcode, oNUM, stack)
981            elif opcode == 0x15: # tParen
982                # source cosmetics
983                pass
984            elif opcode == 0x16: # tMissArg
985                spush(Operand(oMSNG, None, LEAF_RANK, ''))
986            elif opcode == 0x17: # tStr
987                if bv <= 70:
988                    strg, newpos = unpack_string_update_pos(
989                                        data, pos+1, bk.encoding, lenlen=1)
990                else:
991                    strg, newpos = unpack_unicode_update_pos(
992                                        data, pos+1, lenlen=1)
993                sz = newpos - pos
994                if blah: print("   sz=%d strg=%r" % (sz, strg), file=bk.logfile)
995                text = '"' + strg.replace('"', '""') + '"'
996                spush(Operand(oSTRG, strg, LEAF_RANK, text))
997            elif opcode == 0x18: # tExtended
998                # new with BIFF 8
999                assert bv >= 80
1000                # not in OOo docs
1001                raise FormulaError("tExtended token not implemented")
1002            elif opcode == 0x19: # tAttr
1003                subop, nc = unpack("<BH", data[pos+1:pos+4])
1004                subname = tAttrNames.get(subop, "??Unknown??")
1005                if subop == 0x04: # Choose
1006                    sz = nc * 2 + 6
1007                elif subop == 0x10: # Sum (single arg)
1008                    sz = 4
1009                    if blah: print("tAttrSum", stack, file=bk.logfile)
1010                    assert len(stack) >= 1
1011                    aop = stack[-1]
1012                    otext = 'SUM(%s)' % aop.text
1013                    stack[-1] = Operand(oNUM, None, FUNC_RANK, otext)
1014                else:
1015                    sz = 4
1016                if blah:
1017                    print("   subop=%02xh subname=t%s sz=%d nc=%02xh"
1018                        % (subop, subname, sz, nc), file=bk.logfile)
1019            elif 0x1A <= opcode <= 0x1B: # tSheet, tEndSheet
1020                assert bv < 50
1021                raise FormulaError("tSheet & tEndsheet tokens not implemented")
1022            elif 0x1C <= opcode <= 0x1F: # tErr, tBool, tInt, tNum
1023                inx = opcode - 0x1C
1024                nb = [1, 1, 2, 8][inx]
1025                kind = [oERR, oBOOL, oNUM, oNUM][inx]
1026                value, = unpack("<" + "BBHd"[inx], data[pos+1:pos+1+nb])
1027                if inx == 2: # tInt
1028                    value = float(value)
1029                    text = str(value)
1030                elif inx == 3: # tNum
1031                    text = str(value)
1032                elif inx == 1: # tBool
1033                    text = ('FALSE', 'TRUE')[value]
1034                else:
1035                    text = '"' +error_text_from_code[value] + '"'
1036                spush(Operand(kind, value, LEAF_RANK, text))
1037            else:
1038                raise FormulaError("Unhandled opcode: 0x%02x" % opcode)
1039            if sz <= 0:
1040                raise FormulaError("Size not set for opcode 0x%02x" % opcode)
1041            pos += sz
1042            continue
1043        if opcode == 0x00: # tArray
1044            spush(unk_opnd)
1045        elif opcode == 0x01: # tFunc
1046            nb = 1 + int(bv >= 40)
1047            funcx = unpack("<" + " BH"[nb], data[pos+1:pos+1+nb])[0]
1048            func_attrs = func_defs.get(funcx, None)
1049            if not func_attrs:
1050                print("*** formula/tFunc unknown FuncID:%d"
1051                      % funcx, file=bk.logfile)
1052                spush(unk_opnd)
1053            else:
1054                func_name, nargs = func_attrs[:2]
1055                if blah:
1056                    print("    FuncID=%d name=%s nargs=%d"
1057                          % (funcx, func_name, nargs), file=bk.logfile)
1058                assert len(stack) >= nargs
1059                if nargs:
1060                    argtext = listsep.join(arg.text for arg in stack[-nargs:])
1061                    otext = "%s(%s)" % (func_name, argtext)
1062                    del stack[-nargs:]
1063                else:
1064                    otext = func_name + "()"
1065                res = Operand(oUNK, None, FUNC_RANK, otext)
1066                spush(res)
1067        elif opcode == 0x02: #tFuncVar
1068            nb = 1 + int(bv >= 40)
1069            nargs, funcx = unpack("<B" + " BH"[nb], data[pos+1:pos+2+nb])
1070            prompt, nargs = divmod(nargs, 128)
1071            macro, funcx = divmod(funcx, 32768)
1072            if blah:
1073                print("   FuncID=%d nargs=%d macro=%d prompt=%d"
1074                      % (funcx, nargs, macro, prompt), file=bk.logfile)
1075            func_attrs = func_defs.get(funcx, None)
1076            if not func_attrs:
1077                print("*** formula/tFuncVar unknown FuncID:%d"
1078                      % funcx, file=bk.logfile)
1079                spush(unk_opnd)
1080            else:
1081                func_name, minargs, maxargs = func_attrs[:3]
1082                if blah:
1083                    print("    name: %r, min~max args: %d~%d"
1084                        % (func_name, minargs, maxargs), file=bk.logfile)
1085                assert minargs <= nargs <= maxargs
1086                assert len(stack) >= nargs
1087                assert len(stack) >= nargs
1088                argtext = listsep.join(arg.text for arg in stack[-nargs:])
1089                otext = "%s(%s)" % (func_name, argtext)
1090                res = Operand(oUNK, None, FUNC_RANK, otext)
1091                if funcx == 1: # IF
1092                    testarg = stack[-nargs]
1093                    if testarg.kind not in (oNUM, oBOOL):
1094                        if blah and testarg.kind != oUNK:
1095                            print("IF testarg kind?", file=bk.logfile)
1096                    elif testarg.value not in (0, 1):
1097                        if blah and testarg.value is not None:
1098                            print("IF testarg value?", file=bk.logfile)
1099                    else:
1100                        if nargs == 2 and not testarg.value:
1101                            # IF(FALSE, tv) => FALSE
1102                            res.kind, res.value = oBOOL, 0
1103                        else:
1104                            respos = -nargs + 2 - int(testarg.value)
1105                            chosen = stack[respos]
1106                            if chosen.kind == oMSNG:
1107                                res.kind, res.value = oNUM, 0
1108                            else:
1109                                res.kind, res.value = chosen.kind, chosen.value
1110                        if blah:
1111                            print("$$$$$$ IF => constant", file=bk.logfile)
1112                elif funcx == 100: # CHOOSE
1113                    testarg = stack[-nargs]
1114                    if testarg.kind == oNUM:
1115                        if 1 <= testarg.value < nargs:
1116                            chosen = stack[-nargs + int(testarg.value)]
1117                            if chosen.kind == oMSNG:
1118                                res.kind, res.value = oNUM, 0
1119                            else:
1120                                res.kind, res.value = chosen.kind, chosen.value
1121                del stack[-nargs:]
1122                spush(res)
1123        elif opcode == 0x03: #tName
1124            tgtnamex = unpack("<H", data[pos+1:pos+3])[0] - 1
1125            # Only change with BIFF version is number of trailing UNUSED bytes!
1126            if blah: print("   tgtnamex=%d" % tgtnamex, file=bk.logfile)
1127            tgtobj = bk.name_obj_list[tgtnamex]
1128            if not tgtobj.evaluated:
1129                ### recursive ###
1130                evaluate_name_formula(bk, tgtobj, tgtnamex, blah, level+1)
1131            if tgtobj.macro or tgtobj.binary or tgtobj.any_err:
1132                if blah:
1133                    tgtobj.dump(
1134                        bk.logfile,
1135                        header="!!! tgtobj has problems!!!",
1136                        footer="-----------       --------",
1137                    )
1138                res = Operand(oUNK, None)
1139                any_err = any_err or tgtobj.macro or tgtobj.binary or tgtobj.any_err
1140                any_rel = any_rel or tgtobj.any_rel
1141            else:
1142                assert len(tgtobj.stack) == 1
1143                res = copy.deepcopy(tgtobj.stack[0])
1144            res.rank = LEAF_RANK
1145            if tgtobj.scope == -1:
1146                res.text = tgtobj.name
1147            else:
1148                res.text = "%s!%s" \
1149                           % (bk._sheet_names[tgtobj.scope], tgtobj.name)
1150            if blah:
1151                print("    tName: setting text to", repr(res.text), file=bk.logfile)
1152            spush(res)
1153        elif opcode == 0x04: # tRef
1154            # not_in_name_formula(op, oname)
1155            res = get_cell_addr(data, pos+1, bv, reldelta)
1156            if blah: print("  ", res, file=bk.logfile)
1157            rowx, colx, row_rel, col_rel = res
1158            shx1 = shx2 = 0 ####### N.B. relative to the CURRENT SHEET
1159            any_rel = 1
1160            coords = (shx1, shx2+1, rowx, rowx+1, colx, colx+1)
1161            if blah: print("   ", coords, file=bk.logfile)
1162            res = Operand(oUNK, None)
1163            if optype == 1:
1164                relflags = (1, 1, row_rel, row_rel, col_rel, col_rel)
1165                res = Operand(oREL, [Ref3D(coords + relflags)])
1166            spush(res)
1167        elif opcode == 0x05: # tArea
1168            # not_in_name_formula(op, oname)
1169            res1, res2 = get_cell_range_addr(data, pos+1, bv, reldelta)
1170            if blah: print("  ", res1, res2, file=bk.logfile)
1171            rowx1, colx1, row_rel1, col_rel1 = res1
1172            rowx2, colx2, row_rel2, col_rel2 = res2
1173            shx1 = shx2 = 0 ####### N.B. relative to the CURRENT SHEET
1174            any_rel = 1
1175            coords = (shx1, shx2+1, rowx1, rowx2+1, colx1, colx2+1)
1176            if blah: print("   ", coords, file=bk.logfile)
1177            res = Operand(oUNK, None)
1178            if optype == 1:
1179                relflags = (1, 1, row_rel1, row_rel2, col_rel1, col_rel2)
1180                res = Operand(oREL, [Ref3D(coords + relflags)])
1181            spush(res)
1182        elif opcode == 0x06: # tMemArea
1183            not_in_name_formula(op, oname)
1184        elif opcode == 0x09: # tMemFunc
1185            nb = unpack("<H", data[pos+1:pos+3])[0]
1186            if blah: print("  %d bytes of cell ref formula" % nb, file=bk.logfile)
1187            # no effect on stack
1188        elif opcode == 0x0C: #tRefN
1189            not_in_name_formula(op, oname)
1190            # res = get_cell_addr(data, pos+1, bv, reldelta=1)
1191            # # note *ALL* tRefN usage has signed offset for relative addresses
1192            # any_rel = 1
1193            # if blah: print >> bk.logfile, "   ", res
1194            # spush(res)
1195        elif opcode == 0x0D: #tAreaN
1196            not_in_name_formula(op, oname)
1197            # res = get_cell_range_addr(data, pos+1, bv, reldelta=1)
1198            # # note *ALL* tAreaN usage has signed offset for relative addresses
1199            # any_rel = 1
1200            # if blah: print >> bk.logfile, "   ", res
1201        elif opcode == 0x1A: # tRef3d
1202            if bv >= 80:
1203                res = get_cell_addr(data, pos+3, bv, reldelta)
1204                refx = unpack("<H", data[pos+1:pos+3])[0]
1205                shx1, shx2 = get_externsheet_local_range(bk, refx, blah)
1206            else:
1207                res = get_cell_addr(data, pos+15, bv, reldelta)
1208                raw_extshtx, raw_shx1, raw_shx2 = unpack("<hxxxxxxxxhh", data[pos+1:pos+15])
1209                if blah:
1210                    print("tRef3d", raw_extshtx, raw_shx1, raw_shx2, file=bk.logfile)
1211                shx1, shx2 = get_externsheet_local_range_b57(
1212                                bk, raw_extshtx, raw_shx1, raw_shx2, blah)
1213            rowx, colx, row_rel, col_rel = res
1214            is_rel = row_rel or col_rel
1215            any_rel = any_rel or is_rel
1216            coords = (shx1, shx2+1, rowx, rowx+1, colx, colx+1)
1217            any_err |= shx1 < -1
1218            if blah: print("   ", coords, file=bk.logfile)
1219            res = Operand(oUNK, None)
1220            if is_rel:
1221                relflags = (0, 0, row_rel, row_rel, col_rel, col_rel)
1222                ref3d = Ref3D(coords + relflags)
1223                res.kind = oREL
1224                res.text = rangename3drel(bk, ref3d, r1c1=1)
1225            else:
1226                ref3d = Ref3D(coords)
1227                res.kind = oREF
1228                res.text = rangename3d(bk, ref3d)
1229            res.rank = LEAF_RANK
1230            if optype == 1:
1231                res.value = [ref3d]
1232            spush(res)
1233        elif opcode == 0x1B: # tArea3d
1234            if bv >= 80:
1235                res1, res2 = get_cell_range_addr(data, pos+3, bv, reldelta)
1236                refx = unpack("<H", data[pos+1:pos+3])[0]
1237                shx1, shx2 = get_externsheet_local_range(bk, refx, blah)
1238            else:
1239                res1, res2 = get_cell_range_addr(data, pos+15, bv, reldelta)
1240                raw_extshtx, raw_shx1, raw_shx2 = unpack("<hxxxxxxxxhh", data[pos+1:pos+15])
1241                if blah:
1242                    print("tArea3d", raw_extshtx, raw_shx1, raw_shx2, file=bk.logfile)
1243                shx1, shx2 = get_externsheet_local_range_b57(
1244                                bk, raw_extshtx, raw_shx1, raw_shx2, blah)
1245            any_err |= shx1 < -1
1246            rowx1, colx1, row_rel1, col_rel1 = res1
1247            rowx2, colx2, row_rel2, col_rel2 = res2
1248            is_rel = row_rel1 or col_rel1 or row_rel2 or col_rel2
1249            any_rel = any_rel or is_rel
1250            coords = (shx1, shx2+1, rowx1, rowx2+1, colx1, colx2+1)
1251            if blah: print("   ", coords, file=bk.logfile)
1252            res = Operand(oUNK, None)
1253            if is_rel:
1254                relflags = (0, 0, row_rel1, row_rel2, col_rel1, col_rel2)
1255                ref3d = Ref3D(coords + relflags)
1256                res.kind = oREL
1257                res.text = rangename3drel(bk, ref3d, r1c1=1)
1258            else:
1259                ref3d = Ref3D(coords)
1260                res.kind = oREF
1261                res.text = rangename3d(bk, ref3d)
1262            res.rank = LEAF_RANK
1263            if optype == 1:
1264                res.value = [ref3d]
1265
1266            spush(res)
1267        elif opcode == 0x19: # tNameX
1268            dodgy = 0
1269            res = Operand(oUNK, None)
1270            if bv >= 80:
1271                refx, tgtnamex = unpack("<HH", data[pos+1:pos+5])
1272                tgtnamex -= 1
1273                origrefx = refx
1274            else:
1275                refx, tgtnamex = unpack("<hxxxxxxxxH", data[pos+1:pos+13])
1276                tgtnamex -= 1
1277                origrefx = refx
1278                if refx > 0:
1279                    refx -= 1
1280                elif refx < 0:
1281                    refx = -refx - 1
1282                else:
1283                    dodgy = 1
1284            if blah:
1285                print("   origrefx=%d refx=%d tgtnamex=%d dodgy=%d"
1286                    % (origrefx, refx, tgtnamex, dodgy), file=bk.logfile)
1287            if tgtnamex == namex:
1288                if blah: print("!!!! Self-referential !!!!", file=bk.logfile)
1289                dodgy = any_err = 1
1290            if not dodgy:
1291                if bv >= 80:
1292                    shx1, shx2 = get_externsheet_local_range(bk, refx, blah)
1293                elif origrefx > 0:
1294                    shx1, shx2 = (-4, -4) # external ref
1295                else:
1296                    exty = bk._externsheet_type_b57[refx]
1297                    if exty == 4: # non-specific sheet in own doc't
1298                        shx1, shx2 = (-1, -1) # internal, any sheet
1299                    else:
1300                        shx1, shx2 = (-666, -666)
1301            if dodgy or shx1 < -1:
1302                otext = "<<Name #%d in external(?) file #%d>>" \
1303                        % (tgtnamex, origrefx)
1304                res = Operand(oUNK, None, LEAF_RANK, otext)
1305            else:
1306                tgtobj = bk.name_obj_list[tgtnamex]
1307                if not tgtobj.evaluated:
1308                    ### recursive ###
1309                    evaluate_name_formula(bk, tgtobj, tgtnamex, blah, level+1)
1310                if tgtobj.macro or tgtobj.binary or tgtobj.any_err:
1311                    if blah:
1312                        tgtobj.dump(
1313                            bk.logfile,
1314                            header="!!! bad tgtobj !!!",
1315                            footer="------------------",
1316                        )
1317                    res = Operand(oUNK, None)
1318                    any_err = any_err or tgtobj.macro or tgtobj.binary or tgtobj.any_err
1319                    any_rel = any_rel or tgtobj.any_rel
1320                else:
1321                    assert len(tgtobj.stack) == 1
1322                    res = copy.deepcopy(tgtobj.stack[0])
1323                res.rank = LEAF_RANK
1324                if tgtobj.scope == -1:
1325                    res.text = tgtobj.name
1326                else:
1327                    res.text = "%s!%s" \
1328                               % (bk._sheet_names[tgtobj.scope], tgtobj.name)
1329                if blah:
1330                    print("    tNameX: setting text to", repr(res.text), file=bk.logfile)
1331            spush(res)
1332        elif opcode in error_opcodes:
1333            any_err = 1
1334            spush(error_opnd)
1335        else:
1336            if blah:
1337                print("FORMULA: /// Not handled yet: t" + oname, file=bk.logfile)
1338            any_err = 1
1339        if sz <= 0:
1340            raise FormulaError("Fatal: token size is not positive")
1341        pos += sz
1342    any_rel = not not any_rel
1343    if blah:
1344        fprintf(bk.logfile, "End of formula. level=%d any_rel=%d any_err=%d stack=%r\n",
1345            level, not not any_rel, any_err, stack)
1346        if len(stack) >= 2:
1347            print("*** Stack has unprocessed args", file=bk.logfile)
1348        print(file=bk.logfile)
1349    nobj.stack = stack
1350    if len(stack) != 1:
1351        nobj.result = None
1352    else:
1353        nobj.result = stack[0]
1354    nobj.any_rel = any_rel
1355    nobj.any_err = any_err
1356    nobj.any_external = any_external
1357    nobj.evaluated = 1
1358
1359#### under construction #############################################################################
1360def decompile_formula(bk, fmla, fmlalen,
1361                      fmlatype=None, browx=None, bcolx=None,
1362                      blah=0, level=0, r1c1=0):
1363    if level > STACK_ALARM_LEVEL:
1364        blah = 1
1365    reldelta = fmlatype in (FMLA_TYPE_SHARED, FMLA_TYPE_NAME, FMLA_TYPE_COND_FMT, FMLA_TYPE_DATA_VAL)
1366    data = fmla
1367    bv = bk.biff_version
1368    if blah:
1369        print("::: decompile_formula len=%d fmlatype=%r browx=%r bcolx=%r reldelta=%d %r level=%d"
1370            % (fmlalen, fmlatype, browx, bcolx, reldelta, data, level), file=bk.logfile)
1371        hex_char_dump(data, 0, fmlalen, fout=bk.logfile)
1372    if level > STACK_PANIC_LEVEL:
1373        raise XLRDError("Excessive indirect references in formula")
1374    sztab = szdict[bv]
1375    pos = 0
1376    stack = []
1377    any_rel = 0
1378    any_err = 0
1379    unk_opnd = Operand(oUNK, None)
1380    error_opnd = Operand(oERR, None)
1381    spush = stack.append
1382
1383    def do_binop(opcd, stk):
1384        assert len(stk) >= 2
1385        bop = stk.pop()
1386        aop = stk.pop()
1387        argdict, result_kind, func, rank, sym = binop_rules[opcd]
1388        otext = ''.join([
1389            '('[:aop.rank < rank],
1390            aop.text,
1391            ')'[:aop.rank < rank],
1392            sym,
1393            '('[:bop.rank < rank],
1394            bop.text,
1395            ')'[:bop.rank < rank],
1396        ])
1397        resop = Operand(result_kind, None, rank, otext)
1398        stk.append(resop)
1399
1400    def do_unaryop(opcode, result_kind, stk):
1401        assert len(stk) >= 1
1402        aop = stk.pop()
1403        func, rank, sym1, sym2 = unop_rules[opcode]
1404        otext = ''.join([
1405            sym1,
1406            '('[:aop.rank < rank],
1407            aop.text,
1408            ')'[:aop.rank < rank],
1409            sym2,
1410        ])
1411        stk.append(Operand(result_kind, None, rank, otext))
1412
1413    def unexpected_opcode(op_arg, oname_arg):
1414        msg = "ERROR *** Unexpected token 0x%02x (%s) found in formula type %s" \
1415              % (op_arg, oname_arg, FMLA_TYPEDESCR_MAP[fmlatype])
1416        print(msg, file=bk.logfile)
1417        # raise FormulaError(msg)
1418
1419    if fmlalen == 0:
1420        stack = [unk_opnd]
1421
1422    while 0 <= pos < fmlalen:
1423        op = BYTES_ORD(data[pos])
1424        opcode = op & 0x1f
1425        optype = (op & 0x60) >> 5
1426        if optype:
1427            opx = opcode + 32
1428        else:
1429            opx = opcode
1430        oname = onames[opx] # + [" RVA"][optype]
1431        sz = sztab[opx]
1432        if blah:
1433            print("Pos:%d Op:0x%02x opname:t%s Sz:%d opcode:%02xh optype:%02xh"
1434                % (pos, op, oname, sz, opcode, optype), file=bk.logfile)
1435            print("Stack =", stack, file=bk.logfile)
1436        if sz == -2:
1437            msg = 'ERROR *** Unexpected token 0x%02x ("%s"); biff_version=%d' \
1438                % (op, oname, bv)
1439            raise FormulaError(msg)
1440        if _TOKEN_NOT_ALLOWED(opx, 0) & fmlatype:
1441            unexpected_opcode(op, oname)
1442        if not optype:
1443            if opcode <= 0x01: # tExp
1444                if bv >= 30:
1445                    fmt = '<x2H'
1446                else:
1447                    fmt = '<xHB'
1448                assert pos == 0 and fmlalen == sz and not stack
1449                rowx, colx = unpack(fmt, data)
1450                text = "SHARED FMLA at rowx=%d colx=%d" % (rowx, colx)
1451                spush(Operand(oUNK, None, LEAF_RANK, text))
1452                if not fmlatype & (FMLA_TYPE_CELL | FMLA_TYPE_ARRAY):
1453                    unexpected_opcode(op, oname)
1454            elif 0x03 <= opcode <= 0x0E:
1455                # Add, Sub, Mul, Div, Power
1456                # tConcat
1457                # tLT, ..., tNE
1458                do_binop(opcode, stack)
1459            elif opcode == 0x0F: # tIsect
1460                if blah: print("tIsect pre", stack, file=bk.logfile)
1461                assert len(stack) >= 2
1462                bop = stack.pop()
1463                aop = stack.pop()
1464                sym = ' '
1465                rank = 80 ########## check #######
1466                otext = ''.join([
1467                    '('[:aop.rank < rank],
1468                    aop.text,
1469                    ')'[:aop.rank < rank],
1470                    sym,
1471                    '('[:bop.rank < rank],
1472                    bop.text,
1473                    ')'[:bop.rank < rank],
1474                ])
1475                res = Operand(oREF)
1476                res.text = otext
1477                if bop.kind == oERR or aop.kind == oERR:
1478                    res.kind = oERR
1479                elif bop.kind == oUNK or aop.kind == oUNK:
1480                    # This can happen with undefined
1481                    # (go search in the current sheet) labels.
1482                    # For example =Bob Sales
1483                    # Each label gets a NAME record with an empty formula (!)
1484                    # Evaluation of the tName token classifies it as oUNK
1485                    # res.kind = oREF
1486                    pass
1487                elif bop.kind == oREF == aop.kind:
1488                    pass
1489                elif bop.kind == oREL == aop.kind:
1490                    res.kind = oREL
1491                else:
1492                    pass
1493                spush(res)
1494                if blah: print("tIsect post", stack, file=bk.logfile)
1495            elif opcode == 0x10: # tList
1496                if blah: print("tList pre", stack, file=bk.logfile)
1497                assert len(stack) >= 2
1498                bop = stack.pop()
1499                aop = stack.pop()
1500                sym = ','
1501                rank = 80 ########## check #######
1502                otext = ''.join([
1503                    '('[:aop.rank < rank],
1504                    aop.text,
1505                    ')'[:aop.rank < rank],
1506                    sym,
1507                    '('[:bop.rank < rank],
1508                    bop.text,
1509                    ')'[:bop.rank < rank],
1510                ])
1511                res = Operand(oREF, None, rank, otext)
1512                if bop.kind == oERR or aop.kind == oERR:
1513                    res.kind = oERR
1514                elif bop.kind in (oREF, oREL) and aop.kind in (oREF, oREL):
1515                    res.kind = oREF
1516                    if aop.kind == oREL or bop.kind == oREL:
1517                        res.kind = oREL
1518                else:
1519                    pass
1520                spush(res)
1521                if blah: print("tList post", stack, file=bk.logfile)
1522            elif opcode == 0x11: # tRange
1523                if blah: print("tRange pre", stack, file=bk.logfile)
1524                assert len(stack) >= 2
1525                bop = stack.pop()
1526                aop = stack.pop()
1527                sym = ':'
1528                rank = 80 ########## check #######
1529                otext = ''.join([
1530                    '('[:aop.rank < rank],
1531                    aop.text,
1532                    ')'[:aop.rank < rank],
1533                    sym,
1534                    '('[:bop.rank < rank],
1535                    bop.text,
1536                    ')'[:bop.rank < rank],
1537                ])
1538                res = Operand(oREF, None, rank, otext)
1539                if bop.kind == oERR or aop.kind == oERR:
1540                    res = oERR
1541                elif bop.kind == oREF == aop.kind:
1542                    pass
1543                else:
1544                    pass
1545                spush(res)
1546                if blah: print("tRange post", stack, file=bk.logfile)
1547            elif 0x12 <= opcode <= 0x14: # tUplus, tUminus, tPercent
1548                do_unaryop(opcode, oNUM, stack)
1549            elif opcode == 0x15: # tParen
1550                # source cosmetics
1551                pass
1552            elif opcode == 0x16: # tMissArg
1553                spush(Operand(oMSNG, None, LEAF_RANK, ''))
1554            elif opcode == 0x17: # tStr
1555                if bv <= 70:
1556                    strg, newpos = unpack_string_update_pos(
1557                                        data, pos+1, bk.encoding, lenlen=1)
1558                else:
1559                    strg, newpos = unpack_unicode_update_pos(
1560                                        data, pos+1, lenlen=1)
1561                sz = newpos - pos
1562                if blah: print("   sz=%d strg=%r" % (sz, strg), file=bk.logfile)
1563                text = '"' + strg.replace('"', '""') + '"'
1564                spush(Operand(oSTRG, None, LEAF_RANK, text))
1565            elif opcode == 0x18: # tExtended
1566                # new with BIFF 8
1567                assert bv >= 80
1568                # not in OOo docs, don't even know how to determine its length
1569                raise FormulaError("tExtended token not implemented")
1570            elif opcode == 0x19: # tAttr
1571                subop, nc = unpack("<BH", data[pos+1:pos+4])
1572                subname = tAttrNames.get(subop, "??Unknown??")
1573                if subop == 0x04: # Choose
1574                    sz = nc * 2 + 6
1575                elif subop == 0x10: # Sum (single arg)
1576                    sz = 4
1577                    if blah: print("tAttrSum", stack, file=bk.logfile)
1578                    assert len(stack) >= 1
1579                    aop = stack[-1]
1580                    otext = 'SUM(%s)' % aop.text
1581                    stack[-1] = Operand(oNUM, None, FUNC_RANK, otext)
1582                else:
1583                    sz = 4
1584                if blah:
1585                    print("   subop=%02xh subname=t%s sz=%d nc=%02xh"
1586                        % (subop, subname, sz, nc), file=bk.logfile)
1587            elif 0x1A <= opcode <= 0x1B: # tSheet, tEndSheet
1588                assert bv < 50
1589                raise FormulaError("tSheet & tEndsheet tokens not implemented")
1590            elif 0x1C <= opcode <= 0x1F: # tErr, tBool, tInt, tNum
1591                inx = opcode - 0x1C
1592                nb = [1, 1, 2, 8][inx]
1593                kind = [oERR, oBOOL, oNUM, oNUM][inx]
1594                value, = unpack("<" + "BBHd"[inx], data[pos+1:pos+1+nb])
1595                if inx == 2: # tInt
1596                    value = float(value)
1597                    text = str(value)
1598                elif inx == 3: # tNum
1599                    text = str(value)
1600                elif inx == 1: # tBool
1601                    text = ('FALSE', 'TRUE')[value]
1602                else:
1603                    text = '"' +error_text_from_code[value] + '"'
1604                spush(Operand(kind, None, LEAF_RANK, text))
1605            else:
1606                raise FormulaError("Unhandled opcode: 0x%02x" % opcode)
1607            if sz <= 0:
1608                raise FormulaError("Size not set for opcode 0x%02x" % opcode)
1609            pos += sz
1610            continue
1611        if opcode == 0x00: # tArray
1612            spush(unk_opnd)
1613        elif opcode == 0x01: # tFunc
1614            nb = 1 + int(bv >= 40)
1615            funcx = unpack("<" + " BH"[nb], data[pos+1:pos+1+nb])[0]
1616            func_attrs = func_defs.get(funcx, None)
1617            if not func_attrs:
1618                print("*** formula/tFunc unknown FuncID:%d" % funcx, file=bk.logfile)
1619                spush(unk_opnd)
1620            else:
1621                func_name, nargs = func_attrs[:2]
1622                if blah:
1623                    print("    FuncID=%d name=%s nargs=%d"
1624                          % (funcx, func_name, nargs), file=bk.logfile)
1625                assert len(stack) >= nargs
1626                if nargs:
1627                    argtext = listsep.join(arg.text for arg in stack[-nargs:])
1628                    otext = "%s(%s)" % (func_name, argtext)
1629                    del stack[-nargs:]
1630                else:
1631                    otext = func_name + "()"
1632                res = Operand(oUNK, None, FUNC_RANK, otext)
1633                spush(res)
1634        elif opcode == 0x02: #tFuncVar
1635            nb = 1 + int(bv >= 40)
1636            nargs, funcx = unpack("<B" + " BH"[nb], data[pos+1:pos+2+nb])
1637            prompt, nargs = divmod(nargs, 128)
1638            macro, funcx = divmod(funcx, 32768)
1639            if blah:
1640                print("   FuncID=%d nargs=%d macro=%d prompt=%d"
1641                      % (funcx, nargs, macro, prompt), file=bk.logfile)
1642            #### TODO #### if funcx == 255: # call add-in function
1643            if funcx == 255:
1644                func_attrs = ("CALL_ADDIN", 1, 30)
1645            else:
1646                func_attrs = func_defs.get(funcx, None)
1647            if not func_attrs:
1648                print("*** formula/tFuncVar unknown FuncID:%d"
1649                      % funcx, file=bk.logfile)
1650                spush(unk_opnd)
1651            else:
1652                func_name, minargs, maxargs = func_attrs[:3]
1653                if blah:
1654                    print("    name: %r, min~max args: %d~%d"
1655                        % (func_name, minargs, maxargs), file=bk.logfile)
1656                assert minargs <= nargs <= maxargs
1657                assert len(stack) >= nargs
1658                assert len(stack) >= nargs
1659                argtext = listsep.join(arg.text for arg in stack[-nargs:])
1660                otext = "%s(%s)" % (func_name, argtext)
1661                res = Operand(oUNK, None, FUNC_RANK, otext)
1662                del stack[-nargs:]
1663                spush(res)
1664        elif opcode == 0x03: #tName
1665            tgtnamex = unpack("<H", data[pos+1:pos+3])[0] - 1
1666            # Only change with BIFF version is number of trailing UNUSED bytes!
1667            if blah: print("   tgtnamex=%d" % tgtnamex, file=bk.logfile)
1668            tgtobj = bk.name_obj_list[tgtnamex]
1669            if tgtobj.scope == -1:
1670                otext = tgtobj.name
1671            else:
1672                otext = "%s!%s" % (bk._sheet_names[tgtobj.scope], tgtobj.name)
1673            if blah:
1674                print("    tName: setting text to", repr(otext), file=bk.logfile)
1675            res = Operand(oUNK, None, LEAF_RANK, otext)
1676            spush(res)
1677        elif opcode == 0x04: # tRef
1678            res = get_cell_addr(data, pos+1, bv, reldelta, browx, bcolx)
1679            if blah: print("  ", res, file=bk.logfile)
1680            rowx, colx, row_rel, col_rel = res
1681            is_rel = row_rel or col_rel
1682            if is_rel:
1683                okind = oREL
1684            else:
1685                okind = oREF
1686            otext = cellnamerel(rowx, colx, row_rel, col_rel, browx, bcolx, r1c1)
1687            res = Operand(okind, None, LEAF_RANK, otext)
1688            spush(res)
1689        elif opcode == 0x05: # tArea
1690            res1, res2 = get_cell_range_addr(
1691                            data, pos+1, bv, reldelta, browx, bcolx)
1692            if blah: print("  ", res1, res2, file=bk.logfile)
1693            rowx1, colx1, row_rel1, col_rel1 = res1
1694            rowx2, colx2, row_rel2, col_rel2 = res2
1695            coords = (rowx1, rowx2+1, colx1, colx2+1)
1696            relflags = (row_rel1, row_rel2, col_rel1, col_rel2)
1697            if sum(relflags):  # relative
1698                okind = oREL
1699            else:
1700                okind = oREF
1701            if blah: print("   ", coords, relflags, file=bk.logfile)
1702            otext = rangename2drel(coords, relflags, browx, bcolx, r1c1)
1703            res = Operand(okind, None, LEAF_RANK, otext)
1704            spush(res)
1705        elif opcode == 0x06: # tMemArea
1706            not_in_name_formula(op, oname)
1707        elif opcode == 0x09: # tMemFunc
1708            nb = unpack("<H", data[pos+1:pos+3])[0]
1709            if blah: print("  %d bytes of cell ref formula" % nb, file=bk.logfile)
1710            # no effect on stack
1711        elif opcode == 0x0C: #tRefN
1712            res = get_cell_addr(data, pos+1, bv, reldelta, browx, bcolx)
1713            # note *ALL* tRefN usage has signed offset for relative addresses
1714            any_rel = 1
1715            if blah: print("   ", res, file=bk.logfile)
1716            rowx, colx, row_rel, col_rel = res
1717            is_rel = row_rel or col_rel
1718            if is_rel:
1719                okind = oREL
1720            else:
1721                okind = oREF
1722            otext = cellnamerel(rowx, colx, row_rel, col_rel, browx, bcolx, r1c1)
1723            res = Operand(okind, None, LEAF_RANK, otext)
1724            spush(res)
1725        elif opcode == 0x0D: #tAreaN
1726            # res = get_cell_range_addr(data, pos+1, bv, reldelta, browx, bcolx)
1727            # # note *ALL* tAreaN usage has signed offset for relative addresses
1728            # any_rel = 1
1729            # if blah: print >> bk.logfile, "   ", res
1730            res1, res2 = get_cell_range_addr(
1731                            data, pos+1, bv, reldelta, browx, bcolx)
1732            if blah: print("  ", res1, res2, file=bk.logfile)
1733            rowx1, colx1, row_rel1, col_rel1 = res1
1734            rowx2, colx2, row_rel2, col_rel2 = res2
1735            coords = (rowx1, rowx2+1, colx1, colx2+1)
1736            relflags = (row_rel1, row_rel2, col_rel1, col_rel2)
1737            if sum(relflags):  # relative
1738                okind = oREL
1739            else:
1740                okind = oREF
1741            if blah: print("   ", coords, relflags, file=bk.logfile)
1742            otext = rangename2drel(coords, relflags, browx, bcolx, r1c1)
1743            res = Operand(okind, None, LEAF_RANK, otext)
1744            spush(res)
1745        elif opcode == 0x1A: # tRef3d
1746            if bv >= 80:
1747                res = get_cell_addr(data, pos+3, bv, reldelta, browx, bcolx)
1748                refx = unpack("<H", data[pos+1:pos+3])[0]
1749                shx1, shx2 = get_externsheet_local_range(bk, refx, blah)
1750            else:
1751                res = get_cell_addr(data, pos+15, bv, reldelta, browx, bcolx)
1752                raw_extshtx, raw_shx1, raw_shx2 = unpack("<hxxxxxxxxhh", data[pos+1:pos+15])
1753                if blah:
1754                    print("tRef3d", raw_extshtx, raw_shx1, raw_shx2, file=bk.logfile)
1755                shx1, shx2 = get_externsheet_local_range_b57(
1756                                bk, raw_extshtx, raw_shx1, raw_shx2, blah)
1757            rowx, colx, row_rel, col_rel = res
1758            is_rel = row_rel or col_rel
1759            any_rel = any_rel or is_rel
1760            coords = (shx1, shx2+1, rowx, rowx+1, colx, colx+1)
1761            any_err |= shx1 < -1
1762            if blah: print("   ", coords, file=bk.logfile)
1763            res = Operand(oUNK, None)
1764            if is_rel:
1765                relflags = (0, 0, row_rel, row_rel, col_rel, col_rel)
1766                ref3d = Ref3D(coords + relflags)
1767                res.kind = oREL
1768                res.text = rangename3drel(bk, ref3d, browx, bcolx, r1c1)
1769            else:
1770                ref3d = Ref3D(coords)
1771                res.kind = oREF
1772                res.text = rangename3d(bk, ref3d)
1773            res.rank = LEAF_RANK
1774            res.value = None
1775            spush(res)
1776        elif opcode == 0x1B: # tArea3d
1777            if bv >= 80:
1778                res1, res2 = get_cell_range_addr(data, pos+3, bv, reldelta)
1779                refx = unpack("<H", data[pos+1:pos+3])[0]
1780                shx1, shx2 = get_externsheet_local_range(bk, refx, blah)
1781            else:
1782                res1, res2 = get_cell_range_addr(data, pos+15, bv, reldelta)
1783                raw_extshtx, raw_shx1, raw_shx2 = unpack("<hxxxxxxxxhh", data[pos+1:pos+15])
1784                if blah:
1785                    print("tArea3d", raw_extshtx, raw_shx1, raw_shx2, file=bk.logfile)
1786                shx1, shx2 = get_externsheet_local_range_b57(
1787                                bk, raw_extshtx, raw_shx1, raw_shx2, blah)
1788            any_err |= shx1 < -1
1789            rowx1, colx1, row_rel1, col_rel1 = res1
1790            rowx2, colx2, row_rel2, col_rel2 = res2
1791            is_rel = row_rel1 or col_rel1 or row_rel2 or col_rel2
1792            any_rel = any_rel or is_rel
1793            coords = (shx1, shx2+1, rowx1, rowx2+1, colx1, colx2+1)
1794            if blah: print("   ", coords, file=bk.logfile)
1795            res = Operand(oUNK, None)
1796            if is_rel:
1797                relflags = (0, 0, row_rel1, row_rel2, col_rel1, col_rel2)
1798                ref3d = Ref3D(coords + relflags)
1799                res.kind = oREL
1800                res.text = rangename3drel(bk, ref3d, browx, bcolx, r1c1)
1801            else:
1802                ref3d = Ref3D(coords)
1803                res.kind = oREF
1804                res.text = rangename3d(bk, ref3d)
1805            res.rank = LEAF_RANK
1806            spush(res)
1807        elif opcode == 0x19: # tNameX
1808            dodgy = 0
1809            res = Operand(oUNK, None)
1810            if bv >= 80:
1811                refx, tgtnamex = unpack("<HH", data[pos+1:pos+5])
1812                tgtnamex -= 1
1813                origrefx = refx
1814            else:
1815                refx, tgtnamex = unpack("<hxxxxxxxxH", data[pos+1:pos+13])
1816                tgtnamex -= 1
1817                origrefx = refx
1818                if refx > 0:
1819                    refx -= 1
1820                elif refx < 0:
1821                    refx = -refx - 1
1822                else:
1823                    dodgy = 1
1824            if blah:
1825                print("   origrefx=%d refx=%d tgtnamex=%d dodgy=%d"
1826                    % (origrefx, refx, tgtnamex, dodgy), file=bk.logfile)
1827            # if tgtnamex == namex:
1828            #     if blah: print >> bk.logfile, "!!!! Self-referential !!!!"
1829            #     dodgy = any_err = 1
1830            if not dodgy:
1831                if bv >= 80:
1832                    shx1, shx2 = get_externsheet_local_range(bk, refx, blah)
1833                elif origrefx > 0:
1834                    shx1, shx2 = (-4, -4) # external ref
1835                else:
1836                    exty = bk._externsheet_type_b57[refx]
1837                    if exty == 4: # non-specific sheet in own doc't
1838                        shx1, shx2 = (-1, -1) # internal, any sheet
1839                    else:
1840                        shx1, shx2 = (-666, -666)
1841            okind = oUNK
1842            ovalue = None
1843            if shx1 == -5: # addin func name
1844                okind = oSTRG
1845                ovalue = bk.addin_func_names[tgtnamex]
1846                otext = '"' + ovalue.replace('"', '""') + '"'
1847            elif dodgy or shx1 < -1:
1848                otext = "<<Name #%d in external(?) file #%d>>" \
1849                        % (tgtnamex, origrefx)
1850            else:
1851                tgtobj = bk.name_obj_list[tgtnamex]
1852                if tgtobj.scope == -1:
1853                    otext = tgtobj.name
1854                else:
1855                    otext = "%s!%s" \
1856                            % (bk._sheet_names[tgtobj.scope], tgtobj.name)
1857                if blah:
1858                    print("    tNameX: setting text to", repr(res.text), file=bk.logfile)
1859            res = Operand(okind, ovalue, LEAF_RANK, otext)
1860            spush(res)
1861        elif opcode in error_opcodes:
1862            any_err = 1
1863            spush(error_opnd)
1864        else:
1865            if blah:
1866                print("FORMULA: /// Not handled yet: t" + oname, file=bk.logfile)
1867            any_err = 1
1868        if sz <= 0:
1869            raise FormulaError("Fatal: token size is not positive")
1870        pos += sz
1871    any_rel = not not any_rel
1872    if blah:
1873        print("End of formula. level=%d any_rel=%d any_err=%d stack=%r" %
1874            (level, not not any_rel, any_err, stack), file=bk.logfile)
1875        if len(stack) >= 2:
1876            print("*** Stack has unprocessed args", file=bk.logfile)
1877        print(file=bk.logfile)
1878
1879    if len(stack) != 1:
1880        result = None
1881    else:
1882        result = stack[0].text
1883    return result
1884
1885#### under deconstruction ###
1886def dump_formula(bk, data, fmlalen, bv, reldelta, blah=0, isname=0):
1887    if blah:
1888        print("dump_formula", fmlalen, bv, len(data), file=bk.logfile)
1889        hex_char_dump(data, 0, fmlalen, fout=bk.logfile)
1890    assert bv >= 80 #### this function needs updating ####
1891    sztab = szdict[bv]
1892    pos = 0
1893    stack = []
1894    any_rel = 0
1895    any_err = 0
1896    spush = stack.append
1897    while 0 <= pos < fmlalen:
1898        op = BYTES_ORD(data[pos])
1899        opcode = op & 0x1f
1900        optype = (op & 0x60) >> 5
1901        if optype:
1902            opx = opcode + 32
1903        else:
1904            opx = opcode
1905        oname = onames[opx] # + [" RVA"][optype]
1906
1907        sz = sztab[opx]
1908        if blah:
1909            print("Pos:%d Op:0x%02x Name:t%s Sz:%d opcode:%02xh optype:%02xh"
1910                % (pos, op, oname, sz, opcode, optype), file=bk.logfile)
1911        if not optype:
1912            if 0x01 <= opcode <= 0x02: # tExp, tTbl
1913                # reference to a shared formula or table record
1914                rowx, colx = unpack("<HH", data[pos+1:pos+5])
1915                if blah: print("  ", (rowx, colx), file=bk.logfile)
1916            elif opcode == 0x10: # tList
1917                if blah: print("tList pre", stack, file=bk.logfile)
1918                assert len(stack) >= 2
1919                bop = stack.pop()
1920                aop = stack.pop()
1921                spush(aop + bop)
1922                if blah: print("tlist post", stack, file=bk.logfile)
1923            elif opcode == 0x11: # tRange
1924                if blah: print("tRange pre", stack, file=bk.logfile)
1925                assert len(stack) >= 2
1926                bop = stack.pop()
1927                aop = stack.pop()
1928                assert len(aop) == 1
1929                assert len(bop) == 1
1930                result = do_box_funcs(tRangeFuncs, aop[0], bop[0])
1931                spush(result)
1932                if blah: print("tRange post", stack, file=bk.logfile)
1933            elif opcode == 0x0F: # tIsect
1934                if blah: print("tIsect pre", stack, file=bk.logfile)
1935                assert len(stack) >= 2
1936                bop = stack.pop()
1937                aop = stack.pop()
1938                assert len(aop) == 1
1939                assert len(bop) == 1
1940                result = do_box_funcs(tIsectFuncs, aop[0], bop[0])
1941                spush(result)
1942                if blah: print("tIsect post", stack, file=bk.logfile)
1943            elif opcode == 0x19: # tAttr
1944                subop, nc = unpack("<BH", data[pos+1:pos+4])
1945                subname = tAttrNames.get(subop, "??Unknown??")
1946                if subop == 0x04: # Choose
1947                    sz = nc * 2 + 6
1948                else:
1949                    sz = 4
1950                if blah: print("   subop=%02xh subname=t%s sz=%d nc=%02xh" % (subop, subname, sz, nc), file=bk.logfile)
1951            elif opcode == 0x17: # tStr
1952                if bv <= 70:
1953                    nc = BYTES_ORD(data[pos+1])
1954                    strg = data[pos+2:pos+2+nc] # left in 8-bit encoding
1955                    sz = nc + 2
1956                else:
1957                    strg, newpos = unpack_unicode_update_pos(data, pos+1, lenlen=1)
1958                    sz = newpos - pos
1959                if blah: print("   sz=%d strg=%r" % (sz, strg), file=bk.logfile)
1960            else:
1961                if sz <= 0:
1962                    print("**** Dud size; exiting ****", file=bk.logfile)
1963                    return
1964            pos += sz
1965            continue
1966        if opcode == 0x00: # tArray
1967            pass
1968        elif opcode == 0x01: # tFunc
1969            nb = 1 + int(bv >= 40)
1970            funcx = unpack("<" + " BH"[nb], data[pos+1:pos+1+nb])
1971            if blah: print("   FuncID=%d" % funcx, file=bk.logfile)
1972        elif opcode == 0x02: #tFuncVar
1973            nb = 1 + int(bv >= 40)
1974            nargs, funcx = unpack("<B" + " BH"[nb], data[pos+1:pos+2+nb])
1975            prompt, nargs = divmod(nargs, 128)
1976            macro, funcx = divmod(funcx, 32768)
1977            if blah: print("   FuncID=%d nargs=%d macro=%d prompt=%d" % (funcx, nargs, macro, prompt), file=bk.logfile)
1978        elif opcode == 0x03: #tName
1979            namex = unpack("<H", data[pos+1:pos+3])
1980            # Only change with BIFF version is the number of trailing UNUSED bytes!!!
1981            if blah: print("   namex=%d" % namex, file=bk.logfile)
1982        elif opcode == 0x04: # tRef
1983            res = get_cell_addr(data, pos+1, bv, reldelta)
1984            if blah: print("  ", res, file=bk.logfile)
1985        elif opcode == 0x05: # tArea
1986            res = get_cell_range_addr(data, pos+1, bv, reldelta)
1987            if blah: print("  ", res, file=bk.logfile)
1988        elif opcode == 0x09: # tMemFunc
1989            nb = unpack("<H", data[pos+1:pos+3])[0]
1990            if blah: print("  %d bytes of cell ref formula" % nb, file=bk.logfile)
1991        elif opcode == 0x0C: #tRefN
1992            res = get_cell_addr(data, pos+1, bv, reldelta=1)
1993            # note *ALL* tRefN usage has signed offset for relative addresses
1994            any_rel = 1
1995            if blah: print("   ", res, file=bk.logfile)
1996        elif opcode == 0x0D: #tAreaN
1997            res = get_cell_range_addr(data, pos+1, bv, reldelta=1)
1998            # note *ALL* tAreaN usage has signed offset for relative addresses
1999            any_rel = 1
2000            if blah: print("   ", res, file=bk.logfile)
2001        elif opcode == 0x1A: # tRef3d
2002            refx = unpack("<H", data[pos+1:pos+3])[0]
2003            res = get_cell_addr(data, pos+3, bv, reldelta)
2004            if blah: print("  ", refx, res, file=bk.logfile)
2005            rowx, colx, row_rel, col_rel = res
2006            any_rel = any_rel or row_rel or col_rel
2007            shx1, shx2 = get_externsheet_local_range(bk, refx, blah)
2008            any_err |= shx1 < -1
2009            coords = (shx1, shx2+1, rowx, rowx+1, colx, colx+1)
2010            if blah: print("   ", coords, file=bk.logfile)
2011            if optype == 1: spush([coords])
2012        elif opcode == 0x1B: # tArea3d
2013            refx = unpack("<H", data[pos+1:pos+3])[0]
2014            res1, res2 = get_cell_range_addr(data, pos+3, bv, reldelta)
2015            if blah: print("  ", refx, res1, res2, file=bk.logfile)
2016            rowx1, colx1, row_rel1, col_rel1 = res1
2017            rowx2, colx2, row_rel2, col_rel2 = res2
2018            any_rel = any_rel or row_rel1 or col_rel1 or row_rel2 or col_rel2
2019            shx1, shx2 = get_externsheet_local_range(bk, refx, blah)
2020            any_err |= shx1 < -1
2021            coords = (shx1, shx2+1, rowx1, rowx2+1, colx1, colx2+1)
2022            if blah: print("   ", coords, file=bk.logfile)
2023            if optype == 1: spush([coords])
2024        elif opcode == 0x19: # tNameX
2025            refx, namex = unpack("<HH", data[pos+1:pos+5])
2026            if blah: print("   refx=%d namex=%d" % (refx, namex), file=bk.logfile)
2027        elif opcode in error_opcodes:
2028            any_err = 1
2029        else:
2030            if blah: print("FORMULA: /// Not handled yet: t" + oname, file=bk.logfile)
2031            any_err = 1
2032        if sz <= 0:
2033            print("**** Dud size; exiting ****", file=bk.logfile)
2034            return
2035        pos += sz
2036    if blah:
2037        print("End of formula. any_rel=%d any_err=%d stack=%r" %
2038            (not not any_rel, any_err, stack), file=bk.logfile)
2039        if len(stack) >= 2:
2040            print("*** Stack has unprocessed args", file=bk.logfile)
2041
2042# === Some helper functions for displaying cell references ===
2043
2044# I'm aware of only one possibility of a sheet-relative component in
2045# a reference: a 2D reference located in the "current sheet".
2046# xlrd stores this internally with bounds of (0, 1, ...) and
2047# relative flags of (1, 1, ...). These functions display the
2048# sheet component as empty, just like Excel etc.
2049
2050def rownamerel(rowx, rowxrel, browx=None, r1c1=0):
2051    # if no base rowx is provided, we have to return r1c1
2052    if browx is None:
2053        r1c1 = True
2054    if not rowxrel:
2055        if r1c1:
2056            return "R%d" % (rowx+1)
2057        return "$%d" % (rowx+1)
2058    if r1c1:
2059        if rowx:
2060            return "R[%d]" % rowx
2061        return "R"
2062    return "%d" % ((browx + rowx) % 65536 + 1)
2063
2064def colnamerel(colx, colxrel, bcolx=None, r1c1=0):
2065    # if no base colx is provided, we have to return r1c1
2066    if bcolx is None:
2067        r1c1 = True
2068    if not colxrel:
2069        if r1c1:
2070            return "C%d" % (colx + 1)
2071        return "$" + colname(colx)
2072    if r1c1:
2073        if colx:
2074            return "C[%d]" % colx
2075        return "C"
2076    return colname((bcolx + colx) % 256)
2077
2078def cellname(rowx, colx):
2079    """Utility function: ``(5, 7)`` => ``'H6'``"""
2080    return "%s%d" % (colname(colx), rowx+1)
2081
2082def cellnameabs(rowx, colx, r1c1=0):
2083    """Utility function: ``(5, 7)`` => ``'$H$6'``"""
2084    if r1c1:
2085        return "R%dC%d" % (rowx+1, colx+1)
2086    return "$%s$%d" % (colname(colx), rowx+1)
2087
2088def cellnamerel(rowx, colx, rowxrel, colxrel, browx=None, bcolx=None, r1c1=0):
2089    if not rowxrel and not colxrel:
2090        return cellnameabs(rowx, colx, r1c1)
2091    if (rowxrel and browx is None) or (colxrel and bcolx is None):
2092        # must flip the whole cell into R1C1 mode
2093        r1c1 = True
2094    c = colnamerel(colx, colxrel, bcolx, r1c1)
2095    r = rownamerel(rowx, rowxrel, browx, r1c1)
2096    if r1c1:
2097        return r + c
2098    return c + r
2099
2100def colname(colx):
2101    """Utility function: ``7`` => ``'H'``, ``27`` => ``'AB'``"""
2102    alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
2103    if colx <= 25:
2104        return alphabet[colx]
2105    else:
2106        xdiv26, xmod26 = divmod(colx, 26)
2107        return alphabet[xdiv26 - 1] + alphabet[xmod26]
2108
2109def rangename2d(rlo, rhi, clo, chi, r1c1=0):
2110    """ ``(5, 20, 7, 10)`` => ``'$H$6:$J$20'`` """
2111    if r1c1:
2112        return
2113    if rhi == rlo+1 and chi == clo+1:
2114        return cellnameabs(rlo, clo, r1c1)
2115    return "%s:%s" % (cellnameabs(rlo, clo, r1c1), cellnameabs(rhi-1, chi-1, r1c1))
2116
2117def rangename2drel(rlo_rhi_clo_chi, rlorel_rhirel_clorel_chirel, browx=None, bcolx=None, r1c1=0):
2118    rlo, rhi, clo, chi = rlo_rhi_clo_chi
2119    rlorel, rhirel, clorel, chirel = rlorel_rhirel_clorel_chirel
2120    if (rlorel or rhirel) and browx is None:
2121        r1c1 = True
2122    if (clorel or chirel) and bcolx is None:
2123        r1c1 = True
2124    return "%s:%s" % (
2125        cellnamerel(rlo,   clo,   rlorel, clorel, browx, bcolx, r1c1),
2126        cellnamerel(rhi-1, chi-1, rhirel, chirel, browx, bcolx, r1c1),
2127    )
2128
2129
2130def rangename3d(book, ref3d):
2131    """
2132    Utility function:
2133    ``Ref3D(1, 4, 5, 20, 7, 10)`` =>
2134    ``'Sheet2:Sheet3!$H$6:$J$20'``
2135    (assuming Excel's default sheetnames)
2136    """
2137    coords = ref3d.coords
2138    return "%s!%s" % (
2139        sheetrange(book, *coords[:2]),
2140        rangename2d(*coords[2:6]))
2141
2142def rangename3drel(book, ref3d, browx=None, bcolx=None, r1c1=0):
2143    """
2144    Utility function:
2145    ``Ref3D(coords=(0, 1, -32, -22, -13, 13), relflags=(0, 0, 1, 1, 1, 1))``
2146
2147    In R1C1 mode => ``'Sheet1!R[-32]C[-13]:R[-23]C[12]'``
2148
2149    In A1 mode => depends on base cell ``(browx, bcolx)``
2150    """
2151    coords = ref3d.coords
2152    relflags = ref3d.relflags
2153    shdesc = sheetrangerel(book, coords[:2], relflags[:2])
2154    rngdesc = rangename2drel(coords[2:6], relflags[2:6], browx, bcolx, r1c1)
2155    if not shdesc:
2156        return rngdesc
2157    return "%s!%s" % (shdesc, rngdesc)
2158
2159def quotedsheetname(shnames, shx):
2160    if shx >= 0:
2161        shname = shnames[shx]
2162    else:
2163        shname = {
2164            -1: "?internal; any sheet?",
2165            -2: "internal; deleted sheet",
2166            -3: "internal; macro sheet",
2167            -4: "<<external>>",
2168        }.get(shx, "?error %d?" % shx)
2169    if "'" in shname:
2170        return "'" + shname.replace("'", "''") + "'"
2171    if " " in shname:
2172        return "'" + shname + "'"
2173    return shname
2174
2175def sheetrange(book, slo, shi):
2176    shnames = book.sheet_names()
2177    shdesc = quotedsheetname(shnames, slo)
2178    if slo != shi-1:
2179        shdesc += ":" + quotedsheetname(shnames, shi-1)
2180    return shdesc
2181
2182def sheetrangerel(book, srange, srangerel):
2183    slo, shi = srange
2184    slorel, shirel = srangerel
2185    if not slorel and not shirel:
2186        return sheetrange(book, slo, shi)
2187    assert (slo == 0 == shi-1) and slorel and shirel
2188    return ""
2189
2190# ==============================================================