Workbooks a file depends on, and how far linexcel can follow them.
A formula reading '[1]Annual'!B4 names another workbook. The file itself
says which one: xl/externalLinks/externalLink1.xml and its relationship
carry the path Excel last saw it at, and — when Excel saved the link — a cache
of the values that were read. Neither is visible to the calculation engine, so
without this module such a cell is a grey "external reference" node and every
formula above it loses its value.
Three levels of answer, in the order they are tried:
- Named. The path and file name are always readable, so the report can say
which workbook a cell depends on instead of showing
[1].
- Cached. Most files carry the values Excel last read across the link.
They are what the user saw on screen, and they cost nothing to read.
- Resolved. Given a folder of the referenced files, the workbook is opened
and read for real — which is also where VBA lives when the code sits in an
add-in (
.xlam, .xla) rather than in the workbook itself.
Level 3 is opt-in: analyze(..., refs_dir=...). Nothing is read from disk
unless a caller names the folder.
ExternalBook
dataclass
One workbook the analyzed file reads from.
Source code in src/linexcel/external.py
| @dataclass
class ExternalBook:
"""One workbook the analyzed file reads from."""
#: What formulas call it: ``1`` for ``[1]``, or the file name.
key: str
#: The path as the file declares it, unescaped.
target: str
#: File name alone, which is what a reference folder is searched for.
name: str
#: Sheet names, in the order the link declares them.
sheets: list[str] = field(default_factory=list)
#: Values Excel cached across the link, by ``(sheet, row, col)``.
cached: dict[tuple[str, int, int], Any] = field(default_factory=dict)
#: The file itself, once found in a reference folder.
path: Path | None = None
#: Values read from that file, by ``(sheet, row, col)``.
values: dict[tuple[str, int, int], Any] = field(default_factory=dict)
@property
def resolved(self) -> bool:
"""True when the referenced workbook was actually read."""
return self.path is not None
def value(self, sheet: str, row: int, col: int) -> tuple[Any, str | None]:
"""``(value, source)`` for one cell of this workbook.
The file on disk wins over the cache: the cache is what Excel read the
last time it opened the link, which may be older than the workbook a
caller has just pointed linexcel at.
"""
key = (sheet, row, col)
if key in self.values:
return self.values[key], "external"
if key in self.cached:
return self.cached[key], "external-cache"
return None, None
|
resolved
property
True when the referenced workbook was actually read.
value
value(
sheet: str, row: int, col: int
) -> tuple[Any, str | None]
(value, source) for one cell of this workbook.
The file on disk wins over the cache: the cache is what Excel read the
last time it opened the link, which may be older than the workbook a
caller has just pointed linexcel at.
Source code in src/linexcel/external.py
| def value(self, sheet: str, row: int, col: int) -> tuple[Any, str | None]:
"""``(value, source)`` for one cell of this workbook.
The file on disk wins over the cache: the cache is what Excel read the
last time it opened the link, which may be older than the workbook a
caller has just pointed linexcel at.
"""
key = (sheet, row, col)
if key in self.values:
return self.values[key], "external"
if key in self.cached:
return self.cached[key], "external-cache"
return None, None
|
ExternalRef
dataclass
An external reference as written in a formula.
Source code in src/linexcel/external.py
| @dataclass
class ExternalRef:
"""An external reference as written in a formula."""
#: The whole matched text, so it can be substituted back out.
text: str
book: str
sheet: str
cell: str
#: The directory the formula spells out, when it does.
directory: str = ""
|
parse_external_refs
parse_external_refs(formula: str) -> list[ExternalRef]
Every external reference a formula makes, in order of appearance.
Source code in src/linexcel/external.py
| def parse_external_refs(formula: str) -> list[ExternalRef]:
"""Every external reference a formula makes, in order of appearance."""
refs = []
for match in _EXTERNAL_REF_RE.finditer(formula):
refs.append(
ExternalRef(
text=match.group(0),
book=match.group("book").strip(),
sheet=match.group("sheet").replace("''", "'"),
cell=match.group("cell").replace("$", ""),
directory=match.group("dir") or "",
)
)
return refs
|
read_external_links
read_external_links(data: bytes) -> dict[str, ExternalBook]
The workbooks a file declares a link to, keyed by [N] index.
The index is the position in <externalReferences> in xl/workbook.xml
— not the number in the part name, which only coincides most of the time.
Source code in src/linexcel/external.py
| def read_external_links(data: bytes) -> dict[str, ExternalBook]:
"""The workbooks a file declares a link to, keyed by ``[N]`` index.
The index is the position in ``<externalReferences>`` in ``xl/workbook.xml``
— not the number in the part name, which only coincides most of the time.
"""
books: dict[str, ExternalBook] = {}
try:
with zipfile.ZipFile(io.BytesIO(data)) as zf:
book = _xml(zf, "xl/workbook.xml")
if book is None:
return books
rels = _rels(zf, "xl/workbook.xml")
index = 0
for element in book.iter():
if _tag(element) != "externalReference":
continue
index += 1
rid = next(
(v for k, v in element.attrib.items() if _tag_of(k) == "id"), None
)
part = rels.get(rid or "", (None, None))[1]
if part is None:
continue
entry = _read_link_part(zf, part, str(index))
if entry is not None:
books[str(index)] = entry
except Exception:
return {}
return books
|
find_workbooks
find_workbooks(folder: Path) -> dict[str, Path]
Workbook files in folder, keyed by lowercased file name.
The first match wins, and the shallowest one is seen first, so a file next
to the workbook takes precedence over a copy buried in a subfolder.
Source code in src/linexcel/external.py
| def find_workbooks(folder: Path) -> dict[str, Path]:
"""Workbook files in ``folder``, keyed by lowercased file name.
The first match wins, and the shallowest one is seen first, so a file next
to the workbook takes precedence over a copy buried in a subfolder.
"""
found: dict[str, Path] = {}
if not folder.is_dir():
return found
for depth in range(MAX_FOLDER_DEPTH):
pattern = "/".join(["*"] * (depth + 1))
for path in sorted(folder.glob(pattern)):
if not path.is_file() or path.suffix.lower() not in WORKBOOK_SUFFIXES:
continue
found.setdefault(path.name.lower(), path)
return found
|
resolve_books
resolve_books(
books: dict[str, ExternalBook],
folder: Path,
warnings: list[str],
*,
max_dense_cells: int | None = None,
) -> None
Read every declared workbook that the folder actually holds.
Source code in src/linexcel/external.py
| def resolve_books(
books: dict[str, ExternalBook],
folder: Path,
warnings: list[str],
*,
max_dense_cells: int | None = None,
) -> None:
"""Read every declared workbook that the folder actually holds."""
available = find_workbooks(folder)
for entry in books.values():
path = available.get(entry.name.lower())
if path is None:
warnings.append(
f"External workbook '{entry.name}' is not in the reference "
f"folder; its cells keep the value cached in the file, if any"
)
continue
try:
entry.values = read_workbook_values(path, max_dense_cells=max_dense_cells)
entry.path = path
except Exception as exc:
warnings.append(
f"External workbook '{entry.name}' could not be read: {exc}"
)
|
read_workbook_values
read_workbook_values(
path: Path, *, max_dense_cells: int | None = None
) -> dict[tuple[str, int, int], Any]
Every value of a workbook, by (sheet, row, col).
Only values: a referenced workbook is read for what the formulas above it
need, and Excel itself stores nothing but values across a link.
Source code in src/linexcel/external.py
| def read_workbook_values(
path: Path, *, max_dense_cells: int | None = None
) -> dict[tuple[str, int, int], Any]:
"""Every value of a workbook, by ``(sheet, row, col)``.
Only values: a referenced workbook is read for what the formulas above it
need, and Excel itself stores nothing but values across a link.
"""
from python_calamine import CalamineWorkbook
# calamine builds a sheet as a dense rows × columns array before handing
# anything back, so a workbook declaring A1:XFD1048576 asks the allocator
# for 512 GiB and *aborts the process* — a Rust allocation failure, not an
# exception, so the caller's try/except would never see it. A file that
# claims more than any sheet can hold is refused by name instead.
dense_limit = limit_or_default("max_dense_cells", max_dense_cells, MAX_DENSE_CELLS)
declared = declared_cells(path.read_bytes())
if declared > dense_limit:
raise ValueError(
f"it declares a used range of {declared:,} cells, more than can be "
f"read; open it, delete the empty rows below and columns right of "
f"the data, and save"
)
values: dict[tuple[str, int, int], Any] = {}
workbook = CalamineWorkbook.from_path(str(path))
for name in workbook.sheet_names:
rows = workbook.get_sheet_by_name(name).to_python(skip_empty_area=False)
scanned = 0
for r_index, row in enumerate(rows):
scanned += len(row)
if scanned > MAX_EXTERNAL_CELLS:
raise ValueError(
f"sheet {name!r} exceeds the external read ceiling of "
f"{MAX_EXTERNAL_CELLS:,} cells; partial external "
f"values were not used"
)
for c_index, value in enumerate(row):
if value is None or value == "":
continue
if isinstance(value, datetime.date) and not isinstance(
value, datetime.datetime
):
value = datetime.datetime(value.year, value.month, value.day)
values[(name, r_index + 1, c_index + 1)] = value
return values
|
macro_files
macro_files(folder: Path) -> list[Path]
Files in folder that can carry a VBA project.
Source code in src/linexcel/external.py
| def macro_files(folder: Path) -> list[Path]:
"""Files in ``folder`` that can carry a VBA project."""
return [
path
for name, path in sorted(find_workbooks(folder).items())
if path.suffix.lower() in MACRO_SUFFIXES
]
|