I'm getting an XML file from an application, want to extract some information from that file and write it to an Excel file. The XML file contains data about vacations. I need data from the last month with an approval status 1, 5 or 6.

The following function returns the month and year:

    def set_month():
      year = time.localtime()[0]
      month = time.localtime()[1]
      if month == 1:
          month = 12
          year -= 1
      else: month -= 1
      return (year, month)

I assume there is a better way to do this.

Below you'll find the complete script and an example XML file. I'd be happy about any ideas to improve the code.

#!/usr/bin/env python3

# file: urlaub.py
# desc: Reads a xml file created from another software. This file contains
# vacation requests. This program search all vacation requests for the month
# ago, grabs the relevant informations and write they into an excel file.
# possible exit status if the program does not crash:
#   0: all clear
#   1: you hav a double character in your wantedInfos directory
#   2: failed to read the xml file or to get the tree root
#   3: failed to write the excel file

import time
import logging
from sys import exit
import datetime as dt
from openpyxl import Workbook as WB
from openpyxl.styles import Font
import xml.etree.ElementTree as ET

# --- logging --- #

def configure_logging(loglevel):
    Configure logging settings.
    param 1: integer
    formatstring = "%(levelname)-8s %(message)s"
    logging.basicConfig(format=formatstring, level=loglevel)
    logging.info("Loglevel set to {}".format(loglevel))

# --- misk --- #

def check_doubles():
    Checks for double (column) characters in the wantedInfos dictionary.
    char_list = []
    logging.debug("Check for double cell characters")
    for dictionary in (wantedInfos, interestedAbsence):
        for key in dictionary.keys():
            character = dictionary[key]["Col"]
            if character in char_list:
                logging.error("Double cell character found: {}".format(character))
    logging.debug("Character list {}".format(sorted(char_list)))

# --- xml processing --- #

def set_month():
    Searchs for the month ago. Returns year and month as string.
    returns: tuple of strings
    year = time.localtime()[0]
    month = time.localtime()[1]
    if month == 1:
        month = 12
        year -= 1
    else: month -= 1
    logging.info("Search for vacation requests in {} {}".format(month, year))
    return (year, month)

def absence_is_relevant(absence):
    Checks if the given absence is relevant. This includes an check for time
    and also for approval status.
    param 1: xml element
    returns: boolean
    start = absence.find(get_path(["Beginning"]))
    date = start.text.split("T")
    logging.debug("Test absence for {}".format(date))
    year, month, day = date[0].split("-")
    if int(year) == wantedYear and int(month) == wantedMonth:
        logging.debug("Absence is relevant")
        status = absence.find(get_path(["ApprovalStatus"]))
        if int(status.text) in (0, 2, 4):
            logging.debug("Status is {} ... skip.".format(status.text))
            return False
        return True
    logging.debug("Absence is not relevant")
    return False

def request_is_relevant(request):
    Checks if the given request is relevant. Walks through the absence list
    and calls absence_is_relevant for every absence found.
    param 1: xml tree element
    returns: boolean
    guid_main = request.find(get_path(["GUID"]))
    name = request.find(get_path(["RequestorFullName"]))
    logging.debug("Test for relevanz: {}".format(guid_main.text))
    for absence in request.findall(get_path(["AbsenceList", "Absence"])):
        if absence_is_relevant(absence) is True:
            logging.debug("Request is relevant")
            return True
    logging.debug("Request is not relevant")
    return False

def get_path(part_list):
    Because i cannot access the data only with the tag name, we add the
    namespace for every tree level.
    param 1: list
    returns: string
    parts = []
    for tag in part_list:
    path = "/".join(parts)
    return path

def get_full_tag(tag):
    Adds the namespace to an single xml tag.
    param 1: string
    returns: string
    return "".join((ns, tag))

def crop_namespace(node):
    param 1: string
    returns: string or none
    if node is None:
        return None
    return node.tag.split("}")[1].strip()

