Benutzer-Werkzeuge

Webseiten-Werkzeuge


Seitenleiste

pc:excelgroups

Gruppierte Exceltabelle mit Python, pyopenxl und xml

Die Aufgabe bestand darin, die in xml vorliegenden ineinander verschachtelten Tasks einer GANT- Projektplanung (http://ganttproject.biz/about) einzulesen, etwas auszumisten und dann in Excel zu übertragen, und zwar so, das möglichst die Hierarchie der Ober- und Unteraufgaben erhalten bleibt.

Am Ende sah das dann letztlich so aus (so als Blaupause, wenn man mal wieder Excel- Gruppierungen braucht) :

#!/usr/bin/env python3
import sys
import openpyxl
from datetime import datetime
from xml.etree import ElementTree
 
def recursive_tasks(parent_task_list, level, allocations):
    result = []
    for task in parent_task_list.findall("task"):
        if task.get("id") not in allocations:
            allocs = []
        else:
            allocs = allocations[task.get("id")]
        result.append(
            {
                "level": level,
                "data": [
                    task.get("name"),
                    datetime. strptime(task.get("start"), '%Y-%m-%d'),
                    int(task.get("duration")),
                    int(task.get("complete")),
                    ", ".join(allocs),
                ],
            }
        )
        result = result + recursive_tasks(task, level + 1, allocations)
    return result
 
 
def recursive_ws_group(ws, task_list, position):
    # let's see if we can fix the group problem recursively...
    this_task_level = task_list[position]["level"]
    next_task_level = this_task_level
    while True:
        if position < len(task_list) - 1:
            position += 1
        else:  # end reached
            return position
        next_task_level = task_list[position]["level"]
        if next_task_level > this_task_level:
            end_level_position = position
            while (
                end_level_position < len(task_list) - 1
                and task_list[end_level_position]["level"] > this_task_level
            ):
                end_level_position += 1
            if (
                end_level_position < len(task_list) - 1
            ):  # we have not reeached the end, so we need to decrement by 1 to point to the correct element
                end_level_position -= 1
            if end_level_position > position:
                ws.row_dimensions.group(
                    position + 2, end_level_position+2, outline_level=this_task_level + 1 # we need to add one (1) offset more, because we have an additonal header line in the table
                )
            position = recursive_ws_group(ws, task_list, position)
        if next_task_level < this_task_level:  # end if this level reached
            return position
 
tree = ElementTree.parse(sys.argv[1])
 
# ElementTree.dump(tree)
root = tree.getroot()
 
resources_element = root.find("resources")
resources = {}
for resource in resources_element.findall("resource"):
    resources[resource.get("id")] = resource.get("name")
allocations_element = root.find("allocations")
allocations = {}
for allocation in allocations_element.findall("allocation"):
    task_id = allocation.get("task-id")
    if task_id not in allocations:
        allocations[task_id] = []
    allocations[task_id].append(resources[allocation.get("resource-id")])
tasks = root.find("tasks")
task_list = recursive_tasks(tasks, 0, allocations)
# https://stackoverflow.com/questions/27133731/folding-multiple-rows-with-openpyxl
wb = openpyxl.Workbook()
ws = wb.active
# write the header
ws.append(["Task", "Start", "Duration", "Complete", "Resources"])
for task_item in task_list:
    ws.append(task_item["data"])
 
ws.sheet_properties.outlinePr.summaryBelow = True
recursive_ws_group(ws, task_list, 0)
 
wb.save(sys.argv[2])
pc/excelgroups.txt · Zuletzt geändert: 2022/01/30 13:55 von admin