class XlsTest(unittest.TestCase):
def setUp(self):
self.xls = Xls('test/WMX_CoSim_SR.xls')
def test_parse(self):
self.xls.parse()
print("total sheets: {0}".format(len(self.xls.sheets())))
print("sheets: {0}".format(self.xls.sheet_names()))
sheet_1 = 'WMX5.0'
print("rows [{0}], cols [{1}]".format(self.xls.get_rows(sheet_1), self.xls.get_cols(sheet_1)))
def setUp(self):
self.xls = Xls('test/WMX_CoSim_SR.xls')
def __init__(self, excel_file): self.xls = Xls(excel_file) self.xls.parse()
class SrParser:
def __init__(self, excel_file):
self.xls = Xls(excel_file)
self.xls.parse()
def headline(self, sheet_name):
header = []
sheet = self.xls.get_sheet(sheet_name)
ncols = self.xls.get_cols(sheet_name)
for i in range(0,ncols):
header.append(self.xls.get_data(sheet_name, 0, i))
return header
def srlist(self, sheet_name):
srlist = []
sheet = self.xls.get_sheet(sheet_name)
nrows = self.xls.get_rows(sheet_name)
ncols = self.xls.get_cols(sheet_name)
headline = self.headline(sheet_name)
for row in range(1,nrows):
sr = SR()
for col in range(0,ncols):
data = self.xls.get_data(sheet_name, row, col)
setattr(sr, headline[col], data)
srlist.append(sr)
return srlist
def srById(self, sheet_name, srid):
srlist = self.srlistBy(sheet_name, '#SR', [srid])
if len(srlist) > 0:
return srlist[0]
return None
def srlistBy(self, sheet_name, byType, byVals=[], excludes=False):
srlistBy = []
srlist = self.srlist(sheet_name)
def contains(sr):
value = getattr(sr, byType)
if value: value = value.upper()
for byVal in byVals:
if byVal.upper() in value: return True
return False
def notcontains(sr):
value = getattr(sr, byType)
if value: value = value.upper()
for byVal in byVals:
if not byVal.upper() in value: return True
return False
if excludes:
srlistBy = filter(notcontains, srlist)
else:
srlistBy = filter(contains, srlist)
return srlistBy
def srlistNot(self, sheet_name, byType, byVals=[]):
return self.srlistBy(sheet_name, byType, byVals, True)
def latestSrlist(self, sheet_name):
latestSrlist = []
srlist = self.srlistNot(sheet_name, '#Status', ['Closed', 'Performed'])
def parseDate(dateStr):
if not dateStr: return None
(month,day,year) = dateStr.split('/')
return date(int(year),int(month),int(day))
for sr in srlist:
(openDate, openTime) = getattr(sr, '#Open Date').split(' ',1)
#print("open date: {0}".format(openDate))
if len(latestSrlist) == 0:
latestSrlist.append(sr)
continue
(latestOpenDate, latestOpenTime) = getattr(latestSrlist[0], '#Open Date').split(' ', 1)
#print("latest open date: {0}".format(latestOpenDate))
od = parseDate(openDate)
lod = parseDate(latestOpenDate)
if od == lod:
latestSrlist.append(sr)
elif od > lod:
#delete all elements & insert new one
del latestSrlist[:]
else: #od < lod
pass #do nothing, just ignore the sr
return latestSrlist
def srsummary(self, sheet_name):
srsummary = ''
for status in ['Originated','Assessed','Study','Performed','Closed']:
srlen = len(self.srlistBy(sheet_name, '#Status', [status]))
if status == 'Closed':
srsummary += "{0} [{1}]".format(status, srlen)
else:
srsummary += "{0} [{1}], ".format(status, srlen)
return srsummary
def srmappings(self, sheet_name):
srlist = self.srlist(sheet_name)
srMappings = []
for sr in srlist:
tcno = getattr(sr, '#Failed Case No.')
srno = getattr(sr, '#SR No.')
#print("{0} = {1}".format(tcno, srno)
srmapping = SrMapping(int(tcno), srno.strip())
srMappings.append(srmapping)
return srMappings
def srno(self, sheet_name, tcno):
srMappings = self.srmappings(sheet_name)
for srmapping in srMappings:
if srmapping.tcno == int(tcno): return srmapping.srno
return ''
def tclist(self, sheet_name, srno):
tclist = []
srMappings = self.srmappings(sheet_name)
for srmapping in srMappings:
if srmapping.srno == srno.strip():
tclist.append(srmapping.tcno)
return tclist
#specific functions for WMX_CoSim_SR
def srlist4SM40(self, byType, byVals=[]):
if byType and byVals:
return self.srlistBy('WMX4.0', byType, byVals)
return self.srlist('WMX4.0')
def srlist4SM50(self, byType, byVals=[]):
if byType and byVals:
return self.srlistBy('WMX5.0', byType, byVals)
return self.srlist('WMX5.0')
def srMappings4SM40(self):
return self.srMappings('WMX4.0 CASE-SR Mapping')
def srMappings4SM50(self):
return self.srMappings('WMX5.0 CASE-SR Mapping')
def srMappings4SFM40(self):
return self.srMappings('WMX4.0 SFM CASE-SR Mapping')
def srMappings4SFM50(self):
return self.srMappings('WMX5.0 SFM CASE-SR Mapping')
