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.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();
const fRange = fWorksheet.getRange('A1:B2');
fRange.activate();
const cell = fWorksheet.getRange('B2');
cell.activateAsCurrentCell(); // the active cell will be B2
console.log(fWorksheet.getActiveRange().getA1Notation()); // B2

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.

示例

const range = univerAPI.getActiveWorkbook()
 .getActiveSheet()
 .getActiveRange();
 
const comment = univerAPI.newTheadComment()
 .setContent(univerAPI.newRichText().insertText('hello zhangsan'));
const success = await range.addCommentAsync(comment);

addConditionalFormattingRule()

addConditionalFormattingRule(rule): FRange

Add a new conditional format

参数

参数类型描述
ruleIConditionFormattingRule

返回

FRange

Returns the current range instance for method chaining

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.

示例

let sheet = univerAPI.getActiveWorkbook().getActiveSheet();
let range = sheet.getRange(2, 2, 3, 3);
range.attachAlertPopup({ message: 'This is an alert', type: 'warning' });

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.

示例

univerAPI.getComponentManager().register(
	'myPopup',
	() => React.createElement('div', {
		style: {
			color: 'red',
			fontSize: '14px'
		}
	}, 'Custom Popup')
);

let sheet = univerAPI.getActiveWorkbook().getActiveSheet();
let range = sheet.getRange(2, 2, 3, 3);
univerAPI.getActiveWorkbook().setActiveRange(range);
let disposable = range.attachPopup({
	componentKey: 'myPopup'
});

attachRangePopup()

attachRangePopup(popup): any

Attach a DOM popup to the current range.

参数

参数类型
popupIFCanvasPopup

返回

any

The disposable object to detach the alert.

示例

let sheet = univerAPI.getActiveWorkbook().getActiveSheet();
let range = sheet.getRange(2, 2, 3, 3);
univerAPI.getActiveWorkbook().setActiveRange(range);
 
univerAPI.getComponentManager().register(
	'myPopup',
	() => React.createElement('div', {
		style: {
			background: 'red',
			fontSize: '14px'
		}
	}, 'Custom Popup')
);
let disposable = range.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);

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 clearComments 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();

createConditionalFormattingRule()

createConditionalFormattingRule(): FConditionalFormattingBuilder

Creates a constructor for conditional formatting

返回

FConditionalFormattingBuilder

The conditional formatting builder

示例

const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();
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);

deleteConditionalFormattingRule()

deleteConditionalFormattingRule(cfId): FRange

Delete conditional format according to cfId

参数

参数类型描述
cfIdstring

返回

FRange

Returns the current range instance for method chaining

Memberof

IFRangeConditionalFormattingMixin

示例

 const workbook = univerAPI.getActiveWorkbook();
 const worksheet = workbook?.getActiveSheet();
 const rules = worksheet?.getConditionalFormattingRules();
 worksheet?.deleteConditionalFormattingRule(rules![0].cfId);

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

示例

let sheet = univerAPI.getActiveWorkbook().getActiveSheet();
sheet.getRange(5, 7).generateHTML();

getA1Notation()

getA1Notation(withSheet?): string

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

参数

参数类型描述
withSheet?booleanIf true, the sheet name is included in the A1 notation.

返回

string

The A1 notation of the range.

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

getCell()

getCell(this): ICellWithCoord

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

参数

参数类型
thisFRange

返回

ICellWithCoord

cell location and coordinate.

示例

let sheet = univerAPI.getActiveWorkbook().getActiveSheet();
sheet.getRange(5, 7).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

示例

let sheet = univerAPI.getActiveWorkbook().getActiveSheet();
sheet.getRange(5, 7).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 number of the applied area

返回

number

The starting column number of the area

示例

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

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();
const fRange = fWorksheet.getRange('A1:T100');
const rule = fWorksheet.newConditionalFormattingRule()
  .whenCellNotEmpty()
  .setRanges([fRange.getRange()])
  .setItalic(true)
  .setBackground('red')
  .setFontColor('green')
  .build();
fWorksheet.addConditionalFormattingRule(rule);
 
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());

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();
const fRange = fWorksheet.getRange('A1:B10');
const rule = univerAPI.newDataValidation()
  .requireNumberEqualTo(20)
  .build();
fRange.setDataValidation(rule);
console.log(fRange.getDataValidation().getCriteriaValues());
 
