def parse(self): __sheetnames = [u'Q1 GeneralData', u'Q2 EnergyConsumption', u'Q3_ Processes', u'Q3A', u'Q4H_HeatGeneration', u'Q4C_ColdGeneration', u'Q5_Distribution', u'Q6_HeatRecovery', u'Q7_ Renewables', u'Q8 Buildings', u'Q9 Economics'] dlg = DialogGauge(None,"OpenOffice Calc Parsing","reading document") self.__md = self.__connectToDB() xmlString = self.readOOContent(self.__filepath) dlg.update(10) parsedDom = xml.dom.minidom.parseString(xmlString) dlg.update(53) __handle, lists = self.__getLists(parsedDom, dlg, __sheetnames) DButil = Utils(self.__md, __sheetnames) __handle = DButil.writeToDB(lists) dlg.update(100) dlg.Destroy() return __handle
def parseold(self): dlg = DialogGauge(None,"OpenOffice Calc Parsing","reading document") self.__md = self.__connectToDB() xmlString = self.readOOContent(self.__filepath) dlg.update(10) self.__parsedDom = xml.dom.minidom.parseString(xmlString) dlg.update(53) __handle, lists = self.__getLists(dlg, self.sheetnames) if len(lists)==0: return __handle DButil = Utils(self.__md, self.sheetnames) __handle = DButil.writeToDB(lists) dlg.update(100) dlg.Destroy() return __handle
def parse(self): dlg = DialogGauge(None,"Excel Parsing","reading document") Q1 = Q2 = QProduct = QFuel = Q3 = QRenewables = QSurf = QProfiles = QIntervals = Q9Questionnaire = [] try: self.__xlApp, self.__xlWb = self.__openExcelDispatch(self.__filepath) __sheets = self.__xlWb.Sheets self.__sheetnames = [] for i in xrange(0,__sheets.count): if __sheets[i].Name[0] == 'Q': self.__sheetnames.append(__sheets[i].Name) self.__md = self.__connectToDB() except: self.__closeExcelDispatch(self.__xlWb, self.__xlApp) return Utils.parseError("Consistency") dlg.update(5) try: __handle, lists = self.__getExcelLists(self.__sheetnames, self.__xlWb,dlg) except: try: time.sleep(3) self.__xlApp, self.__xlWb = openExcelDispatch() __handle, lists = self.__getExcelLists(self.__sheetnames, self.__xlWb,dlg) except: try: time.sleep(3) self.__xlApp, self.__xlWb = openExcelDispatch() __handle, lists = self.__getExcelLists(self.__sheetnames, self.__xlWb, dlg) except: self.__closeExcelDispatch(self.__xlWb, self.__xlApp) return Utils.parseError("Consistency") DButil = Utils(self.__md, self.__sheetnames) dlg.update(75) __handle = DButil.writeToDB(lists) dlg.update(100) dlg.Destroy() self.__closeExcelDispatch(self.__xlWb, self.__xlApp) return __handle
def __getExcelLists(self, sheetnames, xlWb , dlg): lists = [] if len(sheetnames)!=11: return Utils.parseError("wrong number of Sheets"), [] try: sht = xlWb.Worksheets(sheetnames[0]) Q1=Utils.tupleToList(sht.Range("Q1_GeneralData")) Q1+=Utils.tupleToList(sht.Range("Q1_StatisticalData")) Q1+=Utils.tupleToList(sht.Range("Q1_Operation")) QProduct =Utils.tupleToList(sht.Range("Q1_Products")) except: return Utils.parseError(sheetnames[0]), [] dlg.update(13) try: sht = xlWb.Worksheets(sheetnames[1]) Q1+=Utils.tupleToList(sht.Range("Q1_Percent")) QProduct+=Utils.tupleToList(sht.Range("Q2_Products")) Q2 = Utils.tupleToList(sht.Range("Q2_EnergyConsumption")) Q2 += Utils.tupleToList(sht.Range("Q2_ElectricityConsumption")) Q2 += Utils.tupleToList(sht.Range("Q2_EnergyConsumptionProduct")) QFuel = Utils.tupleToList(sht.Range("Q2_EnergyConsumption")) except: return Utils.parseError(sheetnames[1]), [] dlg.update(22) try: sht = xlWb.Worksheets(sheetnames[2]) Q3 = Utils.tupleToList(sht.Range("Q3_ProcessData")) Q3 += Utils.tupleToList(sht.Range("Q3_WasteHeat")) Q3 += Utils.tupleToList(sht.Range("Q3_Schedule")) Q3 += Utils.tupleToList(sht.Range("Q3_DataOfExistingHCSupply")) except: return Utils.parseError(sheetnames[2]), [] dlg.update(31) try: sht= xlWb.Worksheets(sheetnames[3]) Q3+= Utils.tupleToList(sht.Range("Q3_ScheduleTolerance")) Q3+= Utils.tupleToList(sht.Range("Q3_OperationCycle")) Q3+= Utils.tupleToList(sht.Range("Q3_ScheduleCorrelation")) except: return Utils.parseError(sheetnames[3]), [] dlg.update(40) try: sht = xlWb.Worksheets(sheetnames[8]) QRenewables = Utils.tupleToList(sht.Range("Q7_Interest")) QRenewables += Utils.tupleToList(sht.Range("Q7_REReason")) QRenewables += Utils.tupleToList(sht.Range("Q7_Others")) QRenewables += Utils.tupleToList(sht.Range("Q7_Latitude")) QRenewables += Utils.tupleToList(sht.Range("Q7_Biomass")) QSurf = Utils.tupleToList(sht.Range("Q7_Area")) QSurf += Utils.tupleToList(sht.Range("Q7_Roof")) except: return Utils.parseError(sheetnames[8]), [] dlg.update(49) try: sht = xlWb.Worksheets(sheetnames[3]) QProfiles = [] QProcNames = Utils.tupleToList(sht.Range("Q3A_ProcessName")) for i in xrange(3): QProfil = Utils.tupleToList(sht.Range("Q3A_Profiles_"+ str(i+1))) QProfil.append(QProcNames[i*3]) QProfiles.append(QProfil) QIntervals = Utils.tupleToList(sht.Range("Q3A_StartTime_1")) QIntervals += Utils.tupleToList(sht.Range("Q3A_StartTime_2")) QIntervals += Utils.tupleToList(sht.Range("Q3A_StartTime_3")) QIntervals += Utils.tupleToList(sht.Range("Q3A_EndTime_1")) QIntervals += Utils.tupleToList(sht.Range("Q3A_EndTime_2")) QIntervals += Utils.tupleToList(sht.Range("Q3A_EndTime_3")) except: return Utils.parseError(sheetnames[3]), [] dlg.update(57) try: sht = xlWb.Worksheets(sheetnames[10]) Q9Questionnaire=[] for i in xrange(3): Q9Questionnaire+=Utils.tupleToList(sht.Range("Q9_"+str(i+1))) except: return Utils.parseError(sheetnames[10]), [] dlg.update(66) Q4_8 = [] for i in xrange(5): try: Q4_8.append(Utils.tupleToList(xlWb.Worksheets(sheetnames[4]).Range("Q4H_"+str(i+1)))) except: return self.parseError(sheetnames[4]) try: Q4_8.append(Utils.tupleToList(xlWb.Worksheets(sheetnames[5]).Range("Q4C_"+str(i+1)))) except: return self.parseError(sheetnames[5]) try: Q4_8.append(Utils.tupleToList(xlWb.Worksheets(sheetnames[6]).Range("Q5_"+str(i+1)))) except: return self.parseError(sheetnames[6]) try: Q4_8.append(Utils.tupleToList(xlWb.Worksheets(sheetnames[7]).Range("Q6_"+str(i+1)))) except: return self.parseError(sheetnames[7]) try: Q4_8.append(Utils.tupleToList(xlWb.Worksheets(sheetnames[9]).Range("Q8_"+str(i+1)))) except: return self.parseError(sheetnames[9]) try: latitude = self.__xlWb.Worksheets(sheetnames[8]).Range("Q7_Latitude") except: return self.parseError(sheetnames[8]) dlg.update(72) lists.append(Q1) lists.append(Q2) lists.append(QProduct) lists.append(QFuel) lists.append(Q3) lists.append(QRenewables) lists.append(QSurf) lists.append(QProfiles) lists.append(QIntervals) lists.append(Q9Questionnaire) lists.append(Q4_8) lists.append(latitude) """ biglist = [] for listelem in lists: QList = [] for elem in listelem: try: QList.append(float(elem)) except: try: QList.append(float(str(elem).replace(',', '.'))) except: if type(elem) == type(QList): list = [] for el in elem: try: list.append(float(el)) except: try: list.append(float(str(el).replace(',', '.'))) except: list.append(el) QList.append(list) else: QList.append(elem) biglist.append(QList) print biglist """ return "", lists
def __getLists(self,dlg, sheetnames): lists = [] #if len(sheetnames)!=11: # return self.__parseError("wrong number of Sheets"), [] # try: if len(sheetnames)!=11: return Utils.parseError("wrong number of Sheets"), [] Q1 = self.parseRange("Q1_GeneralData",sheetnames[0]) Q1+= self.parseRange( "Q1_StatisticalData", sheetnames[0]) Q1+= self.parseRange( "Q1_Operation", sheetnames[0]) QProduct = self.parseRange( "Q1_Products", sheetnames[0]) # except: # return "parseError Sheet Q1", [] #return self.__parseError(sheetnames[0]), [] dlg.update(60) try: Q1+= self.parseRange( "Q1_Percent", sheetnames[1]) QProduct += self.parseRange( "Q2_Products", sheetnames[1]) Q2 = self.parseRange( "Q2_EnergyConsumption", sheetnames[1]) Q2+= self.parseRange( "Q2_ElectricityConsumption", sheetnames[1]) Q2+= self.parseRange( "Q2_EnergyConsumptionProduct", sheetnames[1]) QFuel = self.parseRange( "Q2_EnergyConsumption", sheetnames[1]) except: return "parseError Sheet Q2", [] #return self.__parseError(sheetname), [] dlg.update(68) try: Q3 = self.parseRange( "Q3_ProcessData", sheetnames[2]) Q3+= self.parseRange( "Q3_WasteHeat", sheetnames[2]) Q3+= self.parseRange( "Q3_Schedule", sheetnames[2]) Q3+= self.parseRange( "Q3_DataOfExistingHCSupply", sheetnames[2]) dlg.update(76) except: return "parseError Sheet Q3", [] #return self.__parseError(sheetnames[2]), [] #Q3A try: Q3+= self.parseRange( "Q3_ScheduleTolerance", sheetnames[3]) Q3+= self.parseRange( "Q3_OperationCycle", sheetnames[3]) Q3+= self.parseRange( "Q3_ScheduleCorrelation", sheetnames[3]) except: return self.__parseError(sheetnames[3]), [] try: QRenewables = [] QRenewables.append(self.parseRange( "Q7_Interest", sheetnames[8])) QRenewables += self.parseRange( "Q7_REReason", sheetnames[8]) QRenewables.append(self.parseRange( "Q7_Others", sheetnames[8])) QRenewables += self.parseRange( "Q7_Latitude", sheetnames[8]) QRenewables += self.parseRange( "Q7_Biomass", sheetnames[8]) QSurf = self.parseRange( "Q7_Area", sheetnames[8]) QSurf += self.parseRange( "Q7_Roof", sheetnames[8]) except: return self.__parseError(sheetnames[8]), [] dlg.update(84) try: sheetname = sheetnames[3] QProfiles = [] QProcNames = self.parseRange( "Q3A_ProcessName", sheetnames[3]) for i in xrange(3): QProfil = self.parseRange( "Q3A_Profiles_"+ str(i+1), sheetnames[3]) QProfil.append(QProcNames[i*3]) QProfiles.append(QProfil) QIntervals = self.parseRange( "Q3A_StartTime_1", sheetnames[3]) QIntervals += self.parseRange( "Q3A_StartTime_2", sheetnames[3]) QIntervals += self.parseRange( "Q3A_StartTime_3", sheetnames[3]) QIntervals += self.parseRange( "Q3A_EndTime_1", sheetnames[3]) QIntervals += self.parseRange( "Q3A_EndTime_2", sheetnames[3]) QIntervals += self.parseRange( "Q3A_EndTime_3", sheetnames[3]) except: return self.__parseError(sheetnames[3]), [] dlg.update(92) try: Q9Questionnaire=[] for i in xrange(3): Q9Questionnaire+=self.parseRange( "Q9_"+str(i+1), sheetnames[10]) except: return self.__parseError(sheetnames[10]), [] Q4_8=[] # sheets with the same structure structureNames = ["Q4H_HeatGeneration", "Q4C_ColdGeneration", "Q5_Distribution", "Q6_HeatRecovery", "Q8 Buildings"] startStructure = ["Q4H_", "Q4C_", "Q5_", "Q6_", "Q8_"] # Change to xrange(5) to get all sheets --> Q4C_5 for i in xrange(5): for j in xrange(len(structureNames)): try: Q4_8.append(self.parseRange( startStructure[j]+str(i+1), structureNames[j])) except: return structureNames[j] + " " + startStructure[j]+str(i+1),[] try: latitude = self.parseRange( "Q7_Latitude", sheetnames[8]) except: return self.parseError(sheetnames[8]) lists.append(Q1) lists.append(Q2) lists.append(QProduct) lists.append(QFuel) lists.append(Q3) lists.append(QRenewables) lists.append(QSurf) lists.append(QProfiles) lists.append(QIntervals) lists.append(Q9Questionnaire) lists.append(Q4_8) lists.append(latitude) """ biglist = [] for listelem in lists: QList = [] for elem in listelem: try: QList.append(float(elem)) except: try: QList.append(float(str(elem).replace(',', '.'))) except: if type(elem) == type(QList): list = [] for el in elem: try: list.append(float(el)) except: try: list.append(float(str(el).replace(',', '.'))) except: list.append(el) QList.append(list) else: QList.append(elem) biglist.append(QList) print biglist """ return "Parsing successful!", lists
def __getExcelLists(self, sheetnames, xlWb , dlg): lists = [] if len(sheetnames)!=11: return Utils.parseError("wrong number of Sheets"), [] try: sht = sheetnames[0] Q1=self.parseRange("Q1_GeneralData",sht) Q1+=self.parseRange("Q1_StatisticalData",sht) Q1+=self.parseRange("Q1_Operation",sht) QProduct =self.parseRange("Q1_Products",sht) except: return Utils.parseError(sheetnames[0]), [] dlg.update(13) try: sht = sheetnames[1] Q1+=self.parseRange("Q1_Percent",sht) QProduct+=self.parseRange("Q2_Products",sht) Q2 = self.parseRange("Q2_EnergyConsumption",sht) Q2 += self.parseRange("Q2_ElectricityConsumption",sht) Q2 += self.parseRange("Q2_EnergyConsumptionProduct",sht) QFuel = self.parseRange("Q2_EnergyConsumption",sht) except: return Utils.parseError(sheetnames[1]), [] dlg.update(22) try: sht = sheetnames[2] Q3 = self.parseRange("Q3_ProcessData",sht) Q3 += self.parseRange("Q3_WasteHeat",sht) Q3 += self.parseRange("Q3_Schedule",sht) Q3 += self.parseRange("Q3_DataOfExistingHCSupply",sht) except: return Utils.parseError(sheetnames[2]), [] dlg.update(31) try: sht= sheetnames[3] Q3+= self.parseRange("Q3_ScheduleTolerance",sht) Q3+= self.parseRange("Q3_OperationCycle",sht) Q3+= self.parseRange("Q3_ScheduleCorrelation",sht) except: return Utils.parseError(sheetnames[3]), [] dlg.update(40) try: sht = sheetnames[8] QRenewables = [] QRenewables += self.parseRange("Q7_Interest",sht) QRenewables += self.parseRange("Q7_REReason",sht) QRenewables += self.parseRange("Q7_Others",sht) QRenewables += self.parseRange("Q7_Latitude",sht) QRenewables += self.parseRange("Q7_Biomass",sht) QSurf = self.parseRange("Q7_Area",sht) QSurf += self.parseRange("Q7_Roof",sht) except: return Utils.parseError(sheetnames[8]), [] dlg.update(49) try: sht = sheetnames[3] QProfiles = [] QProcNames = self.parseRange("Q3A_ProcessName",sht) for i in xrange(3): QProfil = self.parseRange("Q3A_Profiles_"+ str(i+1), sht) QProfil.append(QProcNames[i*3]) QProfiles.append(QProfil) QIntervals = self.parseRange("Q3A_StartTime_1",sht) QIntervals += self.parseRange("Q3A_StartTime_2",sht) QIntervals += self.parseRange("Q3A_StartTime_3",sht) QIntervals += self.parseRange("Q3A_EndTime_1",sht) QIntervals += self.parseRange("Q3A_EndTime_2",sht) QIntervals += self.parseRange("Q3A_EndTime_3",sht) except: return Utils.parseError(sheetnames[3]), [] dlg.update(57) try: sht = sheetnames[10] Q9Questionnaire=[] for i in xrange(3): Q9Questionnaire+=self.parseRange("Q9_"+str(i+1),sht) except: return Utils.parseError(sheetnames[10]), [] dlg.update(66) Q4_8=[] # sheets with the same structure structureNames = ["Q4H_HeatGeneration", "Q4C_ColdGeneration", "Q5_Distribution", "Q6_HeatRecovery", "Q8 Buildings"] startStructure = ["Q4H_", "Q4C_", "Q5_", "Q6_", "Q8_"] for i in xrange(5): for j in xrange(len(structureNames)): try: Q4_8.append(self.parseRange( startStructure[j]+str(i+1), structureNames[j])) except: return structureNames[j] + " " + startStructure[j]+str(i+1),[] try: sht = sheetnames[8] latitude = self.parseRange("Q7_Latitude",sht) except: return self.parseError(sheetnames[8]) dlg.update(72) lists.append(Q1) lists.append(Q2) lists.append(QProduct) lists.append(QFuel) lists.append(Q3) lists.append(QRenewables) lists.append(QSurf) lists.append(QProfiles) lists.append(QIntervals) lists.append(Q9Questionnaire) lists.append(Q4_8) lists.append(latitude) return "", lists
def parseRange(self, range, sht): return Utils.tupleToList(self.__xlWb.Worksheets(sht).Range(range))