902 lines
32 KiB
Python
Executable file
902 lines
32 KiB
Python
Executable file
#!/usr/bin/env python3
|
||
"""Заполнение одного листа Excel результатами из Mathematica Notebook (.nb).
|
||
|
||
Все найденные диапазоны размещаются НА ОДНОМ ЛИСТЕ друг под другом:
|
||
2-3
|
||
4-5
|
||
6-7
|
||
...
|
||
|
||
Внутри каждого диапазона собираются шесть notebook-файлов:
|
||
osc_Qw1, pr_Qw1, gid_Qw1,
|
||
osc_Qw2, pr_Qw2, gid_Qw2.
|
||
|
||
Можно передать отдельные файлы, маски или корневые каталоги. Каталоги
|
||
просматриваются рекурсивно. Файлы вроде "фитирование.nb" и ".nb.old"
|
||
автоматически пропускаются.
|
||
|
||
Пример для всего дерева каталогов:
|
||
python convert_precision.py \
|
||
--template table_template \
|
||
--notebooks . \
|
||
--output table_filled.xlsx
|
||
|
||
Пример с маской:
|
||
python convert_precision.py \
|
||
--template table_template \
|
||
--notebooks "**/*_Qw1.nb" "**/*_Qw2.nb" \
|
||
--output table_filled.xlsx
|
||
"""
|
||
|
||
from __future__ import annotations
|
||
|
||
import argparse
|
||
import glob
|
||
import math
|
||
import re
|
||
import sys
|
||
from copy import copy
|
||
from decimal import Decimal, InvalidOperation, ROUND_HALF_UP
|
||
from dataclasses import dataclass
|
||
from pathlib import Path
|
||
from typing import Iterable
|
||
|
||
|
||
SCRIPT_VERSION = "2026.07.29.5-precision"
|
||
|
||
|
||
# В шаблоне один блок занимает 14 строк:
|
||
# 1-3 — заголовки, 4 — отступ, 5-12 — значения, 13-14 — отступ.
|
||
TEMPLATE_BLOCK_TOP_ROW = 1
|
||
BLOCK_HEIGHT = 14
|
||
DATA_START_OFFSET = 4
|
||
DATA_ROW_COUNT = 8
|
||
TEMPLATE_MAX_COLUMN = 22 # A:V
|
||
|
||
# Смещение группы относительно первого столбца данных своей половины.
|
||
MODEL_COLUMN_OFFSET = {
|
||
"osc": 0, # Jосц/J
|
||
"pr": 3, # Jпр/J
|
||
"gid": 6, # Jгид/J
|
||
}
|
||
|
||
MODEL_SORT_ORDER = {"osc": 0, "pr": 1, "gid": 2}
|
||
|
||
# Порядок столбцов внутри каждой группы шаблона.
|
||
METHOD_COLUMN_OFFSET = {
|
||
"heavy": 0,
|
||
"new": 1,
|
||
"light": 2,
|
||
}
|
||
|
||
# Смещение строк относительно строки Qb.
|
||
VALUE_ROW_OFFSET = {
|
||
"Qb": 0,
|
||
"Qw": 1,
|
||
"hb": 2,
|
||
"hw": 3,
|
||
"L": 4,
|
||
"Q": 5,
|
||
"int1": 6,
|
||
"int2": 7,
|
||
}
|
||
|
||
QB_TO_METHOD = {
|
||
"Qb3": "heavy",
|
||
"Qb2": "new",
|
||
"Qb1": "light",
|
||
}
|
||
|
||
GROUP_RE = re.compile(r"Cell\[CellGroupData\[\{(.*?)\},\s*Open\s*\]\]", re.S)
|
||
INPUT_LABEL_RE = re.compile(r'CellLabel->\s*"In\[(\d+)\]:="')
|
||
ASSIGNMENT_RE = re.compile(r'RowBox\[\{"([A-Za-z][A-Za-z0-9]*)",\s*"="')
|
||
SIMPLE_OUTPUT_RE = re.compile(r'Cell\[BoxData\["([^"]+)"\],\s*"Output"', re.S)
|
||
PAIR_RE = re.compile(r"(?:^|_)(\d+)-(\d+)(?=_|$)")
|
||
QW_RE = re.compile(r"qw[_-]?([12])(?:[_-]|$)", re.I)
|
||
|
||
|
||
@dataclass(frozen=True)
|
||
class NbRecord:
|
||
order: int
|
||
input_number: int
|
||
expression: str
|
||
assignments: tuple[str, ...]
|
||
value: float | None
|
||
|
||
|
||
@dataclass(frozen=True)
|
||
class Target:
|
||
pair: str
|
||
pair_left: int
|
||
pair_right: int
|
||
model: str
|
||
qw_side: int
|
||
|
||
|
||
@dataclass
|
||
class MethodValues:
|
||
Qb: float | None = None
|
||
Qw: float | None = None
|
||
hb: float | None = None
|
||
hw: float | None = None
|
||
L: float | None = None
|
||
Q: float | None = None
|
||
int1: float | None = None
|
||
int2: float | None = None
|
||
|
||
def as_dict(self) -> dict[str, float | None]:
|
||
return {
|
||
"Qb": self.Qb,
|
||
"Qw": self.Qw,
|
||
"hb": self.hb,
|
||
"hw": self.hw,
|
||
"L": self.L,
|
||
"Q": self.Q,
|
||
"int1": self.int1,
|
||
"int2": self.int2,
|
||
}
|
||
|
||
|
||
def mathematica_number_to_float(raw: str) -> float | None:
|
||
"""Преобразует 1.23`*^-4 и подобные числа Mathematica в float."""
|
||
value = raw.strip()
|
||
value = re.sub(r"`(?:\d+(?:\.\d*)?)?", "", value)
|
||
value = value.replace("*^", "e")
|
||
|
||
special = {
|
||
"Infinity": math.inf,
|
||
"-Infinity": -math.inf,
|
||
"Indeterminate": None,
|
||
}
|
||
if value in special:
|
||
return special[value]
|
||
|
||
try:
|
||
return float(value)
|
||
except ValueError:
|
||
return None
|
||
|
||
|
||
def parse_records(nb_path: Path) -> list[NbRecord]:
|
||
text = nb_path.read_text(encoding="utf-8", errors="replace")
|
||
records: list[NbRecord] = []
|
||
|
||
for order, group_match in enumerate(GROUP_RE.finditer(text)):
|
||
group = group_match.group(1)
|
||
input_label = INPUT_LABEL_RE.search(group)
|
||
if input_label is None:
|
||
continue
|
||
|
||
input_end = group.find(']], "Input"')
|
||
if input_end == -1:
|
||
input_end = group.find('], "Input"')
|
||
expression = group[:input_end] if input_end != -1 else group
|
||
|
||
outputs = SIMPLE_OUTPUT_RE.findall(group)
|
||
value = mathematica_number_to_float(outputs[-1]) if outputs else None
|
||
assignments = tuple(ASSIGNMENT_RE.findall(expression))
|
||
|
||
records.append(
|
||
NbRecord(
|
||
order=order,
|
||
input_number=int(input_label.group(1)),
|
||
expression=expression,
|
||
assignments=assignments,
|
||
value=value,
|
||
)
|
||
)
|
||
|
||
if not records:
|
||
raise ValueError(f"В файле {nb_path.name} не найдены группы Input/Output.")
|
||
return records
|
||
|
||
|
||
def is_q_result(record: NbRecord) -> bool:
|
||
expr = record.expression
|
||
return (
|
||
'FractionBox["q"' in expr
|
||
and "SqrtBox" in expr
|
||
and 'RowBox[{"S1", "*", "S2"}]' in expr
|
||
)
|
||
|
||
|
||
def is_integral(record: NbRecord) -> bool:
|
||
return 'SubsuperscriptBox["\\[Integral]"' in record.expression
|
||
|
||
|
||
def first_value_for_assignment(
|
||
records: Iterable[NbRecord], assignment_name: str
|
||
) -> float | None:
|
||
for record in records:
|
||
if assignment_name in record.assignments and record.value is not None:
|
||
return record.value
|
||
return None
|
||
|
||
|
||
def extract_methods(
|
||
records: list[NbRecord],
|
||
*,
|
||
source_path: Path | None = None,
|
||
source_label: str | None = None,
|
||
) -> dict[str, MethodValues]:
|
||
"""Выделяет heavy/new/light без привязки к номерам In[]/Out[]."""
|
||
q_indexes = [i for i, record in enumerate(records) if is_q_result(record)]
|
||
if len(q_indexes) < 3:
|
||
found_inputs = ", ".join(
|
||
f"In[{records[index].input_number}]" for index in q_indexes
|
||
) or "нет"
|
||
|
||
location_lines: list[str] = []
|
||
if source_path is not None:
|
||
try:
|
||
shown_path = source_path.resolve()
|
||
except OSError:
|
||
shown_path = source_path
|
||
location_lines.append(f"Файл: {shown_path}")
|
||
if source_label:
|
||
location_lines.append(f"Позиция: {source_label}")
|
||
|
||
location = "\n".join(location_lines)
|
||
if location:
|
||
location += "\n"
|
||
|
||
raise ValueError(
|
||
location
|
||
+ "Найдено выражений q/Sqrt[S1*S2]: "
|
||
+ f"{len(q_indexes)} (ожидалось не меньше 3).\n"
|
||
+ f"Соответствующие Input-ячейки: {found_inputs}.\n"
|
||
+ "Проверьте, что notebook полностью вычислен и сохранён "
|
||
+ "с Output-ячейками."
|
||
)
|
||
|
||
extracted: dict[str, MethodValues] = {}
|
||
previous_q_index = -1
|
||
|
||
for q_index in q_indexes:
|
||
search_window = records[previous_q_index + 1 : q_index + 1]
|
||
qb_candidates: list[tuple[int, str]] = []
|
||
for local_index, record in enumerate(search_window):
|
||
for qb_name in QB_TO_METHOD:
|
||
if qb_name in record.assignments and record.value is not None:
|
||
qb_candidates.append((local_index, qb_name))
|
||
|
||
if not qb_candidates:
|
||
previous_q_index = q_index
|
||
continue
|
||
|
||
qb_local_index, qb_name = qb_candidates[-1]
|
||
method = QB_TO_METHOD[qb_name]
|
||
section = search_window[qb_local_index:]
|
||
values = MethodValues()
|
||
values.Qb = first_value_for_assignment(section, qb_name)
|
||
values.hb = first_value_for_assignment(section, "hb")
|
||
values.Q = records[q_index].value
|
||
|
||
if values.Q is None:
|
||
q_value = first_value_for_assignment(section, "q")
|
||
s1_value = first_value_for_assignment(section, "S1")
|
||
s2_value = first_value_for_assignment(section, "S2")
|
||
if (
|
||
q_value is not None
|
||
and s1_value is not None
|
||
and s2_value is not None
|
||
and s1_value * s2_value > 0
|
||
):
|
||
values.Q = q_value / math.sqrt(s1_value * s2_value)
|
||
|
||
if method == "heavy":
|
||
values.Qw = first_value_for_assignment(section, "Qw3")
|
||
values.hw = first_value_for_assignment(section, "hw")
|
||
values.L = first_value_for_assignment(section, "L")
|
||
|
||
# Первый числовой интеграл после o1 — int1, после o2 — int2.
|
||
pending_integral: str | None = None
|
||
oscillator_values: dict[str, float | None] = {"int1": None, "int2": None}
|
||
for record in section:
|
||
if "o1" in record.assignments:
|
||
pending_integral = "int1"
|
||
oscillator_values["int1"] = record.value
|
||
continue
|
||
if "o2" in record.assignments:
|
||
pending_integral = "int2"
|
||
oscillator_values["int2"] = record.value
|
||
continue
|
||
if pending_integral and is_integral(record) and record.value is not None:
|
||
setattr(values, pending_integral, record.value)
|
||
pending_integral = None
|
||
|
||
# Аналитический запасной вариант:
|
||
# integral(0..inf) 2*x^2*o*exp(-o*x^2) dx = sqrt(pi)/(2*sqrt(o)).
|
||
for integral_name, oscillator_value in oscillator_values.items():
|
||
if (
|
||
getattr(values, integral_name) is None
|
||
and oscillator_value is not None
|
||
and oscillator_value > 0
|
||
):
|
||
setattr(
|
||
values,
|
||
integral_name,
|
||
math.sqrt(math.pi) / (2.0 * math.sqrt(oscillator_value)),
|
||
)
|
||
|
||
extracted[method] = values
|
||
previous_q_index = q_index
|
||
|
||
required_methods = {"heavy", "new", "light"}
|
||
missing_methods = required_methods - extracted.keys()
|
||
if missing_methods:
|
||
raise ValueError(
|
||
"Не удалось выделить методы: " + ", ".join(sorted(missing_methods))
|
||
)
|
||
|
||
required_by_method = {
|
||
"heavy": ("Qb", "Qw", "hb", "hw", "L", "Q", "int1", "int2"),
|
||
"new": ("Qb", "hb", "Q", "int1", "int2"),
|
||
"light": ("Qb", "hb", "Q", "int1", "int2"),
|
||
}
|
||
for method, required_names in required_by_method.items():
|
||
missing_values = [
|
||
name
|
||
for name in required_names
|
||
if getattr(extracted[method], name) is None
|
||
]
|
||
if missing_values:
|
||
raise ValueError(
|
||
f"Для метода {method} не найдены значения: "
|
||
+ ", ".join(missing_values)
|
||
)
|
||
|
||
return extracted
|
||
|
||
|
||
def extract_metadata(records: list[NbRecord]) -> dict[str, float | None]:
|
||
return {
|
||
name: first_value_for_assignment(records, name)
|
||
for name in ("Z1", "Z2", "d", "b1", "b2")
|
||
}
|
||
|
||
|
||
def detect_target(nb_path: Path) -> Target:
|
||
name = nb_path.stem
|
||
lower = name.lower()
|
||
|
||
# В имени могут быть две пары чисел: например 174-66 (состав ядра)
|
||
# и 2-3 (нужный диапазон). Берём пару соседних чисел.
|
||
pair_candidates = []
|
||
for match in PAIR_RE.finditer(name):
|
||
left = int(match.group(1))
|
||
right = int(match.group(2))
|
||
if right == left + 1:
|
||
pair_candidates.append((left, right))
|
||
|
||
if not pair_candidates:
|
||
raise ValueError(
|
||
f"В имени {nb_path.name} не найден диапазон соседних чисел вида 2-3."
|
||
)
|
||
|
||
pair_left, pair_right = pair_candidates[-1]
|
||
pair = f"{pair_left}-{pair_right}"
|
||
|
||
model: str | None = None
|
||
for candidate in MODEL_COLUMN_OFFSET:
|
||
if re.search(rf"(?:^|[_-]){candidate}(?:[_-]|$)", lower):
|
||
model = candidate
|
||
break
|
||
if model is None:
|
||
raise ValueError(
|
||
f"В имени {nb_path.name} не найден тип расчёта osc, pr или gid."
|
||
)
|
||
|
||
qw_match = QW_RE.search(lower)
|
||
if qw_match is None:
|
||
raise ValueError(f"В имени {nb_path.name} не найдено Qw1 или Qw2.")
|
||
|
||
return Target(
|
||
pair=pair,
|
||
pair_left=pair_left,
|
||
pair_right=pair_right,
|
||
model=model,
|
||
qw_side=int(qw_match.group(1)),
|
||
)
|
||
|
||
|
||
def looks_like_data_notebook(path: Path) -> bool:
|
||
if path.suffix.lower() != ".nb":
|
||
return False
|
||
try:
|
||
detect_target(path)
|
||
except ValueError:
|
||
return False
|
||
return True
|
||
|
||
|
||
def pair_sort_key(pair: str) -> tuple[int, int]:
|
||
left, right = pair.split("-", maxsplit=1)
|
||
return int(left), int(right)
|
||
|
||
|
||
def data_start_row(block_index: int) -> int:
|
||
return TEMPLATE_BLOCK_TOP_ROW + block_index * BLOCK_HEIGHT + DATA_START_OFFSET
|
||
|
||
|
||
def round_decimal_places(value: float, places: int = 3) -> float:
|
||
"""Округляет число до заданного количества знаков методом ROUND_HALF_UP."""
|
||
if not math.isfinite(value):
|
||
return value
|
||
|
||
quantizer = Decimal(1).scaleb(-places)
|
||
try:
|
||
rounded = Decimal(str(value)).quantize(quantizer, rounding=ROUND_HALF_UP)
|
||
except (InvalidOperation, ValueError):
|
||
return value
|
||
return float(rounded)
|
||
|
||
|
||
def round_significant(value: float, digits: int = 3) -> float:
|
||
"""Округляет число до заданного количества значащих цифр."""
|
||
if value == 0 or not math.isfinite(value):
|
||
return value
|
||
|
||
decimal_value = Decimal(str(value))
|
||
quantizer = Decimal(1).scaleb(decimal_value.adjusted() - digits + 1)
|
||
try:
|
||
rounded = decimal_value.quantize(quantizer, rounding=ROUND_HALF_UP)
|
||
except (InvalidOperation, ValueError):
|
||
return value
|
||
return float(rounded)
|
||
|
||
|
||
def format_metadata(pair: str, metadata: dict[str, float | None]) -> str:
|
||
def integer_or_blank(value: float | None) -> str:
|
||
if value is None:
|
||
return ""
|
||
if float(value).is_integer():
|
||
return str(int(value))
|
||
return f"{round_decimal_places(value):.3f}".replace(".", ",")
|
||
|
||
def three_decimals(value: float | None) -> str:
|
||
if value is None:
|
||
return ""
|
||
# b1 и b2 находятся внутри текстовой подписи, поэтому запятую
|
||
# задаём вручную, а завершающие нули сохраняем.
|
||
return f"{round_decimal_places(value):.3f}".replace(".", ",")
|
||
|
||
return (
|
||
f"{pair}\n"
|
||
f"Z1={integer_or_blank(metadata['Z1'])}\n"
|
||
f"Z2={integer_or_blank(metadata['Z2'])}\n"
|
||
f"d={integer_or_blank(metadata['d'])}\n"
|
||
f"b1={three_decimals(metadata['b1'])}\n"
|
||
f"b2={three_decimals(metadata['b2'])}"
|
||
)
|
||
|
||
|
||
def copy_template_block(worksheet, destination_top_row: int) -> None:
|
||
"""Копирует первый 14-строчный блок шаблона в новую позицию."""
|
||
from openpyxl.cell.cell import MergedCell
|
||
|
||
source_top = TEMPLATE_BLOCK_TOP_ROW
|
||
source_bottom = source_top + BLOCK_HEIGHT - 1
|
||
destination_bottom = destination_top_row + BLOCK_HEIGHT - 1
|
||
row_shift = destination_top_row - source_top
|
||
|
||
source_merges = [
|
||
(
|
||
merged.min_row,
|
||
merged.min_col,
|
||
merged.max_row,
|
||
merged.max_col,
|
||
)
|
||
for merged in worksheet.merged_cells.ranges
|
||
if merged.min_row >= source_top and merged.max_row <= source_bottom
|
||
]
|
||
|
||
# Удаляем старые объединения во всём целевом блоке, если они там были.
|
||
for merged in list(worksheet.merged_cells.ranges):
|
||
if not (
|
||
merged.max_row < destination_top_row
|
||
or merged.min_row > destination_bottom
|
||
):
|
||
worksheet.unmerge_cells(str(merged))
|
||
|
||
for row_offset in range(BLOCK_HEIGHT):
|
||
source_row = source_top + row_offset
|
||
destination_row = destination_top_row + row_offset
|
||
|
||
source_dimension = worksheet.row_dimensions[source_row]
|
||
destination_dimension = worksheet.row_dimensions[destination_row]
|
||
destination_dimension.height = source_dimension.height
|
||
destination_dimension.hidden = source_dimension.hidden
|
||
destination_dimension.outlineLevel = source_dimension.outlineLevel
|
||
|
||
for column in range(1, TEMPLATE_MAX_COLUMN + 1):
|
||
source_cell = worksheet.cell(row=source_row, column=column)
|
||
destination_cell = worksheet.cell(row=destination_row, column=column)
|
||
|
||
if isinstance(source_cell, MergedCell):
|
||
destination_cell.value = None
|
||
else:
|
||
destination_cell.value = source_cell.value
|
||
destination_cell.comment = copy(source_cell.comment)
|
||
if source_cell.hyperlink is not None:
|
||
destination_cell._hyperlink = copy(source_cell.hyperlink)
|
||
|
||
if getattr(source_cell, "has_style", False):
|
||
destination_cell._style = copy(source_cell._style)
|
||
destination_cell.number_format = source_cell.number_format
|
||
destination_cell.protection = copy(source_cell.protection)
|
||
destination_cell.alignment = copy(source_cell.alignment)
|
||
|
||
for min_row, min_col, max_row, max_col in source_merges:
|
||
worksheet.merge_cells(
|
||
start_row=min_row + row_shift,
|
||
start_column=min_col,
|
||
end_row=max_row + row_shift,
|
||
end_column=max_col,
|
||
)
|
||
|
||
|
||
def prepare_sheet_blocks(worksheet, pairs: list[str]) -> dict[str, int]:
|
||
"""Создаёт на одном листе одинаковые блоки для всех диапазонов."""
|
||
from openpyxl.cell.cell import MergedCell
|
||
|
||
if not pairs:
|
||
raise ValueError("Нет диапазонов для создания таблицы.")
|
||
|
||
# Первый блок уже является образцом. Все остальные копируются из него.
|
||
for block_index in range(1, len(pairs)):
|
||
destination_top = TEMPLATE_BLOCK_TOP_ROW + block_index * BLOCK_HEIGHT
|
||
copy_template_block(worksheet, destination_top)
|
||
|
||
# Удаляем лишние старые блоки шаблона, если диапазон только один.
|
||
required_last_row = TEMPLATE_BLOCK_TOP_ROW + len(pairs) * BLOCK_HEIGHT - 1
|
||
if worksheet.max_row > required_last_row:
|
||
for merged in list(worksheet.merged_cells.ranges):
|
||
if merged.min_row > required_last_row:
|
||
worksheet.unmerge_cells(str(merged))
|
||
worksheet.delete_rows(
|
||
required_last_row + 1,
|
||
worksheet.max_row - required_last_row,
|
||
)
|
||
|
||
empty_metadata = {"Z1": None, "Z2": None, "d": None, "b1": None, "b2": None}
|
||
starts: dict[str, int] = {}
|
||
|
||
for block_index, pair in enumerate(pairs):
|
||
start_row = data_start_row(block_index)
|
||
starts[pair] = start_row
|
||
|
||
# Очищаем старые числовые значения, сохраняя оформление и подписи строк.
|
||
for row in range(start_row, start_row + DATA_ROW_COUNT):
|
||
for column in list(range(3, 12)) + list(range(14, 23)):
|
||
cell = worksheet.cell(row=row, column=column)
|
||
if not isinstance(cell, MergedCell):
|
||
cell.value = None
|
||
|
||
# Подпись диапазона видна даже при отсутствии одного из файлов Qw.
|
||
worksheet.cell(row=start_row, column=1).value = format_metadata(
|
||
pair, empty_metadata
|
||
)
|
||
worksheet.cell(row=start_row, column=13).value = format_metadata(
|
||
pair, empty_metadata
|
||
)
|
||
|
||
return starts
|
||
|
||
|
||
def write_notebook_to_sheet(
|
||
worksheet,
|
||
start_row: int,
|
||
target: Target,
|
||
methods: dict[str, MethodValues],
|
||
metadata: dict[str, float | None],
|
||
written_cells: dict[str, str],
|
||
source_name: str,
|
||
) -> list[str]:
|
||
# Qw1: подпись в A, данные с C. Qw2: подпись в M, данные с N.
|
||
if target.qw_side == 1:
|
||
metadata_column = 1
|
||
first_data_column = 3
|
||
else:
|
||
metadata_column = 13
|
||
first_data_column = 14
|
||
|
||
worksheet.cell(row=start_row, column=metadata_column).value = format_metadata(
|
||
target.pair, metadata
|
||
)
|
||
|
||
report: list[str] = []
|
||
model_start_column = first_data_column + MODEL_COLUMN_OFFSET[target.model]
|
||
|
||
for method_name in ("heavy", "new", "light"):
|
||
column = model_start_column + METHOD_COLUMN_OFFSET[method_name]
|
||
values = methods[method_name].as_dict()
|
||
|
||
for value_name, row_offset in VALUE_ROW_OFFSET.items():
|
||
value = values[value_name]
|
||
if value is None:
|
||
continue
|
||
|
||
row = start_row + row_offset
|
||
coordinate = worksheet.cell(row=row, column=column).coordinate
|
||
previous_source = written_cells.get(coordinate)
|
||
if previous_source is not None and previous_source != source_name:
|
||
raise ValueError(
|
||
f"Ячейка {coordinate} уже заполнялась из {previous_source}; "
|
||
f"повторная запись из {source_name}."
|
||
)
|
||
|
||
cell = worksheet.cell(row=row, column=column)
|
||
if value_name == "Q":
|
||
# Q округляется до трёх значащих цифр, а не до трёх
|
||
# фиксированных знаков после запятой. Формат без экспоненты:
|
||
# 0,0688; 0,0000787; 0,112 (разделитель зависит от Excel).
|
||
written_value = round_significant(value, digits=3)
|
||
cell.number_format = "0.###############"
|
||
else:
|
||
# Все остальные параметры сохраняются и показываются
|
||
# с точностью до трёх знаков после запятой.
|
||
written_value = round_decimal_places(value, places=3)
|
||
cell.number_format = "0.000"
|
||
|
||
cell.value = written_value
|
||
written_cells[coordinate] = source_name
|
||
report.append(f"{coordinate}={written_value:.15g}")
|
||
|
||
return report
|
||
|
||
|
||
def expand_notebook_arguments(patterns: list[str]) -> list[Path]:
|
||
"""Разворачивает файлы, glob-маски и каталоги; каталоги ищутся рекурсивно."""
|
||
candidates: list[Path] = []
|
||
|
||
def add_candidate(path: Path) -> None:
|
||
if path.is_dir():
|
||
candidates.extend(path.rglob("*.nb"))
|
||
elif path.is_file():
|
||
candidates.append(path)
|
||
|
||
for pattern in patterns:
|
||
if glob.has_magic(pattern):
|
||
matches = [Path(item) for item in glob.glob(pattern, recursive=True)]
|
||
if not matches:
|
||
print(f"Предупреждение: маска ничего не нашла: {pattern}", file=sys.stderr)
|
||
for match in matches:
|
||
add_candidate(match)
|
||
else:
|
||
path = Path(pattern)
|
||
if not path.exists():
|
||
raise FileNotFoundError(f"Не найден путь: {pattern}")
|
||
add_candidate(path)
|
||
|
||
unique_paths: list[Path] = []
|
||
seen: set[Path] = set()
|
||
skipped: list[Path] = []
|
||
|
||
for path in candidates:
|
||
resolved = path.resolve()
|
||
if resolved in seen:
|
||
continue
|
||
seen.add(resolved)
|
||
|
||
if looks_like_data_notebook(path):
|
||
unique_paths.append(path)
|
||
else:
|
||
skipped.append(path)
|
||
|
||
for path in sorted(skipped):
|
||
print(f"Пропущен служебный notebook: {path}", file=sys.stderr)
|
||
|
||
if not unique_paths:
|
||
raise FileNotFoundError(
|
||
"Не найдено ни одного файла вида *_osc_Qw1.nb, *_pr_Qw2.nb и т. п."
|
||
)
|
||
|
||
return unique_paths
|
||
|
||
|
||
def report_missing_files(parsed_items) -> None:
|
||
expected = {
|
||
(model, qw_side)
|
||
for model in MODEL_COLUMN_OFFSET
|
||
for qw_side in (1, 2)
|
||
}
|
||
by_pair: dict[str, set[tuple[str, int]]] = {}
|
||
for _, target, _, _ in parsed_items:
|
||
by_pair.setdefault(target.pair, set()).add((target.model, target.qw_side))
|
||
|
||
for pair in sorted(by_pair, key=pair_sort_key):
|
||
missing = expected - by_pair[pair]
|
||
if missing:
|
||
missing_text = ", ".join(
|
||
f"{model}_Qw{qw_side}"
|
||
for model, qw_side in sorted(
|
||
missing, key=lambda item: (item[1], MODEL_SORT_ORDER[item[0]])
|
||
)
|
||
)
|
||
print(
|
||
f"Предупреждение: для диапазона {pair} отсутствуют: {missing_text}",
|
||
file=sys.stderr,
|
||
)
|
||
|
||
|
||
def main() -> int:
|
||
parser = argparse.ArgumentParser(description=__doc__)
|
||
parser.add_argument(
|
||
"--version",
|
||
action="version",
|
||
version=f"%(prog)s {SCRIPT_VERSION}",
|
||
)
|
||
parser.add_argument("--template", required=True, type=Path, help="Excel-шаблон")
|
||
parser.add_argument(
|
||
"--notebooks",
|
||
required=True,
|
||
nargs="+",
|
||
help=(
|
||
"Файлы, glob-маски или каталоги. Каталоги обходятся рекурсивно; "
|
||
"например: --notebooks ."
|
||
),
|
||
)
|
||
parser.add_argument("--output", required=True, type=Path, help="Итоговый .xlsx")
|
||
parser.add_argument("--sheet", default="Лист1", help="Имя общего листа шаблона")
|
||
parser.add_argument(
|
||
"--dry-run",
|
||
action="store_true",
|
||
help="Только разобрать notebook и показать найденные значения/позиции",
|
||
)
|
||
args = parser.parse_args()
|
||
|
||
print(
|
||
f"[{Path(__file__).name} {SCRIPT_VERSION}] "
|
||
"расширенная диагностика и новое округление включены",
|
||
file=sys.stderr,
|
||
)
|
||
|
||
notebook_paths = expand_notebook_arguments(args.notebooks)
|
||
parsed_items = []
|
||
seen_targets: dict[tuple[str, str, int], Path] = {}
|
||
notebook_errors: list[str] = []
|
||
|
||
for nb_path in notebook_paths:
|
||
target = detect_target(nb_path)
|
||
target_key = (target.pair, target.model, target.qw_side)
|
||
if target_key in seen_targets:
|
||
raise ValueError(
|
||
f"Два файла претендуют на {target.pair}/{target.model}/Qw{target.qw_side}: "
|
||
f"{seen_targets[target_key]} и {nb_path}"
|
||
)
|
||
seen_targets[target_key] = nb_path
|
||
|
||
try:
|
||
records = parse_records(nb_path)
|
||
# Контекст файла добавляется централизованно в обработчике ниже,
|
||
# чтобы сообщение не дублировало строки «Файл» и «Позиция».
|
||
methods = extract_methods(records)
|
||
metadata = extract_metadata(records)
|
||
except (OSError, ValueError) as exc:
|
||
# Контекст добавляется здесь БЕЗУСЛОВНО, поэтому имя проблемного
|
||
# файла выводится для любой ошибки разбора.
|
||
try:
|
||
shown_path = nb_path.resolve()
|
||
except OSError:
|
||
shown_path = nb_path
|
||
|
||
notebook_errors.append(
|
||
"\n".join(
|
||
(
|
||
"=== ПРОБЛЕМНЫЙ NOTEBOOK ===",
|
||
f"Файл: {shown_path}",
|
||
(
|
||
f"Позиция: диапазон {target.pair}, "
|
||
f"{target.model}, Qw{target.qw_side}"
|
||
),
|
||
f"Причина: {exc}",
|
||
)
|
||
)
|
||
)
|
||
continue
|
||
|
||
parsed_items.append((nb_path, target, methods, metadata))
|
||
|
||
if notebook_errors:
|
||
print(
|
||
f"Ошибка разбора notebook-файлов ({len(notebook_errors)}):",
|
||
file=sys.stderr,
|
||
)
|
||
for index, message in enumerate(notebook_errors, start=1):
|
||
indented = message.replace("\n", "\n ")
|
||
print(f" {index}. {indented}", file=sys.stderr)
|
||
print(
|
||
"Исправьте или пересчитайте перечисленные notebook-файлы "
|
||
"и запустите команду повторно.",
|
||
file=sys.stderr,
|
||
)
|
||
return 1
|
||
|
||
pairs = sorted({item[1].pair for item in parsed_items}, key=pair_sort_key)
|
||
pair_to_index = {pair: index for index, pair in enumerate(pairs)}
|
||
pair_to_start_row = {
|
||
pair: data_start_row(index) for pair, index in pair_to_index.items()
|
||
}
|
||
|
||
parsed_items.sort(
|
||
key=lambda item: (
|
||
item[1].pair_left,
|
||
item[1].pair_right,
|
||
item[1].qw_side,
|
||
MODEL_SORT_ORDER[item[1].model],
|
||
)
|
||
)
|
||
|
||
print("Диапазоны на одном листе:")
|
||
for pair in pairs:
|
||
block_index = pair_to_index[pair]
|
||
block_top = TEMPLATE_BLOCK_TOP_ROW + block_index * BLOCK_HEIGHT
|
||
print(
|
||
f" {pair}: блок строк {block_top}-{block_top + BLOCK_HEIGHT - 1}, "
|
||
f"значения с строки {pair_to_start_row[pair]}"
|
||
)
|
||
|
||
report_missing_files(parsed_items)
|
||
|
||
for nb_path, target, methods, _ in parsed_items:
|
||
print(
|
||
f"{nb_path}: pair={target.pair}, model={target.model}, "
|
||
f"Qw{target.qw_side}"
|
||
)
|
||
for method_name in ("heavy", "new", "light"):
|
||
values = methods[method_name].as_dict()
|
||
visible = ", ".join(
|
||
f"{key}={value:.12g}"
|
||
for key, value in values.items()
|
||
if value is not None
|
||
)
|
||
print(f" {method_name}: {visible}")
|
||
|
||
if args.dry_run:
|
||
return 0
|
||
|
||
try:
|
||
from openpyxl import load_workbook
|
||
except ImportError as exc:
|
||
raise SystemExit(
|
||
"Не установлен openpyxl. Выполните: python -m pip install openpyxl"
|
||
) from exc
|
||
|
||
if not args.template.exists():
|
||
raise FileNotFoundError(f"Не найден шаблон: {args.template}")
|
||
|
||
# Открытие через бинарный поток позволяет использовать шаблон без .xlsx.
|
||
with args.template.open("rb") as template_file:
|
||
workbook = load_workbook(template_file)
|
||
|
||
if args.sheet not in workbook.sheetnames:
|
||
raise KeyError(
|
||
f"В шаблоне нет листа {args.sheet!r}. Доступны: "
|
||
+ ", ".join(workbook.sheetnames)
|
||
)
|
||
worksheet = workbook[args.sheet]
|
||
|
||
actual_starts = prepare_sheet_blocks(worksheet, pairs)
|
||
written_cells: dict[str, str] = {}
|
||
|
||
for nb_path, target, methods, metadata in parsed_items:
|
||
report = write_notebook_to_sheet(
|
||
worksheet=worksheet,
|
||
start_row=actual_starts[target.pair],
|
||
target=target,
|
||
methods=methods,
|
||
metadata=metadata,
|
||
written_cells=written_cells,
|
||
source_name=str(nb_path),
|
||
)
|
||
print(f" Записано {len(report)} ячеек: " + ", ".join(report))
|
||
|
||
args.output.parent.mkdir(parents=True, exist_ok=True)
|
||
workbook.save(args.output)
|
||
print(f"Готово: {args.output}")
|
||
return 0
|
||
|
||
|
||
if __name__ == "__main__":
|
||
raise SystemExit(main())
|