Core Features

Range & Selection & Cell

GitHubEdit on GitHub

Range

A range refers to a rectangular area in a worksheet, determined by the starting row number, starting column number, length and width, or ending row number and ending column number.

Most operations in a spreadsheet can be performed through the Range API, such as setting values, getting values, setting styles, getting styles, etc.

Facade API

To get full definition of range related facade api, please refer to FRange FacadeAPI

Create a Range

To get a range you need to know the starting row number, starting column number, length and width.

const fWorkbook = univerAPI.getActiveWorkbook()
const fWorksheet = fWorkbook.getActiveSheet()

// Create a range of A1 cell:
const fRange = fWorksheet.getRange(0, 0)
console.log(fRange, fRange.getA1Notation())

// Creates a range of A1:B2:
const fRange2 = fWorksheet.getRange(0, 0, 2, 2)
console.log(fRange2, fRange2.getA1Notation())
const fWorkbook = univerAPI.getActiveWorkbook()
const fWorksheet = fWorkbook.getActiveSheet()

// Get the A1:B2 range on named Sheet1
const fRange1 = fWorksheet.getRange('Sheet1!A1:B2')
console.log(fRange1, fRange1.getA1Notation())

// Get a single cell A1
const fRange2 = fWorksheet.getRange('A1')
console.log(fRange2, fRange2.getA1Notation())

// Get the A1:B2 range
const fRange3 = fWorksheet.getRange('A1:B2')
console.log(fRange3, fRange3.getA1Notation())

// Get the range of column A
const fRange4 = fWorksheet.getRange('A:A')
console.log(fRange4, fRange4.getA1Notation())

// Get the range of row 1
const fRange5 = fWorksheet.getRange('1:1')
console.log(fRange5, fRange5.getA1Notation())

Get Range Data

Get the value of the first cell in the range

const fWorkbook = univerAPI.getActiveWorkbook()
const fWorksheet = fWorkbook.getActiveSheet()
const fRange = fWorksheet.getRange('A1:B2')

console.log(fRange.getValue()) // The value of the top-left cell in the range
console.log(fRange.getRawValue()) // The raw value of the top-left cell in the range
console.log(fRange.getDisplayValue()) // The displayed value of the top-left cell in the range
console.log(fRange.getCellData()) // The cell data of the top-left cell in the range
console.log(fRange.getRichTextValue()) // The rich text value of the top-left cell in the range
console.log(fRange.getRichTextValue().toPlainText()) // The plain text of the rich text value of the top-left cell in the range

Get all cell values in the range

const fWorkbook = univerAPI.getActiveWorkbook()
const fWorksheet = fWorkbook.getActiveSheet()
const fRange = fWorksheet.getRange('A1:B2')

console.log(fRange.getValues()) // The values of all cells in the range
console.log(fRange.getRawValues()) // The raw values of all cells in the range
console.log(fRange.getDisplayValues()) // The displayed values of all cells in the range
console.log(fRange.getCellDatas()) // The cell data of all cells in the range
console.log(fRange.getRichTextValues()) // The rich text values of all cells in the range

Get all formulas in the range

const fWorkbook = univerAPI.getActiveWorkbook()
const fWorksheet = fWorkbook.getActiveSheet()
const fRange = fWorksheet.getRange('A1:B2')

console.log(fRange.getFormula()) // The formula of the top-left cell in the range
console.log(fRange.getFormulas()) // The formulas of all cells in the range

Set Range Value

Set a single value

If a value or cell object is passed in, all cells in the range will be overwritten. If it starts with =, it will be interpreted as a formula.

For example, to set the value of A1:B2 to Hello, Univer:

const fWorkbook = univerAPI.getActiveWorkbook()
const fWorksheet = fWorkbook.getActiveSheet()
const fRange = fWorksheet.getRange('A1:B2')
fRange.setValue('Hello, Univer')

Set the value of A1+B1 to the formula:

const fWorkbook = univerAPI.getActiveWorkbook()
const fWorksheet = fWorkbook.getActiveSheet()
const fRange = fWorksheet.getRange('A1:B2')
fRange.setValue('=A1+B1')

Set the value of A1:B2 to the cell object:

const fWorkbook = univerAPI.getActiveWorkbook()
const fWorksheet = fWorkbook.getActiveSheet()
const fRange = fWorksheet.getRange('A1:B2')
fRange.setValue({
  v: 'Hello, Univer',
  custom: {
    key: 'value',
  },
})

If you only want to set the value of the top-left cell in the range, you can use the FRange.setValueForCell method:

fRange.setValueForCell('Hello, Univer')
Set multiple values with an array

The length and width of the array must match the length and width of the range.

You can pass in cell values or cell objects.

const fWorkbook = univerAPI.getActiveWorkbook()
const fWorksheet = fWorkbook.getActiveSheet()
const fRange = fWorksheet.getRange('A1:B2')
fRange.setValues([
  ['A1', 'B1'],
  ['A2', 'B2'],
])

fRange.setValues([
  [{ v: 'A1' }, { v: 'B1' }],
  [{ v: 'A2' }, { v: 'B2' }],
])
Set multiple values with an object

If an object is passed in, the primary index of the object represents the row number, and the secondary index represents the column number, and the length and width of the range do not need to match.

const fWorkbook = univerAPI.getActiveWorkbook()
const fWorksheet = fWorkbook.getActiveSheet()
const fRange = fWorksheet.getRange('A1:B2')
fRange.setValues({
  0: {
    0: 'A1',
    1: 'B1',
  },
  1: {
    0: 'A2',
    1: 'B2',
  },
})

Clear Range data

const fWorkbook = univerAPI.getActiveWorkbook()
const fWorkSheet = fWorkbook.getActiveSheet()
const fRange = fWorkSheet.getRange('A1:D10')

// Clears content and formatting information of the range.
fRange.clear()

Get Range Style

const fWorkbook = univerAPI.getActiveWorkbook()
const fWorksheet = fWorkbook.getActiveSheet()
const fRange = fWorksheet.getRange('A1:B2')
console.log(fRange.getCellStyleData()) // The style data of the top-left cell in the range
console.log(fRange.getCellStyle()) // The style of the top-left cell in the range
console.log(fRange.getCellStyles()) // The styles of all cells in the range

Set Range Style

const fWorkbook = univerAPI.getActiveWorkbook()
const fWorksheet = fWorkbook.getActiveSheet()
const fRange = fWorksheet.getRange('A1:B2')
fRange.setValues([
  [1, 2],
  [3, 4],
]).setFontWeight('bold').setFontLine('underline').setFontFamily('Arial').setFontSize(24).setFontColor('red')

Clear Range Style

const fWorkbook = univerAPI.getActiveWorkbook()
const fWorksheet = fWorkbook.getActiveSheet()
const fRange = fWorksheet.getRange('A1:B2')
fRange
  .setFontWeight(null)
  .setFontLine(null)
  .setFontFamily(null)
  .setFontSize(null)
  .setFontColor(null)

Insert Cells

FRange.insertCells method can inserts empty cells into this range. Existing data in the sheet along the provided dimension is shifted away from the inserted range.

// Assume the active sheet empty sheet.
const fWorkbook = univerAPI.getActiveWorkbook()
const fWorksheet = fWorkbook.getActiveSheet()
const values = [
  [1, 2, 3, 4],
  [2, 3, 4, 5],
  [3, 4, 5, 6],
  [4, 5, 6, 7],
  [5, 6, 7, 8],
]

