import xml.etree.ElementTree as ET
import pandas as pd
from collections import OrderedDict

tree = ET.parse('report_f1619e94_7957_4aa6_bdfc_f21e60ec9ce6_OfSite_2024_10_04_265617.xml')
root = tree.getroot()

def extract_data(element):
    data = OrderedDict()
    for child in element:
        if len(child) > 0:
            data[child.tag] = extract_data(child)
        else:
            data[child.tag] = child.text
    return data

data = extract_data(root)

def flatten_ordered_dict(d, parent_key='', sep='.'):
    items = []
    for k, v in d.items():
        new_key = f"{parent_key}{sep}{k}" if parent_key else k
        if isinstance(v, OrderedDict):
            items.extend(flatten_ordered_dict(v, new_key, sep=sep).items())
        else:
            items.append((new_key, v))
    return OrderedDict(items)

flat_data = flatten_ordered_dict(data)

df = pd.DataFrame([flat_data])

df['fio_cad_ingineer'] = df.apply(
    lambda row: f"{row['land_record.cad_works.cad_work.fio_cad_ingineer.surname']} {row['land_record.cad_works.cad_work.fio_cad_ingineer.name']} {row['land_record.cad_works.cad_work.fio_cad_ingineer.patronymic']}",
    axis=1
)

df['fio_individual'] = df.apply(
    lambda row: f"{row['restrict_records.restrict_record.right_holders.right_holder.individual.surname']} {row['restrict_records.restrict_record.right_holders.right_holder.individual.name']} {row['restrict_records.restrict_record.right_holders.right_holder.individual.patronymic']} {row['restrict_records.restrict_record.right_holders.right_holder.individual.birth_date']} {row['restrict_records.restrict_record.right_holders.right_holder.individual.birth_place']}",
    axis=1
)

df['identity_doc'] = df.apply(
    lambda row: f"{row['restrict_records.restrict_record.right_holders.right_holder.individual.identity_doc.document_name']} {row['restrict_records.restrict_record.right_holders.right_holder.individual.identity_doc.document_series']} {row['restrict_records.restrict_record.right_holders.right_holder.individual.identity_doc.document_number']} {row['restrict_records.restrict_record.right_holders.right_holder.individual.identity_doc.document_date']} {row['restrict_records.restrict_record.right_holders.right_holder.individual.identity_doc.document_issuer']}",
    axis=1
)

columns_to_keep = [
    'land_record.object.common_data.cad_number',
    'land_record.object.common_data.quarter_cad_number',
    'details_statement.group_top_requisites.registration_number',
    'land_record.params.category.type.value',
    'land_record.params.permitted_use.permitted_use_established.by_document',
    'land_record.address_location.address.readable_address',
    'land_record.cad_works.cad_work.work_type',
    'fio_cad_ingineer',
    'land_record.cad_works.cad_work.cadastral_engineer_registry_number',
    'land_record.special_notes',
    'land_record.restrictions_encumbrances.restriction_encumbrance.cad_link.reg_number_border.number',
    'land_record.restrictions_encumbrances.restriction_encumbrance.cad_link.reg_number_border.name_by_doc',
    'land_record.restrictions_encumbrances.restriction_encumbrance.cad_link.reg_number_border.type_zone.code',
    'land_record.restrictions_encumbrances.restriction_encumbrance.cad_link.reg_number_border.type_zone.value',
    'land_record.restrictions_encumbrances.restriction_encumbrance.content_restrict_encumbrances',
    'land_record.restrictions_encumbrances.restriction_encumbrance.underlying_docs.underlying_document.document_code.code',
    'land_record.restrictions_encumbrances.restriction_encumbrance.underlying_docs.underlying_document.document_code.value',
    'land_record.restrictions_encumbrances.restriction_encumbrance.underlying_docs.underlying_document.document_name',
    'land_record.restrictions_encumbrances.restriction_encumbrance.underlying_docs.underlying_document.document_number',
    'land_record.restrictions_encumbrances.restriction_encumbrance.underlying_docs.underlying_document.document_date',
    'land_record.restrictions_encumbrances.restriction_encumbrance.underlying_docs.underlying_document.document_issuer',
    'land_record.restrictions_encumbrances.restriction_encumbrance.starting_date',
    'restrict_records.restrict_record.record_info.registration_date',
    'restrict_records.restrict_record.restrictions_encumbrances_data.restriction_encumbrance_number',
    'restrict_records.restrict_record.restrictions_encumbrances_data.restriction_encumbrance_type.code',
    'restrict_records.restrict_record.restrictions_encumbrances_data.restriction_encumbrance_type.value',
    'restrict_records.restrict_record.restrictions_encumbrances_data.period.period_info.start_date',
    'fio_individual',
    'restrict_records.restrict_record.right_holders.right_holder.individual.citizenship.person_citizenship_country.citizenship_country.value',
    'restrict_records.restrict_record.right_holders.right_holder.individual.snils',
    'identity_doc',
    'restrict_records.restrict_record.underlying_documents.underlying_document.document_code.value',
    'restrict_records.restrict_record.underlying_documents.underlying_document.document_name',
    'deal_records.deal_record.deal_data.deal_type.value',
    'recipient_statement',
    'status'
]


df = df.filter(items=columns_to_keep)


df.to_excel('output.xlsx', index=False)

нужно что бы программа брала не только один xml файл а все xml которые находятся в одной папке