示例#1
0
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)