Ознакомься с кодом: "import os
import pandas as pd
import lxml.etree as ET
from openpyxl import load_workbook
from openpyxl.styles import Font, Alignment
from openpyxl.utils import get_column_letter

# Устанавливаем рабочую директорию
os.chdir(r"C:\VSProjects\PythonProjects\parse-limits-1")

INPUT_FILE = "Limits PIMS.txt"  # Входной файл
OUTPUT_FILE = "output-limits-PIMS-new.xlsx"  # Выходной файл

VALID_SORT_NAMES = {
    "CEM I 42.5 R",
    "CEM II/A-L 42.5 R",
    "CEM II/A-M 42.5 N",
    "PC 500-D0-N",
    "FER.501-0000.00:SRT",
}

NS = {
    "rr": "http://bmi.ch.abb.com/kbs/rr",
    "nm": "http://bmi.ch.abb.com/kbs/nm",
    "db": "http://bmi.ch.abb.com/kbs/db",
}


def find_parent_report_element(property_elem):
    """Поиск родительского элемента 'REPORT30'."""
    parent = property_elem
    while parent is not None:
        if parent.tag.endswith("REPORT30"):
            return parent
        parent = parent.getparent()
    return None


def fetch_report_and_template_names(log_name, trees):
    """Извлекает уникальные пары 'Report Name' и 'Template Name' для заданного имени лога."""
    report_template_pairs = []

    for tree in trees:
        root = tree.getroot()
        for property_elem in root.findall(".//PROPERTY"):
            actual_value = property_elem.get(f"{{{NS['rr']}}}value_repitem_ritext")
            if actual_value == log_name:
                report_elem = find_parent_report_element(property_elem)
                if report_elem is not None:
                    report_name = report_elem.get(f"{{{NS['nm']}}}repname")
                    template_name = report_elem.get(
                        f"{{{NS['rr']}}}tplid_template_tplname"
                    )

                    if (
                        report_name
                        and template_name
                        and (report_name, template_name) not in report_template_pairs
                    ):
                        report_template_pairs.append((report_name, template_name))

    report_names = [pair[0] for pair in report_template_pairs]
    template_names = [pair[1] for pair in report_template_pairs]

    report_name_str = "\n".join(report_names) if report_names else "-"
    template_name_str = "\n".join(template_names) if template_names else "-"

    return report_name_str, template_name_str


def fetch_log_info(limit_name, trees):
    """Ищет лог и его описание в XML-файлах."""
    logs = []
    descriptions = []
    for tree in trees:
        root = tree.getroot()
        for repitem in root.findall(".//REPITEM"):
            limit_id = repitem.get(f"{{{NS['rr']}}}limitid_limitobject_limitname")
            if limit_id == limit_name:
                log_name = repitem.get(f"{{{NS['nm']}}}ritext")
                log_description = repitem.get(f"{{{NS['db']}}}description")
                logs.append(log_name if log_name else "-")
                descriptions.append(log_description if log_description else "-")
                # Отладочная печать только для интересующих логов
                if log_name in ["FER.221+HOUR+CLAY", "FER.221+HOUR+CLAY_TEMP"]:
                    print(
                        f"Found log for limit '{limit_name}': {log_name}, description: {log_description}"
                    )

    log_name_str = "\n".join(logs) if logs else "-"
    log_description_str = "\n".join(descriptions) if descriptions else "-"
    return log_name_str, log_description_str


def read_file(filename):
    """Чтение файла и возврат строк."""
    with open(filename, "r", encoding="utf-8") as file:
        return file.readlines()


def parse_data(lines, trees):
    """Парсинг данных из строк с учетом структуры блоков."""
    data = []
    current_block = None

    for line in lines:
        stripped_line = line.rstrip()

        if (
            "Period Sort name Start date End date LLL LL L Target Value H HH HHH"
            in stripped_line
        ):
            continue

        if stripped_line == "":
            if current_block is not None and current_block["Data"]:
                data.append(current_block)
            current_block = None
            continue

        if current_block is None:
            current_block = {
                "Limit Name": stripped_line,
                "Data": [],
            }
            if stripped_line == "Clay crusher (Operating time)":
                print(f"Processing block for: {stripped_line}")
            continue

        modified_line = stripped_line.replace("  ", " - ")
        parts = modified_line.split()

        if len(parts) < 13:
            continue

        period = "-"
        if parts[0] != "-":
            period_candidates = []
            for i, part in enumerate(parts):
                period_candidates.append(part)
                if "Primary log values" in " ".join(period_candidates):
                    period = " ".join(period_candidates)
                    parts = parts[i + 1 :]
                    break
        else:
            parts = parts[1:]

        sort_name = "-"
        if parts and parts[0] != "-":
            sort_name_candidates = []
            for i, part in enumerate(parts):
                sort_name_candidates.append(part)
                combined_sort_name = " ".join(sort_name_candidates)
                if combined_sort_name in VALID_SORT_NAMES:
                    sort_name = combined_sort_name
                    parts = parts[i + 1 :]
                    break
        else:
            parts = parts[1:]

        start_date = "-"
        if len(parts) >= 2 and parts[0] != "-":
            start_date = " ".join(parts[:2])
            parts = parts[2:]
        else:
            parts = parts[1:]

        end_date = "-"
        if len(parts) >= 2 and parts[0] != "-":
            end_date = " ".join(parts[:2])
            parts = parts[2:]
        else:
            parts = parts[1:]

        if len(parts) < 7:
            continue

        def parse_float_or_dash(value):
            try:
                return float(value) if value != "-" else value
            except ValueError:
                return "-"

        LLL = parse_float_or_dash(parts[0])
        LL = parse_float_or_dash(parts[1])
        L = parse_float_or_dash(parts[2])
        target_value = parse_float_or_dash(parts[3])
        H = parse_float_or_dash(parts[4])
        HH = parse_float_or_dash(parts[5])
        HHH = parse_float_or_dash(parts[6])

        # Получаем лог и описание по Limit Name
        log_name, log_description = fetch_log_info(current_block["Limit Name"], trees)

        # Получаем Report Name и Template Name по Log Name
        report_name, template_name = fetch_report_and_template_names(log_name, trees)

        # Ограничиваем отладочную печать только к нужным данным
        if "Clay crusher (Operating time)" in current_block["Limit Name"]:
            print(f"Log name: {log_name}")
            print(f"Report name: {report_name}")
            print(f"Template name: {template_name}")

        record = {
            "Report Name": report_name,
            "Template Name": template_name,
            "Period": period,
            "Sort Name": sort_name,
            "LLL": LLL,
            "LL": LL,
            "L": L,
            "Target Value": target_value,
            "H": H,
            "HH": HH,
            "HHH": HHH,
            "Limit Name": current_block["Limit Name"],
            "Log Name": log_name,
            "Log Description": log_description,
        }
        current_block["Data"].append(record)

    if current_block is not None and current_block["Data"]:
        data.append(current_block)

    return data


