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
// Charts, bound to columns.
//
// A TILE NAMES A SHEET AND COLUMNS. The series arrays are DERIVED at render and
// never stored — slides learned this the expensive way with `syncLinkedChart`,
// where a chart that carried its own copy of the numbers could disagree with
// the table beside it, go stale without saying so, and doubled the file.
// Promoted here from a special case to the rule: there is no code path that
// writes series data into the document.
//
// So a chart cannot be wrong about its own data. Edit a cell and the chart
// moves; sort the grid and the chart does not, because sorting is view state.
//
// A FILTER IS NOT A SORT, and conflating them is what made the sentence above
// half wrong. A sort is a PERMUTATION: the same rows in a different order, and
// every aggregate over a permutation is the same aggregate, so a chart that
// ignores a sort is right. A FILTER changes which rows exist for this reading,
// and a chart that ignores one asserts a total nobody on screen can see.
// Measured on the starter workbook, Value > 10000: the grid painted 4 rows, the
// footer said £69,050 — and the chart beside it drew North, South AND East
// totalling £97,050, including an East bar for two rows the filter had just
// removed. Same failure as the footer totals (grid.ts `aggregate`), same fix:
// read the view vector, and say so on screen when it excludes rows.
//
// A CHART IS ABOUT A SHEET, so it is PINNED to the one it was built from and
// never follows the tab bar. `StoryStep` already spells this out in the format —
// a step carries `sheet` AND `chart`, because "revenue by region" is an argument
// about a particular table. Following the shown sheet would silently re-point
// the chart on every tab click, which is the same class of lie as a stale one,
// only faster; and the bound columns almost never exist on the next sheet, so
// what actually arrived was an empty axis (measured: title " · value", one
// category "", one null bar, a 0-to-1 y axis, no message). Instead the chart
// keeps drawing its own sheet, SAYS which sheet that is, and offers one button
// to re-point at the sheet in front of the reader. Nothing silent, nothing
// blank.
//
// NULLS ARE GAPS, NOT ZEROES. charts-lite's `num(v, 0)` turns a missing value
// into a plunge to the axis, which reads as "we sold nothing that month"
// rather than "we do not know". Missing values are dropped from the category
// entirely, and a series with nothing in it renders as absent rather than as a
// flat line at zero.
import { CHART_PRESETS, mountChart, type ChartLike } from '../../kernel/src/charts.ts'
import type { TableSheet } from './model.ts'
import { isErr } from './formula.ts'
import { readCell } from './store.ts'
import { readPattern } from './format.ts'
import { t } from './i18n.ts'
export interface ChartBinding {
/** category axis — usually a text column */
x: string
/** one series per column id */
series: string[]
kind: 'bar' | 'line' | 'pie' | 'scatter'
/** group rows by the x value and total the series, rather than one bar per row */
aggregate?: 'sum' | 'avg' | 'count' | 'none'
// ADDITIVE, all of them: a binding without these is the chart every earlier
// build drew, and an earlier build reading one with them ignores them.
/** the chart's title; absent = the generated "Region · Value" heading */
title?: string
/** show the legend; absent = only when there is more than one series */
legend?: boolean
/** a colour per series, by column id; absent = the palette, in order */
colors?: Record<string, string>
/**
* The author ASKED for a line through categories that have no order. Absent,
* a line over a text axis is refused and drawn as bars (see `lineRefused`).
*/
allowLine?: boolean
[extra: string]: unknown
}
type ColLike = { id: string; type?: string; format?: string; name?: string }
const MEASURE = new Set(['number', 'money', 'percent'])
const CATEGORY = new Set(['text', 'enum'])
/**
* Does this column put its values IN ORDER?
*
* A line is a claim that the space between two points means something — that
* March comes after February and a value between them would sit between them.
* Dates and numbers make that claim honestly. Regions do not: North, South, East
* in first-seen order is an accident of the data, and a line from North down to
* South "trends" between two things that are not on a scale at all. Measured in
* the review: the type toggle drew exactly that line, through Region.
*/
export const isOrdered = (c: Pick<ColLike, 'type'> | undefined): boolean =>
!!c && (c.type === 'date' || MEASURE.has(c.type ?? ''))
/**
* A line the chart will NOT draw: kind is line, the category column has no
* order, and the author did not say "draw it anyway". Drawn as bars instead,
* and the panel says so with a button that sets `allowLine`.
*/
export function lineRefused(sheet: TableSheet, bind: ChartBinding): boolean {
if (bind.kind !== 'line' || bind.allowLine) return false
return !isOrdered(sheet.columns.find((c) => c.id === bind.x))
}
/**
* A binding from what the reader SELECTED — what "Chart the selected columns"
* has always promised and, until this, never did (it charted the first three
* numeric columns whatever was selected: the review selected Value and got
* Value, Weighted and Computed).
*
* `selected` is the column ids under the selection, in sheet order. Its
* measures become the series; its first text column becomes the categories, or
* — when the selection holds none, the usual case of "select the numbers" — the
* sheet's first text column does. A selection with no measure in it at all asks
* for nothing chartable, so the sheet's default stands in rather than an empty
* axis.
*/
export function bindingFromSelection(sheet: TableSheet, selected: readonly string[]): ChartBinding | null {
const cols = selected
.map((id) => sheet.columns.find((c) => c.id === id))
.filter((c): c is TableSheet['columns'][number] => !!c)
const series = cols.filter((c) => MEASURE.has(c.type)).map((c) => c.id)
if (!series.length) return defaultBinding(sheet)
const cat = cols.find((c) => CATEGORY.has(c.type)) ??
sheet.columns.find((c) => CATEGORY.has(c.type))
const x = cat?.id ?? cols.find((c) => !series.includes(c.id))?.id ??
sheet.columns.find((c) => !series.includes(c.id))?.id ?? series[0]
// Grouping by a category is the chart people mean ("Value by Region"); with
// no category there is nothing to group by, and every row is its own point.
return { x, series, kind: 'bar', aggregate: cat ? 'sum' : 'none' }
}
/**
* The value axis's label pattern, from the columns being charted — so a money
* column's axis reads £20,000 and not 20,000, and a percent column's reads 40%
* and not 0.4.
*
* ONLY WHEN EVERY SERIES AGREES. Pounds and percentages on one axis share no
* unit, and labelling that axis "£" would put a currency sign on a probability.
* Mixed series get the bare numbers, which are at least not wrong.
*
* `scale` is 100 for a percent axis: percent columns store the FRACTION
* (import.ts:281), and charts-lite prints the number it is given.
*/
export function axisFormat(
sheet: TableSheet, series: readonly string[],
): { formatter?: string; scale: number; pattern: string; mixed: boolean } {
const cols = series.map((id) => sheet.columns.find((c) => c.id === id)).filter(Boolean) as ColLike[]
if (!cols.length) return { scale: 1, pattern: '', mixed: false }
const key = (c: ColLike): string => `${c.type}|${c.format ?? ''}`
if (cols.some((c) => key(c) !== key(cols[0]))) return { scale: 1, pattern: '', mixed: true }
const c = cols[0]
const p = readPattern(c.format)
const pct = p.pct || c.type === 'percent'
if (!p.prefix && !p.suffix && !pct) return { scale: 1, pattern: c.format ?? '', mixed: false }
return {
formatter: `${p.prefix}{value}${p.suffix}${pct && !p.suffix ? '%' : ''}`,
scale: pct ? 100 : 1,
pattern: c.format ?? (pct ? '0%' : ''),
mixed: false,
}
}
/**
* How to READ the sheet — never what to draw.
*
* `rows` is the view vector, `store.order[sheet.id]`: the canonical row indices
* the reader can see, in the order they see them, or null/undefined for "every
* row". It is exactly the argument grid.ts's `aggregate` takes for the footer
* totals, and for exactly the same reason — the two readouts sit on one screen
* and must not disagree.
*
* IT APPLIES TO EVERY BOUND COLUMN OR TO NONE, which is why it lives here
* rather than at the call site. `optionFor` zips the x column against each
* series column BY INDEX; project one of them and not another and the chart
* pairs the wrong label with the wrong number, looking entirely plausible while
* doing it. Passing an index vector makes that unrepresentable — the projection
* happens once, inside `colVals`, for whatever a column's values came from.
*
* The indices are CANONICAL sheet rows, so a `computed` map must be canonical
* too (grid.computed is: `recalc` runs over the whole sheet, ignoring the view).
* Callers that project the columns themselves — story.ts's `viewColumns`,
* dashboard.ts's cross-filter — pass no `rows` and are unaffected.
*/
export interface ChartViewOpts {
rows?: number[] | null
palette?: string[]
}
const PALETTE = ['#F7A600', '#5B8DEF', '#2FB47C', '#E1616C', '#8B6FD3', '#3FA9B8']
const asNumber = (v: unknown): number | null => {
if (v == null || v === '' || isErr(v)) return null
const n = typeof v === 'number' ? v : Number(String(v).replace(/[,\s£$€¥%]/g, ''))
return Number.isFinite(n) ? n : null
}
/**
* Build a charts-lite option from a sheet and a binding.
*
* `computed` lets a formula column be charted without being stored: the grid
* hands in what it just recalculated, so the chart shows the same numbers the
* reader is looking at rather than the raw column underneath them.
*
* `opts.rows` restricts it to the rows the reader can see — see `ChartViewOpts`.
*/
export function optionFor(
sheet: TableSheet,
bind: ChartBinding,
computed?: Map<string, unknown[]>,
opts: ChartViewOpts = {},
): Record<string, unknown> {
const palette = opts.palette ?? PALETTE
const rows = opts.rows ?? null
const all = sheet.rids.reduce((a, [, c]) => a + c, 0)
const colVals = (id: string): unknown[] => {
const c = computed?.get(id)
// ONE projection, applied to whatever the values came from. A raw column and
// a computed one must not take different paths here: that is how half a
// chart ends up filtered.
if (rows) {
const at = c ? (i: number) => c[i] : (i: number) => readCell(sheet.data[id], i)
return rows.map(at)
}
// NO VECTOR MEANS DO NOT TOUCH THE LENGTHS. story.ts and dashboard.ts hand
// in a map that is ALREADY the visible slice against a full-length sheet;
// re-expanding it to `all` here padded every projected column with
// `undefined` and broke both. (It did, for one commit: 8 red lines in
// test-dash-story.ts.)
if (c) return c
const d = sheet.data[id]
return Array.from({ length: all }, (_, i) => readCell(d, i))
}
const nameOf = (id: string) => sheet.columns.find((c) => c.id === id)?.name ?? id
const xs = colVals(bind.x).map((v) => (v == null ? '' : String(v)))
const agg = bind.aggregate ?? 'sum'
let cats: string[]
let series: Array<{ name: string; data: Array<number | null> }>
if (agg === 'none') {
cats = xs
series = bind.series.map((id) => ({
name: nameOf(id),
data: colVals(id).map(asNumber),
}))
} else {
// group by x, in first-seen order — a chart's categories should appear in
// the order the data does, not alphabetically
const order: string[] = []
const buckets = new Map<string, number[][]>()
xs.forEach((k, i) => {
if (!buckets.has(k)) { buckets.set(k, bind.series.map(() => [])); order.push(k) }
const b = buckets.get(k)!
bind.series.forEach((id, si) => {
const v = asNumber(colVals(id)[i])
if (v !== null) b[si].push(v)
})
})
cats = order
series = bind.series.map((id, si) => ({
name: nameOf(id),
data: order.map((k) => {
const vals = buckets.get(k)![si]
if (!vals.length) return null // a gap, never a zero
if (agg === 'count') return vals.length
const sum = vals.reduce((a, b) => a + b, 0)
return agg === 'avg' ? sum / vals.length : sum
}),
}))
}
if (bind.kind === 'pie') {
const first = series[0]
return {
...CHART_PRESETS.pie(),
color: palette,
...(bind.legend === false ? { legend: undefined } : {}),
series: [{
type: 'pie',
radius: '68%',
data: cats
.map((c, i) => ({ name: c, value: first?.data[i] }))
.filter((d) => d.value != null),
}],
}
}
// A line through unordered categories is REFUSED here, at the one place the
// option is built, so every surface that draws a binding (panel, story,
// dashboard) refuses it alike rather than one of them forgetting to.
const kind = lineRefused(sheet, bind) ? 'bar' : bind.kind
const base = CHART_PRESETS[kind]?.() ?? CHART_PRESETS.bar()
const ax = axisFormat(sheet, bind.series)
const showLegend = bind.legend ?? series.length > 1
// Colours are matched to SERIES, by column id — never by position: removing
// the first series must not repaint the second in the first one's colour.
const colors = bind.series.map((id, i) => bind.colors?.[id] ?? palette[i % palette.length])
const option: Record<string, unknown> = {
...base,
color: colors,
xAxis: { type: 'category', data: cats },
yAxis: { type: 'value', ...(ax.formatter ? { axisLabel: { formatter: ax.formatter } } : {}) },
series: series.map((s) => ({
type: kind,
name: s.name,
data: ax.scale === 1 ? s.data : s.data.map((v) => (v === null ? null : v * ax.scale)),
...(kind === 'line' ? { smooth: false, symbolSize: 6 } : {}),
})),
}
// charts-lite reads a PRESENT `legend` as "draw one" and ignores `show`, so
// a hidden legend is an absent key — `{show:false}` drew it anyway.
if (showLegend) option.legend = { bottom: 0 }
else delete option.legend
return option
}
/**
* The bound columns that are not in the sheet any more.
*
* A binding names columns by id, and ids outlive nothing: switch sheets and the
* whole binding is missing; delete one column and one series is. Both used to
* arrive as an axis with no bars — the worst possible answer, because an empty
* chart is indistinguishable from a chart of nothing, and the reader is left to
* guess which.
*/
export function missingColumns(sheet: TableSheet, bind: ChartBinding): string[] {
const have = new Set(sheet.columns.map((c) => c.id))
return [bind.x, ...bind.series].filter((id) => id && !have.has(id))
}
/**
* The panel's heading: what this chart is of, and — when the reader is looking
* at some other sheet — which sheet it is about.
*
* The sheet name is in front ONLY when it differs from the shown one. A label
* that always says "Pipeline · Region · Value" is read once and then never
* again, which is exactly what a warning must not be.
*/
export function chartHeading(
sheet: TableSheet, bind: ChartBinding, showing?: string | null,
): string {
const nameOf = (id: string) => sheet.columns.find((c) => c.id === id)?.name ?? id
const of = `${nameOf(bind.x)} · ${bind.series.map(nameOf).join(', ')}`
return showing && showing !== sheet.id ? `${sheet.name} · ${of}` : of
}
/**
* What can honestly be drawn from this binding against this sheet.
*
* Separated from `renderChart` so it can be asserted without a browser — the
* three outcomes below are a decision about truthfulness, and a decision like
* that should not be verifiable only by squinting at a panel.
*/
export interface ChartPlan {
/** bound column ids the sheet does not have */
missing: string[]
/** false when there is nothing truthful to draw: no x, or no series left */
drawable: boolean
/** the binding minus the columns that are gone */
bind: ChartBinding
}
export function chartPlan(sheet: TableSheet, bind: ChartBinding): ChartPlan {
const missing = missingColumns(sheet, bind)
const live = { ...bind, series: bind.series.filter((id) => !missing.includes(id)) }
return { missing, bind: live, drawable: !missing.includes(bind.x) && live.series.length > 0 }
}
export interface ChartHostOpts extends ChartViewOpts {
/**
* The sheet the reader is looking at, when it is NOT this chart's sheet.
* Null/absent means the chart and the grid are showing the same table.
*/
showing?: { id: string; name: string } | null
/**
* Re-point the chart at the sheet in front of the reader. Given, the panel
* offers it as a button; absent, the panel still SAYS what is wrong.
*
* It is a button and not an automatic rebind on purpose. Re-deriving a
* binding silently changes what the chart asserts without anyone asking, and
* the whole complaint this file answers is a chart that changed its mind
* quietly.
*/
onRebind?: () => void
/** "Draw a line anyway" — offered beside a refused line (`lineRefused`). */
onAllowLine?: () => void
}
/** A line of chrome above or below the plot. Text only — never innerHTML: a
* sheet name is user data and a column id can be anything. */
function noteBar(text: string, action?: { label: string; run: () => void }): HTMLElement {
const bar = document.createElement('div')
bar.className = 'dx-chart-note'
bar.style.cssText = 'flex:0 0 auto;display:flex;align-items:center;gap:8px;' +
'padding:6px 8px;font-size:12px;color:var(--muted);border-bottom:1px solid var(--line)'
const span = document.createElement('span')
span.style.cssText = 'flex:1 1 auto;min-width:0'
span.textContent = text
bar.append(span)
if (action) {
const b = document.createElement('button')
b.type = 'button'
b.className = 'dx-btn'
b.style.cssText = 'flex:0 0 auto;padding:3px 8px;font-size:12px'
b.textContent = action.label
b.addEventListener('click', action.run)
bar.append(b)
}
return bar
}
/**
* Mount a live chart into a host element. Returns a teardown.
*
* THREE OUTCOMES, and only one of them used to exist:
*
* 1. The binding resolves — draw it, from `opts.rows` if a filter is on, and
* say underneath how many rows that is. The count is the honest part: a
* screenshot of a filtered chart otherwise claims to be the whole table.
* 2. Some series columns are gone but the category column and at least one
* series survive — draw what survives and name what did not. Refusing the
* whole chart because one series was deleted throws away data the reader
* still has.
* 3. The category column is gone, or every series is — there is nothing
* truthful to draw. Say what it lost and offer one button, instead of the
* empty 0-to-1 axis this used to paint.
*/
export function renderChart(
host: HTMLElement,
sheet: TableSheet,
bind: ChartBinding,
computed?: Map<string, unknown[]>,
opts: ChartHostOpts = {},
): () => void {
host.innerHTML = ''
host.style.display = 'flex'
host.style.flexDirection = 'column'
const away = opts.showing && opts.showing.id !== sheet.id ? opts.showing : null
const { missing, bind: live, drawable } = chartPlan(sheet, bind)
const rebind = opts.onRebind
? { label: t('Chart {sheet} instead', { sheet: away?.name ?? sheet.name }), run: opts.onRebind }
: undefined
if (away) {
host.append(noteBar(
t('This chart is about {sheet}. You are looking at {other}.',
{ sheet: sheet.name, other: away.name }),
rebind))
}
if (missing.length) {
host.append(noteBar(
drawable
? t('{sheet} no longer has {cols}, so it is not drawn.',
{ sheet: sheet.name, cols: missing.join(', ') })
: t('This chart was drawn from {cols}, and {sheet} does not have {n} of them any more.',
{ cols: [bind.x, ...bind.series].join(', '), sheet: sheet.name, n: missing.length }),
away ? undefined : rebind))
}
if (drawable && lineRefused(sheet, live)) {
const xName = sheet.columns.find((c) => c.id === live.x)?.name ?? live.x
host.append(noteBar(
t('Drawn as bars: {col} has no order for a line to follow.', { col: xName }),
opts.onAllowLine ? { label: t('Draw a line anyway'), run: opts.onAllowLine } : undefined))
}
if (!drawable) {
// No plot at all. An axis with no bars is not a lesser version of a chart,
// it is a different and false claim.
const pad = document.createElement('div')
pad.style.cssText = 'flex:1 1 auto;min-height:0'
host.append(pad)
return () => { host.innerHTML = '' }
}
const plot = document.createElement('div')
plot.style.cssText = 'flex:1 1 auto;min-height:0;position:relative'
host.append(plot)
const all = sheet.rids.reduce((a, [, c]) => a + c, 0)
const shown = opts.rows ? opts.rows.length : all
// ONLY when rows are actually excluded. A caption that says "8 of 8" on every
// unfiltered chart is read as decoration and then not read at all — the same
// argument the footer's `dg-part` marker makes in grid.ts.
if (shown < all) {
const cap = document.createElement('div')
cap.className = 'dx-chart-rows'
cap.style.cssText = 'flex:0 0 auto;padding:4px 8px 2px;font-size:11px;color:var(--muted);text-align:right'
cap.textContent = t('{n} of {all} rows — a filter is hiding the rest.',
{ n: shown, all })
host.append(cap)
}
const el: ChartLike = {
// measured AFTER the notes and the caption are in the flow, so the plot
// gets the space it actually has rather than the panel's full height
w: plot.clientWidth || 640,
h: plot.clientHeight || 320,
option: optionFor(sheet, live, computed, { rows: opts.rows, palette: opts.palette }),
}
const down = mountChart(el, plot)
return () => { down(); host.innerHTML = '' }
}
/**
* A sensible default binding for a sheet: the first text column against every
* numeric one. This is what "+ Chart" produces, so the first chart takes no
* configuration at all.
*/
export function defaultBinding(sheet: TableSheet): ChartBinding | null {
const text = sheet.columns.find((c) => c.type === 'text' || c.type === 'enum')
// PERCENT columns are excluded unless there is nothing else. A 0-to-1 series
// on the same axis as money renders as no bar at all, so the legend names a
// series the reader cannot see — which is worse than leaving it out, because
// it looks like the data is missing rather than the scale being wrong.
// (A second axis is the real fix; it is not in v0.1.)
const magnitude = sheet.columns.filter((c) => c.type === 'number' || c.type === 'money')
const pct = sheet.columns.filter((c) => c.type === 'percent')
const nums = magnitude.length ? magnitude : pct
if (!nums.length) return null
return {
x: text?.id ?? sheet.columns[0].id,
series: nums.slice(0, 3).map((c) => c.id),
kind: 'bar',
aggregate: text ? 'sum' : 'none',
}
}