sheet features

Per-sheet features including borders, hyperlinks, data validation, conditional formatting, and frozen panes.

Borders

Borders are stored per-cell in the borders map:

Sub-type Yjs Type Key Format
borders Y.Map<BorderInfo> "${rowId}_${colId}"

BorderInfo Structure

interface BorderInfo {
    top?: BorderEdge
    right?: BorderEdge
    bottom?: BorderEdge
    left?: BorderEdge
}

interface BorderEdge {
    style: number    // Border style code
    color: string    // Border color (hex)
}

Each cell can have independent borders on all four edges. Only edges with explicit borders are stored.

Border Style Codes

Code Style
1 Thin
2 Medium
3 Thick
4 Dashed
5 Dotted
6 Double

Example

// Set a thick red bottom border on a cell
borders.set(`${rowId}_${colId}`, {
    bottom: { style: 3, color: '#ff0000' }
})
Adjacent cell borders

Border rendering between adjacent cells follows the convention that each cell owns its own border edges. When two adjacent cells define conflicting borders on the shared edge (e.g. cell A’s right border vs cell B’s left border), the application layer resolves which to display.

Hyperlinks are stored per-cell in the hyperlinks map:

Sub-type Yjs Type Key Format
hyperlinks Y.Map<HyperlinkInfo> "${rowId}_${colId}"

HyperlinkInfo Structure

interface HyperlinkInfo {
    type: 'external' | 'internal' | 'email'
    address: string    // URL, cell reference, or email address
    tooltip?: string   // Hover tooltip text
}
Type Address Format Example
external Full URL "https://example.com"
internal Cell or sheet reference "Sheet2!A1"
email Email address "user@example.com"

Example

hyperlinks.set(`${rowId}_${colId}`, {
    type: 'external',
    address: 'https://docs.cloudillo.org',
    tooltip: 'Cloudillo Documentation'
})

Data Validation

Validation rules are stored in the validations map, keyed by unique validation IDs:

Sub-type Yjs Type Key Format
validations Y.Map<ValidationRule> Unique validation ID

ValidationRule Structure

interface ValidationRule {
    ranges: Array<{
        startRow: RowId
        endRow: RowId
        startCol: ColId
        endCol: ColId
    }>
    type: 'dropdown' | 'checkbox' | 'number' | 'date' | 'text'
    options?: ValidationOptions
}

Validation Types

Type Description Options
dropdown Dropdown list selection values: string[] – allowed values
checkbox Boolean checkbox checkedValue?: string, uncheckedValue?: string
number Numeric constraint operator, value1, value2 (for between)
date Date constraint operator, value1, value2 (for between)
text Text constraint operator, value1

Operators

Number and date validations support:

Operator Description
between Value between value1 and value2
notBetween Value not between value1 and value2
equal Value equals value1
notEqual Value not equal to value1
greaterThan Value greater than value1
lessThan Value less than value1
greaterThanOrEqual Value >= value1
lessThanOrEqual Value <= value1

Example

validations.set(validationId, {
    ranges: [{
        startRow: rowId1,
        endRow: rowId10,
        startCol: colIdB,
        endCol: colIdB
    }],
    type: 'dropdown',
    options: {
        values: ['Active', 'Inactive', 'Pending']
    }
})
Range-based rules

Unlike borders and hyperlinks which are per-cell, validation rules apply to rectangular ranges. A single rule can cover multiple cells, avoiding duplication when the same constraint applies to an entire column or region.

Conditional Formatting

Conditional formatting rules are stored as an ordered array:

Sub-type Yjs Type Description
conditionalFormats Y.Array<ConditionalFormat> Rules evaluated in order (first match wins)

ConditionalFormat Structure

interface ConditionalFormat {
    ranges: Array<{
        startRow: RowId
        endRow: RowId
        startCol: ColId
        endCol: ColId
    }>
    type: 'cellValue' | 'colorScale' | 'dataBar' | 'iconSet'
    rule: ConditionalRule
    format?: CellFormat
}

Rule Types

Cell Value Rules (type: 'cellValue')

Apply formatting when cells meet a condition:

{
    "type": "cellValue",
    "rule": {
        "operator": "greaterThan",
        "value1": 100
    },
    "format": {
        "bg": "#c8e6c9",
        "fc": "#2e7d32"
    }
}

Color Scale Rules (type: 'colorScale')

Apply a gradient color scale across cell values:

{
    "type": "colorScale",
    "rule": {
        "minColor": "#f44336",
        "midColor": "#ffeb3b",
        "maxColor": "#4caf50"
    }
}

Data Bar Rules (type: 'dataBar')

Show proportional bars within cells:

{
    "type": "dataBar",
    "rule": {
        "color": "#2196f3"
    }
}

Icon Set Rules (type: 'iconSet')

Display icons based on value thresholds:

{
    "type": "iconSet",
    "rule": {
        "iconType": "arrows3",
        "thresholds": [33, 67]
    }
}

Frozen Panes

Freeze pane settings are stored in the per-sheet frozen map:

Sub-type Yjs Type Description
frozen Y.Map<string|number> Freeze pane configuration

Frozen Map Keys

Key Type Description
type string Freeze type: "row", "column", "both", or "range"
rowIndex number Number of frozen rows (from top)
colIndex number Number of frozen columns (from left)

Freeze Types

Type Description
row Freeze rows above the focus row
column Freeze columns to the left of the focus column
both Freeze both rows and columns
range Freeze a specific range (uses both rowIndex and colIndex)

Example

// Freeze the first 2 rows and first column
frozen.set('type', 'both')
frozen.set('rowIndex', 2)
frozen.set('colIndex', 1)

// Remove freeze
frozen.delete('type')
frozen.delete('rowIndex')
frozen.delete('colIndex')