282 lines
7.9 KiB
Python
282 lines
7.9 KiB
Python
|
import xlrd
|
||
|
|
import os
|
||
|
|
import sys
|
||
|
|
import datetime
|
||
|
|
|
||
|
|
from appPublic.strUtils import *
|
||
|
|
|
||
|
|
TCS={
|
||
|
|
'int':int,
|
||
|
|
'float':float,
|
||
|
|
'str':str,
|
||
|
|
}
|
||
|
|
|
||
|
|
def isEmptyCell(cell):
|
||
|
|
return cell.ctype == xlrd.XL_CELL_EMPTY
|
||
|
|
|
||
|
|
def isCommentValue(v):
|
||
|
|
if type(v)==type('') and v[0] == '#':
|
||
|
|
return True
|
||
|
|
return False
|
||
|
|
|
||
|
|
def purekey(k):
|
||
|
|
return k.split(':')[0]
|
||
|
|
|
||
|
|
def castedValue(v,k):
|
||
|
|
ki = k.split(':')
|
||
|
|
if len(ki)<2 or v is None:
|
||
|
|
return v
|
||
|
|
ki = ki[1:]
|
||
|
|
if 'list' in ki:
|
||
|
|
if type(v) == type(''):
|
||
|
|
v = v.split(',')
|
||
|
|
elif type(v) != type([]):
|
||
|
|
v = [v]
|
||
|
|
|
||
|
|
for k,tc in TCS.items():
|
||
|
|
if k in ki:
|
||
|
|
if type(v) == type([]):
|
||
|
|
return [ tc(i) for i in v ]
|
||
|
|
else:
|
||
|
|
return tc(v)
|
||
|
|
return v
|
||
|
|
|
||
|
|
class ExcelData:
|
||
|
|
_indictors = {
|
||
|
|
':__dict__':'ff',
|
||
|
|
':__list__':'ff',
|
||
|
|
':__include__':'ff',
|
||
|
|
}
|
||
|
|
|
||
|
|
def __init__(self,xlsfile,encoding='UTF8',startrow=0,startcol=0):
|
||
|
|
self._book = xlrd.open_workbook(xlsfile)
|
||
|
|
self.encoding = encoding
|
||
|
|
self._filename = xlsfile
|
||
|
|
self.startrow=0
|
||
|
|
self.startcol=0
|
||
|
|
self._dataset = self.dataload()
|
||
|
|
|
||
|
|
def __del__(self):
|
||
|
|
del self._book
|
||
|
|
del self._dataset
|
||
|
|
|
||
|
|
def cellvalue(self,sheet,x,y):
|
||
|
|
if sheet.cell_type(x,y)==xlrd.XL_CELL_EMPTY:
|
||
|
|
return None
|
||
|
|
if sheet.cell_type(x,y)==xlrd.XL_CELL_DATE:
|
||
|
|
y,m,d,hh,mm,ss = xlrd.xldate_as_tuple(sheet.cell_value(x,y),self._book.datemode)
|
||
|
|
return datetime.date(y,m,d)
|
||
|
|
s = sheet.cell_value(x,y)
|
||
|
|
return self.trimedValue(s)
|
||
|
|
|
||
|
|
def isCommentCell(self,cell):
|
||
|
|
if isEmptyCell(cell):
|
||
|
|
return False
|
||
|
|
v = self.trimedValue(cell.value)
|
||
|
|
return isCommentValue(v)
|
||
|
|
|
||
|
|
def dateMode(self):
|
||
|
|
return self._book.datemode
|
||
|
|
|
||
|
|
def trimedValue(self,v):
|
||
|
|
if type(v) == type(u' '):
|
||
|
|
v = v.encode(self.encoding)
|
||
|
|
if type(v) == type(''):
|
||
|
|
v = lrtrim(v)
|
||
|
|
return v
|
||
|
|
def dataload(self):
|
||
|
|
dat = {}
|
||
|
|
for name in self._book.sheet_names():
|
||
|
|
sheet = self._book.sheet_by_name(name)
|
||
|
|
#name = name.encode(self.encoding)
|
||
|
|
dat[self.trimedValue(name)] = self.loadSheetData(sheet)
|
||
|
|
return dat
|
||
|
|
|
||
|
|
def findDataRange(self,sheet,pos,maxr):
|
||
|
|
x,y = pos
|
||
|
|
j = y + 1
|
||
|
|
while j < sheet.ncols:
|
||
|
|
if isEmptyCell(sheet.cell(x,j)) or self.isCommentCell(sheet.cell(x,y)):
|
||
|
|
maxy = j
|
||
|
|
break
|
||
|
|
j += 1
|
||
|
|
i = x + 1
|
||
|
|
maxx = maxr
|
||
|
|
while i < maxr:
|
||
|
|
if not isEmptyCell(sheet.cell(i,y)):
|
||
|
|
maxx = i
|
||
|
|
break
|
||
|
|
i += 1
|
||
|
|
return maxx
|
||
|
|
|
||
|
|
def loadSheetData(self,sheet):
|
||
|
|
return self.loadSheetDataRange(sheet,(self.startrow,self.startcol),sheet.nrows)
|
||
|
|
|
||
|
|
def include(self,filename,id):
|
||
|
|
try:
|
||
|
|
sub = ExcelData(filename,self.encoding)
|
||
|
|
except Exception as e:
|
||
|
|
print(e,filename)
|
||
|
|
return None
|
||
|
|
if id is None:
|
||
|
|
return sub.dict()
|
||
|
|
env = {'data':sub.dict()}
|
||
|
|
try:
|
||
|
|
exec("""resutl__ = data%s""" % id,globals(),env)
|
||
|
|
except Exception as e:
|
||
|
|
print(e,id)
|
||
|
|
return None
|
||
|
|
return env['resutl__']
|
||
|
|
|
||
|
|
def loadSingleData(self,sheet,pos):
|
||
|
|
x,y = pos
|
||
|
|
if sheet.ncols==y:
|
||
|
|
v = self.cellvalue(sheet,x,y)
|
||
|
|
if isCommentValue(v):
|
||
|
|
return None
|
||
|
|
return v
|
||
|
|
ret = []
|
||
|
|
while y < sheet.ncols:
|
||
|
|
v = self.cellvalue(sheet,x,y)
|
||
|
|
if v is None:
|
||
|
|
break
|
||
|
|
if isCommentValue(v):
|
||
|
|
break
|
||
|
|
ret.append(v)
|
||
|
|
y += 1
|
||
|
|
|
||
|
|
if len(ret) < 1:
|
||
|
|
return None
|
||
|
|
|
||
|
|
if len(ret)<2:
|
||
|
|
return ret[0]
|
||
|
|
if ret[0] == '__include__':
|
||
|
|
if len(ret)<2:
|
||
|
|
print("include mode error: __include__ filename id")
|
||
|
|
return None
|
||
|
|
id = None
|
||
|
|
if len(ret)>=3:
|
||
|
|
id = ret[2]
|
||
|
|
return self.include(ret[1],id)
|
||
|
|
return ret
|
||
|
|
|
||
|
|
def loadDictData(self,sheet,pos,maxr):
|
||
|
|
ret = {}
|
||
|
|
x,y = pos
|
||
|
|
while x < maxr:
|
||
|
|
mr = self.findDataRange(sheet,(x,y),maxr)
|
||
|
|
#print "loadDictData:debug:",x,y,maxr,mr
|
||
|
|
k = self.cellvalue(sheet,x,y)
|
||
|
|
if isCommentValue(k):
|
||
|
|
x = x + 1
|
||
|
|
continue
|
||
|
|
if k is not None:
|
||
|
|
if 'records' in k.split(':'):
|
||
|
|
v = self.loadRecords(sheet,(x,y+1),maxr)
|
||
|
|
else:
|
||
|
|
v = self.loadSheetDataRange(sheet,(x,y+1),mr)
|
||
|
|
ret[purekey(k)] = castedValue(v,k)
|
||
|
|
x = mr
|
||
|
|
|
||
|
|
return ret
|
||
|
|
|
||
|
|
def loadSheetDataRange(self,sheet,pos,maxr):
|
||
|
|
x,y = pos
|
||
|
|
#print "debug1:",pos,maxr
|
||
|
|
if maxr - x < 1 :
|
||
|
|
#print "debug1-1:",pos,maxr
|
||
|
|
return None
|
||
|
|
if isEmptyCell(sheet.cell(x,y)):
|
||
|
|
#print "debug1-2:",pos,maxr
|
||
|
|
return None
|
||
|
|
|
||
|
|
cv = self.cellvalue(sheet,x,y)
|
||
|
|
#print cv
|
||
|
|
if isCommentValue(cv):
|
||
|
|
pos = (x+1,y)
|
||
|
|
return self.loadSheetDataRange(sheet,pos,maxr)
|
||
|
|
|
||
|
|
if cv == '__include__':
|
||
|
|
return self.include(self.cellvalue(sheet,x,y+1),self.cellvalue(sheet,x,y+2))
|
||
|
|
|
||
|
|
if cv == '__dict__':
|
||
|
|
#print "cv==__dict__"
|
||
|
|
i = x + 1
|
||
|
|
vs = []
|
||
|
|
while i < maxr:
|
||
|
|
v = self.cellvalue(sheet,i,y)
|
||
|
|
if v == '__dict__':
|
||
|
|
vs.append(self.loadDictData(sheet,(x+1,y),i))
|
||
|
|
x = i
|
||
|
|
i += 1
|
||
|
|
vs.append(self.loadDictData(sheet,(x+1,y),i))
|
||
|
|
if len(vs) < 1:
|
||
|
|
return None
|
||
|
|
if len(vs) < 2:
|
||
|
|
return vs[0]
|
||
|
|
return vs
|
||
|
|
return self.loadDictData(sheet,(x+1,y),maxr)
|
||
|
|
|
||
|
|
if cv == '__list__':
|
||
|
|
i = x + 1
|
||
|
|
vs = []
|
||
|
|
while i < maxr:
|
||
|
|
v = self.loadSingleData(sheet,(i,y))
|
||
|
|
vs.append(v)
|
||
|
|
i += 1
|
||
|
|
return vs
|
||
|
|
|
||
|
|
if maxr - x < 2:
|
||
|
|
v = self.loadSingleData(sheet,(x,y))
|
||
|
|
return v
|
||
|
|
|
||
|
|
return self.loadRecords(sheet,pos,maxr)
|
||
|
|
|
||
|
|
def loadRecords(self,sheet,pos,maxr):
|
||
|
|
x,y = pos
|
||
|
|
v = self.cellvalue(sheet,x,y)
|
||
|
|
if v==None or isCommentValue(v):
|
||
|
|
return self.loadRecords(sheet,(x+1,y),maxr)
|
||
|
|
|
||
|
|
data = []
|
||
|
|
i = x + 1
|
||
|
|
j = y
|
||
|
|
keys = [ self.trimedValue(k.value) for k in sheet.row(x)[y:] ]
|
||
|
|
while i < maxr:
|
||
|
|
d = {}
|
||
|
|
j = y
|
||
|
|
while j < sheet.ncols:
|
||
|
|
k = self.cellvalue(sheet,x,j)
|
||
|
|
if k is None or isCommentValue(k):
|
||
|
|
break
|
||
|
|
if sheet.cell_type(x,j) == xlrd.XL_CELL_EMPTY:
|
||
|
|
break
|
||
|
|
v = self.cellvalue(sheet,i,j)
|
||
|
|
if sheet.cell_type(x,j) != xlrd.XL_CELL_EMPTY:
|
||
|
|
d[purekey(k)] = castedValue(v,k)
|
||
|
|
j += 1
|
||
|
|
data.append(d)
|
||
|
|
i += 1
|
||
|
|
return data
|
||
|
|
|
||
|
|
def dict(self):
|
||
|
|
return self._dataset
|
||
|
|
|
||
|
|
class ExcelDataL(ExcelData):
|
||
|
|
def dataload(self):
|
||
|
|
ret = []
|
||
|
|
for name in self._book.sheet_names():
|
||
|
|
dat = {}
|
||
|
|
sheet = self._book.sheet_by_name(name)
|
||
|
|
name = name.encode(self.encoding)
|
||
|
|
dat[name] = self.loadSheetData(sheet)
|
||
|
|
ret.append(dat)
|
||
|
|
return ret
|
||
|
|
|
||
|
|
if __name__ == '__main__':
|
||
|
|
if len(sys.argv)<2:
|
||
|
|
print("Usage:\n%s execlfile" % sys.argv[0])
|
||
|
|
sys.exit(1)
|
||
|
|
ed = ExcelData(sys.argv[1])
|
||
|
|
print(ed.dict())
|