def process_request(request):
    Processes the given request. We walk two times through all keys from the
    wantedInfos dictionary. First time we collect all data for keys flagged
    with "Abs" is false. Second time we walk through all branches from
    absence list. If the absence is relevant, we collect all data for keys
    flagged with true. These data will be stored in a subdictionary with
    absenceCounter as key.
    param 1: xml-tree object
    returns: dictionary
    currentRequest: dict = {'AbsenceList': {}, }
    relevantCounter = 0
    absenceCounter = 0

    guid_main = request.find(get_path(["GUID"]))
    name = request.find(get_path(["RequestorFullName"]))
    logging.debug("Processing {}: {}".format(guid_main.text, name.text))
    # collect request level information
    for key in wantedInfos.keys():
        if wantedInfos[key]['Abs'] is False:
            path = get_path(wantedInfos[key]["Path"])
            logging.debug("Search for key '{}' '{}'".format(key, path))
            node = request.find(path)
            logging.debug("Found node '{}'".format(crop_namespace(node)))
            if node is None:
                value = None
                value = node.text
            currentRequest[key] = value
            logging.debug("Store: key: {}, value: '{}'".format(key, value))
    # walk through absence list and collect absence level informations
    for absence in request.findall(get_path(["AbsenceList", "Absence"])):
        absenceCounter += 1
        guid_abs = absence.find(get_path(["VacationRequestGUID"]))
        logging.debug("{}. Abs: {}".format(absenceCounter, guid_abs.text))
        if absence_is_relevant(absence) is False:
            logging.debug("Skip absence")
        logging.debug("Processing absence")
        relevantCounter += 1
        currentRequest['AbsenceList'][relevantCounter] = {}
        for key in wantedInfos.keys():
            if wantedInfos[key]['Abs'] is True:
                path = get_path(wantedInfos[key]['Path'])
                value = absence.find(path).text
                currentRequest['AbsenceList'][relevantCounter][key] = value
                logging.debug("Store: key: '{}', value: '{}'".format(value, key))
    return currentRequest

def xml_to_list():
    Collects the wanted informations from a xml tree and store these data in
    a list of dictionarys.
    returns: list of dictinaries
    counter = 0
    relevant = 0
    allVacations = []

    # read the xml tree from file and grab tree root
        tree = ET.parse(xmlfile)
        logging.debug("Reading xml-file {} successful".format(xmlfile))
        root = tree.getroot()
    except Exception as error:
        logging.error("Failed to get xml-tree root.")
    # iterate over all vacation requests and check if the request is
    # relevant.
    for request in root.findall(get_full_tag("VacationRequests")):
        counter += 1
        logging.debug("* {}. New request found".format(counter))
        guid = request.find(get_path(["GUID"]))
        name = request.find(get_path(["RequestorFullName"]))
        logging.debug("{}: {}".format(guid.text, name.text))
        if request_is_relevant(request) is False:
            # if request is relevant call function to grab all needed data.
            relevant += 1
            logging.debug("Relevant entry found: {}".format(relevant))
            currentRequest = process_request(request)
    logging.info("{} relevant requests found".format(len(allVacations)))

# --- excel --- #

def get_cell_name(character, number):
    Returns an string from column character and row number.
    param 1: string
    param 2: integer
    returns: string
    return "".join((character.strip(), str(number).strip()))

def write_sheet_header(worksheet):
    Writes the first row in the worksheet. The cell text is the key from
    wantedInfos. The column character comes from the keys "col" flag. We use
    characters because worksheet.column_dimensions cannot work with integer.
    param 1: worksheet object
    row = 1
    boldFont = Font(name="Calibri", bold = True)
    for key in wantedInfos.keys():
        width = wantedInfos[key]["Width"]
        column = wantedInfos[key]["Col"]
        worksheet.column_dimensions[column].width = width
        cell_name = get_cell_name(column, row)
        cell = worksheet[cell_name]
        cell.font = boldFont
        worksheet[cell_name] = key
        logging.debug("Column {} was set to width {}".format(key, width))
    worksheet.freeze_panes = "ZZ2"
    logging.debug("First row pinned")

def write_cell(worksheet, row, key, value):
    Writes "value" into a cell. The cell name is build from the wantedInfos
    "Col" flag for "key" and row.
    param 1: sheet object
    param 2: integer
    param 3: string
    param 4: string
    # any values needs to convert into an readable format
    if key == "Art":
        value = absenceKindDict[value]
    if key == "Tage":
        value = int(int(value) / 2)
    elif key in ("Freigabe1", "Freigabe2", "Freigabe"):
        value = approvalStatusDict[value]
    elif key in ("Start", "Ende", "Eingetr."):
        value = dt.date.fromisoformat(value.split("T")[0])
    # write
    column = wantedInfos[key]['Col']
    cell_name = get_cell_name(column, row)
    worksheet[cell_name] = value
    if key in ("Start", "Ende", "Eingetr."):
        worksheet[cell_name].number_format = "dd.mm.yyyy"
    logging.debug("{}: '{}' written".format(cell_name, value))

