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
Beispiel #2
0
 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
Beispiel #5
0
    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
Beispiel #6
0
    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
Beispiel #7
0
 def parseRange(self, range, sht):
     return Utils.tupleToList(self.__xlWb.Worksheets(sht).Range(range))