Skip to Content
ClassesFRange

类: FRange

Represents a range of cells in a sheet. You can call methods on this Facade API object to read contents or manipulate the range.

继承

  • FBaseInitialable.IFRangeSheetsNumfmtMixin.IFRangeHyperlinkMixin.IFRangeFilter.IFRangeSort.IFRangeDataValidationMixin.IFRangeConditionalFormattingMixin.IFRangeCommentMixin.IFRangeSheetsUIMixin.IFRangeSheetDrawingMixin.IFRangePrint

方法

activate()

activate(): FRange

Sets the specified range as the active range, with the top left cell in the range as the current cell.

返回

FRange

This range, for chaining.

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:B2'); fRange.activate(); // the active cell will be A1

activateAsCurrentCell()

activateAsCurrentCell(): FRange

Sets the specified cell as the current cell. If the specified cell is present in an existing range, then that range becomes the active range with the cell as the current cell. If the specified cell is not part of an existing range, then a new range is created with the cell as the active range and the current cell.

返回

FRange

This range, for chaining.

Description

If the range is not a single cell, an error will be thrown.

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); // Set the range A1:B2 as the active range, default active cell is A1 const fRange = fWorksheet.getRange('A1:B2'); fRange.activate(); console.log(fWorksheet.getActiveRange().getA1Notation()); // A1:B2 console.log(fWorksheet.getActiveCell().getA1Notation()); // A1 // Set the cell B2 as the active cell // Because B2 is in the active range A1:B2, the active range will not change, and the active cell will be changed to B2 const cell = fWorksheet.getRange('B2'); cell.activateAsCurrentCell(); console.log(fWorksheet.getActiveRange().getA1Notation()); // A1:B2 console.log(fWorksheet.getActiveCell().getA1Notation()); // B2 // Set the cell C3 as the active cell // Because C3 is not in the active range A1:B2, a new active range C3:C3 will be created, and the active cell will be changed to C3 const cell2 = fWorksheet.getRange('C3'); cell2.activateAsCurrentCell(); console.log(fWorksheet.getActiveRange().getA1Notation()); // C3:C3 console.log(fWorksheet.getActiveCell().getA1Notation()); // C3

addComment()

addComment(content): Promise<boolean>

参数

参数类型
content| IDocumentBody | FTheadCommentBuilder

返回

Promise<boolean>

已被弃用

use addCommentAsync as instead.


addCommentAsync()

addCommentAsync(content): Promise<boolean>

Add a comment to the start cell in the current range.

参数

参数类型描述
content| IDocumentBody | FTheadCommentBuilderThe content of the comment.

返回

Promise<boolean>

Whether the comment is added successfully.

示例

// Create a new comment const richText = univerAPI.newRichText().insertText('hello univer'); const commentBuilder = univerAPI.newTheadComment() .setContent(richText); console.log(commentBuilder.content.toPlainText()); // Add the comment to the cell A1 const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const cell = fWorksheet.getRange('A1'); const result = await cell.addCommentAsync(commentBuilder); console.log(result);

addConditionalFormattingRule()

addConditionalFormattingRule(rule): FRange

Add a new conditional format

参数

参数类型描述
ruleIConditionFormattingRule

返回

FRange

Returns the current range instance for method chaining

已被弃用

use same API in FWorkSheet.

Memberof

IFRangeConditionalFormattingMixin


attachAlertPopup()

attachAlertPopup(alert): IDisposable

Attach an alert popup to the start cell of current range.

参数

参数类型描述
alertOmit<ICellAlert, "location">The alert to attach

返回

IDisposable

The disposable object to detach the alert.

示例

// Attach an alert popup to the start cell of range C3:E5 const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('C3:E5'); const disposable = fRange.attachAlertPopup({ title: 'Warning', message: 'This is an warning message', type: 1 }); // Detach the alert after 5 seconds setTimeout(() => { disposable.dispose(); }, 5000);

attachPopup()

attachPopup(popup): any

Attach a popup to the start cell of current range. If current worksheet is not active, the popup will not be shown. Be careful to manager the detach disposable object, if not dispose correctly, it might memory leaks.

参数

参数类型描述
popupIFCanvasPopupThe popup to attach

返回

any

The disposable object to detach the popup, if the popup is not attached, return null.

示例

// Register a custom popup component univerAPI.registerComponent( 'myPopup', () => React.createElement('div', { style: { color: 'red', fontSize: '14px' } }, 'Custom Popup') ); // Attach the popup to the start cell of range C3:E5 const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('C3:E5'); const disposable = fRange.attachPopup({ componentKey: 'myPopup' }); // Detach the popup after 5 seconds setTimeout(() => { disposable.dispose(); }, 5000);

attachRangePopup()

attachRangePopup(popup): any

Attach a DOM popup to the current range.

参数

参数类型
popupIFCanvasPopup

返回

any

The disposable object to detach the alert.

示例

// Register a custom popup component univerAPI.registerComponent( 'myPopup', () => React.createElement('div', { style: { background: 'red', fontSize: '14px' } }, 'Custom Popup') ); // Attach the popup to the range C3:E5 const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('C3:E5'); const disposable = fRange.attachRangePopup({ componentKey: 'myPopup', direction: 'top' // 'vertical' | 'horizontal' | 'top' | 'right' | 'left' | 'bottom' | 'bottom-center' | 'top-center' });

breakApart()

breakApart(): FRange

Break all horizontally- or vertically-merged cells contained within the range list into individual cells again.

返回

FRange

This range, for chaining

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:B2'); fRange.merge(); const anchor = fWorksheet.getRange('A1'); console.log(anchor.isPartOfMerge()); // true fRange.breakApart(); console.log(anchor.isPartOfMerge()); // false

cancelHyperLink(id): boolean

参数

参数类型
idstring

返回

boolean

已被弃用

use range.setRichTextValueForCell(range.getValue(true).copy().cancelLink(id)) instead

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1'); const richText = univerAPI.newRichText().insertLink('Univer', 'https://univer.ai/'); fRange.setRichTextValueForCell(richText); // Cancel hyperlink after 3 seconds setTimeout(() => { const cellValue = fRange.getValue(true); const hyperlinks = cellValue.getLinks(); const id = hyperlinks[0].rangeId; const newRichText = cellValue.copy().cancelLink(id); fRange.setRichTextValueForCell(newRichText); }, 3000);

clear()

clear(options?): FRange

Clears content and formatting information of the range. Or Optionally clears only the contents or only the formatting.

参数

参数类型描述
options?IFacadeClearOptionsOptions for clearing the range. If not provided, the contents and formatting are cleared both.

返回

FRange

Returns the current worksheet instance for method chaining

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorkSheet = fWorkbook.getActiveSheet(); const fRange = fWorkSheet.getRange('A1:D10'); // clear the content and format of the range A1:D10 fRange.clear(); // clear the content only of the range A1:D10 fRange.clear({ contentsOnly: true });

clearComment()

clearComment(): Promise<boolean>

返回

Promise<boolean>

已被弃用

use clearCommentAsync as instead.


clearCommentAsync()

clearCommentAsync(): Promise<boolean>

Clear the comment of the start cell in the current range.

返回

Promise<boolean>

Whether the comment is cleared successfully.


clearComments()

clearComments(): Promise<boolean>

