import { strToU8, zipSync } from 'fflate'
/** One sheet ready to write: its sanitized name, headers, and cells. */
export interface Sheet {
name: string
columns: string[]
rows: unknown[][]
}
const XML = '\n'
const NS = 'http://schemas.openxmlformats.org/spreadsheetml/2006/main'
const REL = 'http://schemas.openxmlformats.org/officeDocument/2006/relationships'
function esc(s: string): string {
return s
.replace(/[\x00-\x08\x0b\x0c\x0e-\x1f]/g, '')
.replace(/&/g, '&')
.replace(//g, '>')
.replace(/"/g, '"')
}
function colName(i: number): string {
let s = ''
for (let n = i + 1; n > 0; n = Math.floor((n - 1) / 26)) s = String.fromCharCode(65 + ((n - 1) % 26)) + s
return s
}
/** A plain decimal written as text, like "4.77" or "-12", but not "007" or "1,000". */
const NUMERIC = /^-?(0|[1-9]\d*)(\.\d+)?([eE][-+]?\d+)?$/
type Cell = { kind: 'n'; value: number } | { kind: 'b'; value: boolean } | { kind: 's'; value: string } | null
function toCell(v: unknown): Cell {
if (v === null || v === undefined || v === '') return null
if (typeof v === 'number') return Number.isFinite(v) ? { kind: 'n', value: v } : { kind: 's', value: String(v) }
if (typeof v === 'boolean') return { kind: 'b', value: v }
if (typeof v === 'string') {
const t = v.trim()
if (NUMERIC.test(t) && t.length < 16) return { kind: 'n', value: Number(t) }
return { kind: 's', value: v.slice(0, 32767) }
}
return { kind: 's', value: JSON.stringify(v).slice(0, 32767) }
}
function cellXml(ref: string, cell: Cell, style: number): string {
const s = style ? ` s="${style}"` : ''
if (!cell) return style ? `` : ''
if (cell.kind === 'n') return `${cell.value}`
if (cell.kind === 'b') return `${cell.value ? 1 : 0}`
return `${esc(cell.value)}`
}
function displayLength(cell: Cell): number {
if (!cell) return 0
if (cell.kind === 'n') return String(cell.value).length
if (cell.kind === 'b') return 5
return Math.max(...cell.value.split('\n').map((l) => l.length))
}
function sheetXml(sheet: Sheet): string {
const width = Math.max(sheet.columns.length, ...sheet.rows.map((r) => r.length), 1)
const widths = Array.from({ length: width }, (_, i) => (sheet.columns[i] ?? '').length + 2)
const rows: string[] = []
const header = Array.from({ length: width }, (_, i) =>
cellXml(`${colName(i)}1`, { kind: 's', value: sheet.columns[i] ?? `Column ${i + 1}` }, 1),
)
rows.push(`${header.join('')}
`)
sheet.rows.forEach((row, r) => {
const cells = row.map((v, c) => {
const cell = toCell(v)
widths[c] = Math.max(widths[c] ?? 0, displayLength(cell) + 2)
return cellXml(`${colName(c)}${r + 2}`, cell, 0)
})
rows.push(`${cells.join('')}
`)
})
const cols = widths
.map((w, i) => ``)
.join('')
const last = `${colName(width - 1)}${sheet.rows.length + 1}`
return (
XML +
`` +
`` +
`` +
`` +
`${cols}` +
`${rows.join('')}` +
`` +
``
)
}
const STYLES =
XML +
`` +
`` +
`` +
`` +
`` +
`` +
`` +
`` +
`` +
`` +
`` +
``
/** The .xlsx file's bytes for these sheets. */
export function buildXlsx(sheets: Sheet[]): Uint8Array {
const files: Record = {}
const overrides = sheets
.map(
(_, i) =>
``,
)
.join('')
files['[Content_Types].xml'] = strToU8(
XML +
`` +
`` +
`` +
`` +
`` +
overrides +
``,
)
files['_rels/.rels'] = strToU8(
XML +
`` +
``,
)
files['xl/workbook.xml'] = strToU8(
XML +
`` +
sheets.map((s, i) => ``).join('') +
`` +
sheets
.map((s, i) => {
const width = Math.max(s.columns.length, ...s.rows.map((r) => r.length), 1)
const quoted = `'${s.name.replace(/'/g, "''")}'`
return `${esc(quoted)}!$A$1:$${colName(width - 1)}$${s.rows.length + 1}`
})
.join('') +
``,
)
files['xl/_rels/workbook.xml.rels'] = strToU8(
XML +
`` +
sheets
.map((_, i) => ``)
.join('') +
``,
)
files['xl/styles.xml'] = strToU8(STYLES)
sheets.forEach((s, i) => (files[`xl/worksheets/sheet${i + 1}.xml`] = strToU8(sheetXml(s))))
return zipSync(files, { level: 6 })
}