// Set the range A1:D5 with some values, the range A1:D5 will be:
// 1 | 2 | 3 | 4
// 2 | 3 | 4 | 5
// 3 | 4 | 5 | 6
// 4 | 5 | 6 | 7
// 5 | 6 | 7 | 8
const fRange = fWorksheet.getRange('A1:D5')
fRange.setValues(values)
console.log(fWorksheet.getRange('A1:D5').getValues()) // [[1, 2, 3, 4], [2, 3, 4, 5], [3, 4, 5, 6], [4, 5, 6, 7], [5, 6, 7, 8]]

// Insert the empty cells into the range A1:B2 along the columns dimension, the range A1:D5 will be:
//   |   | 1 | 2
//   |   | 2 | 3
// 3 | 4 | 5 | 6
// 4 | 5 | 6 | 7
// 5 | 6 | 7 | 8
const fRange2 = fWorksheet.getRange('A1:B2')
fRange2.insertCells(univerAPI.Enum.Dimension.COLUMNS)
console.log(fWorksheet.getRange('A1:D5').getValues()) // [[null, null, 1, 2], [null, null, 2, 3], [3, 4, 5, 6], [4, 5, 6, 7], [5, 6, 7, 8]]

// Set the range A1:D5 values again, the range A1:D5 will be:
// 1 | 2 | 3 | 4
// 2 | 3 | 4 | 5
// 3 | 4 | 5 | 6
// 4 | 5 | 6 | 7
// 5 | 6 | 7 | 8
fRange.setValues(values)

// Insert the empty cells into the range A1:B2 along the rows dimension, the range A1:D5 will be:
//   |   | 3 | 4
//   |   | 4 | 5
// 1 | 2 | 5 | 6
// 2 | 3 | 6 | 7
// 3 | 4 | 7 | 8
const fRange3 = fWorksheet.getRange('A1:B2')
fRange3.insertCells(univerAPI.Enum.Dimension.ROWS)
console.log(fWorksheet.getRange('A1:D5').getValues()) // [[null, null, 3, 4], [null, null, 4, 5], [1, 2, 5, 6], [2, 3, 6, 7], [3, 4, 7, 8]]

Delete Cells

FRange.deleteCells method can deletes this range of cells. Existing data in the sheet along the provided dimension is shifted towards the deleted range.

// Assume the active sheet empty sheet.
const fWorkbook = univerAPI.getActiveWorkbook()
const fWorksheet = fWorkbook.getActiveSheet()
const values = [
  [1, 2, 3, 4],
  [2, 3, 4, 5],
  [3, 4, 5, 6],
  [4, 5, 6, 7],
  [5, 6, 7, 8],
]

// Set the range A1:D5 with some values, the range A1:D5 will be:
// 1 | 2 | 3 | 4
// 2 | 3 | 4 | 5
// 3 | 4 | 5 | 6
// 4 | 5 | 6 | 7
// 5 | 6 | 7 | 8
const fRange = fWorksheet.getRange('A1:D5')
fRange.setValues(values)
console.log(fWorksheet.getRange('A1:D5').getValues()) // [[1, 2, 3, 4], [2, 3, 4, 5], [3, 4, 5, 6], [4, 5, 6, 7], [5, 6, 7, 8]]

// Delete the range A1:B2 along the columns dimension, the range A1:D5 will be:
// 3 | 4 |   |
// 4 | 5 |   |
// 3 | 4 | 5 | 6
// 4 | 5 | 6 | 7
// 5 | 6 | 7 | 8
const fRange2 = fWorksheet.getRange('A1:B2')
fRange2.deleteCells(univerAPI.Enum.Dimension.COLUMNS)
console.log(fWorksheet.getRange('A1:D5').getValues()) // [[3, 4, null, null], [4, 5, null, null], [3, 4, 5, 6], [4, 5, 6, 7], [5, 6, 7, 8]]

// Set the range A1:D5 values again, the range A1:D5 will be:
// 1 | 2 | 3 | 4
// 2 | 3 | 4 | 5
// 3 | 4 | 5 | 6
// 4 | 5 | 6 | 7
// 5 | 6 | 7 | 8
fRange.setValues(values)