def write_excel(vacationList):
    Becomes the vacation requests list and writes there data into an excel
    param 1: list of dictionaries
    year = str(wantedYear)
    if wantedMonth in range(1, 10):
        month = "".join(("0", str(wantedMonth)))
    else: month = str(wantedMonth)
    fileName = "material/urlaub-{}-{}.xls".format(year, month)
    sheetName = " ".join(("urlaub", year, month))

    logging.info("Starting to write excel file {}".format(fileName))
    workbook = WB()
    workbook.iso_dates = True
    worksheet = workbook.active
    worksheet.title = sheetName

    for i in hidden_column:
        worksheet.column_dimensions[i].hidden = True
    row = 1
    # set width and write header
    # process all requests
    for request in allVacations:
        row += 1
        logging.debug("Write request into row {}: {} {}".format(row, \
            request["GUID-R"], request["Name"]))
        # write the main request data
        for key in request.keys():
            if key == "AbsenceList":
                write_cell(worksheet, row, key, request[key])
        # then we write the absence data
        for absence_nr in request["AbsenceList"].keys():
            if absence_nr > 1:
                row += 1
            guida = request["AbsenceList"][absence_nr]["GUID-A"]
            logging.debug("Write absence into row {}: {} {}".format(row, \
                absence_nr, guida))
            for key in request["AbsenceList"][absence_nr].keys():
                value = request["AbsenceList"][absence_nr][key]
                write_cell(worksheet, row, key, value)
    logging.info("{} absence request written.".format(row - 1))

        logging.info("Excel file successful written")
    except Exception as error:
        logging.critical("Failed to write excel file")

# --- start program --- #

xmlfile = "material/urlaubsantraege.xml"
ns = "{urn:orkan-software.de:schema:ORKAN}"
wantedInfos = {
    # "Eingetr.":  {"Abs": False, "Col": "A", "Width": 12, "Path": ["DateAdd"]},
    # "Kurzname":  {"Abs": False, "Col": "B", "Width": 15, "Path": ["RequestorMaMatch"]},
    "Name":      {"Abs": False, "Col": "C", "Width": 25, "Path": ["RequestorFullName"]},
    # "Tage":      {"Abs": False, "Col": "D", "Width":  8, "Path": ["RequestedHalfDays"]},
    #"Typ":       {"Abs": True, "Col": "E", "Width":  8, "Path": ["AbsenceType"]},
    # "Freigabe1":      {"Abs": False, "Col": "E", "Width": 12, "Path": ["Approval1ApprovalStatus"]},
    "Freigabe1-Name": {"Abs": False, "Col": "F", "Width": 16, "Path": ["Approval1MaMatch"]},
    # "Freigabe2":      {"Abs": False, "Col": "G", "Width": 12, "Path": ["Approval2ApprovalStatus"]},
    "Freigabe2-Name": {"Abs": False, "Col": "H", "Width": 16, "Path": ["Approval2MaMatch"]},
    "Freigabe":   {"Abs": True,  "Col": "I", "Width": 15, "Path": ["ApprovalStatus"]},
    "Art":       {"Abs": True,  "Col": "J", "Width": 15, "Path": ["AbsenceKindGuid"]},
    "Start":     {"Abs": True,  "Col": "K", "Width": 12, "Path": ["Beginning"]},
    "Ende":      {"Abs": True,  "Col": "L", "Width": 12, "Path": ["Ending"]},
    "GUID-A":    {"Abs": True,  "Col": "M", "Width": 45, "Path": ["VacationRequestGUID"]},
    "GUID-R":    {"Abs": False, "Col": "N", "Width": 45, "Path": ["GUID"]},
    # "Grund":     {"Abs": False, "Col": "O", "Width": 40, "Path": ["RejectionReason"]},
    # "Kommentar": {"Abs": False, "Col": "P", "Width": 50, "Path": ["RequestorComment"]}
interestedAbsence: dict = {
absenceKindDict = {
    "{953D3EA0-CB6A-477F-83F1-C1B4939BA0FD}": "Bildungsurlaub",
    "{DECF84D7-ADD8-4785-AD55-662B8F70C158}": "Lehrgang HWK",
    "{945EB227-B3E2-4EFF-831E-8D7D1A08281D}": "Resturlaub",
    "{EFC603E1-5CAF-4153-ADF9-A035440E34CD}": "Sonderurlaub",
    "{3F43AC39-6132-4397-B236-7D45F9E43019}": "Jahresurlaub"
approvalStatusDict = {
    '0': "Nicht einger.",
    '1': "Gelöscht (veraltet)",
    '2': "Beantragt",
    '3': "In Bearbeitung",
    '4': "Abgelehnt",
    '5': "Gebucht",
    '6': "Gelöscht"
hidden_column = ("E", "G", "M", "N")
loglevel = logging.DEBUG
wantedMonth = None
wantedYear = None

# configure_logging(loglevel)
wantedYear, wantedMonth = set_month()

allVacations = xml_to_list()

and the XML file

<VacationRequestsList xmlns="urn:orkan-software.de:schema:ORKAN">
  <RequestorFullName>Christian Alpha</RequestorFullName>
  <Approval1MaFullName>Daniel Lambda</Approval1MaFullName>
  <Approval2MaFullName>Karsten Kappa</Approval2MaFullName>



Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.