262 lines
8.9 KiB
Python
262 lines
8.9 KiB
Python
"""Tests for the Excel writer and reader (including round-trip)."""
|
|
|
|
from __future__ import annotations
|
|
|
|
from pathlib import Path
|
|
|
|
from openpyxl import load_workbook
|
|
|
|
from mitsubishi_label_converter.excel import (
|
|
HEADER_ROW,
|
|
META_SHEET_NAME,
|
|
_unique_sheet_names,
|
|
merge_sections,
|
|
write_workbook,
|
|
)
|
|
from mitsubishi_label_converter.excel_reader import read_workbook
|
|
from mitsubishi_label_converter.models import (
|
|
GlobalVarSection,
|
|
ProjectMetadata,
|
|
StructMember,
|
|
Variable,
|
|
)
|
|
|
|
|
|
def make_sections() -> list[GlobalVarSection]:
|
|
plain = GlobalVarSection(
|
|
name="Global",
|
|
retain=False,
|
|
variables=[
|
|
Variable("version", "D450", "STRING[12]", ""),
|
|
Variable(
|
|
"PLC_FEL_STA",
|
|
"",
|
|
"ST_Din_STA",
|
|
"",
|
|
line_number=39,
|
|
members=(
|
|
StructMember("Value", "D0.0"),
|
|
StructMember("Status", "D1"),
|
|
),
|
|
),
|
|
Variable("PLC_FEL", "", "FB_Din", "PLC error active", line_number=36),
|
|
],
|
|
)
|
|
retained = GlobalVarSection(
|
|
name="Global",
|
|
retain=True,
|
|
variables=[
|
|
Variable("PulseTot", "D600", "WORD", "", line_number=31, retain=True),
|
|
],
|
|
)
|
|
return [plain, retained]
|
|
|
|
|
|
def make_metadata() -> ProjectMetadata:
|
|
return ProjectMetadata(
|
|
file_header={
|
|
"companyName": "MITSUBISHI ELECTRIC CORPORATION",
|
|
"companyURL": "http://www.mitsubishielectric.com",
|
|
"productName": "GX Works3",
|
|
"productVersion": "1.126G",
|
|
"creationDateTime": "2026-10-01T20:59:19+02:00",
|
|
},
|
|
content_header={
|
|
"name": "21014JON-B14_20260930",
|
|
"modificationDateTime": "2026-10-01T20:57:58+02:00",
|
|
},
|
|
project_add_data={"projectPlcType": "FX5U"},
|
|
)
|
|
|
|
|
|
def test_merge_sections() -> None:
|
|
"""Two Global sections (plain + retain) merge into one section."""
|
|
merged = merge_sections(make_sections())
|
|
assert len(merged) == 1
|
|
assert merged[0].name == "Global"
|
|
assert [v.name for v in merged[0].variables] == [
|
|
"version",
|
|
"PLC_FEL_STA",
|
|
"PLC_FEL",
|
|
"PulseTot",
|
|
]
|
|
# per-variable retain flags survive the merge
|
|
assert [v.retain for v in merged[0].variables] == [False, False, False, True]
|
|
|
|
|
|
def test_unique_sheet_names_deduplicates() -> None:
|
|
sections = [
|
|
GlobalVarSection("Same", False, []),
|
|
GlobalVarSection("Same", True, []),
|
|
GlobalVarSection("Same", False, []),
|
|
]
|
|
merged = merge_sections(sections)
|
|
titles = _unique_sheet_names(merged)
|
|
assert len(titles) == len(set(titles))
|
|
assert len(titles) == 1
|
|
|
|
|
|
def test_unique_sheet_names_after_merge() -> None:
|
|
"""Different names never collide after merging."""
|
|
sections = [
|
|
GlobalVarSection("Alpha", False, []),
|
|
GlobalVarSection("Beta", True, []),
|
|
]
|
|
titles = _unique_sheet_names(merge_sections(sections))
|
|
assert titles == ["Alpha", "Beta"]
|
|
|
|
|
|
def test_write_workbook_creates_expected_sheets(tmp_path: Path) -> None:
|
|
"""Plain + retain sections merge into ONE worksheet plus Meta."""
|
|
out = tmp_path / "out.xlsx"
|
|
write_workbook(make_sections(), out, metadata=make_metadata())
|
|
wb = load_workbook(out)
|
|
assert wb.sheetnames == ["Global", META_SHEET_NAME]
|
|
|
|
|
|
def test_sheet_content_with_member_rows(tmp_path: Path) -> None:
|
|
out = tmp_path / "out.xlsx"
|
|
write_workbook(make_sections(), out, metadata=make_metadata())
|
|
wb = load_workbook(out)
|
|
ws = wb["Global"]
|
|
rows = list(ws.iter_rows(values_only=True))
|
|
assert rows[0] == tuple(HEADER_ROW)
|
|
# openpyxl reads cells that were written as "" back as None
|
|
assert rows[1] == ("version", "D450", "STRING[12]", None, None, "FALSE")
|
|
# structured variable row followed by its member rows
|
|
assert rows[2][:3] == ("PLC_FEL_STA", None, "ST_Din_STA")
|
|
assert rows[2][5] == "FALSE"
|
|
# members inherit the parent's retain flag (for filtering)
|
|
assert rows[3] == (
|
|
"PLC_FEL_STA.Value", "D0.0", None, None, None, "FALSE"
|
|
)
|
|
assert rows[4] == (
|
|
"PLC_FEL_STA.Status", "D1", None, None, None, "FALSE"
|
|
)
|
|
# next variable continues after the members
|
|
assert rows[5] == ("PLC_FEL", None, "FB_Din", "PLC error active", 36, "FALSE")
|
|
# the retained variable from the second section follows, flagged TRUE
|
|
assert rows[6] == ("PulseTot", "D600", "WORD", None, 31, "TRUE")
|
|
|
|
|
|
def test_meta_sheet_content(tmp_path: Path) -> None:
|
|
out = tmp_path / "out.xlsx"
|
|
write_workbook(make_sections(), out, metadata=make_metadata())
|
|
wb = load_workbook(out)
|
|
ws = wb[META_SHEET_NAME]
|
|
rows = list(ws.iter_rows(values_only=True))
|
|
meta = {k: v for k, v in rows[1:]}
|
|
assert meta["fileHeader.productName"] == "GX Works3"
|
|
assert meta["contentHeader.name"] == "21014JON-B14_20260930"
|
|
assert meta["project.projectPlcType"] == "FX5U"
|
|
# retain flags live in the Retain column now, not in Meta
|
|
assert meta["sheet.Global.name"] == "Global"
|
|
assert "sheet.Global.retain" not in meta
|
|
|
|
|
|
def test_round_trip_excel_reader(tmp_path: Path) -> None:
|
|
"""Write then read: sections and metadata must survive intact.
|
|
|
|
The reader must split the merged worksheet back into the original two
|
|
sections (plain first, retain second), matching the input. Member rows
|
|
carrying the inherited retain flag must still regroup correctly.
|
|
"""
|
|
out = tmp_path / "out.xlsx"
|
|
sections = make_sections()
|
|
metadata = make_metadata()
|
|
write_workbook(sections, out, metadata=metadata)
|
|
|
|
read_sections, read_meta = read_workbook(out)
|
|
assert read_sections == sections
|
|
assert read_meta == metadata
|
|
|
|
|
|
def test_member_rows_carry_retain_for_filtering(tmp_path: Path) -> None:
|
|
"""Every row — variable or member — must have a non-empty Retain cell
|
|
so the Retain column can be used for Excel filtering."""
|
|
out = tmp_path / "out.xlsx"
|
|
write_workbook(make_sections(), out, metadata=make_metadata())
|
|
wb = load_workbook(out)
|
|
ws = wb["Global"]
|
|
for row in ws.iter_rows(min_row=2, values_only=True):
|
|
assert row[5] in ("TRUE", "FALSE"), (
|
|
f"row {row[0]!r} has no retain flag"
|
|
)
|
|
|
|
|
|
def test_retain_members_of_retained_variable(tmp_path: Path) -> None:
|
|
"""A structured variable with retain=True flags all its members TRUE."""
|
|
out = tmp_path / "out.xlsx"
|
|
retained_struct = GlobalVarSection(
|
|
name="Global",
|
|
retain=True,
|
|
variables=[
|
|
Variable(
|
|
"SUML_DO",
|
|
"",
|
|
"ST_Dout_STA",
|
|
"",
|
|
line_number=5,
|
|
retain=True,
|
|
members=(
|
|
StructMember("Value", "D100.0"),
|
|
StructMember("Status", "D101"),
|
|
),
|
|
),
|
|
],
|
|
)
|
|
write_workbook([retained_struct], out)
|
|
wb = load_workbook(out)
|
|
rows = list(wb["Global"].iter_rows(values_only=True))
|
|
assert rows[1] == ("SUML_DO", None, "ST_Dout_STA", None, 5, "TRUE")
|
|
assert rows[2] == ("SUML_DO.Value", "D100.0", None, None, None, "TRUE")
|
|
assert rows[3] == ("SUML_DO.Status", "D101", None, None, None, "TRUE")
|
|
|
|
|
|
def test_round_trip_regroups_interleaved_retain_rows(tmp_path: Path) -> None:
|
|
"""Manually edited sheets: retain rows moved to the top must still
|
|
regroup into the correct sections."""
|
|
out = tmp_path / "out.xlsx"
|
|
write_workbook(make_sections(), out, metadata=make_metadata())
|
|
|
|
# Simulate an edit: move the retained variable to the top of the sheet.
|
|
from openpyxl import load_workbook as lwb
|
|
|
|
wb = lwb(out)
|
|
ws = wb["Global"]
|
|
header = [c.value for c in ws[1]]
|
|
rows = [r for r in ws.iter_rows(min_row=2, values_only=True)]
|
|
pulse_row = next(r for r in rows if r[0] == "PulseTot")
|
|
others = [r for r in rows if r[0] != "PulseTot"]
|
|
# delete all and rewrite with PulseTot first
|
|
ws.delete_rows(1, ws.max_row)
|
|
for row in [header] + [pulse_row] + others:
|
|
ws.append(list(row))
|
|
wb.save(out)
|
|
|
|
read_sections, _ = read_workbook(out)
|
|
# First section in the sheet is now the retain one (first-appearance order)
|
|
assert len(read_sections) == 2
|
|
assert [v.name for v in read_sections[0].variables] == ["PulseTot"]
|
|
assert read_sections[0].retain is True
|
|
assert [v.name for v in read_sections[1].variables] == [
|
|
"version",
|
|
"PLC_FEL_STA",
|
|
"PLC_FEL",
|
|
]
|
|
assert read_sections[1].retain is False
|
|
|
|
|
|
def test_sheet_name_max_31_chars() -> None:
|
|
long_name = "A" * 40
|
|
sections = [GlobalVarSection(long_name, False, [])]
|
|
titles = _unique_sheet_names(sections)
|
|
assert len(titles[0]) <= 31
|
|
assert titles[0].startswith("A")
|
|
|
|
|
|
def test_forbidden_characters_are_replaced() -> None:
|
|
sections = [GlobalVarSection("Global/Vars[1]:x", False, [])]
|
|
titles = _unique_sheet_names(sections)
|
|
for forbidden in "[]:*?/\\":
|
|
assert forbidden not in titles[0] |