// Delete the range A1:B2 along the rows dimension, the range A1:D5 will be:
// 3 | 4 | 3 | 4
// 4 | 5 | 4 | 5
// 5 | 6 | 5 | 6
//   |   | 6 | 7
//   |   | 7 | 8
const fRange3 = fWorksheet.getRange('A1:B2')
fRange3.deleteCells(univerAPI.Enum.Dimension.ROWS)
console.log(fWorksheet.getRange('A1:D5').getValues()) // [[3, 4, 3, 4], [4, 5, 4, 5], [5, 6, 5, 6], [null, null, 6, 7], [null, null, 7, 8]]

Merge cells

const fWorkbook = univerAPI.getActiveWorkbook()
const fWorksheet = fWorkbook.getActiveSheet()

// Merge B1:B2
const fRange = fWorksheet.getRange('B1:B2')
fRange.merge()
console.log(fRange.isMerged()) // true

// A1:B2 is merged
const fRange2 = fWorksheet.getRange('A1:B2')
console.log(fRange2.isMerged()) // false
console.log(fRange2.isPartOfMerge()) // true

// Break apart the merged range
fRange2.breakApart()

// merge horizontally: fRange2.mergeAcross();
// merge vertically: fRange2.mergeVertically();
// Get all merged ranges in the worksheet: fWorksheet.getMergedRanges();

Highlight Range

FRange.highlight method can highlight the range with the specified style and primary cell.

const fWorkbook = univerAPI.getActiveWorkbook()
const fWorksheet = fWorkbook.getActiveSheet()

// Highlight the range C3:E5 with default style
const fRange = fWorksheet.getRange('C3:E5')
fRange.highlight()

// Highlight the range C7:E9 with custom style and primary cell D8
const fRange2 = fWorksheet.getRange('C7:E9')
const primaryCell = fWorksheet.getRange('D8').getRange()
const disposable = fRange2.highlight(
  {
    stroke: 'red',
    fill: 'yellow',
  },
  {
    ...primaryCell,
    actualRow: primaryCell.startRow,
    actualColumn: primaryCell.startColumn,
  },
)

// Remove the range C7:E9 highlight after 5 seconds
setTimeout(() => {
  disposable.dispose()
}, 5000)

Split text into columns

FRange.splitTextToColumns method can split the text in the range into columns accrording to the specified delimiter.

Default delimiter example:

const fWorkbook = univerAPI.getActiveWorkbook()
const fWorksheet = fWorkbook.getActiveSheet()

// A1:A3 has following values:
//  A      |
//  1,2,3  |
//  4,,5,6 |
const fRange = fWorksheet.getRange('A1:A3')
fRange.setValues([
  ['A'],
  ['1,2,3'],
  ['4,,5,6'],
])

// After calling splitTextToColumns(true), the range will be:
//  A |   |
//  1 | 2 | 3
//  4 | 5 | 6
fRange.splitTextToColumns(true)

// After calling splitTextToColumns(false), the range will be:
//  A |   |   |
//  1 | 2 | 3 |
//  4 |   | 5 | 6
fRange.splitTextToColumns(false)

Specified delimiter example:

const fWorkbook = univerAPI.getActiveWorkbook()
const fWorksheet = fWorkbook.getActiveSheet()

// A1:A3 has following values:
//     A   |
//  1;;2;3 |
//  1;,2;3 |
const fRange = fWorksheet.getRange('A1:A3')
fRange.setValues([
  ['A'],
  ['1;;2;3'],
  ['1;,2;3'],
])

// After calling splitTextToColumns(false, univerAPI.Enum.SplitDelimiterType.Semicolon|univerAPI.Enum.SplitDelimiterType.Comma), the range will be:
//  A |   |   |
//  1 |   | 2 | 3
//  1 |   | 2 | 3
fRange.splitTextToColumns(false, univerAPI.Enum.SplitDelimiterType.Semicolon | univerAPI.Enum.SplitDelimiterType.Comma)

