Mercurial > hg > ooxml

comparison parse.py @ 1:20424d7e99e4

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
handle sheet names more carefully
author Henry S. Thompson <ht@markup.co.uk>
date Fri, 24 Mar 2017 22:41:28 +0000
parents ddd5f7539abc
children 263a1935d37d
comparison
equal deleted inserted replaced
0:ddd5f7539abc 1:20424d7e99e4
5 parser_formule_with_textbox_v01_2003.xla 5 parser_formule_with_textbox_v01_2003.xla
6 linked to therein''' 6 linked to therein'''
7 7
8 import sys,re 8 import sys,re
9 9
10 cw = "[^=\-+*/();:,.$<>^]" 10 cw = "=\-+*/();:,.$<>^!"
11 #cWW = "[=\-+*/();:,.$<>^]" 11 #cWW = "[=\-+*/();:,.$<>^]"
12 12
13 sListSeparator = "," 13 sListSeparator = ","
14 sRowSeparator = ";" 14 sRowSeparator = ";"
15 15
16 # If comma is decimal symbol, then \ is used in place of comma in Array. 16 # If comma is decimal symbol, then \ is used in place of comma in Array.
17 # If semi-colon is decimal symbol, then \ is used in place of semi-colon 17 # If semi-colon is decimal symbol, then \ is used in place of semi-colon
18 # in Array. (Semi-colon is row separator) 18 # in Array. (Semi-colon is row separator)
19 # See https://www.ablebits.com/office-addins-blog/2015/02/25/array-formulas-functions-excel/ 19 # See https://www.ablebits.com/office-addins-blog/2015/02/25/array-formulas-functions-excel/
20 20
21 pats=["\"[^\"]*\"|'[^']*'", ## "'[^']*'!", 21 pats=["\"[^\"]*\"",
22 "\{[^}]+}", 22 "\{[^}]+}",
23 sListSeparator, 23 sListSeparator,
24 cw + "+(?:\." + cw + "+)*\\(", 24 "[^" + cw + "]+(?:\.[^" + cw + "]+)*\\(",
25 "\\)", 25 "\\)",
26 "^=|\\(", 26 "^=|\\(",
27 "'[^']+'!",
27 "."] 28 "."]
28 29
29 ## They perform the following tasks, in order: 30 ## They perform the following tasks, in order:
30 ## 1. Represents a text (delimited by double quotes) or a sheet 31 ## 1. Represents a text (delimited by double quotes)
31 ## name (delimited by single quotes)
32 ## 2. Represents a constant matrix 32 ## 2. Represents a constant matrix
33 ## 3. Represents a list (function parameter) separator 33 ## 3. Represents a list (function parameter) separator
34 ## 4. Represents a function name followed by an opening parenthesis 34 ## 4. Represents a function name followed by an opening parenthesis
35 ## 5. Represents a closing parenthesis 35 ## 5. Represents a closing parenthesis
36 ## 6. Represents the beginning of the formula or an opening 36 ## 6. Represents the beginning of the formula or an opening
37 ## parenthesis (not part of a function) 37 ## parenthesis (not part of a function)
38 ## 7. Each characters not matched by the previous patterns 38 ## 7. A sheet name (delimited by single quotes) and a !
39 ## 8. Each characters not matched by the previous patterns
40
39 41
40 tokPat=re.compile("("+(")|(".join(pats))+")",re.IGNORECASE) 42 tokPat=re.compile("("+(")|(".join(pats))+")",re.IGNORECASE)
41 43
42 f="""=IF(I$1>$C$2,VLOOKUP($C52,GP_input!$C$187:$CM$196,VLOOKUP($C$9,lists!$A$34:$B$39,2,FALSE)+BC$4,FALSE),SUMIF('BEX2011'!$C$32:$C$5000,$B$9&"sl"&$B52&$C52&$B53,'BEX2001'!Q$32))""" 44 f="""=IF(I$1>$C$2,VLOOKUP($C52,GP_input!$C$187:$CM$196,VLOOKUP($C$9,lists!$A$34:$B$39,2,FALSE)+BC$4,FALSE),SUMIF('BEX2011'!$C$32:$C$5000,$B$9&"sl"&$B52&$C52&$B53,'BEX2001'!Q$32))"""
43 45
63 ## s = s & t 65 ## s = s & t
64 ## Next 66 ## Next
65 67
66 def mergeMatches(l): 68 def mergeMatches(l):
67 res="" 69 res=""
70 rtype=None
68 cur="" 71 cur=""
69 for txt,cm,sep,ofun,close,opn,misc in l: 72 for txt,cm,sep,ofun,close,opn,sheet,misc in l:
70 if txt is not '': 73 if txt is not '':
74 if res is not '' and rtype is not 1:
75 yield res
76 rtype=1
77 res=''
71 res+=txt 78 res+=txt
79 rtype=1
72 continue 80 continue
73 if misc is not '': 81 if misc is not '' or sheet is not '':
74 res+=misc 82 if res is not '' and rtype is not 2:
83 yield res
84 rtype=2
85 res=''
86 res+=misc if misc is not '' else sheet
87 rtype=2
75 continue 88 continue
76 if cm is not '': 89 if cm is not '':
77 cur=cm 90 cur=cm
78 elif sep is not '': 91 elif sep is not '':
79 cur=sep 92 cur=sep
83 cur=close 96 cur=close
84 elif opn is not '': 97 elif opn is not '':
85 cur=opn 98 cur=opn
86 if res is not '': 99 if res is not '':
87 yield res 100 yield res
101 rtype=None
88 res='' 102 res=''
89 yield cur 103 yield cur
90 104
91 toks=list(mergeMatches(l)) 105 toks=list(mergeMatches(l))