Mercurial > hg > ooxml

comparison excel.py @ 73:4bd5de7ac247

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
added Range, M and RC
author Henry S. Thompson <ht@markup.co.uk>
date Mon, 03 Jul 2017 16:25:49 +0100
parents 54bb53434887
children 7827e686be75
comparison
equal deleted inserted replaced
72:0654e37583b5 73:4bd5de7ac247
2 '''Class model for analysis of Excel spreadsheets''' 2 '''Class model for analysis of Excel spreadsheets'''
3 from jsonweb.encode import to_object, dumper 3 from jsonweb.encode import to_object, dumper
4 from jsonweb.decode import from_object, loader 4 from jsonweb.decode import from_object, loader
5 import json 5 import json
6 import eDecoder 6 import eDecoder
7
8 import re
7 9
8 try: 10 try:
9 string_types=basestring 11 string_types=basestring
10 except NameError: 12 except NameError:
11 string_types=str 13 string_types=str
56 def __init__(self,name,parent=None,ranges=[],content=[]): 58 def __init__(self,name,parent=None,ranges=[],content=[]):
57 if parent is not None: 59 if parent is not None:
58 assert(isinstance(parent,(Region,Sheet))) 60 assert(isinstance(parent,(Region,Sheet)))
59 self.parent=parent 61 self.parent=parent
60 ranges=list(ranges) 62 ranges=list(ranges)
61 assert(all(isinstance(s,Range) for s in ranges)) 63 assert(all(isinstance(s,string_types) for s in ranges))
62 self.ranges=ranges 64 self.ranges=[Range(s) for s in ranges]
63 content=list(content) 65 content=list(content)
64 assert(all(isinstance(s,(Region,string_types)) for s in content)) 66 assert(all(isinstance(s,(Region,string_types)) for s in content))
65 self.content=content 67 self.content=content
66 assert(isinstance(name,string_types)) 68 assert(isinstance(name,string_types))
67 self.name=name 69 self.name=name
68 if parent is not None: 70 if parent is not None:
69 parent.addRegion(self) 71 parent.addRegion(self)
70 72
71 def addRegion(self,content): 73 def addRegion(self,content):
72 assert(isinstance(content,(Region,string_types))) 74 assert(isinstance(content,(Region,string_types)))
75 # convert
73 self.content.append(content) 76 self.content.append(content)
77
78 cell=r'(\$?)([A-Z]+)(\$?)([1-9][0-9]*)'
79 RANGE=re.compile(cell+(r'(:%s)?'%cell))
80 C=0 # Column
81 R=1 # Row
82 F=0 # Fixed ($)
83 V=1 # Value
84 @to_object()
85 class Range(object):
86 def __init__(self,estr):
87 m=RANGE.match(estr)
88 if m is None:
89 raise ValueError("string %s doesn't represent a range"%estr)
90 rgrps=m.groups()
91 self.tl=((rgrps[0]=='$',rgrps[1]),((rgrps[2]=='$',int(rgrps[3]))))
92 if rgrps[4] is None:
93 self.br=None
94 self.dim=0
95 self.vertical=None
96 else:
97 self.br=((rgrps[5]=='$',rgrps[6]),((rgrps[7]=='$',int(rgrps[8]))))
98 if self.tl[C][V]==self.br[C][V]:
99 self.dim=1
100 self.vertical=True
101 elif self.tl[R][V]==self.br[R][V]:
102 self.dim=1
103 self.vertical=False
104 else:
105 self.dim=2
106 self.vertical=None
107
108 def __str__(self):
109 tls=_cellStr(self.tl[C])+_cellStr(self.tl[R])
110 if self.br is None:
111 return tls
112 else:
113 return '%s:%s'%(tls,_cellStr(self.br[C])+_cellStr(self.br[R]))
114
115 def _cellStr(dvp):
116 (dollar,val)=dvp
117 return '%s%s'%('$' if dollar else '',str(val))
118
119 @from_object()
120 @to_object(exclude_nulls=True)
121 class M(Region):
122 def __init__(self,name,sheet=None,ranges=[],labels=[],data=[]):
123 super(M,self).__init__(name,sheet,ranges,labels,data)
124
125 @from_object()
126 @to_object(exclude_nulls=True)
127 class RC(Region):
128 def __init__(self,name,sheet=None,ranges=[],labels=[],data=[]):
129 super(RC,self).__init__(name,sheet,ranges,labels,data)
74 130
75 @from_object() 131 @from_object()
76 @to_object(exclude_nulls=True,suppress=["sheet"]) 132 @to_object(exclude_nulls=True,suppress=["sheet"])
77 class Table(Region): 133 class Table(M):
78 def __init__(self,name,sheet=None,ranges=[],labels=[],data=[]): 134 def __init__(self,name,shape='mixed',sheet=None,ranges=[],labels=[],data=[]):
79 super(Table,self).__init__(name,sheet,ranges) 135 super(Table,self).__init__(name,sheet,ranges)
136 assert(shape in ('columns','rows','mixed'))
137 self.shape=shape # if columns or rows, that's what correspond to DB columns
80 labels=list(labels) 138 labels=list(labels)
81 assert(all(isinstance(s,Label) for s in labels)) 139 assert(all(isinstance(s,Label) for s in labels))
82 self.labels=labels 140 self.labels=labels
83 data=list(data) 141 data=list(data)
84 assert(all(isinstance(s,Region) for s in data)) 142 assert(all(isinstance(s,Region) for s in data))
103 else: 161 else:
104 self.addData(region) 162 self.addData(region)
105 163
106 def lt(filename): 164 def lt(filename):
107 with open(filename,'r') as js: 165 with open(filename,'r') as js:
108 return json.loads(js.read(),cls=eDecoder.eDecoder) 166 return loader(js.read(),cls=eDecoder.eDecoder)