返回

Promise<boolean>

已被弃用

use clearCommentsAsync as instead.


clearCommentsAsync()

clearCommentsAsync(): Promise<boolean>

Clear all of the comments in the current range.

返回

Promise<boolean>

Whether the comments are cleared successfully.

示例

const range = univerAPI.getActiveWorkbook() .getActiveSheet() .getActiveRange(); const success = await range.clearCommentsAsync();

clearConditionalFormatRules()

clearConditionalFormatRules(): FRange

Clear the conditional rules for the range.

返回

FRange

Returns the current range instance for method chaining

Memberof

IFRangeConditionalFormattingMixin

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:T100'); // Clear all conditional format rules for the range fRange.clearConditionalFormatRules(); console.log(fRange.getConditionalFormattingRules()); // []

clearContent()

clearContent(): FRange

Clears content of the range, while preserving formatting information.

返回

FRange

Returns the current worksheet instance for method chaining

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorkSheet = fWorkbook.getActiveSheet(); const fRange = fWorkSheet.getRange('A1:D10'); // clear the content only of the range A1:D10 fRange.clearContent();

clearFormat()

clearFormat(): FRange

Clears formatting information of the range, while preserving contents.

返回

FRange

Returns the current worksheet instance for method chaining

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorkSheet = fWorkbook.getActiveSheet(); const fRange = fWorkSheet.getRange('A1:D10'); // clear the format only of the range A1:D10 fRange.clearFormat();

createConditionalFormattingRule()

createConditionalFormattingRule(): FConditionalFormattingBuilder

Creates a constructor for conditional formatting

返回

FConditionalFormattingBuilder

The conditional formatting builder

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); // Create a conditional formatting rule that sets the cell format to italic, red background, and green font color when the cell is not empty. const fRange = fWorksheet.getRange('A1:T100'); const rule = fRange.createConditionalFormattingRule() .whenCellNotEmpty() .setItalic(true) .setBackground('red') .setFontColor('green') .build(); fWorksheet.addConditionalFormattingRule(rule); console.log(fRange.getConditionalFormattingRules());

createFilter()

createFilter(this): FFilter

Create a filter for the current range. If the worksheet already has a filter, this method would return null.

参数

参数类型
thisFRange

返回

FFilter

The FFilter instance to handle the filter.

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:D14'); let fFilter = fRange.createFilter(); // If the worksheet already has a filter, remove it and create a new filter. if (!fFilter) { fWorksheet.getFilter().remove(); fFilter = fRange.createFilter(); } console.log(fFilter, fFilter.getRange().getA1Notation());

deleteCells()

deleteCells(shiftDimension): void

Deletes this range of cells. Existing data in the sheet along the provided dimension is shifted towards the deleted range.

参数

参数类型描述
shiftDimensionDimensionThe dimension along which to shift existing data.

返回

void

示例

// Assume the active sheet empty sheet. const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const values = [ [1, 2, 3, 4], [2, 3, 4, 5], [3, 4, 5, 6], [4, 5, 6, 7], [5, 6, 7, 8], ]; // Set the range A1:D5 with some values, the range A1:D5 will be: // 1 | 2 | 3 | 4 // 2 | 3 | 4 | 5 // 3 | 4 | 5 | 6 // 4 | 5 | 6 | 7 // 5 | 6 | 7 | 8 const fRange = fWorksheet.getRange('A1:D5'); fRange.setValues(values); console.log(fWorksheet.getRange('A1:D5').getValues()); // [[1, 2, 3, 4], [2, 3, 4, 5], [3, 4, 5, 6], [4, 5, 6, 7], [5, 6, 7, 8]] // Delete the range A1:B2 along the columns dimension, the range A1:D5 will be: // 3 | 4 | | // 4 | 5 | | // 3 | 4 | 5 | 6 // 4 | 5 | 6 | 7 // 5 | 6 | 7 | 8 const fRange2 = fWorksheet.getRange('A1:B2'); fRange2.deleteCells(univerAPI.Enum.Dimension.COLUMNS); console.log(fWorksheet.getRange('A1:D5').getValues()); // [[3, 4, null, null], [4, 5, null, null], [3, 4, 5, 6], [4, 5, 6, 7], [5, 6, 7, 8]] // Set the range A1:D5 values again, the range A1:D5 will be: // 1 | 2 | 3 | 4 // 2 | 3 | 4 | 5 // 3 | 4 | 5 | 6 // 4 | 5 | 6 | 7 // 5 | 6 | 7 | 8 fRange.setValues(values); // Delete the range A1:B2 along the rows dimension, the range A1:D5 will be: // 3 | 4 | 3 | 4 // 4 | 5 | 4 | 5 // 5 | 6 | 5 | 6 // | | 6 | 7 // | | 7 | 8 const fRange3 = fWorksheet.getRange('A1:B2'); fRange3.deleteCells(univerAPI.Enum.Dimension.ROWS); console.log(fWorksheet.getRange('A1:D5').getValues()); // [[3, 4, 3, 4], [4, 5, 4, 5], [5, 6, 5, 6], [null, null, 6, 7], [null, null, 7, 8]]

deleteConditionalFormattingRule()

deleteConditionalFormattingRule(cfId): FRange

Delete conditional format according to cfId

参数

参数类型描述
cfIdstring

返回

FRange

Returns the current range instance for method chaining

已被弃用

use same API in FWorkSheet.

Memberof

IFRangeConditionalFormattingMixin


forEach()

forEach(callback): void

Iterate cells in this range. Merged cells will be respected.

参数

参数类型描述
callback(row, col, cell) => voidthe callback function to be called for each cell in the range

返回

void


generateHTML()

generateHTML(this): string

Generate HTML content for the range.

参数

参数类型
thisFRange

返回

string

HTML content of the range.

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:B2'); fRange.setValues([ [1, 2], [3, 4] ]); console.log(fRange.generateHTML());

getA1Notation()

getA1Notation( withSheet?, startAbsoluteRefType?, endAbsoluteRefType?): string

Returns a string description of the range, in A1 notation.

参数

参数类型描述
withSheet?booleanIf true, the sheet name is included in the A1 notation.
startAbsoluteRefType?AbsoluteRefTypeThe absolute reference type for the start cell.
endAbsoluteRefType?AbsoluteRefTypeThe absolute reference type for the end cell.

返回

string

The A1 notation of the range.

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); // By default, the A1 notation is returned without the sheet name and without absolute reference types. const fRange = fWorksheet.getRange('A1:B2'); console.log(fRange.getA1Notation()); // A1:B2 // By setting withSheet to true, the sheet name is included in the A1 notation. fWorksheet.setName('Sheet1'); console.log(fRange.getA1Notation(true)); // Sheet1!A1:B2 // By setting startAbsoluteRefType, the absolute reference type for the start cell is included in the A1 notation. console.log(fRange.getA1Notation(false, univerAPI.Enum.AbsoluteRefType.ROW)); // A$1:B2 console.log(fRange.getA1Notation(false, univerAPI.Enum.AbsoluteRefType.COLUMN)); // $A1:B2 console.log(fRange.getA1Notation(false, univerAPI.Enum.AbsoluteRefType.ALL)); // $A$1:B2 // By setting endAbsoluteRefType, the absolute reference type for the end cell is included in the A1 notation. console.log(fRange.getA1Notation(false, null, univerAPI.Enum.AbsoluteRefType.ROW)); // A1:B$2 console.log(fRange.getA1Notation(false, null, univerAPI.Enum.AbsoluteRefType.COLUMN)); // A1:$B2 console.log(fRange.getA1Notation(false, null, univerAPI.Enum.AbsoluteRefType.ALL)); // A1:$B$2 // By setting all parameters example console.log(fRange.getA1Notation(true, univerAPI.Enum.AbsoluteRefType.ALL, univerAPI.Enum.AbsoluteRefType.ALL)); // Sheet1!$A$1:$B$2

