HyperFormula Class

This is a class for creating HyperFormula instance, all the following public methods are related to this class.

The instance can be created only by calling one of the static methods buildFromArray, buildFromSheets or buildEmpty and should be disposed of with the destroy method when it's no longer needed to free the resources.

The instance can be seen as a workbook where worksheets can be created and manipulated. They are organized within a widely known structure of columns and rows which can be manipulated as well. The smallest possible data unit are the cells, which may contain simple values or formulas to be calculated.

All CRUD methods are called directly on HyperFormula instance and will trigger corresponding lifecycle events. The events are marked accordingly, as well as thrown errors, so they can be correctly handled.

Static Properties

buildDate Static

▪ buildDate: string = '10/04/2024 13:38:59'

Defined in src/HyperFormula.ts:101 (opens new window)

Latest build date.

---

languages Static

▪ languages: Record‹string, RawTranslationPackage›

Defined in src/HyperFormula.ts:117 (opens new window)

When using the UMD build, this property contains all available languages to use with the registerLanguage method.

For more information, see the Localizing functions guide.

---

releaseDate Static

▪ releaseDate: string = '10/04/2024'

Defined in src/HyperFormula.ts:108 (opens new window)

A release date.

---

version Static

▪ version: string = '2.7.0'

Defined in src/HyperFormula.ts:94 (opens new window)

Version of the HyperFormula.

Static Accessors

defaultConfig Static

• get defaultConfig(): ConfigParams

Defined in src/HyperFormula.ts:156 (opens new window)

Returns all of HyperFormula's default configuration options.

example

// returns all default configuration options
const defaultConfig = HyperFormula.defaultConfig;

category Static Accessors

Returns: ConfigParams

Factories

buildEmpty Static

▸ buildEmpty(configInput: Partial‹ConfigParams›, namedExpressions: SerializedNamedExpression[]): HyperFormula

Defined in src/HyperFormula.ts:328 (opens new window)

Builds an empty engine instance. Can be configured with the optional parameter that represents a ConfigParams. If not specified the engine will be built with the default configuration.

example

// build with no initial data and with optional config parameter maxColumns
const hfInstance = HyperFormula.buildEmpty({ maxColumns: 1000 });

Parameters:

Name	Type	Default	Description
configInput	Partial‹ConfigParams›	{}	engine configuration
namedExpressions	SerializedNamedExpression[]	[]	starting named expressions

Returns: HyperFormula

---

buildFromArray Static

▸ buildFromArray(sheet: Sheet, configInput: Partial‹ConfigParams›, namedExpressions: SerializedNamedExpression[]): HyperFormula

Defined in src/HyperFormula.ts:268 (opens new window)

Builds the engine for a sheet from a two-dimensional array representation. The engine is created with a single sheet. Can be configured with the optional second parameter that represents a ConfigParams. If not specified, the engine will be built with the default configuration.

throws [[SheetSizeLimitExceededError]] when sheet size exceeds the limits

throws [[InvalidArgumentsError]] when sheet is not an array of arrays

throws [[FunctionPluginValidationError]] when plugin class definition is not consistent with metadata

example

// data represented as an array
const sheetData = [
 ['0', '=SUM(1, 2, 3)', '52'],
 ['=SUM(A1:C1)', '', '=A1'],
 ['2', '=SUM(A1:C1)', '91'],
];

// method with optional config parameter maxColumns
const hfInstance = HyperFormula.buildFromArray(sheetData, { maxColumns: 1000 });

Parameters:

Name	Type	Default	Description
sheet	Sheet	-	two-dimensional array representation of sheet
configInput	Partial‹ConfigParams›	{}	engine configuration
namedExpressions	SerializedNamedExpression[]	[]	starting named expressions

Returns: HyperFormula

---

buildFromSheets Static

▸ buildFromSheets(sheets: Sheets, configInput: Partial‹ConfigParams›, namedExpressions: SerializedNamedExpression[]): HyperFormula

Defined in src/HyperFormula.ts:308 (opens new window)

Builds the engine from an object containing multiple sheets with names. The engine is created with one or more sheets. Can be configured with the optional second parameter that represents a ConfigParams. If not specified the engine will be built with the default configuration.

throws [[SheetSizeLimitExceededError]] when sheet size exceeds the limits

throws [[InvalidArgumentsError]] when any sheet is not an array of arrays

throws [[FunctionPluginValidationError]] when plugin class definition is not consistent with metadata

example

// data represented as an object with sheets: Sheet1 and Sheet2
const sheetData = {
 'Sheet1': [
   ['1', '', '=Sheet2!$A1'],
   ['', '2', '=SUM(1, 2, 3)'],
   ['=Sheet2!$A2', '2', ''],
  ],
 'Sheet2': [
   ['', '4', '=Sheet1!$B1'],
   ['', '8', '=SUM(9, 3, 3)'],
   ['=Sheet1!$B1', '2', ''],
  ],
};

// method with optional config parameter useColumnIndex
const hfInstance = HyperFormula.buildFromSheets(sheetData, { useColumnIndex: true });

Parameters:

Name	Type	Default	Description
sheets	Sheets	-	object with sheets definition
configInput	Partial‹ConfigParams›	{}	engine configuration
namedExpressions	SerializedNamedExpression[]	[]	starting named expressions

Returns: HyperFormula

---

Instance

destroy

▸ destroy(): void

Defined in src/HyperFormula.ts:4402 (opens new window)

Destroys instance of HyperFormula.

example

// destroys the instance
hfInstance.destroy();

Returns: void

---

getConfig

▸ getConfig(): ConfigParams

Defined in src/HyperFormula.ts:1019 (opens new window)

Returns current configuration of the engine instance.

example

// should return all config metadata including default and those which were added
const hfConfig = hfInstance.getConfig();

Returns: ConfigParams

---

rebuildAndRecalculate

▸ rebuildAndRecalculate(): void

Defined in src/HyperFormula.ts:1033 (opens new window)

Rebuilds the HyperFormula instance preserving the current sheets data.

example

hfInstance.rebuildAndRecalculate();

Returns: void

---

updateConfig

▸ updateConfig(newParams: Partial‹ConfigParams›): void

Defined in src/HyperFormula.ts:998 (opens new window)

Updates the config with given new metadata. It is an expensive operation, as it might trigger rebuilding the engine and recalculation of all formulas.

throws [[ExpectedValueOfTypeError]] when some parameters of config are of wrong type (e.g., currencySymbol)

throws [[ConfigValueEmpty]] when some parameters of config are of invalid value (e.g., currencySymbol)

example

const hfInstance = HyperFormula.buildFromArray([
 ['1', '2'],
]);

// add a config param, for example maxColumns,
// you can check the configuration with getConfig method
hfInstance.updateConfig({ maxColumns: 1000 });

Parameters:

Name	Type	Description
newParams	Partial‹ConfigParams›	configuration options to be updated or added

Returns: void

---

Sheets

addSheet

▸ addSheet(sheetName?: undefined | string): string

Defined in src/HyperFormula.ts:2571 (opens new window)

Adds a new sheet to the HyperFormula instance. Returns given or autogenerated name of a new sheet.

fires sheetAdded after the sheet was added

throws [[ExpectedValueOfTypeError]] if any of its basic type argument is of wrong type

throws [[SheetNameAlreadyTakenError]] when sheet with a given name already exists

example

const hfInstance = HyperFormula.buildFromSheets({
 MySheet1: [ ['1'] ],
 MySheet2: [ ['10'] ],
});

// should return 'MySheet3'
const nameProvided = hfInstance.addSheet('MySheet3');

// should return autogenerated 'Sheet4'
// because no name was provided and 3 other ones already exist
const generatedName = hfInstance.addSheet();

Parameters:

Name	Type
sheetName?	undefined | string

Returns: string

---

clearSheet

▸ clearSheet(sheetId: number): ExportedChange[]

Defined in src/HyperFormula.ts:2718 (opens new window)

Clears the sheet content. Double-checks if the sheet exists.

Returns an array of cells whose values changed as a result of this operation.

Note that this method may trigger dependency graph recalculation.

fires valuesUpdated if recalculation was triggered by this change

throws [[ExpectedValueOfTypeError]] if any of its basic type argument is of wrong type

throws [[NoSheetWithIdError]] when the given sheet ID does not exist

example

const hfInstance = HyperFormula.buildFromSheets({
 MySheet1: [ ['=SUM(MySheet2!A1:A2)'] ],
 MySheet2: [ ['10'] ],
});

// should return a list of cells which values changed after the operation,
// their absolute addresses and new values, in this example it will return:
// [{
//   address: { sheet: 0, col: 0, row: 0 },
//   newValue: 0,
// }]
const changes = hfInstance.clearSheet(0);

Parameters:

Name	Type	Description
sheetId	number	sheet ID.

Returns: ExportedChange[]

---

countSheets

▸ countSheets(): number

Defined in src/HyperFormula.ts:3360 (opens new window)

Returns the number of existing sheets.

example

const hfInstance = HyperFormula.buildFromArray([
 ['1', '2'],
]);

// should return the number of sheets which is '1'
const sheetsCount = hfInstance.countSheets();

Returns: number

---

doesSheetExist

▸ doesSheetExist(sheetName: string): boolean

Defined in src/HyperFormula.ts:3083 (opens new window)

Returns true whether sheet with a given name exists. The method accepts sheet name to be checked.

throws [[ExpectedValueOfTypeError]] if any of its basic type argument is of wrong type

example

const hfInstance = HyperFormula.buildFromSheets({
  MySheet1: [ ['1'] ],
  MySheet2: [ ['10'] ],
});

// should return 'true' since 'MySheet1' exists
const sheetExist = hfInstance.doesSheetExist('MySheet1');

Parameters:

Name	Type	Description
sheetName	string	name of the sheet, case-insensitive.

Returns: boolean

---

getAllSheetsDimensions

▸ getAllSheetsDimensions(): Record‹string, SheetDimensions›

Defined in src/HyperFormula.ts:882 (opens new window)

Returns a map containing dimensions of all sheets for the engine instance represented as a key-value pairs where keys are sheet IDs and dimensions are returned as numbers, width and height respectively.

throws [[NoSheetWithIdError]] when the given sheet ID does not exist

example

const hfInstance = HyperFormula.buildFromSheets({
  Sheet1: [
   ['1', '2', '=Sheet2!$A1'],
  ],
  Sheet2: [
   ['3'],
   ['4'],
  ],
});

// should return the dimensions of all sheets:
// { Sheet1: { width: 3, height: 1 }, Sheet2: { width: 1, height: 2 } }
const allSheetsDimensions = hfInstance.getAllSheetsDimensions();

Returns: Record‹string, SheetDimensions›

---

getAllSheetsFormulas

▸ getAllSheetsFormulas(): Record‹string, (string | undefined)[][]›

Defined in src/HyperFormula.ts:951 (opens new window)

Returns formulas of all sheets in a form of an object which property keys are strings and values are 2D arrays of strings or possibly undefined when the call does not contain a formula.

example

const hfInstance = HyperFormula.buildFromArray([
 ['1', '2', '=A1+10'],
]);

// should return only formulas: { Sheet1: [ [ undefined, undefined, '=A1+10' ] ] }
const allSheetsFormulas = hfInstance.getAllSheetsFormulas();

Returns: Record‹string, (string | undefined)[][]›

---

getAllSheetsSerialized

▸ getAllSheetsSerialized(): Record‹string, RawCellContent[][]›

Defined in src/HyperFormula.ts:972 (opens new window)

Returns formulas or values of all sheets in a form of an object which property keys are strings and values are 2D arrays of [[RawCellContent]].

throws [[EvaluationSuspendedError]] when the evaluation is suspended

example

const hfInstance = HyperFormula.buildFromArray([
 ['1', '2', '=A1+10'],
]);

// should return all sheets serialized content: { Sheet1: [ [ 1, 2, '=A1+10' ] ] }
const allSheetsSerialized = hfInstance.getAllSheetsSerialized();

Returns: Record‹string, RawCellContent[][]›

---

getAllSheetsValues

▸ getAllSheetsValues(): Record‹string, CellValue[][]›

Defined in src/HyperFormula.ts:932 (opens new window)

Returns values of all sheets in a form of an object which property keys are strings and values are 2D arrays of [[CellValue]].

throws [[EvaluationSuspendedError]] when the evaluation is suspended

example

const hfInstance = HyperFormula.buildFromArray([
 ['1', '=A1+10', '3'],
]);

// should return all sheets values: { Sheet1: [ [ 1, 11, 3 ] ] }
const allSheetsValues = hfInstance.getAllSheetsValues();

Returns: Record‹string, CellValue[][]›

---

getSheetDimensions

▸ getSheetDimensions(sheetId: number): SheetDimensions

Defined in src/HyperFormula.ts:907 (opens new window)

Returns dimensions of a specified sheet. The sheet dimensions is represented with numbers: width and height.

throws [[ExpectedValueOfTypeError]] if any of its basic type argument is of wrong type

throws [[NoSheetWithIdError]] when the given sheet ID does not exist

example

const hfInstance = HyperFormula.buildFromArray([
   ['1', '2', '=Sheet2!$A1'],
]);

// should return provided sheet's dimensions: { width: 3, height: 1 }
const sheetDimensions = hfInstance.getSheetDimensions(0);

Parameters:

Name	Type	Description
sheetId	number	sheet ID number

Returns: SheetDimensions

---

getSheetFormulas

▸ getSheetFormulas(sheetId: number): (string | undefined)[][]

Defined in src/HyperFormula.ts:819 (opens new window)

Returns an array with normalized formula strings from [[Sheet]] or undefined for a cells that have no value.

throws [[ExpectedValueOfTypeError]] if any of its basic type argument is of wrong type

throws [[NoSheetWithIdError]] when the given sheet ID does not exist

example

const hfInstance = HyperFormula.buildFromArray([
 ['0', '=SUM(1, 2, 3)', '=A1'],
 ['1', '=TEXT(A2, "0.0%")', '=C1'],
 ['2', '=SUM(A1:C1)', '=C1'],
]);

// should return all formulas of a sheet:
// [
//  [undefined, '=SUM(1, 2, 3)', '=A1'],
//  [undefined, '=TEXT(A2, "0.0%")', '=C1'],
//  [undefined, '=SUM(A1:C1)', '=C1'],
// ];
const sheetFormulas = hfInstance.getSheetFormulas(0);

Parameters:

Name	Type	Description
sheetId	number	sheet ID number

Returns: (string | undefined)[][]

---

getSheetId

▸ getSheetId(sheetName: string): number | undefined

Defined in src/HyperFormula.ts:3058 (opens new window)

Returns a unique sheet ID assigned to the sheet with a given name or undefined if the sheet does not exist.

throws [[ExpectedValueOfTypeError]] if any of its basic type argument is of wrong type

example

const hfInstance = HyperFormula.buildFromSheets({
  MySheet1: [ ['1'] ],
  MySheet2: [ ['10'] ],
});

// should return '0' because 'MySheet1' is of ID '0'
const sheetID = hfInstance.getSheetId('MySheet1');

Parameters:

Name	Type	Description
sheetName	string	name of the sheet, for which we want to retrieve ID, case-insensitive.

Returns: number | undefined

---

getSheetName

▸ getSheetName(sheetId: number): string | undefined

Defined in src/HyperFormula.ts:3012 (opens new window)

Returns a unique sheet name assigned to the sheet of a given ID or undefined if the there is no sheet with a given ID.

throws [[ExpectedValueOfTypeError]] if any of its basic type argument is of wrong type

example

const hfInstance = HyperFormula.buildFromSheets({
 MySheet1: [ ['1'] ],
 MySheet2: [ ['10'] ],
});

// should return 'MySheet2' as this sheet is the second one
const sheetName = hfInstance.getSheetName(1);

Parameters:

Name	Type	Description
sheetId	number	ID of the sheet, for which we want to retrieve name

Returns: string | undefined

---

getSheetNames

▸ getSheetNames(): string[]

Defined in src/HyperFormula.ts:3034 (opens new window)

List all sheet names. Returns an array of sheet names as strings.

example

const hfInstance = HyperFormula.buildFromSheets({
 MySheet1: [ ['1'] ],
 MySheet2: [ ['10'] ],
});

// should return all sheets names: ['MySheet1', 'MySheet2']
const sheetNames = hfInstance.getSheetNames();

Returns: string[]

---

getSheetSerialized

▸ getSheetSerialized(sheetId: number): RawCellContent[][]

Defined in src/HyperFormula.ts:852 (opens new window)

Returns an array of arrays of [[RawCellContent]] with serialized content of cells from [[Sheet]], either a cell formula or an explicit value.

throws [[ExpectedValueOfTypeError]] if any of its basic type argument is of wrong type

throws [[EvaluationSuspendedError]] when the evaluation is suspended

throws [[NoSheetWithIdError]] when the given sheet ID does not exist

example

const hfInstance = HyperFormula.buildFromArray([
 ['0', '=SUM(1, 2, 3)', '=A1'],
 ['1', '=TEXT(A2, "0.0%")', '=C1'],
 ['2', '=SUM(A1:C1)', '=C1'],
]);

// should return:
// [
//  ['0', '=SUM(1, 2, 3)', '=A1'],
//  ['1', '=TEXT(A2, "0.0%")', '=C1'],
//  ['2', '=SUM(A1:C1)', '=C1'],
// ];
const serializedContent = hfInstance.getSheetSerialized(0);

Parameters:

Name	Type	Description
sheetId	number	sheet ID number

Returns: RawCellContent[][]

---

getSheetValues

▸ getSheetValues(sheetId: number): CellValue[][]

Defined in src/HyperFormula.ts:786 (opens new window)

Returns an array of arrays of [[CellValue]] with values of all cells from [[Sheet]]. Applies rounding and post-processing.

throws [[ExpectedValueOfTypeError]] if any of its basic type argument is of wrong type

throws [[NoSheetWithIdError]] when the given sheet ID does not exist

throws [[EvaluationSuspendedError]] when the evaluation is suspended

example

const hfInstance = HyperFormula.buildFromArray([
 ['0', '=SUM(1, 2, 3)', '=A1'],
 ['1', '=TEXT(A2, "0.0%")', '=C1'],
 ['2', '=SUM(A1:C1)', '=C1'],
]);

// should return all values of a sheet: [[0, 6, 0], [1, '1.0%', 0], [2, 6, 0]]
const sheetValues = hfInstance.getSheetValues(0);

Parameters:

Name	Type	Description
sheetId	number	sheet ID number

Returns: CellValue[][]

---

isItPossibleToAddSheet

▸ isItPossibleToAddSheet(sheetName: string): boolean

Defined in src/HyperFormula.ts:2534 (opens new window)

Returns information whether it is possible to add a sheet to the engine. Checks against particular rules to ascertain that addSheet can be called. If returns true, doing addSheet operation won't throw any errors, and it is possible to add sheet with provided name. Returns false if the chosen name is already used.

throws [[ExpectedValueOfTypeError]] if any of its basic type argument is of wrong type

example

const hfInstance = HyperFormula.buildFromSheets({
  MySheet1: [ ['1'] ],
  MySheet2: [ ['10'] ],
});

// should return 'false' because 'MySheet2' already exists
const isAddable = hfInstance.isItPossibleToAddSheet('MySheet2');

Parameters:

Name	Type	Description
sheetName	string	sheet name, case-insensitive

Returns: boolean

---

isItPossibleToClearSheet

▸ isItPossibleToClearSheet(sheetId: number): boolean

Defined in src/HyperFormula.ts:2676 (opens new window)

Returns information whether it is possible to clear a specified sheet. If returns true, doing clearSheet operation won't throw any errors, provided sheet exists and its content can be cleared. Returns false otherwise

throws [[ExpectedValueOfTypeError]] if any of its basic type argument is of wrong type

example

const hfInstance = HyperFormula.buildFromSheets({
 MySheet1: [ ['1'] ],
 MySheet2: [ ['10'] ],
});

// should return 'true' because 'MySheet2' exists and can be cleared
const isClearable = hfInstance.isItPossibleToClearSheet(1);

Parameters:

Name	Type	Description
sheetId	number	sheet ID.

Returns: boolean

---

isItPossibleToRemoveSheet

▸ isItPossibleToRemoveSheet(sheetId: number): boolean

Defined in src/HyperFormula.ts:2602 (opens new window)

Returns information whether it is possible to remove sheet for the engine. Returns true if the provided sheet exists, and therefore it can be removed, doing removeSheet operation won't throw any errors. Returns false otherwise

throws [[ExpectedValueOfTypeError]] if any of its basic type argument is of wrong type

example

const hfInstance = HyperFormula.buildFromSheets({
 MySheet1: [ ['1'] ],
 MySheet2: [ ['10'] ],
});

// should return 'true' because sheet with ID 1 exists and is removable
const isRemovable = hfInstance.isItPossibleToRemoveSheet(1);

Parameters:

Name	Type	Description
sheetId	number	sheet ID.

Returns: boolean

---

isItPossibleToRenameSheet

▸ isItPossibleToRenameSheet(sheetId: number, newName: string): boolean

Defined in src/HyperFormula.ts:3387 (opens new window)

Returns information whether it is possible to rename sheet. Returns true if the sheet with provided id exists and new name is available Returns false if sheet cannot be renamed

throws [[ExpectedValueOfTypeError]] if any of its basic type argument is of wrong type

example

const hfInstance = HyperFormula.buildFromSheets({
  MySheet1: [ ['1'] ],
  MySheet2: [ ['10'] ],
});

// returns true
hfInstance.isItPossibleToRenameSheet(0, 'MySheet0');

Parameters:

Name	Type	Description
sheetId	number	a sheet number
newName	string	a name of the sheet to be given

Returns: boolean

---

isItPossibleToReplaceSheetContent

▸ isItPossibleToReplaceSheetContent(sheetId: number, values: RawCellContent[][]): boolean

Defined in src/HyperFormula.ts:2748 (opens new window)

Returns information whether it is possible to replace the sheet content. If returns true, doing setSheetContent operation won't throw any errors, the provided sheet exists and then its content can be replaced. Returns false otherwise

throws [[ExpectedValueOfTypeError]] if any of its basic type argument is of wrong type

example

const hfInstance = HyperFormula.buildFromSheets({
 MySheet1: [ ['1'] ],
 MySheet2: [ ['10'] ],
});

// should return 'true' because sheet of ID 0 exists
// and the provided content can be placed in this sheet
const isReplaceable = hfInstance.isItPossibleToReplaceSheetContent(0, [['50'], ['60']]);

Parameters:

Name	Type	Description
sheetId	number	sheet ID.
values	RawCellContent[][]	array of new values

Returns: boolean

---

removeSheet

▸ removeSheet(sheetId: number): ExportedChange[]

Defined in src/HyperFormula.ts:2645 (opens new window)

Removes a sheet

Returns an array of cells whose values changed as a result of this operation.

Note that this method may trigger dependency graph recalculation.

fires sheetRemoved after the sheet was removed

fires valuesUpdated if recalculation was triggered by this change

throws [[ExpectedValueOfTypeError]] if any of its basic type argument is of wrong type

throws [[NoSheetWithIdError]] when the given sheet ID does not exist

example

const hfInstance = HyperFormula.buildFromSheets({
 MySheet1: [ ['=SUM(MySheet2!A1:A2)'] ],
 MySheet2: [ ['10'] ],
});

// should return a list of cells which values changed after the operation,
// their absolute addresses and new values, in this example it will return:
// [{
//   address: { sheet: 0, col: 0, row: 0 },
//   newValue: { error: [CellError], value: '#REF!' },
// }]
const changes = hfInstance.removeSheet(1);

Parameters:

Name	Type	Description
sheetId	number	sheet ID.

Returns: ExportedChange[]

---

renameSheet

▸ renameSheet(sheetId: number, newName: string): void

Defined in src/HyperFormula.ts:3423 (opens new window)

Renames a specified sheet.

fires sheetRenamed after the sheet was renamed

throws [[ExpectedValueOfTypeError]] if any of its basic type argument is of wrong type

throws [[NoSheetWithIdError]] when the given sheet ID does not exist

throws [[SheetNameAlreadyTakenError]] when the provided sheet name already exists

example

const hfInstance = HyperFormula.buildFromSheets({
  MySheet1: [ ['1'] ],
  MySheet2: [ ['10'] ],
});

// renames the sheet 'MySheet1'
hfInstance.renameSheet(0, 'MySheet0');

Parameters:

Name	Type	Description
sheetId	number	a sheet ID
newName	string	a name of the sheet to be given, if is the same as the old one the method does nothing

Returns: void

---

setSheetContent

▸ setSheetContent(sheetId: number, values: RawCellContent[][]): ExportedChange[]

Defined in src/HyperFormula.ts:2785 (opens new window)

Replaces the sheet content with new values.

Returns an array of cells whose values changed as a result of this operation.

throws [[ExpectedValueOfTypeError]] if any of its basic type argument is of wrong type

throws [[NoSheetWithIdError]] when the given sheet ID does not exist

throws [[InvalidArgumentsError]] when values argument is not an array of arrays

example

const hfInstance = HyperFormula.buildFromSheets({
 MySheet1: [ ['1'] ],
 MySheet2: [ ['10'] ],
});

// should return a list of cells which values changed after the operation,
// their absolute addresses and new values
const changes = hfInstance.setSheetContent(0, [['50'], ['60']]);

Parameters:

Name	Type	Description
sheetId	number	sheet ID.
values	RawCellContent[][]	array of new values

Returns: ExportedChange[]

---

Ranges

getFillRangeData

▸ getFillRangeData(source: SimpleCellRange, target: SimpleCellRange, offsetsFromTarget: boolean): RawCellContent[][]

Defined in src/HyperFormula.ts:2490 (opens new window)

Returns values to fill target range using source range, with properly extending the range using wrap-around heuristic.

throws [[EvaluationSuspendedError]] when the evaluation is suspended

throws [[ExpectedValueOfTypeError]] if source or target are of wrong type

throws [[SheetsNotEqual]] if range provided has distinct sheet numbers for start and end

example

const hfInstance = HyperFormula.buildFromArray([[1, '=A1'], ['=$A$1', '2']]);

// should return [['2', '=$A$1', '2'], ['=A3', 1, '=C3'], ['2', '=$A$1', '2']]
hfInstance.getFillRangeData( {start: {sheet: 0, row: 0, col: 0}, end: {sheet: 0, row: 1, col: 1}},
{start: {sheet: 0, row: 1, col: 1}, end: {sheet: 0, row: 3, col: 3}});

Parameters:

Name	Type	Default	Description
source	SimpleCellRange	-	of data
target	SimpleCellRange	-	range where data is intended to be put
offsetsFromTarget	boolean	false	if true, offsets are computed from target corner, otherwise from source corner

Returns: RawCellContent[][]

---

getRangeFormulas

▸ getRangeFormulas(source: SimpleCellRange): (string | undefined)[][]

Defined in src/HyperFormula.ts:2420 (opens new window)

Returns cell formulas in given range.

throws [[ExpectedValueOfTypeError]] if source is of wrong type

throws [[SheetsNotEqual]] if range provided has distinct sheet numbers for start and end

throws [[NoSheetWithIdError]] when the given sheet ID does not exist

example

const hfInstance = HyperFormula.buildFromArray([
 ['=SUM(1, 2)', '2', '10'],
 ['5', '6', '7'],
 ['40', '30', '20'],
]);

// returns cell formulas of a given range only:
// [ [ '=SUM(1, 2)', undefined ], [ undefined, undefined ] ]
const rangeFormulas = hfInstance.getRangeFormulas({ start: { sheet: 0, col: 0, row: 0 }, end: { sheet: 0, col: 1, row: 1 } });

Parameters:

Name	Type	Description
source	SimpleCellRange	rectangular range

Returns: (string | undefined)[][]

---

getRangeSerialized

▸ getRangeSerialized(source: SimpleCellRange): RawCellContent[][]

Defined in src/HyperFormula.ts:2456 (opens new window)

Returns serialized cells in given range.

throws [[ExpectedValueOfTypeError]] if source is of wrong type

throws [[SheetsNotEqual]] if range provided has distinct sheet numbers for start and end

throws [[NoSheetWithIdError]] when the given sheet ID does not exist

example

const hfInstance = HyperFormula.buildFromArray([
 ['=SUM(1, 2)', '2', '10'],
 ['5', '6', '7'],
 ['40', '30', '20'],
]);

// should return serialized cell content for the given range:
// [ [ '=SUM(1, 2)', 2 ], [ 5, 6 ] ]
const rangeSerialized = hfInstance.getRangeSerialized({ start: { sheet: 0, col: 0, row: 0 }, end: { sheet: 0, col: 1, row: 1 } });

Parameters:

Name	Type	Description
source	SimpleCellRange	rectangular range

Returns: RawCellContent[][]

---

getRangeValues

▸ getRangeValues(source: SimpleCellRange): CellValue[][]

Defined in src/HyperFormula.ts:2384 (opens new window)

Returns the cell content of a given range in a [[CellValue]][][] format.

throws [[ExpectedValueOfTypeError]] if source is of wrong type

throws [[SheetsNotEqual]] if range provided has distinct sheet numbers for start and end

throws [[NoSheetWithIdError]] when the given sheet ID does not exist

example

const hfInstance = HyperFormula.buildFromArray([
 ['=SUM(1, 2)', '2', '10'],
 ['5', '6', '7'],
 ['40', '30', '20'],
]);

// returns calculated cells content: [ [ 3, 2 ], [ 5, 6 ] ]
const rangeValues = hfInstance.getRangeValues({ start: { sheet: 0, col: 0, row: 0 }, end: { sheet: 0, col: 1, row: 1 } });

Parameters:

Name	Type	Description
source	SimpleCellRange	rectangular range

Returns: CellValue[][]

---

Rows

addRows

▸ addRows(sheetId: number, ...indexes: ColumnRowIndex[]): ExportedChange[]

Defined in src/HyperFormula.ts:1641 (opens new window)

