Bento, the office suite that fits in a file
TypeScript mit-licenseofficeoffice-365office-suiteoffice-toolsoffice365open-sourcepowerpointself-hostedsingle-page-appslidesslideshareslideshowslideshow-galleryslideshow-makerslideshows
// SPDX-License-Identifier: MIT
// Copyright (c) 2026 The Bento authors
// .xlsx — SpreadsheetML in, SpreadsheetML out.
//
// THE REASON THIS EXISTS. Nobody adopts a spreadsheet they cannot get their
// existing files into, or get their work out of. CSV is the interchange format
// people already have and import.ts handles it; .xlsx is the format their work
// is actually IN, and a tool that can only read a flattened, untyped, formula-
// less copy of it is a tool people try once. This module is the other half of
// the door, and the export half matters more than it looks: an importer with no
// exporter is a roach motel, and people can tell.
//
// NO DEPENDENCY, ON PURPOSE. SheetJS is larger than this entire product and
// JSZip roughly doubles it, so either one is paid for by every user who opens
// any workbook, forever, to serve a format many of them never touch. What .xlsx
// needs is a ZIP (zip.ts) and the fraction of SpreadsheetML that carries values,
// types, number formats and formulas. That fraction is this file.
//
// THE THREE SILENT DISASTERS, in the order they cost, because every one of them
// produces a file that OPENS, fills the grid, computes its totals, and is wrong:
//
// 1. THE DATE EPOCH. A workbook is on the 1900 serial system or the 1904 one,
// and the difference is 1,462 days — four years and a day. Nothing on
// screen says which; it is one attribute in workbook.xml, and a reader
// that assumes 1900 shifts every date in every Mac-authored file by four
// years without a word. Worse, the 1900 system has a deliberate BUG: it
// believes 1900 was a leap year (Lotus did, and compatibility outlived the
// reason), so serials 1–59 and 61+ need different epochs and serial 60 is
// a day that never existed. All three cases are handled below and all
// three are in the rig.
// 2. PERCENT IS ALREADY A FRACTION. Excel stores 15% as 0.15 and dash stores
// percent as a fraction too, so the value passes through UNCHANGED. The
// instinct to multiply by 100 on the way in — because the cell *said* 15 —
// is how a margin column becomes 1500%.
// 3. THE CACHED VALUE IS THE TRUTH, THE FORMULA IS THE PROVENANCE. A formula
// cell carries both. Importing only the formula means every cell dash
// cannot evaluate reads `#NAME?` where a number used to be; importing only
// the value throws away what the workbook was FOR. So both are taken, and
// the formula is only made LIVE when dash can actually evaluate it — see
// `liveFormula` for the three ways that can fail, one of which (a
// cross-sheet reference) would otherwise resolve silently against the
// wrong sheet.
//
// AND THE import.ts PRINCIPLE, which is the house style: where the file is
// genuinely AMBIGUOUS, refuse and report rather than pick. `XlsxFinding` is
// deliberately the same shape as `ImportFinding` so one banner renders both.
//
// WHAT IS CARRIED BESIDES VALUES, because a file that says something dash can
// hold and is not asked is a loss dash chose: data validation (`DataRule`),
// defined names (`doc.names`), frozen panes (`sheet.frozen`), per-cell bold,
// colour, background and borders (`APPEARANCE_FIELDS`), and an Excel table's
// TOTALS ROW as the column property it already is in Microsoft's schema.
//
// WHAT IS DELIBERATELY NOT HERE, and why, so nobody thinks it was forgotten:
// charts, images, pivot tables, conditional formatting, comments, VBA. All of
// them survive as unread parts on import (we drop them) and are not written on
// export. dash's own chart tiles are derived from columns and would have to be
// REBUILT as DrawingML, which is a larger module than this one for a feature
// whose data is exported anyway. Conditional formatting is the one of those
// dash could hold today (condfmt.ts) and is left for the pass that also gives
// the four unreachable rule kinds a dialog — half of it, imported into a UI
// that cannot show or edit it, is a rule nobody can find.
import {
readZip, writeZip, ZipError, type ZipEntry,
} from './zip.ts'
import {
colToLetters, lettersToCol, mapNames, parseRef, shiftRefsForInsert,
} from './a1.ts'
import {
nameText, translateCellFormula, validateDefinedName, type NameProblem,
} from './cellformula.ts'
import { FUNCTIONS } from './formula.ts'
import { t } from './i18n.ts'
import { TYPE_LABEL } from './format.ts'
import { readCell } from './store.ts'
import type {
CellOverride, Column, ColumnData, ColumnType, DashDoc, DataRule, DefinedName,
TableSheet,
} from './model.ts'
import { columnRule, describeRule, refBox } from './datavalid.ts'
// --- findings ---------------------------------------------------------------
/**
* One thing the import or export decided on your behalf, in the same shape as
* `import.ts`'s `ImportFinding` — deliberately, so `showFindings` renders both
* without knowing which produced it. The `code` is for tests and future UI; the
* `message` is what a person reads.
*/
export interface XlsxFinding {
code:
| 'date-system' | 'date-1900-bug' | 'merged-cells' | 'mixed-types'
| 'coerce-failed' | 'no-header' | 'duplicate-header' | 'empty-header'
| 'formula-not-live' | 'hidden-sheet' | 'sheet-skipped' | 'time-of-day'
| 'leading-blanks' | 'formula-column' | 'chart-dropped' | 'renamed-sheet'
| 'data-validation' | 'defined-name' | 'header-row' | 'totals-row'
| 'cell-format'
sheet?: string
column?: string
message: string
}
export interface XlsxImportResult {
sheets: TableSheet[]
findings: XlsxFinding[]
/** The workbook's own title, if `docProps/core.xml` carried one. */
title?: string
/**
* The workbook's `<definedNames>`, as `doc.names`. Present only when the
* caller asked for them (`XlsxImportOpts.names`) — see the DEFINED NAMES
* section below for why the default is to leave them out rather than to
* return a table nobody installs.
*/
names?: Record<string, DefinedName>
}
// --- a very small XML reader --------------------------------------------------
//
// DOMParser would be free in the browser and is the obvious choice — and it is
// the wrong one twice over. It does not exist in node, so the rig could not
// test a single line of the import path, which for a format this full of silent
// failures is not a trade worth making; and it builds a full DOM for a
// worksheet that can be a hundred megabytes of XML, when all we do is stream
// through it once.
//
// SCANNING WITH REGEX IS SAFE HERE, and only here, for a reason worth writing
// down: XML forbids a raw `<` in element content and in attribute values, so
// `</c>` cannot appear inside a `<c>` element — a non-greedy match to the next
// close tag cannot overshoot. That guarantee does NOT hold for HTML, and it
// stops holding the moment an element can nest inside itself. None of the
// elements read here can (`row` never contains `row`, `c` never contains `c`).
type Attrs = Map<string, string>
const ATTR_RE = /([\w:.-]+)\s*=\s*"([^"]*)"/g
/** Attributes of one start tag. A Map, not an object: attribute names come out
* of the file, and `__proto__="…"` is legal XML. */
function attrs(s: string): Attrs {
const out: Attrs = new Map()
ATTR_RE.lastIndex = 0
let m: RegExpExecArray | null
while ((m = ATTR_RE.exec(s))) out.set(m[1], unescapeXml(m[2]))
return out
}
/** `r:id`, `id`, `x:id` — producers differ on prefixes and we do not care. */
function attr(a: Attrs, name: string): string | undefined {
const v = a.get(name)
if (v !== undefined) return v
for (const [k, val] of a) if (k === name || k.endsWith(`:${name}`)) return val
return undefined
}
const NS = '(?:[A-Za-z0-9_.-]+:)?'
/** Every `<name …>…</name>` (and `<name …/>`) at any depth, in document order. */
function* elements(xml: string, name: string): Generator<{ a: Attrs; body: string }> {
const re = new RegExp(`<${NS}${name}(\\s[^>]*?)?\\s*(/>|>([\\s\\S]*?)</${NS}${name}\\s*>)`, 'g')
let m: RegExpExecArray | null
while ((m = re.exec(xml))) yield { a: attrs(m[1] ?? ''), body: m[3] ?? '' }
}
/** The first such element, or undefined. */
function element(xml: string, name: string): { a: Attrs; body: string } | undefined {
for (const e of elements(xml, name)) return e
return undefined
}
const NUM_ENT = /&#(x?)([0-9A-Fa-f]+);/g
const NAMED_ENT: Record<string, string> = {
amp: '&', lt: '<', gt: '>', quot: '"', apos: "'",
}
/**
* XML text back to a string.
*
* `_xHHHH_` is Excel's own escape for characters XML cannot carry, and it is
* decoded ONLY for control characters (< 0x20). The escape is ambiguous with
* ordinary text — a product code literally called `_x0041_` exists — and Excel
* resolves that by escaping the underscore too (`_x005F_x0041_`), which we
* would have to unwind in the right order to get right. Limiting the decode to
* control characters keeps the case that actually occurs (`_x000D_`, a carriage
* return inside a shared string) and cannot corrupt a product code.
*/
export function unescapeXml(s: string): string {
if (!s) return s
let out = s
if (out.includes('&')) {
out = out.replace(NUM_ENT, (_, hex, digits) =>
String.fromCodePoint(parseInt(digits, hex ? 16 : 10)))
.replace(/&(amp|lt|gt|quot|apos);/g, (_, n: string) => NAMED_ENT[n])
}
if (out.includes('_x00')) {
out = out.replace(/_x00([01][0-9A-Fa-f])_/g, (_, h) => String.fromCharCode(parseInt(h, 16)))
}
return out
}
/**
* A string into XML text.
*
* Control characters are STRIPPED rather than escaped. XML 1.0 simply cannot
* carry most of them at any encoding, and Excel treats a file containing one as
* corrupt — the "we found a problem with some content" dialog, which is the
* exact outcome this whole exporter is judged on. Tab, newline and carriage
* return are legal and stay.
*/
export function escapeXml(s: string): string {
let out = ''
for (const ch of s) {
const c = ch.codePointAt(0)!
if (c < 0x20 && c !== 9 && c !== 10 && c !== 13) continue
if (c === 0xfffe || c === 0xffff) continue
out += ch === '&' ? '&' : ch === '<' ? '<' : ch === '>' ? '>'
: ch === '"' ? '"' : ch
}
return out
}
// --- dates --------------------------------------------------------------------
/** ms per day, spelled out because a bare 86400000 in date maths is unreadable. */
const DAY = 86_400_000
/**
* A serial number to an ISO date (or date-time).
*
* THE 1900 LEAP BUG, in full, because getting it approximately right is worse
* than getting it wrong loudly. Excel's 1900 system believes 1900 was a leap
* year, so its day numbering runs:
*
* 1 → 1900-01-01 (epoch 1899-12-31)
* 59 → 1900-02-28
* 60 → "1900-02-29" a day that does not exist
* 61 → 1900-03-01 (epoch 1899-12-30, and from here on)
*
* A single epoch therefore cannot be right for the whole range. Almost every
* implementation uses 1899-12-30 throughout, which is correct for every date
* after February 1900 and off by one for every date before it — invisible,
* unless your data is a century old, in which case it is invisible AND wrong.
*
* The 1904 system (Mac Excel's default until 2011, and still what a converted
* file carries) has no such bug: serial 0 is 1904-01-01.
*/
export function serialToIso(serial: number, epoch1904: boolean, wantTime = false): string | null {
if (!Number.isFinite(serial)) return null
const whole = Math.floor(serial)
const frac = serial - whole
let ms: number
if (epoch1904) {
ms = Date.UTC(1904, 0, 1) + whole * DAY
} else {
if (whole === 60) {
// The impossible day. Reported as a finding by the caller; rendered as
// Excel renders it, because pretending it is 1900-02-28 or 1900-03-01
// silently moves someone's data by a day.
return wantTime ? `1900-02-29T${clock(frac)}` : '1900-02-29'
}
ms = (whole < 60 ? Date.UTC(1899, 11, 31) : Date.UTC(1899, 11, 30)) + whole * DAY
}
const d = new Date(ms)
if (!Number.isFinite(d.getTime())) return null
const iso = `${String(d.getUTCFullYear()).padStart(4, '0')}-${
String(d.getUTCMonth() + 1).padStart(2, '0')}-${String(d.getUTCDate()).padStart(2, '0')}`
return wantTime ? `${iso}T${clock(frac)}` : iso
}
/** The fraction of a day as `HH:MM:SS`. */
function clock(frac: number): string {
// Round to the nearest second FIRST: 0.5 of a day held as a float is
// 11:59:59.999… often enough to matter, and truncating gives 11:59:59.
let secs = Math.round(frac * 86400)
if (secs >= 86400) secs = 86399
const h = Math.floor(secs / 3600)
const m = Math.floor((secs % 3600) / 60)
return `${String(h).padStart(2, '0')}:${String(m).padStart(2, '0')}:${String(secs % 60).padStart(2, '0')}`
}
const ISO_RE = /^(\d{4})-(\d{2})-(\d{2})(?:[T ](\d{2}):(\d{2})(?::(\d{2}))?)?/
/**
* An ISO date string to a 1900-system serial — the inverse of the above, and it
* has to carry the same leap bug or a round trip moves early-1900 dates by a
* day. Exports always use the 1900 system, so there is no epoch to choose.
*/
export function isoToSerial(iso: string): number | null {
const m = ISO_RE.exec(iso.trim())
if (!m) return null
const days = (Date.UTC(+m[1], +m[2] - 1, +m[3]) - Date.UTC(1899, 11, 30)) / DAY
if (!Number.isFinite(days)) return null
const time = (Number(m[4] ?? 0) * 3600 + Number(m[5] ?? 0) * 60 + Number(m[6] ?? 0)) / 86400
// Below serial 61 Excel's numbering is one AHEAD of a sane epoch, because of
// the phantom 29 February.
return (days < 61 ? days - 1 : days) + time
}
// --- number formats ------------------------------------------------------------
/**
* The built-in format ids every consumer is required to know. Only the ones
* that CHANGE A TYPE are listed: 14–22 are dates and times, 9/10 are percent,
* 5–8 and 37–44 are currency/accounting. Everything else is a number, and 49
* (`@`) is text.
*
* The CJK block (27–36, 50–58) is date formats in the Japanese, Chinese and
* Korean locales. They are omitted deliberately: they are locale-dependent, a
* file carrying them will normally also carry an explicit custom format, and
* claiming a range we have not tested is how a number column becomes a column
* of 1970s dates.
*/
const BUILTIN: Record<number, 'date' | 'time' | 'percent' | 'money' | 'text'> = {
5: 'money', 6: 'money', 7: 'money', 8: 'money',
9: 'percent', 10: 'percent',
14: 'date', 15: 'date', 16: 'date', 17: 'date', 18: 'time', 19: 'time',
20: 'time', 21: 'time', 22: 'date',
37: 'money', 38: 'money', 39: 'money', 40: 'money',
41: 'money', 42: 'money', 43: 'money', 44: 'money',
45: 'time', 46: 'time', 47: 'time',
49: 'text',
}
const CURRENCY = /[$£€¥₹₩]|\[\$|"[A-Z]{3}"/
/**
* What a custom format code MEANS, for the purpose of choosing a column type.
*
* The parsing that matters is what to IGNORE. A format code is full of
* characters that look like date tokens and are not:
*
* "GBP" a quoted literal — `m` inside "Amount" is not a month
* \- \( an escaped character — the `\d` in `#,##0\d` is a literal d
* _0 a width-skip whose argument is the NEXT character
* [Red] a colour or a condition — but `[h]` is elapsed hours, a TIME
* ; section separator (positive;negative;zero;text)
*
* Stripping those first is the whole job; what remains can be tested for date
* letters. Not stripping them was the first implementation, and `#,##0;\(#,##0\)`
* — an ordinary accounting number — came out as a DATE, because of the `\(`.
*/
export function classifyFormat(code: string): 'date' | 'time' | 'percent' | 'money' | 'text' | 'number' {
if (!code || code === 'General') return 'number'
const money = CURRENCY.test(code)
let s = ''
let elapsed = false
for (let i = 0; i < code.length; i++) {
const c = code[i]
if (c === '"') { const j = code.indexOf('"', i + 1); i = j < 0 ? code.length : j; continue }
if (c === '\\' || c === '_' || c === '*') { i++; continue }
if (c === '[') {
const j = code.indexOf(']', i)
const inner = code.slice(i + 1, j < 0 ? code.length : j)
// [h] [hh] [m] [mm] [s] are ELAPSED time, not a colour or a condition.
if (/^\[?[hms]+\]?$/i.test(inner)) elapsed = true
i = j < 0 ? code.length : j
continue
}
s += c
}
// `@` is the text placeholder, and it beats everything: a column formatted
// `@` holds text even when every value in it looks like a number, which is
// exactly what a column of ZIP codes or part numbers with leading zeros is.
if (s.includes('@')) return 'text'
if (s.includes('%')) return 'percent'
const hasDate = /[yd]/i.test(s) || /\bmmm/i.test(s)
const hasClock = elapsed || /h/i.test(s) || /s/i.test(s)
// `m` alone is genuinely ambiguous — month or minute — and is resolved by
// what it sits next to, exactly as Excel resolves it: after `h` or before
// `s` it is minutes, otherwise it is a month.
const bareM = /m/i.test(s) && !hasDate && !hasClock
if (hasDate || bareM) return hasClock ? 'date' : 'date'
if (hasClock) return 'time'
if (money) return 'money'
return 'number'
}
/** Does a date format also carry a time? Used to decide whether to keep one. */
const formatHasTime = (code: string): boolean => /h|\[h\]/i.test(code.replace(/"[^"]*"/g, ''))
// --- reading a workbook --------------------------------------------------------
interface Styles {
/** cellXfs index → format code ('' = General). */
xf: string[]
/** cellXfs index → how the cell is DRAWN, or undefined for plain. */
look: Array<CellLook | undefined>
}
/**
* The appearance half of a style, in dash's own vocabulary
* (`APPEARANCE_FIELDS`, model.ts) rather than Excel's.
*
* Finding 7 of the bounce test: every fixture bolded its header and its totals
* row and not one imported cell carried `bold`, even though dash has had
* per-cell bold, colour, background and borders since cellfmt.ts landed. The
* file said so and dash threw it away without a word, then made the person do
* it again by hand.
*
* WHAT IS NOT CARRIED, and why each is a drop rather than an approximation:
*
* THEME AND INDEXED COLOURS. `<color theme="4" tint="0.4"/>` means "the
* fourth colour of whatever theme this workbook is wearing, lightened" —
* resolving it needs theme1.xml, the tint algebra, and the indexed palette,
* and getting it approximately right paints someone's brand colour a
* slightly different colour with no way to notice. Only an explicit
* `rgb="FFRRGGBB"` is read.
* PATTERN FILLS. dash's `bg` is one colour; a gray125 crosshatch is not one.
* FONT FAMILY AND SIZE. dash has neither per cell.
* PER-EDGE border colours and weights. dash carries which edges, one colour
* and one style for the box, so a cell with a thick red top and a thin grey
* bottom keeps its edges and loses the difference between them.
*/
interface CellLook {
bold?: true
italic?: true
underline?: true
color?: string
bg?: string
border?: string
borderColor?: string
borderStyle?: 'dashed' | 'dotted'
}
/** `FFCC0000` → `#cc0000`. ARGB, and the alpha is dropped: dash's colours are
* `#rrggbb` and every Excel cell colour anybody writes is opaque. A theme or
* indexed colour returns undefined — see the block above. */
function rgbOf(el: { a: Attrs } | undefined): string | undefined {
if (!el) return undefined
const raw = attr(el.a, 'rgb')
if (!raw) return undefined
const hex = raw.length === 8 ? raw.slice(2) : raw.length === 6 ? raw : ''
return /^[0-9A-Fa-f]{6}$/.test(hex) ? `#${hex.toLowerCase()}` : undefined
}
/** `<b/>` is on, `<b val="0"/>` is off. The second spelling is what a style
* that TURNS OFF an inherited bold looks like, and reading it as on is how a
* whole sheet arrives bold. */
const flagOn = (body: string, name: string): boolean => {
const el = element(body, name)
if (!el) return false
const v = attr(el.a, 'val')
return v !== '0' && v !== 'false' && v !== 'none'
}
const EDGES: Array<[string, 'top' | 'right' | 'bottom' | 'left']> = [
['t', 'top'], ['r', 'right'], ['b', 'bottom'], ['l', 'left'],
]
function readStyles(xml: string): Styles {
const codes = new Map<number, string>()
for (const nf of elements(xml, 'numFmt')) {
const id = Number(attr(nf.a, 'numFmtId'))
const code = attr(nf.a, 'formatCode') ?? ''
if (Number.isFinite(id)) codes.set(id, code)
}
// The three appearance tables, each indexed by the id an `<xf>` carries.
// Scoped to their own block for the same reason cellXfs is below: `<font>`
// also appears inside `<dxfs>` (conditional formats), and mixing the two
// shifts every font id in the workbook.
const fonts: CellLook[] = []
const fontBlock = element(xml, 'fonts')
for (const f of elements(fontBlock ? fontBlock.body : '', 'font')) {
const look: CellLook = {}
if (flagOn(f.body, 'b')) look.bold = true
if (flagOn(f.body, 'i')) look.italic = true
if (flagOn(f.body, 'u')) look.underline = true
const color = rgbOf(element(f.body, 'color'))
if (color) look.color = color
fonts.push(look)
}
const fills: Array<string | undefined> = []
const fillBlock = element(xml, 'fills')
for (const f of elements(fillBlock ? fillBlock.body : '', 'fill')) {
const pf = element(f.body, 'patternFill')
// Only a SOLID fill is a background dash can hold; a pattern is a texture.
fills.push(pf && attr(pf.a, 'patternType') === 'solid'
? rgbOf(element(pf.body, 'fgColor')) : undefined)
}
const borders: CellLook[] = []
const borderBlock = element(xml, 'borders')
for (const b of elements(borderBlock ? borderBlock.body : '', 'border')) {
let edges = ''
let color: string | undefined
let style: 'dashed' | 'dotted' | undefined
for (const [letter, tag] of EDGES) {
const e = element(b.body, tag)
const st = e ? attr(e.a, 'style') ?? '' : ''
if (!e || !st || st === 'none') continue
edges += letter
color = color ?? rgbOf(element(e.body, 'color'))
if (!style && /dash/i.test(st)) style = 'dashed'
if (!style && /dot|hair/i.test(st)) style = 'dotted'
}
const look: CellLook = {}
if (edges) look.border = edges
if (edges && color) look.borderColor = color
if (edges && style) look.borderStyle = style
borders.push(look)
}
// ONLY the cellXfs block. `cellStyleXfs` has an identical `<xf>` shape and
// comes FIRST in the file, so a scan for every `<xf>` in the document indexes
// a cell's `s="12"` into the wrong table — the bug produces plausible formats
// for the first few columns and nonsense after, which reads as random.
const block = element(xml, 'cellXfs')
const xf: string[] = []
const look: Array<CellLook | undefined> = []
if (block) {
for (const e of elements(block.body, 'xf')) {
const id = Number(attr(e.a, 'numFmtId') ?? 0)
xf.push(codes.get(id) ?? BUILTIN_CODE[id] ?? '')
const merged: CellLook = {
...(fonts[Number(attr(e.a, 'fontId') ?? 0)] ?? {}),
...(borders[Number(attr(e.a, 'borderId') ?? 0)] ?? {}),
}
const bg = fills[Number(attr(e.a, 'fillId') ?? 0)]
if (bg) merged.bg = bg
look.push(Object.keys(merged).length ? merged : undefined)
}
}
return { xf, look }
}
/** A stand-in code for the built-ins we classify by id. Only the letters
* matter — `classifyFormat` reads these, nothing displays them. */
const BUILTIN_CODE: Record<number, string> = (() => {
const out: Record<number, string> = {}
for (const [id, kind] of Object.entries(BUILTIN)) {
out[Number(id)] = kind === 'date' ? 'yyyy-mm-dd' : kind === 'time' ? 'hh:mm:ss'
: kind === 'percent' ? '0%' : kind === 'money' ? '$#,##0.00' : '@'
}
return out
})()
function readSharedStrings(xml: string): string[] {
const out: string[] = []
for (const si of elements(xml, 'si')) {
// A rich-text string is a run of `<r><t>…</t></r>`; a plain one is a single
// `<t>`. Concatenating every `<t>` handles both, and drops the formatting,
// which dash has nowhere to put.
let s = ''
for (const t of elements(si.body, 't')) s += unescapeXml(t.body)
out.push(s)
}
return out
}
/** Resolve a relationship id against a `.rels` part. */
function relTargets(xml: string): Map<string, { target: string; type: string }> {
const out = new Map<string, { target: string; type: string }>()
for (const r of elements(xml, 'Relationship')) {
const id = attr(r.a, 'Id')
const target = attr(r.a, 'Target')
if (id && target) out.set(id, { target, type: attr(r.a, 'Type') ?? '' })
}
return out
}
/** Join an OPC part path with a relationship target (which may be relative). */
function resolvePart(base: string, target: string): string {
if (target.startsWith('/')) return target.slice(1)
const dir = base.includes('/') ? base.slice(0, base.lastIndexOf('/') + 1) : ''
const parts = (dir + target).split('/')
const out: string[] = []
for (const p of parts) {
if (p === '.' || p === '') continue
if (p === '..') out.pop()
else out.push(p)
}
return out.join('/')
}
// --- one worksheet's cells -------------------------------------------------------
interface RawCell {
/** 0-based */
row: number
col: number
/** the raw `t` attribute: s | str | b | e | inlineStr | d | (absent = number) */
t: string
/** style index into cellXfs */
s: number
/** the `<v>` text, already unescaped */
v: string
/** inline or shared string text, when the value is a string */
text?: string
/** formula source WITHOUT a leading `=` */
f?: string
}
/**
* Pull every cell out of a worksheet part.
*
* SHARED FORMULAS are expanded here, and they are why `translateCellFormula` is
* imported rather than reimplemented. Excel writes a group of identical
* formulas once — `<f t="shared" si="0" ref="B2:B99">A2*2</f>` on the master
* and `<f t="shared" si="0"/>` on the other 97 — and a reader that ignores the
* followers imports one formula and 97 bare numbers. The translation from the
* master to each follower is exactly a fill-down, which cellformula.ts already
* does correctly (including leaving `$`-pinned references alone).
*/
function readSheetCells(xml: string, shared: string[]): {
cells: RawCell[]; merges: string[]; maxCol: number; widths: Map<number, number>
validations: RawValidation[]; pane: { rows: number; cols: number }
} {
const cells: RawCell[] = []
const merges: string[] = []
const validations: RawValidation[] = []
const widths = new Map<number, number>()
let maxCol = -1
const masters = new Map<string, { f: string; row: number; col: number }>()
const data = element(xml, 'sheetData')
for (const row of elements(data ? data.body : xml, 'row')) {
const rAttr = Number(attr(row.a, 'r') ?? NaN)
let cursor = -1
for (const c of elements(row.body, 'c')) {
const ref = attr(c.a, 'r')
const pos = ref ? parseRef(ref) : null
// A `<c>` may legally omit `r`, in which case it is the next column along.
const col = pos ? pos.col : cursor + 1
const rowIdx = pos ? pos.row : (Number.isFinite(rAttr) ? rAttr - 1 : 0)
cursor = col
if (col > maxCol) maxCol = col
const t = attr(c.a, 't') ?? ''
const s = Number(attr(c.a, 's') ?? 0) || 0
const vEl = element(c.body, 'v')
let v = vEl ? unescapeXml(vEl.body) : ''
let text: string | undefined
if (t === 's') {
const i = Number(v)
text = shared[i] ?? ''
v = ''
} else if (t === 'inlineStr') {
let str = ''
const is = element(c.body, 'is')
for (const tEl of elements(is ? is.body : c.body, 't')) str += unescapeXml(tEl.body)
text = str
} else if (t === 'str' || t === 'e') {
text = v
}
let f: string | undefined
const fEl = element(c.body, 'f')
if (fEl) {
const kind = attr(fEl.a, 't') ?? ''
const si = attr(fEl.a, 'si')
const body = unescapeXml(fEl.body)
if (kind === 'shared' && si !== undefined) {
if (body) {
masters.set(si, { f: body, row: rowIdx, col })
f = body
} else {
const m = masters.get(si)
// A follower whose master we have not seen is unrecoverable — the
// file is out of order or damaged. Dropping the formula (and
// keeping the cached value) loses less than inventing one.
if (m) f = translateCellFormula(`=${m.f}`, rowIdx - m.row, col - m.col).slice(1)
}
} else if (kind !== 'dataTable' && body) {
f = body
}
}
// A cell with neither a value nor a formula is styling only.
if (v === '' && text === undefined && f === undefined) continue
cells.push({ row: rowIdx, col, t, s, v, text, f })
}
}
for (const m of elements(xml, 'mergeCell')) {
const ref = attr(m.a, 'ref')
if (ref) merges.push(ref)
}
for (const c of elements(xml, 'col')) {
const min = Number(attr(c.a, 'min') ?? NaN)
const max = Number(attr(c.a, 'max') ?? NaN)
const w = Number(attr(c.a, 'width') ?? NaN)
if (!Number.isFinite(min) || !Number.isFinite(w)) continue
// Excel's width is in "characters of the default font"; dash's is pixels.
// 7px per character + 5px of padding is the conversion Excel documents.
const px = Math.round(w * 7 + 5)
for (let i = min; i <= (Number.isFinite(max) ? max : min); i++) widths.set(i - 1, px)
}
for (const dv of elements(xml, 'dataValidation')) {
const sqref = attr(dv.a, 'sqref')
if (!sqref) continue
const f = (n: 'formula1' | 'formula2'): string | undefined => {
const el = element(dv.body, n)
return el ? unescapeXml(el.body) : undefined
}
validations.push({
sqref,
type: attr(dv.a, 'type') ?? 'none',
operator: attr(dv.a, 'operator') ?? 'between',
allowBlank: attr(dv.a, 'allowBlank') === '1',
// NOTE THE INVERSION, which is the single most-misread attribute in the
// whole schema: `showDropDown="1"` HIDES the in-cell dropdown. Excel's
// own UI checkbox is "In-cell dropdown" and the file stores its
// NEGATION, so a reader that takes the name at face value ships every
// list rule with its arrow switched the wrong way round.
hideDropdown: attr(dv.a, 'showDropDown') === '1',
errorStyle: attr(dv.a, 'errorStyle') ?? 'stop',
error: attr(dv.a, 'error'),
f1: f('formula1'),
f2: f('formula2'),
})
}
// FROZEN PANES. `state` matters: a SPLIT pane is a scroll position two panes
// share and dash has no such thing, while a FROZEN one is the header pinned
// where the reader can see it, which dash does have (`sheet.frozen`). Only
// the frozen kind is read; a split is a view the reader re-makes in a second.
let pane = { rows: 0, cols: 0 }
const pv = element(xml, 'pane')
if (pv) {
const st = attr(pv.a, 'state') ?? ''
if (st === 'frozen' || st === 'frozenSplit') {
const n = (k: string): number => {
const v = Number(attr(pv.a, k) ?? 0)
return Number.isFinite(v) && v > 0 ? Math.floor(v) : 0
}
pane = { rows: n('ySplit'), cols: n('xSplit') }
}
}
return { cells, merges, maxCol, widths, validations, pane }
}
// --- data validation ----------------------------------------------------------
//
// Excel's `<dataValidation>` is dash's `DataRule` (model.ts, datavalid.ts) with
// three differences that have to be handled rather than assumed away, because
// each one is a silent wrong answer if it is not:
//
// 1. `showDropDown` is INVERTED (see above).
// 2. `errorStyle` defaults to `stop`. dash's `on` defaults to `warn`, and
// that is not a translation bug — dash's reject is a keyboard-only
// refusal (datavalid.ts's header explains why a reject that runs on apply
// is a divergence under collaboration), so `stop` maps to `reject` and
// means slightly less than it did. It is still the closer of the two.
// 3. A validation covers a RANGE. On a dataset that range has to become a
// COLUMN, because a dataset's rule lives on its column — so a rule over
// part of a column is WIDENED to the whole one, and that is reported.
// Widening is the safe direction here: it marks more cells than Excel
// circled, and marking never changes a value.
//
// An operator dash cannot express (`notBetween`, `equal`, `notEqual`) is
// DROPPED with a finding rather than approximated. `greaterThan` and
// `lessThan` are kept as inclusive bounds and reported, because off by one at
// the boundary is a small, stated loss and losing the rule entirely is a
// larger one.
interface RawValidation {
sqref: string
type: string
operator: string
allowBlank: boolean
hideDropdown: boolean
errorStyle: string
error?: string
f1?: string
f2?: string
}
/** The inline list Excel writes: `"Open,Won,Lost"` — quoted, comma separated.
* A RANGE reference (`$H$1:$H$5`) is a different feature dash has no answer
* for; the caller reports it rather than importing an empty list. */
function inlineList(f1: string | undefined): string[] | null {
if (!f1) return null
const s = f1.trim()
if (!(s.startsWith('"') && s.endsWith('"') && s.length >= 2)) return null
return s.slice(1, -1).split(',').map((x) => x.trim()).filter(Boolean)
}
/** A bound, as dash stores one. Dates arrive as serials and go in as ISO, so
* the panel shows `2026-03-01` rather than `46082`. */
function dvBound(raw: string | undefined, isDate: boolean, epoch1904: boolean): number | string | undefined {
if (raw === undefined) return undefined
const s = raw.trim().replace(/^=/, '')
if (s === '') return undefined
const n = Number(s)
if (!Number.isFinite(n)) return s // DATE(2026,1,1) and friends: verbatim
if (!isDate) return n
return serialToIso(n, epoch1904) ?? n
}
/**
* One `<dataValidation>` as a dash rule, plus what could not be carried.
* `null` means nothing worth storing came out of it.
*/
function toDataRule(
v: RawValidation, epoch1904: boolean,
): { rule: DataRule | null; lost?: string } {
const on = v.errorStyle === 'stop' ? 'reject' : 'warn'
const base = {
...(v.allowBlank ? {} : { blank: false as const }),
on: on as 'reject' | 'warn',
...(v.error ? { message: v.error } : {}),
}
if (v.type === 'list') {
const list = inlineList(v.f1)
if (!list?.length) {
return { rule: null, lost: t('its list of values points at other cells ({ref}), and dash needs the values written out', { ref: v.f1 ?? '—' }) }
}
return { rule: { kind: 'list', list, ...(v.hideDropdown ? { noDropdown: true } : {}), ...base } }
}
if (v.type === 'custom') {
return v.f1
? { rule: { kind: 'formula', formula: v.f1, ...base } }
: { rule: null }
}
const kind = v.type === 'whole' || v.type === 'decimal' ? 'number'
: v.type === 'date' ? 'date'
: v.type === 'textLength' ? 'textLength' : null
if (!kind) return { rule: null }
const isDate = kind === 'date'
const a = dvBound(v.f1, isDate, epoch1904)
const b = dvBound(v.f2, isDate, epoch1904)
switch (v.operator) {
case 'between':
return { rule: { kind, ...(a !== undefined ? { min: a } : {}), ...(b !== undefined ? { max: b } : {}), ...base } }
case 'greaterThanOrEqual':
return { rule: { kind, ...(a !== undefined ? { min: a } : {}), ...base } }
case 'lessThanOrEqual':
return { rule: { kind, ...(a !== undefined ? { max: a } : {}), ...base } }
case 'greaterThan':
return {
rule: { kind, ...(a !== undefined ? { min: a } : {}), ...base },
lost: t('“greater than {v}” became “{v} or more”, because dash’s limits include the boundary value', { v: v.f1 ?? '' }),
}
case 'lessThan':
return {
rule: { kind, ...(a !== undefined ? { max: a } : {}), ...base },
lost: t('“less than {v}” became “{v} or less”, because dash’s limits include the boundary value', { v: v.f1 ?? '' }),
}
default:
return { rule: null, lost: t('dash has no “{op}” test', { op: v.operator }) }
}
}
/** Column index → rule, from every `<dataValidation>` on the sheet. */
function validationsByColumn(
raws: readonly RawValidation[], epoch1904: boolean, sheetName: string,
lastRow: number, findings: XlsxFinding[],
): Map<number, DataRule> {
const out = new Map<number, DataRule>()
for (const v of raws) {
if (v.type === 'none' || v.type === '') continue
const { rule, lost } = toDataRule(v, epoch1904)
// `sqref` is SPACE SEPARATED and may name several ranges.
const refs = v.sqref.trim().split(/\s+/).filter(Boolean)
const boxes = refs.map((r) => refBox(r.replace(/\$/g, ''))).filter((b): b is NonNullable<typeof b> => !!b)
if (!boxes.length) continue
if (!rule) {
findings.push({
code: 'data-validation', sheet: sheetName,
message: t('A data validation rule on {range} of “{sheet}” was not imported: {why}. The cells keep their values, but nothing checks them.', {
range: v.sqref, sheet: sheetName,
why: lost ?? t('dash has no equivalent of its “{type}” test', { type: v.type }),
}),
})
continue
}
let widened = false
for (const b of boxes) {
// Rows 2..lastRow is the whole data body. Anything narrower has to widen
// to the column, because a dataset's rule belongs to the column.
if (b.top > 1 || (lastRow > 1 && b.bottom < lastRow - 1)) widened = true
for (let ci = b.left; ci <= b.right; ci++) out.set(ci, rule)
}
if (widened || lost) {
const parts: string[] = []
if (widened) parts.push(t('it covered {range} and now applies to the whole column, because in dash a rule belongs to the column', { range: v.sqref }))
if (lost) parts.push(lost)
findings.push({
code: 'data-validation', sheet: sheetName,
message: t('A data validation rule on “{sheet}” changed on the way in: {why}.', { sheet: sheetName, why: parts.join('; ') }),
})
}
}
return out
}
// --- the Excel table (ListObject) --------------------------------------------
//
// AN xlsx TABLE IS A dash DATASET, and docs/dash-sheet-kinds.md worked the
// mapping out before this code existed: an ordinary worksheet is cell-typed, a
// `ListObject` is COLUMN-typed — named columns, a header row, a calculated
// column, and a totals row driven by a PROPERTY (`totalsRowFunction="sum"`)
// rather than by a formula somebody typed. That is dash's dataset described in
// Microsoft's own schema, so `totals: {value:'sum'}` is a translation and not
// a compromise.
//
// WHY IT IS THE CONSEQUENTIAL ONE. Without this, Excel's totals row imports as
// an ordinary data row: sorting `pipeline.xlsx` by Value descending puts the
// row labelled "Total", holding 869,050, at the TOP of the deals. It is also
// caught by filters and counted by aggregates. A total that sorts into the
// middle of the data is not a cosmetic loss — it is a wrong number in every
// view built on the sheet.
//
// WHAT IS NOT TAKEN FROM THE TABLE. Its autofilter state (dash's filters are
// the reader's, not the document's), its style name, and its calculated
// columns: an Excel calculated column is written into every cell as an
// ordinary formula, so it arrives through the ordinary formula path with the
// ordinary liveness gate, which is where the judgement about what dash can
// evaluate belongs.
interface XlsxTable {
/** the whole table, header and totals included, 0-based sheet coordinates */
box: { top: number; left: number; bottom: number; right: number }
headerRows: number
totalsRows: number
/** column index (sheet coordinates) → what its totals cell says */
totals: Map<number, string>
/** column index → the label Excel put in the totals row ("Total") */
labels: Map<number, string>
name: string
}
/** Excel's totals functions, in dash's words. `countNums` is Excel's COUNT and
* `count` is its COUNTA; dash has one count, so both land on it and the
* difference (whether text counts) is stated nowhere because dash's count
* already answers the question the column's type asks. */
const TOTALS_FN: Record<string, 'sum' | 'avg' | 'count' | 'min' | 'max'> = {
sum: 'sum', average: 'avg', count: 'count', countNums: 'count', min: 'min', max: 'max',
}
function readTable(xml: string): XlsxTable | null {
const t = element(xml, 'table')
if (!t) return null
const ref = attr(t.a, 'ref')
const box = ref ? refBox(ref.replace(/\$/g, '')) : null
if (!box) return null
const num = (k: string, dflt: number): number => {
const v = Number(attr(t.a, k) ?? NaN)
return Number.isFinite(v) ? v : dflt
}
const totals = new Map<number, string>()
const labels = new Map<number, string>()
const block = element(t.body, 'tableColumns')
let i = 0
for (const c of elements(block ? block.body : t.body, 'tableColumn')) {
const fn = attr(c.a, 'totalsRowFunction')
const label = attr(c.a, 'totalsRowLabel')
if (fn && fn !== 'none') totals.set(box.left + i, fn)
if (label) labels.set(box.left + i, label)
i++
}
return {
box,
// `headerRowCount` defaults to 1 and `totalsRowCount` to 0 — the schema's
// defaults, and taking them the other way round either eats a row of data
// or leaves the total in it.
headerRows: num('headerRowCount', 1),
totalsRows: num('totalsRowCount', 0),
totals,
labels,
name: attr(t.a, 'displayName') ?? attr(t.a, 'name') ?? 'a table',
}
}
// --- typing a column ---------------------------------------------------------
type Kind = 'blank' | 'text' | 'number' | 'money' | 'percent' | 'date' | 'time' | 'bool' | 'error'
/** What ONE cell is, given the workbook's styles. The only place a cell's type
* is decided; everything downstream aggregates these. */
function kindOf(c: RawCell, styles: Styles): Kind {
if (c.t === 'e') return 'error'
if (c.t === 'b') return 'bool'
if (c.t === 's' || c.t === 'str' || c.t === 'inlineStr') return c.text ? 'text' : 'blank'
if (c.t === 'd') return 'date' // ISO-in-the-file, an ECMA-376 2nd-edition thing
if (c.v === '') return 'blank'
const code = styles.xf[c.s] ?? ''
const f = classifyFormat(code)
return f === 'text' ? 'text' : f === 'number' ? 'number' : f
}
/** The four families that are genuinely different KINDS OF DATA, as opposed to
* different ways of displaying a number. money/percent/number differ only in
* presentation; date and text differ in what the value IS. */
const familyOf = (k: Kind): 'numeric' | 'date' | 'bool' | 'text' | 'blank' =>
k === 'blank' ? 'blank'
: k === 'date' ? 'date'
: k === 'bool' ? 'bool'
: k === 'number' || k === 'money' || k === 'percent' ? 'numeric'
: 'text'
/** import.ts's threshold, and the same reasoning: a column that is 90% one
* thing has 10% mess in it, which is reported; a column that is 60% one thing
* is not one thing. */
const CONSENSUS = 0.9
interface ColumnPlan {
type: ColumnType
/** cells that will not survive the chosen type, and become blank */
failed: number
/** no family reached consensus: imported as text, and SAID SO */
ambiguous?: string
/** the format code the majority of cells carried, kept as `Column.format` */
format?: string
/** date cells carried a time of day */
hasTime?: boolean
}
function planColumn(cells: Array<RawCell | undefined>, styles: Styles): ColumnPlan {
const present = cells.filter((c): c is RawCell => !!c && kindOf(c, styles) !== 'blank')
if (!present.length) return { type: 'text', failed: 0 }
const kinds = present.map((c) => kindOf(c, styles))
const fam = new Map<string, number>()
for (const k of kinds) fam.set(familyOf(k), (fam.get(familyOf(k)) ?? 0) + 1)
let bestFam = 'text'
let bestN = 0
for (const [f, n] of fam) if (n > bestN) { bestFam = f; bestN = n }
if (bestN / present.length < CONSENSUS) {
return {
type: 'text',
failed: 0,
ambiguous: `${[...fam].map(([f, n]) => `${n} ${f}`).join(', ')} — no single type covers the column`,
}
}
// The format code the winning family mostly carries, so `Column.format` keeps
// the author's chosen presentation instead of dash inventing one.
const codes = new Map<string, number>()
present.forEach((c, i) => {
if (familyOf(kinds[i]) !== bestFam) return
const code = styles.xf[c.s] ?? ''
if (code && code !== 'General') codes.set(code, (codes.get(code) ?? 0) + 1)
})
let format: string | undefined
let topN = 0
for (const [code, n] of codes) if (n > topN) { topN = n; format = code }
const failed = present.length - bestN
if (bestFam === 'date') {
return { type: 'date', failed, format, hasTime: !!format && formatHasTime(format) }
}
if (bestFam === 'bool') return { type: 'bool', failed }
if (bestFam === 'text') return { type: 'text', failed: 0 }
// numeric: money vs percent vs number is a DISPLAY choice over the same
// stored value, so the majority wins and nothing can be lost by getting it
// wrong beyond a currency sign.
const sub = new Map<Kind, number>()
present.forEach((_, i) => {
if (familyOf(kinds[i]) !== 'numeric') return
sub.set(kinds[i], (sub.get(kinds[i]) ?? 0) + 1)
})
let type: ColumnType = 'number'
let n = 0
for (const [k, count] of sub) if (count > n) { n = count; type = k as ColumnType }
return { type, failed, format }
}
/** Coerce one cell into the column's chosen type. `null` means it would not go. */
function coerceCell(
c: RawCell | undefined, plan: ColumnPlan, styles: Styles, epoch1904: boolean,
): { v: unknown; lost: boolean; leap1900: boolean } {
if (!c) return { v: null, lost: false, leap1900: false }
const k = kindOf(c, styles)
if (k === 'blank') return { v: null, lost: false, leap1900: false }
const num = () => (c.v === '' ? NaN : Number(c.v))
switch (plan.type) {
case 'date': {
if (k === 'date' && c.t === 'd') return { v: c.text ?? c.v, lost: false, leap1900: false }
if (k !== 'date') return { v: null, lost: true, leap1900: false }
const n = num()
const iso = serialToIso(n, epoch1904, !!plan.hasTime && n % 1 !== 0)
return { v: iso, lost: iso === null, leap1900: !epoch1904 && Math.floor(n) === 60 }
}
case 'bool': {
if (k === 'bool') return { v: c.v === '1' || c.v.toLowerCase() === 'true', lost: false, leap1900: false }
const n = num()
if (n === 0 || n === 1) return { v: n === 1, lost: false, leap1900: false }
return { v: null, lost: true, leap1900: false }
}
case 'text':
// Everything has a string form, so nothing is ever lost to a text column.
// An error cell keeps its code (`#DIV/0!`) — that IS what the cell says.
if (k === 'text' || k === 'error') return { v: c.text ?? c.v, lost: false, leap1900: false }
if (k === 'bool') return { v: c.v === '1' ? 'TRUE' : 'FALSE', lost: false, leap1900: false }
if (k === 'date') {
const iso = serialToIso(num(), epoch1904)
return { v: iso ?? c.v, lost: false, leap1900: false }
}
if (k === 'time') return { v: clock(num() % 1), lost: false, leap1900: false }
return { v: c.v, lost: false, leap1900: false }
default: {
// number / money / percent — all stored as the plain number Excel holds.
// PERCENT IS NOT MULTIPLIED: Excel stores 15% as 0.15 and so does dash.
if (familyOf(k) !== 'numeric') return { v: null, lost: true, leap1900: false }
const n = num()
return { v: Number.isFinite(n) ? n : null, lost: !Number.isFinite(n), leap1900: false }
}
}
}
// --- defined names --------------------------------------------------------
//
// `=SUM(RentCells)/B5`, where `RentCells` is a `<definedName>` in workbook.xml.
//
// WHY THIS SECTION EXISTS, AND IT IS NOT "names are nice to have". Before it,
// a bare identifier was the ONE class of formula the liveness gate let through
// by accident, and it is the class that loses data: dash imported
// `SUM(RentCells)/B5` LIVE, the grid painted `#NAME?` in red, and the 0.61
// Excel had computed — the number the cell was FOR — was gone. The gate
// screened for a `!`, a `[` and unknown function names; a bare word is none of
// the three. See `liveFormula` for the check that closes it.
//
// SO WHAT IS A NAME WORTH KEEPING. dash has `doc.names` now, so the answer is
// no longer "nothing". A definition is imported when it is one of the three
// things dash can hold:
//
// a NUMBER `0.2` → `{ v: 0.2 }`
// a TEXT `"North"` → `{ v: 'North' }`
// a RANGE `Summary!$B$2:$B$4` → `{ ref: … }`, row-shifted below
//
// and it is DROPPED, with a finding, when it is anything else: a formula
// (`OFFSET(…)`), a multi-area reference, or a spelling dash cannot reach
// (`TAX1` is a cell address, so a name spelled that way would sit in the file
// and never once be consulted — `validateDefinedName` is the refusal).
//
// AN UNQUALIFIED REF IS DROPPED, which looks harsh and is not. dash's names
// are workbook-scoped (model.ts), so `$B$2:$B$4` with no sheet on it means a
// different range on every tab, and there is no sheet to row-shift it against
// either. Excel does not write one; a hand-made file that does gets told.
//
// `_xlnm.*` (Print_Area, Print_Titles, _FilterDatabase) are VIEW state, not
// names anybody writes in a formula, and are skipped in silence.
/**
* How many cells of one sheet may carry imported appearance.
*
* A dash cell override is a sparse overlay entry keyed `<colId>:<rid>`, so it
* is the right shape for the ragged few per cent of a sheet that differ and
* the wrong shape for a workbook whose author selected every cell and pressed
* a colour. 20,000 entries is a few hundred KB of document for paint; past it
* the values still all arrive and the finding says the styling did not.
*/
const CELL_FORMAT_BUDGET = 20_000
/** What the import decided about the workbook's names. */
interface NameImport {
names: Record<string, DefinedName>
/**
* The spellings, lower-cased, that a live formula may mention: those whose
* SUBSTITUTION would itself survive the gate. `TaxRate` = 0.2 substitutes to
* `(0.2)` and dash evaluates it; `RentCells` substitutes to
* `Summary!$B$2:$B$4`, which is a cross-sheet reference dash's dataset kind
* cannot resolve — so a formula using it keeps its cached value, exactly as
* one calling `SUBTOTAL` does.
*/
live: Set<string>
}
/** Why a name was refused — English, translated where it is used. */
const NAME_REFUSED: Record<NameProblem, string> = {
empty: 'it has no name',
shape: 'its spelling can’t be used in a formula (letters, digits, “_” and “.” only, starting with a letter)',
cellshaped: 'it is spelled like a cell address, so a formula would read the cell instead',
taken: 'another name is already spelled that way',
}
/** A single cell or range, sheet-qualified. Deliberately not multi-area:
* `Sheet1!$A$1,Sheet1!$C$1` is two ranges and dash's `ref` is one. */
const QUALIFIED_REF =
/^(?:'(?:[^']|'')+'|[A-Za-z_\\][A-Za-z0-9_.\\ ]*)!\$?[A-Za-z]{1,3}\$?[1-9]\d*(?::\$?[A-Za-z]{1,3}\$?[1-9]\d*)?$/
const BARE_REF = /^\$?[A-Za-z]{1,3}\$?[1-9]\d*(?::\$?[A-Za-z]{1,3}\$?[1-9]\d*)?$/
const NUM_LITERAL = /^-?(?:\d+\.?\d*|\.\d+)(?:[eE][-+]?\d+)?$/
const STR_LITERAL = /^"(?:[^"]|"")*"$/
/** The workbook's `<definedNames>`, as far as dash can carry them. */
function readDefinedNames(wbXml: string, findings: XlsxFinding[]): NameImport {
const names: Record<string, DefinedName> = {}
const live = new Set<string>()
const block = element(wbXml, 'definedNames')
if (!block) return { names, live }
for (const d of elements(block.body, 'definedName')) {
const raw = (attr(d.a, 'name') ?? '').trim()
if (!raw || raw.toLowerCase().startsWith('_xlnm')) continue
const body = unescapeXml(d.body).trim().replace(/^=/, '').trim()
const drop = (why: string): void => {
findings.push({
code: 'defined-name',
message: t('The name “{name}” was not imported: {why}. Formulas that used it show the value Excel last calculated, with the formula text kept beside it.', { name: raw, why }),
})
}
const problem = validateDefinedName(raw, names)
if (problem) { drop(t(NAME_REFUSED[problem])); continue }
if (!body) { drop(t('it defines nothing')); continue }
let def: DefinedName | undefined
if (NUM_LITERAL.test(body)) def = { v: Number(body) }
else if (STR_LITERAL.test(body)) def = { v: body.slice(1, -1).replace(/""/g, '"') }
else if (QUALIFIED_REF.test(body)) def = { ref: body }
else if (BARE_REF.test(body)) {
drop(t('it points at {ref} without naming a sheet, and a dash name has to mean one thing across the whole workbook', { ref: body }))
continue
} else {
drop(t('it is defined as “{def}”, which is not a number, a piece of text or a single range', { def: body }))
continue
}
names[raw] = def
const text = nameText(def)
if (text !== undefined && liveFormula(text).ok) live.add(raw.toLowerCase())
}
return { names, live }
}
/**
* Move every name's `ref` by the rows its sheet lost on the way in.
*
* A dataset's row 1 is the first DATA row: the header, and any blank
* letterhead rows above it, are not rows dash has. So `Summary!$B$2:$B$4`
* written against Excel's numbering points one row too low here, and pointing
* a name at the wrong rows is the same silent wrong number the formula shift
* exists to prevent — this is that shift, for the other kind of reference.
*/
function shiftNamesForImport(
names: Record<string, DefinedName>, drops: Map<string, number>,
): void {
for (const [sheet, drop] of drops) {
if (drop <= 0) continue
for (const def of Object.values(names)) {
if (typeof def.ref !== 'string') continue
def.ref = shiftRefsForInsert(def.ref, 'row', 0, -drop, { on: sheet })
}
}
}
/**
* Put an import's names into a document — the caller's half of
* `XlsxImportOpts.names`, in one line, so no caller has to work out the
* collision rule for itself.
*
* A NAME ALREADY IN THE DOCUMENT WINS. Importing a second workbook must not
* silently repoint `TaxRate` at the new file's rate: every formula in the
* document that used it would change its answer, all at once, with nothing on
* screen having been edited. The skipped spellings come back so the caller can
* say so.
*
* Returns the names that were NOT installed because the document already had
* them; an empty array is the ordinary case.
*/
export function installNames(
doc: DashDoc, names: Record<string, DefinedName> | undefined,
): string[] {
if (!names || !Object.keys(names).length) return []
const have = doc.names ?? {}
const taken = new Set(Object.keys(have).map((k) => k.toLowerCase()))
const skipped: string[] = []
const next: Record<string, DefinedName> = { ...have }
for (const [k, d] of Object.entries(names)) {
if (taken.has(k.toLowerCase())) { skipped.push(k); continue }
next[k] = d
}
doc.names = next
return skipped
}
// --- formulas ------------------------------------------------------------------
const FN_SET = new Set(FUNCTIONS.map((f) => f.toUpperCase()))
const CALL_RE = /([A-Za-z][A-Za-z0-9_.]*)\s*\(/g
/** Words that are values in a formula, not names: nothing defines them and
* nothing needs to. */
const LITERAL_WORDS = new Set(['TRUE', 'FALSE'])
/**
* Can dash make this formula LIVE, or must it stay a number with the source
* recorded beside it?
*
* Four ways it cannot, and the first is the dangerous one:
*
* 1. A SHEET QUALIFIER (`Sheet2!A1`, `'Q1 data'!B7`). a1.ts deliberately
* leaves the name before `!` alone — but the `A1` AFTER it is then read as
* a reference on THIS sheet, so `Sheet2!A1` would silently resolve against
* the wrong sheet's column A. That is a wrong number wearing a right
* number's clothes, which is the one outcome this codebase refuses.
* 2. AN EXTERNAL OR STRUCTURED REFERENCE (`[1]Sheet!A1`, `Table1[Amount]`).
* The data it names is not in the file.
* 3. A FUNCTION dash does not implement. VLOOKUP, INDEX/MATCH, XLOOKUP and
* the rest would evaluate to `#NAME?` — visible, not silent, but a grid
* full of `#NAME?` where numbers used to be is not an import, it is a
* demolition.
* 4. A BARE IDENTIFIER THAT RESOLVES TO NOTHING. `=SUM(RentCells)/B5` has no
* `!`, no `[` and no unknown function in it, so for a long time it sailed
* through all three checks above and landed LIVE — and `RentCells` is a
* `<definedName>`, which dash did not import, so the cell that had held
* Excel's 0.61 painted `#NAME?` in red. The message this module prints
* elsewhere on the same screen promises the opposite in as many words:
* *a live formula dash cannot evaluate would replace real numbers with
* #NAME?*. That was the hole, and it was the only one that DESTROYED a
* value rather than merely declining to compute one.
*
* `known` closes it without the blunt answer of refusing every word.
* Names now exist in dash (`doc.names`), so a word the workbook defines
* AND the import carried — and whose substitution would itself pass this
* gate — is allowed through; every other word fails, keeps the cached
* value and takes a `formula-not-live` finding, exactly as `SUBTOTAL`
* does. `mapNames` is the lexer rather than a regex of our own because it
* already knows the four places a word is not a name: inside a string, in
* front of a `(`, after a `!`, and where the word is really a cell
* address (`B5`, `TAX1`).
*
* In all four cases the cached VALUE is kept (so the sheet still reads
* correctly) and the formula text is preserved on the override under `xlsxF`,
* which is an additive field: nothing in dash reads it yet, the format promises
* it survives (PLATFORM §3), and the export half below writes it back out. That
* is what makes xlsx → dash → xlsx not lose someone's model.
*/
export function liveFormula(
src: string, known: ReadonlySet<string> = new Set(),
): { ok: true } | { ok: false; why: string; fn?: string; name?: string } {
// A quoted literal can contain anything; strip it before looking for `!`.
const bare = src.replace(/"[^"]*"/g, '""')
if (/'[^']*'\s*!/.test(bare) || /[A-Za-z0-9_.$]\s*!/.test(bare.replace(/#REF!/g, ''))) {
return { ok: false, why: 'it points at another sheet' }
}
if (/\[/.test(bare)) return { ok: false, why: 'it uses an external or table reference' }
CALL_RE.lastIndex = 0
let m: RegExpExecArray | null
while ((m = CALL_RE.exec(bare))) {
const fn = m[1].toUpperCase()
if (!FN_SET.has(fn)) return { ok: false, why: `dash has no ${fn}() yet`, fn }
}
// The fourth check. `mapNames` offers exactly the words that are neither a
// reference, a call, nor part of somebody else's syntax; the walk is only
// reading, so every word goes back unchanged.
let unknown: string | undefined
mapNames(src, (w) => {
if (unknown === undefined && !LITERAL_WORDS.has(w.toUpperCase()) && !known.has(w.toLowerCase())) {
unknown = w
}
return w
})
if (unknown !== undefined) {
return {
ok: false,
why: `it uses the name ${unknown}, which this workbook defines somewhere dash could not follow`,
name: unknown,
}
}
return { ok: true }
}
// --- import --------------------------------------------------------------------
export interface XlsxImportOpts {
/** Provenance, written into `steps[0]`. */
source?: string
at?: string
/** Sheet id prefix; ids are `${prefix}-1`, `${prefix}-2`, … */
idPrefix?: string
/** Override the header-row decision instead of letting it be inferred. */
header?: boolean
/**
* WHICH row is the header, 0-based within the sheet's used range — the
* answer `header` cannot give, because "has one or hasn't" is not the
* question a report with a spanning title over its header row asks. Beats
* both `header` and the inference below. Data starts on the row after it.
*/
headerRow?: number
/**
* THE CALLER PROMISES TO INSTALL `result.names` INTO `doc.names`.
*
* Names are opt-in rather than always-on because the liveness gate trusts
* them: with this set, `=B4*TaxRate` is imported LIVE, and if the name table
* never reaches the document that live formula paints `#NAME?` over the
* number Excel computed — the precise damage the gate exists to prevent. So
* the default is off and safe (every name-using formula keeps its cached
* value and says so), and a caller that wires the table up says so here.
*/
names?: boolean
}
const blank = (s: string): boolean => s.trim() === ''
const slug = (s: string, i: number): string =>
(s.trim().toLowerCase().replace(/[^a-z0-9]+/g, '-').replace(/^-|-$/g, '') || `col-${i + 1}`).slice(0, 40)
/**
* Read a .xlsx into dash sheets.
*
* Throws only for a container we cannot open at all (`ZipError`, or a package
* with no workbook part). Everything else is a FINDING: a workbook that is
* partly readable should open, with the parts we could not read named, rather
* than refusing wholesale — the person has the file and needs what is in it.
*/
export async function importXlsx(
bytes: Uint8Array, opts: XlsxImportOpts = {},
): Promise<XlsxImportResult> {
const parts = await readZip(bytes)
const dec = new TextDecoder()
const text = (name: string): string | undefined => {
const b = parts.get(name)
return b ? dec.decode(b) : undefined
}
// The workbook part is found through the package relationships, never
// assumed to be `xl/workbook.xml`. It usually is; Numbers and a few
// generators put it elsewhere, and the cost of doing this properly is four
// lines.
const rootRels = text('_rels/.rels')
let wbPath = 'xl/workbook.xml'
if (rootRels) {
for (const r of relTargets(rootRels).values()) {
if (r.type.endsWith('/officeDocument')) { wbPath = resolvePart('', r.target); break }
}
}
const wbXml = text(wbPath)
if (!wbXml) throw new ZipError('this .xlsx has no workbook part — it is not a spreadsheet package')
const wbRels = text(resolvePart(wbPath, `_rels/${wbPath.split('/').pop()}.rels`))
const rels = wbRels ? relTargets(wbRels) : new Map()
let sharedPath = ''
let stylesPath = ''
for (const r of rels.values()) {
if (r.type.endsWith('/sharedStrings')) sharedPath = resolvePart(wbPath, r.target)
else if (r.type.endsWith('/styles')) stylesPath = resolvePart(wbPath, r.target)
}
const shared = readSharedStrings(text(sharedPath) ?? '')
const styles = readStyles(text(stylesPath) ?? '')
// THE FOUR-YEAR BUG. `date1904` is one attribute and there is no other clue
// in the file; assuming 1900 shifts every date in a Mac-authored workbook by
// 1,462 days, silently, and the numbers still look like dates.
const pr = element(wbXml, 'workbookPr')
const d1904 = pr ? (attr(pr.a, 'date1904') ?? '') : ''
const epoch1904 = d1904 === '1' || d1904.toLowerCase() === 'true'
const findings: XlsxFinding[] = []
// Names are read BEFORE the sheets, because the liveness gate needs to know
// which words a formula may mention; their refs are row-shifted AFTER, once
// every sheet has said how many rows it dropped on the way in.
const nameFindings: XlsxFinding[] = []
const nameImport = readDefinedNames(wbXml, nameFindings)
const carryNames = opts.names === true
if (carryNames) {
findings.push(...nameFindings)
} else {
const listed = Object.keys(nameImport.names)
const total = listed.length + nameFindings.length
if (total) {
const list = `${listed.slice(0, 4).join(', ')}${listed.length > 4 ? ', …' : ''}`
findings.push({
code: 'defined-name',
message: total === 1
? (listed.length
? t('This workbook defines a name ({list}) that this import doesn’t bring in. Formulas that used it show the value Excel last calculated, with the formula text kept beside it.', { list })
: t('This workbook defines a name that this import doesn’t bring in. Formulas that used it show the value Excel last calculated, with the formula text kept beside it.'))
: (listed.length
? t('This workbook defines {n} names ({list}) that this import doesn’t bring in. Formulas that used them show the value Excel last calculated, with the formula text kept beside it.', { n: total, list })
: t('This workbook defines {n} names that this import doesn’t bring in. Formulas that used them show the value Excel last calculated, with the formula text kept beside it.', { n: total })),
})
}
}
const liveNames: ReadonlySet<string> = carryNames ? nameImport.live : new Set<string>()
/** sheet name → rows that exist in Excel and do not exist in dash. */
const drops = new Map<string, number>()
if (epoch1904) {
findings.push({
code: 'date-system',
message: t('This workbook uses the 1904 date system (from Excel for Mac). Its dates were converted, so they read correctly here.'),
})
}
const sheets: TableSheet[] = []
const prefix = opts.idPrefix ?? `xl-${Math.floor(Date.now() % 1e8).toString(36)}`
const usedIds = new Set<string>()
let n = 0
for (const s of elements(wbXml, 'sheet')) {
const name = attr(s.a, 'name') ?? `Sheet ${n + 1}`
const rid = attr(s.a, 'id') ?? ''
const state = attr(s.a, 'state') ?? ''
const rel = rels.get(rid)
if (!rel) {
findings.push({ code: 'sheet-skipped', sheet: name, message: t('“{sheet}” couldn’t be found inside the file, so it was not imported.', { sheet: name }) })
continue
}
const partPath = resolvePart(wbPath, rel.target)
const xml = text(partPath)
if (xml === undefined) {
findings.push({ code: 'sheet-skipped', sheet: name, message: t('“{sheet}” is listed in the workbook, but its data is missing from the file.', { sheet: name }) })
continue
}
// A chartsheet has no `sheetData` at all.
if (rel.type.endsWith('/chartsheet') || !/<(?:[A-Za-z0-9_.-]+:)?sheetData[\s>]/.test(xml)) {
findings.push({ code: 'chart-dropped', sheet: name, message: t('“{sheet}” is a chart sheet with no data of its own, so it was not imported.', { sheet: name }) })
continue
}
if (state === 'hidden' || state === 'veryHidden') {
findings.push({
code: 'hidden-sheet', sheet: name,
message: t('“{sheet}” was hidden in Excel. It was imported anyway, because hiding a sheet doesn’t delete it.', { sheet: name }),
})
}
n++
const id = `${prefix}-${n}`
usedIds.add(id)
// An Excel Table (`ListObject`) is reached through the WORKSHEET's own
// relationships, never by guessing `xl/tables/table1.xml`: a workbook with
// several tables numbers the parts in creation order, not sheet order.
const sheetRels = text(resolvePart(partPath, `_rels/${partPath.split('/').pop()}.rels`))
const tableXml: string[] = []
if (sheetRels) {
for (const rr of relTargets(sheetRels).values()) {
if (!rr.type.endsWith('/table')) continue
const tx = text(resolvePart(partPath, rr.target))
if (tx) tableXml.push(tx)
}
}
sheets.push(readOneSheet(xml, name, id, shared, styles, epoch1904, findings, opts,
{ liveNames, drops, tables: tableXml }))
}
if (carryNames) shiftNamesForImport(nameImport.names, drops)
if (!sheets.length && !findings.some((f) => f.code === 'sheet-skipped')) {
throw new ZipError('this workbook contains no worksheets')
}
const core = text('docProps/core.xml')
const title = core ? element(core, 'title')?.body : undefined
return {
sheets,
findings: condense(findings),
title: title ? unescapeXml(title) : undefined,
...(carryNames && Object.keys(nameImport.names).length ? { names: nameImport.names } : {}),
}
}
/** How many of one KIND of finding, on one sheet, are worth listing before the
* rest become a count. */
const FINDING_RUN = 3
/**
* Collapse repetition, without dropping the fact that it happened.
*
* A real financial model — measured on a 4-sheet cost model with 34 columns of
* mixed-type data — produces over EIGHTY findings, one per column, and the
* banner that was supposed to say "here is what opening this file decided on
* your behalf" becomes a wall nobody reads. That is a worse outcome than a
* shorter list: an unread warning and an absent warning are the same warning.
*
* So the first few of each (code, sheet) pair are listed in full and the rest
* become one line with the count. Nothing is hidden — the number is there, and
* the columns are still flagged on the columns themselves via `failed`.
*/
function condense(findings: XlsxFinding[]): XlsxFinding[] {
const seen = new Map<string, number>()
const out: XlsxFinding[] = []
const extra = new Map<string, { n: number; f: XlsxFinding }>()
for (const f of findings) {
const key = `${f.code}${f.sheet ?? ''}`
const n = (seen.get(key) ?? 0) + 1
seen.set(key, n)
if (n <= FINDING_RUN) out.push(f)
else extra.set(key, { n: n - FINDING_RUN, f })
}
for (const [, { n, f }] of extra) {
out.push({
code: f.code, sheet: f.sheet,
message: f.sheet
? (n === 1
? t('…and 1 more column in “{sheet}” with the same note.', { sheet: f.sheet })
: t('…and {n} more columns in “{sheet}” with the same note.', { n, sheet: f.sheet }))
: (n === 1
? t('…and 1 more column with the same note.')
: t('…and {n} more columns with the same note.', { n })),
})
}
return out
}
/** What one sheet needs from the workbook around it, and what it reports back
* to it. Bundled rather than added to an already long parameter list. */
interface SheetCtx {
/** names a formula on this sheet may mention and still go live */
liveNames: ReadonlySet<string>
/** filled in: sheet name → rows Excel had that dash does not */
drops: Map<string, number>
/** the `xl/tables/*.xml` parts this worksheet relates to */
tables: string[]
}
function readOneSheet(
xml: string, name: string, id: string, shared: string[], styles: Styles,
epoch1904: boolean, findings: XlsxFinding[], opts: XlsxImportOpts,
ctx: SheetCtx,
): TableSheet {
const { cells, merges, maxCol, widths, validations, pane } = readSheetCells(xml, shared)
const table = ctx.tables.length ? readTable(ctx.tables[0]) : null
if (ctx.tables.length > 1) {
findings.push({
code: 'totals-row', sheet: name,
message: t('“{sheet}” holds {n} Excel tables. A dash sheet holds one, so the first became this sheet and the others came in as ordinary rows beside it, totals rows included.', { sheet: name, n: ctx.tables.length }),
})
}
if (merges.length) {
findings.push({
code: 'merged-cells', sheet: name,
message: merges.length === 1
? t('“{sheet}” has a merged range ({list}). Its value stays in the top-left cell only, so a heading isn’t repeated across every cell it spanned.', { sheet: name, list: merges[0] })
: t('“{sheet}” has {n} merged ranges ({list}). Each keeps its value in its top-left cell only, so a heading isn’t repeated across every cell it spanned.', {
sheet: name, n: merges.length, list: `${merges.slice(0, 3).join(', ')}${merges.length > 3 ? ', …' : ''}`,
}),
})
}
// The used range. Rows are taken from the first row that has anything in it:
// a report with four blank rows of letterhead above the table is common, and
// importing them as empty rows makes every column read as ambiguous.
const rowsUsed = [...new Set(cells.map((c) => c.row))].sort((a, b) => a - b)
const firstRow = rowsUsed.length ? rowsUsed[0] : 0
const lastRow = rowsUsed.length ? rowsUsed[rowsUsed.length - 1] : -1
const width = maxCol + 1
// COLUMNS KEEP THEIR POSITION, always from column A. A table starting at C2
// could have its two empty leading columns trimmed — and then every A1
// formula in the sheet would point two columns off. Position is what a
// formula means; an empty column is cosmetic. So the empties stay, and are
// reported so nobody thinks dash invented them.
const grid: Array<Array<RawCell | undefined>> = []
for (const c of cells) {
const r = c.row - firstRow
;(grid[r] ??= [])[c.col] = c
}
// THE HEADER IS NOT ALWAYS ROW 1 — see `titleRowAbove`. An explicit
// instruction always wins; otherwise a spanning title over a full header row
// moves the header down one, and says so.
const told = opts.header !== undefined || opts.headerRow !== undefined
const title = told ? null : titleRowAbove(grid, merges, firstRow, styles)
// A table SAYS where its header is (`headerRowCount`), which beats inferring
// it — but only for the rows the table actually covers.
const tableHead = table && !told && !title && table.box.top === firstRow
? table.headerRows : null
const head = opts.headerRow !== undefined
? { start: Math.max(0, Math.floor(opts.headerRow)) + 1, sure: true }
: title
? { start: 2, sure: true }
: tableHead !== null
? { start: tableHead, sure: true }
: decideHeader(grid[0] ?? [], grid, styles, opts.header)
const bodyStart = head.start
const headerRow = bodyStart > 0 ? (grid[bodyStart - 1] ?? []) : []
if (title) {
const at = firstRow + 2
findings.push({
code: 'header-row', sheet: name,
message: t('Row {row} of “{sheet}” is a title spanning {range}, so the column names were taken from row {header} instead. The title (“{title}”) is not in the data. If that’s wrong, import again and say the header is row {row}.', {
row: firstRow + 1, sheet: name, range: title.merge, header: at, title: title.text,
}),
})
}
if (bodyStart === 0 && opts.header === undefined) {
findings.push({
code: 'no-header', sheet: name,
message: t('“{sheet}” doesn’t seem to start with a header row, so its columns are named by position. Rename them, or import again and say there is a header.', { sheet: name }),
})
} else if (!head.sure && !told) {
// The undecidable case, and it costs a ROW OF DATA if we are wrong. Every
// column of an all-text sheet looks exactly like its own heading, so there
// is no evidence in the file and the honest thing is to say which way it
// was resolved rather than to resolve it quietly.
findings.push({
code: 'no-header', sheet: name,
message: t('Every column in “{sheet}” is text, so its first row could be a header or data. It was taken as the header — if it was data, import again without a header.', { sheet: name }),
})
}
let leadingBlank = 0
while (leadingBlank < width && !cells.some((c) => c.col === leadingBlank)) leadingBlank++
if (leadingBlank > 0 && leadingBlank < width) {
findings.push({
code: 'leading-blanks', sheet: name,
message: leadingBlank === 1
? t('“{sheet}” starts at column {col}. The empty column before it was kept, so its formulas still point at the right cells.', { sheet: name, col: colToLetters(leadingBlank) })
: t('“{sheet}” starts at column {col}. The {n} empty columns before it were kept, so its formulas still point at the right cells.', { sheet: name, col: colToLetters(leadingBlank), n: leadingBlank }),
})
}
// THE TOTALS ROW LEAVES THE DATA. Only when the table's last row IS the
// sheet's last row: anything below it is somebody else's rows, and trimming
// then would delete them.
const totalsRows = table && table.totalsRows > 0 && table.box.bottom === lastRow
? Math.min(table.totalsRows, Math.max(0, lastRow - firstRow + 1 - bodyStart))
: 0
const dataRows = grid.slice(bodyStart)
const rowCount = Math.max(0, lastRow - firstRow + 1 - bodyStart - totalsRows)
// What Excel's row numbering has and dash's does not: the blank letterhead
// above the table, plus the header. Every reference written against this
// sheet — in a formula (below) or in a defined name (importXlsx) — moves by
// exactly this much.
ctx.drops.set(name, firstRow + bodyStart)
// Data validation, mapped to COLUMNS before the loop that builds them —
// `lastRow - firstRow` is the body's extent, which is what decides whether a
// rule covered the whole column or a slice of it.
const dvByCol = validationsByColumn(validations, epoch1904, name, lastRow - firstRow + 1, findings)
const columns: Column[] = []
const data: Record<string, ColumnData> = {}
const overrides: Record<string, CellOverride> = {}
const ids = new Set<string>()
let leapSeen = false
let timeSeen = false
let notLive = 0
let liveCount = 0
const missingFns = new Set<string>()
const missingNames = new Set<string>()
let formatted = 0
let formatSkipped = 0
for (let ci = 0; ci < width; ci++) {
const rawName = bodyStart > 0 ? cellText(headerRow[ci], shared, styles, epoch1904) : ''
let colId = slug(rawName, ci)
if (ids.has(colId)) {
let k = 2
while (ids.has(`${colId}-${k}`)) k++
findings.push({
code: 'duplicate-header', sheet: name, column: rawName,
message: t('“{sheet}” has two columns called “{column}”; the second is kept as a separate column.', { sheet: name, column: rawName }),
})
colId = `${colId}-${k}`
}
ids.add(colId)
if (bodyStart > 0 && blank(rawName)) {
findings.push({
code: 'empty-header', sheet: name, column: colId,
message: t('Column {col} of “{sheet}” has no name in the header row.', { col: colToLetters(ci), sheet: name }),
})
}
const column = Array.from({ length: rowCount }, (_, r) => dataRows[r]?.[ci])
const plan = planColumn(column, styles)
if (plan.ambiguous) {
findings.push({
code: 'mixed-types', sheet: name, column: rawName || colId,
message: t('“{column}” holds more than one kind of value, so it came in as text and nothing was lost. Set its type once you’ve decided what it is.', { column: rawName || colId }),
})
}
const values: unknown[] = []
let lostHere = 0
for (let r = 0; r < rowCount; r++) {
const cell = column[r]
const key = `${colId}:${r + 1}`
const out = coerceCell(cell, plan, styles, epoch1904)
if (out.lost) lostHere++
if (out.leap1900) leapSeen = true
// A TIME OF DAY, not "any string with a T in it" — which is what this
// was, and `Financials` set the flag on a sheet with no dates at all.
if (plan.type === 'date' && typeof out.v === 'string' && out.v.includes('T')) timeSeen = true
values.push(out.v)
// APPEARANCE. Written through the same overlay as a hand correction and
// deliberately WITHOUT a value: every reader of the overlay asks
// `'v' in over` (grid.ts, preview.ts, steps.ts, the export below), so a
// bolded cell cannot move a total, a chart, a pivot or an export by one
// digit. cellfmt.ts's one line, honoured from the import side.
const look = cell ? styles.look[cell.s] : undefined
if (look) {
if (formatted < CELL_FORMAT_BUDGET) {
overrides[key] = { ...(overrides[key] ?? {}), ...look }
formatted++
} else formatSkipped++
}
if (cell?.f !== undefined) {
// LIVENESS IS DECIDED ON THE ORIGINAL TEXT, BEFORE ANY SHIFT, and the
// order is not cosmetic. Shifting first ran `Sheet2!A1*3` through
// a1.ts, which leaves `Sheet2` alone and then dutifully moves the `A1`
// after it — off the top of the sheet, into `Sheet2!#REF!*3`. So the
// very formula we were refusing to trust got corrupted on its way into
// the field that exists to preserve it verbatim.
const live = liveFormula(cell.f, ctx.liveNames)
if (live.ok) {
// Row references shift by exactly the rows we did not import: the
// blank rows above the table plus the header.
// `shiftRefsForInsert(..., 0, -k)` is the same call the grid makes
// when someone deletes k rows at the top, which is precisely what
// happened to this formula's world.
const drop = firstRow + bodyStart
const moved = drop > 0 ? shiftRefsForInsert(cell.f, 'row', 0, -drop) : cell.f
overrides[key] = { ...(overrides[key] ?? {}), f: `=${moved}` }
liveCount++
} else {
// The value stays (it is in `values` already); the source is kept
// beside it, EXACTLY as the workbook wrote it, so nothing the author
// wrote is lost and a re-export puts it back where it came from.
overrides[key] = { ...(overrides[key] ?? {}), xlsxF: `=${cell.f}` }
notLive++
if ('fn' in live && live.fn) missingFns.add(live.fn)
if ('name' in live && live.name) missingNames.add(live.name)
}
}
}
if (lostHere) {
findings.push({
code: 'coerce-failed', sheet: name, column: rawName || colId,
message: lostHere === 1
? t('1 value in “{column}” couldn’t be read as {type} and was left blank.', { column: rawName || colId, type: t(TYPE_LABEL[plan.type]) })
: t('{n} values in “{column}” couldn’t be read as {type} and were left blank.', { n: lostHere, column: rawName || colId, type: t(TYPE_LABEL[plan.type]) }),
})
}
columns.push({
id: colId,
name: bodyStart > 0 && !blank(rawName) ? rawName.trim() : `Column ${ci + 1}`,
type: plan.type,
...(plan.format ? { format: plan.format } : {}),
...(plan.failed || lostHere ? { failed: lostHere } : {}),
...(widths.has(ci) ? { w: widths.get(ci) } : {}),
...(dvByCol.has(ci) ? { validate: dvByCol.get(ci) } : {}),
})
data[colId] = { enc: 'raw', v: values as Array<number | string | boolean | null> }
}
if (leapSeen) {
findings.push({
code: 'date-1900-bug', sheet: name,
message: t('“{sheet}” contains 29 February 1900, a date Excel shows but that never existed. It was kept as written rather than moved a day.', { sheet: name }),
})
}
if (timeSeen) {
findings.push({
code: 'time-of-day', sheet: name,
message: t('Some dates in “{sheet}” include a time of day; the time was kept.', { sheet: name }),
})
}
if (formatSkipped) {
findings.push({
code: 'cell-format', sheet: name,
message: t('{n} formatted cells in “{sheet}” kept their bold, colour, background and borders; {skipped} more did not, because styling every cell of a long sheet would make the file much larger. All the values are there.', { n: formatted, sheet: name, skipped: formatSkipped }),
})
}
if (notLive) {
const fns = [...missingFns].slice(0, 4).join(', ')
const nms = [...missingNames].slice(0, 4).join(', ')
const why = fns ? t('dash doesn’t have {fns} yet', { fns })
: nms ? t('dash couldn’t follow {names}', { names: nms })
: t('references to other sheets or outside data')
findings.push({
code: 'formula-not-live', sheet: name,
message: notLive === 1
? t('1 formula in “{sheet}” couldn’t be made live ({why}). It shows the value Excel last calculated, and its formula text is kept.', { sheet: name, why })
: t('{n} formulas in “{sheet}” couldn’t be made live ({why}). They show the values Excel last calculated, and their formula text is kept.', { n: notLive, sheet: name, why }),
})
}
// THE TOTALS ROW, as the column property it is in Excel's own schema.
const totals: NonNullable<TableSheet['totals']> = {}
const lostTotals: string[] = []
if (table && totalsRows) {
for (const [ci, fn] of table.totals) {
const col = columns[ci]
if (!col) continue
const dashFn = TOTALS_FN[fn]
if (dashFn) totals[col.id] = dashFn
// `custom`, `stdDev` and `var` have no dash total. Inventing the nearest
// one would print a number under a column that is not the number the
// author asked for, which is worse than an empty footer.
else lostTotals.push(`${col.name} (${fn})`)
}
const kept = Object.keys(totals).length
const label = [...table.labels.values()][0]
// One sentence per fact, in the reader's terms: what became of the row,
// then anything that did not survive the trip.
const parts = [
kept === 0
? t('The totals row of {table} was taken out of the data, so it won’t sort or filter with it.', { table: table.name })
: kept === 1
? t('The totals row of {table} became a column total, so it won’t sort or filter with the data.', { table: table.name })
: t('The totals row of {table} became {n} column totals, so it won’t sort or filter with the data.', { table: table.name, n: kept }),
]
if (label) parts.push(t('Its “{label}” label isn’t carried — dash labels its own totals row.', { label }))
if (lostTotals.length) {
parts.push(lostTotals.length === 1
? t('There is no equivalent here for {list}, so that total was left off.', { list: lostTotals[0] })
: t('There is no equivalent here for {list}, so those totals were left off.', { list: lostTotals.join(', ') }))
}
findings.push({
code: 'totals-row', sheet: name,
message: parts.join(' '),
})
}
// FROZEN PANES. Excel's `ySplit` counts SHEET rows and its first one is the
// header; a dash dataset pins its header on its own, and `frozen.rows`
// counts DATA rows. So the `ySplit="1"` every fixture carried maps to zero
// frozen rows and loses NOTHING — the reader still sees the header — while
// `ySplit="2"` over a one-row header is the one data row the author pinned.
// Clamped to leave a row and a column free, which is `freezeAt`'s rule and
// is what stops a grid that cannot scroll.
const dropRows = firstRow + bodyStart
const frozen = {
rows: Math.max(0, Math.min(pane.rows - dropRows, Math.max(0, rowCount - 1))),
cols: Math.max(0, Math.min(pane.cols, Math.max(0, columns.length - 1))),
}
return {
id, name, kind: 'table',
rids: rowCount ? [[1, rowCount]] : [],
columns,
data,
...(Object.keys(totals).length ? { totals } : {}),
...(frozen.rows || frozen.cols ? { frozen } : {}),
...(Object.keys(overrides).length ? { cells: overrides } : {}),
steps: [{
op: 'import',
from: opts.source ?? `${name}.xlsx`,
at: opts.at ?? '',
rows: rowCount,
note: liveCount ? `${liveCount} cell formula(s) imported live` : undefined,
}],
} as TableSheet
}
/** The displayed text of a cell, for header names. */
function cellText(
c: RawCell | undefined, _shared: string[], styles: Styles, epoch1904: boolean,
): string {
if (!c) return ''
if (c.text !== undefined) return c.text
const k = kindOf(c, styles)
if (k === 'date') return serialToIso(Number(c.v), epoch1904) ?? c.v
return c.v
}
/**
* A spanning TITLE above the real header — and the repair for the worst import
* outcome in the 2026-08-18 bounce test.
*
* WHAT HAPPENED. `budget.xlsx` had "Jan 2026 budget" merged across A1:C1 and
* its real header ("Category / Budget / Actual") in row 2. Row 1 was taken as
* the header, so column A was called "Jan 2026 budget", B and C became
* "Column 2" and "Column 3", the real header became data row 1 — and because
* every numeric column now held one text value, EVERY COLUMN TYPED AS TEXT. A
* three-column budget arrived with no numbers in it, and dash said so three
* times (`merged-cells`, `empty-header`, `mixed-types`) without ever joining
* the three into the one sentence that would have helped.
*
* WHY THIS DETECTS AND ACTS RATHER THAN OFFERING. The evidence below is not a
* heuristic about what a header usually looks like; it is a PROOF that row 1
* is not one. A row holding a single value merged across the columns cannot be
* the header of those columns — there is one label for three of them — and the
* row under it names every column that has data. Nothing is discarded either
* way: taking row 1 costs a row of real data (the header becomes a row) and
* costs every numeric column its type, while taking row 2 costs the title,
* which is a caption and is named in the finding. So the honest thing is to do
* it and say it, loudly, with the row number to re-import against. Doing it
* SILENTLY is the one answer that is wrong — that is how the finding started.
*
* THE EVIDENCE, all four required:
* 1. a merged range on the first used row, spanning at least two columns;
* 2. that row holds exactly ONE value, and it is text (a title, not data);
* 3. the row below it has a text value in every column that has data;
* 4. there is more than one column, and there is a row below the header.
*
* (2) is what keeps this from stealing a header: the two-tier `Region | Q1 Q2`
* idiom merges B1:C1 over a row that is ALSO the header, and that row has
* three values, not one.
*/
function titleRowAbove(
grid: Array<Array<RawCell | undefined>>, merges: readonly string[],
firstRow: number, styles: Styles,
): { merge: string; text: string } | null {
if (grid.length < 3) return null
const spanning = merges.find((m) => {
const box = refBox(m.replace(/\$/g, ''))
return !!box && box.top === firstRow && box.right > box.left
})
if (!spanning) return null
const top = grid[0] ?? []
const filled = [...top.entries()].filter(([, c]) => !!c) as Array<[number, RawCell]>
if (filled.length !== 1) return null
if (familyOf(kindOf(filled[0][1], styles)) !== 'text') return null
// Every column with data under the title has to be named by the row below
// it, or that row is not a header either and nothing here is safe.
const used = new Set<number>()
for (let r = 1; r < grid.length; r++) {
for (const [ci, c] of (grid[r] ?? []).entries()) if (c) used.add(ci)
}
if (used.size < 2) return null
const second = grid[1] ?? []
for (const ci of used) {
const c = second[ci]
if (!c || familyOf(kindOf(c, styles)) !== 'text') return null
}
return { merge: spanning, text: cellText(filled[0][1], [], styles, false) }
}
/**
* Is the first row a header? Returns where the DATA starts (1 = yes, 0 = no).
*
* The test is not "row 1 is all strings" — a column of country names is all
* strings too. It is that row 1 is all strings AND the rows below it are not,
* i.e. row 1 looks different from its column. A sheet that is entirely text has
* no evidence either way, and there the header assumption is right far more
* often than not (it is what someone typed at the top), so it wins — but the
* caller reports the no-header decision when it goes the other way, which is
* the case where the reader loses a row of data to a header if we guess wrong.
*/
function decideHeader(
head: Array<RawCell | undefined>, grid: Array<Array<RawCell | undefined>>,
styles: Styles, forced?: boolean,
): { start: number; sure: boolean } {
if (forced !== undefined) return { start: forced ? 1 : 0, sure: true }
const filled = head.filter(Boolean) as RawCell[]
if (!filled.length || grid.length < 2) return { start: filled.length ? 1 : 0, sure: true }
const allText = filled.every((c) => familyOf(kindOf(c, styles)) === 'text')
if (!allText) return { start: 0, sure: true }
// Any non-text below means row 1 is describing rather than being data, and
// that IS evidence.
for (let r = 1; r < Math.min(grid.length, 25); r++) {
for (const c of grid[r] ?? []) {
const f = c ? familyOf(kindOf(c, styles)) : 'blank'
if (f !== 'text' && f !== 'blank') return { start: 1, sure: true }
}
}
// Text above text. No evidence at all, and the caller says so.
return { start: 1, sure: false }
}
// --- export ---------------------------------------------------------------------
export interface XlsxExportOpts {
/** Timestamp for the ZIP entries. Fixed by default so exports are diffable. */
at?: Date
/**
* sheetId → (columnId → computed values, in canonical row order).
*
* A FORMULA COLUMN HAS NO STORED VALUES — that is the point of it (model.ts:
* storing them would let a file carry a number that disagrees with the
* expression that produced it) — so an export that does not receive them
* writes blanks and says so. The editor hands `grid.computed` in, exactly as
* it does for charts.
*/
computed?: Record<string, Map<string, unknown[]>>
/** Skip DEFLATE (rig only). */
store?: boolean
}
export interface XlsxExportResult {
bytes: Uint8Array
findings: XlsxFinding[]
}
const MAIN_NS = 'http://schemas.openxmlformats.org/spreadsheetml/2006/main'
const REL_NS = 'http://schemas.openxmlformats.org/officeDocument/2006/relationships'
const CT = 'http://schemas.openxmlformats.org/package/2006/content-types'
const DECL = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
/**
* A sheet name Excel will accept.
*
* Excel does not warn about a bad sheet name — it refuses to open the file, or
* "repairs" it, which is the same thing with an extra dialog. The rules are:
* 1–31 characters, none of `[]:*?/\`, not starting or ending with an
* apostrophe, and unique within the workbook.
*/
function safeSheetName(name: string, taken: Set<string>): string {
let s = (name || 'Sheet').replace(/[[\]:*?/\\]/g, '-').replace(/^'+|'+$/g, '').trim()
if (!s) s = 'Sheet'
s = s.slice(0, 31)
if (taken.has(s.toLowerCase())) {
let k = 2
let t = `${s.slice(0, 28)} ${k}`
while (taken.has(t.toLowerCase())) { k++; t = `${s.slice(0, 28)} ${k}` }
s = t
}
taken.add(s.toLowerCase())
return s
}
/**
* The style table, built as we go: one xf per (format code, appearance) pair.
*
* IT CARRIES APPEARANCE BECAUSE THE IMPORT DOES. Bold, colour, background and
* borders now arrive from a workbook (see `CellLook`), and an exporter that
* dropped them would have turned one silent loss into a round trip that loses
* them on the way back out — the same finding, one door along. What dash holds
* per cell is what goes out; what dash does not hold (font family, size,
* per-edge weights) never came in.
*/
interface Paint {
bold?: boolean; italic?: boolean; underline?: boolean
color?: string; bg?: string
border?: string; borderColor?: string; borderStyle?: string
}
/** `#rrggbb` → Excel's opaque ARGB. Anything else is not a colour we wrote. */
const argb = (c: unknown): string | undefined =>
(typeof c === 'string' && /^#[0-9A-Fa-f]{6}$/.test(c) ? `FF${c.slice(1).toUpperCase()}` : undefined)
/** The appearance an override asks for, or undefined for a plain cell. */
function paintOf(over: CellOverride | undefined): Paint | undefined {
if (!over) return undefined
const p: Paint = {}
if (over.bold === true) p.bold = true
if (over.italic === true) p.italic = true
if (over.underline === true) p.underline = true
const color = argb(over.color)
if (color) p.color = color
const bg = argb(over.bg)
if (bg) p.bg = bg
if (typeof over.border === 'string' && over.border) {
p.border = over.border
const bc = argb(over.borderColor)
if (bc) p.borderColor = bc
if (over.borderStyle === 'dashed' || over.borderStyle === 'dotted') p.borderStyle = over.borderStyle
}
return Object.keys(p).length ? p : undefined
}
const EDGE_TAG: Record<string, string> = { t: 'top', r: 'right', b: 'bottom', l: 'left' }
class StyleBook {
codes: string[] = []
/** index 0 is the default font; every other entry is one an xf asked for. */
private fonts: Paint[] = [{}]
/** index 0 = none and index 1 = gray125 are REQUIRED by Excel; solid fills
* start at 2. Getting this wrong is the repair dialog. */
private fills: Array<string | undefined> = [undefined, undefined]
private borders: Paint[] = [{}]
xfs: Array<{ fmt: number; font: number; fill: number; border: number }> =
[{ fmt: 0, font: 0, fill: 0, border: 0 }]
private seen = new Map<string, number>()
private fontAt = new Map<string, number>()
private fillAt = new Map<string, number>()
private borderAt = new Map<string, number>()
constructor() { this.seen.set('|', 0) }
/** The cellXfs index for a format code (`''` = General) and an appearance. */
at(code: string, paint?: Paint): number {
const sig = paint
? `${paint.bold ? 'b' : ''}${paint.italic ? 'i' : ''}${paint.underline ? 'u' : ''}` +
`|${paint.color ?? ''}|${paint.bg ?? ''}|${paint.border ?? ''}|${paint.borderColor ?? ''}|${paint.borderStyle ?? ''}`
: ''
const key = `${code}|${sig}`
const hit = this.seen.get(key)
if (hit !== undefined) return hit
let fmt = 0
if (code) {
const i = this.codes.indexOf(code)
fmt = 164 + (i >= 0 ? i : this.codes.push(code) - 1)
}
const at = this.xfs.push({
fmt,
font: this.font(paint),
fill: this.fill(paint),
border: this.border(paint),
}) - 1
this.seen.set(key, at)
return at
}
private font(p?: Paint): number {
if (!p || (!p.bold && !p.italic && !p.underline && !p.color)) return 0
const key = `${p.bold ? 'b' : ''}${p.italic ? 'i' : ''}${p.underline ? 'u' : ''}|${p.color ?? ''}`
const hit = this.fontAt.get(key)
if (hit !== undefined) return hit
const at = this.fonts.push({
bold: p.bold, italic: p.italic, underline: p.underline, color: p.color,
}) - 1
this.fontAt.set(key, at)
return at
}
private fill(p?: Paint): number {
if (!p?.bg) return 0
const hit = this.fillAt.get(p.bg)
if (hit !== undefined) return hit
const at = this.fills.push(p.bg) - 1
this.fillAt.set(p.bg, at)
return at
}
private border(p?: Paint): number {
if (!p?.border) return 0
const key = `${p.border}|${p.borderColor ?? ''}|${p.borderStyle ?? ''}`
const hit = this.borderAt.get(key)
if (hit !== undefined) return hit
const at = this.borders.push({
border: p.border, borderColor: p.borderColor, borderStyle: p.borderStyle,
}) - 1
this.borderAt.set(key, at)
return at
}
xml(): string {
const numFmts = this.codes.length
? `<numFmts count="${this.codes.length}">${this.codes.map((c, i) =>
`<numFmt numFmtId="${164 + i}" formatCode="${escapeXml(c)}"/>`).join('')}</numFmts>`
: ''
const fonts = this.fonts.map((f) =>
`<font>${f.bold ? '<b/>' : ''}${f.italic ? '<i/>' : ''}${f.underline ? '<u/>' : ''}` +
`${f.color ? `<color rgb="${f.color}"/>` : ''}<sz val="11"/><name val="Calibri"/></font>`).join('')
const fills = this.fills.map((f, i) =>
`<fill><patternFill patternType="${i === 1 ? 'gray125' : f ? 'solid' : 'none'}">` +
`${f ? `<fgColor rgb="${f}"/><bgColor indexed="64"/>` : ''}</patternFill></fill>`).join('')
const borders = this.borders.map((b) => {
const style = b.borderStyle === 'dashed' ? 'dashed' : b.borderStyle === 'dotted' ? 'dotted' : 'thin'
const edge = (letter: string): string => {
const tag = EDGE_TAG[letter]
return (b.border ?? '').includes(letter)
? `<${tag} style="${style}">${b.borderColor ? `<color rgb="${b.borderColor}"/>` : ''}</${tag}>`
: `<${tag}/>`
}
return `<border>${edge('l')}${edge('r')}${edge('t')}${edge('b')}<diagonal/></border>`
}).join('')
// The counts and the two fills are not decoration. Excel requires at least
// the `none` and `gray125` fills and a `cellStyleXfs` entry; a styles part
// missing either is the single most common cause of the repair dialog.
return `${DECL}<styleSheet xmlns="${MAIN_NS}">${numFmts}` +
`<fonts count="${this.fonts.length}">${fonts}</fonts>` +
`<fills count="${this.fills.length}">${fills}</fills>` +
`<borders count="${this.borders.length}">${borders}</borders>` +
'<cellStyleXfs count="1"><xf numFmtId="0" fontId="0" fillId="0" borderId="0"/></cellStyleXfs>' +
`<cellXfs count="${this.xfs.length}">${this.xfs.map((x) =>
`<xf numFmtId="${x.fmt}" fontId="${x.font}" fillId="${x.fill}" borderId="${x.border}" xfId="0"` +
`${x.fmt ? ' applyNumberFormat="1"' : ''}${x.font ? ' applyFont="1"' : ''}` +
`${x.fill ? ' applyFill="1"' : ''}${x.border ? ' applyBorder="1"' : ''}/>`).join('')}</cellXfs>` +
'<cellStyles count="1"><cellStyle name="Normal" xfId="0" builtinId="0"/></cellStyles>' +
'<dxfs count="0"/></styleSheet>'
}
}
/** The number format a column should carry in Excel. The author's own
* `Column.format` wins — model.ts says it is an Excel-style pattern and it is
* DOCUMENT data, so passing it through is both correct and free. */
function formatFor(col: Column): string {
if (typeof col.format === 'string' && col.format) return col.format
switch (col.type) {
// ISO, deliberately: `mm/dd/yyyy` renders as 4 March in half the world and
// 3 April in the other half, and the export is what someone else opens.
case 'date': return 'yyyy-mm-dd'
case 'percent': return '0.0%'
case 'money': return moneyFormat(col.unit)
default: return ''
}
}
const SYMBOL: Record<string, string> = { USD: '$', GBP: '£', EUR: '€', JPY: '¥', INR: '₹' }
const moneyFormat = (unit: unknown): string => {
const u = typeof unit === 'string' ? unit.toUpperCase() : ''
const sym = SYMBOL[u]
if (sym) return `"${sym}"#,##0.00`
return u ? `#,##0.00" ${u}"` : '#,##0.00'
}
/** Shared-string table, built while the sheets are written. */
class Strings {
list: string[] = []
private at = new Map<string, number>()
total = 0
id(s: string): number {
this.total++
const hit = this.at.get(s)
if (hit !== undefined) return hit
const i = this.list.push(s) - 1
this.at.set(s, i)
return i
}
xml(): string {
return `${DECL}<sst xmlns="${MAIN_NS}" count="${this.total}" uniqueCount="${this.list.length}">` +
this.list.map((s) => `<si><t xml:space="preserve">${escapeXml(s)}</t></si>`).join('') +
'</sst>'
}
}
const AGG_FN: Record<string, string> = {
sum: 'SUM', avg: 'AVERAGE', count: 'COUNT', min: 'MIN', max: 'MAX',
}
/**
* Write a dash workbook as .xlsx.
*
* WHAT IS EXPORTED AS A FORMULA AND WHAT IS EXPORTED AS A NUMBER, which is the
* one judgement call in here:
*
* PER-CELL formulas (`CellOverride.f`) go out as formulas. They are already
* A1 expressions in Excel's own syntax; the only change they need is a
* one-row shift, because dash's row 0 is the first DATA row and Excel's row 1
* is the header.
*
* COLUMN formulas go out as VALUES. A dash column expression names columns by
* IDENTITY and evaluates over vectors — `Value / SUM(Value)` is one
* expression over a whole column — and Excel has no such thing. Translating
* it per row is possible for simple arithmetic and stops being possible the
* moment the expression uses anything whose Excel counterpart differs even
* slightly. A translation that is right 95% of the time produces a workbook
* that is wrong 5% of the time and says nothing, which is the failure mode
* this whole codebase is organised against. So the numbers go out, and a
* finding says the expression did not.
*/
export async function exportXlsx(
doc: DashDoc, opts: XlsxExportOpts = {},
): Promise<XlsxExportResult> {
const findings: XlsxFinding[] = []
const styles = new StyleBook()
const strings = new Strings()
const taken = new Set<string>()
const tables = doc.sheets.filter((s): s is TableSheet => s.kind === 'table')
if (!tables.length) {
findings.push({ code: 'sheet-skipped', message: t('This workbook has no datasets to export.') })
}
const sheetXml: string[] = []
const tableXml: Array<string | undefined> = []
const names: string[] = []
for (const sheet of tables) {
const name = safeSheetName(sheet.name, taken)
if (name !== sheet.name) {
findings.push({
code: 'renamed-sheet', sheet: sheet.name,
message: t('“{sheet}” was renamed “{name}” in the export, because Excel won’t open a file whose sheet name is longer than 31 characters or contains []:*?/\\.', { sheet: sheet.name, name }),
})
}
names.push(name)
const written = writeSheet(sheet, styles, strings, opts, findings, sheetXml.length)
sheetXml.push(written.xml)
tableXml.push(written.table)
}
const parts: ZipEntry[] = []
const enc = new TextEncoder()
const add = (n: string, s: string) => parts.push({ name: n, data: enc.encode(s) })
// [Content_Types].xml FIRST — a consumer streaming the package cannot type
// any part until it has read this one, and it is the one ordering rule OPC
// actually states.
add('[Content_Types].xml', `${DECL}<Types xmlns="${CT}">` +
'<Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>' +
'<Default Extension="xml" ContentType="application/xml"/>' +
'<Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>' +
sheetXml.map((_, i) => `<Override PartName="/xl/worksheets/sheet${i + 1}.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>`).join('') +
tableXml.map((t, i) => (t ? `<Override PartName="/xl/tables/table${i + 1}.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.table+xml"/>` : '')).join('') +
'<Override PartName="/xl/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/>' +
'<Override PartName="/xl/sharedStrings.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml"/>' +
'<Override PartName="/docProps/core.xml" ContentType="application/vnd.openxmlformats-package.core-properties+xml"/>' +
'</Types>')
add('_rels/.rels', `${DECL}<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">` +
`<Relationship Id="rId1" Type="${REL_NS}/officeDocument" Target="xl/workbook.xml"/>` +
'<Relationship Id="rId2" Type="http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties" Target="docProps/core.xml"/>' +
'</Relationships>')
add('docProps/core.xml',
`${DECL}<cp:coreProperties xmlns:cp="http://schemas.openxmlformats.org/package/2006/metadata/core-properties"` +
' xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:dcterms="http://purl.org/dc/terms/"' +
' xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">' +
`<dc:title>${escapeXml(doc.title ?? '')}</dc:title>` +
`<dc:creator>${escapeXml(String(doc.meta?.author ?? 'bento/dash'))}</dc:creator>` +
'</cp:coreProperties>')
// `fullCalcOnLoad` is why no formula cell needs a cached `<v>`: every
// consumer recalculates on open. A cached value we did not compute would be
// a number that disagrees with the formula beside it — the exact thing
// model.ts refuses to store for formula columns.
add('xl/workbook.xml', `${DECL}<workbook xmlns="${MAIN_NS}" xmlns:r="${REL_NS}">` +
`<sheets>${names.map((nm, i) =>
`<sheet name="${escapeXml(nm)}" sheetId="${i + 1}" r:id="rId${i + 1}"/>`).join('')}</sheets>` +
'<calcPr calcId="0" fullCalcOnLoad="1"/></workbook>')
add('xl/_rels/workbook.xml.rels',
`${DECL}<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">` +
names.map((_, i) => `<Relationship Id="rId${i + 1}" Type="${REL_NS}/worksheet" Target="worksheets/sheet${i + 1}.xml"/>`).join('') +
`<Relationship Id="rId${names.length + 1}" Type="${REL_NS}/styles" Target="styles.xml"/>` +
`<Relationship Id="rId${names.length + 2}" Type="${REL_NS}/sharedStrings" Target="sharedStrings.xml"/>` +
'</Relationships>')
sheetXml.forEach((x, i) => add(`xl/worksheets/sheet${i + 1}.xml`, x))
// A table is reached through its WORKSHEET's relationships, so the rels part
// exists only for the sheets that have one.
tableXml.forEach((t, i) => {
if (!t) return
add(`xl/worksheets/_rels/sheet${i + 1}.xml.rels`,
`${DECL}<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">` +
`<Relationship Id="rIdT" Type="${REL_NS}/table" Target="../tables/table${i + 1}.xml"/></Relationships>`)
add(`xl/tables/table${i + 1}.xml`, t)
})
// styles LAST of the pair, because writing the sheets is what filled it in
add('xl/styles.xml', styles.xml())
add('xl/sharedStrings.xml', strings.xml())
return { bytes: await writeZip(parts, { at: opts.at, store: opts.store }), findings }
}
/**
* The sheet's frozen panes, read defensively — the same reading `readFrozen`
* (rowcol.ts) does, spelled again here rather than imported, because this
* module is the FILE layer and rowcol.ts is the editing layer. An additive
* field can hold anything an older or hand-edited build put there, and
* anything unreadable means "not frozen".
*/
function readFrozenPane(sheet: TableSheet): { rows: number; cols: number } {
const f = sheet.frozen
if (typeof f !== 'object' || f === null || Array.isArray(f)) return { rows: 0, cols: 0 }
const o = f as Record<string, unknown>
const num = (v: unknown): number =>
(typeof v === 'number' && Number.isFinite(v) && v > 0 ? Math.floor(v) : 0)
return { rows: num(o.rows), cols: num(o.cols) }
}
/** Rows of one sheet, and the `ListObject` part that goes beside it. */
function writeSheet(
sheet: TableSheet, styles: StyleBook, strings: Strings,
opts: XlsxExportOpts, findings: XlsxFinding[], index: number,
): { xml: string; table?: string } {
const cols = sheet.columns.filter((c) => !c.hidden)
const rows = sheet.rids.reduce((n, [, c]) => n + c, 0)
const computed = opts.computed?.[sheet.id]
const overrides = sheet.cells ?? {}
const rids: number[] = []
for (const [start, count] of sheet.rids) for (let i = 0; i < count; i++) rids.push(start + i)
const headStyle = styles.at('', { bold: true })
const colStyle = cols.map((c) => styles.at(formatFor(c)))
const out: string[] = []
out.push(`<row r="1">${cols.map((c, i) =>
`<c r="${colToLetters(i)}1" s="${headStyle}" t="s"><v>${strings.id(c.name)}</v></c>`).join('')}</row>`)
for (let r = 0; r < rows; r++) {
const cells: string[] = []
for (let i = 0; i < cols.length; i++) {
const col = cols[i]
const rid = rids[r]
const over = overrides[`${col.id}:${rid}`]
const ref = `${colToLetters(i)}${r + 2}`
// A cell someone painted gets its own xf; every other cell in the column
// shares the column's one, so a sheet with three bolded cells has three
// extra styles rather than one per cell.
const paint = paintOf(over)
const style = paint ? styles.at(formatFor(col), paint) : colStyle[i]
// A per-cell formula: dash's `f`, or the source an import could not make
// live (`xlsxF`) — writing the latter back out is what closes the
// xlsx → dash → xlsx loop without losing someone's model.
const live = typeof over?.f === 'string' ? over.f : undefined
const src = live ?? (typeof over?.xlsxF === 'string' ? over.xlsxF : undefined)
if (src) {
// Down one row — but ONLY for a live formula. `f` is written in dash
// coordinates, where row 0 is the first DATA row, so it moves to
// Excel's row 2. `xlsxF` is the source text an import could not make
// live, kept verbatim in the ORIGINAL workbook's coordinates; shifting
// it would run a cross-sheet reference through a1.ts, which cannot see
// past the `!` and would silently renumber the other sheet's row.
const body = live
? shiftRefsForInsert(src.replace(/^=/, ''), 'row', 0, 1)
: src.replace(/^=/, '')
// A cached value if dash happens to hold one. It is NOT authoritative —
// dash computes a formula cell live and never writes the result back,
// so what is stored underneath may be stale — but `fullCalcOnLoad`
// means the first thing every consumer does is replace it. Emitting it
// is what lets an xlsx → dash round trip keep a formula column's TYPE
// instead of reading a column of empty cells as text.
const cached = over && 'v' in over ? over.v : readCell(sheet.data[col.id], r)
const cachedXml = cached === null || cached === undefined || cached === ''
? '' : cachedValue(cached, col.type)
cells.push(`<c r="${ref}" s="${style}"><f>${escapeXml(body)}</f>${cachedXml}</c>`)
continue
}
const v = over && 'v' in over ? over.v
: computed?.has(col.id) ? computed.get(col.id)![r]
: col.formula ? undefined
: readCell(sheet.data[col.id], r)
const cell = valueCell(ref, style, v, col.type, strings)
if (cell) cells.push(cell)
}
if (cells.length) out.push(`<row r="${r + 2}">${cells.join('')}</row>`)
}
for (const col of cols) {
if (col.formula && !computed?.has(col.id)) {
findings.push({
code: 'formula-column', sheet: sheet.name, column: col.name,
message: t('“{column}” is a formula column ({formula}) and its values weren’t available, so it was exported empty. Excel has no whole-column formulas — export from the editor, where the values are calculated.', { column: col.name, formula: col.formula }),
})
} else if (col.formula) {
findings.push({
code: 'formula-column', sheet: sheet.name, column: col.name,
message: t('“{column}” is a formula column ({formula}). Excel has no whole-column formulas, so its values were exported instead.', { column: col.name, formula: col.formula }),
})
}
}
// The totals row. Written as real SUM/AVERAGE formulas over the data range
// AND with the value we computed, so it reads correctly before a recalc.
let totalsXml = ''
if (sheet.totals && Object.keys(sheet.totals).length && rows) {
const cells: string[] = []
cols.forEach((col, i) => {
const spec = sheet.totals![col.id]
if (!spec) return
const ref = `${colToLetters(i)}${rows + 2}`
const range = `${colToLetters(i)}2:${colToLetters(i)}${rows + 1}`
if (typeof spec === 'object' && typeof spec.f === 'string') {
cells.push(`<c r="${ref}" s="${colStyle[i]}"><f>${escapeXml(spec.f.replace(/^=/, ''))}</f></c>`)
return
}
const fn = AGG_FN[spec as string]
if (!fn) return
const nums: number[] = []
for (let r = 0; r < rows; r++) {
const raw = computed?.has(col.id) ? computed.get(col.id)![r] : readCell(sheet.data[col.id], r)
const n = typeof raw === 'number' ? raw : Number(raw)
if (Number.isFinite(n) && raw !== null && raw !== '') nums.push(n)
}
// MIN/MAX BY LOOP, never `Math.min(...nums)`: `nums` holds one entry per
// ROW, a spread is one argument per entry, and the engine throws
// `RangeError: Maximum call stack size exceeded` past ~125k of them
// (condfmt.ts:52). Exporting a large workbook is precisely when this
// fires, and the throw escapes the whole export rather than spoiling one
// total. Seeded at ±Infinity so an all-blank column still reduces to the
// non-finite value the `shown` line below already turns into 0.
const extreme = (want: 'MIN' | 'MAX'): number => {
let acc = want === 'MIN' ? Infinity : -Infinity
for (const n of nums) {
if (want === 'MIN' ? n < acc : n > acc) acc = n
}
return acc
}
const value = fn === 'SUM' ? nums.reduce((a, b) => a + b, 0)
: fn === 'AVERAGE' ? (nums.length ? nums.reduce((a, b) => a + b, 0) / nums.length : 0)
: fn === 'COUNT' ? nums.length
: fn === 'MIN' ? extreme('MIN') : extreme('MAX')
const shown = Number.isFinite(value) ? value : 0
cells.push(`<c r="${ref}" s="${colStyle[i]}"><f>${fn}(${range})</f><v>${shown}</v></c>`)
})
if (cells.length) totalsXml = `<row r="${rows + 2}">${cells.join('')}</row>`
}
const widths = cols.map((c, i) => typeof c.w === 'number' && c.w > 0
? `<col min="${i + 1}" max="${i + 1}" width="${Math.max(2, (c.w - 5) / 7).toFixed(2)}" customWidth="1"/>`
: '').join('')
// DATA VALIDATION, out as well as in — which is the whole of what makes a
// round trip survive. `<dataValidations>` sits AFTER `<sheetData>` in the
// CT_Worksheet sequence; Excel refuses to open a file that puts it before,
// with the same "unreadable content" dialog it gives a truncated zip, so the
// ordering here is a hard requirement rather than tidiness.
const dv = writeValidations(cols, rows, sheet.name, findings)
// THE FROZEN PANE. The header is always pinned — that is what a dataset
// does, and every workbook this ever wrote said `ySplit="1"` — so what goes
// out is the header PLUS whatever rows the author froze, and `frozen.cols`
// as the column split. The `topLeftCell` has to agree with both splits or
// Excel opens the sheet scrolled somewhere nobody asked for.
const fz = readFrozenPane(sheet)
const ySplit = 1 + fz.rows
const pane = `<pane${fz.cols ? ` xSplit="${fz.cols}"` : ''} ySplit="${ySplit}"` +
` topLeftCell="${colToLetters(fz.cols)}${ySplit + 1}"` +
` activePane="${fz.cols ? 'bottomRight' : 'bottomLeft'}" state="frozen"/>`
const lastRow = rows + (totalsXml ? 2 : 1)
// THE TOTALS ROW GOES OUT AS A TABLE PROPERTY, not only as a row of SUM()s.
// Excel's totals row belongs to a `ListObject` — which is what a dash
// dataset IS (docs/dash-sheet-kinds.md) — and writing only the formulas
// means a re-import cannot tell the total from a deal, so the round trip
// comes home flatter than it left and the total sorts into the data.
const table = totalsXml ? tablePart(sheet, cols, rows, index, findings) : undefined
return {
xml: `${DECL}<worksheet xmlns="${MAIN_NS}" xmlns:r="${REL_NS}">` +
`<dimension ref="A1:${colToLetters(Math.max(0, cols.length - 1))}${Math.max(1, lastRow)}"/>` +
`<sheetViews><sheetView workbookViewId="0">${pane}</sheetView></sheetViews>` +
'<sheetFormatPr defaultRowHeight="15"/>' +
(widths ? `<cols>${widths}</cols>` : '') +
`<sheetData>${out.join('')}${totalsXml}</sheetData>${dv}` +
(table ? '<tableParts count="1"><tablePart r:id="rIdT"/></tableParts>' : '') +
'</worksheet>',
...(table ? { table } : {}),
}
}
const TOTALS_XLSX: Record<string, string> = {
sum: 'sum', avg: 'average', count: 'count', min: 'min', max: 'max',
}
/**
* The `<table>` part — one `ListObject` over the whole sheet, header and
* totals row included.
*
* EXCEL IS UNFORGIVING HERE and its complaint is the repair dialog, so the two
* rules that are actually enforced are checked rather than assumed: every
* `tableColumn name` must match its header cell EXACTLY, and the names must be
* non-empty and unique. dash column NAMES are display strings and carry no
* such promise (two columns may legitimately read "Value"), so a sheet that
* would break the rule gets no table part at all — the totals row still goes
* out as SUM() formulas, which is what shipped before this, and the finding
* says the property did not travel.
*/
function tablePart(
sheet: TableSheet, cols: readonly Column[], rows: number,
index: number, findings: XlsxFinding[],
): string | undefined {
const names = cols.map((c) => c.name.trim())
const unique = new Set(names.map((n) => n.toLowerCase()))
if (names.some((n) => !n) || unique.size !== names.length) {
findings.push({
code: 'totals-row', sheet: sheet.name,
message: t('The totals row of “{sheet}” was written as SUM() formulas instead of an Excel table, because Excel needs every column heading filled in and different. The numbers are the same; importing the file again will read the totals as an ordinary row.', { sheet: sheet.name }),
})
return undefined
}
const columns = cols.map((c, i) => {
const spec = sheet.totals?.[c.id]
const fn = typeof spec === 'string' ? TOTALS_XLSX[spec] : undefined
return `<tableColumn id="${i + 1}" name="${escapeXml(names[i])}"` +
(i === 0 && !fn ? ' totalsRowLabel="Total"' : '') +
(fn ? ` totalsRowFunction="${fn}"` : '') + '/>'
}).join('')
const right = colToLetters(Math.max(0, cols.length - 1))
const id = `T${index + 1}`
return `${DECL}<table xmlns="${MAIN_NS}" id="${index + 1}" name="${id}" displayName="${id}"` +
` ref="A1:${right}${rows + 2}" headerRowCount="1" totalsRowCount="1">` +
`<autoFilter ref="A1:${right}${rows + 1}"/>` +
`<tableColumns count="${cols.length}">${columns}</tableColumns>` +
`<tableStyleInfo name="TableStyleMedium2" showRowStripes="1"/></table>`
}
/**
* `<dataValidations>` for every column carrying a rule.
*
* The sqref is the column's DATA range — row 2 to row n+1, because dash's row
* 0 is Excel's row 2 — never the header and never the totals row. Putting the
* header inside the range is what makes Excel circle a heading as invalid data
* the first time somebody runs the command.
*
* `kind:'formula'` goes out as `type="custom"` with the expression this build
* never evaluated, verbatim: the `xlsxF` bargain, one level up. Whatever Excel
* meant by it, Excel gets back.
*
* The one rule that CANNOT go out is a list too long for Excel's 255-character
* `formula1`. Truncating it would export a rule that permits a subset of what
* the author allowed — a stricter rule than anyone wrote, which would start
* refusing real values on the other side. So it is dropped and named.
*/
function writeValidations(
cols: readonly Column[], rows: number, sheetName: string, findings: XlsxFinding[],
): string {
if (!rows) return ''
const out: string[] = []
cols.forEach((col, i) => {
const rule = columnRule(col)
if (!rule) return
const sqref = `${colToLetters(i)}2:${colToLetters(i)}${rows + 1}`
const common = ` allowBlank="${rule.blank === false ? 0 : 1}"` +
` errorStyle="${rule.on === 'reject' ? 'stop' : 'warning'}"` +
` error="${escapeXml(rule.message ?? describeRule(rule))}"` +
` errorTitle="${escapeXml(sheetName)}"`
if (rule.kind === 'list') {
const body = (rule.list ?? []).join(',')
// Excel's own limit, and it counts the quotes.
if (body.length + 2 > 255) {
findings.push({
code: 'data-validation', sheet: sheetName, column: col.name,
message: t('“{column}” has a list rule of {n} values — longer than the 255 characters Excel allows — so it was not exported rather than cut short.', { column: col.name, n: (rule.list ?? []).length }),
})
return
}
// `showDropDown` is the INVERSION noted on the import side: 1 HIDES it.
out.push(`<dataValidation type="list" sqref="${sqref}"${rule.noDropdown ? ' showDropDown="1"' : ''}${common}>` +
`<formula1>"${escapeXml(body)}"</formula1></dataValidation>`)
return
}
if (rule.kind === 'formula') {
if (!rule.formula) return
out.push(`<dataValidation type="custom" sqref="${sqref}"${common}>` +
`<formula1>${escapeXml(rule.formula.replace(/^=/, ''))}</formula1></dataValidation>`)
return
}
const type = rule.kind === 'number' ? 'decimal'
: rule.kind === 'date' ? 'date'
: rule.kind === 'textLength' ? 'textLength' : null
if (!type) return
const bound = (b: number | string | undefined): string | null => {
if (b === undefined) return null
if (rule.kind === 'date') {
const serial = typeof b === 'number' ? b : isoToSerial(String(b))
return serial === null ? null : String(serial)
}
const n = Number(b)
return Number.isFinite(n) ? String(n) : null
}
const lo = bound(rule.min)
const hi = bound(rule.max)
// An UNBOUNDED rule ("must be a number", no min or max) has no Excel
// spelling at all — every `decimal` validation carries an operator and at
// least one formula. It is dropped rather than invented, because inventing
// a bound is inventing a refusal.
if (lo === null && hi === null) {
findings.push({
code: 'data-validation', sheet: sheetName, column: col.name,
message: rule.kind === 'date'
? t('“{column}” must be a date, with no lower or upper limit. Excel’s data validation always needs a limit, so this rule was not exported.', { column: col.name })
: rule.kind === 'number'
? t('“{column}” must be a number, with no lower or upper limit. Excel’s data validation always needs a limit, so this rule was not exported.', { column: col.name })
: t('“{column}” has a text-length rule with no lower or upper limit. Excel’s data validation always needs a limit, so this rule was not exported.', { column: col.name }),
})
return
}
const op = lo !== null && hi !== null ? 'between'
: lo !== null ? 'greaterThanOrEqual' : 'lessThanOrEqual'
const f1 = lo !== null ? lo : hi!
out.push(`<dataValidation type="${type}" operator="${op}" sqref="${sqref}"${common}>` +
`<formula1>${f1}</formula1>${op === 'between' ? `<formula2>${hi}</formula2>` : ''}</dataValidation>`)
})
return out.length ? `<dataValidations count="${out.length}">${out.join('')}</dataValidations>` : ''
}
/**
* The `<v>` to sit beside an `<f>`, for NUMERIC results only.
*
* A text or boolean result would need its own `t` attribute on the enclosing
* `<c>`, which is written before we know the value; and a formula whose result
* is text is rare enough that carrying no cached value (and letting
* `fullCalcOnLoad` fill it in) costs nothing. Numbers are the case that matters,
* because they are what a re-import types the column from.
*/
function cachedValue(v: unknown, type: ColumnType): string {
if (type === 'date') {
const s = typeof v === 'number' ? v : isoToSerial(String(v))
return s === null ? '' : `<v>${s}</v>`
}
const n = typeof v === 'number' ? v : NaN
return Number.isFinite(n) ? `<v>${n}</v>` : ''
}
/** One value cell, or '' for a blank (a blank cell is simply absent in xlsx —
* writing `<c r="B7"/>` for every empty cell doubles a sparse sheet). */
function valueCell(
ref: string, style: number, v: unknown, type: ColumnType, strings: Strings,
): string {
if (v === null || v === undefined || v === '') return ''
if (typeof v === 'boolean') return `<c r="${ref}" s="${style}" t="b"><v>${v ? 1 : 0}</v></c>`
if (type === 'date') {
const serial = typeof v === 'number' ? v : isoToSerial(String(v))
// A date column with something in it that is not a date keeps the text
// rather than becoming a wrong day.
if (serial === null) return `<c r="${ref}" s="${style}" t="s"><v>${strings.id(String(v))}</v></c>`
return `<c r="${ref}" s="${style}"><v>${serial}</v></c>`
}
if (typeof v === 'number') {
// NaN and Infinity have no XSD double spelling; Excel calls the file
// corrupt rather than showing an error, so they go out as error cells.
if (!Number.isFinite(v)) return `<c r="${ref}" s="${style}" t="e"><v>#NUM!</v></c>`
return `<c r="${ref}" s="${style}"><v>${v}</v></c>`
}
const s = String(v)
// A dash cell can hold a formula ERROR value; it round-trips as an error cell
// rather than as the literal text "#DIV/0!".
if (/^#(DIV\/0!|VALUE!|REF!|NAME\?|NUM!|N\/A|NULL!|CYCLE!)$/.test(s)) {
return `<c r="${ref}" s="${style}" t="e"><v>${escapeXml(s === '#CYCLE!' ? '#REF!' : s)}</v></c>`
}
return `<c r="${ref}" s="${style}" t="s"><v>${strings.id(s)}</v></c>`
}
/** A filename for the download. `.xlsx`, never `.xls` — the extension is what
* every consumer sniffs first. */
export const xlsxFileName = (title: string): string =>
`${(title || 'Workbook').replace(/[/\\:*?"<>|]/g, '-').slice(0, 80)}.xlsx`
export const _internals = {
attrs, attr, elements, element, planColumn, decideHeader, readStyles,
readSharedStrings, readSheetCells, resolvePart, safeSheetName, valueCell,
clock, lettersToCol,
}