// After calling splitTextToColumns(true, univerAPI.Enum.SplitDelimiterType.Semicolon|univerAPI.Enum.SplitDelimiterType.Comma), the range will be:
//  A |   |
//  1 | 2 | 3
//  1 | 2 | 3
fRange.splitTextToColumns(true, univerAPI.Enum.SplitDelimiterType.Semicolon | univerAPI.Enum.SplitDelimiterType.Comma)

Custom delimiter example:

const fWorkbook = univerAPI.getActiveWorkbook()
const fWorksheet = fWorkbook.getActiveSheet()

// A1:A3 has following values:
//     A   |
//  1#2#3  |
//  4##5#6 |
const fRange = fWorksheet.getRange('A1:A3')
fRange.setValues([
  ['A'],
  ['1#2#3'],
  ['4##5#6'],
])

// After calling splitTextToColumns(false, univerAPI.Enum.SplitDelimiterType.Custom, '#'), the range will be:
//  A |   |   |
//  1 | 2 | 3 |
//  4 |   | 5 | 6
fRange.splitTextToColumns(false, univerAPI.Enum.SplitDelimiterType.Custom, '#')

// After calling splitTextToColumns(true, univerAPI.Enum.SplitDelimiterType.Custom, '#'), the range will be:
//  A |   |
//  1 | 2 | 3
//  4 | 5 | 6
fRange.splitTextToColumns(true, univerAPI.Enum.SplitDelimiterType.Custom, '#')

Get the coordinates of the range

const fWorkbook = univerAPI.getActiveWorkbook()
const fWorksheet = fWorkbook.getActiveSheet()
const fRange = fWorksheet.getRange('A1:B2')
console.log(fRange.getCellRect()) // width、heigh、left、right、top、bottom、x、y

Get the merge information and coordinates of the range at the same time

const fWorkbook = univerAPI.getActiveWorkbook()
const fWorksheet = fWorkbook.getActiveSheet()
const fRange = fWorksheet.getRange('A1:B2')
console.log(fRange.getCell())

Selection

Univer Sheets support multiple constituencies, so a constituency is an array of ranges, and you can manipulate the constituency data through the range API.

We also provide APIs to get the current selection, set the selection, and listen for changes to the selection.

Facade API

To get full definition of selection related facade api, please refer to FSelection FacadeAPI

Get Active Selection

const fWorkbook = univerAPI.getActiveWorkbook()
const fWorksheet = fWorkbook.getActiveSheet()

// Activate the new selection of A1:B2
const fRange = fWorksheet.getRange('A1:B2')
fRange.activate()

// Get the current active selection
const fSelection = fWorksheet.getSelection()
console.log(fSelection)
const activeRange = fSelection.getActiveRange()
console.log(activeRange.getA1Notation()) // A1:B2

Set Selection

const fWorkbook = univerAPI.getActiveWorkbook()
const fWorksheet = fWorkbook.getActiveSheet()

// Set the selection to A1:B2 by FRange
const fRange = fWorksheet.getRange('A1:B2')
fRange.activate()

// Set the selection to C1:D2 by FWorksheet
fWorksheet.setActiveSelection(fWorksheet.getRange('C1:D2'))

Get Current Cell

const fWorkbook = univerAPI.getActiveWorkbook()
const fWorksheet = fWorkbook.getActiveSheet()

// Activate the new selection of A10:B11
const fRange = fWorksheet.getRange('A10:B11')
fRange.activate()

// Get the current active cell
let fSelection = fWorksheet.getSelection()
const { actualRow, actualColumn } = fSelection.getCurrentCell()
console.log(fWorksheet.getRange(actualRow, actualColumn).getA1Notation()) // A10

// Update the new active cell to B11
fSelection = fSelection.updatePrimaryCell(fWorksheet.getRange('B11'))
const { actualRow: newRow, actualColumn: newColumn } = fSelection.getCurrentCell()
console.log(fWorksheet.getRange(newRow, newColumn).getA1Notation()) // B11

