Build your own AI office with Univer Office SDK. Bring editing, calculation, collaboration, and file processing into your product for people and AI agents. Built on an extensible open-source core.
TypeScript boardcollaborationdocdocxexceloffce365officepdfpptpptxpresentationsdksheetslidesspreadsheetuniverwordwordprocessorworkspacexlsx
/**
* Copyright 2023-present DreamNum Co., Ltd.
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
import type {
AbsoluteRefType,
BorderStyleTypes,
BorderType,
CellValue,
CustomData,
ICellData,
IColorStyle,
IDocumentData,
IObjectMatrixPrimitiveType,
IRange,
IStyleData,
ITextDecoration,
Nullable,
Workbook,
Worksheet,
} from '@univerjs/core';
import type {
AUTO_FILL_APPLY_TYPE,
IMergeCellsUtilOptions,
ISetBorderBasicCommandParams,
ISetHorizontalTextAlignCommandParams,
ISetRangeCustomMetadataCommandParams,
ISetRangeValuesCommandParams,
ISetSelectionsOperationParams,
ISetShrinkToFitCommandParams,
ISetStyleCommandParams,
ISetTextRotationCommandParams,
ISetTextWrapCommandParams,
ISetVerticalTextAlignCommandParams,
IStyleTypeValue,
SplitDelimiterEnum,
} from '@univerjs/sheets';
import {
BooleanNumber,
covertCellValue,
covertCellValues,
DEFAULT_STYLES,
Dimension,
getDisplayValueFromCell,
getOriginCellValue,
ICommandService,
ILogService,
Inject,
Injector,
isNullCell,
Rectangle,
RichTextValue,
TextStyleValue,
WrapStrategy,
} from '@univerjs/core';
import { FBaseInitialable } from '@univerjs/core/facade';
import { FormulaDataModel, serializeRange, serializeRangeWithSheet } from '@univerjs/engine-formula';
import {
addMergeCellsUtil,
AutoFillCommand,
ClearSelectionAllCommand,
ClearSelectionContentCommand,
ClearSelectionFormatCommand,
DeleteRangeMoveLeftCommand,
DeleteRangeMoveUpCommand,
DeleteWorksheetRangeThemeStyleCommand,
getAddMergeMutationRangeByType,
getPrimaryForRange,
InsertRangeMoveDownCommand,
InsertRangeMoveRightCommand,
RemoveWorksheetMergeCommand,
SetBorderBasicCommand,
SetHorizontalTextAlignCommand,
SetRangeCustomMetadataCommand,
SetRangeValuesCommand,
SetSelectionsOperation,
SetShrinkToFitCommand,
SetStyleCommand,
SetTextRotationCommand,
SetTextWrapCommand,
SetVerticalTextAlignCommand,
SetWorksheetRangeThemeStyleCommand,
SheetRangeThemeService,
SplitTextToColumnsCommand,
} from '@univerjs/sheets';
import type { IFacadeClearOptions } from './f-worksheet';
import type { FHorizontalAlignment, FVerticalAlignment } from './utils';
import { SHEETS_CUSTOM_FIELD_WARNING_MESSAGE } from './const';
import { FWorkbook } from './f-workbook';
import { FWorksheet } from './f-worksheet';
import { FRangePermission } from './permission/f-range-permission';
import {
transformCoreHorizontalAlignment,
transformCoreVerticalAlignment,
transformFacadeHorizontalAlignment,
transformFacadeVerticalAlignment,
} from './utils';
export type FontLine = 'none' | 'underline' | 'line-through';
export type FontStyle = 'normal' | 'italic';
export type FontWeight = 'normal' | 'bold';
/**
* The type of the style to get.
*
* - `row`: get composed style of row, col and default worksheet style. Row style has the highest priority, then col style.
* - `col`: get composed style of col, row and default worksheet style. Col style has the highest priority, then row style.
* - `cell`: get style of cell without merging row style, col style and default worksheet style.
*/
export type GetStyleType = 'row' | 'col' | 'cell';
/**
* Represents a range of cells in a sheet. You can call methods on this Facade API object
* to read contents or manipulate the range.
*
* @hideconstructor
*/
export class FRange extends FBaseInitialable {
static { this._enableManualInit(); }
constructor(
protected readonly _workbook: Workbook,
protected readonly _worksheet: Worksheet,
protected readonly _range: IRange,
@Inject(Injector) protected override readonly _injector: Injector,
@ICommandService protected readonly _commandService: ICommandService,
@Inject(FormulaDataModel) protected readonly _formulaDataModel: FormulaDataModel,
@ILogService protected readonly _logService: ILogService
) {
super(_injector);
const maxRows = this._worksheet.getRowCount();
const maxColumns = this._worksheet.getColumnCount();
if (
this._range.startRow < 0 ||
this._range.startColumn < 0 ||
this._range.endRow >= maxRows ||
this._range.endColumn >= maxColumns
) {
throw new Error(`Range is out of bounds. Max rows: ${maxRows}, Max columns: ${maxColumns}, Given range: ${JSON.stringify(this._range)}`);
}
this._runInitializers(
this._injector,
this._workbook,
this._worksheet,
this._range,
this._commandService,
this._formulaDataModel
);
}
/**
* Get the unit ID of the current workbook
* @returns {string} The unit ID of the workbook
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* const fRange = fWorksheet.getRange('A1:B2');
* console.log(fRange.getUnitId());
* ```
*/
getUnitId(): string {
return this._workbook.getUnitId();
}
/**
* Gets the name of the worksheet
* @returns {string} The name of the worksheet
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* const fRange = fWorksheet.getRange('A1:B2');
* console.log(fRange.getSheetName());
* ```
*/
getSheetName(): string {
return this._worksheet.getName();
}
/**
* Gets the ID of the worksheet
* @returns {string} The ID of the worksheet
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* const fRange = fWorksheet.getRange('A1:B2');
* console.log(fRange.getSheetId());
* ```
*/
getSheetId(): string {
return this._worksheet.getSheetId();
}
/**
* Gets the area where the statement is applied
* @returns {IRange} The area where the statement is applied
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* const fRange = fWorksheet.getRange('A1:B2');
* const range = fRange.getRange();
* const { startRow, startColumn, endRow, endColumn } = range;
* console.log(range);
* ```
*/
getRange(): IRange {
return this._range;
}
/**
* Gets the starting row index of the range. index starts at 0.
* @returns {number} The starting row index of the range.
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* const fRange = fWorksheet.getRange('A1:B2');
* console.log(fRange.getRow()); // 0
* ```
*/
getRow(): number {
return this._range.startRow;
}
/**
* Gets the ending row index of the range. index starts at 0.
* @returns {number} The ending row index of the range.
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* const fRange = fWorksheet.getRange('A1:B2');
* console.log(fRange.getLastRow()); // 1
* ```
*/
getLastRow(): number {
return this._range.endRow;
}
/**
* Gets the starting column index of the range. index starts at 0.
* @returns {number} The starting column index of the range.
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* const fRange = fWorksheet.getRange('A1:B2');
* console.log(fRange.getColumn()); // 0
* ```
*/
getColumn(): number {
return this._range.startColumn;
}
/**
* Gets the ending column index of the range. index starts at 0.
* @returns {number} The ending column index of the range.
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* const fRange = fWorksheet.getRange('A1:B2');
* console.log(fRange.getLastColumn()); // 1
* ```
*/
getLastColumn(): number {
return this._range.endColumn;
}
/**
* Returns the number of columns in this range.
* @returns {number} The column count, not a size in pixels.
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* const fRange = fWorksheet.getRange('A1:B2');
* console.log(fRange.getWidth());
* ```
*/
getWidth(): number {
return this._range.endColumn - this._range.startColumn + 1;
}
/**
* Returns the number of rows in this range.
* @returns {number} The row count, not a size in pixels.
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* const fRange = fWorksheet.getRange('A1:B2');
* console.log(fRange.getHeight());
* ```
*/
getHeight(): number {
return this._range.endRow - this._range.startRow + 1;
}
/**
* Checks whether this range exactly matches a merged cell range.
* @returns {boolean} `true` only for an exact merged range match. Use `isPartOfMerge()` to check overlap.
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* const fRange = fWorksheet.getRange('A1:B2');
* console.log(fRange.isMerged());
* // merge cells A1:B2
* fRange.merge();
* console.log(fRange.isMerged());
* ```
*/
isMerged(): boolean {
const { startColumn, startRow, endColumn, endRow } = this._range;
const mergedCells = this._worksheet.getMergedCellRange(startRow, startColumn, endRow, endColumn);
return mergedCells.some((range) => Rectangle.equals(range, this._range));
}
/**
* Return first cell style data in this range. Please note that if there are row styles, col styles and (or)
* worksheet style, they will be merged into the cell style. You can use `type` to specify the type of the style to get.
*
* @param {GetStyleType} [type] - The type of the style to get. 'row' means get the composed style of row, col and
* default worksheet style. 'col' means get the composed style of col, row and default worksheet style.
* 'cell' means get the style of cell without merging row style, col style and default worksheet style.
* Default is 'row'.
*
* @returns {IStyleData | null} The cell style data
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* const fRange = fWorksheet.getRange('A1:B2');
* console.log(fRange.getCellStyleData());
* ```
*/
getCellStyleData(type: GetStyleType = 'row'): IStyleData | null {
if (type !== 'cell') {
return this._worksheet.getComposedCellStyle(this._range.startRow, this._range.startColumn, type === 'row');
}
return this._worksheet.getCellStyle(this._range.startRow, this._range.startColumn) as IStyleData | null;
}
/**
* Get the font family of the cell.
*
* @param {GetStyleType} [type] - The type of the style to get. 'row' means get the composed style of row, col and
* default worksheet style. 'col' means get the composed style of col, row and default worksheet style.
* 'cell' means get the style of cell without merging row style, col style and default worksheet style.
* Default is 'row'.
*
* @returns {string | null} The font family of the cell
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* const fRange = fWorksheet.getRange('A1:B2');
* console.log(fRange.getFontFamily());
* ```
*/
getFontFamily(type: GetStyleType = 'row'): string | null {
return this.getCellStyleData(type)?.ff ?? null;
}
/**
* Get the font size of the cell.
*
* @param {GetStyleType} [type] - The type of the style to get. 'row' means get the composed style of row, col and
* default worksheet style. 'col' means get the composed style of col, row and default worksheet style.
* 'cell' means get the style of cell without merging row style, col style and default worksheet style.
* Default is 'row'.
*
* @returns {number | null} The font size of the cell
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* const fRange = fWorksheet.getRange('A1:B2');
* console.log(fRange.getFontSize());
* ```
*/
getFontSize(type: GetStyleType = 'row'): number | null {
return this.getCellStyleData(type)?.fs ?? null;
}
/**
* Return first cell style in this range.
*
* For multiple cells, create the target range with {@link FWorksheet.getRange} and call
* {@link FRange.getCellStyles} once instead of calling this method for each cell.
*
* @param {GetStyleType} [type] - The type of the style to get. 'row' means get the composed style of row, col and
* default worksheet style. 'col' means get the composed style of col, row and default worksheet style.
* 'cell' means get the style of cell without merging row style, col style and default worksheet style.
* Default is 'row'.
*
* @returns {TextStyleValue | null} The cell style
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* const fRange = fWorksheet.getRange('A1:B2');
* console.log(fRange.getCellStyle());
* ```
*/
getCellStyle(type: GetStyleType = 'row'): TextStyleValue | null {
const data = this.getCellStyleData(type);
return data ? TextStyleValue.create(data) : null;
}
/**
* Returns the cell styles for the cells in the range.
*
* @param {GetStyleType} [type] - The type of the style to get. 'row' means get the composed style of row, col and
* default worksheet style. 'col' means get the composed style of col, row and default worksheet style.
* 'cell' means get the style of cell without merging row style, col style and default worksheet style.
* Default is 'row'.
*
* @returns {Array<Array<TextStyleValue | null>>} A two-dimensional array of cell styles.
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* const fRange = fWorksheet.getRange('A1:B2');
* console.log(fRange.getCellStyles());
* ```
*/
getCellStyles(type: GetStyleType = 'row'): Array<Array<TextStyleValue | null>> {
const cells = this.getCellDatas();
return cells.map((row, rowIndex) => row.map((cell, colIndex) => {
if (!cell) return null;
const style = type !== 'cell'
// NOTE: this has potential performance issue. Because in the implementation of getComposedCellStyle,
// it will get default worksheet style, row styles and col styles, which can be cached.
? this._worksheet.getComposedCellStyle(rowIndex + this._range.startRow, colIndex + this._range.startColumn, type === 'row')
: this._worksheet.getCellStyle(rowIndex + this._range.startRow, colIndex + this._range.startColumn);
return style ? TextStyleValue.create(style) : null;
}));
}
/**
* Return first cell value in this range
*
* For multiple cells, create the target range with {@link FWorksheet.getRange} and call
* {@link FRange.getValues} once instead of calling this method for each cell.
*
* @returns {CellValue | null} The cell value
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* 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(): CellValue | null;
/**
* Return first cell value in this range
*
* For multiple cells, create the target range with {@link FWorksheet.getRange} and call
* {@link FRange.getValues} once instead of calling this method for each cell.
*
* @param {true} includeRichText Pass `true` to return a `RichTextValue` for rich-text content instead of plain text.
* @returns {CellValue | RichTextValue | null} The cell value
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* const fRange = fWorksheet.getRange('A1:B2');
* console.log(fRange.getValue(true));
*
* // set the first cell value to 123
* const richText = univerAPI.newRichText()
* .text('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
* ```
*/
getValue(includeRichText: true): Nullable<CellValue | RichTextValue>;
getValue(includeRichText?: boolean): Nullable<CellValue | RichTextValue> {
if (includeRichText) {
return this.getValueAndRichTextValue();
}
return this._worksheet.getCell(this._range.startRow, this._range.startColumn)?.v ?? null;
}
/**
* Returns the raw value of the top-left cell in the range. Empty cells return `null`.
*
* For multiple cells, create the target range with {@link FWorksheet.getRange} and call
* {@link FRange.getRawValues} once instead of calling this method for each cell.
*
* @returns {Nullable<CellValue>} The raw value of the cell. Returns `null` if the cell is empty.
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* const fRange = fWorksheet.getRange('A1:B2');
* fRange.setValueForCell({
* v: 0.2,
* s: {
* n: {
* pattern: '0%',
* },
* },
* });
* console.log(fRange.getRawValue()); // 0.2
* ```
*/
getRawValue(): Nullable<CellValue> {
const cell = this._worksheet.getCellMatrix().getValue(this._range.startRow, this._range.startColumn);
return getOriginCellValue(cell);
}
/**
* Returns the displayed value of the top-left cell in the range. The value is a String. Empty cells return an empty string.
*
* For multiple cells, create the target range with {@link FWorksheet.getRange} and call
* {@link FRange.getDisplayValues} once instead of calling this method for each cell.
*
* @returns {string} The displayed value of the cell. Returns an empty string if the cell is empty.
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* const fRange = fWorksheet.getRange('A1:B2');
* fRange.setValueForCell({
* v: 0.2,
* s: {
* n: {
* pattern: '0%',
* },
* },
* });
* console.log(fRange.getDisplayValue()); // 20%
* ```
*/
getDisplayValue(): string {
const cell = this._worksheet.getCell(this._range.startRow, this._range.startColumn);
return getDisplayValueFromCell(cell);
}
/**
* Returns the cell values for the cells in the range.
* @returns {Nullable<CellValue>[][]} A two-dimensional array of cell values.
* @example
* ```ts
* // Get plain values
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* const fRange = fWorksheet.getRange('A1:B2');
* console.log(fRange.getValues());
* ```
*/
getValues(): Nullable<CellValue>[][];
/**
* Returns the cell values for the cells in the range.
* @param {true} includeRichText Pass `true` to return `RichTextValue` entries for rich-text content instead of plain text.
* @returns {Nullable<RichTextValue | CellValue>[][]} A two-dimensional array of cell values.
* @example
* ```ts
* // Get values with rich text if available
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* const fRange = fWorksheet.getRange('A1:B2');
* console.log(fRange.getValues(true));
* ```
*/
getValues(includeRichText: true): (Nullable<RichTextValue | CellValue>)[][];
getValues(includeRichText?: true): (Nullable<RichTextValue | CellValue>)[][] {
if (includeRichText) {
return this.getValueAndRichTextValues();
}
const { startRow, endRow, startColumn, endColumn } = this._range;
const range: Array<Array<Nullable<CellValue>>> = [];
for (let r = startRow; r <= endRow; r++) {
const row: Array<Nullable<CellValue>> = [];
for (let c = startColumn; c <= endColumn; c++) {
row.push(this._worksheet.getCell(r, c)?.v ?? null);
}
range.push(row);
}
return range;
}
/**
* Returns a two-dimensional array of the range raw values. Empty cells return `null`.
* @returns {Array<Array<Nullable<CellValue>>>} The raw value of the cell. Returns `null` if the cell is empty.
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* 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]]
* ```
*/
getRawValues(): Array<Array<Nullable<CellValue>>> {
const cellMatrix = this._worksheet.getCellMatrix();
const { startRow, endRow, startColumn, endColumn } = this._range;
const values: Array<Array<Nullable<CellValue>>> = [];
for (let r = startRow; r <= endRow; r++) {
const row: Array<Nullable<CellValue>> = [];
for (let c = startColumn; c <= endColumn; c++) {
const cell = cellMatrix.getValue(r, c);
const rawValue = getOriginCellValue(cell);
row.push(rawValue);
}
values.push(row);
}
return values;
}
/**
* Returns a two-dimensional array of the range displayed values. Empty cells return an empty string.
* @returns {string[][]} A two-dimensional array of values.
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* 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', '']]
* ```
*/
getDisplayValues(): string[][] {
const { startRow, endRow, startColumn, endColumn } = this._range;
const values: string[][] = [];
for (let r = startRow; r <= endRow; r++) {
const row: string[] = [];
for (let c = startColumn; c <= endColumn; c++) {
const cell = this._worksheet.getCell(r, c);
const displayValue = getDisplayValueFromCell(cell);
row.push(displayValue);
}
values.push(row);
}
return values;
}
/**
* Return first cell model data in this range
*
* For multiple cells, create the target range with {@link FWorksheet.getRange} and call
* {@link FRange.getCellDataGrid} once instead of calling this method for each cell.
*
* @returns {ICellData | null} The cell model data
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* const fRange = fWorksheet.getRange('A1:B2');
* console.log(fRange.getCellData());
* ```
*/
getCellData(): ICellData | null {
return this._worksheet.getCell(this._range.startRow, this._range.startColumn) ?? null;
}
/**
* Alias for getCellDataGrid.
* @returns {Nullable<ICellData>[][]} A two-dimensional array of cell data.
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* const fRange = fWorksheet.getRange('A1:B2');
* console.log(fRange.getCellDatas());
* ```
*/
getCellDatas(): Nullable<ICellData>[][] {
return this.getCellDataGrid();
}
/**
* Returns the cell data for the cells in the range.
* @returns {Nullable<ICellData>[][]} A two-dimensional array of cell data.
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* const fRange = fWorksheet.getRange('A1:B2');
* console.log(fRange.getCellDataGrid());
* ```
*/
getCellDataGrid(): Nullable<ICellData>[][] {
const { startRow, endRow, startColumn, endColumn } = this._range;
const range: Nullable<ICellData>[][] = [];
for (let r = startRow; r <= endRow; r++) {
const row: Nullable<ICellData>[] = [];
for (let c = startColumn; c <= endColumn; c++) {
row.push(this._worksheet.getCellRaw(r, c));
}
range.push(row);
}
return range;
}
/**
* Returns the rich text value for the cell at the start of this range.
* @returns {Nullable<RichTextValue>} The rich text value
* @internal
* @beta
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* const fRange = fWorksheet.getRange('A1:B2');
* console.log(fRange.getRichTextValue());
* ```
*/
private getRichTextValue(): Nullable<RichTextValue> {
const data = this.getCellData();
if (data?.p) {
return new RichTextValue(data.p);
}
return null;
}
/**
* Returns the rich text value for the cells in the range.
* @returns {Nullable<RichTextValue>[][]} A two-dimensional array of RichTextValue objects.
* @internal
* @beta
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* const fRange = fWorksheet.getRange('A1:B2');
* console.log(fRange.getRichTextValues());
* ```
*/
private getRichTextValues(): Nullable<RichTextValue>[][] {
const dataGrid = this.getCellDataGrid();
return dataGrid.map((row) => row.map((data) => data?.p ? new RichTextValue(data.p) : null));
}
/**
* Returns the value and rich text value for the cell at the start of this range.
* @returns {Nullable<CellValue | RichTextValue>} The value and rich text value
* @internal
* @beta
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* const fRange = fWorksheet.getRange('A1:B2');
* console.log(fRange.getValueAndRichTextValue());
* ```
*/
private getValueAndRichTextValue(): Nullable<CellValue | RichTextValue> {
const cell = this.getCellData();
return cell?.p ? new RichTextValue(cell.p) : cell?.v;
}
/**
* Returns the value and rich text value for the cells in the range.
* @returns {Nullable<CellValue | RichTextValue>[][]} A two-dimensional array of value and rich text value
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* const fRange = fWorksheet.getRange('A1:B2');
* console.log(fRange.getValueAndRichTextValues());
* ```
*/
getValueAndRichTextValues(): Nullable<CellValue | RichTextValue>[][] {
const dataGrid = this.getCellDatas();
return dataGrid.map((row) => row.map((data) => data?.p ? new RichTextValue(data.p) : data?.v));
}
/**
* 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.
*
* For multiple cells, create the target range with {@link FWorksheet.getRange} and call
* {@link FRange.getFormulas} once instead of calling this method for each cell.
*
* @returns {string} The formula for the cell.
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* const fRange = fWorksheet.getRange('A1:B2');
* console.log(fRange.getFormula());
* ```
*/
getFormula(): string {
return this._formulaDataModel.getFormulaStringByCell(
this._range.startRow,
this._range.startColumn,
this._worksheet.getSheetId(),
this._workbook.getUnitId()
) ?? '';
}
/**
* Returns the formulas (A1 notation) for the cells in the range. Entries in the 2D array are empty strings for cells with no formula.
* @returns {string[][]} A two-dimensional array of formulas in string format.
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* const fRange = fWorksheet.getRange('A1:B2');
* console.log(fRange.getFormulas());
* ```
*/
getFormulas(): string[][] {
const formulas: string[][] = [];
const { startRow, endRow, startColumn, endColumn } = this._range;
const sheetId = this._worksheet.getSheetId();
const unitId = this._workbook.getUnitId();
for (let row = startRow; row <= endRow; row++) {
const rowFormulas: string[] = [];
for (let col = startColumn; col <= endColumn; col++) {
const formulaString = this._formulaDataModel.getFormulaStringByCell(row, col, sheetId, unitId);
rowFormulas.push(formulaString || '');
}
formulas.push(rowFormulas);
}
return formulas;
}
/**
* Gets whether text wrapping is enabled for top-left cell in the range.
*
* For multiple cells, create the target range with {@link FWorksheet.getRange} and call
* {@link FRange.getWraps} once instead of calling this method for each cell.
*
* @returns {boolean} whether text wrapping is enabled for the cell.
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* const fRange = fWorksheet.getRange('A1:B2');
* console.log(fRange.getWrap());
* ```
*/
getWrap(): boolean {
return this._worksheet.getRange(this._range).getWrap() === BooleanNumber.TRUE;
}
/**
* Gets whether the top-left cell shrinks its font size to fit the cell width.
* @returns {boolean} Whether shrink-to-fit is enabled for the top-left cell.
*/
getShrinkToFit(): boolean {
const { startRow, startColumn } = this._range;
return this._worksheet.getComposedCellStyle(startRow, startColumn)?.stf === BooleanNumber.TRUE;
}
/**
* Gets whether text wrapping is enabled for cells in the range.
* @returns {boolean[][]} A two-dimensional array of whether text wrapping is enabled for each cell in the range.
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* const fRange = fWorksheet.getRange('A1:B2');
* console.log(fRange.getWraps());
* ```
*/
getWraps(): boolean[][] {
const cells = this.getCellDatas();
const styles = this._workbook.getStyles();
return cells.map((row) => row.map((cell) => styles.getStyleByCell(cell)?.tb === WrapStrategy.WRAP));
}
/**
* Returns the text wrapping strategy for the top left cell of the range.
* @returns {WrapStrategy} The text wrapping strategy
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* const fRange = fWorksheet.getRange('A1:B2');
* console.log(fRange.getWrapStrategy());
* ```
*/
getWrapStrategy(): WrapStrategy {
return this._worksheet.getRange(this._range).getWrapStrategy();
}
/**
* Returns the horizontal alignment of the top-left cell as `left`, `center`, or `normal` (right alignment).
* Default and other core alignment values return `general`, which is not accepted by `setHorizontalAlignment()`.
*
* For multiple cells, create the target range with {@link FWorksheet.getRange} and call
* {@link FRange.getHorizontalAlignments} once instead of calling this method for each cell.
*
* @returns {string} The horizontal alignment of the text in the cell.
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* const fRange = fWorksheet.getRange('A1:B2');
* console.log(fRange.getHorizontalAlignment());
* ```
*/
getHorizontalAlignment(): string {
const coreHorizontalAlignment = this._worksheet.getRange(this._range).getHorizontalAlignment();
return transformCoreHorizontalAlignment(coreHorizontalAlignment);
}
/**
* Returns a two-dimensional array of horizontal alignments: `left`, `center`, or `normal` (right alignment).
* Default and other core alignment values return `general`, which is not accepted by `setHorizontalAlignment()`.
* @returns {string[][]} A two-dimensional array of horizontal alignments of text associated with cells in the range.
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* const fRange = fWorksheet.getRange('A1:B2');
* console.log(fRange.getHorizontalAlignments());
* ```
*/
getHorizontalAlignments(): string[][] {
const coreHorizontalAlignments = this._worksheet.getRange(this._range).getHorizontalAlignments();
return coreHorizontalAlignments.map((row) => row.map((alignment) => transformCoreHorizontalAlignment(alignment)));
}
/**
* Returns `top`, `middle`, or `bottom` for the top-left cell; unspecified alignment returns `general`.
* `general` is a getter result and is not accepted by `setVerticalAlignment()`.
*
* For multiple cells, create the target range with {@link FWorksheet.getRange} and call
* {@link FRange.getVerticalAlignments} once instead of calling this method for each cell.
*
* @returns {string} The vertical alignment of the text in the cell.
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* const fRange = fWorksheet.getRange('A1:B2');
* console.log(fRange.getVerticalAlignment());
* ```
*/
getVerticalAlignment(): string {
return transformCoreVerticalAlignment(this._worksheet.getRange(this._range).getVerticalAlignment());
}
/**
* Returns a two-dimensional array of `top`, `middle`, or `bottom` values; unspecified alignment returns `general`.
* `general` is a getter result and is not accepted by `setVerticalAlignment()`.
* @returns {string[][]} A two-dimensional array of vertical alignments of text associated with cells in the range.
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* const fRange = fWorksheet.getRange('A1:B2');
* console.log(fRange.getVerticalAlignments());
* ```
*/
getVerticalAlignments(): string[][] {
const coreVerticalAlignments = this._worksheet.getRange(this._range).getVerticalAlignments();
return coreVerticalAlignments.map((row) => row.map((alignment) => transformCoreVerticalAlignment(alignment)));
}
/**
* Set the same custom metadata for every cell in the current range.
*
* For the same metadata across multiple cells, call this method once on the entire target range.
* For different metadata per cell, prefer {@link FRange.setCustomMetaDatas} with a two-dimensional
* array matching the range dimensions instead of calling this method for each cell.
*
* @param {CustomData} data The custom meta data
* @returns {FRange} This range, for chaining
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* const fRange = fWorksheet.getRange('A1:B2');
* fRange.setCustomMetaData({ key: 'value' });
* console.log(fRange.getCustomMetaData());
* ```
*/
setCustomMetaData(data: CustomData): FRange {
this._logService.warn(SHEETS_CUSTOM_FIELD_WARNING_MESSAGE);
const params: ISetRangeCustomMetadataCommandParams = {
unitId: this._workbook.getUnitId(),
subUnitId: this._worksheet.getSheetId(),
range: this._range,
customMetadata: {
custom: data,
},
};
this._commandService.syncExecuteCommand(SetRangeCustomMetadataCommand.id, params);
return this;
}
/**
* Set custom meta data for current range.
* @param {CustomData[][]} datas The custom meta data
* @returns {FRange} This range, for chaining
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* const fRange = fWorksheet.getRange('A1:B2');
* fRange.setCustomMetaDatas([
* [{ key: 'value' }, { key: 'value2' }],
* [{ key: 'value3' }, { key: 'value4' }],
* ]);
* console.log(fRange.getCustomMetaDatas());
* ```
*/
setCustomMetaDatas(datas: CustomData[][]): FRange {
this._logService.warn(SHEETS_CUSTOM_FIELD_WARNING_MESSAGE);
const params: ISetRangeCustomMetadataCommandParams = {
unitId: this._workbook.getUnitId(),
subUnitId: this._worksheet.getSheetId(),
range: this._range,
customMetadata: datas.map((row) => row.map((data) => ({ custom: data }))),
};
this._commandService.syncExecuteCommand(SetRangeCustomMetadataCommand.id, params);
return this;
}
/**
* Returns the custom meta data for the cell at the start of this range.
*
* For multiple cells, create the target range with {@link FWorksheet.getRange} and call
* {@link FRange.getCustomMetaDatas} once instead of calling this method for each cell.
*
* @returns {CustomData | null} The custom meta data
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* const fRange = fWorksheet.getRange('A1:B2');
* console.log(fRange.getCustomMetaData());
* ```
*/
getCustomMetaData(): CustomData | null {
this._logService.warn(SHEETS_CUSTOM_FIELD_WARNING_MESSAGE);
const cell = this.getCellData();
return cell?.custom ?? null;
}
/**
* Returns the custom meta data for the cells in the range.
* @returns {Nullable<CustomData>[][]} A two-dimensional array of custom metadata, with `null` for cells without metadata.
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* const fRange = fWorksheet.getRange('A1:B2');
* console.log(fRange.getCustomMetaDatas());
* ```
*/
getCustomMetaDatas(): Nullable<CustomData>[][] {
this._logService.warn(SHEETS_CUSTOM_FIELD_WARNING_MESSAGE);
const dataGrid = this.getCellDataGrid();
return dataGrid.map((row) => row.map((data) => data?.custom ?? null));
}
/**
* Sets basic border properties for the current range.
*
* For borders on multiple cells, call this method once on the full target range. Border types such as
* outer and inner are relative to that range, so applying them to each cell can produce different borders.
*
* @param {BorderType} type The type of border to apply
* @param {BorderStyleTypes} style The border style
* @param {string} [color] Optional border color in CSS notation
* @returns {FRange} This range, for chaining
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* const fRange = fWorksheet.getRange('A1:B2');
* fRange.setBorder(univerAPI.Enum.BorderType.ALL, univerAPI.Enum.BorderStyleTypes.THIN, '#ff0000');
* ```
*/
setBorder(type: BorderType, style: BorderStyleTypes, color?: string): FRange {
this._commandService.syncExecuteCommand(SetBorderBasicCommand.id, {
unitId: this._workbook.getUnitId(),
subUnitId: this._worksheet.getSheetId(),
ranges: [this._range],
value: {
type,
style,
color,
},
} as ISetBorderBasicCommandParams);
return this;
}
// #region editing
/**
* Returns the background color of the top-left cell in the range.
*
* For multiple cells, create the target range with {@link FWorksheet.getRange} and call
* {@link FRange.getBackgrounds} once instead of calling this method for each cell.
*
* @returns {string} The color code of the background.
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* const fRange = fWorksheet.getRange('A1:B2');
* console.log(fRange.getBackground());
* ```
*/
getBackground(): string {
const style = this.getCellStyle();
return style?.background?.rgb ?? DEFAULT_STYLES.bg.rgb;
}
/**
* Returns the background colors of the cells in the range.
* @returns {string[][]} A two-dimensional array of color codes of the backgrounds.
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* const fRange = fWorksheet.getRange('A1:B2');
* console.log(fRange.getBackgrounds());
* ```
*/
getBackgrounds(): string[][] {
const styles = this.getCellStyles();
return styles.map((row) => row.map((style) => style?.background?.rgb ?? DEFAULT_STYLES.bg.rgb));
}
/**
* Set background color for current range.
*
* To apply the same setting to multiple cells, create the full target range with {@link FWorksheet.getRange}
* and call this method once instead of calling it on each cell separately.
*
* @param {string} color The background color
* @returns {FRange} This range, for chaining
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* const fRange = fWorksheet.getRange('A1:B2');
* fRange.setBackgroundColor('red');
* ```
*/
setBackgroundColor(color: string): FRange {
this._commandService.syncExecuteCommand(SetStyleCommand.id, {
unitId: this._workbook.getUnitId(),
subUnitId: this._worksheet.getSheetId(),
range: this._range,
style: {
type: 'bg',
value: {
rgb: color,
},
},
} as ISetStyleCommandParams<IColorStyle>);
return this;
}
/**
* Set background color for current range.
*
* To apply the same setting to multiple cells, create the full target range with {@link FWorksheet.getRange}
* and call this method once instead of calling it on each cell separately.
*
* @param {string} color The background color
* @returns {FRange} This range, for chaining
* @example
* ```typescript
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* const fRange = fWorksheet.getRange('A1:B2');
* fRange.setBackground('red');
* ```
*/
setBackground(color: string): FRange {
this.setBackgroundColor(color);
return this;
}
/**
* Set rotation for text in current range.
*
* To apply the same setting to multiple cells, create the full target range with {@link FWorksheet.getRange}
* and call this method once instead of calling it on each cell separately.
*
* @param {number} rotation - The rotation angle in degrees
* @returns This range, for chaining
* @example
* ```typescript
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* const fRange = fWorksheet.getRange('A1:B2');
* fRange.setTextRotation(45);
* ```
*/
setTextRotation(rotation: number): FRange {
this._commandService.syncExecuteCommand(SetTextRotationCommand.id, {
unitId: this._workbook.getUnitId(),
subUnitId: this._worksheet.getSheetId(),
range: this._range,
value: rotation,
} as ISetTextRotationCommandParams);
return this;
}
/**
* Sets the value or specified cell properties for every cell in this range.
*
* For the same value or cell-data update across multiple cells, call this method once on the entire
* target range. For different values or updates per cell, prefer {@link FRange.setValues} with a
* two-dimensional array or sparse matrix instead of calling this method for each cell.
*
* There are two input modes:
*
* - `CellValue` (`number`, `string`, or `boolean`): replaces the cell content. A string starting
* with `=` and containing at least one more character is written as a formula (`f`), clearing
* the previous value (`v`) and rich text (`p`). Other values clear the previous formula and
* rich text. Strings recognized as formatted numbers (for example, percentages, dates, or
* currencies) are converted to numeric values and apply the parsed number format. Existing
* formatting is otherwise preserved.
* - `ICellData`: updates cell-data fields directly, for explicit control over `v` (value),
* `f` (formula), `p` (rich text), `t` (value type), and `s` (style). The object bypasses the
* formula and formatted-number parsing above: `{ v: '=SUM(A1:A2)' }` does not set a formula;
* use `{ f: '=SUM(A1:A2)', v: null, p: null }` instead. Writing `v` or `p` without `f` and
* `si` replaces the target formula, as writing a scalar value does, and writing `f` without
* `si` disassociates the cell from its previous shared formula. Rich text is still not
* cleared by a value write, so pass `p: null` when replacing rich text with `v`. Use
* `v: null` to clear the stored value. Supplied style properties are merged into the existing
* style; `s: null` clears the style.
*
* In both modes, the stored value is converted according to its cell type. Unless an `ICellData`
* input supplies `t`, the type is inferred from the value, number format, and existing cell type.
* Consequently, passing `{ v: '00123' }` alone does not guarantee that the value stays a string;
* supply `t: CellValueType.STRING` to store it as text.
*
* @param {CellValue | ICellData} value The scalar content or cell-data update to apply throughout the range.
* @returns {FRange} This range, for chaining
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* const fRange = fWorksheet.getRange('B2:B3');
*
* // Replace the content of both cells, preserving their formatting.
* fRange.setValue(123);
*
* // Parse a percentage and apply its number format to both cells.
* fRange.setValue('25%');
*
* // Write the same formula to both cells.
* fRange.setValue('=SUM(A1:A2)');
*
* // Explicitly replace content and update the background color.
* fRange.setValue({ v: 234, f: null, p: null, s: { bg: { rgb: '#ff0000' } } });
*
* // Store numeric-looking text (CellValueType is imported from '@univerjs/core').
* fRange.setValue({ v: '00123', t: CellValueType.STRING, f: null, p: null });
*
* // Clear value, formula, and rich text while preserving formatting.
* fRange.setValue({ v: null, f: null, p: null });
* ```
*/
setValue(value: CellValue | ICellData): FRange {
const realValue = covertCellValue(value);
if (!realValue) {
throw new Error('Invalid value');
}
this._commandService.syncExecuteCommand(SetRangeValuesCommand.id, {
unitId: this._workbook.getUnitId(),
subUnitId: this._worksheet.getSheetId(),
range: this._range,
value: realValue,
});
return this;
}
/**
* Sets the value or specified cell properties of the top-left cell in this range.
* Uses the same scalar parsing and cell-data update rules as {@link FRange.setValue}.
*
* For multiple cells, prefer {@link FRange.setValues} on the target range with a two-dimensional array
* or sparse matrix. To apply the same value to the entire range, use {@link FRange.setValue} once.
*
* @param {CellValue | ICellData} value The scalar content or cell-data update to apply to the top-left cell only.
* @returns {FRange} This range, for chaining
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* const fRange = fWorksheet.getRange('A1:B2');
* fRange.setValueForCell(123);
*
* // or
* fRange.setValueForCell({ v: 234, s: { bg: { rgb: '#ff0000' } } });
* ```
*/
setValueForCell(value: CellValue | ICellData): FRange {
const realValue = covertCellValue(value);
if (!realValue) {
throw new Error('Invalid value');
}
this._commandService.syncExecuteCommand(SetRangeValuesCommand.id, {
unitId: this._workbook.getUnitId(),
subUnitId: this._worksheet.getSheetId(),
range: {
startColumn: this._range.startColumn,
startRow: this._range.startRow,
endColumn: this._range.startColumn,
endRow: this._range.startRow,
},
value: realValue,
});
return this;
}
/**
* Set the rich text value for the cell at the start of this range.
*
* For multiple cells, prefer {@link FRange.setRichTextValues} on the target range with a two-dimensional
* array matching its dimensions instead of calling this method for each cell.
*
* @param {RichTextValue | IDocumentData} value The rich text value
* @returns {FRange} The range
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* 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
* ```
*/
setRichTextValueForCell(value: RichTextValue | IDocumentData): FRange {
const p = value instanceof RichTextValue ? value.getData() : value;
const params: ISetRangeValuesCommandParams = {
unitId: this._workbook.getUnitId(),
subUnitId: this._worksheet.getSheetId(),
range: {
startColumn: this._range.startColumn,
startRow: this._range.startRow,
endColumn: this._range.startColumn,
endRow: this._range.startRow,
},
value: { p },
};
this._commandService.syncExecuteCommand(SetRangeValuesCommand.id, params);
return this;
}
/**
* Set the rich text value for the cells in the range.
* @param {(RichTextValue | IDocumentData)[][]} values A two-dimensional array of rich-text values or document data matching this range's dimensions.
* @returns {FRange} The range
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* 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],
* [richText, richText]
* ]);
* console.log(fRange.getValue(true).toPlainText()); // Hello World
* ```
*/
setRichTextValues(values: (RichTextValue | IDocumentData)[][]): FRange {
const cellDatas = values.map((row) => row.map((item) => item && { p: item instanceof RichTextValue ? item.getData() : item }));
const realValue = covertCellValues(cellDatas, this._range);
const params: ISetRangeValuesCommandParams = {
unitId: this._workbook.getUnitId(),
subUnitId: this._worksheet.getSheetId(),
range: this._range,
value: realValue,
};
this._commandService.syncExecuteCommand(SetRangeValuesCommand.id, params);
return this;
}
/**
* Set the cell wrap of the given range.
* Pass `true` to set `WrapStrategy.WRAP`, or `false` to reset to `WrapStrategy.UNSPECIFIED`.
* Use `setWrapStrategy()` to explicitly select clipping or overflow behavior.
*
* To apply the same setting to multiple cells, create the full target range with {@link FWorksheet.getRange}
* and call this method once instead of calling it on each cell separately.
*
* @param {boolean} isWrapEnabled Whether to enable wrap
* @returns {FRange} this range, for chaining
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* const fRange = fWorksheet.getRange('A1:B2');
* fRange.setWrap(true);
* console.log(fRange.getWrap());
* ```
*/
setWrap(isWrapEnabled: boolean): FRange {
this._commandService.syncExecuteCommand(SetTextWrapCommand.id, {
unitId: this._workbook.getUnitId(),
subUnitId: this._worksheet.getSheetId(),
range: this._range,
value: isWrapEnabled ? WrapStrategy.WRAP : WrapStrategy.UNSPECIFIED,
} as ISetTextWrapCommandParams);
return this;
}
/**
* Sets whether cells shrink their font size to fit the cell width.
*
* To apply the same setting to multiple cells, create the full target range with {@link FWorksheet.getRange}
* and call this method once instead of calling it on each cell separately.
*
* @param {boolean} enabled Whether to enable shrink-to-fit for this range.
* @returns {FRange} This range, for chaining.
* @example
* ```ts
* univerAPI.getActiveWorkbook()?.getActiveSheet().getRange('A1:B2').setShrinkToFit(true);
* ```
*/
setShrinkToFit(enabled: boolean): FRange {
this._commandService.syncExecuteCommand(SetShrinkToFitCommand.id, {
unitId: this._workbook.getUnitId(),
subUnitId: this._worksheet.getSheetId(),
range: this._range,
value: enabled ? BooleanNumber.TRUE : BooleanNumber.FALSE,
} as ISetShrinkToFitCommandParams);
return this;
}
/**
* Sets the text wrapping strategy for the cells in the range.
*
* To apply the same setting to multiple cells, create the full target range with {@link FWorksheet.getRange}
* and call this method once instead of calling it on each cell separately.
*
* @param {WrapStrategy} strategy The text wrapping strategy
* @returns {FRange} this range, for chaining
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* const fRange = fWorksheet.getRange('A1:B2');
* fRange.setWrapStrategy(univerAPI.Enum.WrapStrategy.WRAP);
* console.log(fRange.getWrapStrategy());
* ```
*/
setWrapStrategy(strategy: WrapStrategy): FRange {
this._commandService.syncExecuteCommand(SetTextWrapCommand.id, {
unitId: this._workbook.getUnitId(),
subUnitId: this._worksheet.getSheetId(),
range: this._range,
value: strategy,
} as ISetTextWrapCommandParams);
return this;
}
/**
* Set the vertical (top to bottom) alignment for the given range (top/middle/bottom).
*
* To apply the same setting to multiple cells, create the full target range with {@link FWorksheet.getRange}
* and call this method once instead of calling it on each cell separately.
*
* @param {FVerticalAlignment} alignment The vertical alignment
* @returns {FRange} this range, for chaining
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* const fRange = fWorksheet.getRange('A1:B2');
* fRange.setVerticalAlignment('top');
* ```
*/
setVerticalAlignment(alignment: FVerticalAlignment): FRange {
this._commandService.syncExecuteCommand(SetVerticalTextAlignCommand.id, {
unitId: this._workbook.getUnitId(),
subUnitId: this._worksheet.getSheetId(),
range: this._range,
value: transformFacadeVerticalAlignment(alignment),
} as ISetVerticalTextAlignCommandParams);
return this;
}
/**
* Sets the horizontal alignment for the range using `left`, `center`, or `normal`.
* These parameter names follow Google Apps Script. In Univer, `normal` means right alignment; `right` is not accepted.
*
* To apply the same setting to multiple cells, create the full target range with {@link FWorksheet.getRange}
* and call this method once instead of calling it on each cell separately.
*
* @param {FHorizontalAlignment} alignment The horizontal alignment: `left`, `center`, or `normal` (right alignment).
* @returns {FRange} this range, for chaining
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* const fRange = fWorksheet.getRange('A1:B2');
* fRange.setHorizontalAlignment('normal'); // Align right
* ```
*/
setHorizontalAlignment(alignment: FHorizontalAlignment): FRange {
this._commandService.syncExecuteCommand(SetHorizontalTextAlignCommand.id, {
unitId: this._workbook.getUnitId(),
subUnitId: this._worksheet.getSheetId(),
range: this._range,
value: transformFacadeHorizontalAlignment(alignment),
} as ISetHorizontalTextAlignCommandParams);
return this;
}
/**
* Sets cell values or specified cell properties using an array or a sparse matrix.
* Each entry follows the scalar parsing and cell-data update rules of {@link FRange.setValue}.
*
* For multiple cells, pass all values or cell-data updates in one call instead of calling this method
* on individual cell ranges. Use a matching two-dimensional array for a rectangle, or a sparse matrix
* with absolute worksheet coordinates for non-contiguous cells.
*
* A two-dimensional array is relative to this range's top-left cell and must match its dimensions.
* A sparse matrix uses absolute, zero-based worksheet row and column keys. Only supplied entries
* are updated; matrix coordinates are not offset by or clipped to this range.
* @param {CellValue[][] | IObjectMatrixPrimitiveType<CellValue> | ICellData[][] | IObjectMatrixPrimitiveType<ICellData>} value An array relative to this range, or a sparse matrix using absolute worksheet coordinates.
* @returns {FRange} This range, for chaining
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* const fRange = fWorksheet.getRange('A1:B2');
* fRange.setValues([
* [1, { v: 2, s: { bg: { rgb: '#ff0000' } } }],
* [3, 4]
* ]);
*
* // Update only B2 and C3 using absolute worksheet coordinates.
* fWorksheet.getRange('B2:C3').setValues({
* 1: { 1: 'B2' },
* 2: { 2: { v: 10, f: null, p: null } },
* });
* ```
*/
setValues(
value:
| CellValue[][]
| IObjectMatrixPrimitiveType<CellValue>
| ICellData[][]
| IObjectMatrixPrimitiveType<ICellData>
): FRange {
const realValue = covertCellValues(value, this._range);
this._commandService.syncExecuteCommand(SetRangeValuesCommand.id, {
unitId: this._workbook.getUnitId(),
subUnitId: this._worksheet.getSheetId(),
range: this._range,
value: realValue,
});
return this;
}
/**
* Sets the font weight for the given range (normal/bold),
*
* To apply the same setting to multiple cells, create the full target range with {@link FWorksheet.getRange}
* and call this method once instead of calling it on each cell separately.
*
* @param {FontWeight|null} fontWeight The font weight, either 'normal' or 'bold'; a null value resets the font weight.
* @returns {FRange} This range, for chaining
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* const fRange = fWorksheet.getRange('A1:B2');
* fRange.setFontWeight('bold');
* ```
*/
setFontWeight(fontWeight: FontWeight | null): this {
let value: BooleanNumber | null;
if (fontWeight === 'bold') {
value = BooleanNumber.TRUE;
} else if (fontWeight === 'normal') {
value = BooleanNumber.FALSE;
} else if (fontWeight === null) {
value = null;
} else {
throw new Error('Invalid fontWeight');
}
const style: IStyleTypeValue<BooleanNumber | null> = {
type: 'bl',
value,
};
const setStyleParams: ISetStyleCommandParams<BooleanNumber | null> = {
unitId: this._workbook.getUnitId(),
subUnitId: this._worksheet.getSheetId(),
range: this._range,
style,
};
this._commandService.syncExecuteCommand(SetStyleCommand.id, setStyleParams);
return this;
}
/**
* Sets the font style for the given range ('italic' or 'normal').
*
* To apply the same setting to multiple cells, create the full target range with {@link FWorksheet.getRange}
* and call this method once instead of calling it on each cell separately.
*
* @param {FontStyle|null} fontStyle The font style, either 'italic' or 'normal'; a null value resets the font style.
* @returns {FRange} This range, for chaining
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* const fRange = fWorksheet.getRange('A1:B2');
* fRange.setFontStyle('italic');
* ```
*/
setFontStyle(fontStyle: FontStyle | null): this {
let value: BooleanNumber | null;
if (fontStyle === 'italic') {
value = BooleanNumber.TRUE;
} else if (fontStyle === 'normal') {
value = BooleanNumber.FALSE;
} else if (fontStyle === null) {
value = null;
} else {
throw new Error('Invalid fontStyle');
}
const style: IStyleTypeValue<BooleanNumber | null> = {
type: 'it',
value,
};
const setStyleParams: ISetStyleCommandParams<BooleanNumber | null> = {
unitId: this._workbook.getUnitId(),
subUnitId: this._worksheet.getSheetId(),
range: this._range,
style,
};
this._commandService.syncExecuteCommand(SetStyleCommand.id, setStyleParams);
return this;
}
/**
* Sets the font line style of the given range ('underline', 'line-through', or 'none').
*
* To apply the same setting to multiple cells, create the full target range with {@link FWorksheet.getRange}
* and call this method once instead of calling it on each cell separately.
*
* @param {FontLine|null} fontLine The font line style, either 'underline', 'line-through', or 'none'; a null value resets the font line style.
* @returns {FRange} This range, for chaining
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* const fRange = fWorksheet.getRange('A1:B2');
* fRange.setFontLine('underline');
* ```
*/
setFontLine(fontLine: FontLine | null): this {
if (fontLine === 'underline') {
this._setFontUnderline({
s: BooleanNumber.TRUE,
});
} else if (fontLine === 'line-through') {
this._setFontStrikethrough({
s: BooleanNumber.TRUE,
});
} else if (fontLine === 'none') {
this._setFontUnderline({
s: BooleanNumber.FALSE,
});
this._setFontStrikethrough({
s: BooleanNumber.FALSE,
});
} else if (fontLine === null) {
this._setFontUnderline(null);
this._setFontStrikethrough(null);
} else {
throw new Error('Invalid fontLine');
}
return this;
}
/**
* Sets the font underline style of the given ITextDecoration
* @param {ITextDecoration|null} value The font underline style of the given ITextDecoration; a null value resets the font underline style
* @returns {void}
*/
private _setFontUnderline(value: ITextDecoration | null): void {
const style: IStyleTypeValue<ITextDecoration | null> = {
type: 'ul',
value,
};
const setStyleParams: ISetStyleCommandParams<ITextDecoration | null> = {
unitId: this._workbook.getUnitId(),
subUnitId: this._worksheet.getSheetId(),
range: this._range,
style,
};
this._commandService.syncExecuteCommand(SetStyleCommand.id, setStyleParams);
}
/**
* Sets the font strikethrough style of the given ITextDecoration
* @param {ITextDecoration|null} value The font strikethrough style of the given ITextDecoration; a null value resets the font strikethrough style
* @returns {void}
*/
private _setFontStrikethrough(value: ITextDecoration | null): void {
const style: IStyleTypeValue<ITextDecoration | null> = {
type: 'st',
value,
};
const setStyleParams: ISetStyleCommandParams<ITextDecoration | null> = {
unitId: this._workbook.getUnitId(),
subUnitId: this._worksheet.getSheetId(),
range: this._range,
style,
};
this._commandService.syncExecuteCommand(SetStyleCommand.id, setStyleParams);
}
/**
* Sets the font family, such as "Arial" or "Helvetica".
*
* To apply the same setting to multiple cells, create the full target range with {@link FWorksheet.getRange}
* and call this method once instead of calling it on each cell separately.
*
* @param {string|null} fontFamily The font family to set; a null value resets the font family.
* @returns {FRange} This range, for chaining
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* const fRange = fWorksheet.getRange('A1:B2');
* fRange.setFontFamily('Arial');
* ```
*/
setFontFamily(fontFamily: string | null): this {
const style: IStyleTypeValue<string | null> = {
type: 'ff',
value: fontFamily,
};
const setStyleParams: ISetStyleCommandParams<string | null> = {
unitId: this._workbook.getUnitId(),
subUnitId: this._worksheet.getSheetId(),
range: this._range,
style,
};
this._commandService.syncExecuteCommand(SetStyleCommand.id, setStyleParams);
return this;
}
/**
* Sets the font size, with the size being the point size to use.
*
* To apply the same setting to multiple cells, create the full target range with {@link FWorksheet.getRange}
* and call this method once instead of calling it on each cell separately.
*
* @param {number|null} size A font size in point size. A null value resets the font size.
* @returns {FRange} This range, for chaining
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* const fRange = fWorksheet.getRange('A1:B2');
* fRange.setFontSize(24);
* ```
*/
setFontSize(size: number | null): this {
const style: IStyleTypeValue<number | null> = {
type: 'fs',
value: size,
};
const setStyleParams: ISetStyleCommandParams<number | null> = {
unitId: this._workbook.getUnitId(),
subUnitId: this._worksheet.getSheetId(),
range: this._range,
style,
};
this._commandService.syncExecuteCommand(SetStyleCommand.id, setStyleParams);
return this;
}
/**
* Sets the font color in CSS notation (such as '#ffffff' or 'white').
*
* To apply the same setting to multiple cells, create the full target range with {@link FWorksheet.getRange}
* and call this method once instead of calling it on each cell separately.
*
* @param {string|null} color The font color in CSS notation (such as '#ffffff' or 'white'); a null value resets the color.
* @returns {FRange} This range, for chaining
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* const fRange = fWorksheet.getRange('A1:B2');
* fRange.setFontColor('#ff0000');
* ```
*/
setFontColor(color: string | null): this {
const value: IColorStyle | null = color === null ? null : { rgb: color };
const style: IStyleTypeValue<IColorStyle | null> = {
type: 'cl',
value,
};
const setStyleParams: ISetStyleCommandParams<IColorStyle | null> = {
unitId: this._workbook.getUnitId(),
subUnitId: this._worksheet.getSheetId(),
range: this._range,
style,
};
this._commandService.syncExecuteCommand(SetStyleCommand.id, setStyleParams);
return this;
}
// #endregion editing
//#region Merge cell
/**
* Merge cells in a range into one merged cell
* @param {IMergeCellsUtilOptions} [options] - The options for merging cells.
* @param {boolean} [options.defaultMerge] - If true, only the value in the upper left cell is retained. If false, a confirm dialog will be shown to the user. Default is true.
* @param {boolean} [options.isForceMerge] - If true, the overlapping merged cells will be removed before performing the new merge. Default is false.
* @returns {FRange} This range, for chaining
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* const fRange = fWorksheet.getRange('A1:B2');
* fRange.merge();
* console.log(fRange.isMerged());
* ```
*
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* const fRange = fWorksheet.getRange('B1:C2');
* // Assume A1:B2 is already merged.
* fRange.merge({ isForceMerge: true });
* ```
*/
merge(options?: IMergeCellsUtilOptions): FRange {
const unitId = this._workbook.getUnitId();
const subUnitId = this._worksheet.getSheetId();
addMergeCellsUtil(this._injector, unitId, subUnitId, [this._range], options);
return this;
}
/**
* Merges cells in a range horizontally.
* @param {IMergeCellsUtilOptions} [options] - The options for merging cells.
* @param {boolean} [options.defaultMerge] - If true, only the value in the upper left cell is retained. If false, a confirm dialog will be shown to the user. Default is true.
* @param {boolean} [options.isForceMerge] - If true, the overlapping merged cells will be removed before performing the new merge. Default is false.
* @returns {FRange} This range, for chaining
* @example
* ```ts
* // Assume the active sheet is a new sheet with no merged cells.
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* 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());
* });
* ```
*
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* const fRange = fWorksheet.getRange('B1:C2');
* // Assume A1:B2 is already merged.
* fRange.mergeAcross({ isForceMerge: true });
* ```
*/
mergeAcross(options?: IMergeCellsUtilOptions): FRange {
const ranges = getAddMergeMutationRangeByType([this._range], Dimension.ROWS);
const unitId = this._workbook.getUnitId();
const subUnitId = this._worksheet.getSheetId();
addMergeCellsUtil(this._injector, unitId, subUnitId, ranges, options);
return this;
}
/**
* Merges cells in a range vertically.
* @param {IMergeCellsUtilOptions} [options] - The options for merging cells.
* @param {boolean} [options.defaultMerge] - If true, only the value in the upper left cell is retained. If false, a confirm dialog will be shown to the user. Default is true.
* @param {boolean} [options.isForceMerge] - If true, the overlapping merged cells will be removed before performing the new merge. Default is false.
* @returns {FRange} This range, for chaining
* @example
* ```ts
* // Assume the active sheet is a new sheet with no merged cells.
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* 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());
* });
* ```
*
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* const fRange = fWorksheet.getRange('B1:C2');
* // Assume A1:B2 is already merged.
* fRange.mergeVertically({ isForceMerge: true });
* ```
*/
mergeVertically(options?: IMergeCellsUtilOptions): FRange {
const ranges = getAddMergeMutationRangeByType([this._range], Dimension.COLUMNS);
const unitId = this._workbook.getUnitId();
const subUnitId = this._worksheet.getSheetId();
addMergeCellsUtil(this._injector, unitId, subUnitId, ranges, options);
return this;
}
/**
* Returns true if cells in the current range overlap a merged cell.
* @returns {boolean} is overlap with a merged cell
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* const fRange = fWorksheet.getRange('A1:B2');
* fRange.merge();
* const anchor = fWorksheet.getRange('A1');
* console.log(anchor.isPartOfMerge()); // true
* ```
*/
isPartOfMerge(): boolean {
const { startRow, startColumn, endRow, endColumn } = this._range;
return this._worksheet.getMergedCellRange(startRow, startColumn, endRow, endColumn).length > 0;
}
/**
* Break all horizontally- or vertically-merged cells contained within the range list into individual cells again.
* @returns {FRange} This range, for chaining
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* 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
* ```
*/
breakApart(): FRange {
this._commandService.syncExecuteCommand(RemoveWorksheetMergeCommand.id, {
unitId: this._workbook.getUnitId(),
subUnitId: this._worksheet.getSheetId(),
ranges: [this._range],
});
return this;
}
//#endregion
/**
* Iterate cells in this range. Merged cells will be respected.
* @param {Function} callback the callback function to be called for each cell in the range
* @param {number} callback.row the row number of the cell
* @param {number} callback.col the column number of the cell
* @param {ICellData} callback.cell the cell data
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* const fRange = fWorksheet.getRange('A1:B2');
* fRange.forEach((row, col, cell) => {
* console.log(row, col, cell);
* });
* ```
*/
forEach(callback: (row: number, col: number, cell: ICellData) => void): void {
// Iterate each cell in this range.
const { startColumn, startRow, endColumn, endRow } = this._range;
this._worksheet
.getMatrixWithMergedCells(startRow, startColumn, endRow, endColumn)
.forValue((row, col, value) => {
callback(row, col, value);
});
}
/**
* Returns a string description of the range, in A1 notation.
* @param {boolean} [withSheet] - If true, the sheet name is included in the A1 notation.
* @param {AbsoluteRefType} [startAbsoluteRefType] - The absolute reference type for the start cell.
* @param {AbsoluteRefType} [endAbsoluteRefType] - The absolute reference type for the end cell.
* @returns {string} The A1 notation of the range.
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
*
* // 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
* ```
*/
getA1Notation(withSheet?: boolean, startAbsoluteRefType?: AbsoluteRefType, endAbsoluteRefType?: AbsoluteRefType): string {
const range = {
...this._range,
startAbsoluteRefType,
endAbsoluteRefType,
};
return withSheet ? serializeRangeWithSheet(this._worksheet.getName(), range) : serializeRange(range);
}
/**
* Sets the specified range as the active range, with the top left cell in the range as the current cell.
* @returns {FRange} This range, for chaining.
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* const fRange = fWorksheet.getRange('A1:B2');
* fRange.activate(); // the active cell will be A1
* ```
*/
activate(): FRange {
const fWorkbook = this._injector.createInstance(FWorkbook, this._workbook);
fWorkbook.setActiveRange(this);
return this;
}
/**
* 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.
* @returns {FRange} This range, for chaining.
* @description If the range is not a single cell, an error will be thrown.
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
*
* // 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
* ```
*/
activateAsCurrentCell(): FRange {
const mergeInfo = this._worksheet.getMergedCell(this._range.startRow, this._range.startColumn);
// the range is a merge cell or single cell
const valid = (mergeInfo && Rectangle.equals(mergeInfo, this._range)) ||
(!mergeInfo && this._range.startRow === this._range.endRow && this._range.startColumn === this._range.endColumn);
if (valid) {
const fWorkbook = this._injector.createInstance(FWorkbook, this._workbook);
const activeRange = fWorkbook.getActiveRange();
if (!activeRange || activeRange.getUnitId() !== this.getUnitId() || activeRange.getSheetId() !== this.getSheetId()) {
return this.activate();
}
if (Rectangle.contains(activeRange.getRange(), this._range)) {
const setSelectionOperationParams: ISetSelectionsOperationParams = {
unitId: this.getUnitId(),
subUnitId: this.getSheetId(),
selections: [
{
range: activeRange.getRange(),
primary: getPrimaryForRange(this.getRange(), this._worksheet),
style: null,
},
],
};
this._commandService.syncExecuteCommand(SetSelectionsOperation.id, setSelectionOperationParams);
return this;
}
return this.activate();
} else {
throw new Error('The range is not a single cell');
}
}
/**
* Splits a column of text into multiple columns based on an auto-detected delimiter.
* @param {boolean} [treatMultipleDelimitersAsOne] Whether to treat multiple continuous delimiters as one. The default value is false.
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
*
* // 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?: boolean): void;
/**
* Splits a column of text into multiple columns based on a specified delimiter.
* @param {boolean} [treatMultipleDelimitersAsOne] Whether to treat multiple continuous delimiters as one. The default value is false.
* @param {SplitDelimiterEnum} [delimiter] The 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|SplitDelimiterEnum.Semicolon) means using Comma and Semicolon to split the text.
* @example Show how to split text to columns with combined delimiter. The bit operations are used to combine the delimiters.
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
*
* // 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);
* ```
*/
splitTextToColumns(treatMultipleDelimitersAsOne?: boolean, delimiter?: SplitDelimiterEnum): void;
/**
* Splits a column of text into multiple columns based on a custom specified delimiter.
* @param {boolean} [treatMultipleDelimitersAsOne] Whether to treat multiple continuous delimiters as one. The default value is false.
* @param {SplitDelimiterEnum} [delimiter] The 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|SplitDelimiterEnum.Semicolon) means using Comma and Semicolon to split the text.
* @param {string} [customDelimiter] The custom delimiter to split the text. An error will be thrown if custom delimiter is set but the customDelimiter is not a character.
* @example Show how to split text to columns with custom delimiter
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
*
* // 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(false, univerAPI.Enum.SplitDelimiterType.Custom, '#'), the range will be:
* // A | | |
* // 1 | 2 | 3 |
* // 4 | | 5 | 6
* fRange.splitTextToColumns(false, univerAPI.Enum.SplitDelimiterType.Custom, '#');
*
* // After calling splitTextToColumns(true, univerAPI.Enum.SplitDelimiterType.Custom, '#'), the range will be:
* // A | |
* // 1 | 2 | 3
* // 4 | 5 | 6
* fRange.splitTextToColumns(true, univerAPI.Enum.SplitDelimiterType.Custom, '#');
* ```
*/
splitTextToColumns(treatMultipleDelimitersAsOne?: boolean, delimiter?: SplitDelimiterEnum, customDelimiter?: string): void {
this._commandService.syncExecuteCommand(SplitTextToColumnsCommand.id, {
unitId: this._workbook.getUnitId(),
subUnitId: this._worksheet.getSheetId(),
range: this._range,
delimiter,
customDelimiter,
treatMultipleDelimitersAsOne,
});
}
/**
* Set the theme style for the range.
* @param {string|undefined} themeName The name of the theme style to apply.If a undefined value is passed, the theme style will be removed if it exist.
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* const fRange = fWorksheet.getRange('A1:E20');
* fRange.useThemeStyle('default');
* ```
*/
useThemeStyle(themeName: string | undefined): void {
if (themeName === null || themeName === undefined) {
const usedThemeName = this.getUsedThemeStyle();
if (usedThemeName) {
this.removeThemeStyle(usedThemeName);
}
} else {
this._commandService.syncExecuteCommand(SetWorksheetRangeThemeStyleCommand.id, {
unitId: this._workbook.getUnitId(),
subUnitId: this._worksheet.getSheetId(),
range: this._range,
themeName,
});
}
}
/**
* Remove the theme style for the range.
* @param {string} themeName The name of the theme style to remove.
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* const fRange = fWorksheet.getRange('A1:E20');
* fRange.removeThemeStyle('default');
* ```
*/
removeThemeStyle(themeName: string): void {
this._commandService.syncExecuteCommand(DeleteWorksheetRangeThemeStyleCommand.id, {
unitId: this._workbook.getUnitId(),
subUnitId: this._worksheet.getSheetId(),
range: this._range,
themeName,
});
}
/**
* Gets the theme style applied to the range.
* @returns {string | undefined} The name of the theme style applied to the range or not exist.
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* const fRange = fWorksheet.getRange('A1:E20');
* console.log(fRange.getUsedThemeStyle()); // undefined
* fRange.useThemeStyle('default');
* console.log(fRange.getUsedThemeStyle()); // 'default'
* ```
*/
getUsedThemeStyle(): string | undefined {
return this._injector.get(SheetRangeThemeService).getAppliedRangeThemeStyle({
unitId: this._workbook.getUnitId(),
subUnitId: this._worksheet.getSheetId(),
range: this._range,
});
}
/**
* Clears the range content and formatting, or only one of them as specified by the options.
* Both content and formatting are cleared when both flags are true or both are false.
*
* To clear multiple cells, create the full target range with {@link FWorksheet.getRange} and call this
* method once instead of clearing each cell separately.
*
* @param {IFacadeClearOptions} [options] - Options for clearing the range. If not provided, the contents and formatting are cleared both.
* @param {boolean} [options.contentsOnly] - If true, the contents of the range are cleared. Effective only when `formatOnly` is false. Defaults to false.
* @param {boolean} [options.formatOnly] - Clears only formatting when true and `contentsOnly` is false. Defaults to false.
* @returns {FRange} This range, for chaining.
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorkSheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorkSheet) return;
* 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 });
* ```
*/
clear(options?: IFacadeClearOptions): FRange {
if (options && options.contentsOnly && !options.formatOnly) {
return this.clearContent();
}
if (options && options.formatOnly && !options.contentsOnly) {
return this.clearFormat();
}
this._commandService.syncExecuteCommand(ClearSelectionAllCommand.id, {
unitId: this._workbook.getUnitId(),
subUnitId: this._worksheet.getSheetId(),
ranges: [this._range],
options,
});
return this;
}
/**
* Clears content of the range, while preserving formatting information.
*
* To clear multiple cells, create the full target range with {@link FWorksheet.getRange} and call this
* method once instead of clearing each cell separately.
*
* @returns {FRange} This range, for chaining.
* @example
* ```typescript
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorkSheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorkSheet) return;
* const fRange = fWorkSheet.getRange('A1:D10');
*
* // clear the content only of the range A1:D10
* fRange.clearContent();
* ```
*/
clearContent(): FRange {
this._commandService.syncExecuteCommand(ClearSelectionContentCommand.id, {
unitId: this._workbook.getUnitId(),
subUnitId: this._worksheet.getSheetId(),
ranges: [this._range],
});
return this;
}
/**
* Clears formatting information of the range, while preserving contents.
*
* To clear multiple cells, create the full target range with {@link FWorksheet.getRange} and call this
* method once instead of clearing each cell separately.
*
* @returns {FRange} This range, for chaining.
* @example
* ```typescript
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorkSheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorkSheet) return;
* const fRange = fWorkSheet.getRange('A1:D10');
* // clear the format only of the range A1:D10
* fRange.clearFormat();
* ```
*/
clearFormat(): FRange {
this._commandService.syncExecuteCommand(ClearSelectionFormatCommand.id, {
unitId: this._workbook.getUnitId(),
subUnitId: this._worksheet.getSheetId(),
ranges: [this._range],
});
return this;
}
/**
* Inserts empty cells into this range. Existing data in the sheet along the provided dimension is shifted away from the inserted range.
* @param {Dimension} shiftDimension - The dimension along which to shift existing data.
* @example
* ```ts
* // Assume the active sheet empty sheet.
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* 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]]
* ```
*/
insertCells(shiftDimension: Dimension): void {
if (shiftDimension === Dimension.ROWS) {
this._commandService.executeCommand(InsertRangeMoveDownCommand.id, {
range: this._range,
});
} else {
this._commandService.executeCommand(InsertRangeMoveRightCommand.id, {
range: this._range,
});
}
}
/**
* Deletes this range of cells. Existing data in the sheet along the provided dimension is shifted towards the deleted range.
* @param {Dimension} shiftDimension - The dimension along which to shift existing data.
* @example
* ```ts
* // Assume the active sheet empty sheet.
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* 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]]
* ```
*/
deleteCells(shiftDimension: Dimension): void {
if (shiftDimension === Dimension.ROWS) {
this._commandService.executeCommand(DeleteRangeMoveUpCommand.id, {
range: this._range,
});
} else {
this._commandService.executeCommand(DeleteRangeMoveLeftCommand.id, {
range: this._range,
});
}
}
/**
* 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.
* Cells connected only diagonally do not cause the range to expand.
* If the original range is surrounded by empty cells along the specified dimension, the range itself is returned.
* @param {Dimension} [dimension] - The dimension along which to expand the range. If not provided, the range will be expanded in both dimensions.
* @returns {FRange} The range's data region or a range covering each column or each row spanned by the original range.
* @example
* ```ts
* // Assume the active sheet is a new sheet with no data.
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
*
* // 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:C4
* 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
* ```
*/
getDataRegion(dimension?: Dimension): FRange {
let { startRow, startColumn, endRow, endColumn } = this._range;
const maxRows = this._worksheet.getMaxRows();
const maxColumns = this._worksheet.getMaxColumns();
const cellMatrix = this._worksheet.getCellMatrix();
const rowHasData = (row: number): boolean => {
for (let c = startColumn; c <= endColumn; c++) {
if (!isNullCell(cellMatrix.getValue(row, c))) {
return true;
}
}
return false;
};
const columnHasData = (column: number): boolean => {
for (let r = startRow; r <= endRow; r++) {
if (!isNullCell(cellMatrix.getValue(r, column))) {
return true;
}
}
return false;
};
let expanded = true;
while (expanded) {
expanded = false;
while (dimension !== Dimension.COLUMNS && startRow > 0 && rowHasData(startRow - 1)) {
startRow--;
expanded = true;
}
while (dimension !== Dimension.COLUMNS && endRow < maxRows - 1 && rowHasData(endRow + 1)) {
endRow++;
expanded = true;
}
while (dimension !== Dimension.ROWS && startColumn > 0 && columnHasData(startColumn - 1)) {
startColumn--;
expanded = true;
}
while (dimension !== Dimension.ROWS && endColumn < maxColumns - 1 && columnHasData(endColumn + 1)) {
endColumn++;
expanded = true;
}
}
return this._injector.createInstance(FRange, this._workbook, this._worksheet, {
startRow,
startColumn,
endRow,
endColumn,
});
}
/**
* Returns true if the range is totally blank.
* @returns {boolean} true if the range is blank; false otherwise.
* @example
* ```ts
* // Assume the active sheet is a new sheet with no data.
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* 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
* ```
*/
isBlank(): boolean {
const cellMatrix = this._worksheet.getCellMatrix();
const { startRow, startColumn, endRow, endColumn } = this._range;
let isBlank = true;
for (let r = startRow; r <= endRow; r++) {
for (let c = startColumn; c <= endColumn; c++) {
if (!isNullCell(cellMatrix.getValue(r, c))) {
isBlank = false;
break;
}
}
if (!isBlank) {
break;
}
}
return isBlank;
}
/**
* 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.
* @param {number} rowOffset - The number of rows down from the range's top-left cell; negative values represent rows up from the range's top-left cell.
* @param {number} columnOffset - The number of columns right from the range's top-left cell; negative values represent columns left from the range's top-left cell.
* @returns {FRange} The new range.
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* 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: number, columnOffset: number): 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.
* @param {number} rowOffset - The number of rows down from the range's top-left cell; negative values represent rows up from the range's top-left cell.
* @param {number} columnOffset - The number of columns right from the range's top-left cell; negative values represent columns left from the range's top-left cell.
* @param {number} numRows - The height in rows of the new range.
* @returns {FRange} The new range.
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* 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
* ```
*/
offset(rowOffset: number, columnOffset: number, numRows: number): 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 and width in cells.
* @param {number} rowOffset - The number of rows down from the range's top-left cell; negative values represent rows up from the range's top-left cell.
* @param {number} columnOffset - The number of columns right from the range's top-left cell; negative values represent columns left from the range's top-left cell.
* @param {number} [numRows] - The height in rows of the new range.
* @param {number} [numColumns] - The width in columns of the new range.
* @returns {FRange} The new range.
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* 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 and the width to 3
* const newRange = fRange.offset(1, 1, 3, 3);
* console.log(newRange.getA1Notation()); // B2:D4
* ```
*/
offset(rowOffset: number, columnOffset: number, numRows?: number, numColumns?: number): FRange {
const { startRow, startColumn, endRow, endColumn } = this._range;
const newStartRow = startRow + rowOffset;
const newStartColumn = startColumn + columnOffset;
const newEndRow = numRows ? newStartRow + numRows - 1 : endRow + rowOffset;
const newEndColumn = numColumns ? newStartColumn + numColumns - 1 : endColumn + columnOffset;
if (newStartRow < 0 || newStartColumn < 0 || newEndRow < 0 || newEndColumn < 0) {
throw new Error('The row or column index is out of range');
}
return this._injector.createInstance(FRange, this._workbook, this._worksheet, {
startRow: newStartRow,
startColumn: newStartColumn,
endRow: newEndRow,
endColumn: newEndColumn,
});
}
/**
* Updates the formula for this range. The given formula must be in A1 notation.
*
* For the same formula string across multiple cells, call this method once on the entire target range.
* For different formulas per cell, prefer {@link FRange.setFormulas} with a two-dimensional array
* matching the range dimensions instead of calling this method for each cell.
*
* @param {string} formula - A string representing the formula to set for the cell.
* @returns {FRange} This range instance for chaining.
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* const fRange = fWorksheet.getRange('A1');
* fRange.setFormula('=SUM(A2:A5)');
* console.log(fRange.getFormula()); // '=SUM(A2:A5)'
* ```
*/
setFormula(formula: string): FRange {
return this.setValue({
f: formula,
});
}
/**
* Sets a rectangular grid of formulas (must match dimensions of this range). The given formulas must be in A1 notation.
* @param {string[][]} formulas - A two-dimensional string array of formulas.
* @returns {FRange} This range instance for chaining.
* @example
* ```ts
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* 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)']]
* ```
*/
setFormulas(formulas: string[][]): FRange {
return this.setValues(formulas.map((row) => row.map((formula) => ({ f: formula }))));
}
/**
* Get the RangePermission instance for managing range-level permissions.
* This is the new permission API that provides range-specific permission control.
* @returns {FRangePermission} - The RangePermission instance.
* @example
* ```ts
* const fWorksheet = univerAPI.getActiveWorkbook().getSheetByName('Sheet1');
* if (!fWorksheet) return;
* const fRange = fWorksheet.getRange('A1:B10');
* const permission = fRange.getRangePermission();
*
* // Protect the range
* await permission.protect({ name: 'Protected Area', allowEdit: false });
*
* // Check if range is protected
* const isProtected = permission.isProtected();
*
* // Check if current user can edit
* const canEdit = permission.canEdit();
*
* // Unprotect the range
* await permission.unprotect();
*
* // Subscribe to protection changes
* permission.protectionChange$.subscribe(change => {
* console.log('Protection changed:', change);
* });
* ```
*/
getRangePermission(): FRangePermission {
const fWorksheet = this._injector.createInstance(FWorksheet, this._injector.createInstance(FWorkbook, this._workbook), this._workbook, this._worksheet);
return this._injector.createInstance(
FRangePermission,
this._workbook.getUnitId(),
this._worksheet.getSheetId(),
this,
fWorksheet
);
}
/**
* Fills the target range with data based on the data in the current range.
* @param {FRange} targetRange - The range to be filled with data.
* @param {AUTO_FILL_APPLY_TYPE} [applyType] - The type of data fill to be applied.
* @returns {Promise<boolean>} A promise that resolves to true if the fill operation was successful, false otherwise.
* @example
* ```ts
* // Auto-fill the range D1:D10 based on the data in the range C1:C2
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetByName('Sheet1');
* if (!fWorksheet) return;
* const fRange = fWorksheet.getRange('A1:A4');
*
* // Auto-fill without specifying applyType (default behavior)
* await fRange.autoFill(fWorksheet.getRange('A1:A20'))
*
* // Auto-fill with 'COPY' type
* await fRange.autoFill(fWorksheet.getRange('A1:A20'), 'COPY')
*
* // Auto-fill with 'SERIES' type
* await fRange.autoFill(fWorksheet.getRange('A1:A20'), 'SERIES')
* ```
*
* ```ts
* // Operate on a specific worksheet
* const fWorkbook = univerAPI.getActiveWorkbook();
* const fWorksheet = fWorkbook.getSheetBySheetId('sheetId');
* const fRange = fWorksheet.getRange('A1:A4');
*
* // Auto-fill without specifying applyType (default behavior)
* await fRange.autoFill(fWorksheet.getRange('A1:A20'))
*
* // Auto-fill with 'COPY' type
* await fRange.autoFill(fWorksheet.getRange('A1:A20'), 'COPY')
*
* // Auto-fill with 'SERIES' type
* await fRange.autoFill(fWorksheet.getRange('A1:A20'), 'SERIES')
* ```
*/
autoFill(targetRange: FRange, applyType?: AUTO_FILL_APPLY_TYPE): Promise<boolean> {
const _sourceRange = this.getRange();
const _targetRange = targetRange.getRange();
if (!Rectangle.contains(_targetRange, _sourceRange)) {
throw new Error('AutoFill target range must contain source range');
}
const { startRow: sourceStartRow, startColumn: sourceStartColumn, endRow: sourceEndRow, endColumn: sourceEndColumn } = _sourceRange;
const { startRow: targetStartRow, startColumn: targetStartColumn, endRow: targetEndRow, endColumn: targetEndColumn } = _targetRange;
// If both row and column count are different, throw error
if ((sourceEndRow - sourceStartRow) !== (targetEndRow - targetStartRow) && (sourceEndColumn - sourceStartColumn) !== (targetEndColumn - targetStartColumn)) {
throw new Error('AutoFill can only fill in one direction');
}
// If the direction includes both left and right, throw error
if (
(sourceEndRow - sourceStartRow) === (targetEndRow - targetStartRow) &&
sourceStartColumn !== targetStartColumn &&
sourceEndColumn !== targetEndColumn
) {
throw new Error('AutoFill can only fill in one direction');
}
// If the direction includes both up and down, throw error
if (
(sourceEndColumn - sourceStartColumn) === (targetEndColumn - targetStartColumn) &&
sourceStartRow !== targetStartRow &&
sourceEndRow !== targetEndRow
) {
throw new Error('AutoFill can only fill in one direction');
}
return this._commandService.executeCommand(AutoFillCommand.id, {
sourceRange: _sourceRange,
targetRange: _targetRange,
unitId: this.getUnitId(),
subUnitId: this.getSheetId(),
applyType,
});
};
}