getBackground()

getBackground(): string

Returns the background color of the top-left cell in the range.

返回

string

The color code of the background.

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:B2'); console.log(fRange.getBackground());

getBackgrounds()

getBackgrounds(): ...[][]

Returns the background colors of the cells in the range.

返回

…[][]

A two-dimensional array of color codes of the backgrounds.

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:B2'); console.log(fRange.getBackgrounds());

getCell()

getCell(this): ICellWithCoord

Return this cell information, including whether it is merged and cell coordinates

参数

参数类型
thisFRange

返回

ICellWithCoord

cell location and coordinate.

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('H6'); console.log(fRange.getCell());

getCellData()

getCellData(): ICellData

Return first cell model data in this range

返回

ICellData

The cell model data

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:B2'); console.log(fRange.getCellData());

getCellDataGrid()

getCellDataGrid(): ...[][]

Returns the cell data for the cells in the range.

返回

…[][]

A two-dimensional array of cell data.

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:B2'); console.log(fRange.getCellDataGrid());

getCellDatas()

getCellDatas(): ...[][]

Alias for getCellDataGrid.

返回

…[][]

A two-dimensional array of cell data.

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:B2'); console.log(fRange.getCellDatas());

getCellRect()

getCellRect(this): DOMRect

Returns the coordinates of this cell,does not include units

参数

参数类型
thisFRange

返回

DOMRect

coordinates of the cell, top, right, bottom, left

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('H6'); console.log(fRange.getCellRect());

getCellStyle()

getCellStyle(): any

Return first cell style in this range

返回

any

The cell style

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:B2'); console.log(fRange.getCellStyle());

getCellStyleData()

getCellStyleData(): IStyleData

Return first cell style data in this range

返回

IStyleData

The cell style data

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:B2'); console.log(fRange.getCellStyleData());

getCellStyles()

getCellStyles(): ...[][]

Returns the cell styles for the cells in the range.

返回

…[][]

A two-dimensional array of cell styles.

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:B2'); console.log(fRange.getCellStyles());

getColumn()

getColumn(): number

Gets the starting column index of the range. index starts at 0.

返回

number

The starting column index of the range.

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:B2'); console.log(fRange.getColumn()); // 0

getComment()

getComment(): Nullable<FThreadComment>

Get the comment of the start cell in the current range.

返回

Nullable<FThreadComment>

The comment of the start cell in the current range. If the cell does not have a comment, return null.

示例

const range = univerAPI.getActiveWorkbook() .getActiveSheet() .getActiveRange(); const comment = range.getComment();

getComments()

getComments(): FThreadComment[]

Get the comments in the current range.

返回

FThreadComment[]

The comments in the current range.

示例

const range = univerAPI.getActiveWorkbook() .getActiveSheet() .getActiveRange(); const comments = range.getComments(); comments.forEach((comment) => { console.log(comment.getContent()); });

getConditionalFormattingRules()

getConditionalFormattingRules(): IConditionFormattingRule[]

Gets all the conditional formatting for the current range.

返回

IConditionFormattingRule[]

conditional formatting rules for the current range.

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); // Create a conditional formatting rule that sets the cell format to italic, red background, and green font color when the cell is not empty. const fRange = fWorksheet.getRange('A1:T100'); const rule = fWorksheet.newConditionalFormattingRule() .whenCellNotEmpty() .setRanges([fRange.getRange()]) .setItalic(true) .setBackground('red') .setFontColor('green') .build(); fWorksheet.addConditionalFormattingRule(rule); // Get all the conditional formatting rules for the range F6:H8. const targetRange = fWorksheet.getRange('F6:H8'); const rules = targetRange.getConditionalFormattingRules(); console.log(rules);

getCustomMetaData()

getCustomMetaData(): CustomData

Returns the custom meta data for the cell at the start of this range.

返回

CustomData

The custom meta data

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:B2'); console.log(fRange.getCustomMetaData());

getCustomMetaDatas()

getCustomMetaDatas(): ...[][]

Returns the custom meta data for the cells in the range.

返回

…[][]

A two-dimensional array of custom meta data

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:B2'); console.log(fRange.getCustomMetaDatas());

getDataRegion()

getDataRegion(dimension?): FRange

Returns a copy of the range expanded Direction.UP and Direction.DOWN if the specified dimension is Dimension.ROWS, or Direction.NEXT and Direction.PREVIOUS if the dimension is Dimension.COLUMNS. The expansion of the range is based on detecting data next to the range that is organized like a table. The expanded range covers all adjacent cells with data in them along the specified dimension including the table boundaries. If the original range is surrounded by empty cells along the specified dimension, the range itself is returned.

参数

参数类型描述
dimension?DimensionThe dimension along which to expand the range. If not provided, the range will be expanded in both dimensions.

返回

FRange

The range’s data region or a range covering each column or each row spanned by the original range.

示例

// Assume the active sheet is a new sheet with no data. const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); // Set the range A1:D4 with some values, the range A1:D4 will be: // | | | // | | 100 | // | 100 | | 100 // | | 100 | fWorksheet.getRange('C2').setValue(100); fWorksheet.getRange('B3').setValue(100); fWorksheet.getRange('D3').setValue(100); fWorksheet.getRange('C4').setValue(100); // Get C3 data region along the rows dimension, the range will be C2:D4 const range = fWorksheet.getRange('C3').getDataRegion(univerAPI.Enum.Dimension.ROWS); console.log(range.getA1Notation()); // C2:C4 // Get C3 data region along the columns dimension, the range will be B3:D3 const range2 = fWorksheet.getRange('C3').getDataRegion(univerAPI.Enum.Dimension.COLUMNS); console.log(range2.getA1Notation()); // B3:D3 // Get C3 data region along the both dimension, the range will be B2:D4 const range3 = fWorksheet.getRange('C3').getDataRegion(); console.log(range3.getA1Notation()); // B2:D4

getDataValidation()

getDataValidation(): Nullable<FDataValidation>

Get first data validation rule in current range.

返回

Nullable<FDataValidation>

data validation rule

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); // Create a data validation rule that requires a number equal to 20 for the range A1:B10 const fRange = fWorksheet.getRange('A1:B10'); const rule = univerAPI.newDataValidation() .requireNumberEqualTo(20) .build(); fRange.setDataValidation(rule); console.log(fRange.getDataValidation().getCriteriaValues()); // Change the rule criteria to require a number between 1 and 10 fRange.getDataValidation().setCriteria( univerAPI.Enum.DataValidationType.DECIMAL, [univerAPI.Enum.DataValidationOperator.BETWEEN, '1', '10'] ); // Print the new rule criteria values console.log(fRange.getDataValidation().getCriteriaValues());

