Row & Column
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 position
sheet.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 position
sheet.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 three
sheet.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 row
sheet.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 row
sheet.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 row
sheet.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 rows
sheet.deleteRows(0, 2);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-4
const sheet = univerAPI.getActiveWorkbook().getActiveSheet();
// Selects row 1 and row 2 to be moved
const rowSpec = sheet.getRange(0,0,2,1);
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 row
const range = sheet.getRange(0,0,0,0);
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 rows
sheet.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 hidden
const range = sheet.getRange(0,0,0,0);
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 rows
sheet.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 pixels
sheet.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 20 pixels
sheet.setRowHeights(0, 3, 20);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 pixels
sheet.setRowHeightsForced(0, 3, 5);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 rows
sheet.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 position
sheet.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 position
sheet.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 three
sheet.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 column
sheet.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 column
sheet.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 column
sheet.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 columns
sheet.deleteColumns(0, 2);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(0,0,1,2);
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 column
const range = sheet.getRange(0,0,0,0);
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 columns
sheet.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 hidden
const range = sheet.getRange(0,0,0,0);
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 columns
sheet.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 pixels
sheet.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 pixels
sheet.setColumnWidths(0, 3, 200);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 columns
sheet.setColumnCustom({
0: { color: 'red' },
2: { size: 16 },
});