Range & Selection & Cell

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.

TypeScript

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())

TypeScript

const fWorkbook = univerAPI.getActiveWorkbook()const fWorksheet = fWorkbook.getActiveSheet()// Get the A1:B2 range on named Sheet1const fRange1 = fWorksheet.getRange('Sheet1!A1:B2')console.log(fRange1, fRange1.getA1Notation())// Get a single cell A1const fRange2 = fWorksheet.getRange('A1')console.log(fRange2, fRange2.getA1Notation())// Get the A1:B2 rangeconst fRange3 = fWorksheet.getRange('A1:B2')console.log(fRange3, fRange3.getA1Notation())// Get the range of column Aconst fRange4 = fWorksheet.getRange('A:A')console.log(fRange4, fRange4.getA1Notation())// Get the range of row 1const fRange5 = fWorksheet.getRange('1:1')console.log(fRange5, fRange5.getA1Notation())

Get Range Data

Get the value of the first cell in the range

TypeScript

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 rangeconsole.log(fRange.getRawValue()) // The raw value of the top-left cell in the rangeconsole.log(fRange.getDisplayValue()) // The displayed value of the top-left cell in the rangeconsole.log(fRange.getCellData()) // The cell data of the top-left cell in the rangeconsole.log(fRange.getRichTextValue()) // The rich text value of the top-left cell in the rangeconsole.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

TypeScript

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 rangeconsole.log(fRange.getRawValues()) // The raw values of all cells in the rangeconsole.log(fRange.getDisplayValues()) // The displayed values of all cells in the rangeconsole.log(fRange.getCellDatas()) // The cell data of all cells in the rangeconsole.log(fRange.getRichTextValues()) // The rich text values of all cells in the range

Get all formulas in the range

TypeScript

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 rangeconsole.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:

TypeScript

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:

TypeScript

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:

TypeScript

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:

TypeScript

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.

TypeScript

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.

TypeScript

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

FRange.clear method can clear the content and format information of the range.
FRange.clearContent method can clear the content information of the range.
FRange.clearFormat method can clear the format information of the range.

TypeScript

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

TypeScript

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 rangeconsole.log(fRange.getCellStyle()) // The style of the top-left cell in the rangeconsole.log(fRange.getCellStyles()) // The styles of all cells in the range

Set Range Style

TypeScript

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

TypeScript

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.

TypeScript

// 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 | 8const 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 | 8const 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 | 8fRange.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 | 8const 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.

TypeScript

// 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 | 8const 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 | 8const 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 | 8fRange.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 | 8const 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

TypeScript

const fWorkbook = univerAPI.getActiveWorkbook()const fWorksheet = fWorkbook.getActiveSheet()// Merge B1:B2const fRange = fWorksheet.getRange('B1:B2')fRange.merge()console.log(fRange.isMerged()) // true// A1:B2 is mergedconst fRange2 = fWorksheet.getRange('A1:B2')console.log(fRange2.isMerged()) // falseconsole.log(fRange2.isPartOfMerge()) // true// Break apart the merged rangefRange2.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.

TypeScript

const fWorkbook = univerAPI.getActiveWorkbook()const fWorksheet = fWorkbook.getActiveSheet()// Highlight the range C3:E5 with default styleconst fRange = fWorksheet.getRange('C3:E5')fRange.highlight()// Highlight the range C7:E9 with custom style and primary cell D8const 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 secondssetTimeout(() => {  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:

TypeScript

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 | 6fRange.splitTextToColumns(true)// After calling splitTextToColumns(false), the range will be://  A |   |   |//  1 | 2 | 3 |//  4 |   | 5 | 6fRange.splitTextToColumns(false)

Specified delimiter example:

TypeScript

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 | 3fRange.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 | 3fRange.splitTextToColumns(true, univerAPI.Enum.SplitDelimiterType.Semicolon | univerAPI.Enum.SplitDelimiterType.Comma)

Custom delimiter example:

TypeScript

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 | 6fRange.splitTextToColumns(false, univerAPI.Enum.SplitDelimiterType.Custom, '#')// After calling splitTextToColumns(true, univerAPI.Enum.SplitDelimiterType.Custom, '#'), the range will be://  A |   |//  1 | 2 | 3//  4 | 5 | 6fRange.splitTextToColumns(true, univerAPI.Enum.SplitDelimiterType.Custom, '#')

Get the coordinates of the range

TypeScript

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

TypeScript

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

TypeScript

const fWorkbook = univerAPI.getActiveWorkbook()const fWorksheet = fWorkbook.getActiveSheet()// Activate the new selection of A1:B2const fRange = fWorksheet.getRange('A1:B2')fRange.activate()// Get the current active selectionconst fSelection = fWorksheet.getSelection()console.log(fSelection)const activeRange = fSelection.getActiveRange()console.log(activeRange.getA1Notation()) // A1:B2

Set Selection

TypeScript

const fWorkbook = univerAPI.getActiveWorkbook()const fWorksheet = fWorkbook.getActiveSheet()// Set the selection to A1:B2 by FRangeconst fRange = fWorksheet.getRange('A1:B2')fRange.activate()// Set the selection to C1:D2 by FWorksheetfWorksheet.setActiveSelection(fWorksheet.getRange('C1:D2'))