getDataValidations()

getDataValidations(): FDataValidation[]

Get all data validation rules in current range.

返回

FDataValidation[]

all data validation rules

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); // Create a data validation rule that requires a number equal to 20 for the range A1:B10 const fRange1 = fWorksheet.getRange('A1:B10'); const rule1 = univerAPI.newDataValidation() .requireNumberEqualTo(20) .build(); fRange1.setDataValidation(rule1); // Create a data validation rule that requires a number between 1 and 10 for the range C1:D10 const fRange2 = fWorksheet.getRange('C1:D10'); const rule2 = univerAPI.newDataValidation() .requireNumberBetween(1, 10) .build(); fRange2.setDataValidation(rule2); // Get all data validation rules in the range A1:D10 const range = fWorksheet.getRange('A1:D10'); const rules = range.getDataValidations(); console.log(rules.length); // 2

getDisplayValue()

getDisplayValue(): string

Returns the displayed value of the top-left cell in the range. The value is a String. Empty cells return an empty string.

返回

string

The displayed value of the cell. Returns an empty string if the cell is empty.

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:B2'); fRange.setValueForCell({ v: 0.2, s: { n: { pattern: '0%', }, }, }); console.log(fRange.getDisplayValue()); // 20%

getDisplayValues()

getDisplayValues(): ...[][]

Returns a two-dimensional array of the range displayed values. Empty cells return an empty string.

返回

…[][]

A two-dimensional array of values.

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:B2'); fRange.setValues([ [ { v: 0.2, s: { n: { pattern: '0%', }, }, }, { v: 45658, s: { n: { pattern: 'yyyy-mm-dd', }, }, } ], [ { v: 1234.567, s: { n: { pattern: '#,##0.00', } } }, null, ], ]); console.log(fRange.getDisplayValues()); // [['20%', '2025-01-01'], ['1,234.57', '']]

getFilter()

getFilter(): FFilter

Get the filter in the worksheet to which the range belongs. If the worksheet does not have a filter, this method would return null. Normally, you can directly call getFilter on FWorksheet.

返回

FFilter

The FFilter instance to handle the filter.

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:D14'); let fFilter = fRange.getFilter(); // If the worksheet does not have a filter, create a new filter. if (!fFilter) { fFilter = fRange.createFilter(); } console.log(fFilter, fFilter.getRange().getA1Notation());

getFormula()

getFormula(): string

Returns the formula (A1 notation) of the top-left cell in the range, or an empty string if the cell is empty or doesn’t contain a formula.

返回

string

The formula for the cell.

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:B2'); console.log(fRange.getFormula());

getFormulas()

getFormulas(): ...[][]

Returns the formulas (A1 notation) for the cells in the range. Entries in the 2D array are empty strings for cells with no formula.

返回

…[][]

A two-dimensional array of formulas in string format.

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:B2'); console.log(fRange.getFormulas());

getHeight()

getHeight(): number

Gets the height of the applied area

返回

number

The height of the area

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:B2'); console.log(fRange.getHeight());

getHorizontalAlignment()

getHorizontalAlignment(): string

Returns the horizontal alignment of the text (left/center/right) of the top-left cell in the range.

返回

string

The horizontal alignment of the text in the cell.

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:B2'); console.log(fRange.getHorizontalAlignment());

getHorizontalAlignments()

getHorizontalAlignments(): ...[][]

Returns the horizontal alignments of the cells in the range.

返回

…[][]

A two-dimensional array of horizontal alignments of text associated with cells in the range.

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:B2'); console.log(fRange.getHorizontalAlignments());

getHyperLinks(): ICellHyperLink[]

返回

ICellHyperLink[]

已被弃用

use range.setRichTextValueForCell(range.getValue(true).getLinks()) instead

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); // Create a hyperlink to Univer on cell A1 const fRange = fWorksheet.getRange('A1'); const richText = univerAPI.newRichText().insertLink('Univer', 'https://univer.ai/'); fRange.setRichTextValueForCell(richText); // Get hyperlinks from cell A1 console.log(fRange.getValue(true).getLinks());

getLastColumn()

getLastColumn(): number

Gets the ending column index of the range. index starts at 0.

返回

number

The ending column index of the range.

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:B2'); console.log(fRange.getLastColumn()); // 1

getLastRow()

getLastRow(): number

Gets the ending row index of the range. index starts at 0.

返回

number

The ending row index of the range.

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:B2'); console.log(fRange.getLastRow()); // 1

getNumberFormat()

getNumberFormat(): string

Get the number formatting of the top-left cell of the given range. Empty cells return an empty string.

返回

string

The number format of the top-left cell of the range.

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); // Get the number format of the top-left cell of the A1:B2 range. const fRange = fWorksheet.getRange('A1:B2'); console.log(fRange.getNumberFormat());

getNumberFormats()

getNumberFormats(): ...[][]

Returns the number formats for the cells in the range.

返回

…[][]

A two-dimensional array of number formats.

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); // Get the number formats of the A1:B2 range. const fRange = fWorksheet.getRange('A1:B2'); console.log(fRange.getNumberFormats());

getRange()

getRange(): IRange

Gets the area where the statement is applied

返回

IRange

The area where the statement is applied

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:B2'); const range = fRange.getRange(); const { startRow, startColumn, endRow, endColumn } = range; console.log(range);

getRawValue()

getRawValue(): Nullable<CellValue>

Returns the raw value of the top-left cell in the range. Empty cells return null.

返回

Nullable<CellValue>

The raw value of the cell. Returns null if the cell is empty.

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:B2'); fRange.setValueForCell({ v: 0.2, s: { n: { pattern: '0%', }, }, }); console.log(fRange.getRawValue()); // 0.2

getRawValues()

getRawValues(): ...[][]

Returns a two-dimensional array of the range raw values. Empty cells return null.

返回

…[][]

The raw value of the cell. Returns null if the cell is empty.

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:B2'); fRange.setValues([ [ { v: 0.2, s: { n: { pattern: '0%', }, }, }, { v: 45658, s: { n: { pattern: 'yyyy-mm-dd', }, }, } ], [ { v: 1234.567, s: { n: { pattern: '#,##0.00', } } }, null, ], ]); console.log(fRange.getRawValues()); // [[0.2, 45658], [1234.567, null]]

getRow()

getRow(): number

Gets the starting row index of the range. index starts at 0.

返回

number

The starting row index of the range.

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:B2'); console.log(fRange.getRow()); // 0

getScreenshot()

getScreenshot(): string | false

Get screenshot of this range. This API is only available with a license. Users without a license will face usage restrictions. On failure, it returns false, and on success, it returns the image’s base64 string.

返回

string | false

  • The base64 encoded image string, or false if the user does not have permission.

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:D10'); console.log(fRange.getScreenshot());

getSheetId()

getSheetId(): string

Gets the ID of the worksheet

返回

string

The ID of the worksheet

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:B2'); console.log(fRange.getSheetId());

getSheetName()

getSheetName(): string

Gets the name of the worksheet

返回

string

The name of the worksheet

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:B2'); console.log(fRange.getSheetName());

getUnitId()

getUnitId(): string

Get the unit ID of the current workbook

返回

string

