Skip to content
AnglinAI Docs

Step 0 — XLSX Ingestion (Implementation Spec)

Hand-off-ready spec for Step 0 of the multi-chart plan (docs/admin/org-chart-multi-chart-design.md, issue #1549). Self-contained and independently shippable. Does not implement multi-tab UX, the org/view split, or transclusion — it only adds spreadsheet file ingestion so those later steps have a real spreadsheet path to build on.

All line references verified against main as of 2026-07-13. Confirm they haven’t drifted before editing.

Goal

Accept .xlsx (and, free via the chosen library, .xls/.ods) uploads on the existing chart-creation path, parsing them into the same normalized people rows that CSV already produces — so the entire downstream insert/hierarchy pipeline is unchanged.

Scope

In scope

  1. Add SheetJS (xlsx) as a dependency of workers/api (Node/Lambda runtime only).
  2. New util parseOrgChartSpreadsheet(buffer) in workers/api/src/utils/ that reads a workbook, parses the first non-empty sheet, and returns the exact same { people, errors } shape as parseOrgChartCSV, reusing the existing column-matching/normalization logic.
  3. Extend the MIME/extension allowlists in packages/org-chart-shared/src/constants.ts.
  4. Wire spreadsheet detection + parsing into POST /api/orgcharts (workers/api/src/routes/org-chart/orgcharts.ts).
  5. Add a file-size cap to the CSV and spreadsheet branches (currently only image/PDF is capped — see §“Bug to fix in passing”).
  6. Update client accept filters and type-gates (dashboard/page.tsx, wizard/page.tsx).
  7. Unit tests for the new parser + a route-level test for the spreadsheet branch.

Out of scope (later steps — do NOT build here)

  • Multi-tab handling / one-chart-per-tab / tab→department mapping (Step 1). Step 0 parses the first sheet only and reports ignored sheets so Step 1 can build on it.
  • Import mapping/preview UI (Step 1).
  • ID-based hierarchy (employeeId+managerId) — larger data-model change; defer to Step 1/2.
  • An .xlsx download template (keep the existing CSV template).
  • ;/, delimiter fix — already shipped in Phase 1 (apps/org-chart/src/lib/csv.ts, MULTI_VALUE_DELIMITER = ';' with legacy comma fallback). Do not re-do it.

Current ingestion path (verified)

  • Client uploads via FormData (file + name) to POST /api/orgcharts (apps/org-chart/src/lib/api.ts:263-298). CSV is parsed server-side, not in the browser.
  • Route workers/api/src/routes/org-chart/orgcharts.ts:
    • orgcharts.ts:45 — CSV detection: file.type === 'text/csv' || file.name.endsWith('.csv').
    • orgcharts.ts:47-64 — CSV branch: await file.text() → parseOrgChartCSV(csvText) → { people, errors } → chart insert + people batch insert (first/second-pass manager-name resolution, orgcharts.ts:84-232).
    • orgcharts.ts:241-272 — image/PDF branch: checks ALL_ALLOWED_MIME_TYPES (:244) and MAX_FILE_SIZE (:249).
  • Parser workers/api/src/utils/csv-parser.ts:
    • parseOrgChartCSV(csvText): { people: ParsedPerson[]; errors: string[] } (:158).
    • normalizePersonRow(row: Record<string,string>): { normalized; customFields } (:282) — reused per row.
    • STANDARD_FIELDS (:22), FIELD_MAP (:61), fuzzyMatchColumn (:126) — the column-matching layer to reuse.
  • Constants packages/org-chart-shared/src/constants.ts:
    • MAX_FILE_SIZE = 10 * 1024 * 1024 (:12).
    • ALLOWED_MIME_TYPES = { pdf, image } (:15), ALLOWED_EXTENSIONS (:21), ALL_ALLOWED_MIME_TYPES (:27).
  • Client accept filters: dashboard/page.tsx:420, wizard/page.tsx:409 (image/*,application/pdf,.csv,text/csv); type-gates at dashboard/page.tsx:165,261-262 and wizard/page.tsx:152,198,201.

Library decision

SheetJS (xlsx) — one API reads .xlsx/.xls/.ods; legacy support is free. Community build is heavy CJS but runs fine on Node/Lambda. Must not run at the Cloudflare edge — see §Runtime. (Fallback if bundle size is unacceptable: ExcelJS, .xlsx-only; then reject .xls with an actionable message. Do not hand-roll a binary .xls parser.)

Install pinned to a specific version; run npm audit after (SheetJS has had advisories — pull from the official distribution/registry per their guidance and verify no high/critical findings).

Implementation

1. packages/org-chart-shared/src/constants.ts

Add a spreadsheet group and fold it into the flat arrays:

ALLOWED_MIME_TYPES = {
  pdf:   ['application/pdf'],
  image: ['image/png', 'image/jpeg', 'image/bmp'],
  spreadsheet: [
    'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet', // .xlsx
    'application/vnd.ms-excel',                                          // .xls
    'application/vnd.oasis.opendocument.spreadsheet',                    // .ods
  ],
}
ALLOWED_EXTENSIONS.spreadsheet = ['.xlsx', '.xls', '.ods']

Keep ALL_ALLOWED_MIME_TYPES as image+pdf (it gates the AI-vision branch). Export a separate SPREADSHEET_MIME_TYPES / SPREADSHEET_EXTENSIONS for the structured branch. Rebuild the shared package (@org-chart/shared) so workers/api picks up the new exports.

Browser MIME reporting for spreadsheets is unreliable (empty or application/octet-stream). Detection MUST fall back to file extension, exactly as the CSV check already does.

2. New util workers/api/src/utils/spreadsheet-parser.ts

export function parseOrgChartSpreadsheet(buffer: ArrayBuffer | Uint8Array): {
  people: ParsedPerson[];   // same type parseOrgChartCSV returns
  errors: string[];
  sheetNames: string[];     // all sheet names in the workbook
  parsedSheet: string;      // the sheet actually used
  ignoredSheets: string[];  // sheetNames minus parsedSheet (for Step 1)
}
  • XLSX.read(buffer, { type: 'array', cellFormula: false, cellHTML: false }) — do not evaluate formulas.
  • Pick the first sheet whose used range has ≥1 non-empty row after the header; record the rest in ignoredSheets.
  • Convert that sheet to row objects with XLSX.utils.sheet_to_json(ws, { header: 1, raw: false, defval: '' }) (array-of-arrays), then map the header row through the same fuzzyMatchColumn/FIELD_MAP path parseOrgChartCSV uses, and each data row through normalizePersonRow. Refactor parseOrgChartCSV to extract a shared rowsToPeople(headerRow, dataRows) core and have both CSV and spreadsheet parsers call it — no duplicated column logic.
  • Sanitize each cell to a string; strip leading control chars; treat leading = + - @ cautiously (import stores values only — never evaluate — but keep parity with the CSV export sanitization rule).
  • Errors use the same human-readable style as parseOrgChartCSV (row-numbered).

3. workers/api/src/routes/org-chart/orgcharts.ts

  • Add detection alongside isCSV (:45): 
    const isSpreadsheet = SPREADSHEET_MIME_TYPES.includes(file.type)
      || SPREADSHEET_EXTENSIONS.some(ext => file.name.toLowerCase().endsWith(ext));
  • Size cap first, for both structured branches: before reading, if (file.size > MAX_FILE_SIZE) return validationError(c, 'File exceeds the 10MB limit'). (Fixes the current CSV gap.)
  • New branch mirroring the CSV branch: const buf = await file.arrayBuffer(); const { people, errors, ignoredSheets } = parseOrgChartSpreadsheet(buf); then feed people/errors into the existing insert path (lines 66-232) — factor that path into a helper if needed so CSV and spreadsheet share it verbatim.
  • If ignoredSheets.length > 0, log.info('Spreadsheet had additional sheets (ignored in Step 0)', { chartId, ignoredSheets }) and include them in the response warnings so the UI can surface “only sheet X was imported” (sets up Step 1).
  • Keep the same error semantics: all-rows-failed → validationError; partial → insert + warnings.

4. Client (apps/org-chart/src/app/dashboard/page.tsx, wizard/page.tsx)

  • Extend both accept attributes (:420, :409) to add .xlsx,.xls,.ods,application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.
  • Extend the type-gates (dashboard/page.tsx:165,261-262; wizard/page.tsx:152,198,201) to treat spreadsheet extensions as a valid structured upload (same UX branch as CSV — no AI extraction).
  • No new client parsing — the file is posted as-is; the server parses.

Runtime & deploy

  • POST /api/orgcharts is served by the Node API (.4) and Lambda — both Node runtimes, where SheetJS works. This is an existing route: no new route registration, but confirm it is mounted in both index.ts (Node) and index-aws.ts (Lambda) — it is, since CSV works in prod. (Dual-entry gotcha applies to new routes; this isn’t one.)
  • Do not move spreadsheet parsing into any Cloudflare Worker (edge) path — SheetJS’s CJS bundle and Node deps won’t run there. If a Worker ever needs it, that’s a separate design.
  • New npm dep → follow the workers/api Dockerfile workspace-deps rule (the .4 api-nodes image needs the dep present or it crash-loops; Lambda bundles so it’s unaffected). Verify the container builds before npm run rebuild.
  • After merge: Lambda + CF auto-deploy; Node needs manual npm run rebuild on 10.1.1.4.

Bug to fix in passing

The CSV branch (orgcharts.ts:47-64) reads file.text() with no size check — only image/PDF is capped. Add the MAX_FILE_SIZE guard to both structured branches (see §3). Small, contained, in the code we’re already touching.

Tests

  • Unit — workers/api/src/__tests__/utils/spreadsheet-parser.test.ts (new): a well-formed .xlsx fixture parses to the expected people/managers; multi-sheet workbook → first sheet used + ignoredSheets populated; .xls and .ods fixtures parse; empty workbook → clear error; malformed/non-spreadsheet bytes → error, no throw; fuzzy headers (Reports To, Job Title) map correctly; position-only rows (title, no name) handled; parity check that the same tabular data yields the same people as parseOrgChartCSV.
  • Route — extend the orgcharts create test (per the route-test-coverage rule): posting an .xlsx FormData creates a chart with people; oversized file → 400; additional-sheets warning surfaces in the response.
  • Existing csv.test.ts / csv-parser tests must still pass after the rowsToPeople refactor.
  • npm run typecheck (via npm, not bare tsc) + test:ci green; coverage not decreased.

Acceptance criteria

  1. Uploading a valid .xlsx on the create-chart screen produces a chart with the correct people and reporting lines — no AI extraction, same result as the equivalent CSV.
  2. .xls and .ods also import (via SheetJS); browser-empty MIME still works via extension fallback.
  3. A multi-sheet workbook imports the first sheet and the response/UI states that other sheets were ignored.
  4. Files over 10MB are rejected with an actionable message on the CSV and spreadsheet paths.
  5. Formulas are never evaluated; only cell values are stored.
  6. workers/api container builds with the new dep; npm audit shows no new high/critical; all tests + typecheck pass.
  7. No change to the image/PDF vision path or to existing CSV behavior.

Rollout

Single PR off a feature/org-chart-xlsx-ingestion branch → tests + container build green → merge → npm run rebuild on .4 → smoke-test an .xlsx upload in prod. Then Step 1 (mapping/preview UI + multi-tab) builds on ignoredSheets.