createFormulaXML.py

import xlwings as xw
import pandas as pd
from pathlib import PurePath


def getWB():
    try:
        wb = xw.Book.caller()
    except:
        xw.Book(r"formula Derivation.xlsm").set_mock_caller()
        wb = xw.Book.caller()
    return wb



def createXML():

    def create_ele(formulaFactor):
        ele = f"<{formulaFactor.name}>\n\t"
        ele += f"<Position>{int(formulaFactor.PositionCode)}</Position>\n\t"
        ele += f"<keeper>{formulaFactor.GK}</keeper>\n\t"
        ele += f"<defense>{formulaFactor.DEF}</defense>\n\t"
        ele += f"<wing>{formulaFactor.WI}</wing>\n\t"
        ele += f"<playmaking>{formulaFactor.PM}</playmaking>\n\t"
        ele += f"<scoring>{formulaFactor.SC}</scoring>\n\t"
        ele += f"<passing>{formulaFactor.PS}</passing>\n\t"
        ele += f"<setpieces>{0.0}</setpieces>\n\t"
        ele += f"<normalization_factor>{formulaFactor.FACTOR}</normalization_factor>\n"
        ele += f"</{formulaFactor.name}>\n"
        return ele

    # load formula factors ================================================
    wb = getWB()
    ws = wb.sheets["Formula"]
    ws.activate()
    dfFormulaFactors = ws.range("rFormulaFactors").options(pd.DataFrame).value
    dfFormulaFactors.dropna(axis=0, inplace=True)
    dfFormulaFactors.PositionCode = dfFormulaFactors.PositionCode.astype("int64")

    # create document head   ==========================================
    doc = "<?xml version=\"1.0\" encoding=\"utf-8\"?>\n"
    doc += "<!-- Version 2.0 autogenerated by HO Analyser python script (Akasolace 27.06.2019)-->\n"
    doc += "<FormulaFactors>\n"

    # add all elements  ==========================================
    for _, row in dfFormulaFactors.iterrows():
        doc += create_ele(row)

    # terminating document   ==========================
    doc += "</FormulaFactors>"

    # writing xml ==========================
    XMLfilename = ws.range("rPathXML").value
    with open(XMLfilename, 'w') as writer:
        writer.write(doc)

    print("xml exported !")



def loadCSV():
    wb = getWB()
    ws = wb.sheets["player export"]
    ws.activate()

    ws.range("rCSV").current_region.clear_contents()

    CSVfilename = PurePath(ws.range("rCSVPath").value, "playerexport.csv")

    dfCSV = pd.read_csv(CSVfilename, index_col="Name")


    dfSubSkills = ws.range("rSubSkillCorr").options(pd.DataFrame, expand='table').value
    dfSubSkills = dfSubSkills.astype("float64")


    dfCSV[["KP", "DE", "WI", "PM", "PS", "SC", "SP"]] = dfCSV[["KP", "DE", "WI", "PM", "PS", "SC", "SP"]].add(dfSubSkills, fill_value = 0)



    ws.range("rCSV").value = dfCSV


if __name__ == '__main__':
    # Mock the calling Excel file
    # xw.Book(r"formula Derivation.xlsm").set_mock_caller()

    createXML()