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