The unit ID of the workbook

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:B2'); console.log(fRange.getUnitId());

getUrl()

getUrl(): string

Create a hyperlink url to this range

返回

string

The url of this range

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1'); const url = fRange.getUrl(); console.log(url);

getUsedThemeStyle()

getUsedThemeStyle(): string

Gets the theme style applied to the range.

返回

string

The name of the theme style applied to the range or not exist.

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:E20'); console.log(fRange.getUsedThemeStyle()); // undefined fRange.useThemeStyle('default'); console.log(fRange.getUsedThemeStyle()); // 'default'

getValidatorStatus()

getValidatorStatus(): Promise<...[]>

Get data validation validator status for current range.

返回

Promise<…[]>

matrix of validator status

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); // Set some values in the range A1:B10 const fRange = fWorksheet.getRange('A1:B10'); fRange.setValues([ [1, 2], [3, 4], [5, 6], [7, 8], [9, 10], [11, 12], [13, 14], [15, 16], [17, 18], [19, 20] ]); // Create a data validation rule that requires a number between 1 and 10 for the range A1:B10 const rule = univerAPI.newDataValidation() .requireNumberBetween(1, 10) .build(); fRange.setDataValidation(rule); // Get the validator status for the cell B2 const status = await fWorksheet.getRange('B2').getValidatorStatus(); console.log(status?.[0]?.[0]); // 'valid' // Get the validator status for the cell B10 const status2 = await fWorksheet.getRange('B10').getValidatorStatus(); console.log(status2?.[0]?.[0]); // 'invalid'

getValue()

调用签名

getValue(): CellValue

Return first cell value in this range

返回

CellValue

The cell value

示例
const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:B2'); console.log(fRange.getValue()); // set the first cell value to 123 fRange.setValueForCell(123); console.log(fRange.getValue()); // 123

调用签名

getValue(includeRichText): any

Return first cell value in this range

参数
参数类型描述
includeRichTexttrueShould the returns of this func to include rich text
返回

any

The cell value

示例
const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:B2'); console.log(fRange.getValue(true)); // set the first cell value to 123 const richText = univerAPI.newRichText({ body: { dataStream: 'Hello World\r\n' } }) .setStyle(0, 1, { bl: 1, cl: { rgb: '#c81e1e' } }) .setStyle(6, 7, { bl: 1, cl: { rgb: '#c81e1e' } }); fRange.setRichTextValueForCell(richText); console.log(fRange.getValue(true).toPlainText()); // Hello World

getValueAndRichTextValues()

getValueAndRichTextValues(): ...[][]

Returns the value and rich text value for the cells in the range.

返回

…[][]

A two-dimensional array of value and rich text value

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:B2'); console.log(fRange.getValueAndRichTextValues());

getValues()

调用签名

getValues(): ...[][]

Returns the cell values for the cells in the range.

返回

…[][]

A two-dimensional array of cell values.

示例
// Get plain values const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:B2'); console.log(fRange.getValues());

调用签名

getValues(includeRichText): ...[][]

Returns the cell values for the cells in the range.

参数
参数类型描述
includeRichTexttrueShould the returns of this func to include rich text
返回

…[][]

A two-dimensional array of cell values.

示例
// Get values with rich text if available const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:B2'); console.log(fRange.getValues(true));

getVerticalAlignment()

getVerticalAlignment(): string

Returns the vertical alignment (top/middle/bottom) of the top-left cell in the range.

返回

string

The vertical alignment of the text in the cell.

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:B2'); console.log(fRange.getVerticalAlignment());

getVerticalAlignments()

getVerticalAlignments(): ...[][]

Returns the vertical alignments of the cells in the range.

返回

…[][]

A two-dimensional array of vertical alignments of text associated with cells in the range.

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:B2'); console.log(fRange.getVerticalAlignments());

getWidth()

getWidth(): number

Gets the width of the applied area

返回

number

The width of the area

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:B2'); console.log(fRange.getWidth());

getWrap()

getWrap(): boolean

Gets whether text wrapping is enabled for top-left cell in the range.

返回

boolean

whether text wrapping is enabled for the cell.

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:B2'); console.log(fRange.getWrap());

getWraps()

getWraps(): ...[][]

Gets whether text wrapping is enabled for cells in the range.

返回

…[][]

A two-dimensional array of whether text wrapping is enabled for each cell in the range.

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:B2'); console.log(fRange.getWraps()); *** ### getWrapStrategy() ```ts getWrapStrategy(): WrapStrategy

Returns the text wrapping strategy for the top left cell of the range.

返回

WrapStrategy

The text wrapping strategy

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:B2'); console.log(fRange.getWrapStrategy());

highlight()

highlight(style?, primary?): IDisposable

Highlight the range with the specified style and primary cell.

参数

参数类型描述
style?anystyle for highlight range.
primary?anyprimary cell for highlight range.

返回

IDisposable

The disposable object to remove the highlight.

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); // Highlight the range C3:E5 with default style const fRange = fWorksheet.getRange('C3:E5'); fRange.highlight(); // Highlight the range C7:E9 with custom style and primary cell D8 const fRange2 = fWorksheet.getRange('C7:E9'); const primaryCell = fWorksheet.getRange('D8').getRange(); const disposable = fRange2.highlight( { stroke: 'red', fill: 'yellow' }, { ...primaryCell, actualRow: primaryCell.startRow, actualColumn: primaryCell.startColumn } ); // Remove the range C7:E9 highlight after 5 seconds setTimeout(() => { disposable.dispose(); }, 5000);

insertCellImageAsync()

insertCellImageAsync(file): Promise<boolean>

Inserts an image into the current cell.

参数

参数类型描述
filestring | FileFile or URL string

返回

Promise<boolean>

True if the image is inserted successfully, otherwise false

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); // Insert an image into the cell A10 const fRange = fWorksheet.getRange('A10'); const result = await fRange.insertCellImageAsync('https://avatars.githubusercontent.com/u/61444807?s=48&v=4'); console.log(result);

继承自

IFRangeSheetDrawingMixin.insertCellImageAsync


insertCells()

insertCells(shiftDimension): void

Inserts empty cells into this range. Existing data in the sheet along the provided dimension is shifted away from the inserted range.

参数

参数类型描述
shiftDimensionDimensionThe dimension along which to shift existing data.

返回

void

示例

