# -*- coding: cp1252 -*-
##
# Module for parsing/evaluating Microsoft Excel formulas.
#
# <p>Copyright � 2005-2009 Stephen John Machin, Lingfo Pty Ltd</p>
# <p>This module is part of the xlrd package, which is released under
# a BSD-style licence.</p>
##
# No part of the content of this file was derived from the works of David Giffin.
import copy
from struct import unpack
from timemachine import *
from biffh import unpack_unicode_update_pos, unpack_string_update_pos, \
XLRDError, hex_char_dump, error_text_from_code, BaseObject
__all__ = [
'oBOOL', 'oERR', 'oNUM', 'oREF', 'oREL', 'oSTRG', 'oUNK',
'decompile_formula',
'dump_formula',
'evaluate_name_formula',
'okind_dict',
'rangename3d', 'rangename3drel', 'cellname', 'cellnameabs', 'colname',
]
# sztabN[opcode] -> the number of bytes to consume.
# -1 means variable
# -2 means this opcode not implemented in this version.
# Which N to use? Depends on biff_version; see szdict.
sztab0 = [-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]
sztab1 = [-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]
sztab2 = [-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]
sztab3 = [-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]
sztab4 = [-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]
szdict = {
20 : sztab0,
30 : sztab1,
40 : sztab2,
45 : sztab2,
50 : sztab3,
70 : sztab3,
80 : sztab4,
}
# For debugging purposes ... the name for each opcode
# (without the prefix "t" used on OOo docs)
onames = ['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', '', '']
func_defs = {
# index: (name, min#args, max#args, flags, #known_args, return_type, kargs)
0 : ('COUNT', 0, 30, 0x04, 1, 'V', 'R'),
1 : ('IF', 2, 3, 0x04, 3, 'V', 'VRR'),
2 : ('ISNA', 1, 1, 0x02, 1, 'V', 'V'),
3 : ('ISERROR', 1, 1, 0x02, 1, 'V', 'V'),
4 : ('SUM', 0, 30, 0x04, 1, 'V', 'R'),
5 : ('AVERAGE', 1, 30, 0x04, 1, 'V', 'R'),
6 : ('MIN', 1, 30, 0x04, 1, 'V', 'R'),
7 : ('MAX', 1, 30, 0x04, 1, 'V', 'R'),
8 : ('ROW', 0, 1, 0x04, 1, 'V', 'R'),
9 : ('COLUMN', 0, 1, 0x04, 1, 'V', 'R'),
10 : ('NA', 0, 0, 0x02, 0, 'V', ''),
11 : ('NPV', 2, 30, 0x04, 2, 'V', 'VR'),
12 : ('STDEV', 1, 30, 0x04, 1, 'V', 'R'),
13 : ('DOLLAR', 1, 2, 0x04, 1, 'V', 'V'),
14 : ('FIXED', 2, 3, 0x04, 3, 'V', 'VVV'),
15 : ('SIN', 1, 1, 0x02, 1, 'V', 'V'),
16 : ('COS', 1, 1, 0x02, 1, 'V', 'V'),
17 : ('TAN', 1, 1, 0x02, 1, 'V', 'V'),
18 : ('ATAN', 1, 1, 0x02, 1, 'V', 'V'),
19 : ('PI', 0, 0, 0x02, 0, 'V', ''),
20 : ('SQRT', 1, 1, 0x02, 1, 'V', 'V'),
21 : ('EXP', 1, 1, 0x02, 1, 'V', 'V'),
22 : ('LN', 1, 1, 0x02, 1, 'V', 'V'),
23 : ('LOG10', 1, 1, 0x02, 1, 'V', 'V'),
24 : ('ABS', 1, 1, 0x02, 1, 'V', 'V'),
25 : ('INT', 1, 1, 0x02, 1, 'V', 'V'),
26 : ('SIGN', 1, 1, 0x02, 1, 'V', 'V'),
27 : ('ROUND', 2, 2, 0x02, 2, 'V', 'VV'),
28 : ('LOOKUP', 2, 3, 0x04, 2, 'V', 'VR'),
29 : ('INDEX', 2, 4, 0x0c, 4, 'R', 'RVVV'),
30 : ('REPT', 2, 2, 0x02, 2, 'V', 'VV'),
31 : ('MID', 3, 3, 0x02, 3, 'V', 'VVV'),
32 : ('LEN', 1, 1, 0x02, 1, 'V', 'V'),
33 : ('VALUE', 1, 1, 0x02, 1, 'V', 'V'),
34 : ('TRUE', 0, 0, 0x02, 0, 'V', ''),
35 : ('FALSE', 0, 0, 0x02, 0, 'V', ''),
36 : ('AND', 1, 30, 0x04, 1, 'V', 'R'),
37 : ('OR', 1, 30, 0x04, 1, 'V', 'R'),
38 : ('NOT', 1, 1, 0x02, 1, 'V', 'V'),
39 : ('MOD', 2, 2, 0x02, 2, 'V', 'VV'),
40 : ('DCOUNT', 3, 3, 0x02, 3, 'V', 'RRR'),
41 : ('DSUM', 3, 3, 0x02, 3, 'V', 'RRR'),
42 : ('DAVERAGE', 3, 3, 0x02, 3, 'V', 'RRR'),
43 : ('DMIN', 3, 3, 0x02, 3, 'V', 'RRR'),
44 : ('DMAX', 3, 3, 0x02, 3, 'V', 'RRR'),
45 : ('DSTDEV', 3, 3, 0x02, 3, 'V', 'RRR'),
46 : ('VAR', 1, 30, 0x04, 1, 'V', 'R'),
47 : ('DVAR', 3, 3, 0x02, 3, 'V', 'RRR'),
48 : ('TEXT', 2, 2, 0x02, 2, 'V', 'VV'),
49 : ('LINEST', 1, 4, 0x04, 4, 'A', 'RRVV'),
50 : ('TREND', 1, 4, 0x04, 4, 'A', 'RRRV'),
51 : ('LOGEST', 1, 4, 0x04, 4, 'A', 'RRVV'),
52 : ('GROWTH', 1, 4, 0x04, 4, 'A', 'RRRV'),
56 : ('PV', 3, 5, 0x04, 5, 'V', 'VVVVV'),
57 : ('FV', 3, 5, 0x04, 5, 'V', 'VVVVV'),
58 : ('NPER', 3, 5, 0x04, 5, 'V', 'VVVVV'),
59 : ('PMT', 3, 5, 0x04, 5, 'V', 'VVVVV'),
60 : ('RATE', 3, 6, 0x04, 6, 'V', 'VVVVVV'),
61 : ('MIRR', 3, 3, 0x02, 3, 'V', 'RVV'),
62 : ('IRR', 1, 2, 0x04, 2, 'V', 'RV'),
63 : ('RAND', 0, 0, 0x0a, 0, 'V', ''),
64 : ('MATCH', 2, 3, 0x04, 3, 'V', 'VRR'),
65 : ('DATE', 3, 3, 0x02, 3, 'V', 'VVV'),
66 : ('TIME', 3, 3, 0x02, 3, 'V', 'VVV'),
67 : ('DAY', 1, 1, 0x02, 1, 'V', 'V'),
68 : ('MONTH', 1, 1, 0x02, 1, 'V', 'V'),
69 : ('YEAR', 1, 1, 0x02, 1, 'V', 'V'),
70 : ('WEEKDAY', 1, 2, 0x04, 2, 'V', 'VV'),
71 : ('HOUR', 1, 1, 0x02, 1, 'V', 'V'),
72 : ('MINUTE', 1, 1, 0x02, 1, 'V', 'V'),
73 : ('SECOND', 1, 1, 0x02, 1, 'V', 'V'),
74 : ('NOW', 0, 0, 0x0a, 0, 'V', ''),
75 : ('AREAS', 1, 1, 0x02, 1, 'V', 'R'),
76 : ('ROWS', 1, 1, 0x02, 1, 'V', 'R'),
77 : ('COLUMNS', 1, 1, 0x02, 1, 'V', 'R'),
78 : ('OFFSET', 3, 5, 0x04, 5, 'R', 'RVVVV'),
82 : ('SEARCH', 2, 3, 0x04, 3, 'V', 'VVV'),
83 : ('TRANSPOSE', 1, 1, 0x02, 1, 'A', 'A'),
86 : ('TYPE', 1, 1, 0x02, 1, 'V', 'V'),
92 : ('SERIESSUM', 4, 4, 0x02, 4, 'V', 'VVVA'),
97 : ('ATAN2', 2, 2, 0x02, 2, 'V', 'VV'),
98 : ('ASIN', 1, 1, 0x02, 1, 'V', 'V'),
99 : ('ACOS', 1, 1, 0x02, 1, 'V', 'V'),
100: ('CHOOSE', 2, 30, 0x04, 2, 'V', 'VR'),
101: ('HLOOKUP', 3, 4, 0x04, 4, 'V', 'VRRV'),
102: ('VLOOKUP', 3, 4, 0x04,