Excel for channel tables, both ways.
A channel table is the one visualdynamics object that is a spreadsheet — rows of
per-channel text — so it gets a spreadsheet format. Nothing is lost in the
writing: every column goes out in order, 'channel' as the integer it is
and the rest as the strings they are. Units here are metadata, not values,
so there is nothing to convert and unit_system is accepted and unused.
Reading it back matters as much as writing it. A channel table arrives
as a spreadsheet far more often than as anything else — a calibration
lab sends one, a controller writes one — and until this existed the
export was a one-way door: a table dragged out of the window could not
be dragged back in.
Functions:
| Name |
Description |
sniff |
Is this a spreadsheet holding a channel table?
|
load |
The spreadsheet as a ChannelTable.
|
Classes
Functions:
sniff(path: str | PathLike) -> bool
Is this a spreadsheet holding a channel table?
The extension is not enough — an .xlsx may hold anything — so this
opens it and looks for a header row naming a channel column. Wrong
guesses here are expensive: this importer is asked about every file
dropped on the window.
Source code in src/visualdynamics/io/excel.py
| def sniff(path: str | os.PathLike) -> bool:
"""Is this a spreadsheet holding a channel table?
The extension is not enough — an `.xlsx` may hold anything — so this
opens it and looks for a header row naming a channel column. Wrong
guesses here are expensive: this importer is asked about every file
dropped on the window.
"""
if not str(path).lower().endswith(('.xlsx', '.xlsm')):
return False
try:
from openpyxl import load_workbook
book = load_workbook(str(path), read_only=True, data_only=True)
try:
return _header_row(book.active) is not None
finally:
book.close()
except Exception: # noqa: BLE001 — an unreadable file is not ours
return False
|
The spreadsheet as a ChannelTable.
Every column is kept, in the file's own order, under the name its
header spells — the same rule the object itself follows, so whatever
a lab or a controller put in the file survives the trip. Values are
read as text, because that is what the object stores: a serial
number that happens to be all digits is not a number, and one with a
leading zero would come back a different string if it were.
Blank trailing rows are dropped; a blank column header is not a
column and its cells go with it.
Source code in src/visualdynamics/io/excel.py
| def load(path: str | os.PathLike, **_kwargs: Any) -> ChannelTable:
"""The spreadsheet as a ChannelTable.
Every column is kept, in the file's own order, under the name its
header spells — the same rule the object itself follows, so whatever
a lab or a controller put in the file survives the trip. Values are
read as **text**, because that is what the object stores: a serial
number that happens to be all digits is not a number, and one with a
leading zero would come back a different string if it were.
Blank trailing rows are dropped; a blank *column* header is not a
column and its cells go with it.
"""
from openpyxl import load_workbook
from ..core.channel_table import ChannelTable
book = load_workbook(str(path), read_only=True, data_only=True)
try:
sheet = book.active
found = _header_row(sheet)
if found is None:
raise ValueError(
f'{os.path.basename(str(path))} has no channel table in it: '
f'no row in the first {HEADER_SEARCH_ROWS} names a channel '
f'column')
number, keys = found
# a column named twice — 'Node' beside 'Node Number', say — keeps
# the first, since the second would silently overwrite it
seen: set[str] = set()
wanted = []
for i, key in enumerate(keys):
if key and key not in seen:
seen.add(key)
wanted.append((i, key))
columns: dict[str, list[str]] = {key: [] for _i, key in wanted}
for row in sheet.iter_rows(min_row=number + 1, values_only=True):
values = [row[i] if i < len(row) else None for i, _k in wanted]
# a row of nothing is the end of the table, not a channel
if all(v is None or str(v).strip() == '' for v in values):
continue
for (_i, key), value in zip(wanted, values):
columns[key].append('' if value is None else str(value).strip())
finally:
book.close()
if not columns.get('channel'):
raise ValueError(f'{os.path.basename(str(path))} has a channel '
f'header but no channels under it')
# the object needs its four; anything the file did not say is blank,
# which is a channel waiting to be told rather than a refusal
for core in ('channel', 'node', 'direction', 'unit'):
columns.setdefault(core, [''] * len(columns['channel']))
return ChannelTable(columns)
|