Adds multiple rows into a specified position in a given sheet. Does nothing if rows are outside effective sheet size.

Returns an array of cells whose values changed as a result of this operation.

Note that this method may trigger dependency graph recalculation.

fires valuesUpdated if recalculation was triggered by this change

throws [[ExpectedValueOfTypeError]] if any of its basic type argument is of wrong type

throws [[NoSheetWithIdError]] when the given sheet ID does not exist

throws [[SheetSizeLimitExceededError]] when performing this operation would result in sheet size limits exceeding

example

const hfInstance = HyperFormula.buildFromArray([
 ['1'],
 ['2'],
]);

// should return a list of cells which values changed after the operation,
// their absolute addresses and new values
const changes = hfInstance.addRows(0, [0, 1]);

Parameters:

Name	Type	Description
sheetId	number	sheet ID in which rows will be added
...indexes	ColumnRowIndex[]	non-contiguous indexes with format [row, amount], where row is a row number above which the rows will be added

Returns: ExportedChange[]

---

isItPossibleToAddRows

▸ isItPossibleToAddRows(sheetId: number, ...indexes: ColumnRowIndex[]): boolean

Defined in src/HyperFormula.ts:1599 (opens new window)

Returns information whether it is possible to add rows into a specified position in a given sheet. Checks against particular rules to ascertain that addRows can be called. If returns true, doing addRows operation won't throw any errors. Returns false if adding rows would exceed the sheet size limit or given arguments are invalid.

throws [[ExpectedValueOfTypeError]] if any of its basic type argument is of wrong type

example

const hfInstance = HyperFormula.buildFromArray([
 ['1', '2', '3'],
]);

// should return 'true' for this example,
// it is possible to add one row in the second row of sheet 0
const isAddable = hfInstance.isItPossibleToAddRows(0, [1, 1]);

Parameters:

Name	Type	Description
sheetId	number	sheet ID in which rows will be added
...indexes	ColumnRowIndex[]	non-contiguous indexes with format [row, amount], where row is a row number above which the rows will be added

Returns: boolean

---

isItPossibleToMoveRows

▸ isItPossibleToMoveRows(sheetId: number, startRow: number, numberOfRows: number, targetRow: number): boolean

Defined in src/HyperFormula.ts:1996 (opens new window)

Returns information whether it is possible to move a particular number of rows to a specified position in a given sheet. Checks against particular rules to ascertain that moveRows can be called. If returns true, doing moveRows operation won't throw any errors. Returns false if the operation might be disrupted and causes side effects by the fact that there is an array inside the selected rows, the target location includes an array or the provided address is invalid.

throws [[ExpectedValueOfTypeError]] if any of its basic type argument is of wrong type

example

const hfInstance = HyperFormula.buildFromArray([
 ['1'],
 ['2'],
]);

// should return 'true' for this example
// it is possible to move one row from row 0 into row 2
const isMovable = hfInstance.isItPossibleToMoveRows(0, 0, 1, 2);

Parameters:

Name	Type	Description
sheetId	number	a sheet number in which the operation will be performed
startRow	number	number of the first row to move
numberOfRows	number	number of rows to move
targetRow	number	row number before which rows will be moved

Returns: boolean

---

isItPossibleToRemoveRows

▸ isItPossibleToRemoveRows(sheetId: number, ...indexes: ColumnRowIndex[]): boolean

Defined in src/HyperFormula.ts:1672 (opens new window)

Returns information whether it is possible to remove rows from a specified position in a given sheet. Checks against particular rules to ascertain that removeRows can be called. If returns true, doing removeRows operation won't throw any errors. Returns false if given arguments are invalid.

throws [[ExpectedValueOfTypeError]] if any of its basic type argument is of wrong type

example

const hfInstance = HyperFormula.buildFromArray([
 ['1'],
 ['2'],
]);

// should return 'true' for this example
// it is possible to remove one row from row 1 of sheet 0
const isRemovable = hfInstance.isItPossibleToRemoveRows(0, [1, 1]);

Parameters:

Name	Type	Description
sheetId	number	sheet ID from which rows will be removed
...indexes	ColumnRowIndex[]	non-contiguous indexes with format: [row, amount]

Returns: boolean

---

isItPossibleToSetRowOrder

▸ isItPossibleToSetRowOrder(sheetId: number, newRowOrder: number[]): boolean

Defined in src/HyperFormula.ts:1402 (opens new window)

Checks if it is possible to reorder rows of a sheet according to a permutation.

throws [[ExpectedValueOfTypeError]] if any of its basic type argument is of wrong type

example

const hfInstance = HyperFormula.buildFromArray([
 [1],
 [2],
 [4, 5],
]);

// returns true
hfInstance.isItPossibleToSetRowOrder(0, [2, 1, 0]);

// returns false
hfInstance.isItPossibleToSetRowOrder(0, [2]);

Parameters:

Name	Type	Description
sheetId	number	ID of a sheet to operate on
newRowOrder	number[]	permutation of rows

Returns: boolean

---

isItPossibleToSwapRowIndexes

▸ isItPossibleToSwapRowIndexes(sheetId: number, rowMapping: [number, number][]): boolean

Defined in src/HyperFormula.ts:1323 (opens new window)

Checks if it is possible to reorder rows of a sheet according to a source-target mapping.

throws [[ExpectedValueOfTypeError]] if any of its basic type argument is of wrong type

example

const hfInstance = HyperFormula.buildFromArray([
 [1],
 [2],
 [4, 5],
]);

// returns true
const isSwappable = hfInstance.isItPossibleToSwapRowIndexes(0, [[0, 2], [2, 0]]);

// returns false
const isSwappable = hfInstance.isItPossibleToSwapRowIndexes(0, [[0, 1]]);

Parameters:

Name	Type	Description
sheetId	number	ID of a sheet to operate on
rowMapping	[number, number][]	array mapping original positions to final positions of rows

Returns: boolean

---

moveRows

▸ moveRows(sheetId: number, startRow: number, numberOfRows: number, targetRow: number): ExportedChange[]

Defined in src/HyperFormula.ts:2043 (opens new window)

Moves a particular number of rows to a specified position in a given sheet.

Returns an array of cells whose values changed as a result of this operation.

Note that this method may trigger dependency graph recalculation.

fires valuesUpdated if recalculation was triggered by this change

throws [[NoSheetWithIdError]] when the given sheet ID does not exist

throws [[ExpectedValueOfTypeError]] if any of its basic type argument is of wrong type

throws [[InvalidArgumentsError]] when the given arguments are invalid

throws [[SourceLocationHasArrayError]] when the source location has array inside - array cannot be moved

throws [[TargetLocationHasArrayError]] when the target location has array inside - cells cannot be replaced by the array

example

const hfInstance = HyperFormula.buildFromArray([
 ['1'],
 ['2'],
]);

// should return a list of cells which values changed after the operation,
// their absolute addresses and new values
const changes = hfInstance.moveRows(0, 0, 1, 2);

Parameters:

Name	Type	Description
sheetId	number	a sheet number in which the operation will be performed
startRow	number	number of the first row to move
numberOfRows	number	number of rows to move
targetRow	number	row number before which rows will be moved

Returns: ExportedChange[]

---

removeRows

▸ removeRows(sheetId: number, ...indexes: ColumnRowIndex[]): ExportedChange[]

Defined in src/HyperFormula.ts:1713 (opens new window)

Removes multiple rows from a specified position in a given sheet. Does nothing if rows are outside the effective sheet size.

Returns an array of cells whose values changed as a result of this operation.

Note that this method may trigger dependency graph recalculation.

fires valuesUpdated if recalculation was triggered by this change

throws [[ExpectedValueOfTypeError]] if any of its basic type argument is of wrong type

throws [[InvalidArgumentsError]] when the given arguments are invalid

throws [[NoSheetWithIdError]] when the given sheet ID does not exist

example

const hfInstance = HyperFormula.buildFromArray([
 ['1'],
 ['2'],
]);

// should return: [{ sheet: 0, col: 1, row: 2, value: null }] for this example
const changes = hfInstance.removeRows(0, [1, 1]);

Parameters:

Name	Type	Description
sheetId	number	sheet ID from which rows will be removed
...indexes	ColumnRowIndex[]	non-contiguous indexes with format: [row, amount]

Returns: ExportedChange[]

---

setRowOrder

▸ setRowOrder(sheetId: number, newRowOrder: number[]): ExportedChange[]

Defined in src/HyperFormula.ts:1371 (opens new window)

Reorders rows of a sheet according to a permutation of 0-based indexes. Parameter newRowOrder should have a form [ newPositionForRow0, newPositionForRow1, newPositionForRow2, ... ]. This method might be used to sort the rows of a sheet.

Returns an array of cells whose values changed as a result of this operation.

Note: This method may trigger dependency graph recalculation.

fires valuesUpdated if recalculation was triggered by this change

throws [[ExpectedValueOfTypeError]] if any of its basic type argument is of wrong type

throws [[NoSheetWithIdError]] when the given sheet ID does not exist

throws [[InvalidArgumentsError]] when rowMapping does not define correct row permutation for some subset of rows of the given sheet

throws [[SourceLocationHasArrayError]] when the selected position has array inside

example

const hfInstance = HyperFormula.buildFromArray([
 ['A'],
 ['B'],
 ['C'],
 ['D']
]);

const newRowOrder = [0, 3, 2, 1]; // [ newPosForA, newPosForB, newPosForC, newPosForD ]

const changes = hfInstance.setRowOrder(0, newRowOrder);

// Sheet after this operation: [['A'], ['D'], ['C'], ['B']]

Parameters:

Name	Type	Description
sheetId	number	ID of a sheet to operate on
newRowOrder	number[]	permutation of rows

Returns: ExportedChange[]

---

swapRowIndexes

▸ swapRowIndexes(sheetId: number, rowMapping: [number, number][]): ExportedChange[]

Defined in src/HyperFormula.ts:1292 (opens new window)

Reorders rows of a sheet according to a source-target mapping.

Returns an array of cells whose values changed as a result of this operation.

Note that this method may trigger dependency graph recalculation.

fires valuesUpdated if recalculation was triggered by this change

throws [[ExpectedValueOfTypeError]] if any of its basic type argument is of wrong type

throws [[NoSheetWithIdError]] when the given sheet ID does not exist

throws [[InvalidArgumentsError]] when rowMapping does not define correct row permutation for some subset of rows of the given sheet

throws [[SourceLocationHasArrayError]] when the selected position has array inside

example

const hfInstance = HyperFormula.buildFromArray([
 [1],
 [2],
 [4, 5],
]);

// should set swap rows 0 and 2 in place, returns:
// [{
//   address: { sheet: 0, col: 0, row: 2 },
//   newValue: 1,
// },
// {
//   address: { sheet: 0, col: 1, row: 2 },
//   newValue: null,
// },
// {
//   address: { sheet: 0, col: 0, row: 0 },
//   newValue: 4,
// },
// {
//   address: { sheet: 0, col: 1, row: 0 },
//   newValue: 5,
// }]
const changes = hfInstance.swapRowIndexes(0, [[0, 2], [2, 0]]);

Parameters:

Name	Type	Description
sheetId	number	ID of a sheet to operate on
rowMapping	[number, number][]	array mapping original positions to final positions of rows

Returns: ExportedChange[]

---

Columns

addColumns

▸ addColumns(sheetId: number, ...indexes: ColumnRowIndex[]): ExportedChange[]

Defined in src/HyperFormula.ts:1789 (opens new window)

Adds multiple columns into a specified position in a given sheet. Does nothing if the columns are outside the effective sheet size.

Returns an array of cells whose values changed as a result of this operation.

Note that this method may trigger dependency graph recalculation.

fires valuesUpdated if recalculation was triggered by this change

throws [[ExpectedValueOfTypeError]] if any of its basic type argument is of wrong type

throws [[NoSheetWithIdError]] when the given sheet ID does not exist

throws [[InvalidArgumentsError]] when the given arguments are invalid

throws [[SheetSizeLimitExceededError]] when performing this operation would result in sheet size limits exceeding

example

const hfInstance = HyperFormula.buildFromArray([
 ['=RAND()', '42'],
]);

// should return a list of cells which values changed after the operation,
// their absolute addresses and new values, for this example:
// [{
//   address: { sheet: 0, col: 1, row: 0 },
//   newValue: 0.92754862796338,
// }]
const changes = hfInstance.addColumns(0, [0, 1]);

Parameters:

Name	Type	Description
sheetId	number	sheet ID in which columns will be added
...indexes	ColumnRowIndex[]	non-contiguous indexes with format: [column, amount], where column is a column number from which new columns will be added

Returns: ExportedChange[]

---

isItPossibleToAddColumns

▸ isItPossibleToAddColumns(sheetId: number, ...indexes: ColumnRowIndex[]): boolean

Defined in src/HyperFormula.ts:1743 (opens new window)

Returns information whether it is possible to add columns into a specified position in a given sheet. Checks against particular rules to ascertain that addColumns can be called. If returns true, doing addColumns operation won't throw any errors. Returns false if adding columns would exceed the sheet size limit or given arguments are invalid.

throws [[ExpectedValueOfTypeError]] if any of its basic type argument is of wrong type

example

const hfInstance = HyperFormula.buildFromArray([
 ['1', '2'],
]);

// should return 'true' for this example,
// it is possible to add 1 column in sheet 0, at column 1
const isAddable = hfInstance.isItPossibleToAddColumns(0, [1, 1]);

Parameters:

Name	Type	Description
sheetId	number	sheet ID in which columns will be added
...indexes	ColumnRowIndex[]	non-contiguous indexes with format: [column, amount], where column is a column number from which new columns will be added

Returns: boolean

---

isItPossibleToMoveColumns

▸ isItPossibleToMoveColumns(sheetId: number, startColumn: number, numberOfColumns: number, targetColumn: number): boolean

Defined in src/HyperFormula.ts:2078 (opens new window)

Returns information whether it is possible to move a particular number of columns to a specified position in a given sheet. Checks against particular rules to ascertain that moveColumns can be called. If returns true, doing moveColumns operation won't throw any errors. Returns false if the operation might be disrupted and causes side effects by the fact that there is an array inside the selected columns, the target location includes an array or the provided address is invalid.

throws [[ExpectedValueOfTypeError]] if any of its basic type argument is of wrong type

example

const hfInstance = HyperFormula.buildFromArray([
 ['1', '2'],
]);

// should return 'true' for this example
// it is possible to move one column from column 1 into column 2 of sheet 0
const isMovable = hfInstance.isItPossibleToMoveColumns(0, 1, 1, 2);

Parameters:

Name	Type	Description
sheetId	number	a sheet number in which the operation will be performed
startColumn	number	number of the first column to move
numberOfColumns	number	number of columns to move
targetColumn	number	column number before which columns will be moved

Returns: boolean

---

isItPossibleToRemoveColumns

▸ isItPossibleToRemoveColumns(sheetId: number, ...indexes: ColumnRowIndex[]): boolean

Defined in src/HyperFormula.ts:1819 (opens new window)

