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)) exit(1) char_list.append(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: parts.append(get_full_tag(tag)) 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 else: 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") continue 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 try: 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.") logging.error(error) exit(2) # 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: continue else: # if request is relevant call function to grab all needed data. relevant += 1 logging.debug("Relevant entry found: {}".format(relevant)) currentRequest = process_request(request) allVacations.append(currentRequest) logging.info("{} relevant requests found".format(len(allVacations))) return(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 file. 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 write_sheet_header(worksheet) # 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": continue else: 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)) try: workbook.save(fileName) logging.info("Excel file successful written") except Exception as error: logging.critical("Failed to write excel file") exit(3) # --- 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) check_doubles() wantedYear, wantedMonth = set_month() allVacations = xml_to_list() write_excel(allVacations) and the XML file
<VacationRequestsList xmlns="urn:orkan-software.de:schema:ORKAN"> <VacationRequests> <DateAdd>2023-01-22</DateAdd> <RequestorMaMatch>ALPHA</RequestorMaMatch> <ApprovalStatus>5</ApprovalStatus> <FilingDateDate>2023-02-22</FilingDateDate> <RequestedHalfDays>30</RequestedHalfDays> <Approval1RcptTyp>0</Approval1RcptTyp> <Approval1RcptMatch>LAMBDA</Approval1RcptMatch> <Approval1ApprovalStatus>5</Approval1ApprovalStatus> <Approval1TimeStampPutDate>2023-02-23</Approval1TimeStampPutDate> <Approval1MaMatch>LAMBDA</Approval1MaMatch> <Approval2ApprovalStatus>5</Approval2ApprovalStatus> <Approval2TimeStampPutDate>2023-02-23</Approval2TimeStampPutDate> <Approval2MaMatch>KAPPA</Approval2MaMatch> <RequestorFullName>Christian Alpha</RequestorFullName> <Approval1MaFullName>Daniel Lambda</Approval1MaFullName> <Approval2MaFullName>Karsten Kappa</Approval2MaFullName> <AbsenceList> <Absence> <DateAdd>2023-01-22</DateAdd> <MaMatch>ALPHA</MaMatch> <AbsenceType>1</AbsenceType> <AbsenceAccountChange>10</AbsenceAccountChange> <Origin>0</Origin> <Beginning>2023-07-17T00:00:00.00+02:00</Beginning> <BeginningDate>2023-07-17</BeginningDate> <Ending>2023-07-21T23:59:59.99+02:00</Ending> <EndingDate>07:231:07.21</EndingDate> <ApprovalStatus>5</ApprovalStatus> </Absence> <Absence> <DateAdd>2023-01-22</DateAdd> <MaMatch>ALPHA</MaMatch> <AbsenceType>1</AbsenceType> <AbsenceAccountChange>10</AbsenceAccountChange> <Origin>0</Origin> <Beginning>2023-07-24T00:00:00.00+02:00</Beginning> <BeginningDate>2023-07-24</BeginningDate> <Ending>2023-07-28T23:59:59.99+02:00</Ending> <EndingDate>07:231:07.28</EndingDate> <ApprovalStatus>5</ApprovalStatus> </Absence> <Absence> <DateAdd>2023-01-22</DateAdd> <MaMatch>ALPHA</MaMatch> <AbsenceType>1</AbsenceType> <AbsenceAccountChange>10</AbsenceAccountChange> <Origin>0</Origin> <Beginning>2023-07-31T00:00:00.00+02:00</Beginning> <BeginningDate>2023-07-31</BeginningDate> <Ending>2023-08-04T23:59:59.99+02:00</Ending> <EndingDate>07:231:08.04</EndingDate> <ApprovalStatus>5</ApprovalStatus> </Absence> </AbsenceList> </VacationRequests> </VacationRequestsList> ```