// Assume the active sheet empty sheet. const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const values = [ [1, 2, 3, 4], [2, 3, 4, 5], [3, 4, 5, 6], [4, 5, 6, 7], [5, 6, 7, 8], ]; // Set the range A1:D5 with some values, the range A1:D5 will be: // 1 | 2 | 3 | 4 // 2 | 3 | 4 | 5 // 3 | 4 | 5 | 6 // 4 | 5 | 6 | 7 // 5 | 6 | 7 | 8 const fRange = fWorksheet.getRange('A1:D5'); fRange.setValues(values); console.log(fWorksheet.getRange('A1:D5').getValues()); // [[1, 2, 3, 4], [2, 3, 4, 5], [3, 4, 5, 6], [4, 5, 6, 7], [5, 6, 7, 8]] // Insert the empty cells into the range A1:B2 along the columns dimension, the range A1:D5 will be: // | | 1 | 2 // | | 2 | 3 // 3 | 4 | 5 | 6 // 4 | 5 | 6 | 7 // 5 | 6 | 7 | 8 const fRange2 = fWorksheet.getRange('A1:B2'); fRange2.insertCells(univerAPI.Enum.Dimension.COLUMNS); console.log(fWorksheet.getRange('A1:D5').getValues()); // [[null, null, 1, 2], [null, null, 2, 3], [3, 4, 5, 6], [4, 5, 6, 7], [5, 6, 7, 8]] // Set the range A1:D5 values again, the range A1:D5 will be: // 1 | 2 | 3 | 4 // 2 | 3 | 4 | 5 // 3 | 4 | 5 | 6 // 4 | 5 | 6 | 7 // 5 | 6 | 7 | 8 fRange.setValues(values); // Insert the empty cells into the range A1:B2 along the rows dimension, the range A1:D5 will be: // | | 3 | 4 // | | 4 | 5 // 1 | 2 | 5 | 6 // 2 | 3 | 6 | 7 // 3 | 4 | 7 | 8 const fRange3 = fWorksheet.getRange('A1:B2'); fRange3.insertCells(univerAPI.Enum.Dimension.ROWS); console.log(fWorksheet.getRange('A1:D5').getValues()); // [[null, null, 3, 4], [null, null, 4, 5], [1, 2, 5, 6], [2, 3, 6, 7], [3, 4, 7, 8]]

isBlank()

isBlank(): boolean

Returns true if the range is totally blank.

返回

boolean

true if the range is blank; false otherwise.

示例

// Assume the active sheet is a new sheet with no data. const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:B2'); console.log(fRange.isBlank()); // true // Set the range A1:B2 with some values fRange.setValueForCell(123); console.log(fRange.isBlank()); // false

isMerged()

isMerged(): boolean

Return range whether this range is merged

返回

boolean

if true is merged

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:B2'); console.log(fRange.isMerged()); // merge cells A1:B2 fRange.merge(); console.log(fRange.isMerged());

isPartOfMerge()

isPartOfMerge(): boolean

Returns true if cells in the current range overlap a merged cell.

返回

boolean

is overlap with a merged cell

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:B2'); fRange.merge(); const anchor = fWorksheet.getRange('A1'); console.log(anchor.isPartOfMerge()); // true

merge()

merge(defaultMerge?): FRange

Merge cells in a range into one merged cell

参数

参数类型描述
defaultMerge?booleanIf true, only the value in the upper left cell is retained.

返回

FRange

This range, for chaining

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:B2'); fRange.merge(); console.log(fRange.isMerged());

mergeAcross()

mergeAcross(defaultMerge?): FRange

Merges cells in a range horizontally.

参数

参数类型描述
defaultMerge?booleanIf true, only the value in the upper left cell is retained.

返回

FRange

This range, for chaining

示例

// Assume the active sheet is a new sheet with no merged cells. const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:B2'); fRange.mergeAcross(); // There will be two merged cells. A1:B1 and A2:B2. const mergeData = fWorksheet.getMergeData(); mergeData.forEach((item) => { console.log(item.getA1Notation()); });

mergeVertically()

mergeVertically(defaultMerge?): FRange

Merges cells in a range vertically.

参数

参数类型描述
defaultMerge?booleanIf true, only the value in the upper left cell is retained.

返回

FRange

This range, for chaining

示例

// Assume the active sheet is a new sheet with no merged cells. const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:B2'); fRange.mergeVertically(); // There will be two merged cells. A1:A2 and B1:B2. const mergeData = fWorksheet.getMergeData(); mergeData.forEach((item) => { console.log(item.getA1Notation()); });

moveConditionalFormattingRule()

moveConditionalFormattingRule( cfId, toCfId, type?): FRange

Modify the priority of the conditional format

参数

参数类型描述
cfIdstringRules that need to be moved
toCfIdstringTarget rule
type?IAnchorAfter the default move to the destination rule, if type = before moves to the front, the default value is after

返回

FRange

Returns the current range instance for method chaining

已被弃用

use same API in FWorkSheet.

Memberof

FRangeConditionalFormattingMixin


offset()

调用签名

offset(rowOffset, columnOffset): FRange

Returns a new range that is offset from this range by the given number of rows and columns (which can be negative). The new range is the same size as the original range.

参数
参数类型描述
rowOffsetnumberThe number of rows down from the range’s top-left cell; negative values represent rows up from the range’s top-left cell.
columnOffsetnumberThe number of columns right from the range’s top-left cell; negative values represent columns left from the range’s top-left cell.
返回

FRange

The new range.

示例
const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:B2'); console.log(fRange.getA1Notation()); // A1:B2 // Offset the range by 1 row and 1 column const newRange = fRange.offset(1, 1); console.log(newRange.getA1Notation()); // B2:C3

调用签名

offset( rowOffset, columnOffset, numRows): FRange

Returns a new range that is relative to the current range, whose upper left point is offset from the current range by the given rows and columns, and with the given height in cells.

参数
参数类型描述
rowOffsetnumberThe number of rows down from the range’s top-left cell; negative values represent rows up from the range’s top-left cell.
columnOffsetnumberThe number of columns right from the range’s top-left cell; negative values represent columns left from the range’s top-left cell.
numRowsnumberThe height in rows of the new range.
返回

FRange

The new range.

示例
const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:B2'); console.log(fRange.getA1Notation()); // A1:B2 // Offset the range by 1 row and 1 column, and set the height of the new range to 3 const newRange = fRange.offset(1, 1, 3); console.log(newRange.getA1Notation()); // B2:C4

removeThemeStyle()

removeThemeStyle(themeName): void

Remove the theme style for the range.

参数

参数类型描述
themeNamestringThe name of the theme style to remove.

返回

void

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:E20'); fRange.removeThemeStyle('default');

setBackground()

setBackground(color): FRange

Set background color for current range.

参数

参数类型描述
colorstringThe background color

返回

FRange

This range, for chaining

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:B2'); fRange.setBackground('red');

setBackgroundColor()

setBackgroundColor(color): FRange

Set background color for current range.

参数

参数类型描述
colorstringThe background color

返回

FRange

This range, for chaining

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:B2'); fRange.setBackgroundColor('red');

setBorder()

setBorder( type, style, color?): FRange

Sets basic border properties for the current range.

参数

参数类型描述
typeBorderTypeThe type of border to apply
styleBorderStyleTypesThe border style
color?stringOptional border color in CSS notation

返回

FRange

This range, for chaining

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:B2'); fRange.setBorder(univerAPI.Enum.BorderType.ALL, univerAPI.Enum.BorderStyleTypes.THIN, '#ff0000');

setConditionalFormattingRule()

setConditionalFormattingRule(cfId, rule): FRange

Set the conditional format according to cfId

参数

参数类型描述
cfIdstring
ruleIConditionFormattingRule

返回

FRange

Returns the current range instance for method chaining

已被弃用

use same API in FWorkSheet.

Memberof

IFRangeConditionalFormattingMixin


setCustomMetaData()

setCustomMetaData(data): FRange

Set custom meta data for first cell in current range.

参数

参数类型描述
dataCustomDataThe custom meta data

返回

FRange

This range, for chaining

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:B2'); fRange.setCustomMetaData({ key: 'value' }); console.log(fRange.getCustomMetaData());

