Univer Sheet API
Concepts
Univer table-related concepts are designed to be as consistent as possible with Excel.
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 univer.createUnit(UniverInstanceType.UNIVER_SHEET, {})
method creates and returns the Workbook
object.
Getting Workbook Data
const univerAPI = FUniver.newAPI(univer);
const activeWorkbook = univerAPI.getActiveWorkbook()
const saveData = activeWorkbook.getSnapshot();
Unload Workbook
When you no longer need a Workbook
, you can call the disposeUnit
method on the API instance to unload it.
univerAPI.disposeUnit('your-sheet-id')
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 activeWorkbook = univerAPI.getActiveWorkbook();
const sheets = activeWorkbook.getSnapshot().sheets;
Get Active Worksheet
const activeWorkbook = univerAPI.getActiveWorkbook();
const activeSheet = activeWorkbook.getActiveSheet();
Get Worksheet Data
const activeWorkbook = univerAPI.getActiveWorkbook();
const snapshot = activeWorkbook.getSnapshot()
const sheet1 = Object.values(snapshot.sheets).find((sheet) => {
return sheet.name === 'Sheet1'
})
Create a Worksheet
When creating a workbook, if no parameters are passed, a worksheet will be automatically created.
The following example shows how to create a worksheet using the Workbook.create
method.
Remove Worksheet
Remove Worksheet by worksheet id
const sheetId = 'SheetId';
univerAPI.executeCommand('sheet.command.remove-sheet', { subUnitId: sheetId });
Activate Worksheet
To activate a worksheet, you need to know the workbook id and the sheet id.
const workbookId = 'WorkbookId';
const sheetId = 'SheetId';
univerAPI.executeCommand('sheet.command.active-sheet', { unitId: workbookId, subUnitId: sheetId });
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.
Cell PointerMove
The onCellPointerMove
event is fired when a pointer changes coordinates.
univerAPI.getSheetHooks().onCellPointerMove((cell) => {
// Get the cell currently pointed to by the mouse
console.log(cell);
})
Cell PointerOver
The onCellPointerOver
event is fired when a pointer is moved into a cell's hit test boundaries.
univerAPI.getSheetHooks().onCellPointerOver((cell) => {
// Get the cell currently pointed to by the mouse
console.log(cell);
})
Cell DragOver
The onCellDragOver
event is fired when an element or text selection is being dragged into a cell's hit test boundaries.
univerAPI.getSheetHooks().onCellDragOver((cell) => {
// Get the cell currently pointed to by the mouse
console.log(cell);
})
Cell Drop
The onCellDrop
event is fired when an element or text selection is being dropped on the cell.
univerAPI.getSheetHooks().onCellDrop((cell) => {
// Get the cell currently pointed to by the mouse
console.log(cell);
})
Cell Enters Editing
univerAPI.onCommandExecuted((command) => {
if(command.id === 'sheet.operation.set-cell-edit-visible' && command.params.visible){
console.log('Cell edit visible')
}
})
Cell Exits Editing
univerAPI.onCommandExecuted((command) => {
if(command.id === 'sheet.operation.set-cell-edit-visible' && !command.params.visible){
console.log('Cell edit invisible')
}
})
Range
A range refers to a rectangular area in a worksheet, which is determined by the starting row number, starting column number, length, and width, or ending row number, ending column number.
Create a Range
To get a range you need to know the starting row number, starting column number, length and width.
Create a range of A1 cells:
const activeSheet = univerAPI.getActiveWorkbook().getActiveSheet();
const range = activeSheet.getRange(0, 0, 1, 1);
Creates a range of A1:B2:
const activeSheet = univerAPI.getActiveWorkbook().getActiveSheet();
const range = activeSheet.getRange(0, 0, 2, 2);
Get Range Data
Get the value of the first cell in the range
const range = activeSheet.getRange(0, 0, 2, 2);
const value = range.getValue();
Get all cell values in the range
const range = activeSheet.getRange(0, 0, 2, 2);
range.forEach((cell, row, column) => {
console.log(cell.getValue());
});
Get all formulas in the range
const range = activeSheet.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 range = activeSheet.getRange(0, 0, 2, 2);
range.setValue('Hello, Univer');
Set the value of A1+B1 to the formula:
const range = activeSheet.getRange(0, 0, 2, 2);
range.setValue('=A1+B1');
Set the value of A1:B2 to the cell object:
const range = activeSheet.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 range = activeSheet.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 range = activeSheet.getRange(0, 0, 2, 2);
range.setValues({
0: {
0: 'A1',
1: 'B1',
},
1: {
0: 'A2',
1: 'B2',
},
});
Get Range Style
const range = activeSheet.getRange(0, 0, 2, 2);
const style = range.getCellStyleData();
Set Range Style
const range = activeSheet.getRange(0, 0, 2, 2);
range
.setFontWeight('bold')
.setFontLine('underline')
.setFontFamily('Arial')
.setFontSize(24)
.setFontColor('red');
Clear Range Style
const range = activeSheet.getRange(0, 0, 2, 2);
range
.setFontWeight(null)
.setFontLine(null)
.setFontFamily(null)
.setFontSize(null)
.setFontColor(null);
Whether the range is merged
const range = activeSheet.getRange(0, 0, 2, 2);
const isMerged = range.isMerged();
Get the coordinates of the range
const range = activeSheet.getRange(0, 0, 2, 2);
const rect = range.getCellRect(); // width、heigh、left、right、top、bottom、x、y
Get the merge information and coordinates of the range at the same time
const range = activeSheet.getRange(0, 0, 2, 2);
const cell = range.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.
Get Active Selection
const activeSheet = univerAPI.getActiveWorkbook().getActiveSheet();
const selection = activeSheet.getSelection();
const range = selection.getActiveRange();
Set Selection
Set A1:B2 as the selection
const sheetId = 'SheetId';
univerAPI.executeCommand('sheet.operation.set-selections', {
selections: [{
range: {
startRow: 0,
startColumn: 0,
endRow: 1,
endColumn: 1,
rangeType: 0,
},
}],
subUnitId,
unitId: activeWorkbook.getId(),
type: 2,
})
Listen for Selection Changes
const activeWorkbook = univerAPI.getActiveWorkbook();
activeWorkbook.onSelectionChange((selection) => {
console.log(selection);
});
Server-Related Features
This feature depends on the Univer backend service. Please make sure you have read the related documentation and completed the deployment before using it.
Import XLSX
Import XLSX and Get unitId
In a collaborative environment, each workbook has a unique unitId
. Use the API importXLSXToUnitId
to pass in the file
parameter and return unitId
, which can be used to access the workbook. The file
parameter can be a File object or a URL to a remote file.
univerAPI.importXLSXToUnitId(file).then((id)=>{
console.log(id)
})
Import XLSX and Get Workbook Data
When using the import XLSX capability separately in a non-collaborative environment, you can use the API importXLSXToSnapshot
to return the workbook data in the IWorkbookData format.
univerAPI.importXLSXToSnapshot(file).then((data)=>{
console.log(data)
})
Export XLSX
Export XLSX via unitId
Use the API exportXLSXByUnitId
to pass in the unitId
parameter and return a File object.
univerAPI.exportXLSXByUnitId(unitId).then((file)=>{
console.log(file)
})
Export XLSX via Workbook Data
Use the API exportXLSXBySnapshot
to pass in the table data in the IWorkbookData
format and return a File object.
You can refer to Getting Workbook Data to get the data in the IWorkbookData
format.
univerAPI.exportXLSXBySnapshot(snapshot).then((file)=>{
console.log(file)
})
Reference
Please refer to the following API documentation for more information: