MatrixParser

MatrixParser Service

Type: Component (Service) Domain: Spreadsheet Processing / District Volume Import Status: Complete (Issue #905) Last updated: 2026-06-17

Overview

MatrixParser is the core parsing engine for two-row merged-header matrix spreadsheets. It resolves merged-cell group headers (row 1) and sub-column headers (row 2) into flat Record<string, unknown>[] output, producing one record per (district, group) combination.

Created as a generic, reusable component — not specific to district volumes. The parser accepts a MatrixConfig to describe the layout, making it usable for any two-row merged header format.

Public Interface

Class: MatrixParser

Stateless service. No constructor parameters.

parse(worksheet, config): ParseResult<Record<string, unknown>>

Parses a worksheet into flat records.

Parameter	Type	Required	Description
worksheet	ExcelJS.Worksheet	yes	Worksheet from ExcelReader.read()
config	MatrixConfig	yes	Describes the matrix layout

Returns: ParseResult<Record<string, unknown>> — { data, errors } where each record has keys [config.districtKey, config.groupOutputKey, ...subColumnKeys].

Algorithm

Step 1 — Resolve group labels from merged cells

Iterates worksheet.model.merges looking for horizontal merges on config.groupHeaderRow. Each merge range from column A to column B creates an entry in groupLabels mapping every column in that range to the merge's top-left cell value.

Step 2 — Fallback to config labels

For columns covered by config.groups but not resolved in Step 1, use the config's group.label as the group label.

Step 3 — Read column headers

Reads row config.columnHeaderRow to get sub-column header labels.

Step 4 — Iterate data rows

For each row from config.dataStartRow to end of sheet:

1. Skips rows with empty district code
2. For each group in config.groups, produces one record:
   • [config.districtKey] = district code (column config.districtCol)
   • [config.groupOutputKey] = group label
   • [subColumn.key] = raw cell value for each sub-column

Configuration Format

MatrixConfig

Field	Type	Description
sheetName	string	Expected worksheet name
headerRows	number	Total header rows (typically 2)
groupHeaderRow	number	Row number of the merged group header (1-based)
columnHeaderRow	number	Row number of the sub-column headers (1-based)
dataStartRow	number	First data row (1-based)
groups	ColumnGroup[]	Column groups defining zones/sections
districtCol	number	Column index of the district code (1-based)
districtKey	string	Output key for the district value
groupOutputKey	string	Output key for the group label

ColumnGroup

Field	Type	Description
label	string	Display name of the group (zone/section)
colStart	number	Start column index (1-based, inclusive)
colEnd	number	End column index (1-based, inclusive)
subColumns	SubColumnDef[]	Ordered sub-column definitions

SubColumnDef

Field	Type	Description
header	string	Expected header text (whitespace-normalized)
key	string	Output key for this value column

Design Principles

Generic — Not Tied to District Volumes

The same parser can handle any two-row merged-header matrix format. District volume configs (interiorMatrixConfig, coastMatrixConfig) are just data.

Merged-Cell Resolution

Uses exceljs's native merge-range model (worksheet.model.merges) to propagate group header values across merged cell ranges.

Whitespace Normalization

Cell text is cleaned via replace(/\s+/g, ' ').trim() to match the normalization in validators and configs.

Dependencies

Dependency	Purpose
exceljs	Worksheet access and merge-range model
types.ts (local)	MatrixConfig, ParseResult, ColumnGroup, SubColumnDef

Constraints

Constraint	Detail
Two-row headers	Row 1 = merged group labels, Row 2 = sub-column headers
Enforces none	Parser does not validate — returns all values as-is with null for missing cells
ASCII column letters	columnLetterToIndex only handles A-Z (single and multi-letter)
SheetJS not used	Relies on exceljs merge model, not SheetJS/XLSX

Code Location

Artifact	Path
Service	frontend/src/services/spreadsheet/matrixParser.ts
Types	frontend/src/services/spreadsheet/types.ts
Tests	frontend/src/services/spreadsheet/matrixParser.unit.test.ts

Test Coverage

6 tests covering: single district (one per zone), multiple districts, empty district skip, null cells, headers-only (empty data), and coast format.

Consumers

Consumer	Location
DistrictVolumeProcessor	src/services/districtvolumes/processors/
(Any future matrix-format parser)	—

Usage Example

import { ExcelReader } from '@/services/spreadsheet/excelReader';
import { MatrixParser } from '@/services/spreadsheet/matrixParser';
import { interiorMatrixConfig } from '@/services/districtvolumes/config/interiorMatrixConfig';

const reader = new ExcelReader();
const worksheet = await reader.read(file, 'Interior');

const parser = new MatrixParser();
const result = parser.parse(worksheet, interiorMatrixConfig);
// result.data = [
//   { district: 'DCC', zone: 'Dry belt', avoidableSawlog: 2.04, avoidableGradeY4: 7.05, unavoidable: 0.08, total: 9.17 },
//   { district: 'DCC', zone: 'Transition zone', avoidableSawlog: 7.96, ... },
//   { district: 'DCC', zone: 'Wet belt', avoidableSawlog: 13.5, ... },
// ]

References

Reference	Link
GitHub Issue	#905
Related page	ExcelReader
Related page	SpreadsheetValidator
Related page	DistrictVolumeProcessor