# Excel Spectral Tables

> **Status:** Supported (scoped) · **Vendor:** Generic / lab & handheld exports · **Extensions:** `.xlsx`, `.xlsm`

Spreadsheet workbooks that store a NIRS dataset as a spectral table — one row per
sample, numeric wavelength headers across the columns. nirs4all-formats reads the
OOXML workbook family (`.xlsx` and `.xlsm`) through the pure-Rust `calamine`
crate; legacy OLE `.xls` is not yet supported.

## Instruments & software

Vendor-neutral; common as an export from lab software and handheld NIR apps.
Committed fixtures include synthetic single-sheet and multi-sheet workbooks, a
macro-compatible `.xlsm`, and real forensic handheld exports from SCiO, NIRone,
Si-Ware NeoSpectra and VIAVI MicroNIR.

## File structure

A workbook is detected by the ZIP/OOXML magic (`PK\x03\x04`) plus the `.xlsx` /
`.xlsm` extension. The reader prefers a worksheet named `spectra` and otherwise
falls back to the first worksheet. The selected sheet has:

- one header row;
- numeric wavelength headers for the spectral columns;
- optional identifier columns (`sample`, `sample_id`, `id`);
- optional numeric target columns (e.g. `protein`);
- an optional first-cell axis descriptor such as
  `axis: wavelength (nm) / data: absorbance (a.u.)`, in which case the first
  column holds sample IDs.

Optional sibling worksheets named `metadata`/`meta`/`samples` and
`references`/`reference`/`targets` are joined to the spectral rows by
`sample_id`.

## What nirs4all-formats extracts

- **Signals** — one `SpectralRecord` per non-empty data row. The signal name and
  type are inferred from the axis descriptor's `data:` label (e.g. `absorbance`,
  `reflectance`, `raw`); absent a descriptor the signal defaults to `absorbance`.
- **Axis** — values from the numeric headers. The descriptor selects the axis:
  `wavenumber`/`cm-1` gives kind `Wavenumber` (`cm-1`), otherwise kind
  `Wavelength` (`nm`). The signal unit is taken from the descriptor's parentheses
  when present.
- **Targets** — numeric non-spectral columns become `targets`; the `references`
  sheet contributes additional numeric targets joined by `sample_id`.
- **Metadata** — identifier columns map to `metadata.sample_id`; other text
  columns are kept; `sheet`, `row_index` and the `axis_descriptor` are recorded;
  the `metadata` sheet contributes extra fields joined by `sample_id`.
- **Provenance** — source file + SHA-256, reader name and version.

## Variants & support status

| Variant | Status | Notes |
|---|---|---|
| `.xlsx` numeric-header spectral table | Supported | Single `spectra`/first sheet. |
| `.xlsm` macro-compatible workbook | Supported | Read as OOXML; macros ignored. |
| Multi-sheet workbook with `metadata` / `references` | Supported | Auxiliary sheets joined by `sample_id`. |
| Axis descriptor in first cell | Supported | Drives axis kind/unit and signal label/unit. |
| Metadata-only workbook (methods/settings, no matrix) | Detected / refused | Fails with `no numeric spectral headers found`. |
| Legacy `.xls` (OLE) | Blocked | Not handled by the OOXML path; samples still wanted. |

## Limitations & known gaps

- Legacy `.xls` OLE workbooks, caller-selected non-canonical sheet names, and
  workbooks where Excel has coerced wavelengths into dates are not handled. The
  reader is intentionally limited to numeric spectral headers so malformed lab
  transfers fail clearly instead of silently producing a shifted axis.
- Metadata-only companion workbooks (e.g. sensAIfood AuroraNIR and Foss XDS
  method/settings sheets) are explicit refusals because they carry no spectral
  matrix.
- A workbook that mixes several spectral sheets is read as the single chosen
  sheet only; arbitrary multi-table layouts are out of scope.

## Reference readers

`calamine` (the underlying Rust engine), `openpyxl`, `pandas.read_excel` and R
`readxl` read the same workbooks. nirs4all-formats adds sheet selection, axis-descriptor
parsing, auxiliary-sheet joins, signal typing and provenance.

## Samples & validation

Fixtures live under `samples/excel/`, `samples/siware_neospectra/` and
`samples/viavi_micronir/`, covered by golden summaries in
`crates/nirs4all-formats/tests/goldens/` (`excel_*`). Representative outputs:
`synthetic_nirs.xlsx` and `synthetic_nirs_macro_compatible.xlsm` each yield 50
records over a 200-point `nm` axis with an `absorbance` signal and a `protein`
target; `scio_forensic_P_avg.xlsx` yields 71 `raw` records over 331 points. Probe
tests lock the synthetic `.xlsx`/`.xlsm` workbooks plus the real SCiO, NIRone,
NeoSpectra and MicroNIR workbooks, and lock the AuroraNIR / Foss XDS metadata-only
workbooks as refusals.