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

Create a Range

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


const sheet = univerAPI.getActiveWorkbook().getActiveSheet();
 
// Create a range of A1 cell:
const range = sheet.getRange(0, 0, 1, 1);
 
// Creates a range of A1:B2:
const range2 = sheet.getRange(0, 0, 2, 2);

Starting from 0.2.15, getRange supports using A1 notation to get ranges.


const sheet = univerAPI.getActiveWorkbook().getActiveSheet();
// Get the A1:B2 range on named Sheet1
const range1 = sheet.getRange('Sheet1!A1:B2');
 
// Get a single cell A1
const range2 = sheet.getRange('A1');
 
// Get the A1:B2 range
const range3 = sheet.getRange('A1:B2');
 
// Get the range of column A
const range4 = sheet.getRange('A:A');
 
// Get the range of row 1
const range5 = sheet.getRange('1:1');

Get Range Data

Get the value of the first cell in the range


const sheet = univerAPI.getActiveWorkbook().getActiveSheet();
 
const range = sheet.getRange(0, 0, 2, 2);
const value = range.getValue();

Get all cell values in the range


const sheet = univerAPI.getActiveWorkbook().getActiveSheet();
 
const range = sheet.getRange(0, 0, 2, 2);
range.forEach(( row, column, cell) => {
  console.log( row, column, cell);
});

Get all formulas in the range


const sheet = univerAPI.getActiveWorkbook().getActiveSheet();
 
const range = sheet.getRange(0, 0, 2, 2);
console.log(range.getFormulas());

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 sheet = univerAPI.getActiveWorkbook().getActiveSheet();
 
const range = sheet.getRange(0, 0, 2, 2);
range.setValue('Hello, Univer');

Set the value of A1+B1 to the formula:


const sheet = univerAPI.getActiveWorkbook().getActiveSheet();
 
const range = sheet.getRange(0, 0, 2, 2);
range.setValue('=A1+B1');

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


const sheet = univerAPI.getActiveWorkbook().getActiveSheet();
 
const range = sheet.getRange(0, 0, 2, 2);
range.setValue({
  v: 'Hello, Univer',
  custom: {
      key: 'value',
  },
});

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 sheet = univerAPI.getActiveWorkbook().getActiveSheet();
 
const range = sheet.getRange(0, 0, 2, 2);
range.setValues([
  ['A1', 'B1'],
  ['A2', 'B2'],
]);
 
range.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 sheet = univerAPI.getActiveWorkbook().getActiveSheet();
 
const range = sheet.getRange(0, 0, 2, 2);
range.setValues({
  0: {
    0: 'A1',
    1: 'B1',
  },
  1: {
    0: 'A2',
    1: 'B2',
  },
});

Get Range Style


const sheet = univerAPI.getActiveWorkbook().getActiveSheet();
 
const range = sheet.getRange(0, 0, 2, 2);
const style = range.getCellStyleData();

Set Range Style


const sheet = univerAPI.getActiveWorkbook().getActiveSheet();
 
const range = sheet.getRange(0, 0, 2, 2);
range
  .setFontWeight('bold')
  .setFontLine('underline')
  .setFontFamily('Arial')
  .setFontSize(24)
  .setFontColor('red');

Clear Range Style


const sheet = univerAPI.getActiveWorkbook().getActiveSheet();
 
const range = sheet.getRange(0, 0, 2, 2);
range
  .setFontWeight(null)
  .setFontLine(null)
  .setFontFamily(null)
  .setFontSize(null)
  .setFontColor(null);

Whether the range is merged


const sheet = univerAPI.getActiveWorkbook().getActiveSheet();
 
const range = sheet.getRange(0, 0, 2, 2);
const isMerged = range.isMerged();

Get the coordinates of the range


const sheet = univerAPI.getActiveWorkbook().getActiveSheet();
const range = sheet.getRange(0, 0, 2, 2);
 
univerAPI.getHooks().onRendered(() => {
  const rect = range.getCellRect(); // width、heigh、left、right、top、bottom、x、y
  console.log(rect);
});

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


const sheet = univerAPI.getActiveWorkbook().getActiveSheet();
const range = sheet.getRange(0, 0, 2, 2);
 
univerAPI.getHooks().onRendered(() => {
  const cell = range.getCell();
  console.log(cell);
});

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

Get Active Selection


const sheet = univerAPI.getActiveWorkbook().getActiveSheet();\
 
univerAPI.getHooks().onRendered(() => {
  const selection = sheet.getSelection();
  console.log(selection);
 
  const range = selection.getActiveRange();
  console.log(range);
});

Set Selection

Set A1:B2 as the selection


import { SetSelectionsOperation } from '@univerjs/sheets'
 
univerAPI.executeCommand(SetSelectionsOperation.id, {
  unitId,
  subUnitId,
  selections: [{
    range: {
      startRow: 0,
      startColumn: 0,
      endRow: 1,
      endColumn: 1,
      rangeType: 0,
    },
  }],
  type: 2,
})

Listen for Selection Changes


const activeWorkbook = univerAPI.getActiveWorkbook();
activeWorkbook.onSelectionChange((selection) => {
  console.log(selection);
});

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:


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

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

ℹ️

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, isZenEditor } = params`
`SheetEditStarted`	Triggered when cell editing starts	`ISheetEditStartedEventParams`	`const { worksheet, workbook, row, column, eventType, keycode, isZenEditor } = params`
`SheetEditChanging`	Triggered when cell content is being changed	`ISheetEditChangingEventParams`	`const { worksheet, workbook, row, column, value, isZenEditor } = params`
`BeforeSheetEditEnd`	Triggered before cell editing ends	`IBeforeSheetEditEndEventParams`	`const { worksheet, workbook, row, column, value, eventType, keycode, isZenEditor, isConfirm } = params`
`SheetEditEnded`	Triggered after cell editing ends	`ISheetEditEndedEventParams`	`const { 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:', row, column);
})

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:', row, column);
})

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:', row, column);
})

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:', row, column);
})

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:', row, column);
})

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:', row, column);
})

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:', row, column);
})

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 workbook = univerAPI.getActiveWorkbook();
 
// Start editing after rendering is complete
univerAPI.addEvent(univerAPI.Event.Rendered, () => {
    workbook.startEditing();
});

End Editing


// Pass true to commit changes, false to cancel editing
// This is an async function, requires await
await workbook.endEditing(true);

Before Edit 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);
})

After Edit Event

The SheetEditEnded event is triggered after cell editing is completed.


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, (param) => {
    const {text, html} = param;
    console.log('Clipboard content:', text, html);
    // If you want to cancel the clipboard change
    // param.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, (param) => {
    const {text, html} = param;
    console.log('Content to paste:', text, html);
    // If you want to cancel the paste operation
    // param.cancel = true;
});

ClipboardChanged

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


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

ClipboardPasted

The ClipboardPasted event is triggered after content has been pasted.


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