Disable/Enable/Hide/Show Selection

After disabled, there would be no response for selection. Set selection invisible, Unlike disableSelection, selection still works, you just can not see them.

const fWorkbook = univerAPI.getActiveWorkbook()

// Disable selection
fWorkbook.disableSelection()

// Enable selection
fWorkbook.enableSelection()

// Hide selection
fWorkbook.transparentSelection()

// Show selection
fWorkbook.showSelection()

Listen for Selection Changes

univerAPI.addEvent(univerAPI.Event.SelectionChanged, (params) => {
  const { worksheet, workbook, selections } = params
  console.log(params)
})

Cell

Cell data is stored in the worksheet as a two-dimensional Map, with the first and second indexes representing the row number and column number respectively.

The following is a typical cell object:

const data = {
  v: 'Hello, Univer',
  s: 'styleId',
  t: CellValueType.STRING,
}

For detailed field descriptions, please refer to Configure Cell Data.

Good to know

  1. Cell operations can be regarded as operations on a range of rows and columns with a height of 1 and a width of 1. For the operation range, please refer to Range.
  2. The Univer API plugin will also store the extended cell attributes in the resources property of the Workbook, please refer to Plugin Custom Model.

Event List

Here is a list of available cell-related events:

Event NameDescriptionParameter TypeExample
CellPointerMoveTriggered when mouse moves over a cellICellEventParamconst { worksheet, workbook, row, column } = params
CellPointerDownTriggered when mouse button is pressedICellEventParamconst { worksheet, workbook, row, column } = params
CellPointerUpTriggered when mouse button is releasedICellEventParamconst { worksheet, workbook, row, column } = params
CellHoverTriggered when mouse hovers over a cellICellEventParamconst { worksheet, workbook, row, column } = params
DragOverTriggered when dragging over a cellICellEventParamconst { worksheet, workbook, row, column } = params
DropTriggered when dropping onto a cellICellEventParamconst { worksheet, workbook, row, column } = params
CellClickedTriggered when a cell is clickedICellEventParamconst { worksheet, workbook, row, column } = params
BeforeSheetEditStartTriggered before cell editing beginsIBeforeSheetEditStartEventParamsconst { worksheet, workbook, row, column, eventType, keycode, isZenEditor } = params
SheetEditStartedTriggered when cell editing startsISheetEditStartedEventParamsconst { worksheet, workbook, row, column, eventType, keycode, isZenEditor } = params
SheetEditChangingTriggered when cell content is being changedISheetEditChangingEventParamsconst { worksheet, workbook, row, column, value, isZenEditor } = params
BeforeSheetEditEndTriggered before cell editing endsIBeforeSheetEditEndEventParamsconst { worksheet, workbook, row, column, value, eventType, keycode, isZenEditor, isConfirm } = params
SheetEditEndedTriggered after cell editing endsISheetEditEndedEventParamsconst { worksheet, workbook, row, column, eventType, keycode, isZenEditor, isConfirm } = params

Usage Example

All events can be listened to using the addEvent method. The basic format is:

univerAPI.addEvent(univerAPI.Event.EventName, (params) => {
  // Event handling logic
})

Cell PointerMove Event

The CellPointerMove event is triggered when the mouse moves over a cell. This event provides information about the cell currently under the mouse pointer.

univerAPI.addEvent(univerAPI.Event.CellPointerMove, (params) => {
  // Get event parameters
  const { worksheet, workbook, row, column } = params
  console.log('Current cell position:', worksheet.getRange(row, column).getA1Notation())
})

Cell PointerDown Event

The CellPointerDown event is triggered when the mouse button is pressed on a cell.

univerAPI.addEvent(univerAPI.Event.CellPointerDown, (params) => {
  const { worksheet, workbook, row, column } = params
  console.log('Mouse down cell:', worksheet.getRange(row, column).getA1Notation())
})

