Coverage for /var/srv/projects/api.amasfac.comuna18.com/tmp/venv/lib/python3.9/site-packages/xlrd/formula.py: 6%
1305 statements
« prev ^ index » next coverage.py v6.4.4, created at 2023-07-17 14:22 -0600
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"""
11from __future__ import print_function
13import copy
14import operator as opr
15from struct import unpack
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 *
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]
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
48FMLA_TYPEDESCR_MAP = {
49 1 : 'CELL',
50 2 : 'SHARED',
51 4 : 'ARRAY',
52 8 : 'COND-FMT',
53 16: 'DATA-VAL',
54 32: 'NAME',
55}
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
73oBOOL = 3
74oERR = 4
75oMSNG = 5 # tMissArg
76oNUM = 2
77oREF = -1
78oREL = -2
79oSTRG = 1
80oUNK = 0
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}
93listsep = ',' #### probably should depend on locale
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]
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}
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', '', '']
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}
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}
397error_opcodes = set([0x07, 0x08, 0x0A, 0x0B, 0x1C, 0x1D, 0x2F])
399tRangeFuncs = (min, max, min, max, min, max)
400tIsectFuncs = (max, min, max, min, max, min)
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 )
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
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
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)
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
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
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
530class FormulaError(Exception):
531 pass
534class Operand(object):
535 """
536 Used in evaluating formulas.
537 The following table describes the kinds and how their values
538 are represented.
540 .. raw:: html
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 """
597 #: None means that the actual value of the operand is a variable
598 #: (depends on cell data), not a constant.
599 value = None
601 #: oUNK means that the kind of operand is not known unambiguously.
602 kind = oUNK
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 = '?'
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
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)
626class Ref3D(tuple):
627 """
628 Represents an absolute or relative 3-dimensional reference to a box
629 of one or more cells.
631 The ``coords`` attribute is a tuple of the form::
633 (shtxlo, shtxhi, rowxlo, rowxhi, colxlo, colxhi)
635 where ``0 <= thingxlo <= thingx < thingxhi``.
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` :-)
644 The components of the coords attribute are also available as individual
645 attributes: ``shtxlo``, ``shtxhi``, ``rowxlo``, ``rowxhi``, ``colxlo``, and
646 ``colxhi``.
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).
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.
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``.
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>
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".
669 This will appear as ``coords = (0, 1, ...)`` and
670 ``relflags = (1, 1, ...)``.
672 .. versionadded:: 0.6.0
673 """
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
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)
691tAdd = 0x03
692tSub = 0x04
693tMul = 0x05
694tDiv = 0x06
695tPower = 0x07
696tConcat = 0x08
697tLT, tLE, tEQ, tGE, tGT, tNE = range(0x09, 0x0F)
700def nop(x):
701 return x
703def _opr_pow(x, y): return x ** y 703 ↛ exitline 703 didn't return from function '_opr_pow', because the return on line 703 wasn't executed
705def _opr_lt(x, y): return x < y 705 ↛ exitline 705 didn't return from function '_opr_lt', because the return on line 705 wasn't executed
706def _opr_le(x, y): return x <= y 706 ↛ exitline 706 didn't return from function '_opr_le', because the return on line 706 wasn't executed
707def _opr_eq(x, y): return x == y 707 ↛ exitline 707 didn't return from function '_opr_eq', because the return on line 707 wasn't executed
708def _opr_ge(x, y): return x >= y 708 ↛ exitline 708 didn't return from function '_opr_ge', because the return on line 708 wasn't executed
709def _opr_gt(x, y): return x > y 709 ↛ exitline 709 didn't return from function '_opr_gt', because the return on line 709 wasn't executed
710def _opr_ne(x, y): return x != y 710 ↛ exitline 710 didn't return from function '_opr_ne', because the return on line 710 wasn't executed
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
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}
740unop_rules = { 740 ↛ exitline 740 didn't jump to the function exit
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}
746LEAF_RANK = 90
747FUNC_RANK = 90
749STACK_ALARM_LEVEL = 5
750STACK_PANIC_LEVEL = 10
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
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)
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))
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)
828 if fmlalen == 0:
829 stack = [unk_opnd]
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 = 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]
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
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
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)
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))
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)
1419 if fmlalen == 0:
1420 stack = [unk_opnd]
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)
1879 if len(stack) != 1:
1880 result = None
1881 else:
1882 result = stack[0].text
1883 return result
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]
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)
2042# === Some helper functions for displaying cell references ===
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.
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)
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)
2078def cellname(rowx, colx):
2079 """Utility function: ``(5, 7)`` => ``'H6'``"""
2080 return "%s%d" % (colname(colx), rowx+1)
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)
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
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]
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))
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 )
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]))
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))``
2147 In R1C1 mode => ``'Sheet1!R[-32]C[-13]:R[-23]C[12]'``
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)
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
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
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 ""
2190# ==============================================================