def ascii2xls(asciiName, xlsName, substMapping):
# reading emission data file
f = codecs.open(filename=asciiName, mode="r", encoding="HP Roman8")
dlines = f.readlines()
f.close()
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 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,
"ac": ac.join("."),
"gc": gc.join("."),
}
)
# 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")
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"
)
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 = pyEdb.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 markerTablePath 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 * 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)
if markerTablePath is not None:
aqWs.write(
2,
0,
u"OBS! Röd kursiv text anger att dataunderlaget är otillräckligt och att siffran avviker markant frÔn andra källor",
markerStyle1,
)
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)
if markerTablePath is not None:
ghgWs.write(
3,
0,
u"OBS! Röd kursiv text anger att dataunderlaget är otillräckligt och att siffran avviker markant frÔn andra källor",
markerStyle1,
)
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)
# If a substance name is specified in the controlfile this is used, otherwise
# The substance bname from the airviro substance list is used
if subst in substMapping:
aqWs.write(firstRow, col, substMapping[subst], header1Style)
else:
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)
# If a substance name is specified in the controlfile this is used, otherwise
# The substance bname from the airviro substance list is used
if subst in substMapping:
ghgWs.write(firstRow, col, substMapping[subst], header1Style)
else:
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"]
macro = macros[macroInd]
if subst in ghgList:
col = getColInd(nrmacros, ghgList, macroInd, subst)
row = ghgRow[col]
# Check if gc, ac and year can be found in the error list
nTableRowInd = markerTable.rowIndices([macro["label"], emis["gc"], emis["ac"], "ja", "*"])
if nTableRowInd > 0:
valueStyle = markerStyle1
else:
nTableRowInd = markerTable.rowIndices([macro["label"], emis["gc"], emis["ac"], "*", "ja"])
if nTableRowInd > 0:
valueStyle = markerStyle2
else:
valueStyle = normalStyle
if ghgRow[0] <= +row:
ghgWs.write(row, 0, emis["acLev1"], valueStyle)
ghgWs.write(row, 1, emis["acLev2"], valueStyle)
ghgWs.write(row, 2, emis["gcLev1"], valueStyle)
ghgWs.write(row, 3, emis["gcLev2"], valueStyle)
ghgRow[0] += 1
# converts the emission to CO2-ekquivalents
ghgWs.write(row, col, float(emisVal) * float(ekvFactors[subst]), valueStyle)
ghgRow[col] += 1
else:
col = getColInd(nrmacros, aqList, macroInd, subst)
row = aqRow[col]
if aqRow[0] <= +row:
aqWs.write(row, 0, emis["acLev1"], valueStyle)
aqWs.write(row, 1, emis["acLev2"], valueStyle)
aqWs.write(row, 2, emis["gcLev1"], valueStyle)
aqWs.write(row, 3, emis["gcLev2"], valueStyle)
aqRow[0] += 1
aqWs.write(row, col, float(emisVal), valueStyle)
aqRow[col] += 1
excelBook.save(xlsName)
def ascii2xls(asciiName,xlsName,substMapping,markerTable=None):
#reading emission data file
f=codecs.open(filename=asciiName,mode="r",encoding="HP Roman8")
dlines=f.readlines()
f.close()
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 default edb.rsrc file in domain
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,"ac":".".join(ac),"gc":".".join(gc)})
#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')
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')
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=pyEdb.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=4
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*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)
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
#Three extra columns are adjusted to account for double columns in case of CO2-equivalents
for col in range((nrsubstances+3)*nrmacros):
ghgWs.col(col+4).width=256*20
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,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 mInd,macro in enumerate(macros):
col=getColInd(nrmacros,aqList,mInd,subst)
aqWs.write(firstRow-1,col,macro["label"],header1Style)
#If a substance name is specified in the controlfile 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 mInd,macro in enumerate(macros):
col=getColInd(nrmacros,ghgList,mInd,subst)
#If CO2-equivalents are calculated, an extra column is needed
if subst in doubleColumns:
ghgWs.write(firstRow-1,col-1,macro["label"],header1Style)
ghgWs.write(firstRow-1,col,macro["label"],header1Style)
#If a substance name is specified in the controlfile this is used, otherwise
#The substance bname from the airviro substance list is used
# ghgWs.write(firstRow,col,substMapping.get(subst,subst),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
#Creating a lists with max row index for each column
ghgRow=[]
aqRow=[]
for m in range(nrmacros*nrsubstances+4+3*nrmacros):
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"]
macro = macros[macroInd]
#Check if gc, ac and year can be found in the error list
#debugging marker style
#if macro["label"]=="2005" and emis["ac"]=="1.1" and emis["gc"]=="1.183":
# pdb.set_trace()
if markerTable is not None:
TableRowInd=markerTable.rowIndices([macro["label"],emis["gc"],emis["ac"],"ja","*"])
if len(TableRowInd) >0:
valueStyle=markerStyle1
else:
TableRowInd=markerTable.rowIndices([macro["label"],emis["gc"],emis["ac"],"*","ja"])
if len(TableRowInd)>0:
valueStyle=markerStyle2
else:
valueStyle=normalStyle
else:
valueStyle=normalStyle
if subst in ghgList:
col=getColInd(nrmacros,ghgList,macroInd,subst)
row=ghgRow[col]
if ghgRow[0]<=+row:
ghgWs.write(row,0,emis["acLev1"],valueStyle)
ghgWs.write(row,1,emis["acLev2"],valueStyle)
ghgWs.write(row,2,emis["gcLev1"],valueStyle)
ghgWs.write(row,3,emis["gcLev2"],valueStyle)
ghgRow[0]+=1
#converts the emission to CO2-equivalents
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(nrmacros,aqList,macroInd,subst)
row=aqRow[col]
if aqRow[0]<=+row:
aqWs.write(row,0,emis["acLev1"],valueStyle)
aqWs.write(row,1,emis["acLev2"],valueStyle)
aqWs.write(row,2,emis["gcLev1"],valueStyle)
aqWs.write(row,3,emis["gcLev2"],valueStyle)
aqRow[0]+=1
aqWs.write(row,col,float(emisVal),valueStyle)
aqRow[col]+=1
excelBook.save(xlsName)