Returns information whether it is possible to remove columns from a specified position in a given sheet. Checks against particular rules to ascertain that removeColumns can be called. If returns true, doing removeColumns operation won't throw any errors. Returns false if given arguments are invalid.

throws [[ExpectedValueOfTypeError]] if any of its basic type argument is of wrong type

example

const hfInstance = HyperFormula.buildFromArray([
 ['1', '2'],
]);

// should return 'true' for this example
// it is possible to remove one column, in place of the second column of sheet 0
const isRemovable = hfInstance.isItPossibleToRemoveColumns(0, [1, 1]);

Parameters:

Name	Type	Description
sheetId	number	sheet ID from which columns will be removed
...indexes	ColumnRowIndex[]	non-contiguous indexes with format [column, amount]

Returns: boolean

---

isItPossibleToSetColumnOrder

▸ isItPossibleToSetColumnOrder(sheetId: number, newColumnOrder: number[]): boolean

Defined in src/HyperFormula.ts:1563 (opens new window)

Checks if it is possible to reorder columns of a sheet according to a permutation.

throws [[ExpectedValueOfTypeError]] if any of its basic type argument is of wrong type

example

const hfInstance = HyperFormula.buildFromArray([
 [1, 2, 4],
 [5]
]);

// returns true
hfInstance.isItPossibleToSetColumnOrder(0, [2, 1, 0]);

// returns false
hfInstance.isItPossibleToSetColumnOrder(0, [1]);

Parameters:

Name	Type	Description
sheetId	number	ID of a sheet to operate on
newColumnOrder	number[]	permutation of columns

Returns: boolean

---

isItPossibleToSwapColumnIndexes

▸ isItPossibleToSwapColumnIndexes(sheetId: number, columnMapping: [number, number][]): boolean

Defined in src/HyperFormula.ts:1488 (opens new window)

Checks if it is possible to reorder columns of a sheet according to a source-target mapping.

fires valuesUpdated if recalculation was triggered by this change

throws [[ExpectedValueOfTypeError]] if any of its basic type argument is of wrong type

example

const hfInstance = HyperFormula.buildFromArray([
 [1, 2, 4],
 [5]
]);

// returns true
hfInstance.isItPossibleToSwapColumnIndexes(0, [[0, 2], [2, 0]]);

// returns false
hfInstance.isItPossibleToSwapColumnIndexes(0, [[0, 1]]);

Parameters:

Name	Type
sheetId	number
columnMapping	[number, number][]

Returns: boolean

---

moveColumns

▸ moveColumns(sheetId: number, startColumn: number, numberOfColumns: number, targetColumn: number): ExportedChange[]

Defined in src/HyperFormula.ts:2131 (opens new window)

Moves a particular number of columns to a specified position in a given sheet.

Returns an array of cells whose values changed as a result of this operation.

Note that this method may trigger dependency graph recalculation.

fires valuesUpdated if recalculation was triggered by this change

throws [[NoSheetWithIdError]] when the given sheet ID does not exist

throws [[ExpectedValueOfTypeError]] if any of its basic type argument is of wrong type

throws [[InvalidArgumentsError]] when the given arguments are invalid

throws [[SourceLocationHasArrayError]] when the source location has array inside - array cannot be moved

throws [[TargetLocationHasArrayError]] when the target location has array inside - cells cannot be replaced by the array

example

const hfInstance = HyperFormula.buildFromArray([
 ['1', '2', '3', '=RAND()', '=SUM(A1:C1)'],
]);

// should return a list of cells which values changed after the operation,
// their absolute addresses and new values, for this example:
// [{
//   address: { sheet: 0, col: 1, row: 0 },
//   newValue: 0.16210054671639,
//  }, {
//   address: { sheet: 0, col: 4, row: 0 },
//   newValue: 6.16210054671639,
// }]
const changes = hfInstance.moveColumns(0, 1, 1, 2);

Parameters:

Name	Type	Description
sheetId	number	a sheet number in which the operation will be performed
startColumn	number	number of the first column to move
numberOfColumns	number	number of columns to move
targetColumn	number	column number before which columns will be moved

Returns: ExportedChange[]

---

removeColumns

▸ removeColumns(sheetId: number, ...indexes: ColumnRowIndex[]): ExportedChange[]

Defined in src/HyperFormula.ts:1864 (opens new window)

Removes multiple columns from a specified position in a given sheet. Does nothing if columns are outside the effective sheet size.

Returns an array of cells whose values changed as a result of this operation.

Note that this method may trigger dependency graph recalculation.

fires valuesUpdated if recalculation was triggered by this change

throws [[ExpectedValueOfTypeError]] if any of its basic type argument is of wrong type

throws [[NoSheetWithIdError]] when the given sheet ID does not exist

throws [[InvalidArgumentsError]] when the given arguments are invalid

example

const hfInstance = HyperFormula.buildFromArray([
 ['0', '=SUM(1, 2, 3)', '=A1'],
]);

// should return a list of cells which values changed after the operation,
// their absolute addresses and new values, in this example it will return:
// [{
//   address: { sheet: 0, col: 1, row: 0 },
//   newValue: { error: [CellError], value: '#REF!' },
// }]
const changes = hfInstance.removeColumns(0, [0, 1]);

Parameters:

Name	Type	Description
sheetId	number	sheet ID from which columns will be removed
...indexes	ColumnRowIndex[]	non-contiguous indexes with format: [column, amount]

Returns: ExportedChange[]

---

setColumnOrder

▸ setColumnOrder(sheetId: number, newColumnOrder: number[]): ExportedChange[]

Defined in src/HyperFormula.ts:1533 (opens new window)

Reorders columns of a sheet according to a permutation of 0-based indexes. Parameter newColumnOrder should have a form [ newPositionForColumn0, newPositionForColumn1, newPositionForColumn2, ... ]. This method might be used to sort the columns of a sheet.

Returns an array of cells whose values changed as a result of this operation.

Note: This method may trigger dependency graph recalculation.

fires valuesUpdated if recalculation was triggered by this change

throws [[ExpectedValueOfTypeError]] if any of its basic type argument is of wrong type

throws [[NoSheetWithIdError]] when the given sheet ID does not exist

throws [[InvalidArgumentsError]] when columnMapping does not define correct column permutation for some subset of columns of the given sheet

throws [[SourceLocationHasArrayError]] when the selected position has array inside

example

const hfInstance = HyperFormula.buildFromArray([
  ['A', 'B', 'C', 'D']
]);

const newColumnOrder = [0, 3, 2, 1]; // [ newPosForA, newPosForB, newPosForC, newPosForD ]

const changes = hfInstance.setColumnOrder(0, newColumnOrder);

// Sheet after this operation: [['A', 'D', 'C', 'B']]

Parameters:

Name	Type	Description
sheetId	number	ID of a sheet to operate on
newColumnOrder	number[]	permutation of columns

Returns: ExportedChange[]

---

swapColumnIndexes

▸ swapColumnIndexes(sheetId: number, columnMapping: [number, number][]): ExportedChange[]

Defined in src/HyperFormula.ts:1460 (opens new window)

Reorders columns of a sheet according to a source-target mapping.

Returns an array of cells whose values changed as a result of this operation.

Note that this method may trigger dependency graph recalculation.

fires valuesUpdated if recalculation was triggered by this change

throws [[ExpectedValueOfTypeError]] if any of its basic type argument is of wrong type

throws [[NoSheetWithIdError]] when the given sheet ID does not exist

throws [[InvalidArgumentsError]] when columnMapping does not define correct column permutation for some subset of columns of the given sheet

throws [[SourceLocationHasArrayError]] when the selected position has array inside

example

const hfInstance = HyperFormula.buildFromArray([
 [1, 2, 4],
 [5]
]);

// should set swap columns 0 and 2 in place, returns:
// [{
//   address: { sheet: 0, col: 2, row: 0 },
//   newValue: 1,
// },
// {
//   address: { sheet: 0, col: 2, row: 1 },
//   newValue: 5,
// },
// {
//   address: { sheet: 0, col: 0, row: 0 },
//   newValue: 4,
// },
// {
//   address: { sheet: 0, col: 0, row: 1 },
//   newValue: null,
// }]
const changes = hfInstance.swapColumnIndexes(0, [[0, 2], [2, 0]]);

Parameters:

Name	Type	Description
sheetId	number	ID of a sheet to operate on
columnMapping	[number, number][]	array mapping original positions to final positions of columns

Returns: ExportedChange[]

---

Cells

doesCellHaveFormula

▸ doesCellHaveFormula(cellAddress: SimpleCellAddress): boolean

Defined in src/HyperFormula.ts:3175 (opens new window)

Returns true if the specified cell contains a formula. The method accepts cell coordinates as object with column, row and sheet numbers.

throws [[NoSheetWithIdError]] when the given sheet ID does not exist

throws [[ExpectedValueOfTypeError]] if cellAddress is of wrong type

example

const hfInstance = HyperFormula.buildFromArray([
 ['=SUM(A2:A3)', '2'],
]);

// should return 'true' since the A1 cell contains a formula
const A1Formula = hfInstance.doesCellHaveFormula({ sheet: 0, col: 0, row: 0 });

// should return 'false' since the B1 cell does not contain a formula
const B1NoFormula = hfInstance.doesCellHaveFormula({ sheet: 0, col: 1, row: 0 });

Parameters:

Name	Type	Description
cellAddress	SimpleCellAddress	cell coordinates

Returns: boolean

---

doesCellHaveSimpleValue

▸ doesCellHaveSimpleValue(cellAddress: SimpleCellAddress): boolean

Defined in src/HyperFormula.ts:3144 (opens new window)

Returns true if the specified cell contains a simple value. The method accepts cell coordinates as object with column, row and sheet numbers.

throws [[NoSheetWithIdError]] when the given sheet ID does not exist

throws [[ExpectedValueOfTypeError]] if cellAddress is of wrong type

example

const hfInstance = HyperFormula.buildFromArray([
 ['=SUM(A2:A3)', '2'],
]);

// should return 'true' since the selected cell contains a simple value
const isA1Simple = hfInstance.doesCellHaveSimpleValue({ sheet: 0, col: 0, row: 0 });

// should return 'false' since the selected cell does not contain a simple value
const isB1Simple = hfInstance.doesCellHaveSimpleValue({ sheet: 0, col: 1, row: 0 });

Parameters:

Name	Type	Description
cellAddress	SimpleCellAddress	cell coordinates

Returns: boolean

---

getCellFormula

▸ getCellFormula(cellAddress: SimpleCellAddress): string | undefined

Defined in src/HyperFormula.ts:692 (opens new window)

Returns a normalized formula string from the cell of a given address or undefined for an address that does not exist and empty values.

throws [[NoSheetWithIdError]] when the given sheet ID does not exist

throws [[ExpectedValueOfTypeError]] when cellAddress is of incorrect type

example

const hfInstance = HyperFormula.buildFromArray([
 ['=SUM(1, 2, 3)', '0'],
]);

// should return a normalized A1 cell formula: '=SUM(1, 2, 3)'
const A1Formula = hfInstance.getCellFormula({ sheet: 0, col: 0, row: 0 });

// should return a normalized B1 cell formula: 'undefined'
const B1Formula = hfInstance.getCellFormula({ sheet: 0, col: 1, row: 0 });

Parameters:

Name	Type	Description
cellAddress	SimpleCellAddress	cell coordinates

Returns: string | undefined

---

getCellHyperlink

▸ getCellHyperlink(cellAddress: SimpleCellAddress): string | undefined

Defined in src/HyperFormula.ts:722 (opens new window)

Returns the HYPERLINK url for a cell of a given address or undefined for an address that does not exist or a cell that is not HYPERLINK

throws [[NoSheetWithIdError]] when the given sheet ID does not exist

throws [[ExpectedValueOfTypeError]] when cellAddress is of incorrect type

example

const hfInstance = HyperFormula.buildFromArray([
 ['=HYPERLINK("https://hyperformula.handsontable.com/", "HyperFormula")', '0'],
]);

// should return url of 'HYPERLINK': https://hyperformula.handsontable.com/
const A1Hyperlink = hfInstance.getCellHyperlink({ sheet: 0, col: 0, row: 0 });

// should return 'undefined' for a cell that is not 'HYPERLINK'
const B1Hyperlink = hfInstance.getCellHyperlink({ sheet: 0, col: 1, row: 0 });

Parameters:

Name	Type	Description
cellAddress	SimpleCellAddress	cell coordinates

Returns: string | undefined

---

getCellSerialized

▸ getCellSerialized(cellAddress: SimpleCellAddress): RawCellContent

Defined in src/HyperFormula.ts:754 (opens new window)

Returns [[RawCellContent]] with a serialized content of the cell of a given address: either a cell formula, an explicit value, or an error.

throws [[NoSheetWithIdError]] when the given sheet ID does not exist

throws [[EvaluationSuspendedError]] when the evaluation is suspended

throws [[ExpectedValueOfTypeError]] when cellAddress is of incorrect type

example

const hfInstance = HyperFormula.buildFromArray([
 ['=SUM(1, 2, 3)', '0'],
]);

// should return serialized content of A1 cell: '=SUM(1, 2, 3)'
const cellA1Serialized = hfInstance.getCellSerialized({ sheet: 0, col: 0, row: 0 });

// should return serialized content of B1 cell: '0'
const cellB1Serialized = hfInstance.getCellSerialized({ sheet: 0, col: 1, row: 0 });

Parameters:

Name	Type	Description
cellAddress	SimpleCellAddress	cell coordinates

Returns: RawCellContent

---

getCellType

▸ getCellType(cellAddress: SimpleCellAddress): CellType

Defined in src/HyperFormula.ts:3112 (opens new window)

Returns the type of a cell at a given address. The method accepts cell coordinates as object with column, row and sheet numbers.

throws [[NoSheetWithIdError]] when the given sheet ID does not exist

throws [[ExpectedValueOfTypeError]] if cellAddress is of wrong type

example

const hfInstance = HyperFormula.buildFromArray([
 ['=SUM(A2:A3)', '2'],
]);

// should return 'FORMULA', the cell of given coordinates is of this type
const cellA1Type = hfInstance.getCellType({ sheet: 0, col: 0, row: 0 });

// should return 'VALUE', the cell of given coordinates is of this type
const cellB1Type = hfInstance.getCellType({ sheet: 0, col: 1, row: 0 });

Parameters:

Name	Type	Description
cellAddress	SimpleCellAddress	cell coordinates

Returns: CellType

---

getCellValue

▸ getCellValue(cellAddress: SimpleCellAddress): CellValue

Defined in src/HyperFormula.ts:661 (opens new window)

Returns the cell value of a given address. Applies rounding and post-processing.

throws [[ExpectedValueOfTypeError]] when cellAddress is of incorrect type

throws [[NoSheetWithIdError]] when the given sheet ID does not exist

throws [[EvaluationSuspendedError]] when the evaluation is suspended

example

const hfInstance = HyperFormula.buildFromArray([
 ['=SUM(1, 2, 3)', '2'],
]);

// get value of A1 cell, should be '6'
const A1Value = hfInstance.getCellValue({ sheet: 0, col: 0, row: 0 });

