Univer Sheets API

Univer Sheets provides professional-grade spreadsheet capabilities, with concepts designed to be as consistent as possible with Microsoft Excel.

Importing

import '@univerjs/sheets/facade'
import '@univerjs/sheets-ui/facade'

Workbook

A workbook contains multiple worksheets and can be thought of as an Excel file.

The unitId can be used as a unique identifier for the workbook.

Create a Workbook

The univerAPI.createWorkbook(data, options) method creates and returns the FWorkbook object.

The IWorkbookData is an object that contains the configuration information of the workbook.

const fWorkbook = univerAPI.createWorkbook({ id: 'Sheet1', name: 'Sheet1' })
console.log(fWorkbook)

Add you can make the workbook not as the active workbook by setting options:

const fWorkbook = univerAPI.createWorkbook({ id: 'Sheet1', name: 'Sheet1' }, { makeCurrent: false })
console.log(fWorkbook)

// If you want to switch to this workbook after 3 seconds
setTimeout(() => {
  univerAPI.setCurrent(fWorkbook.getId())
}, 3000)

Get Workbook Data

const fWorkbook = univerAPI.getActiveWorkbook()
const snapshot = fWorkbook.save()
console.log(snapshot)

Unload Workbook

When you no longer need a Workbook, you can call the disposeUnit method on the API instance to unload it.

const fWorkbook = univerAPI.getActiveWorkbook()
const unitId = fWorkbook?.getId()
if (unitId) {
  univerAPI.disposeUnit(unitId)
}

When the entire page hosting the Univer instance is destroyed or the route is unloaded, make sure to call the univer.dispose() method for cleanup, rather than using univerAPI.disposeUnit. Also, please note that univer and univerAPI are different instances. For instructions on how to obtain these instances, refer to the Installation & Basic Usage.

Get Workbook ID

const fWorkbook = univerAPI.getActiveWorkbook()
fWorkbook?.getId()

Worksheet

Worksheets store table data, worksheets belong to the workbook.

A workbook can contain multiple worksheets, and the names of worksheets in the same workbook cannot be duplicated.

The subUnitId can be used to uniquely identify a sheet in a workbook.

Get Worksheets

Get all sheets in a sheet

const fWorkbook = univerAPI.getActiveWorkbook()
const sheets = fWorkbook.getSheets()

Get Active Worksheet

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

Get Worksheet Data

const fWorkbook = univerAPI.getActiveWorkbook()
const fWorksheet = fWorkbook.getActiveSheet()
const sheetSnapshot = fWorksheet.getSheet().getSnapshot()

Create a Worksheet

The following example shows how to create a worksheet using the FWorkbook.create method.

const fWorkbook = univerAPI.getActiveWorkbook()

// Create a worksheet named 'Sheet2' with 10 rows and 10 columns
const newSheet = fWorkbook.create('Sheet2', 10, 10)

Remove Worksheet

Remove Worksheet by passing the worksheet instance or the worksheet Id

// Delete the second worksheet
const fWorkbook = univerAPI.getActiveWorkbook()
const sheet = fWorkbook.getSheets()[1]
fWorkbook.deleteSheet(sheet)

// The code below deletes the specified worksheet by id
// fWorkbook.deleteSheet(sheet.getSheetId());

Activate Worksheet

Activate the worksheet by passing the worksheet instance or the worksheet Id, or use FWorksheet.activate() method.

// Activate the second worksheet
const fWorkbook = univerAPI.getActiveWorkbook()
const sheet = fWorkbook.getSheets()[1]
fWorkbook.setActiveSheet(sheet)

// Activates the specified worksheet by id
// fWorkbook.setActiveSheet(sheet.getSheetId());

// Activate the worksheet using the FWorksheet.activate() method
// sheet.activate();

Get Worksheet ID

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

Refresh Worksheet

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

Worksheet Zoom

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

// Set zoom ratio to 200%
fWorksheet.zoom(2)
const zoomRatio = fWorksheet.getZoom()

Scroll to Cell

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

// Scroll to cell D10
const fRange = fWorksheet.getRange('D10')
const row = fRange.getRow()
const column = fRange.getColumn()
fWorksheet.scrollToCell(row, column)

// Add scroll animation duration
fWorksheet.scrollToCell(row, column, 1000)

// Get scroll state
const scrollState = fWorksheet.getScrollState()
const { offsetX, offsetY, sheetViewStartColumn, sheetViewStartRow } = scrollState
console.log(scrollState) // sheetViewStartRow: 9, sheetViewStartColumn: 3, offsetX: 0, offsetY: 0

Univer Sheets API

Importing

Workbook

Create a Workbook

Get Workbook Data

Unload Workbook

Get Workbook ID

Worksheet

Get Worksheets

Get Worksheet Data

Create a Worksheet

Remove Worksheet

Activate Worksheet

Get Worksheet ID

Refresh Worksheet

Worksheet Zoom

Scroll to Cell

Core Features

Permission

Formula

Row and Column

Range

Selection

Cell

Freeze

Integrating Custom Components into Univer

Reference

Table of Contents