setCustomMetaDatas()

setCustomMetaDatas(datas): FRange

Set custom meta data for current range.

参数

参数类型描述
datas…[][]The custom meta data

返回

FRange

This range, for chaining

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:B2'); fRange.setCustomMetaDatas([ [{ key: 'value' }, { key: 'value2' }], [{ key: 'value3' }, { key: 'value4' }], ]); console.log(fRange.getCustomMetaDatas());

setDataValidation()

setDataValidation(rule): FRange

Set a data validation rule to current range. if rule is null, clear data validation rule.

参数

参数类型描述
ruleNullable<FDataValidation>data validation rule, build by FUniver.newDataValidation

返回

FRange

current range

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); // Create a data validation rule that requires a number between 1 and 10 for the range A1:B10 const fRange = fWorksheet.getRange('A1:B10'); const rule = univerAPI.newDataValidation() .requireNumberBetween(1, 10) .setOptions({ allowBlank: true, showErrorMessage: true, error: 'Please enter a number between 1 and 10' }) .build(); fRange.setDataValidation(rule);

setFontColor()

setFontColor(color): this

Sets the font color in CSS notation (such as ‘#ffffff’ or ‘white’).

参数

参数类型描述
colorstringThe font color in CSS notation (such as ‘#ffffff’ or ‘white’); a null value resets the color.

返回

this

This range, for chaining

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:B2'); fRange.setFontColor('#ff0000');

setFontFamily()

setFontFamily(fontFamily): this

Sets the font family, such as “Arial” or “Helvetica”.

参数

参数类型描述
fontFamilystringThe font family to set; a null value resets the font family.

返回

this

This range, for chaining

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:B2'); fRange.setFontFamily('Arial');

setFontLine()

setFontLine(fontLine): this

Sets the font line style of the given range (‘underline’, ‘line-through’, or ‘none’).

参数

参数类型描述
fontLineFontLineThe font line style, either ‘underline’, ‘line-through’, or ‘none’; a null value resets the font line style.

返回

this

This range, for chaining

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:B2'); fRange.setFontLine('underline');

setFontSize()

setFontSize(size): this

Sets the font size, with the size being the point size to use.

参数

参数类型描述
sizenumberA font size in point size. A null value resets the font size.

返回

this

This range, for chaining

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:B2'); fRange.setFontSize(24);

setFontStyle()

setFontStyle(fontStyle): this

Sets the font style for the given range (‘italic’ or ‘normal’).

参数

参数类型描述
fontStyleFontStyleThe font style, either ‘italic’ or ‘normal’; a null value resets the font style.

返回

this

This range, for chaining

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:B2'); fRange.setFontStyle('italic');

setFontWeight()

setFontWeight(fontWeight): this

Sets the font weight for the given range (normal/bold),

参数

参数类型描述
fontWeightFontWeight$1The font weight, either ‘normal’ or ‘bold’; a null value resets the font weight.

返回

this

This range, for chaining

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:B2'); fRange.setFontWeight('bold');

setFormula()

setFormula(formula): FRange

Updates the formula for this range. The given formula must be in A1 notation.

参数

参数类型描述
formulastringA string representing the formula to set for the cell.

返回

FRange

This range instance for chaining.

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1'); fRange.setFormula('=SUM(A2:A5)'); console.log(fRange.getFormula()); // '=SUM(A2:A5)'

setFormulas()

setFormulas(formulas): FRange

Sets a rectangular grid of formulas (must match dimensions of this range). The given formulas must be in A1 notation.

参数

参数类型描述
formulas…[][]A two-dimensional string array of formulas.

返回

FRange

This range instance for chaining.

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:B2'); fRange.setFormulas([ ['=SUM(A2:A5)', '=SUM(B2:B5)'], ['=SUM(A6:A9)', '=SUM(B6:B9)'], ]); console.log(fRange.getFormulas()); // [['=SUM(A2:A5)', '=SUM(B2:B5)'], ['=SUM(A6:A9)', '=SUM(B6:B9)']]

setHorizontalAlignment()

setHorizontalAlignment(alignment): FRange

Set the horizontal (left to right) alignment for the given range (left/center/right).

参数

参数类型描述
alignmentFHorizontalAlignmentThe horizontal alignment

返回

FRange

this range, for chaining

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:B2'); fRange.setHorizontalAlignment('left');

setHyperLink(url, label?): Promise<boolean>

参数

参数类型
urlstring
label?string

返回

Promise<boolean>

已被弃用

use range.setRichTextValueForCell(univerAPI.newRichText().insertLink(label, url)) instead

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); // Create a hyperlink to Univer on cell A1 const fRange = fWorksheet.getRange('A1'); const richText = univerAPI.newRichText().insertLink('Univer', 'https://univer.ai/'); fRange.setRichTextValueForCell(richText);

setNumberFormat()

setNumberFormat(pattern): FRange

Set the number format of the range.

参数

参数类型描述
patternstringThe number format pattern.

返回

FRange

The FRange instance for chaining.

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); // Set the number format of the A1 cell to '#,##0.00'. const fRange = fWorksheet.getRange('A1'); fRange.setValue(1234.567).setNumberFormat('#,##0.00'); console.log(fRange.getDisplayValue()); // 1,234.57

setNumberFormats()

setNumberFormats(patterns): FRange

Sets a rectangular grid of number formats (must match dimensions of this range).

参数

参数类型描述
patterns…[][]A two-dimensional array of number formats.

返回

FRange

The FRange instance for chaining.

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); // Set the number formats of the A1:B2 range. const fRange = fWorksheet.getRange('A1:B2'); fRange.setValues([ [1234.567, 0.1234], [45658, 0.9876] ]).setNumberFormats([ ['#,##0.00', '0.00%'], ['yyyy-MM-DD', ''] ]); console.log(fRange.getDisplayValues()); // [['1,234.57', '12.34%'], ['2025-01-01', 0.9876]]

setRichTextValueForCell()

setRichTextValueForCell(value): FRange

Set the rich text value for the cell at the start of this range.

参数

参数类型描述
valueanyThe rich text value

返回

FRange

The range

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:B2'); console.log(fRange.getValue(true)); // Set A1 cell value to rich text const richText = univerAPI.newRichText() .insertText('Hello World') .setStyle(0, 1, { bl: 1, cl: { rgb: '#c81e1e' } }) .setStyle(6, 7, { bl: 1, cl: { rgb: '#c81e1e' } }); fRange.setRichTextValueForCell(richText); console.log(fRange.getValue(true).toPlainText()); // Hello World

setRichTextValues()

setRichTextValues(values): FRange

Set the rich text value for the cells in the range.

参数

参数类型描述
values…[][]The rich text value

返回

FRange

The range

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:B2'); console.log(fRange.getValue(true)); // Set A1:B2 cell value to rich text const richText = univerAPI.newRichText() .insertText('Hello World') .setStyle(0, 1, { bl: 1, cl: { rgb: '#c81e1e' } }) .setStyle(6, 7, { bl: 1, cl: { rgb: '#c81e1e' } }); fRange.setRichTextValues([ [richText, richText], [null, null] ]); console.log(fRange.getValue(true).toPlainText()); // Hello World

setTextRotation()

setTextRotation(rotation): FRange