// get value of B1 cell, should be '2'
const B1Value = hfInstance.getCellValue({ sheet: 0, col: 1, row: 0 });

Parameters:

Name	Type	Description
cellAddress	SimpleCellAddress	cell coordinates

Returns: CellValue

---

getCellValueDetailedType

▸ getCellValueDetailedType(cellAddress: SimpleCellAddress): CellValueDetailedType

Defined in src/HyperFormula.ts:3302 (opens new window)

Returns detailed type of the cell value of a given address. The method accepts cell coordinates as object with column, row and sheet numbers.

throws [[NoSheetWithIdError]] when the given sheet ID does not exist

throws [[EvaluationSuspendedError]] when the evaluation is suspended

throws [[ExpectedValueOfTypeError]] if cellAddress is of wrong type

example

const hfInstance = HyperFormula.buildFromArray([
 ['1%', '1$'],
]);

// should return 'NUMBER_PERCENT', cell value type of provided coordinates is a number with a format inference percent.
const cellType = hfInstance.getCellValueDetailedType({ sheet: 0, col: 0, row: 0 });

// should return 'NUMBER_CURRENCY', cell value type of provided coordinates is a number with a format inference currency.
const cellType = hfInstance.getCellValueDetailedType({ sheet: 0, col: 1, row: 0 });

Parameters:

Name	Type	Description
cellAddress	SimpleCellAddress	cell coordinates

Returns: CellValueDetailedType

---

getCellValueFormat

▸ getCellValueFormat(cellAddress: SimpleCellAddress): FormatInfo

Defined in src/HyperFormula.ts:3336 (opens new window)

Returns auxiliary format information of the cell value of a given address. The method accepts cell coordinates as object with column, row and sheet numbers.

throws [[NoSheetWithIdError]] when the given sheet ID does not exist

throws [[EvaluationSuspendedError]] when the evaluation is suspended

throws [[ExpectedValueOfTypeError]] if cellAddress is of wrong type

example

const hfInstance = HyperFormula.buildFromArray([
 ['1$', '1'],
]);

// should return '$', cell value type of provided coordinates is a number with a format inference currency, parsed as using '$' as currency.
const cellFormat = hfInstance.getCellValueFormat({ sheet: 0, col: 0, row: 0 });

// should return undefined, cell value type of provided coordinates is a number with no format information.
const cellFormat = hfInstance.getCellValueFormat({ sheet: 0, col: 1, row: 0 });

Parameters:

Name	Type	Description
cellAddress	SimpleCellAddress	cell coordinates

Returns: FormatInfo

---

getCellValueType

▸ getCellValueType(cellAddress: SimpleCellAddress): CellValueType

Defined in src/HyperFormula.ts:3268 (opens new window)

Returns type of the cell value of a given address. The method accepts cell coordinates as object with column, row and sheet numbers.

throws [[NoSheetWithIdError]] when the given sheet ID does not exist

throws [[EvaluationSuspendedError]] when the evaluation is suspended

throws [[ExpectedValueOfTypeError]] if cellAddress is of wrong type

example

const hfInstance = HyperFormula.buildFromArray([
 ['=SUM(1, 2, 3)', '2'],
]);

// should return 'NUMBER', cell value type of provided coordinates is a number
const cellValue = hfInstance.getCellValueType({ sheet: 0, col: 1, row: 0 });

// should return 'NUMBER', cell value type of provided coordinates is a number
const cellValue = hfInstance.getCellValueType({ sheet: 0, col: 0, row: 0 });

Parameters:

Name	Type	Description
cellAddress	SimpleCellAddress	cell coordinates

Returns: CellValueType

---

isCellEmpty

▸ isCellEmpty(cellAddress: SimpleCellAddress): boolean

Defined in src/HyperFormula.ts:3207 (opens new window)

Returnstrue if the specified cell is empty. The method accepts cell coordinates as object with column, row and sheet numbers.

throws [[NoSheetWithIdError]] when the given sheet ID does not exist

throws [[ExpectedValueOfTypeError]] if cellAddress is of wrong type

example

const hfInstance = HyperFormula.buildFromArray([
  [null, '1'],
]);

// should return 'true', cell of provided coordinates is empty
const isEmpty = hfInstance.isCellEmpty({ sheet: 0, col: 0, row: 0 });

// should return 'false', cell of provided coordinates is not empty
const isNotEmpty = hfInstance.isCellEmpty({ sheet: 0, col: 1, row: 0 });

Parameters:

Name	Type	Description
cellAddress	SimpleCellAddress	cell coordinates

Returns: boolean

---

isCellPartOfArray

▸ isCellPartOfArray(cellAddress: SimpleCellAddress): boolean

Defined in src/HyperFormula.ts:3235 (opens new window)

Returns true if a given cell is a part of an array. The method accepts cell coordinates as object with column, row and sheet numbers.

throws [[NoSheetWithIdError]] when the given sheet ID does not exist

throws [[ExpectedValueOfTypeError]] if cellAddress is of wrong type

example

const hfInstance = HyperFormula.buildFromArray([
   ['{=TRANSPOSE(B1:B1)}'],
]);

// should return 'true', cell of provided coordinates is a part of an array
const isPartOfArray = hfInstance.isCellPartOfArray({ sheet: 0, col: 0, row: 0 });

Parameters:

Name	Type	Description
cellAddress	SimpleCellAddress	cell coordinates

Returns: boolean

---

isItPossibleToMoveCells

▸ isItPossibleToMoveCells(source: SimpleCellRange, destinationLeftCorner: SimpleCellAddress): boolean

Defined in src/HyperFormula.ts:1900 (opens new window)

Returns information whether it is possible to move cells to a specified position in a given sheet. Checks against particular rules to ascertain that moveCells can be called. If returns true, doing moveCells operation won't throw any errors. Returns false if the operation might be disrupted and causes side effects by the fact that there is an array inside the selected columns, the target location includes an array or the provided address is invalid.

throws [[ExpectedValueOfTypeError]] if destinationLeftCorner, source, or any of basic type arguments are of wrong type

throws [[SheetsNotEqual]] if range provided has distinct sheet numbers for start and end

example

const hfInstance = HyperFormula.buildFromArray([
 ['1', '2'],
]);

// choose the coordinates and assign them to variables
const source = { sheet: 0, col: 1, row: 0 };
const destination = { sheet: 0, col: 3, row: 0 };

// should return 'true' for this example
// it is possible to move a block of width 1 and height 1
// from the corner: column 1 and row 0 of sheet 0
// into destination corner: column 3, row 0 of sheet 0
const isMovable = hfInstance.isItPossibleToMoveCells({ start: source, end: source }, destination);

Parameters:

Name	Type	Description
source	SimpleCellRange	range for a moved block
destinationLeftCorner	SimpleCellAddress	upper left address of the target cell block

Returns: boolean

---

isItPossibleToSetCellContents

▸ isItPossibleToSetCellContents(address: SimpleCellAddress | SimpleCellRange): boolean

Defined in src/HyperFormula.ts:1187 (opens new window)

Returns information whether it is possible to change the content in a rectangular area bounded by the box. If returns true, doing setCellContents operation won't throw any errors. Returns false if the address is invalid or the sheet does not exist.

throws [[ExpectedValueOfTypeError]] if any of its basic type argument is of wrong type

throws [[SheetsNotEqual]] if range provided has distinct sheet numbers for start and end

example

const hfInstance = HyperFormula.buildFromArray([
 ['1', '2'],
]);

// top left corner
const address1 = { col: 0, row: 0, sheet: 0 };
// bottom right corner
const address2 = { col: 1, row: 0, sheet: 0 };

// should return 'true' for this example, it is possible to set content of
// width 2, height 1 in the first row and column of sheet 0
const isSettable = hfInstance.isItPossibleToSetCellContents({ start: address1, end: address2 });

Parameters:

Name	Type	Description
address	SimpleCellAddress | SimpleCellRange	single cell or block of cells to check

Returns: boolean

---

moveCells

▸ moveCells(source: SimpleCellRange, destinationLeftCorner: SimpleCellAddress): ExportedChange[]

Defined in src/HyperFormula.ts:1957 (opens new window)

Moves the content of a cell block from source to the target location.

Returns an array of cells whose values changed as a result of this operation.

Note that this method may trigger dependency graph recalculation.

fires valuesUpdated if recalculation was triggered by this change

throws [[NoSheetWithIdError]] when the given sheet ID does not exist

throws [[ExpectedValueOfTypeError]] if destinationLeftCorner or source are of wrong type

throws [[InvalidArgumentsError]] when the given arguments are invalid

throws [[SheetSizeLimitExceededError]] when performing this operation would result in sheet size limits exceeding

throws [[SourceLocationHasArrayError]] when the source location has array inside - array cannot be moved

throws [[TargetLocationHasArrayError]] when the target location has array inside - cells cannot be replaced by the array

throws [[SheetsNotEqual]] if range provided has distinct sheet numbers for start and end

example

const hfInstance = HyperFormula.buildFromArray([
 ['=RAND()', '42'],
]);

// choose the coordinates and assign them to variables
const source = { sheet: 0, col: 1, row: 0 };
const destination = { sheet: 0, col: 3, row: 0 };

// should return a list of cells which values changed after the operation,
// their absolute addresses and new values, for this example:
// [{
//   address: { sheet: 0, col: 0, row: 0 },
//   newValue: 0.93524248002062,
// }]
const changes = hfInstance.moveCells({ start: source, end: source }, destination);

Parameters:

Name	Type	Description
source	SimpleCellRange	range for a moved block
destinationLeftCorner	SimpleCellAddress	upper left address of the target cell block

Returns: ExportedChange[]

---

setCellContents

▸ setCellContents(topLeftCornerAddress: SimpleCellAddress, cellContents: RawCellContent[][] | RawCellContent): ExportedChange[]

Defined in src/HyperFormula.ts:1240 (opens new window)

Sets the content for a block of cells of a given coordinates.

Returns an array of cells whose values changed as a result of this operation.

Note that this method may trigger dependency graph recalculation.

fires valuesUpdated if recalculation was triggered by this change

throws [[NoSheetWithIdError]] when the given sheet ID does not exist

throws [[InvalidArgumentsError]] when the value is not an array of arrays or a raw cell value

throws [[SheetSizeLimitExceededError]] when performing this operation would result in sheet size limits exceeding

throws [[ExpectedValueOfTypeError]] if topLeftCornerAddress argument is of wrong type

example

const hfInstance = HyperFormula.buildFromArray([
 ['1', '2', '=A1'],
]);

// should set the content, returns:
// [{
//   address: { sheet: 0, col: 3, row: 0 },
//   newValue: 2,
// }]
const changes = hfInstance.setCellContents({ col: 3, row: 0, sheet: 0 }, [['=B1']]);

Parameters:

Name	Type	Description
topLeftCornerAddress	SimpleCellAddress	top left corner of block of cells
cellContents	RawCellContent[][] | RawCellContent	array with content

Returns: ExportedChange[]

---

Named Expressions

addNamedExpression

▸ addNamedExpression(expressionName: string, expression: RawCellContent, scope?: undefined | number, options?: NamedExpressionOptions): ExportedChange[]

Defined in src/HyperFormula.ts:3653 (opens new window)

Adds a specified named expression.

Returns an array of cells whose values changed as a result of this operation.

Note that this method may trigger dependency graph recalculation.

fires namedExpressionAdded always, unless batch mode is used

fires valuesUpdated if recalculation was triggered by this change

throws [[ExpectedValueOfTypeError]] if any of its basic type argument is of wrong type

throws [[NamedExpressionNameIsAlreadyTakenError]] when the named-expression name is not available.

throws [[NamedExpressionNameIsInvalidError]] when the named-expression name is not valid

throws [[NoRelativeAddressesAllowedError]] when the named-expression formula contains relative references

throws [[NoSheetWithIdError]] if no sheet with given sheetId exists

example

const hfInstance = HyperFormula.buildFromArray([
 ['42'],
]);

// add own expression, scope limited to 'Sheet1' (sheetId=0), the method should return a list of cells which values
// changed after the operation, their absolute addresses and new values
// for this example:
// [{
//   name: 'prettyName',
//   newValue: 142,
// }]
const changes = hfInstance.addNamedExpression('prettyName', '=Sheet1!$A$1+100', 0);

Parameters:

Name	Type	Description
expressionName	string	a name of the expression to be added
expression	RawCellContent	the expression
scope?	undefined | number	scope definition, sheetId for local scope or undefined for global scope
options?	NamedExpressionOptions	additional metadata related to named expression

Returns: ExportedChange[]

---

changeNamedExpression

▸ changeNamedExpression(expressionName: string, newExpression: RawCellContent, scope?: undefined | number, options?: NamedExpressionOptions): ExportedChange[]

Defined in src/HyperFormula.ts:3869 (opens new window)

Changes a given named expression to a specified formula.

Returns an array of cells whose values changed as a result of this operation.

Note that this method may trigger dependency graph recalculation.

fires valuesUpdated if recalculation was triggered by this change

throws [[ExpectedValueOfTypeError]] if any of its basic type argument is of wrong type

throws [[NamedExpressionDoesNotExistError]] when the given expression does not exist.

throws [[NoSheetWithIdError]] if no sheet with given sheetId exists

throws [[ArrayFormulasNotSupportedError]] when the named expression formula is an array formula

throws [[NoRelativeAddressesAllowedError]] when the named expression formula contains relative references

example

const hfInstance = HyperFormula.buildFromArray([
 ['42'],
]);

// add a named expression, scope limited to 'Sheet1' (sheetId=0)
hfInstance.addNamedExpression('prettyName', '=Sheet1!$A$1+100', 0);

// change the named expression
const changes = hfInstance.changeNamedExpression('prettyName', '=Sheet1!$A$1+200');

Parameters:

Name	Type	Description
expressionName	string	an expression name, case-insensitive.
newExpression	RawCellContent	a new expression
scope?	undefined | number	scope definition, sheetId for local scope or undefined for global scope
options?	NamedExpressionOptions	additional metadata related to named expression

Returns: ExportedChange[]

---

getAllNamedExpressionsSerialized

▸ getAllNamedExpressionsSerialized(): SerializedNamedExpression[]

Defined in src/HyperFormula.ts:4033 (opens new window)

Returns all named expressions serialized.

example

const hfInstance = HyperFormula.buildFromArray([
 ['42'],
 ['50'],
 ['60'],
]);

// add two named expressions and one scoped
hfInstance.addNamedExpression('prettyName', '=Sheet1!$A$1+100');
hfInstance.addNamedExpression('anotherPrettyName', '=Sheet1!$A$2+100');
hfInstance.addNamedExpression('prettyName3', '=Sheet1!$A$3+100', 0);

// get all expressions serialized
// should return:
// [
// {name: 'prettyName', expression: '=Sheet1!$A$1+100', options: undefined, scope: undefined},
// {name: 'anotherPrettyName', expression: '=Sheet1!$A$2+100', options: undefined, scope: undefined},
// {name: 'alsoPrettyName', expression: '=Sheet1!$A$3+100', options: undefined, scope: 0}
// ]
const allExpressions = hfInstance.getAllNamedExpressionsSerialized();