fRange.getDataValidation().setCriteria(
  univerAPI.Enum.DataValidationType.DECIMAL,
  [univerAPI.Enum.DataValidationOperator.BETWEEN, '1', '10']
);
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();
const fRange1 = fWorksheet.getRange('A1:B10');
const rule1 = univerAPI.newDataValidation()
  .requireNumberEqualTo(20)
  .build();
fRange1.setDataValidation(rule1);
 
const fRange2 = fWorksheet.getRange('C1:D10');
const rule2 = univerAPI.newDataValidation()
  .requireNumberBetween(1, 10)
  .build();
fRange2.setDataValidation(rule2);
 
const range = fWorksheet.getRange('A1:D10');
const rules = range.getDataValidations();
console.log(rules.length); // 2

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);

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 for the top left cell of the range.

返回

string

The horizontal alignment

示例

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

返回

ICellHyperLink[]

已被弃用

use range.setRichTextValueForCell(range.getValue(true).getLinks()) 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);
 
// Get hyperlinks
console.log(fRange.getValue(true).getLinks());

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);

getRow()

getRow(): number

Gets the starting row number of the applied area

返回

number

The starting row number of the area

示例

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

getScreenshot()

getScreenshot(): string | false

get screenshot of this range

返回

string | false

示例

univerAPI.getActiveUniverSheet()
 .getActiveSheet()
 .getActiveRange()
 .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();
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]
]);
const rule = univerAPI.newDataValidation()
  .requireNumberBetween(1, 10)
  .build();
fRange.setDataValidation(rule);
 
const status = await fWorksheet.getRange('B2').getValidatorStatus();
console.log(status?.[0]?.[0]); // 'valid'
 
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 for the top left cell of the range.

返回

string

The vertical alignment

示例

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

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

Returns true if the cell wrap is enabled for the top left cell of the range.

返回

boolean

True if the cell wrap is enabled

示例

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

getWrapStrategy()

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

示例

let sheet = univerAPI.getActiveWorkbook().getActiveSheet();
let range = sheet.getRange(2, 2, 3, 3);
range.highlight({ stroke: 'red' }, { startRow: 2, startColumn: 2 });

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

Memberof

FRangeConditionalFormattingMixin

示例

const workbook = univerAPI.getActiveWorkbook();
const worksheet = workbook?.getActiveSheet();
const rules = worksheet?.getConditionalFormattingRules()!;
const rule = rules[2];
const targetRule = rules[0];
worksheet?.moveConditionalFormattingRule(rule.cfId, targetRule.cfId, 'before');

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

Memberof

IFRangeConditionalFormattingMixin

示例

  const workbook = univerAPI.getActiveWorkbook();
  const worksheet = workbook?.getActiveSheet();
  const rules = worksheet?.getConditionalFormattingRules()!;
  const rule = rules[0];
  worksheet?.setConditionalFormattingRule(rule.cfId, { ...rule, ranges: [] });

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();
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');

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();
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();
const fRange = fWorksheet.getRange('A1');
fRange.setValue(1234.567).setNumberFormat('#,##0.00');
console.log(fRange.getValue()); // 1,234.57

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 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

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 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.setRichTextValues([
  [richText, richText],
  [null, null]
]);
console.log(fRange.getValue(true).toPlainText()); // Hello World

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 column index starts from 1.

返回

FRange

The range itself for chaining.

示例

const activeSpreadsheet = univerAPI.getActiveWorkbook();
const activeSheet = activeSpreadsheet.getActiveSheet();
const range = activeSheet.getRange(0, 0, 10, 10);
range.sort(1); // Sorts the range by the first column in ascending order.
range.sort({ column: 1, ascending: false }); // Sorts the range by the first column in descending order.
range.sort([{ column: 1, ascending: false }, 2]); // Sorts the range by the first column in descending order and the second column in ascending order.

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      | B | C
//  1,2,3  |   |
//  4,,5,6 |   |
const fRange = fWorksheet.getRange('A1:A3');
fRange.setValues([
  ['A', 'B', 'C'],
  ['1,2,3', null, null],
  ['4,,5,6', null, null]
]);
 
// 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   | B | C
//  1;;2;3 |   |
//  1;,2;3 |   |
const fRange = fWorksheet.getRange('A1:A3');
fRange.setValues([
  ['A', 'B', 'C'],
  ['1;;2;3', null, null],
  ['1;,2;3', null, null]
]);
 
// 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://go.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');
FPivotTableFSelection