def main(): #setting up parser parser = argparse.ArgumentParser(description=__doc__) utils.add_standard_command_options(parser) parser.add_argument( "-e","--edbs", action="store",dest="edbList", help="List of 'user/edb' pairs separated by :" ) parser.add_argument( "-L","--labels", action="store", dest="labels", help="List of edb labels separated by :" ) parser.add_argument( "-s","--substances", action="store",dest="substances", help="List of substance names separated by :" ) parser.add_argument( "-t","--title", action="store",dest="title", help="Report title" ) parser.add_argument( "-g","--gc-filter", action="store",dest="gcfilter", help="Filter on Geo codes, separated by :" ) parser.add_argument( "-o","--outfile", action="store",dest="outfile", help="Output filename" ) parser.add_argument( "-f","--format", action="store",dest="format", help="Output in 'excel','csv' or 'raw' " + "(Excel-format requires xlwt python module)" ) parser.add_argument("--substMapping", action="store",dest="substMapping", help="File with tab separated mappings of substance names") parser.add_argument("--markerTable", action="store",dest="markerTable", help="Table of codes to be formatted and commented") parser.add_argument( "macro",metavar="MACRO", help="A macro to use" ) args = parser.parse_args() if args.markerTable is not None: keys=["Year","GC","AC","note_1","note_2"] markerTable = DataTable(keys=keys,desc=[{"id":"Year","type":str},{"id":"GC","type":str},{"id":"AC","type":str},{"id":"note_1","type":str},{"id":"note_2","type":str}]) markerTable.read(args.markerTable) else: markerTable=None substMapping={} if args.substMapping is not None: with codecs.open(args.substMapping,encoding="HP Roman8",mode="r") as f: for line in f: oldName,newName = line.split(":") substMapping[oldName.strip()]=newName.strip() dmn = Domain() if args.gcfilter is not None: args.gcfilter = args.gcfilter.split(":") # Read original macro with codecs.open(args.macro, encoding="HP Roman8", mode="r") as f: originalContent = f.read() # Create a tmp copy of the macro, write content from the original macro macroTempFile = tempfile.NamedTemporaryFile( suffix=".sedb", dir=dmn.tmpDir() ) tmpMacro = codecs.open( macroTempFile.name, encoding="HP Roman8",mode="w" ) tmpMacro.write(originalContent) tmpMacro.flush() # Create a ControlFile obj to simplify reading and modifying macro macro = ControlFile(macroTempFile.name, removeComments=False) ebd = macro.findString("edb.edb:") user = macro.findString("edb.user:"******"edb.reportgeocode:")[-1]) acIndex = int(macro.findString("edb.reportactcode:")[-1]) if args.edbList is None: ebds = [[user, edb]] else: edbs = args.edbList.split(":") edbs = [e.split("/") for e in edbs] nedbs = len(edbs) if args.labels is None: labels = ["No label"] * len(edbs) else: labels = args.labels.split(":") if len(labels) != nedbs: log.error("Number of labels specified should match number of edb:s") sys.exit(1) if args.substances is None: log.error("Need to specify substances") sys.exit(1) else: substances = args.substances.split(":") if args.format not in ('excel','csv','raw'): log.error( "Invalid format specifier : %s, should be one of 'excel'" + ", 'csv' or 'raw'" %args.format ) sys.exit(1) elif args.format == "excel": try: import xlwt except: log.error( "trendReport.py requires python module xlwt to write excel-files") sys.exit(1) # first edb # import pdb; pdb.set_trace() edb = Edb(dmn, edbs[0][0], edbs[0][1]) # assume same code definitions in all edbs to be processed, read from first rsrc = edb.rsrc nrsubstances = len(substances) unitIndex = int(macro.findString("UNIT :")) units = rsrc.search[unitIndex] subdb = Subdb(edb) subdb.read() #decode input title using stdin encoding title=args.title.decode(sys.stdin.encoding) rawOutput = "" rawMeta = u"name: %s\nnrmacros: %i\nnrsub: %i\nunit: %s\n" %( title, nedbs, nrsubstances, units) emissions = [] for ind, edbUser in enumerate(edbs): label = labels[ind] userName = edbUser[0] edbName = edbUser[1] macro.setParam("edb.user:"******"edb.edb:", edbName) macro.setParam("USER :"******"EDB :", edbName) rawMeta += "macro.%i.edbuser: %s\n" %(ind, userName) rawMeta += "macro.%i.edbname: %s\n" %(ind, edbName) rawMeta += "macro.%i.desc: %s\n" %(ind, label) for subst in substances: log.info( "User: %s, edb: %s, substance %s" %( userName, edbName, subst) ) substanceIndex = subdb.substIndex(subst) macro.setParam("ELEMENT :", substanceIndex) macro.write() command = "xrepedb -i " + macro.name log.info("Running xrepedb for substance %s" % subst) # import pdb; pdb.set_trace() (returnCode, errMsg, outMsg) = utilities.execute(command) if returnCode != 0: log.error("Could not run %s\nstdout: %s\nstderr:%s" %( command,outMsg,errMsg)) sys.exit(1) if len(outMsg) < 10: log.error("Invalid output from xrepedb: %s" % outMsg) sys.exit(1) rawOutput += "#MACRO %i \"%s\" \"%s\"\n" % (ind, subst, labels[ind]) rawOutput += outMsg lines = outMsg.split("\n")[:-1] for lineInd, line in enumerate(lines): vals = line.split() ac = vals[1].split(".") gc = vals[3].split(".") if len(ac) == 1: if ac[0] == "<all>": acLev1 = "alla" else: acLev1 = ac[0] acLev2 = "alla" else: acLev1 = ac[0] acLev2 = ac[1] if len(gc) == 1: if gc[0] == "<all>": gcLev1 = "alla" else: gcLev1 = gc[0] gcLev2 = "alla" else: gcLev1 = gc[0] gcLev2 = gc[1] emis = float(vals[4]) if acLev1 == "alla": acLev1Name = "alla" acLev2Name = "alla" else: node = rsrc.ac[acIndex - 1].root.find(acLev1) acLev1Name = node.attrib["name"] if acLev2 == "alla": acLev2Name = "alla" else: node = rsrc.ac[acIndex-1].root.find( acLev1 + "/" + acLev2 ) acLev2Name = node.attrib["name"] if gcLev1 == "alla": gcLev1Name = "alla" gcLev2Name = "alla" else: node = rsrc.gc[gcIndex-1].root.find(gcLev1) gcLev1Name = node.attrib["name"] if gcLev2 == "alla": gcLev2Name = "alla" else: node = rsrc.gc[gcIndex - 1].root.find( gcLev1 + "/" + gcLev2 ) gcLev2Name = node.attrib["name"] if args.gcfilter is not None: if gc[0] not in args.gcfilter: # if args.gcfilter != gcLev1: continue emissions.append({"label": label, "substance": subst, "ac": '.'.join(ac), "gc": '.'.join(gc), "gcLev1": gcLev1Name, "gcLev2": gcLev2Name, "acLev1": acLev1Name, "acLev2": acLev2Name, "acLev1Code": acLev1, "acLev2Code": acLev2, "val": emis, "edbIndex": ind}) #Close tempfile to automatically remove it tmpMacro.close() if args.format == "raw": outfile = codecs.open(args.outfile,"w","HP Roman8") outfile.write(rawMeta) outfile.write(rawOutput) outfile.close() elif args.format == "csv": outfile = open(args.outfile,"w") desc = [ {'id': 'gc', 'type': unicode}, {'id': 'ac', 'type': unicode}, {'id': 'label', 'type': unicode}, {'id': 'user', 'type': unicode}, {'id': 'edb', 'type': unicode} ] for subst in substances: desc.append({'id': subst, 'type': float}) keys = ['gc', 'ac', 'label'] table = DataTable(desc=desc, keys=keys) log.info("Adding emissions to csv-table") for emis in emissions: row = [None] * len(desc) user = edbs[emis['edbIndex']][0] edb = edbs[emis['edbIndex']][1] row[table.colIndex['gc']] = emis['gc'] row[table.colIndex['ac']] = emis['ac'] row[table.colIndex['label']] = emis['label'] row[table.colIndex['user']] = user row[table.colIndex['edb']] = edb row[table.colIndex[emis['substance']]] = emis['val'] # data is appended to the correct row, or a new row is added if the # table keys do not match any existing row log.debug( "Adding row for substance %s, gc %s, ac %s" %( emis['substance'], emis['gc'], emis['ac']) ) table.addRow(row, append=True) table.write(outfile) outfile.close() else: # Create style objects for excel output header1Style = xlwt.easyxf( 'font: name Times New Roman,color-index black, bold on', num_format_str='0.000E+00' ) markerStyle1 = xlwt.easyxf( 'font: name Times New Roman,color-index red, bold off, italic on', num_format_str='0.000E+00') markerStyle2 = xlwt.easyxf( 'font: name Times New Roman,color-index orange, bold off, italic on', num_format_str='0.000E+00') normalStyle = xlwt.easyxf( 'font: name Times New Roman,color-index black, bold off', num_format_str='0.000E+00' ) excelBook = xlwt.Workbook() # Creating info sheet infoWs = excelBook.add_sheet("Info") infoWs.col(0).width = 256*20 infoWs.col(1).width = 256*25 infoWs.col(2).width = 256*20 infoWs.col(3).width = 256*200 infoWs.write(0,0,u"Rapportnamn:",header1Style) infoWs.write(0,1,title,header1Style) infoWs.write(1,0,u"Beskrivning av dataunderlaget",header1Style) infoWs.write(3,0,u"Makron (specificerar utsökningar ur databasen)",header1Style) infoWs.write(4,0,u"Etikett",header1Style) infoWs.write(4,1,u"Ägare till EDB",header1Style) infoWs.write(4,2,u"EDB (emissiondatabas)",header1Style) infoWs.write(4,3,u"Beskrivning",header1Style) for i,edbUser in enumerate(edbs): userName=edbUser[0] edbName=edbUser[1] label=labels[i] infoWs.write(5+i,0,label) infoWs.write(5+i,1,userName) infoWs.write(5+i,2,edbName) #reading edb description file (if it exists) edb=Edb(dmn,userName,edbName) infoWs.write(5+i,3,edb.desc().replace("\n"," ")) #split substances in green house gases and air quality related ghgList=[s for s in substances if s in ghgs] aqList=[s for s in substances if s not in ghgs] #Write air quality headers firstRow=4 #Add two rows for marker comments if markerTable is not None: firstRow+=2 if len(aqList)>0: aqWs = excelBook.add_sheet(u"Luftföroreningar") aqWs.col(0).width = 256*25 aqWs.col(1).width = 256*30 aqWs.col(2).width = 256*20 aqWs.col(3).width = 256*15 for col in range(nrsubstances*nedbs): aqWs.col(col+4).width=256*15 aqWs.write(0,0,u"Rapportnamn:",header1Style) aqWs.write(0,1,title,header1Style) aqWs.write(1,0,u"Emissioner av luftföroreningar",header1Style) aqWs.write(1,1,u"Enhet: "+units,header1Style) if markerTable is not None: aqWs.write(2,0,u"OBS! Röd kursiv text anger osäkra värden p.g.a. att en stor del av emissionen är fördelad med schabloner inom kommungruppen. Granska underkategorin \"Energiförsörjning via el-värmeverk samt inom industrin\" för att se eventuella misstänkta värden.",markerStyle1) aqWs.write(3,0,u"OBS! Orange kursiv text anger osäkra värden p.g.a. att trenden varierar kraftigt och eventuellt felaktigt, ytterligare verifiering krävs. Granska underkategorin \"Energiförsörjning via el-värmeverk samt inom industrin\" för att se eventuella misstänkta värden.",markerStyle2) aqWs.write(firstRow,0,"Huvudsektor",header1Style) aqWs.write(firstRow,1,"Undersektor",header1Style) aqWs.write(firstRow,2,u"Län",header1Style) aqWs.write(firstRow,3,"Kommun",header1Style) #Write ghg headers if len(ghgList)>0: ghgWs = excelBook.add_sheet(u"Växthusgaser") ghgWs.col(0).width = 256*25 ghgWs.col(1).width = 256*30 ghgWs.col(2).width = 256*20 ghgWs.col(3).width = 256*15 for col in range(nrsubstances*nedbs): ghgWs.col(col+4).width=256*15 ghgWs.write(0,0,u"Rapportnamn:",header1Style) ghgWs.write(0,1,title,header1Style) ghgWs.write(1,0,u"Emissioner av Växthusgaser",header1Style) ghgWs.write(2,0,u"CO2-ekv. efter ämnesnamn innebär att emissionen är uttryckt i CO2-ekvivalenter",header1Style) if markerTable is not None: ghgWs.write(3,0,u"OBS! Röd kursiv text anger osäkra värden p.g.a. att en stor del av emissionen är fördelad med schabloner inom kommungruppen. Granska underkategorin \"Energiförsörjning via el-värmeverk samt inom industrin\" för att se eventuella misstänkta värden.",markerStyle1) ghgWs.write(4,0,u"OBS! Orange kursiv text anger osäkra värden p.g.a. att trenden varierar kraftigt och eventuellt felaktigt, ytterligare verifiering krävs. Granska underkategorin \"Energiförsörjning via el-värmeverk samt inom industrin\" för att se eventuella misstänkta värden.",markerStyle2) ghgWs.write(1,1,u"Enhet: "+units,header1Style) ghgWs.write(firstRow,0,"Huvudsektor",header1Style) ghgWs.write(firstRow,1,"Undersektor",header1Style) ghgWs.write(firstRow,2,u"Län",header1Style) ghgWs.write(firstRow,3,"Kommun",header1Style) def getColInd(nmacros, substances,macroInd,subst): #gets the column index in excel file sInd=substances.index(subst) #Including extra columns to write CO2-equivalents nSubstWithCO2equivalents=0 for s in substances[:sInd+1]: if s in doubleColumns: nSubstWithCO2equivalents+=1 return 4 + macroInd+sInd*nmacros+nSubstWithCO2equivalents*(macroInd+1) #write macro labels and substance headers for air quality sheet for sInd,subst in enumerate(aqList): for i,edbUser in enumerate(edbs): col=getColInd(nedbs,aqList,i,subst) aqWs.write(firstRow-1,col,labels[i],header1Style) #If a substance name is given in mapping this is used, otherwise #The substance bname from the airviro substance list is used aqWs.write(firstRow,col,substMapping.get(subst,subst),header1Style) #write macro labels and substance headers for ghg sheet for sInd,subst in enumerate(ghgList): for i,edbUser in enumerate(edbs): col=getColInd(nedbs,ghgList,i,subst) #If CO2-equivalents are calculated, an extra column is needed if subst in doubleColumns: ghgWs.write(firstRow-1,col-1,labels[i],header1Style) ghgWs.write(firstRow-1,col,labels[i],header1Style) #If CO2-equivalents are calculated, an extra column is needed if subst in doubleColumns: #debug statement #print "writing subst %s in col %i and %i" %(subst,col-1,col) ghgWs.write(firstRow,col-1,substMapping.get(subst,subst),header1Style) ghgWs.write(firstRow,col,substMapping.get(subst,subst)+"CO2-ekv.",header1Style) elif subst in storedAsCO2equivalents: #debug statement #print "writing subst %s in col %i" %(subst,col) ghgWs.write(firstRow,col,substMapping.get(subst,subst)+"CO2-ekv.",header1Style) else: #debug statement #print "writing subst %s in col %i" %(subst,col) ghgWs.write(firstRow,col,substMapping.get(subst,subst),header1Style) #looping over all emissions, writing them to the correct column and row ghgRow=[] aqRow=[] for m in range(nedbs*nrsubstances+4+3*nedbs): ghgRow.append(firstRow+1) for m in range(nedbs*nrsubstances+4): aqRow.append(firstRow+1) for emis in emissions: subst = emis["substance"] emisVal=emis["val"] edbInd=emis["edbIndex"] #Check if gc, ac and year can be found in the error list #debugging marker style if markerTable is not None: TableRowInd=markerTable.rowIndices([labels[edbInd], emis["gc"], emis["ac"], "ja","*"]) if len(TableRowInd) >0: valueStyle=markerStyle1 else: TableRowInd=markerTable.rowIndices([labels[edbInd], emis["gc"], emis["ac"], "*","ja"]) if len(TableRowInd)>0: valueStyle=markerStyle2 else: valueStyle=normalStyle else: valueStyle=normalStyle if subst in ghgList: col=getColInd(nedbs,ghgList,edbInd,subst) row=ghgRow[col] if ghgRow[0]<=+row: ghgWs.write(row,0,emis["acLev1"],normalStyle) ghgWs.write(row,1,emis["acLev2"],normalStyle) ghgWs.write(row,2,emis["gcLev1"],normalStyle) ghgWs.write(row,3,emis["gcLev2"],normalStyle) ghgRow[0]+=1 #converts the emission to CO2-ekquivalents if subst in doubleColumns: ghgWs.write(row,col-1,float(emisVal),valueStyle) ghgWs.write(row,col,float(emisVal)*float(ekvFactors[subst]),valueStyle) else: ghgWs.write(row,col,float(emisVal),valueStyle) ghgRow[col]+=1 else: col=getColInd(nedbs,aqList,edbInd,subst) row=aqRow[col] if aqRow[0]<=+row: aqWs.write(row,0,emis["acLev1"],normalStyle) aqWs.write(row,1,emis["acLev2"],normalStyle) aqWs.write(row,2,emis["gcLev1"],normalStyle) aqWs.write(row,3,emis["gcLev2"],normalStyle) aqRow[0]+=1 aqWs.write(row,col,float(emisVal),valueStyle) aqRow[col]+=1 excelBook.save(args.outfile) log.info("Finished!")
def main(): #setting up parser parser=OptionParser(usage= usage, version=version) (options, args) = parser.parse_args() #Setting up logging environment logLevel=os.environ.get("LOG_LEVEL") if logLevel==None or logLevel=="": logLevel=2 logLevels={0:logging.NOTSET, 1:logging.WARNING, 2:logging.INFO, 3:logging.DEBUG} rootLogger=logging.getLogger('') logLevelObj=logLevels[int(logLevel)] #info is written to stderr #errors are written stdout rootLogger.setLevel(logLevelObj) infoHandler=logging.StreamHandler(sys.stderr) infoHandler.setLevel(logLevel) errHandler=logging.StreamHandler(sys.stdout) errHandler.setLevel(logging.ERROR) formatter = logging.Formatter('%(name)-12s: %(levelname)-8s %(message)s') infoHandler.setFormatter(formatter) errHandler.setFormatter(formatter) rootLogger.addHandler(infoHandler) rootLogger.addHandler(errHandler) logger=logging.getLogger("trendReport") if len(args)!=2: parser.error("Incorrect number of arguments") infile=path.abspath(args[0]) #file with emission data per code outfile=path.abspath(args[1]) #path for output excel report if not path.exists(infile): logger.error("Input asciiReport does not exist") sys.exit(-1) #reading emission data file f=codecs.open(infile,"r","HP Roman8") dlines=f.readlines() f.close() #parsing meta-data file dlines.pop(0) dlines.pop(0) name=dlines.pop(0)[5:].strip() nrmacros=int(dlines.pop(0).split()[1]) nrsubstances=int(dlines.pop(0).split()[1]) units=dlines.pop(0)[5:].strip() macros=[] for mIndex in range(nrmacros): edbuser=dlines.pop(0).split()[1] edbname=dlines.pop(0).split()[1] label=dlines.pop(0).split()[1] macros.append({"edbuser":edbuser,"edbname":edbname,"label":label}) #finding path to edb.rsrc for first edb in trend domainPath=os.environ["DBAS_PATH"] domainName=os.environ["AVDBNAME"] rsrcPath=path.join(domainPath,"edb",macros[0]["edbuser"],macros[0]["edbname"],"edb.rsrc") #Creating codeTable object (for look-ups of activitycode names and geocodenames) try: cTable=codetable.CodeTable(rsrcPath) except: logger.error("Could not build code tree from .../dba/edb/edb.rsrc") sys.exit(-1) #parsing emission data file macroIndex=0 emissions=[] substances=[] for line in dlines: data=line.split() if data[0]=="""#MACRO""": #if header, update substance and macro index macroIndex=int(data[1]) subst=data[2].replace("\"","") if subst not in substances: substances.append(subst) else: #else get codes and corresponding name ac=data[1] ac=ac.split(".") if ac[0]=="<all>": #add "all" to get the right depth in the dictionary ac=["all","all"] elif len(ac)==1: ac.append("all") gc=data[3] gc=gc.split(".") if gc[0]=="<all>": gc=["all","all"] elif len(gc)==1: gc.append("all") gcLev1Name=cTable.gc[ gc[0] ][ "name" ] if gc[1]!='all': gcLev2Name=cTable.gc[ gc[0] ][ gc[1] ][ "name" ] else: gcLev2Name="Alla" acLev1Name=cTable.ac[ ac[0] ][ "name" ] if ac[1]!='all': acLev2Name=cTable.ac[ ac[0] ][ ac[1] ]["name"] else: acLev2Name="Alla" val=data[4] emissions.append({"label":label,"substance":subst,"gcLev1":gcLev1Name,"gcLev2":gcLev2Name,"acLev1":acLev1Name,"acLev2":acLev2Name,"val":val,"macroIndex":macroIndex}) #Create style objects for excel output header1Style=xlwt.easyxf('font: name Times New Roman,color-index black, bold on', num_format_str='0.000E+00') normalStyle=xlwt.easyxf('font: name Times New Roman,color-index black, bold off', num_format_str='0.000E+00') excelBook=xlwt.Workbook() #Creating info sheet infoWs = excelBook.add_sheet("Info") infoWs.col(0).width = 256*20 infoWs.col(1).width = 256*25 infoWs.col(2).width = 256*20 infoWs.col(3).width = 256*200 infoWs.write(0,0,u"Rapportnamn:",header1Style) infoWs.write(0,1,name,header1Style) infoWs.write(1,0,u"Beskrivning av dataunderlaget",header1Style) infoWs.write(3,0,u"Makron (specificerar utsökningar ur databasen)",header1Style) infoWs.write(4,0,u"Etikett",header1Style) infoWs.write(4,1,u"Ägare till EDB",header1Style) infoWs.write(4,2,u"EDB (emissiondatabas)",header1Style) infoWs.write(4,3,u"Beskrivning",header1Style) for m in range(nrmacros): infoWs.write(5+m,0,macros[m]["label"]) infoWs.write(5+m,1,macros[m]["edbuser"]) infoWs.write(5+m,2,macros[m]["edbname"]) #reading edb description file (if it exists) edb=Edb(domainName,macros[m]["edbuser"],macros[m]["edbname"]) infoWs.write(5+m,3,edb.desc().replace("\n"," ")) #split substances in green house gases and air quality related ghgList=[s for s in substances if s in ghgs] aqList=[s for s in substances if s not in ghgs] sheets={} for s in substances: sheets[s]=excelBook.add_sheet(s) #Write air quality headers for subst,sheet in sheets.iteritems(): firstRow=3 sheet.col(0).width = 256*25 sheet.col(1).width = 256*30 sheet.col(2).width = 256*20 sheet.col(3).width = 256*15 for col in range(nrsubstances*nrmacros): sheet.col(col+4).width=256*15 sheet.write(0,0,u"Rapportnamn:",header1Style) sheet.write(0,1,name,header1Style) sheet.write(1,0,u"Emissioner av luftföroreningar",header1Style) sheet.write(1,1,subst,header1Style) sheet.write(firstRow,1,"Undersektor",header1Style) sheet.write(firstRow,2,u"Län",header1Style) sheet.write(firstRow,3,"Kommun",header1Style) def getColInd(nmacros,macroInd,co2Ekv=False): #gets the column index in excel file if not co2Ekv: return 4 + macroInd else: return 4+ macroInd + nmacros #write macro labels and substance headers for air quality sheet for sInd,subst in enumerate(substances): for mInd,macro in enumerate(macros): col=getColInd(nrmacros,mInd) sheets[subst].write(firstRow-1,col,macro["label"],header1Style) sheets[subst].write(firstRow,col,units,header1Style) if subst in ghgList: col=getColInd(nrmacros,mInd,co2Ekv=True) sheets[subst].write(firstRow-1,col,macro["label"],header1Style) sheets[subst].write(firstRow,col,units+u" (CO2-ekvivalenter)",header1Style) #looping over all emissions, writing them to the correct column and row sheetRow={} for s in substances: sheetRow[s]=[] #For each sheet an array with as many values as there are columns with data #Each value is initialized with the first row containing data #Each value is a counter for the row number for m in range(nrmacros*2+4): for s in substances: sheetRow[s].append(firstRow+1) for emis in emissions: subst = emis["substance"] emisVal=emis["val"] macroInd=emis["macroIndex"] col=getColInd(nrmacros,macroInd) row=sheetRow[subst][col] #If row index for sheet is not larger than current row #The meta data for the emission is written if sheetRow[subst][0]<=row: sheets[subst].write(row,0,emis["acLev1"],normalStyle) sheets[subst].write(row,1,emis["acLev2"],normalStyle) sheets[subst].write(row,2,emis["gcLev1"],normalStyle) sheets[subst].write(row,3,emis["gcLev2"],normalStyle) sheetRow[subst][0]+=1 #increment row in first col for sheet sheets[subst].write(row,col,float(emisVal),normalStyle) sheetRow[subst][col]+=1 if subst in ghgList: col=getColInd(nrmacros,macroInd,co2Ekv=True) #converts the emission to CO2-ekquivalents sheets[subst].write(row,col,float(emisVal)*float(ekvFactors[subst]),normalStyle) sheetRow[subst][col]+=1 excelBook.save(outfile)
def main(): # setting up parser parser = OptionParser(usage=usage, version=version) (options, args) = parser.parse_args() # Setting up logging environment logLevel = os.environ.get("LOG_LEVEL") if logLevel == None or logLevel == "": logLevel = 2 logLevels = {0: logging.NOTSET, 1: logging.WARNING, 2: logging.INFO, 3: logging.DEBUG} rootLogger = logging.getLogger("") logLevelObj = logLevels[int(logLevel)] # info is written to stderr # errors are written stdout rootLogger.setLevel(logLevelObj) infoHandler = logging.StreamHandler(sys.stderr) infoHandler.setLevel(logLevel) errHandler = logging.StreamHandler(sys.stdout) errHandler.setLevel(logging.ERROR) formatter = logging.Formatter("%(name)-12s: %(levelname)-8s %(message)s") infoHandler.setFormatter(formatter) errHandler.setFormatter(formatter) rootLogger.addHandler(infoHandler) rootLogger.addHandler(errHandler) logger = logging.getLogger("trendReport") if len(args) != 3: parser.error("Incorrect number of arguments") metadatafile = path.abspath(args[0]) # file with meta data for trend report infile = path.abspath(args[1]) # file with emission data per code outfile = path.abspath(args[2]) # path for output excel report if not path.exists(metadatafile): logger.error("Input asciiReport does not exist") sys.exit(-1) if not path.exists(infile): logger.error("Input asciiReport does not exist") sys.exit(-1) # reading emission data file f = open(infile, "r") dlines = f.readlines() f.close() # reading meta-data file f = codecs.open(metadatafile, "r", "HP Roman8") mdlines = f.readlines() f.close() # parsing meta-data file name = mdlines.pop(0)[5:].strip() nrmacros = int(mdlines.pop(0).split()[1]) nrsubstances = int(mdlines.pop(0).split()[1]) units = mdlines.pop(0)[5:].strip() macros = [] for mIndex in range(nrmacros): edbuser = mdlines.pop(0).split()[1] edbname = mdlines.pop(0).split()[1] label = mdlines.pop(0).split()[1] macros.append({"edbuser": edbuser, "edbname": edbname, "label": label}) # finding path to default edb.rsrc file in domain domainPath = os.environ["DBAS_PATH"] domainName = os.environ["AVDBNAME"] rsrcPath = path.join(domainPath, "dba/edb/edb.rsrc") # Creating codeTable object (for look-ups of activitycode names and geocodenames) try: cTable = codetable.CodeTable(rsrcPath) except: logger.error("Could not build code tree from .../dba/edb/edb.rsrc") sys.exit(-1) # parsing emission data file macroIndex = 0 emissions = [] substances = [] for line in dlines: data = line.split() if data[0] == """#MACRO""": # if header, update substance and macro index vals = line.split('"') macroIndex = int(data[1]) subst = vals[1] if subst not in substances: substances.append(subst) else: # else get codes and corresponding name ac = data[1] ac = ac.split(".") if ac[0] == "<all>": # add "all" to get the right depth in the dictionary ac = ["all", "all"] elif len(ac) == 1: ac.append("all") gc = data[3] gc = gc.split(".") if gc[0] == "<all>": gc = ["all", "all"] elif len(gc) == 1: gc.append("all") gcLev1Name = cTable.gc[gc[0]]["name"] if gc[1] != "all": gcLev2Name = cTable.gc[gc[0]][gc[1]]["name"] else: gcLev2Name = "Alla" acLev1Name = cTable.ac[ac[0]]["name"] if ac[1] != "all": acLev2Name = cTable.ac[ac[0]][ac[1]]["name"] else: acLev2Name = "Alla" val = data[4] emissions.append( { "label": label, "substance": subst, "gcLev1": gcLev1Name, "gcLev2": gcLev2Name, "acLev1": acLev1Name, "acLev2": acLev2Name, "val": val, "macroIndex": macroIndex, } ) # Create style objects for excel output header1Style = xlwt.easyxf("font: name Times New Roman,color-index black, bold on", num_format_str="0.000E+00") normalStyle = xlwt.easyxf("font: name Times New Roman,color-index black, bold off", num_format_str="0.000E+00") excelBook = xlwt.Workbook() # Creating info sheet infoWs = excelBook.add_sheet("Info") infoWs.col(0).width = 256 * 20 infoWs.col(1).width = 256 * 25 infoWs.col(2).width = 256 * 20 infoWs.col(3).width = 256 * 200 infoWs.write(0, 0, u"Rapportnamn:", header1Style) infoWs.write(0, 1, name, header1Style) infoWs.write(1, 0, u"Beskrivning av dataunderlaget", header1Style) infoWs.write(3, 0, u"Makron (specificerar utsökningar ur databasen)", header1Style) infoWs.write(4, 0, u"Etikett", header1Style) infoWs.write(4, 1, u"Ägare till EDB", header1Style) infoWs.write(4, 2, u"EDB (emissiondatabas)", header1Style) infoWs.write(4, 3, u"Beskrivning", header1Style) for m in range(nrmacros): infoWs.write(5 + m, 0, macros[m]["label"]) infoWs.write(5 + m, 1, macros[m]["edbuser"]) infoWs.write(5 + m, 2, macros[m]["edbname"]) # reading edb description file (if it exists) edb = Edb(domainName, macros[m]["edbuser"], macros[m]["edbname"]) infoWs.write(5 + m, 3, edb.desc().replace("\n", " ")) # split substances in green house gases and air quality related ghgList = [s for s in substances if s in ghgs] aqList = [s for s in substances if s not in ghgs] # Write air quality headers firstRow = 3 if len(aqList) > 0: aqWs = excelBook.add_sheet(u"Luftföroreningar") aqWs.col(0).width = 256 * 25 aqWs.col(1).width = 256 * 30 aqWs.col(2).width = 256 * 20 aqWs.col(3).width = 256 * 15 for col in range(nrsubstances * nrmacros): aqWs.col(col + 4).width = 256 * 15 aqWs.write(0, 0, u"Rapportnamn:", header1Style) aqWs.write(0, 1, name, header1Style) aqWs.write(1, 0, u"Emissioner av luftföroreningar", header1Style) aqWs.write(1, 1, u"Enhet: " + units, header1Style) aqWs.write(firstRow, 0, "Huvudsektor", header1Style) aqWs.write(firstRow, 1, "Undersektor", header1Style) aqWs.write(firstRow, 2, u"Län", header1Style) aqWs.write(firstRow, 3, "Kommun", header1Style) # Write ghg headers if len(ghgList) > 0: ghgWs = excelBook.add_sheet(u"Växthusgaser") ghgWs.col(0).width = 256 * 25 ghgWs.col(1).width = 256 * 30 ghgWs.col(2).width = 256 * 20 ghgWs.col(3).width = 256 * 15 for col in range(nrsubstances * nrmacros): ghgWs.col(col + 4).width = 256 * 15 ghgWs.write(0, 0, u"Rapportnamn:", header1Style) ghgWs.write(0, 1, name, header1Style) ghgWs.write(1, 0, u"Emissioner av Växthusgaser", header1Style) ghgWs.write(2, 0, "Uttryckt i CO2-ekvivalenter", header1Style) ghgWs.write(1, 1, u"Enhet: " + units, header1Style) ghgWs.write(firstRow, 0, "Huvudsektor", header1Style) ghgWs.write(firstRow, 1, "Undersektor", header1Style) ghgWs.write(firstRow, 2, u"Län", header1Style) ghgWs.write(firstRow, 3, "Kommun", header1Style) def getColInd(nmacros, substances, macroInd, subst): # gets the column index in excel file sInd = substances.index(subst) return 4 + macroInd + sInd * nmacros # write macro labels and substance headers for air quality sheet for sInd, subst in enumerate(aqList): for mInd, macro in enumerate(macros): col = getColInd(nrmacros, aqList, mInd, subst) aqWs.write(firstRow - 1, col, macro["label"], header1Style) aqWs.write(firstRow, col, subst, header1Style) # write macro labels and substance headers for ghg sheet for sInd, subst in enumerate(ghgList): for mInd, macro in enumerate(macros): col = getColInd(nrmacros, ghgList, mInd, subst) ghgWs.write(firstRow - 1, col, macro["label"], header1Style) ghgWs.write(firstRow, col, subst, header1Style) # looping over all emissions, writing them to the correct column and row ghgRow = [] aqRow = [] for m in range(nrmacros * nrsubstances + 4): ghgRow.append(firstRow + 1) for m in range(nrmacros * nrsubstances + 4): aqRow.append(firstRow + 1) for emis in emissions: subst = emis["substance"] emisVal = emis["val"] macroInd = emis["macroIndex"] if subst in ghgList: col = getColInd(nrmacros, ghgList, macroInd, subst) row = ghgRow[col] if ghgRow[0] <= +row: ghgWs.write(row, 0, emis["acLev1"], normalStyle) ghgWs.write(row, 1, emis["acLev2"], normalStyle) ghgWs.write(row, 2, emis["gcLev1"], normalStyle) ghgWs.write(row, 3, emis["gcLev2"], normalStyle) ghgRow[0] += 1 # converts the emission to CO2-ekquivalents ghgWs.write(row, col, float(emisVal) * float(ekvFactors[subst]), normalStyle) ghgRow[col] += 1 else: col = getColInd(nrmacros, aqList, macroInd, subst) row = aqRow[col] if aqRow[0] <= +row: aqWs.write(row, 0, emis["acLev1"], normalStyle) aqWs.write(row, 1, emis["acLev2"], normalStyle) aqWs.write(row, 2, emis["gcLev1"], normalStyle) aqWs.write(row, 3, emis["gcLev2"], normalStyle) aqRow[0] += 1 aqWs.write(row, col, float(emisVal), normalStyle) aqRow[col] += 1 excelBook.save(outfile)