Cell PointerUp Event

The CellPointerUp event is triggered when the mouse button is released on a cell.

univerAPI.addEvent(univerAPI.Event.CellPointerUp, (params) => {
  const { worksheet, workbook, row, column } = params
  console.log('Mouse up cell:', worksheet.getRange(row, column).getA1Notation())
})

Cell Hover Event

The CellHover event is triggered when the mouse hovers over a cell.

univerAPI.addEvent(univerAPI.Event.CellHover, (params) => {
  const { worksheet, workbook, row, column } = params
  console.log('Hovered cell:', worksheet.getRange(row, column).getA1Notation())
})

Cell DragOver Event

The DragOver event is triggered when dragging an element over a cell.

univerAPI.addEvent(univerAPI.Event.DragOver, (params) => {
  const { worksheet, workbook, row, column } = params
  console.log('Drag over cell:', worksheet.getRange(row, column).getA1Notation())
})

Cell Drop Event

The Drop event is triggered when dropping an element onto a cell.

univerAPI.addEvent(univerAPI.Event.Drop, (params) => {
  const { worksheet, workbook, row, column } = params
  console.log('Drop target cell:', worksheet.getRange(row, column).getA1Notation())
})

Cell Click Event

The CellClicked event is triggered when a cell is clicked.

univerAPI.addEvent(univerAPI.Event.CellClicked, (params) => {
  const { worksheet, workbook, row, column } = params
  console.log('Clicked cell:', worksheet.getRange(row, column).getA1Notation())
})

Cell Render Event

The onCellRender event is triggered during cell rendering and can be used for custom cell rendering.

Example 1: Fixed position rendering - row additions won't affect the render position

// Fixed position rendering
univerAPI.getSheetHooks().onCellRender([{
  drawWith: (ctx, info, skeleton, spreadsheets) => {
    const { row, col } = info
    // Render a checkmark at position (1,1)
    if (row === 1 && col === 1) {
      const { primaryWithCoord } = info
      const { startX, startY } = primaryWithCoord
      ctx.fillText('✅', startX, startY + 10)
    }
  },
}])

// Refresh canvas to apply rendering
univerAPI.getActiveWorkbook().getActiveSheet().refreshCanvas()

Example 2: Marker-based rendering - render position changes with row/column changes

// Set marker
univerAPI.getActiveWorkbook().getActiveSheet().getRange('B2').setValue({ custom: { key: 'needCheck' } })

// Render based on marker
univerAPI.getSheetHooks().onCellRender([{
  drawWith: (ctx, info, skeleton, spreadsheets) => {
    const { row, col, data } = info
    // Render checkmark at marked position
    if (data?.custom?.key === 'needCheck') {
      const { primaryWithCoord } = info
      const { startX, startY } = primaryWithCoord
      ctx.fillText('✅', startX, startY + 10)
    }
  },
}])

// Refresh canvas to apply rendering
univerAPI.getActiveWorkbook().getActiveSheet().refreshCanvas()

Cell Editing Events

Start Editing

const fWorkbook = univerAPI.getActiveWorkbook()
fWorkbook.startEditing()

End Editing

// Pass true to commit changes, false to cancel editing
// This is an async function, requires await
const fWorkbook = univerAPI.getActiveWorkbook()
await fWorkbook.endEditingAsync(true)

Start Editing Event

The BeforeSheetEditStart event is triggered before cell editing begins.

univerAPI.addEvent(univerAPI.Event.BeforeSheetEditStart, (params) => {
  const { worksheet, workbook, row, column, eventType, keycode, isZenEditor } = params
  console.log('Before cell edit:', params)

  // If you want to prevent entering the editing state
  params.cancel = true
})

The SheetEditStarted event is triggered when cell editing starts.

const disposable = univerAPI.addEvent(univerAPI.Event.SheetEditStarted, (params) => {
  const { worksheet, workbook, row, column, eventType, keycode, isZenEditor } = params
  console.log(params)
})