def export_to_excel(data, output_filename):
    """Экспорт собранных данных в Excel."""
    rows = []
    for block in data:
        block_rows = []

        previous_log_name = ""  # Предыдущий Log Name
        previous_log_description = ""  # Предыдущее Log Description

        for entry in block["Data"]:
            log_name = entry["Log Name"]
            log_description = entry["Log Description"]

            # Проверяем, чтобы Log Name и Log Description не дублировались
            if log_name == previous_log_name:
                log_name = "-"
                log_description = "-"

            row = [
                entry["Report Name"],
                entry["Template Name"],
                entry["Period"],
                entry["Sort Name"],
                entry["LLL"] if entry["LLL"] != "-" else "-",
                entry["LL"] if entry["LL"] != "-" else "-",
                entry["L"] if entry["L"] != "-" else "-",
                entry["Target Value"] if entry["Target Value"] != "-" else "-",
                entry["H"] if entry["H"] != "-" else "-",
                entry["HH"] if entry["HH"] != "-" else "-",
                entry["HHH"] if entry["HHH"] != "-" else "-",
                entry["Limit Name"],
                log_name,
                log_description,
            ]

            # Обновляем предыдущие значения
            previous_log_name = entry["Log Name"]
            previous_log_description = entry["Log Description"]

            block_rows.append(row)
        rows.extend(block_rows)

    columns = [
        "Report Name",
        "Template Name",
        "Period",
        "Sort Name",
        "LLL",
        "LL",
        "L",
        "Target Value",
        "H",
        "HH",
        "HHH",
        "Limit Name",
        "Log Name",
        "Log Description",
    ]

    df = pd.DataFrame(rows, columns=columns)
    df.to_excel(output_filename, index=False, header=True)

    wb = load_workbook(output_filename)
    ws = wb.active

    for col_num, column_name in enumerate(df.columns, start=1):
        cell = ws.cell(row=1, column=col_num, value=column_name)
        cell.font = Font(bold=True)
        cell.alignment = Alignment(wrap_text=True)  # Включаем перенос текста

    start_row = 2
    for block in data:
        limit_name = block["Limit Name"]
        # Вставляем строку и объединяем ячейки от C до K
        ws.insert_rows(start_row)
        ws.merge_cells(f"C{start_row}:K{start_row}")

        # Устанавливаем значение и форматирование для объединенной ячейки
        cell = ws.cell(row=start_row, column=3, value=limit_name)
        cell.alignment = Alignment(
            horizontal="center", vertical="center", wrap_text=True
        )
        cell.font = Font(bold=True)

        block_data_len = len(block["Data"])
        end_row = start_row + block_data_len

        # Объединяем ячейки для вертикального объединения
        if end_row > start_row + 1:
            for col in ["A", "B", "L", "M", "N"]:
                ws.merge_cells(f"{col}{start_row + 1}:{col}{end_row}")
                cell = ws[f"{col}{start_row + 1}"]
                cell.alignment = Alignment(
                    vertical="center", horizontal="left", wrap_text=True
                )

        start_row = end_row + 1

    # Настройка ширины столбцов
    for column in ws.columns:
        max_length = 0
        column_letter = get_column_letter(column[0].column)
        for cell in column:
            cell.alignment = Alignment(wrap_text=True)  # Включаем перенос текста
            if cell.value:
                max_length = max(max_length, len(str(cell.value)))
        adjusted_width = max_length + 2
        ws.column_dimensions[column_letter].width = adjusted_width

    wb.save(output_filename)


def main():
    lines = read_file(INPUT_FILE)
    trees = [ET.parse("1.LIMS-upd.xml"), ET.parse("2.PIMS-upd.xml")]
    data = parse_data(lines, trees)

    if data:
        export_to_excel(data, OUTPUT_FILE)
        print(
            f"Парсинг завершен и данные сохранены в '{OUTPUT_FILE}' с форматированием."
        )
    else:
        print("Нет данных для экспорта.")


if __name__ == "__main__":
    main()
"