Spreadsheet
A small editable grid with basic formulas, for budgets, quotes, price lists and score sheets.
Attributes
| Name | Type | Default | Description |
|---|---|---|---|
styles | CustomEvent | — | Cell styles keyed by address ({"D5": {"bold": true, "background": "yellow"}}), as JSON. Edits write a new object to the styles property, so it always holds the current styles. |
data | CellInput[][] | [] | Raw inputs, row by row: numbers, booleans, text, or formulas as strings starting with =. JSON in markup.
Edits write a new array here, so it always holds the current inputs. |
rows | number | 20 | Number of rows. Grows to fit data, a paste past the last row and insertRows. |
columns | number | SpreadsheetColumn[] | 8 | Number of columns, or one options object per column (label, width, format, align, resizable, minWidth). JSON
in markup. Grows to fit data, a paste past the last column and insertColumns. Resizing a column writes a new
array here with its width. |
resizable | boolean | true | Lets the user drag a column header's edge to resize the column, and double-click it to fit the content. On by
default; resizable="false" turns it off. A column's own resizable still wins. |
rename-columns | boolean | false | Lets the user name a column by double-clicking its header. renameColumn() works without it. |
context-menu | boolean | false | A right-click menu of its own: 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 an
enclosing c2-context-menu builds the menu (it gets SpreadsheetContextMenuData). |
hide-selection-on-blur | boolean | false | Shows the selection and the current cell only while the sheet has the focus or its menu is open (its own, or a
c2-context-menu it sits in), for a sheet among other content (a card on a board, a table in a page) where a
highlight left behind reads as still being there. |
formula-bar | boolean | false | Shows the current cell's address and raw input above the grid. The formula bar is editable. |
summary | boolean | false | Shows Sum, Average and Count of the selected cells below the grid. |
readonly | boolean | false | Selection and copy work; editing, paste, cut and delete do not. |
label | string | — | Accessible name of the grid. |
locale | string | undefined | — | Locale for number and currency display. Defaults to the browser's. Formulas always use , between arguments. |
currency | string | USD | ISO currency code for the currency format and for amounts typed with $. |
Events
| Name | Type | Description |
|---|---|---|
input | Event | Fired with every cell-change, for generic two-way bindings. |
change | Event | Fired with every cell-change, for generic two-way bindings. |
selection-change | CustomEvent<SpreadsheetSelectionChangeEventDetail> | Fired after the user moves the selection. detail.range is the selected range and detail.active the current cell. Does not bubble: several components fire selection-change, so a listener belongs on the element itself. |
cell-change | CustomEvent<SpreadsheetCellChangeEventDetail> | Fired once per edit, paste, cut, delete, undo or redo with every cell the user changed (detail.changes: ref, input, value, previous). Cells that only recalculated are not listed. |
style-change | CustomEvent<SpreadsheetStyleChangeEventDetail> | Fired when the user restyles cells (Ctrl/⌘+B, a paste or a cut, undo or redo) with every cell whose style changed (detail.changes: ref, style, previous). setStyle() fires none. |
column-rename | CustomEvent<SpreadsheetColumnRenameEventDetail> | Fired when the user saves a new name for a column. detail.column is the letter, detail.index the zero-based index, detail.label the new name ('' when cleared) and detail.previous the old one. The name is also written to columns. |
column-resize | CustomEvent<SpreadsheetColumnResizeEventDetail> | Fired when the user releases a column's resize handle or double-clicks it to fit the content. detail.column is the letter, detail.index the zero-based index and detail.width the new width in pixels, which is also written to columns. |
CSS parts
Shadow DOM styling hooks used with ::part(name). Prefer CSS custom properties when they cover the change.
| Name | Description |
|---|---|
resizer | The grab area at a column header's right edge. |
CSS custom properties
Grouped by semantic target and state from --c2-<component>__<target>__<state>--<property>. Click a group to collapse it; click a name to copy it.
| Name | Type | Default | Description |
|---|---|---|---|
--c2-spreadsheet--background-color | color | #ffffff | |
--c2-spreadsheet--color | color | #18181b | |
--c2-spreadsheet--font-family | font-family | — | |
--c2-spreadsheet--font-size | font-size | 14px | |
--c2-spreadsheet--border | border | 1px solid #bcbcc6 | |
--c2-spreadsheet--border-radius | border-radius | 8px | |
--c2-spreadsheet--max-height | pixel | — | The grid scrolls when it is taller than this. |
--c2-spreadsheet--width | width | auto | Width of the frame: auto fills the line; fit-content ends it with the last column, a sheet wider than its container still scrolling inside it. |
--c2-spreadsheet--color-gray | color | #91918e | Text of a cell styled color: 'gray'. |
--c2-spreadsheet--color-brown | color | #a27763 | Text of a cell styled color: 'brown'. |
--c2-spreadsheet--color-orange | color | #d9730d | Text of a cell styled color: 'orange'. |
--c2-spreadsheet--color-yellow | color | #cb912f | Text of a cell styled color: 'yellow'. |
--c2-spreadsheet--color-green | color | #448361 | Text of a cell styled color: 'green'. |
--c2-spreadsheet--color-blue | color | #337ea9 | Text of a cell styled color: 'blue'. |
--c2-spreadsheet--color-purple | color | #9065b0 | Text of a cell styled color: 'purple'. |
--c2-spreadsheet--color-red | color | #d44c47 | Text of a cell styled color: 'red'. |
--c2-spreadsheet--background-gray | color | rgba(145, 145, 142, 0.2) | Background of a cell styled background: 'gray'. |
--c2-spreadsheet--background-brown | color | rgba(162, 119, 99, 0.2) | Background of a cell styled background: 'brown'. |
--c2-spreadsheet--background-orange | color | rgba(217, 115, 13, 0.18) | Background of a cell styled background: 'orange'. |
--c2-spreadsheet--background-yellow | color | rgba(223, 171, 1, 0.22) | Background of a cell styled background: 'yellow'. |
--c2-spreadsheet--background-green | color | rgba(68, 131, 97, 0.18) | Background of a cell styled background: 'green'. |
--c2-spreadsheet--background-blue | color | rgba(51, 126, 169, 0.18) | Background of a cell styled background: 'blue'. |
--c2-spreadsheet--background-purple | color | rgba(144, 101, 176, 0.18) | Background of a cell styled background: 'purple'. |
--c2-spreadsheet--background-red | color | rgba(212, 76, 71, 0.18) | Background of a cell styled background: 'red'. |
--c2-spreadsheet__grid__focus--outline | outline | 2px solid rgba(2, 101, 220, 0.4) | Ring around the grid when it has keyboard focus. |
--c2-spreadsheet__cell--width | pixel | 112px | Width of a column without its own width. |
--c2-spreadsheet__cell--height | pixel | 32px | |
--c2-spreadsheet__cell--padding | padding | 0 10px | |
--c2-spreadsheet__cell--border-color | color | #e4e4e7 | |
--c2-spreadsheet__cell__selected--background-color | color | rgba(2, 101, 220, 0.08) | Cells in a selected range. |
--c2-spreadsheet__cell__active--outline | outline | 2px solid rgb(2, 101, 220) | Ring on the current cell. |
--c2-spreadsheet__cell__error--color | color | #dc2626 | |
--c2-spreadsheet__cell__small--font-size | font-size | 0.86em | Text of a cell styled size: 'small'. |
--c2-spreadsheet__cell__large--font-size | font-size | 1.25em | Text of a cell styled size: 'large'. |
--c2-spreadsheet__cell__bold--font-weight | font-weight | 600 | Text of a bold cell. |
--c2-spreadsheet__header--background-color | color | #fafafa | Column and row headers. |
--c2-spreadsheet__header--color | color | #71717a | |
--c2-spreadsheet__header--font-size | font-size | 12px | |
--c2-spreadsheet__header--font-weight | font-weight | 500 | |
--c2-spreadsheet__header__selected--background-color | color | #edf1fe | Headers of the selected rows and columns. |
--c2-spreadsheet__header__selected--color | color | rgb(2, 101, 220) | |
--c2-spreadsheet__column-label--color | color | #18181b | Column label shown after the letter. |
--c2-spreadsheet__row-header--width | pixel | 44px | |
--c2-spreadsheet__resizer--width | pixel | 9px | Width of a column's resize grab area, not of the visible line. |
--c2-spreadsheet__resizer__hover--color | color | rgb(2, 101, 220) | The line drawn at a column's edge while its handle is hovered or dragged. |
--c2-spreadsheet__resizer__hover--line-width | pixel | 2px | |
--c2-spreadsheet__editor--background-color | color | #ffffff | The input over a cell being edited. |
--c2-spreadsheet__editor--font-family | font-family | ui-monospace, SFMono-Regular, Menlo, Consolas, monospace | A formula being edited in its cell; text keeps the cell's font. |
--c2-spreadsheet__editor--box-shadow | box-shadow | 0 4px 14px rgba(24, 24, 27, 0.14) | |
--c2-spreadsheet__formula-bar--height | pixel | 38px | |
--c2-spreadsheet__formula-bar--background-color | color | #ffffff | |
--c2-spreadsheet__formula-bar--font-family | font-family | ui-monospace, SFMono-Regular, Menlo, Consolas, monospace | |
--c2-spreadsheet__name-box--width | pixel | 72px | The box showing the current cell or range. |
--c2-spreadsheet__fx--color | color | #71717a | The fx mark before the formula. |
--c2-spreadsheet__fx__hover--background-color | color | #edf1fe | The fx button (it lists the functions) under the pointer. |
--c2-spreadsheet__fx__hover--color | color | rgb(2, 101, 220) | |
--c2-spreadsheet__summary--background-color | color | #fafafa | The Sum / Average / Count line. |
--c2-spreadsheet__summary--color | color | #71717a | |
--c2-spreadsheet__summary--font-size | font-size | 12px | |
--c2-spreadsheet__summary-value--color | color | #18181b | |
--c2-spreadsheet__reference-1--color | color | rgb(2, 101, 220) | Outline of the first cell or range a formula being edited reads; the next three references use colours 2 to 4. |
--c2-spreadsheet__reference-2--color | color | #c2410c | |
--c2-spreadsheet__reference-3--color | color | #7c3aed | |
--c2-spreadsheet__reference-4--color | color | #0f766e | |
--c2-spreadsheet__total__hover--background-color | color | rgba(2, 101, 220, 0.08) | A total in the totals bar under the pointer. |
--c2-spreadsheet__help--background-color | color | #ffffff | The formula help: function suggestions and the argument hint. |
--c2-spreadsheet__help--color | color | #18181b | |
--c2-spreadsheet__help--border | border | 1px solid #e4e4e7 | |
--c2-spreadsheet__help--border-radius | length | 10px | |
--c2-spreadsheet__help--box-shadow | shadow | 0 12px 32px rgba(24, 24, 27, 0.16) | |
--c2-spreadsheet__help--width | length | 340px | |
--c2-spreadsheet__help-muted--color | color | #71717a | Argument names and descriptions in the help. |
--c2-spreadsheet__help-option__active--background-color | color | #edf1fe | The suggestion Tab would insert. |
--c2-spreadsheet__help-accent--color | color | rgb(2, 101, 220) | The typed part of a suggested name. |
Text and languages
The text the component shows, in English. It shows another language when the app adds one with addTranslations, under spreadsheet, and the nearest lang asks for it; any message not translated stays in English. {0}, {1}… stand for the values a message carries. Languages guide.
| Key | English | Values |
|---|---|---|
columnName | Name of column {0} | {0} column |
selectToSum | Select cells to see their sum | — |
sum | Sum | — |
average | Average | — |
count | Count | — |
insertTotal | Insert {0} in {1} | {0} formula {1} cell |
minimum | Minimum | — |
maximum | Maximum | — |
insertRowAbove | Insert row above | — |
insertRowBelow | Insert row below | — |
insertColumnLeft | Insert column left | — |
insertColumnRight | Insert column right | — |
renameColumn | Rename column | — |
addTotal | Add a total | — |
deleteRows | Delete rows | — |
deleteColumns | Delete columns | — |
bold | Bold | — |
textColour | Text colour | — |
backgroundColour | Background | — |
defaultColour | Default colour | — |
noBackground | No background | — |
textSize | Size | — |
sizeSmall | Small | — |
sizeNormal | Normal | — |
sizeLarge | Large | — |
gray | Gray | — |
brown | Brown | — |
orange | Orange | — |
yellow | Yellow | — |
green | Green | — |
blue | Blue | — |
purple | Purple | — |
red | Red | — |
functions | Functions | — |
showFunctions | Show the functions | — |
suggestionKeys | ↑↓ choose · Tab insert | — |
pointHint | Click or drag over cells to add them | — |
result | Result | — |
argsSUM | value1, [value2, …] | — |
helpSUM | Adds up numbers and ranges. | — |
argsAVERAGE | value1, [value2, …] | — |
helpAVERAGE | The average of the numbers. | — |
argsCOUNT | value1, [value2, …] | — |
helpCOUNT | Counts the cells that hold a number. | — |
argsCOUNTA | value1, [value2, …] | — |
helpCOUNTA | Counts the cells that are not empty. | — |
argsMIN | value1, [value2, …] | — |
helpMIN | The smallest number. | — |
argsMAX | value1, [value2, …] | — |
helpMAX | The largest number. | — |
argsROUND | number, [digits] | — |
helpROUND | Rounds a number to a number of decimal places. | — |
argsABS | number | — |
helpABS | The number without its sign. | — |
argsIF | condition, then, [otherwise] | — |
helpIF | One value when the condition holds, another when it does not. | — |
argsIFERROR | value, fallback | — |
helpIFERROR | The value, or the fallback when the value is an error. | — |
argsSUMIF | range, condition, [sum_range] | — |
helpSUMIF | Adds the numbers whose cell meets a condition, such as ">100" or "Food". | — |
argsAND | condition1, [condition2, …] | — |
helpAND | TRUE when every condition holds. | — |
argsOR | condition1, [condition2, …] | — |
helpOR | TRUE when any condition holds. | — |
argsNOT | condition | — |
helpNOT | TRUE when the condition does not hold. | — |
argsCONCAT | text1, [text2, …] | — |
helpCONCAT | Joins texts together. | — |
argsLEN | text | — |
helpLEN | The number of characters in a text. | — |
argsUPPER | text | — |
helpUPPER | The text in capital letters. | — |
argsLOWER | text | — |
helpLOWER | The text in small letters. | — |
argsTRIM | text | — |
helpTRIM | The text without extra spaces. | — |
argsTODAY | — | — |
helpTODAY | Today’s date. | — |