Spreadsheet
A small editable grid with basic formulas, for budgets, quotes, price lists and score sheets.
c2-spreadsheet is a calculating grid for the cases where an app needs a few rows of numbers that add up: a budget,
a quote with line items, a price list, a grade sheet. It is one sheet with about twenty functions, keyboard editing
that works like Google Sheets and Excel, and copy and paste with both. It does not try to be a spreadsheet
application: there is no cell styling, no second sheet and no file import.
data holds the raw inputs row by row. A string starting with = is a formula, a leading ' keeps the rest as
text, and a number typed as 10% or $12 keeps that look. columns is either a count or one object per column with
a label, a width in pixels, a format (general, number, currency, percent, text) and an align. The
column letter stays in the header next to the label, because formulas use the letter. Edits write a new array to
data and fire cell-change with every cell the user changed.
Formulas support + - * / ^ &, comparisons, a % suffix, A1 references and A1:B5 ranges, and the functions
SUM, AVERAGE, MIN, MAX, COUNT, COUNTA, ROUND, ABS, IF, IFERROR, AND, OR, NOT, SUMIF,
CONCAT, LEN, UPPER, LOWER, TRIM and TODAY. Add your own through the functions property. Changing a cell
recalculates only the cells that read it. Errors stay in their cell (#DIV/0!, #REF!, #NAME?, #VALUE!,
#CIRC!) and the tooltip says why.
Arrows move, Shift+arrows extend the range, typing replaces the cell, Enter, F2 or a double-click edit it, Enter and
Tab commit, Escape cancels and Delete clears. Ctrl/⌘+C, X and V copy, cut and paste tab-separated text, so a range
copied from Excel or Sheets pastes straight in; a formula copied inside the grid moves its references when pasted
(=B2*C2 one row down becomes =B3*C3, and $ pins a row or column). Ctrl/⌘+Z and Y undo and redo. Tab outside an
edit leaves the grid.
A paste larger than the space left grows the sheet to fit rather than dropping what does not fit. To change the
sheet’s shape from code, call insertRows(index, count), insertColumns(index, count), deleteRows(index, count) or
deleteColumns(index, count) with a zero-based index (0 for the top or first column, rows or the column count
for the end). The cells after it move, column options move with their columns, and formulas keep reading the same
cells: =SUM(B1:B5) becomes =SUM(B1:B6) when a row goes in inside the range and =SUM(B1:B4) when one comes out,
and a reference to a deleted cell becomes #REF!. These calls clear the undo history; at least one row and one
column always stay.
Drag the right edge of a column header to resize the column, or double-click it to fit the column to its widest
cell. The new width is written to columns and reported by column-resize, so an app can save it and hand it back.
A column stops at its minWidth (32px by default); resizable: false on a column, or resizable="false" on the
sheet, turns resizing off.
Import
import '@c2n/components/spreadsheet'Usage
Insert and delete from a context menu
Wrapped in a c2-context-menu, a right-clicked cell, row header or column header selects itself (unless it is
already selected) and answers the menu with a SpreadsheetContextMenuData: the area (cell, row, column or
corner), the selected range and active cell, and the zero-based start and end of the selection. The sheet
has no built-in menu, so the app decides what to offer:
import { html } from 'lit'
import type { ContextMenu } from '@c2n/components/context-menu'
import type { Spreadsheet, SpreadsheetContextMenuData } from '@c2n/components/spreadsheet'
const menu = document.querySelector<ContextMenu>('c2-context-menu')!
menu.renderContextMenu = ({ data, source }) => {
if (source?.localName !== 'c2-spreadsheet') return null
const { area } = data as SpreadsheetContextMenuData
return html`
${area !== 'column' ? html`<c2-menu-item value="row-above">Insert row above</c2-menu-item>` : ''}
${area !== 'row' ? html`<c2-menu-item value="column-left">Insert column left</c2-menu-item>` : ''}
<hr />
${area !== 'column' ? html`<c2-menu-item value="delete-rows" destructive>Delete rows</c2-menu-item>` : ''}
${area !== 'row' ? html`<c2-menu-item value="delete-columns" destructive>Delete columns</c2-menu-item>` : ''}
`
}
menu.addEventListener('context-menu-select', ({ detail }) => {
const sheet = detail.context.source as Spreadsheet
const { start, end } = detail.context.data as SpreadsheetContextMenuData
if (detail.value === 'row-above') sheet.insertRows(start.row, end.row - start.row + 1)
if (detail.value === 'column-left') sheet.insertColumns(start.column, end.column - start.column + 1)
if (detail.value === 'delete-rows') sheet.deleteRows(start.row, end.row - start.row + 1)
if (detail.value === 'delete-columns') sheet.deleteColumns(start.column, end.column - start.column + 1)
})Typing a formula is guided. After =, or on a name, a list offers the functions with their arguments and what they do: ↑↓ chooses and Tab or Enter inserts SUM(. Inside a call, a hint names its arguments with the current one in bold, and the result shows before Enter. Where a reference can go, clicking a cell puts it in the formula and dragging makes it a range. The formula bar’s fx button lists every function. App functions from functions are listed by name.
With context-menu, a right-click opens the sheet’s own menu, loaded with the first sheet that asks for it: insert and delete rows and columns, rename a column, add a total, and make the selected cells bold, coloured or larger. Its changes fire input and change like an edit. Leave it off when you wrap the sheet in your own c2-context-menu, which receives SpreadsheetContextMenuData.
With hide-selection-on-blur, the selection and the current cell show only while the sheet has the focus or a menu acting on it is open (its own, or a c2-context-menu around it). Use it for a sheet among other content, such as a card on a board or a table in a page, where a highlight left behind would look like the sheet is still in use.
With summary, each total of the selection is a button that writes its formula next to the range: under each column of a taller range (=SUM(C1:C4) in C5), after a one-row range, with a row or column inserted when that place is taken. insertTotal('SUM', 'C1:C4') does the same from your own menu.
Cells take a style in styles or through setStyle(range, change): one of eight named text and background colours (gray, brown, orange, yellow, green, blue, purple, red), a small or large size, and bold, which Ctrl/⌘+B toggles on the selection. Styles move with their cells, paste with a range copied from the sheet and undo with the values; a user’s change fires style-change.
With rename-columns, double-clicking a column header names the column in place, and renameColumn(index) opens the same field from your own menu: Enter or leaving the field saves the name, Escape cancels, and Tab saves it and goes on to the next column. The name is written to columns as the column’s label and reported by column-rename.
Read the result from script with getValue('E8'), write a cell with setInput('C1', 8), select a range with
select('B1:E5') and export the computed values with toCSV() or toTSV(). selection-change does not bubble, so
listen on the element itself.
const sheet = document.querySelector('c2-spreadsheet')!
sheet.functions = { TAX: ([amount]) => (typeof amount === 'number' ? amount * 0.2 : 0) }
sheet.addEventListener('cell-change', (event) => save(sheet.data, event.detail.changes))