Returns: SerializedNamedExpression[]

---

getNamedExpression

▸ getNamedExpression(expressionName: string, scope?: undefined | number): NamedExpression | undefined

Defined in src/HyperFormula.ts:3772 (opens new window)

Returns a named expression, or undefined for a named expression that does not exist or does not hold a formula.

throws [[ExpectedValueOfTypeError]] if any of its basic type argument is of wrong type

throws [[NoSheetWithIdError]] if no sheet with given sheetId exists

example

const hfInstance = HyperFormula.buildFromArray([
 ['42'],
]);

// add a named expression in 'Sheet1' (sheetId=0)
hfInstance.addNamedExpression('prettyName', '=Sheet1!$A$1+100', 0);

// returns a named expression that corresponds to the passed name from 'Sheet1' (sheetId=0)
// for this example, returns:
// {name: 'prettyName', expression: '=Sheet1!$A$1+100', options: undefined, scope: 0}
const myFormula = hfInstance.getNamedExpression('prettyName', 0);

// for a named expression that doesn't exist, returns 'undefined':
const myFormulaTwo = hfInstance.getNamedExpression('uglyName', 0);

Parameters:

Name	Type	Description
expressionName	string	expression name, case-insensitive.
scope?	undefined | number	scope definition, sheetId for local scope or undefined for global scope

Returns: NamedExpression | undefined

---

getNamedExpressionFormula

▸ getNamedExpressionFormula(expressionName: string, scope?: undefined | number): string | undefined

Defined in src/HyperFormula.ts:3729 (opens new window)

Returns a normalized formula string for given named expression, or undefined for a named expression that does not exist or does not hold a formula.

throws [[ExpectedValueOfTypeError]] if any of its basic type argument is of wrong type

throws [[NoSheetWithIdError]] if no sheet with given sheetId exists

example

const hfInstance = HyperFormula.buildFromArray([
 ['42'],
]);

// add a named expression in 'Sheet1' (sheetId=0)
hfInstance.addNamedExpression('prettyName', '=Sheet1!$A$1+100', 0);

// returns a normalized formula string corresponding to the passed name from 'Sheet1' (sheetId=0),
// '=Sheet1!A1+100' for this example
const myFormula = hfInstance.getNamedExpressionFormula('prettyName', 0);

Parameters:

Name	Type	Description
expressionName	string	expression name, case-insensitive.
scope?	undefined | number	scope definition, sheetId for local scope or undefined for global scope

Returns: string | undefined

---

getNamedExpressionValue

▸ getNamedExpressionValue(expressionName: string, scope?: undefined | number): CellValue | undefined

Defined in src/HyperFormula.ts:3689 (opens new window)

Gets specified named expression value. Returns a [[CellValue]] or undefined if the given named expression does not exist.

throws [[ExpectedValueOfTypeError]] if any of its basic type argument is of wrong type

throws [[NoSheetWithIdError]] if no sheet with given sheetId exists

example

const hfInstance = HyperFormula.buildFromArray([
 ['42'],
]);

// add a named expression, only 'Sheet1' (sheetId=0) considered as it is the scope
hfInstance.addNamedExpression('prettyName', '=Sheet1!$A$1+100', 'Sheet1');

// returns the calculated value of a passed named expression, '142' for this example
const myFormula = hfInstance.getNamedExpressionValue('prettyName', 'Sheet1');

Parameters:

Name	Type	Description
expressionName	string	expression name, case-insensitive.
scope?	undefined | number	scope definition, sheetId for local scope or undefined for global scope

Returns: CellValue | undefined

---

isItPossibleToAddNamedExpression

▸ isItPossibleToAddNamedExpression(expressionName: string, expression: RawCellContent, scope?: undefined | number): boolean

Defined in src/HyperFormula.ts:3601 (opens new window)

Returns information whether it is possible to add named expression into a specific scope. Checks against particular rules to ascertain that addNamedExpression can be called. If returns true, doing addNamedExpression operation won't throw any errors. Returns false if the operation might be disrupted.

throws [[ExpectedValueOfTypeError]] if any of its basic type argument is of wrong type

example

const hfInstance = HyperFormula.buildFromArray([
 ['42'],
]);

// should return 'true' for this example,
// it is possible to add named expression to global scope
const isAddable = hfInstance.isItPossibleToAddNamedExpression('prettyName', '=Sheet1!$A$1+100');

Parameters:

Name	Type	Description
expressionName	string	a name of the expression to be added
expression	RawCellContent	the expression
scope?	undefined | number	scope definition, sheetId for local scope or undefined for global scope

Returns: boolean

---

isItPossibleToChangeNamedExpression

▸ isItPossibleToChangeNamedExpression(expressionName: string, newExpression: RawCellContent, scope?: undefined | number): boolean

Defined in src/HyperFormula.ts:3821 (opens new window)

Returns information whether it is possible to change named expression in a specific scope. Checks against particular rules to ascertain that changeNamedExpression can be called. If returns true, doing changeNamedExpression operation won't throw any errors. Returns false if the operation might be disrupted.

throws [[ExpectedValueOfTypeError]] if any of its basic type argument is of wrong type

example

const hfInstance = HyperFormula.buildFromArray([
 ['42'],
]);

// add a named expression
hfInstance.addNamedExpression('prettyName', '=Sheet1!$A$1+100');

// should return 'true' for this example,
// it is possible to change named expression
const isAddable = hfInstance.isItPossibleToChangeNamedExpression('prettyName', '=Sheet1!$A$1+100');

Parameters:

Name	Type	Description
expressionName	string	an expression name, case-insensitive.
newExpression	RawCellContent	a new expression
scope?	undefined | number	scope definition, sheetId for local scope or undefined for global scope

Returns: boolean

---

isItPossibleToRemoveNamedExpression

▸ isItPossibleToRemoveNamedExpression(expressionName: string, scope?: undefined | number): boolean

Defined in src/HyperFormula.ts:3905 (opens new window)

Returns information whether it is possible to remove named expression from a specific scope. Checks against particular rules to ascertain that removeNamedExpression can be called. If returns true, doing removeNamedExpression operation won't throw any errors. Returns false if the operation might be disrupted.

throws [[ExpectedValueOfTypeError]] if any of its basic type argument is of wrong type

example

const hfInstance = HyperFormula.buildFromArray([
 ['42'],
]);

// add a named expression
hfInstance.addNamedExpression('prettyName', '=Sheet1!$A$1+100');

// should return 'true' for this example,
// it is possible to change named expression
const isAddable = hfInstance.isItPossibleToRemoveNamedExpression('prettyName');

Parameters:

Name	Type	Description
expressionName	string	an expression name, case-insensitive.
scope?	undefined | number	scope definition, sheetId for local scope or undefined for global scope

Returns: boolean

---

listNamedExpressions

▸ listNamedExpressions(scope?: undefined | number): string[]

Defined in src/HyperFormula.ts:3997 (opens new window)

Lists named expressions.

• If scope parameter is provided, returns an array of expression names defined for this scope.
• If scope parameter is undefined, returns an array of global expression names.

throws [[ExpectedValueOfTypeError]] if any of its basic type argument is of wrong type

throws [[NoSheetWithIdError]] if no sheet with given sheetId exists

example

const hfInstance = HyperFormula.buildFromArray([
 ['42'],
 ['50'],
 ['60'],
]);

// add two named expressions and one scoped
hfInstance.addNamedExpression('prettyName', '=Sheet1!$A$1+100');
hfInstance.addNamedExpression('anotherPrettyName', '=Sheet1!$A$2+100');
hfInstance.addNamedExpression('alsoPrettyName', '=Sheet1!$A$3+100', 0);

// list the expressions, should return: ['prettyName', 'anotherPrettyName'] for this example
const listOfExpressions = hfInstance.listNamedExpressions();

 // list the expressions, should return: ['alsoPrettyName'] for this example
const listOfExpressions = hfInstance.listNamedExpressions(0);

Parameters:

Name	Type	Description
scope?	undefined | number	scope of the named expressions, sheetId for local scope or undefined for global scope

Returns: string[]

---

removeNamedExpression

▸ removeNamedExpression(expressionName: string, scope?: undefined | number): ExportedChange[]

Defined in src/HyperFormula.ts:3950 (opens new window)

Removes a named expression.

Returns an array of cells whose values changed as a result of this operation.

Note that this method may trigger dependency graph recalculation.

fires namedExpressionRemoved after the expression was removed

fires valuesUpdated if recalculation was triggered by this change

throws [[ExpectedValueOfTypeError]] if any of its basic type argument is of wrong type

throws [[NamedExpressionDoesNotExistError]] when the given expression does not exist.

throws [[NoSheetWithIdError]] if no sheet with given sheetId exists

example

const hfInstance = HyperFormula.buildFromArray([
 ['42'],
]);

// add a named expression
hfInstance.addNamedExpression('prettyName', '=Sheet1!$A$1+100', 0);

// remove the named expression
const changes = hfInstance.removeNamedExpression('prettyName', 0);

Parameters:

Name	Type	Description
expressionName	string	expression name, case-insensitive.
scope?	undefined | number	scope definition, sheetId for local scope or undefined for global scope

Returns: ExportedChange[]

---

Helpers

calculateFormula

▸ calculateFormula(formulaString: string, sheetId: number): CellValue | CellValue[][]

Defined in src/HyperFormula.ts:4098 (opens new window)

Calculates fire-and-forget formula, returns the calculated value.

throws [[ExpectedValueOfTypeError]] if any of its basic type arguments is of wrong type.