Set rotation for text in current range.

参数

参数类型描述
rotationnumberThe rotation angle in degrees

返回

FRange

This range, for chaining

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:B2'); fRange.setTextRotation(45);

setValue()

setValue(value): FRange

Sets the value of the range.

参数

参数类型描述
value| ICellData | CellValueThe value can be a number, string, boolean, or standard cell format. If it begins with =, it is interpreted as a formula. The value is tiled to all cells in the range.

返回

FRange

This range, for chaining

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('B2'); fRange.setValue(123); // or fRange.setValue({ v: 234, s: { bg: { rgb: '#ff0000' } } });

setValueForCell()

setValueForCell(value): FRange

Set new value for current cell, first cell in this range.

参数

参数类型描述
value| ICellData | CellValueThe value can be a number, string, boolean, or standard cell format. If it begins with =, it is interpreted as a formula. The value is tiled to all cells in the range.

返回

FRange

This range, for chaining

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:B2'); fRange.setValueForCell(123); // or fRange.setValueForCell({ v: 234, s: { bg: { rgb: '#ff0000' } } });

setValues()

setValues(value): FRange

Sets a different value for each cell in the range. The value can be a two-dimensional array or a standard range matrix (must match the dimensions of this range), consisting of numbers, strings, Boolean values or Composed of standard cell formats. If a value begins with =, it is interpreted as a formula.

参数

参数类型描述
value| IObjectMatrixPrimitiveType<…> | …[] | IObjectMatrixPrimitiveType<…> | …[]The value can be a two-dimensional array or a standard range matrix (must match the dimensions of this range), consisting of numbers, strings, Boolean values or Composed of standard cell formats.

返回

FRange

This range, for chaining

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:B2'); fRange.setValues([ [1, { v: 2, s: { bg: { rgb: '#ff0000' } } }], [3, 4] ]);

setVerticalAlignment()

setVerticalAlignment(alignment): FRange

Set the vertical (top to bottom) alignment for the given range (top/middle/bottom).

参数

参数类型描述
alignmentFVerticalAlignmentThe vertical alignment

返回

FRange

this range, for chaining

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:B2'); fRange.setVerticalAlignment('top');

setWrap()

setWrap(isWrapEnabled): FRange

Set the cell wrap of the given range. Cells with wrap enabled (the default) resize to display their full content. Cells with wrap disabled display as much as possible in the cell without resizing or running to multiple lines.

参数

参数类型描述
isWrapEnabledbooleanWhether to enable wrap

返回

FRange

this range, for chaining

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:B2'); fRange.setWrap(true); console.log(fRange.getWrap());

setWrapStrategy()

setWrapStrategy(strategy): FRange

Sets the text wrapping strategy for the cells in the range.

参数

参数类型描述
strategyWrapStrategyThe text wrapping strategy

返回

FRange

this range, for chaining

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:B2'); fRange.setWrapStrategy(univerAPI.Enum.WrapStrategy.WRAP); console.log(fRange.getWrapStrategy());

sort()

sort(column): FRange

Sorts the cells in the given range, by column(s) and order specified.

参数

参数类型描述
columnSortColumnSpec | …[]The column index with order or an array of column indexes with order. The range first column index is 0.

返回

FRange

The range itself for chaining.

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('D1:G10'); // Sorts the range by the first column in ascending order. fRange.sort(0); // Sorts the range by the first column in descending order. fRange.sort({ column: 0, ascending: false }); // Sorts the range by the first column in descending order and the second column in ascending order. fRange.sort([{ column: 0, ascending: false }, 1]);

splitTextToColumns()

调用签名

splitTextToColumns(treatMultipleDelimitersAsOne?): void

Splits a column of text into multiple columns based on an auto-detected delimiter.

参数
参数类型描述
treatMultipleDelimitersAsOne?booleanWhether to treat multiple continuous delimiters as one. The default value is false.
返回

void

示例
const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); // A1:A3 has following values: // A | // 1,2,3 | // 4,,5,6 | const fRange = fWorksheet.getRange('A1:A3'); fRange.setValues([ ['A'], ['1,2,3'], ['4,,5,6'] ]); // After calling splitTextToColumns(true), the range will be: // A | | // 1 | 2 | 3 // 4 | 5 | 6 fRange.splitTextToColumns(true); // After calling splitTextToColumns(false), the range will be: // A | | | // 1 | 2 | 3 | // 4 | | 5 | 6 fRange.splitTextToColumns(false);

调用签名

splitTextToColumns(treatMultipleDelimitersAsOne?, delimiter?): void

Splits a column of text into multiple columns based on a specified delimiter.

参数
参数类型描述
treatMultipleDelimitersAsOne?booleanWhether to treat multiple continuous delimiters as one. The default value is false.
delimiter?SplitDelimiterEnumThe delimiter to use to split the text. The default delimiter is Tab(1)、Comma(2)、Semicolon(4)、Space(8)、Custom(16).A delimiter like 6 (SplitDelimiterEnum.Comma
返回

void

示例
const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); // A1:A3 has following values: // A | // 1;;2;3 | // 1;,2;3 | const fRange = fWorksheet.getRange('A1:A3'); fRange.setValues([ ['A'], ['1;;2;3'], ['1;,2;3'] ]); // After calling splitTextToColumns(false, univerAPI.Enum.SplitDelimiterType.Semicolon|univerAPI.Enum.SplitDelimiterType.Comma), the range will be: // A | | | // 1 | | 2 | 3 // 1 | | 2 | 3 fRange.splitTextToColumns(false, univerAPI.Enum.SplitDelimiterType.Semicolon|univerAPI.Enum.SplitDelimiterType.Comma); // After calling splitTextToColumns(true, univerAPI.Enum.SplitDelimiterType.Semicolon|univerAPI.Enum.SplitDelimiterType.Comma), the range will be: // A | | // 1 | 2 | 3 // 1 | 2 | 3 fRange.splitTextToColumns(true, univerAPI.Enum.SplitDelimiterType.Semicolon|univerAPI.Enum.SplitDelimiterType.Comma);

updateHyperLink( id, url, label?): Promise<boolean>

参数

参数类型
idstring
urlstring
label?string

返回

Promise<boolean>

已被弃用

use range.setRichTextValueForCell(range.getValue(true).copy().updateLink(id, url)) instead

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1'); const richText = univerAPI.newRichText().insertLink('Univer', 'https://univer.ai/'); fRange.setRichTextValueForCell(richText); // Update hyperlink after 3 seconds setTimeout(() => { const cellValue = fRange.getValue(true); const hyperlinks = cellValue.getLinks(); const id = hyperlinks[0].rangeId; const newUrl = 'https://insight.univer.ai/'; const newRichText = cellValue.copy().updateLink(id, newUrl); fRange.setRichTextValueForCell(newRichText); }, 3000);

useThemeStyle()

useThemeStyle(themeName): void

Set the theme style for the range.

参数

参数类型描述
themeNamestringThe name of the theme style to apply.If a undefined value is passed, the theme style will be removed if it exist.

返回

void

示例

const fWorkbook = univerAPI.getActiveWorkbook(); const fWorksheet = fWorkbook.getActiveSheet(); const fRange = fWorksheet.getRange('A1:E20'); fRange.useThemeStyle('default');