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)))
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')