Get Current Cell

TypeScript

const fWorkbook = univerAPI.getActiveWorkbook()const fWorksheet = fWorkbook.getActiveSheet()// Activate the new selection of A10:B11const fRange = fWorksheet.getRange('A10:B11')fRange.activate()// Get the current active celllet fSelection = fWorksheet.getSelection()const { actualRow, actualColumn } = fSelection.getCurrentCell()console.log(fWorksheet.getRange(actualRow, actualColumn).getA1Notation()) // A10// Update the new active cell to B11fSelection = 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

When disabled, selection becomes unresponsive. hideSelection makes the selection invisible—unlike disableSelection, selection still works; you just cannot see it.

TypeScript

const fWorkbook = univerAPI.getActiveWorkbook()// Disable selectionfWorkbook.disableSelection()// Enable selectionfWorkbook.enableSelection()// Hide selectionfWorkbook.transparentSelection()// Show selectionfWorkbook.showSelection()

Listen for Selection Changes

TypeScript

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:

TypeScript

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

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

Good to know

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.
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 Name	Description	Parameter Type	Example
`CellPointerMove`	Triggered when mouse moves over a cell	`ICellEventParam`	`const { worksheet, workbook, row, column } = params`
`CellPointerDown`	Triggered when mouse button is pressed	`ICellEventParam`	`const { worksheet, workbook, row, column } = params`
`CellPointerUp`	Triggered when mouse button is released	`ICellEventParam`	`const { worksheet, workbook, row, column } = params`
`CellHover`	Triggered when mouse hovers over a cell	`ICellEventParam`	`const { worksheet, workbook, row, column } = params`
`DragOver`	Triggered when dragging over a cell	`ICellEventParam`	`const { worksheet, workbook, row, column } = params`
`Drop`	Triggered when dropping onto a cell	`ICellEventParam`	`const { worksheet, workbook, row, column } = params`
`CellClicked`	Triggered when a cell is clicked	`ICellEventParam`	`const { worksheet, workbook, row, column } = params`
`BeforeSheetEditStart`	Triggered before cell editing begins	`IBeforeSheetEditStartEventParams`	`const { worksheet, workbook, row, column, eventType, keycode } = params`
`SheetEditStarted`	Triggered when cell editing starts	`ISheetEditStartedEventParams`	`const { worksheet, workbook, row, column, eventType, keycode } = params`
`SheetEditChanging`	Triggered when cell content is being changed	`ISheetEditChangingEventParams`	`const { worksheet, workbook, row, column, value } = params`
`BeforeSheetEditEnd`	Triggered before cell editing ends	`IBeforeSheetEditEndEventParams`	`const { worksheet, workbook, row, column, value, eventType, keycode, isConfirm } = params`
`SheetEditEnded`	Triggered after cell editing ends	`ISheetEditEndedEventParams`	`const { worksheet, workbook, row, column, eventType, keycode, isConfirm } = params`

Usage Example

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

TypeScript

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.

TypeScript

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.

TypeScript

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.

TypeScript

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.

TypeScript

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.

TypeScript

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.

TypeScript

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.

TypeScript

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

Custom Cell Rendering

registerCellCustomRender installs custom renderers used during cell rendering.

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

TypeScript

// Fixed position renderinguniverAPI.registerCellCustomRender([  {    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 renderinguniverAPI.getActiveWorkbook().getActiveSheet().refreshCanvas()

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

TypeScript

// Set markeruniverAPI  .getActiveWorkbook()  .getActiveSheet()  .getRange('B2')  .setValue({ custom: { key: 'needCheck' } })// Render based on markeruniverAPI.registerCellCustomRender([  {    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 renderinguniverAPI.getActiveWorkbook().getActiveSheet().refreshCanvas()

Cell Editing Events

Start Editing

TypeScript

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

End Editing

TypeScript

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

Start Editing Event

The BeforeSheetEditStart event is triggered before cell editing begins.

TypeScript

univerAPI.addEvent(univerAPI.Event.BeforeSheetEditStart, (params) => {  const { worksheet, workbook, row, column, eventType, keycode } = 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.

TypeScript

const disposable = univerAPI.addEvent(univerAPI.Event.SheetEditStarted, (params) => {  const { worksheet, workbook, row, column, eventType, keycode } = params  console.log(params)})// Remove the event listener, use `disposable.dispose()`

End Editing Event

The BeforeSheetEditEnd event is triggered before cell editing ends.

TypeScript

const disposable = univerAPI.addEvent(univerAPI.Event.BeforeSheetEditEnd, (params) => {  const { worksheet, workbook, row, column, value, eventType, keycode, 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.

TypeScript

univerAPI.addEvent(univerAPI.Event.SheetEditEnded, (params) => {  const { worksheet, workbook, row, column, eventType, keycode, 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.

TypeScript

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.

TypeScript

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.

TypeScript

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.

TypeScript

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

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.

TypeScript

univerAPI.addEvent(univerAPI.Event.CommandExecuted, ({ id }) => {  if (id === 'doc.command.insert-text' || id === 'doc.command.delete-text') {    const doc = univerAPI.getActiveDocument()    if (doc) {      const snapshot = doc.save()      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:

TypeScript

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:

TypeScript

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: Lifecycle

How is this guide?