def main():
#-----------Setting up and unsing option parser-----------------------
parser=OptionParser(usage= usage, version=version)
parser.add_option("-u",'--user',
action="store",dest="user",
help="Name of target edb user")
parser.add_option("-e","--edb",
action="store",dest="edb",
help="Name of target edb")
parser.add_option("-i","--infile",
action="store",dest="infile",
help="Input csv file")
# parser.add_option("-y","--year",
# action="store",dest="year",
# help="Only store sources for given year")
parser.add_option("-v",dest='loglevel',
action="store_const",default=get_loglevel(),
help="produce verbose output")
parser.add_option("-t", "--template",
action="store",dest="cf",default=None,
help="Generate default controlfile")
parser.add_option("-o", "--outfile",
action="store",dest="outfile",default=None,
help="Name of outfiles (without extension)")
parser.add_option("-d","--delimiter",
action="store",dest="delimiter",default="\t",
help="Delimiter used in csv-file")
parser.add_option("-c","--filterCol",
action="store",dest="filterCol",
help="Header of column to use as filter")
parser.add_option("-f","--filterVal",
action="store",dest="filterVal",
help="Value to use in filter")
# parser.add_option("-g", "--geocodeRasterDir",
# action="store",dest="geocodeRasterDir",default=None,
# help="Directory with geocode rasters")
(options, args) = parser.parse_args()
#--------------------Init logger-----------------------
# rootLogger = logger.RootLogger(level=options.loglevel)
logging.basicConfig(
format='%(levelname)s:%(name)s: %(message)s',
level=options.loglevel,
)
global log
# log = rootLogger.getLogger(sys.argv[0])
log = logging.getLogger(parser.prog)
#-----------------Validating options-------------------
if options.cf is not None:
generateCf(path.abspath(options.cf),controlFileTemplate)
log.info("Wrote default controlfile")
return 1
if options.user is None:
log.error("Need to specify -u <user>")
return 1
if options.edb is None:
log.error("Need to specify -e <edb>")
return 1
# if options.year is None:
# log.error("Need to specify -y <year>")
# return 1
# if len(options.year)!=4:
# log.error("Year should be given with four digits")
# return 1
if len(args)!=1:
log.error("Controlfile should be given as argument")
return 1
dmn=Domain()
edb=Edb(dmn,options.user,options.edb)
if not edb.exists():
log.error("Edb %s does not exist" %options.edb)
return 1
log.info("Parsing controlfile")
cf=ControlFile(args[0])
cdbPars=re.compile("companydb\.par\.(\w*?):").findall(cf.content)
fdbPars=re.compile("facilitydb\.par\.(\w*?):").findall(cf.content)
sdbPars=re.compile("sourcedb\.par\.(\w*?):").findall(cf.content)
substEmisNr=re.compile("sourcedb\.subst_emis\.([0-9]*)\.emis").findall(cf.content)
subgrpEmisNr=re.compile("sourcedb\.subgrp_emis\.([0-9]*)\.emis").findall(cf.content)
cdbCols={}
cdbDefaults={}
for par in cdbPars:
cdbCols[par]=cf.findString("companydb.par.%s:" %par)
cdbDefaults[par]=cf.findString("companydb.par.%s.default:" %par,
optional=True,default=None)
fdbCols={}
fdbDefaults={}
for par in fdbPars:
fdbCols[par]=cf.findString("facilitydb.par.%s:" %par)
fdbDefaults[par]=cf.findString("facilitydb.par.%s.default:" %par,
optional=True,default=None)
sdbCols={}
sdbDefaults={}
for par in sdbPars:
sdbCols[par]=cf.findString("sourcedb.par.%s:" %par)
sdbDefaults[par]=cf.findString("sourcedb.par.%s.default:" %par,
optional=True,default=None)
substEmisCols={}
substEmisDefaults={}
if substEmisNr is not None:
for emisNr in substEmisNr:
cols={}
defaults={}
emisPars=re.compile("sourcedb\.subst_emis\.%s\.(\w*?):" %(emisNr)).findall(cf.content)
emisDefaultPars=re.compile(
"sourcedb\.subst_emis\.%s\.(\w*?)\.default:" %(emisNr)).findall(cf.content)
if emisPars is not None:
for par in emisPars:
cols[par]=cf.findString("sourcedb.subst_emis.%s.%s:" %(emisNr,par))
if emisDefaultPars is not None:
for par in emisDefaultPars:
defaults[par]=cf.findString("sourcedb.subst_emis.%s.%s.default:" %(emisNr,par),
optional=True,default=None)
substEmisCols[emisNr]=cols
substEmisDefaults[emisNr]=defaults
subgrpEmisCols={}
subgrpEmisDefaults={}
if subgrpEmisNr is not None:
for emisNr in subgrpEmisNr:
cols={}
defaults={}
emisPars=re.compile("sourcedb\.subgrp_emis\.%s\.(\w*?):" %(emisNr)).findall(cf.content)
emisDefaultPars=re.compile(
"sourcedb\.subgrp_emis\.%s\.(\w*?)\.default:" %(emisNr)).findall(cf.content)
if emisPars is not None:
for par in emisPars:
cols[par]=cf.findString("sourcedb.subgrp_emis.%s.%s:" %(emisNr,par))
if emisDefaultPars is not None:
for par in emisDefaultPars:
defaults[par]=cf.findString("sourcedb.subgrp_emis.%s.%s.default:" %(emisNr,par),
optional=True,default=None)
subgrpEmisCols[emisNr]=cols
subgrpEmisDefaults[emisNr]=defaults
log.info("Reading subdb...")
subdb=Subdb(edb)
subdb.read()
log.info("Reading companydb...")
companydb=Companydb(edb)
companydb.read()
log.info("Reading sourcedb...")
# source_stream = SourceStream(edb, 'w')
source_stream = open(options.outfile, 'w')
source_writer = ModelWriter(source_stream,encoding="HP Roman8")
log.info("Reading facilitydb...")
facilitydb=Facilitydb(edb)
facilitydb.read()
log.info("Reading subgrpdb")
subgrpdb=Subgrpdb(edb)
subgrpdb.read()
log.info("Reading edb.rsrc")
rsrc=Rsrc(edb.rsrcPath())
acCodeTables=[]
for i in range(rsrc.numberOfCodeTrees("ac")):
acCodeTables.append(CodeTable(rsrc.path,codeType="ac",codeIndex=i+1))
gcCodeTables=[]
for i in range(rsrc.numberOfCodeTrees("gc")):
gcCodeTables.append(CodeTable(rsrc.path,codeType="gc",codeIndex=i+1))
geocodeRasters=[]
rast1=Raster()
rast1.read("/usr/airviro/data/geo/topdown/dynamicRasters/dynamic__GEOCODE__1.txt")
rast2=Raster()
rast2.read("/usr/airviro/data/geo/topdown/dynamicRasters/dynamic__GEOCODE__2.txt")
geocodeRasters.append(rast1)
geocodeRasters.append(rast2)
log.info("Reading csv-file")
table=DataTable()
table.read(options.infile,delimiter=options.delimiter,encoding="ISO-8859-15")
if options.filterCol is not None:
if options.filterCol not in table.colIndex:
log.error("Filter column header not found in table")
sys.exit(1)
invalid=False
nFiltered=0
nRows=0
log.info("Processing rows")
for rowInd,row in enumerate(table.data):
nRows+=1
if options.filterCol is not None:
filterVal=row[table.colIndex[options.filterCol]]
if options.filterVal!=str(filterVal):
nFiltered+=1
continue
comp = Company()
for par in comp.parOrder:
val=cdbDefaults.get(par,None)
if par in cdbCols:
colId=cdbCols[par]
try:
tableVal=row[table.colIndex[colId]]
except KeyError:
log.error(
"No column with header %s, columns: %s" %(
colId,str(table.listIds())))
if tableVal is not None:
val = tableVal
if val is not None:
#Too long names are truncated
if par=="NAME" and len(val)>45:
val=val[:45]
comp[par]=val
fac = Facility()
for par in fac.parOrder:
val=fdbDefaults.get(par,None)
if par in fdbCols:
colId=fdbCols[par]
tableVal=row[table.colIndex[colId]]
if tableVal is not None:
val = tableVal
if val is not None:
#Too long names are truncated
if par=="NAME" and len(val)>45:
val=val[:45]
fac[par]=val
src = Source()
for par in ["X1", "Y1","X2","Y2",
"PX","PY","NAME","INFO","INFO2","DATE","CHANGED",
"CHIMNEY HEIGHT","GASTEMPERATURE","GAS FLOW",
"SEARCHKEY1","SEARCHKEY2","SEARCHKEY3",
"SEARCHKEY4","SEARCHKEY5","CHIMNEY OUT","CHIMNEY IN",
"HOUSE WIDTH","HOUSE HEIGHT","NOSEGMENTS","BUILD_WIDTHS",
"BUILD_HEIGHTS","BUILD_LENGTHS","BUILD_DISTFARWALL",
"BUILD_CENTER","GEOCODE","FORMULAMACRO","ALOB"]:
val=sdbDefaults.get(par,None)
if par in sdbCols:
colId=sdbCols[par]
tableVal=row[table.colIndex[colId]]
if tableVal is not None:
val = tableVal
if val is not None:
#validate code
if par=="GEOCODE" and val is not None:
gcList=val.split()
for codeIndex,code in enumerate(gcList):
if not gcCodeTables[codeIndex].hasCode(code):
log.error("Invalid geo code %s on row %i" %(code,rowInd))
invalid=True
#Too long names are truncated
if par=="NAME" and len(val)>45:
val=val[:45]
#Store in src object and convert to correct type
src._fieldvalues[par] = lazy_parse(
src, par, val)
gc1=geocodeRasters[0].getVal(src.get_coord()[0],src.get_coord()[1])
gc2=geocodeRasters[1].getVal(src.get_coord()[0],src.get_coord()[1])
src.GEOCODE = [str(int(gc1)) + "." + str(int(gc2))]
for emisNr,emis in substEmisCols.items():
substEmis={"unit":None,"ac":None,"substance":None,"emis":None}
for par in substEmis.keys():
if par in emis:
substEmis[par]=row[table.colIndex[emis[par]]]
else:
try:
substEmis[par]=substEmisDefaults[emisNr][par]
except KeyError:
log.error(
"Need to specify column or default value for subgrp emis %i" %emisNr)
substInd=subdb.substIndex(substEmis["substance"])
if substInd is None:
log.error("Invalid substance name %s on row %i" %(
substEmis["substance"],rowInd))
sys.exit(1)
try:
unit=rsrc.sub[substEmis["unit"]]
except KeyError:
log.error("Invalid unit name %s on row %i" %(emis["unit"],rowInd))
sys.exit(1)
acList=substEmis["ac"].split('\\')[0].split()
for codeIndex,code in enumerate(acList):
# if code == "2.A.4.2":
# import pdb; pdb.set_trace()
refCode = acCodeTables[codeIndex].checkCode(code)
if refCode == "-":
log.error("Invalid activity code %s on row %i" %(code,rowInd))
sys.exit(1)
if refCode != code:
acList[codeIndex] = refCode
substEmis["ac"] = acList
if substEmis["emis"] is not None and substEmis["emis"]!="0":
try:
emis = src.add_emission()
emis.UNIT = substEmis["unit"]
emis.ACTCODE = substEmis["ac"] # needs re-formatting
emis.EMISSION = float(substEmis["emis"])
emis.SUBSTANCE = substInd
emis.auto_adjust_unit(edb)
except:
# print substEmis
# log.error("Invalid substance emission on row %i" %rowInd)
invalid=True
src.EMISSION=src.EMISSION[:-1]
for emis in subgrpEmisCols.values():
subgrpEmis={"unit":None,"ac":None,"name":None,"emis":None}
for par in subgrpEmis.keys():
if par in emis:
subgrpEmis[par]=row[table.colIndex[emis[par]]]
else:
try:
subgrpEmis[par]=subgrpEmisDefaults[emisNr][par]
except KeyError:
log.error(
"Need to specify column or default value for subgrp emis %i" %emisNr)
#validating subgrp name
try:
subgrp=subgrpdb.getByName(subgrpEmis["name"])
except KeyError:
log.error("Invalid subgrp name %s on row %i" %(subgrpEmis["name"],rowInd))
invalid=True
#validating subgrp emis unit
try:
unitFactor=rsrc.subGrpEm[subgrpEmis["unit"]]
except KeyError:
log.error("Invalid unit %s for subgrp emission on row %i" %(
subgrpEmis["unit"],rowInd))
invalid=True
#validating subgrp activity code
acList=subgrpEmis["ac"].split()
for codeIndex,code in enumerate(acList):
refCode = acCodeTables[codeIndex].checkCode(code)
if refCode == "-":
log.error("Invalid activity code %s on row %i" %(code,rowInd))
invalid=True
break
if refCode != code:
acList[codeIndex] = refCode
substEmis["ac"] = acList
try:
src.addSubgrpEmis(subgrp.index,emis=subgrpEmis["emis"],unit=subgrpEmis["unit"],
ac=subgrpEmis["ac"])
except:
log.error("Invalid subgrp emission on row %i" %rowInd)
invalid=True
companydb.append(comp,force=True)
facilitydb.append(fac,force=True)
source_writer.write(src)
# sourcedb.append(src)
if invalid:
log.info("No output written due to validation errors")
sys.exit(0)
if len(companydb.items)>0:
if options.outfile is None:
log.info("Writing companydb")
else:
log.info("Writing company db to file")
companydb.write(filename=options.outfile+".companydb")
if len(facilitydb.items)>0:
if options.outfile is None:
log.info("Writing facilitydb")
else:
log.info("Writing facilitydb to file")
facilitydb.write(filename=options.outfile+".facilitydb")
# if len(sourcedb.sources)>0:
# if options.outfile is None:
# log.info("Writing sourcedb")
# else:
# log.info("Writing sourcedb to file")
# sourcedb.write(filename=options.outfile+".sourcedb")
if options.filterCol is not None:
log.info("Filtered out %i out of %i" %(nFiltered,nRows))
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!")