// Remove the event listener, use `disposable.dispose()`

End Editing Event

The BeforeSheetEditEnd event is triggered before cell editing ends.

const disposable = univerAPI.addEvent(univerAPI.Event.BeforeSheetEditEnd, (params) => {
  const { worksheet, workbook, row, column, value, eventType, keycode, isZenEditor, isConfirm } = params
  console.log(params)

  // Cancel the cell edit end operation
  params.cancel = true
})

// Remove the event listener, use `disposable.dispose()`

The SheetEditEnded event is triggered after cell editing ends.

univerAPI.addEvent(univerAPI.Event.SheetEditEnded, (params) => {
  const { worksheet, workbook, row, column, eventType, keycode, isZenEditor, isConfirm } = params
  console.log('After cell edit:', params)
})

Clipboard Events

BeforeClipboardChange

The BeforeClipboardChange event is triggered before the clipboard content changes. You can use this event to monitor or modify clipboard content before it changes.

univerAPI.addEvent(univerAPI.Event.BeforeClipboardChange, (params) => {
  const { text, html } = params
  console.log('Clipboard content:', text, html)
  // If you want to cancel the clipboard change
  // params.cancel = true;
})

BeforeClipboardPaste

The BeforeClipboardPaste event is triggered before content is pasted. You can use this event to monitor or modify content before it is pasted.

univerAPI.addEvent(univerAPI.Event.BeforeClipboardPaste, (params) => {
  const { text, html } = params
  console.log('Content to paste:', text, html)
  // If you want to cancel the paste operation
  // params.cancel = true;
})

ClipboardChanged

The ClipboardChanged event is triggered after the clipboard content has changed.

univerAPI.addEvent(univerAPI.Event.ClipboardChanged, (params) => {
  const { text, html } = params
  console.log('New clipboard content:', text, html)
})

ClipboardPasted

The ClipboardPasted event is triggered after content has been pasted.

univerAPI.addEvent(univerAPI.Event.ClipboardPasted, (params) => {
  const { text, html } = params
  console.log('Pasted content:', text, html)
})

Miscellaneous

How to get the data in the cell editor in real time?

The Univer Sheets cell editor is built on the Univer Docs editor engine, allowing you to use the Facade API to obtain the snapshot data of the currently active Univer Docs editor. This enables real-time retrieval and processing of the edited content.

univerAPI.onCommandExecuted((command) => {
  const { id } = command
  if (id === 'doc.command.insert-text' || id === 'doc.command.delete-text') {
    const doc = univerAPI.getActiveDocument()
    if (doc) {
      const snapshot = doc.getSnapshot()
      console.log(snapshot.body?.dataStream)
    }
  }
})

Reference: https://github.com/dream-num/univer/discussions/2261

When the cell editor is focused and an external button is clicked to trigger an event, the snapshot obtained does not contain the data of the focused cell.

The cell data is synchronized to the snapshot when the cell editor loses focus (i.e., when editing ends). Therefore, before attempting to obtain the snapshot data, ensure that the cell has lost focus. You can actively end editing as follows:

import '@univerjs/sheets-ui/facade'

$btn.addEventListener('click', () => {
  univerAPI.getActiveWorkbook().endEditingAsync(true)
})

Reference: https://github.com/dream-num/univer/issues/1314

Many APIs depend on the interface being fully rendered before they can function correctly. If you call related APIs immediately after creating a Workbook, they may not work due to the interface not being fully rendered yet. It is recommended to perform related operations in the Rendered or Steady stage of the lifecycle:

const disposable = univerAPI.addEvent(univerAPI.Event.LifeCycleChanged, ({ stage }) => {
  if (stage === univerAPI.Enum.LifecycleStages.Steady) {
    // code...
  }
})

// To remove the event listener, you can call `disposable.dispose()`

Reference: Life Cycle

Row & Column

Previous Page

Freeze

Next Page