throws [[NotAFormulaError]] when the provided string is not a valid formula (i.e., doesn't start with =).

throws [[NoSheetWithIdError]] when the provided sheetID doesn't exist.

example

const hfInstance = HyperFormula.buildFromSheets({
 Sheet1: [['58']],
 Sheet2: [['1', '2', '3'], ['4', '5', '6']]
});

// returns the calculated formula's value
// for this example, returns `68`
const calculatedFormula = hfInstance.calculateFormula('=A1+10', 0);

// for this example, returns [['11', '12', '13'], ['14', '15', '16']]
const calculatedFormula = hfInstance.calculateFormula('=A1:B3+10', 1);

Parameters:

Name	Type	Description
formulaString	string	A formula in a proper format, starting with =.
sheetId	number	The ID of a sheet in context of which the formula gets evaluated.

Returns: CellValue | CellValue[][]

---

getCellDependents

▸ getCellDependents(address: SimpleCellAddress | SimpleCellRange): (SimpleCellRange | SimpleCellAddress)[]

Defined in src/HyperFormula.ts:2939 (opens new window)

Returns all the out-neighbors in the dependency graph for a given cell address or range. Including:

• All cells with formulas that contain the given cell address or range
• Some of the ranges that contain the given cell address or range

The exact result depends on the optimizations applied by the HyperFormula to the dependency graph, some of which are described in the section "Optimizations for large ranges".

The returned array includes also named expression dependents. They are represented as cell references with sheet ID -1.

throws [[ExpectedValueOfTypeError]] if address is not [[SimpleCellAddress]] or [[SimpleCellRange]]

throws [[SheetsNotEqual]] if range provided has distinct sheet numbers for start and end

throws [[NoSheetWithIdError]] when the given sheet ID does not exist

example

const hfInstance = HyperFormula.buildFromArray( [ ['1', '=A1', '=A1+B1'] ] );

hfInstance.getCellDependents({ sheet: 0, col: 0, row: 0});
// returns [{ sheet: 0, col: 1, row: 0}, { sheet: 0, col: 2, row: 0}]

Parameters:

Name	Type	Description
address	SimpleCellAddress | SimpleCellRange	object representation of an absolute address or range of addresses

Returns: (SimpleCellRange | SimpleCellAddress)[]

---

getCellPrecedents

▸ getCellPrecedents(address: SimpleCellAddress | SimpleCellRange): (SimpleCellRange | SimpleCellAddress)[]

Defined in src/HyperFormula.ts:2977 (opens new window)

Returns all the in-neighbors in the dependency graph for a given cell address or range. In particular:

• If the argument is a single cell, getCellPrecedents() returns all cells and ranges contained in that cell's formula.
• If the argument is a range of cells, getCellPrecedents() returns some of the cell addresses and smaller ranges contained in that range (but not all of them). The exact result depends on the optimizations applied by the HyperFormula to the dependency graph, some of which are described in the section "Optimizations for large ranges".

The returned array includes also named expression precedents. They are represented as cell references with sheet ID -1.

throws [[ExpectedValueOfTypeError]] if address is of wrong type

throws [[NoSheetWithIdError]] when the given sheet ID does not exist

example

const hfInstance = HyperFormula.buildFromArray( [ ['1', '=A1', '=A1+B1'] ] );

hfInstance.getCellPrecedents({ sheet: 0, col: 2, row: 0});
// returns [{ sheet: 0, col: 0, row: 0}, { sheet: 0, col: 1, row: 0}]

Parameters:

Name	Type	Description
address	SimpleCellAddress | SimpleCellRange	object representation of an absolute address or range of addresses

Returns: (SimpleCellRange | SimpleCellAddress)[]

---

getNamedExpressionsFromFormula

▸ getNamedExpressionsFromFormula(formulaString: string): string[]

Defined in src/HyperFormula.ts:4129 (opens new window)

Return a list of named expressions used by a formula.

throws [[ExpectedValueOfTypeError]] if any of its basic type arguments is of wrong type.

throws [[NotAFormulaError]] when the provided string is not a valid formula (i.e., doesn't start with =).

example

const hfInstance = HyperFormula.buildEmpty();

// returns a list of named expressions used by a formula
// for this example, returns ['foo', 'bar']
const namedExpressions = hfInstance.getNamedExpressionsFromFormula('=foo+bar*2');

Parameters:

Name	Type	Description
formulaString	string	A formula in a proper format, starting with =.

Returns: string[]

---

normalizeFormula

▸ normalizeFormula(formulaString: string): string

Defined in src/HyperFormula.ts:4062 (opens new window)

Parses and then unparses a formula. Returns a normalized formula (e.g., restores the original capitalization of sheet names, function names, cell addresses, and named expressions).

throws [[ExpectedValueOfTypeError]] if any of its basic type argument is of wrong type

throws [[NotAFormulaError]] when the provided string is not a valid formula, i.e., does not start with "="

example

const hfInstance = HyperFormula.buildFromArray([
 ['42'],
 ['50'],
]);

// returns '=Sheet1!$A$1+10'
const normalizedFormula = hfInstance.normalizeFormula('=SHEET1!$A$1+10');

// returns '=3*$A$1'
const normalizedFormula = hfInstance.normalizeFormula('=3*$a$1');

Parameters:

Name	Type	Description
formulaString	string	a formula in a proper format - it must start with "="

Returns: string

---

numberToDate

▸ numberToDate(inputNumber: number): DateTime

Defined in src/HyperFormula.ts:4278 (opens new window)

Interprets number as a date.

throws [[ExpectedValueOfTypeError]] if any of its basic type argument is of wrong type

example

const hfInstance = HyperFormula.buildEmpty();

// pass the number of days since nullDate
// the method should return formatted date, for this example:
// {year: 2020, month: 1, day: 15}
const dateFromNumber = hfInstance.numberToDate(43845);

Parameters:

Name	Type	Description
inputNumber	number	number of days since nullDate, should be non-negative, fractions are ignored.

Returns: DateTime

---

numberToDateTime

▸ numberToDateTime(inputNumber: number): DateTime

Defined in src/HyperFormula.ts:4254 (opens new window)

Interprets number as a date + time.

throws [[ExpectedValueOfTypeError]] if any of its basic type argument is of wrong type

example

const hfInstance = HyperFormula.buildEmpty();

// pass the number of days since nullDate
// the method should return formatted date and time, for this example:
// {year: 2020, month: 1, day: 15, hours: 2, minutes: 24, seconds: 0}
const dateTimeFromNumber = hfInstance.numberToDateTime(43845.1);

Parameters:

Name	Type	Description
inputNumber	number	number of days since nullDate, should be non-negative, fractions are interpreted as hours/minutes/seconds.

Returns: DateTime

---

numberToTime

▸ numberToTime(inputNumber: number): DateTime

Defined in src/HyperFormula.ts:4301 (opens new window)

Interprets number as a time (hours/minutes/seconds).

throws [[ExpectedValueOfTypeError]] if any of its basic type argument is of wrong type

example

const hfInstance = HyperFormula.buildEmpty();

// pass a number to be interpreted as a time
// should return {hours: 26, minutes: 24} for this example
const timeFromNumber = hfInstance.numberToTime(1.1);

Parameters:

Name	Type	Description
inputNumber	number	time in 24h units.

Returns: DateTime

---

simpleCellAddressFromString

▸ simpleCellAddressFromString(cellAddress: string, contextSheetId: number): SimpleCellAddress | undefined

Defined in src/HyperFormula.ts:2821 (opens new window)

Computes the simple (absolute) address of a cell address, based on its string representation.

• If a sheet name is present in the string representation but is not present in the engine, returns undefined.
• If no sheet name is present in the string representation, returns contextSheetId as sheet number.

throws [[ExpectedValueOfTypeError]] if any of its basic type argument is of wrong type

example

const hfInstance = HyperFormula.buildEmpty();
hfInstance.addSheet('Sheet0'); //sheetId = 0

// returns { sheet: 0, col: 0, row: 0 }
const simpleCellAddress = hfInstance.simpleCellAddressFromString('A1', 0);

// returns { sheet: 0, col: 0, row: 5 }
const simpleCellAddress = hfInstance.simpleCellAddressFromString('Sheet1!A6');

// returns { sheet: 0, col: 0, row: 5 }
const simpleCellAddress = hfInstance.simpleCellAddressFromString('Sheet1!$A$6');

// returns 'undefined', as there's no 'Sheet 2' in the HyperFormula instance
const simpleCellAddress = hfInstance.simpleCellAddressFromString('Sheet2!A6');

Parameters:

Name	Type	Description
cellAddress	string	string representation of cell address in A1 notation
contextSheetId	number	context used in case of missing sheet in the first argument

Returns: SimpleCellAddress | undefined

---

simpleCellAddressToString

▸ simpleCellAddressToString(cellAddress: SimpleCellAddress, sheetId: number): string | undefined

Defined in src/HyperFormula.ts:2873 (opens new window)

Returns string representation of an absolute address in A1 notation or undefined if the sheet index is not present in the engine.

throws [[ExpectedValueOfTypeError]] if its arguments are of wrong type

example

const hfInstance = HyperFormula.buildEmpty();
hfInstance.addSheet('Sheet0'); //sheetId = 0

// should return 'B2'
const A1Notation = hfInstance.simpleCellAddressToString({ sheet: 0, col: 1, row: 1 }, 0);

Parameters:

Name	Type	Description
cellAddress	SimpleCellAddress	object representation of an absolute address
sheetId	number	context used in case of missing sheet in the first argument

Returns: string | undefined

---

simpleCellRangeFromString

▸ simpleCellRangeFromString(cellRange: string, sheetId: number): SimpleCellRange | undefined

Defined in src/HyperFormula.ts:2848 (opens new window)

Computes simple (absolute) address of a cell range based on its string representation. If sheet name is present in string representation but not present in the engine, returns undefined.

throws [[NoSheetWithIdError]] when the given sheet ID does not exist

throws [[ExpectedValueOfTypeError]] if any of its basic type argument is of wrong type

example

const hfInstance = HyperFormula.buildEmpty();
hfInstance.addSheet('Sheet0'); //sheetId = 0

// should return { start: { sheet: 0, col: 0, row: 0 }, end: { sheet: 0, col: 1, row: 0 } }
const simpleCellAddress = hfInstance.simpleCellRangeFromString('A1:A2', 0);

Parameters:

Name	Type	Description
cellRange	string	string representation of cell range in A1 notation
sheetId	number	context used in case of missing sheet in the first argument

Returns: SimpleCellRange | undefined

---

simpleCellRangeToString

▸ simpleCellRangeToString(cellRange: SimpleCellRange, sheetId: number): string | undefined

Defined in src/HyperFormula.ts:2906 (opens new window)

Returns string representation of an absolute range in A1 notation or undefined if the sheet index is not present in the engine.

Note: This method is useful only for cell ranges; does not work with column ranges and row ranges.

throws [[ExpectedValueOfTypeError]] if its arguments are of wrong type

example

const hfInstance = HyperFormula.buildEmpty();
hfInstance.addSheet('Sheet0'); //sheetId = 0
hfInstance.addSheet('Sheet1'); //sheetId = 1

// should return 'B2:C2'
const A1Notation = hfInstance.simpleCellRangeToString({ start: { sheet: 0, col: 1, row: 1 }, end: { sheet: 0, col: 2, row: 1 } }, 0);

 // should return 'Sheet1!B2:C2'
const another = hfInstance.simpleCellRangeToString({ start: { sheet: 1, col: 1, row: 1 }, end: { sheet: 1, col: 2, row: 1 } }, 0);

Parameters:

Name	Type	Description
cellRange	SimpleCellRange	object representation of an absolute range
sheetId	number	context used in case of missing sheet in the first argument

Returns: string | undefined

---

validateFormula

▸ validateFormula(formulaString: string): boolean

Defined in src/HyperFormula.ts:4163 (opens new window)

Validates the formula. If the provided string starts with "=" and is a parsable formula, the method returns true. The validation is purely grammatical: the method doesn't verify if the formula can be calculated or not.

throws [[ExpectedValueOfTypeError]] if any of its basic type argument is of wrong type

example

// checks if the given string is a valid formula, should return 'true' for this example
const isFormula = hfInstance.validateFormula('=SUM(1, 2)');

Parameters:

Name	Type	Description
formulaString	string	a formula in a proper format - it must start with "="

Returns: boolean

---

Clipboard

clearClipboard

▸ clearClipboard(): void

Defined in src/HyperFormula.ts:2303 (opens new window)

Clears the clipboard content.

example

// clears the clipboard, isClipboardEmpty() should return true if called afterwards
hfInstance.clearClipboard();

The usage of the internal clipboard is described thoroughly in the Clipboard Operations guide.

Returns: void

---

copy

▸ copy(source: SimpleCellRange): CellValue[][]

Defined in src/HyperFormula.ts:2167 (opens new window)

Stores a copy of the cell block in internal clipboard for the further paste. Returns the copied values for use in external clipboard.

throws [[NoSheetWithIdError]] when the given sheet ID does not exist

throws [[ExpectedValueOfTypeError]] if source is of wrong type

throws [[SheetsNotEqual]] if range provided has distinct sheet numbers for start and end

example

const hfInstance = HyperFormula.buildFromArray([
  ['1', '2'],
]);

// it copies [ [ 2 ] ]
const clipboardContent = hfInstance.copy({
  start: { sheet: 0, col: 1, row: 0 },
  end: { sheet: 0, col: 1, row: 0 },
});

The usage of the internal clipboard is described thoroughly in the Clipboard Operations guide.

Parameters:

Name	Type	Description
source	SimpleCellRange	rectangle range to copy

Returns: CellValue[][]

---

cut

▸ cut(source: SimpleCellRange): CellValue[][]

Defined in src/HyperFormula.ts:2205 (opens new window)

Stores information of the cell block in internal clipboard for further paste. Calling paste right after this method is equivalent to call moveCells. Almost any CRUD operation called after this method will abort the cut operation. Returns the cut values for use in external clipboard.

throws [[ExpectedValueOfTypeError]] if source is of wrong type

throws [[SheetsNotEqual]] if range provided has distinct sheet numbers for start and end

throws [[NoSheetWithIdError]] when the given sheet ID does not exist

example

const hfInstance = HyperFormula.buildFromArray([
  ['1', '2'],
]);

// returns the values that were cut: [ [ 1 ] ]
const clipboardContent = hfInstance.cut({
  start: { sheet: 0, col: 0, row: 0 },
  end: { sheet: 0, col: 0, row: 0 },
});

The usage of the internal clipboard is described thoroughly in the Clipboard Operations guide.

Parameters:

Name	Type	Description
source	SimpleCellRange	rectangle range to cut

Returns: CellValue[][]

---

isClipboardEmpty

▸ isClipboardEmpty(): boolean

Defined in src/HyperFormula.ts:2286 (opens new window)

Returns information whether there is something in the clipboard.

example

const hfInstance = HyperFormula.buildFromArray([
 ['1', '2'],
]);

// copy desired content
const clipboardContent = hfInstance.copy({
  start: { sheet: 0, col: 1, row: 0 },
  end: { sheet: 0, col: 1, row: 0 },
});

// returns 'false', there is content in the clipboard
const isClipboardEmpty = hfInstance.isClipboardEmpty();

The usage of the internal clipboard is described thoroughly in the Clipboard Operations guide.

Returns: boolean

---

paste

▸ paste(targetLeftCorner: SimpleCellAddress): ExportedChange[]

Defined in src/HyperFormula.ts:2254 (opens new window)

When called after copy it pastes copied values and formulas into a cell block. When called after cut it performs moveCells operation into the cell block. Does nothing if the clipboard is empty.

Returns an array of cells whose values changed as a result of this operation.

Note that this method may trigger dependency graph recalculation.

fires valuesUpdated if recalculation was triggered by this change

throws [[NoSheetWithIdError]] when the given sheet ID does not exist

throws [[EvaluationSuspendedError]] when the evaluation is suspended

throws [[SheetSizeLimitExceededError]] when performing this operation would result in sheet size limits exceeding

throws [[NothingToPasteError]] when clipboard is empty

throws [[TargetLocationHasArrayError]] when the selected target area has array inside

throws [[ExpectedValueOfTypeError]] if targetLeftCorner is of wrong type

example

const hfInstance = HyperFormula.buildFromArray([
  ['1', '2'],
]);

// [ [ 2 ] ] was copied
const clipboardContent = hfInstance.copy({
  start: { sheet: 0, col: 1, row: 0 },
  end: { sheet: 0, col: 1, row: 0 },
});

// returns a list of modified cells: their absolute addresses and new values
const changes = hfInstance.paste({ sheet: 0, col: 1, row: 0 });

The usage of the internal clipboard is described thoroughly in the Clipboard Operations guide.

Parameters:

Name	Type	Description
targetLeftCorner	SimpleCellAddress	upper left address of the target cell block

Returns: ExportedChange[]

---

Undo and Redo

clearRedoStack

▸ clearRedoStack(): void

Defined in src/HyperFormula.ts:2331 (opens new window)

Clears the redo stack in undoRedo history.

example

const hfInstance = HyperFormula.buildFromArray([
  ['1', '2', '3'],
]);

// do an operation, for example remove columns
hfInstance.removeColumns(0, [0, 1]);

// undo the operation
hfInstance.undo();

// redo the operation
hfInstance.redo();

// clear the redo stack
hfInstance.clearRedoStack();

Returns: void

---

clearUndoStack

▸ clearUndoStack(): void

Defined in src/HyperFormula.ts:2356 (opens new window)

Clears the undo stack in undoRedo history.

example

const hfInstance = HyperFormula.buildFromArray([
  ['1', '2', '3'],
]);

// do an operation, for example remove columns
hfInstance.removeColumns(0, [0, 1]);

// undo the operation
hfInstance.undo();

// clear the undo stack
hfInstance.clearUndoStack();

Returns: void

---

isThereSomethingToRedo

▸ isThereSomethingToRedo(): boolean

Defined in src/HyperFormula.ts:1155 (opens new window)

Checks if there is at least one operation that can be re-done.

example

hfInstance.undo();

// when there is an action to redo, this returns 'true'
const isSomethingToRedo = hfInstance.isThereSomethingToRedo();

Returns: boolean

---

isThereSomethingToUndo

▸ isThereSomethingToUndo(): boolean

Defined in src/HyperFormula.ts:1138 (opens new window)

Checks if there is at least one operation that can be undone.

example

const hfInstance = HyperFormula.buildFromArray([
 ['1'],
 ['2'],
 ['3'],
]);

// perform CRUD operation, for example remove the second row
hfInstance.removeRows(0, [1, 1]);

// should return 'true', it is possible to undo last operation
// which is removing rows in this example
const isSomethingToUndo = hfInstance.isThereSomethingToUndo();

Returns: boolean

---

redo

▸ redo(): ExportedChange[]

Defined in src/HyperFormula.ts:1112 (opens new window)

Re-do recently undone operation.

Returns an array of cells whose values changed as a result of this operation.

Note that this method may trigger dependency graph recalculation.

fires valuesUpdated if recalculation was triggered by this change

throws [[NoOperationToRedoError]] when there is no operation running that can be re-done

example

const hfInstance = HyperFormula.buildFromArray([
 ['1'],
 ['2'],
 ['3'],
]);

// perform CRUD operation, for example remove the second row
hfInstance.removeRows(0, [1, 1]);

// undo the operation, it should return previous values: [['1'], ['2'], ['3']]
hfInstance.undo();

// do a redo, it should return the values after removing the second row: [['1'], ['3']]
const changes = hfInstance.redo();

Returns: ExportedChange[]

---

undo

▸ undo(): ExportedChange[]

Defined in src/HyperFormula.ts:1076 (opens new window)

Undo the previous operation.

Returns an array of cells whose values changed as a result of this operation.

Note that this method may trigger dependency graph recalculation.

fires valuesUpdated if recalculation was triggered by this change

throws [[NoOperationToUndoError]] when there is no operation running that can be undone

example

const hfInstance = HyperFormula.buildFromArray([
 ['1', '2'],
 ['3', ''],
]);

// perform CRUD operation, for example remove the second row
hfInstance.removeRows(0, [1, 1]);

// undo the operation, it should return the changes
const changes = hfInstance.undo();

Returns: ExportedChange[]

---

Batch

batch

▸ batch(batchOperations: function): ExportedChange[]

Defined in src/HyperFormula.ts:3463 (opens new window)

Runs the provided callback as a single batch operation and returns the changed cells.

Returns an array of cells whose values changed as a result of all batched operations.

Note that this method may trigger dependency graph recalculation.

fires valuesUpdated if recalculation was triggered by this change

fires evaluationSuspended always

fires evaluationResumed after the recomputation of necessary values

example

const hfInstance = HyperFormula.buildFromSheets({
 MySheet1: [ ['1'] ],
 MySheet2: [ ['10'] ],
});

// multiple operations in a single callback will trigger evaluation only once
// and only one set of changes is returned as a combined result of all
// the operations that were triggered within the callback
const changes = hfInstance.batch(() => {
 hfInstance.setCellContents({ col: 3, row: 0, sheet: 0 }, [['=B1']]);
 hfInstance.setCellContents({ col: 4, row: 0, sheet: 0 }, [['=A1']]);
});

Parameters:

▪ batchOperations: function

a function with operations to be performed

▸ (): void

Returns: ExportedChange[]

---

isEvaluationSuspended

▸ isEvaluationSuspended(): boolean

Defined in src/HyperFormula.ts:3572 (opens new window)

Checks if the dependency graph recalculation process is suspended or not.

example

const hfInstance = HyperFormula.buildEmpty();

// suspend the evaluation
hfInstance.suspendEvaluation();

// between suspendEvaluation() and resumeEvaluation()
// or inside batch() callback it will return 'true', otherwise 'false'
const isEvaluationSuspended = hfInstance.isEvaluationSuspended();

const changes = hfInstance.resumeEvaluation();

Returns: boolean

---

resumeEvaluation

▸ resumeEvaluation(): ExportedChange[]

Defined in src/HyperFormula.ts:3546 (opens new window)

Resumes the dependency graph recalculation that was suspended with suspendEvaluation. It also triggers the recalculation and returns an array of cells whose values changed as a result of all batched operations.

fires valuesUpdated if recalculation was triggered by this change

fires evaluationResumed after the recomputation of necessary values

example

const hfInstance = HyperFormula.buildFromSheets({
 MySheet1: [ ['1'] ],
 MySheet2: [ ['10'] ],
});

// similar to batch() but operations are not within a callback,
// one method suspends the recalculation
// the second will resume calculations and return the changes

// first, suspend the evaluation
hfInstance.suspendEvaluation();

// perform operations
hfInstance.setCellContents({ col: 3, row: 0, sheet: 0 }, [['=B1']]);
hfInstance.setSheetContent(1, [['50'], ['60']]);

// resume the evaluation
const changes = hfInstance.resumeEvaluation();

Returns: ExportedChange[]

---

suspendEvaluation

▸ suspendEvaluation(): void

Defined in src/HyperFormula.ts:3510 (opens new window)

Suspends the dependency graph recalculation to start a batch operation. It allows optimizing the performance. With this method, multiple CRUD operations can be done without triggering recalculation after every operation. Suspending evaluation should result in an overall faster calculation compared to recalculating after each operation separately. To resume the evaluation use resumeEvaluation.

fires evaluationSuspended always

example

const hfInstance = HyperFormula.buildFromSheets({
 MySheet1: [ ['1'] ],
 MySheet2: [ ['10'] ],
});

// similar to batch() but operations are not within a callback,
// one method suspends the recalculation
// the second will resume calculations and return the changes

// suspend the evaluation with this method
hfInstance.suspendEvaluation();

// perform operations
hfInstance.setCellContents({ col: 3, row: 0, sheet: 0 }, [['=B1']]);
hfInstance.setSheetContent(1, [['50'], ['60']]);

// use resumeEvaluation to resume
const changes = hfInstance.resumeEvaluation();

Returns: void

---

Events

off

▸ off‹Event›(event: Event, listener: Listeners[Event]): void

Defined in src/HyperFormula.ts:4387 (opens new window)

Unsubscribes from an event or from all events. For the list of all available events, see Listeners.

example

const hfInstance = HyperFormula.buildEmpty();

// define a simple function to be called upon emitting an event
const handler = ( ) => { console.log('baz') }

// subscribe to a 'sheetAdded', pass the handler
hfInstance.on('sheetAdded', handler);

// add a sheet to trigger an event,
// console should print 'baz' each time a sheet is added
hfInstance.addSheet('FooBar');

// unsubscribe from a 'sheetAdded'
hfInstance.off('sheetAdded', handler);

// add a sheet, the console should not print anything
hfInstance.addSheet('FooBaz');

Type parameters:

▪ Event: keyof Listeners

Parameters:

Name	Type	Description
event	Event	the name of the event to subscribe to
listener	Listeners[Event]	to be called when event is emitted

Returns: void

---

on

▸ on‹Event›(event: Event, listener: Listeners[Event]): void

Defined in src/HyperFormula.ts:4327 (opens new window)

Subscribes to an event. For the list of all available events, see Listeners.

example

const hfInstance = HyperFormula.buildEmpty();

// subscribe to a 'sheetAdded', pass a simple handler
hfInstance.on('sheetAdded', ( ) => { console.log('foo') });

// add a sheet to trigger an event,
// console should print 'foo' after each time sheet is added in this example
hfInstance.addSheet('FooBar');

Type parameters:

▪ Event: keyof Listeners

Parameters:

Name	Type	Description
event	Event	the name of the event to subscribe to
listener	Listeners[Event]	to be called when event is emitted

Returns: void

---

once

▸ once‹Event›(event: Event, listener: Listeners[Event]): void

Defined in src/HyperFormula.ts:4353 (opens new window)

Subscribes to an event once. For the list of all available events, see Listeners.

example

const hfInstance = HyperFormula.buildEmpty();

// subscribe to a 'sheetAdded', pass a simple handler
hfInstance.once('sheetAdded', ( ) => { console.log('foo') });

// call addSheet twice,
// console should print 'foo' only once when the sheet is added in this example
hfInstance.addSheet('FooBar');
hfInstance.addSheet('FooBaz');

Type parameters:

▪ Event: keyof Listeners

Parameters:

Name	Type	Description
event	Event	the name of the event to subscribe to
listener	Listeners[Event]	to be called when event is emitted

Returns: void

---

Custom Functions

getAllFunctionPlugins

▸ getAllFunctionPlugins(): FunctionPluginDefinition[]

Defined in src/HyperFormula.ts:4230 (opens new window)

Returns classes of all plugins registered in this instance of HyperFormula

example

const hfInstance = HyperFormula.buildEmpty();

// return classes of all plugins registered, assign to a variable
const allNames = hfInstance.getAllFunctionPlugins();

Returns: FunctionPluginDefinition[]

---

getFunctionPlugin

▸ getFunctionPlugin(functionId: string): FunctionPluginDefinition | undefined

Defined in src/HyperFormula.ts:4212 (opens new window)

Returns class of a plugin used by function with given id

throws [[ExpectedValueOfTypeError]] if any of its basic type argument is of wrong type

example

// import your own plugin
import { MyExamplePlugin } from './file_with_your_plugin';

const hfInstance = HyperFormula.buildEmpty();

// register a plugin
HyperFormula.registerFunctionPlugin(MyExamplePlugin);

// get the plugin
const myPlugin = hfInstance.getFunctionPlugin('EXAMPLE');

Parameters:

Name	Type	Description
functionId	string	id of a function, e.g., 'SUMIF'

Returns: FunctionPluginDefinition | undefined

---

getRegisteredFunctionNames

▸ getRegisteredFunctionNames(): string[]

Defined in src/HyperFormula.ts:4184 (opens new window)

Returns translated names of all functions registered in this instance of HyperFormula according to the language set in the configuration

example

const hfInstance = HyperFormula.buildEmpty();

// return translated names of all functions, assign to a variable
const allNames = hfInstance.getRegisteredFunctionNames();

Returns: string[]

---

Static Methods

getAllFunctionPlugins Static

▸ getAllFunctionPlugins(): FunctionPluginDefinition[]

Defined in src/HyperFormula.ts:613 (opens new window)

Returns classes of all plugins registered in HyperFormula.

example

// return classes of all plugins
const allClasses = HyperFormula.getAllFunctionPlugins();

Returns: FunctionPluginDefinition[]

---

getFunctionPlugin Static

▸ getFunctionPlugin(functionId: string): FunctionPluginDefinition | undefined

Defined in src/HyperFormula.ts:597 (opens new window)

Returns class of a plugin used by function with given id

throws [[ExpectedValueOfTypeError]] if any of its basic type argument is of wrong type

example

// import your own plugin
import { MyExamplePlugin } from './file_with_your_plugin';

// register a plugin
HyperFormula.registerFunctionPlugin(MyExamplePlugin);

// return the class of a given plugin
const myFunctionClass = HyperFormula.getFunctionPlugin('EXAMPLE');

Parameters:

Name	Type	Description
functionId	string	id of a function, e.g., 'SUMIF'

Returns: FunctionPluginDefinition | undefined

---

getLanguage Static

▸ getLanguage(languageCode: string): TranslationPackage

Defined in src/HyperFormula.ts:348 (opens new window)

Returns registered language from its code string.

throws [[ExpectedValueOfTypeError]] if any of its basic type argument is of wrong type

throws [[LanguageNotRegisteredError]] when trying to retrieve not registered language

example

// return registered language
const language = HyperFormula.getLanguage('enGB');

Parameters:

Name	Type	Description
languageCode	string	code string of the translation package

Returns: TranslationPackage

---

getRegisteredFunctionNames Static

▸ getRegisteredFunctionNames(code: string): string[]

Defined in src/HyperFormula.ts:569 (opens new window)

Returns translated names of all registered functions for a given language

throws [[ExpectedValueOfTypeError]] if any of its basic type argument is of wrong type

example

// return a list of function names registered for enGB
const allNames = HyperFormula.getRegisteredFunctionNames('enGB');

Parameters:

Name	Type	Description
code	string	language code

Returns: string[]

---

getRegisteredLanguagesCodes Static

▸ getRegisteredLanguagesCodes(): string[]

Defined in src/HyperFormula.ts:427 (opens new window)

Returns all registered languages codes.

example

// should return all registered language codes: ['enGB', 'plPL']
const registeredLanguages = HyperFormula.getRegisteredLanguagesCodes();

Returns: string[]

---

registerFunction Static

▸ registerFunction(functionId: string, plugin: FunctionPluginDefinition, translations?: FunctionTranslationsPackage): void

Defined in src/HyperFormula.ts:505 (opens new window)

Registers a function with a given id if such exists in a plugin.

Note: This method does not affect the existing HyperFormula instances.

throws [[ExpectedValueOfTypeError]] if any of its basic type argument is of wrong type

throws [[FunctionPluginValidationError]] when function with a given id does not exist in plugin or plugin class definition is not consistent with metadata

throws [[ProtectedFunctionTranslationError]] when trying to register translation for protected function

example

// import your own plugin
import { MyExamplePlugin } from './file_with_your_plugin';

// register a function
HyperFormula.registerFunction('EXAMPLE', MyExamplePlugin);

Parameters:

Name	Type	Description
functionId	string	function id, e.g., 'SUMIF'
plugin	FunctionPluginDefinition	plugin class
translations?	FunctionTranslationsPackage	translations for the function name

Returns: void

---

registerFunctionPlugin Static

▸ registerFunctionPlugin(plugin: FunctionPluginDefinition, translations?: FunctionTranslationsPackage): void

Defined in src/HyperFormula.ts:455 (opens new window)

Registers all functions in a given plugin with optional translations.

Note: FunctionPlugins must be registered prior to the creation of HyperFormula instances in which they are used. HyperFormula instances created prior to the registration of a FunctionPlugin are unable to access the FunctionPlugin. Registering a FunctionPlugin with [[custom-functions]] requires the translations parameter.

throws [[FunctionPluginValidationError]] when plugin class definition is not consistent with metadata

throws [[ProtectedFunctionTranslationError]] when trying to register translation for protected function

example

// import your own plugin
import { MyExamplePlugin } from './file_with_your_plugin';

// register the plugin
HyperFormula.registerFunctionPlugin(MyExamplePlugin);

Parameters:

Name	Type	Description
plugin	FunctionPluginDefinition	plugin class
translations?	FunctionTranslationsPackage	optional package of function names translations

Returns: void

---

registerLanguage Static

▸ registerLanguage(languageCode: string, languagePackage: RawTranslationPackage): void

Defined in src/HyperFormula.ts:379 (opens new window)

Registers language under given code string.

For more information, see the Localizing functions guide.

throws [[ExpectedValueOfTypeError]] if any of its basic type argument is of wrong type

throws [[ProtectedFunctionTranslationError]] when trying to register translation for protected function

throws [[LanguageAlreadyRegisteredError]] when given language is already registered

example

// return registered language
HyperFormula.registerLanguage('enUS', enUS);
const engine = HyperFormula.buildEmpty({language: 'enUS'});

Parameters:

Name	Type	Description
languageCode	string	code string of the translation package
languagePackage	RawTranslationPackage	translation package to be registered

Returns: void

---

unregisterAllFunctions Static

▸ unregisterAllFunctions(): void

Defined in src/HyperFormula.ts:550 (opens new window)

Clears function registry.

Note: This method does not affect the existing HyperFormula instances.

example

HyperFormula.unregisterAllFunctions();

Returns: void

---

unregisterFunction Static

▸ unregisterFunction(functionId: string): void

Defined in src/HyperFormula.ts:533 (opens new window)

Unregisters a function with a given id.

Note: This method does not affect the existing HyperFormula instances.

throws [[ExpectedValueOfTypeError]] if any of its basic type argument is of wrong type

example

// import your own plugin
import { MyExamplePlugin } from './file_with_your_plugin';

// register a function
HyperFormula.registerFunction('EXAMPLE', MyExamplePlugin);

// unregister a function
HyperFormula.unregisterFunction('EXAMPLE');

Parameters:

Name	Type	Description
functionId	string	function id, e.g., 'SUMIF'

Returns: void

---

unregisterFunctionPlugin Static

▸ unregisterFunctionPlugin(plugin: FunctionPluginDefinition): void

Defined in src/HyperFormula.ts:477 (opens new window)

Unregisters all functions defined in given plugin.

Note: This method does not affect the existing HyperFormula instances.

example

// get the class of a plugin
const registeredPluginClass = HyperFormula.getFunctionPlugin('EXAMPLE');

// unregister all functions defined in a plugin of ID 'EXAMPLE'
HyperFormula.unregisterFunctionPlugin(registeredPluginClass);

Parameters:

Name	Type	Description
plugin	FunctionPluginDefinition	plugin class

Returns: void

---

unregisterLanguage Static

▸ unregisterLanguage(languageCode: string): void

Defined in src/HyperFormula.ts:407 (opens new window)

Unregisters language that is registered under given code string.

throws [[ExpectedValueOfTypeError]] if any of its basic type argument is of wrong type

throws [[LanguageNotRegisteredError]] when given language is not registered

example

// register the language for the instance
HyperFormula.registerLanguage('plPL', plPL);

// unregister plPL
HyperFormula.unregisterLanguage('plPL');

Parameters:

Name	Type	Description
languageCode	string	code string of the translation package

Returns: void