Row & Column
Facade API
Complete Facade API type definitions can be found in the Facade API.
Row
Insert row after given position
insertRowAfter(afterPosition): Inserts a row after the specified row position.
const sheet = univerAPI.getActiveWorkbook().getActiveSheet()// This inserts a row after the first row positionsheet.insertRowAfter(0)Insert row before given position
insertRowBefore(beforePosition): Inserts a row before the specified row position.
const sheet = univerAPI.getActiveWorkbook().getActiveSheet()// This inserts a row before the first row positionsheet.insertRowBefore(0)Insert multiple rows at specified location
insertRows(rowIndex, numRows): Inserts one or more consecutive blank rows in a sheet starting at the specified location.
const sheet = univerAPI.getActiveWorkbook().getActiveSheet()// Shifts all rows down by threesheet.insertRows(0, 3)Insert multiple rows after given position
insertRowsAfter(afterPosition, howMany): Inserts a specified number of rows after the given row position.
const sheet = univerAPI.getActiveWorkbook().getActiveSheet()// This inserts five rows after the first rowsheet.insertRowsAfter(0, 5)Insert multiple rows before given position
insertRowsBefore(beforePosition, howMany): Inserts a specified number of rows before the given row position.
const sheet = univerAPI.getActiveWorkbook().getActiveSheet()// This inserts five rows before the first rowsheet.insertRowsBefore(0, 5)Delete row at given position
deleteRow(rowPosition): Deletes the row at the specified row position.
const sheet = univerAPI.getActiveWorkbook().getActiveSheet()// Rows start at "0" - this deletes the first rowsheet.deleteRow(0)Delete multiple rows starting at given position
deleteRows(rowPosition, howMany): Deletes a specified number of rows starting at the given row position.
const sheet = univerAPI.getActiveWorkbook().getActiveSheet()// Rows start at "0" - this deletes the first two rowssheet.deleteRows(0, 2)Delete multiple rows by specified positions
deleteRowsByPoints(rowPoints): Deletes multiple rows (not necessarily consecutive) specified by an array of row positions.
const sheet = univerAPI.getActiveWorkbook().getActiveSheet()// Rows start at "0" - this deletes the 3rd row and rows 5 to 7fWorksheet.deleteRowsByPoints([2, [4, 6]])Move rows to new position
moveRows(rowSpec, destinationIndex): Moves the rows selected by the given range to the position indicated by the destinationIndex. The rowSpec itself does not have to exactly represent an entire row or group of rows to move—it selects all rows that the range spans.
// The code below moves rows 1-2 to destination index 5// This results in those rows becoming rows 3-4const sheet = univerAPI.getActiveWorkbook().getActiveSheet()// Selects row 1 and row 2 to be movedconst rowSpec = sheet.getRange('1:2')sheet.moveRows(rowSpec, 5)Hide rows in given range
hideRow(row): Hides the rows in the specified range.
const sheet = univerAPI.getActiveWorkbook().getActiveSheet()// This hides the first rowconst range = sheet.getRange('1:1')sheet.hideRow(range)Hide multiple consecutive rows
hideRows(rowIndex, numRows): Hides one or more consecutive rows starting at the given index. Use 0-index for this method.
const sheet = univerAPI.getActiveWorkbook().getActiveSheet()// Hides the first three rowssheet.hideRows(0, 3)Unhide row in given range
unhideRow(row): Unhides the row in the specified range.
const sheet = univerAPI.getActiveWorkbook().getActiveSheet()// This unhides the first row if it was previously hiddenconst range = sheet.getRange('1:1')sheet.unhideRow(range)Unhide multiple consecutive rows
showRows(rowIndex, numRows): Unhides one or more consecutive rows starting at the given index. Use 0-index for this method.
const sheet = univerAPI.getActiveWorkbook().getActiveSheet()// Unhides the first three rowssheet.showRows(0, 3)Set row height
setRowHeight(rowPosition, height): Sets the row height of the given row in pixels. By default, rows grow to fit cell contents. If you want to force rows to a specified height, use setRowHeightsForced(startRow, numRows, height).
const sheet = univerAPI.getActiveWorkbook().getActiveSheet()// Sets the first row to a height of 200 pixelssheet.setRowHeight(0, 200)Set height for multiple rows
setRowHeights(startRow, numRows, height): Sets the height of the given rows in pixels. By default, rows grow to fit cell contents. If you want to force rows to a specified height, use setRowHeightsForced(startRow, numRows, height).
const sheet = univerAPI.getActiveWorkbook().getActiveSheet()// Sets the first three rows to a height of 200 pixelssheet.setRowHeights(0, 3, 200)Force set height for multiple rows
setRowHeightsForced(startRow, numRows, height): Sets the height of the given rows in pixels. By default, rows grow to fit cell contents. When you use setRowHeightsForced, rows are forced to the specified height even if the cell contents are taller than the row height.
const sheet = univerAPI.getActiveWorkbook().getActiveSheet()// Sets the first three rows to a height of 5 pixelssheet.setRowHeightsForced(0, 3, 5)Auto resize rows
autoResizeRows(startRow, numRows): Automatically resizes the height of the given rows to fit their text content.
const sheet = univerAPI.getActiveWorkbook().getActiveSheet()// Set the first 3 rows to a height that fits their text.sheet.autoResizeRows(0, 3)Set row custom properties
setRowCustom(custom): Set custom properties for given rows, store an object custom on the row, in which you can put any data that conforms to the JSON format, for custom storage of some additional information.
Updating custom data will overwrite the original custom data. If you need to keep the original custom data when updating data, please get the custom data in advance and merge it into new data before updating.
const sheet = univerAPI.getActiveWorkbook().getActiveSheet()// Set custom properties for the first and third rowssheet.setRowCustom({ 0: { color: 'red' }, 2: { size: 16 },})Column
Insert column after given position
insertColumnAfter(afterPosition): Inserts a column after the given column position.
const sheet = univerAPI.getActiveWorkbook().getActiveSheet()// This inserts a column after the first column positionsheet.insertColumnAfter(0)Insert column before given position
insertColumnBefore(beforePosition): Inserts a column before the given column position.
const sheet = univerAPI.getActiveWorkbook().getActiveSheet()// This inserts a column in the first column positionsheet.insertColumnBefore(0)Insert multiple columns at specified location
insertColumns(columnIndex, numColumns): Inserts one or more consecutive blank columns in a sheet starting at the specified location.
const sheet = univerAPI.getActiveWorkbook().getActiveSheet()// Shifts all columns by threesheet.insertColumns(0, 3)Insert multiple columns after given position
insertColumnsAfter(afterPosition, howMany): Inserts a given number of columns after the given column position.
const sheet = univerAPI.getActiveWorkbook().getActiveSheet()// Inserts two columns after the first columnsheet.insertColumnsAfter(0, 2)Insert multiple columns before given position
insertColumnsBefore(beforePosition, howMany): Inserts a number of columns before the given column position.
const sheet = univerAPI.getActiveWorkbook().getActiveSheet()// This inserts five columns before the first columnsheet.insertColumnsBefore(0, 5)Delete column at given position
deleteColumn(columnPosition): Deletes the column at the given column position.
const sheet = univerAPI.getActiveWorkbook().getActiveSheet()// Columns start at "0" - this deletes the first columnsheet.deleteColumn(0)Delete multiple columns starting at given position
deleteColumns(columnPosition, howMany): Deletes a number of columns starting at the given column position.
const sheet = univerAPI.getActiveWorkbook().getActiveSheet()// Columns start at "0" - this deletes the first two columnssheet.deleteColumns(0, 2)Delete multiple columns by specified positions
deleteColumnsByPoints(columnPoints): Deletes multiple columns (not necessarily consecutive) specified by an array of column positions.
const sheet = univerAPI.getActiveWorkbook().getActiveSheet()// Columns start at "0" - this deletes the Column C and Columns E-GfWorksheet.deleteColumnsByPoints([2, [4, 6]])Move columns to new position
moveColumns(columnSpec, destinationIndex): Moves the columns selected by the given range to the position indicated by the destinationIndex. The columnSpec itself does not have to exactly represent an entire column or group of columns to move—it selects all columns that the range spans.
// The code below moves rows A-B to destination index 5.// This results in those columns becoming columns C-D.const sheet = univerAPI.getActiveWorkbook().getActiveSheet()// Selects column A and column B to be moved.const columnSpec = sheet.getRange('A:B')sheet.moveColumns(columnSpec, 5)Hide columns in given range
hideColumn(column): Hides the column or columns in the given range.
const sheet = univerAPI.getActiveWorkbook().getActiveSheet()// This hides the first columnconst range = sheet.getRange('A:A')sheet.hideColumn(range)Hide multiple consecutive columns
hideColumns(columnIndex, numColumns): Hides one or more consecutive columns starting at the given index. Use 0-index for this method.
const sheet = univerAPI.getActiveWorkbook().getActiveSheet()// Hides the first three columnssheet.hideColumns(0, 3)Unhide column in given range
unhideColumn(column): Unhides the column in the given range.
const sheet = univerAPI.getActiveWorkbook().getActiveSheet()// This unhides the first column if it was previously hiddenconst range = sheet.getRange('A:A')sheet.unhideColumn(range)Unhide multiple consecutive columns
showColumns(columnIndex, numColumns): Unhides one or more consecutive columns starting at the given index. Use 0-index for this method.
const sheet = univerAPI.getActiveWorkbook().getActiveSheet()// Unhides the first three columnssheet.showColumns(0, 3)Set column width
setColumnWidth(columnPosition, width): Sets the width of the given column in pixels.
const sheet = univerAPI.getActiveWorkbook().getActiveSheet()// Sets the first column to a width of 200 pixelssheet.setColumnWidth(0, 200)Set width for multiple columns
setColumnWidths(startColumn, numColumns, width): Sets the width of the given columns in pixels.
const sheet = univerAPI.getActiveWorkbook().getActiveSheet()// Sets the first three columns to a width of 200 pixelssheet.setColumnWidths(0, 3, 200)Auto resize columns
autoResizeColumns(startColumn, numColumns): Sets the width of the given columns to automatically fit their text content.
const sheet = univerAPI.getActiveWorkbook().getActiveSheet()// Set the A:C columns to a width that fits their text.sheet.autoResizeColumns(0, 3)Set column custom properties
setColumnCustom(custom): Set custom properties for given columns, store an object custom on the column, in which you can put any data that conforms to the JSON format, for custom storage of some additional information.
Updating custom data will overwrite the original custom data. If you need to keep the original custom data when updating data, please get the custom data in advance and merge it into new data before updating.
const sheet = univerAPI.getActiveWorkbook().getActiveSheet()// Set custom properties for the first and third columnssheet.setColumnCustom({ 0: { color: 'red' }, 2: { size: 16 },})How is this guide?