HyperFormula

https://hyperformula.handsontable.com
Admin
HyperFormula is an open-source headless spreadsheet library built in TypeScript. It serves as a...
Tokens:129,307
Snippets:902
Trust Score:10
Update:8 months ago
Show doc for...
Tokens:
Raw
### Initialize HyperFormula Instance

Source: https://hyperformula.handsontable.com/guide/advanced-usage

This JavaScript snippet initializes a HyperFormula instance with a GPL-v3 license key. It demonstrates the basic setup required to start using HyperFormula's calculation engine.

```javascript
import './styles.css'
import HyperFormula from 'hyperformula';

console.log(
`%c Using HyperFormula ${HyperFormula.version}`,
'color: blue; font-weight: bold',
);

// first column represents players' IDs
// second column represents players' scores
const playersAData = [
['1', '2'],
['2', '3'],
['3', '5'],
['4', '7'],
['5', '13'],
['6', '17'],
];

const playersBData = [
['7', '19'],
['8', '31'],
['9', '61'],
['10', '89'],
['11', '107'],
['12', '127'],
];

// in a cell A1 a formula checks which team is a winning one
// in cells A2 and A3 formulas calculate the average score of players
const formulasData = [
['=IF(Formulas!A2>Formulas!A3,"TeamA","TeamB")'],
['=AVERAGE(TeamA!B1:B6)'],
['=AVERAGE(TeamB!B1:B6)'],
];

// Create an empty HyperFormula instance.
const hf = HyperFormula.buildEmpty({
licenseKey: 'gpl-v3',
});
```

--------------------------------

### Initialize HyperFormula Instance for Column Sorting

Source: https://hyperformula.handsontable.com/guide/sorting-data

Initializes a HyperFormula instance with example data for demonstrating column sorting. This setup is a prerequisite for using the column sorting methods like `isItPossibleToSetColumnOrder` and `setColumnOrder`.

```javascript
// a HyperFormula instance with example data
const hfInstance = HyperFormula.buildFromArray([
 [1, 2, 4],
 [5]
]);

// we'll set the column order to [2, 1, 0] in the next steps
```

--------------------------------

### Install HyperFormula using npm

Source: https://hyperformula.handsontable.com/index

This command installs the HyperFormula library from npm. It's a prerequisite for using HyperFormula in your project.

```bash
npm install hyperformula

```

--------------------------------

### Install HyperFormula with Yarn

Source: https://hyperformula.handsontable.com/guide/server-side-installation

This command installs the latest version of the HyperFormula package using Yarn. It adds the package to your project's dependencies in `package.json` and installs it in the `./node_modules` directory.

```bash
$ yarn add hyperformula

```

--------------------------------

### Algorithm to Get All Precedents of a Cell or Range using BFS

Source: https://hyperformula.handsontable.com/guide/dependency-graph

This pseudocode outlines a Breadth-First Search (BFS) algorithm to find all reachable precedent nodes from a starting cell or range. It utilizes the `getCellPrecedents` method to explore the dependency graph.

```pseudocode
AllCellPrecedents={start}
let Q be an empty queue
Q.enqueue(start)
while Q is not empty do
  cell := Q.dequeue()
  S := getCellPrecedents(cell)
  for all cells c in S do:
    if c is not in AllCellPrecedents then:
      insert w to AllCellPrecedents
      Q.enqueue(c)
```

--------------------------------

### Get All Precedents (BFS Algorithm)

Source: https://hyperformula.handsontable.com/guide/dependency-graph

Illustrates how to implement a Breadth-First Search (BFS) algorithm using `getCellPrecedents` to find all reachable precedents of a cell or range.

```APIDOC
## GET /api/getAllPrecedents (via BFS)

### Description
This section describes how to find all precedents of a cell or range by implementing a Breadth-First Search (BFS) algorithm. It leverages the `getCellPrecedents()` method to traverse the dependency graph.

### Method
GET (Conceptual - implementation required)

### Endpoint
`/api/getAllPrecedents` (Conceptual)

### Parameters
* This is an algorithmic description, not a direct API endpoint with parameters.

### Request Example
```javascript
// Pseudocode for BFS to find all precedents
function getAllPrecedents(startCell, hfInstance) {
  const AllCellPrecedents = new Set();
  const Q = []; // Queue

  AllCellPrecedents.add(JSON.stringify(startCell)); // Use stringified object for Set comparison
  Q.push(startCell);

  while (Q.length > 0) {
    const cell = Q.shift(); // Dequeue
    const S = hfInstance.getCellPrecedents(cell);

    for (const c of S) {
      const cString = JSON.stringify(c);
      if (!AllCellPrecedents.has(cString)) {
        AllCellPrecedents.add(cString);
        Q.push(c);
      }
    }
  }

  // Convert back from stringified objects to actual cell objects
  return Array.from(AllCellPrecedents).map(cellStr => JSON.parse(cellStr));
}

// Example Usage:
// const hfInstance = ...;
// const startCell = { sheet: 0, col: 3, row: 0 };
// const allPrecedents = getAllPrecedents(startCell, hfInstance);
// console.log(allPrecedents);
```

### Response
#### Success Response (200)
- **allPrecedents** (array) - An array of objects, where each object represents a precedent cell reachable from the start cell, including `sheet`, `col`, and `row` properties.

#### Response Example
```json
{
  "allPrecedents": [
    { "sheet": 0, "col": 1, "row": 0 },
    { "sheet": 0, "col": 2, "row": 0 },
    { "sheet": 0, "col": 0, "row": 0 } // Example if A1 is a precedent of B1 or C1
  ]
}
```
```

--------------------------------

### Install HyperFormula with npm

Source: https://hyperformula.handsontable.com/guide/server-side-installation

This command installs the latest version of the HyperFormula package using npm. It adds the package to your project's dependencies in `package.json` and installs it in the `./node_modules` directory.

```bash
$ npm install hyperformula

```

--------------------------------

### Install HyperFormula with npm or Yarn (JavaScript)

Source: https://hyperformula.handsontable.com/guide/client-side-installation

Installs the HyperFormula library using npm or Yarn package managers. After installation, it shows how to import the HyperFormula class into your JavaScript project.

```bash
$ npm install hyperformula

```

```bash
$ yarn add hyperformula

```

```javascript
import { HyperFormula } from 'hyperformula';

// your code

```

--------------------------------

### Full Custom Function Implementation Example

Source: https://hyperformula.handsontable.com/guide/custom-functions

This comprehensive example includes the complete implementation of a custom function plugin, including its methods, implemented functions definition, translations, and registration. It demonstrates how to define function parameters and use the `runFunction` method.

```typescript
import { FunctionPlugin, FunctionArgumentType } from 'hyperformula';

export class MyCustomPlugin extends FunctionPlugin {
  greet(ast, state) {
    return this.runFunction(
      ast.args,
      state,
      this.metadata('GREET'),
      (firstName) => {
        return `👋 Hello, ${firstName}!`;
      }
    );
  }
}

MyCustomPlugin.implementedFunctions = {
  GREET: {
    method: 'greet',
    parameters: [{ argumentType: FunctionArgumentType.STRING }],
  },
};

export const MyCustomPluginTranslations = {
  enGB: {
    GREET: 'GREET',
  },
  enUS: {
    GREET: 'GREET',
  },
};

HyperFormula.registerFunctionPlugin(MyCustomPlugin, MyCustomPluginTranslations);
```

--------------------------------

### Import HyperFormula and Log Version

Source: https://hyperformula.handsontable.com/guide/basic-operations

Imports the HyperFormula library and logs its version to the console with specific styling. This is typically part of the initial setup for using the library.

```javascript
import HyperFormula from 'hyperformula';

console.log(
  `%c Using HyperFormula ${HyperFormula.version}`,
  'color: blue; font-weight: bold',
);
```

--------------------------------

### Adapt to matrix to array Naming Changes (JavaScript)

Source: https://hyperformula.handsontable.com/guide/migration-from-0.6-to-1.0

Shows examples of how to adapt to the renaming of 'matrix' to 'array' in configuration options, API method names, and exception names. This involves updating string literals in code.

```javascript
// Before:
const options = {
    licenseKey: 'gpl-v3',
    matrixDetection: true,
    matrixDetectionThreshold: 150
};

// After:
const options = {
    licenseKey: 'gpl-v3'
    // remove `matrixDetection` and `matrixDetectionThreshold`
};
```

--------------------------------

### HyperFormula Configuration Options

Source: https://hyperformula.handsontable.com/api

Provides an example of common configuration options for a HyperFormula instance. These options control aspects like license key, null date representation, and function argument separators.

```javascript
const options = {
  licenseKey: 'gpl-v3',
  nullDate: { year: 1900, month: 1, day: 1 },
  functionArgSeparator: '.'
};
```

--------------------------------

### Handle Cell Movement with HyperFormula

Source: https://hyperformula.handsontable.com/guide/cell-references

Illustrates how to use HyperFormula's `moveCells` method to handle cell movements, demonstrating how relative references are updated. The example builds a simple dataset and logs the changes after moving a cell.

```javascript
// build with a simple dataset
const hfInstance = HyperFormula.buildFromArray([
 ['=B2', '=A1', ''],
]);

// these are the coordinates for a move operation
const source = { sheet: 0, col: 1, row: 0 };
const destination = { sheet: 0, col: 2, row: 0 };

// move B1
const changes = hfInstance.moveCells({ start: source, end: source }, destination);

// you can see the changes inside the console
console.log(changes);

```

--------------------------------

### Changes Array Structure and Example (JavaScript)

Source: https://hyperformula.handsontable.com/guide/basic-operations

Illustrates the structure of the Changes array returned by data modification methods. Each element contains the address and new value of affected cells, useful for tracking modifications.

```javascript
[{
  address: { sheet: 0, col: 0, row: 0 },
  newValue: { error: [CellError], value: '#REF!' },
}]
```

```javascript
const hf = HyperFormula.buildFromArray([
  [0],
  [1],
  ['=SUM(A1:A2)'],
  ['=COUNTBLANK(A1:A3)'],
]);

const changes = hf.addRows(0, [1, 1]);

console.log(hf.getSheetSerialized(0));
// sheet after adding the row:
// [
//   [0],
//   [],
//   [1],
//   ['=SUM(A1:A3)'],
//   ['=COUNTBLANK(A1:A4)'],
// ]

console.log(changes);
// changes include only the COUNTBLANK cell:
// [{
//   address: { sheet: 0, row: 4, col: 0 },
//   newValue: 1,
// }]
```

--------------------------------

### Initialize and Configure HyperFormula Instance

Source: https://hyperformula.handsontable.com/guide/i18n-features

This snippet shows how to set up a HyperFormula instance with a license key and register a language. It then creates an empty instance and adds a sheet named 'main'.

```javascript
const config = {
  licenseKey: 'gpl-v3',
};
if (!HyperFormula.getRegisteredLanguagesCodes().includes('enUS')) {
  HyperFormula.registerLanguage('enUS', enUS);
}
// Create an empty HyperFormula instance.
const hf = HyperFormula.buildEmpty(config);
// Add a new sheet and get its id.
const sheetName = hf.addSheet('main');
const sheetId = hf.getSheetId(sheetName);
```

--------------------------------

### Get Named Expressions from Formula in HyperFormula

Source: https://hyperformula.handsontable.com/api/classes/hyperformula

Returns a list of named expressions used within a given formula string. The input must be a valid formula starting with '='. Throws ExpectedValueOfTypeError or NotAFormulaError if the input is invalid. This function is useful for analyzing formula dependencies.

```typescript
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');
```

--------------------------------

### Configure HyperFormula Instance

Source: https://hyperformula.handsontable.com/guide/configuration-options

Demonstrates how to define configuration options and initialize a new HyperFormula instance with these settings. Requires the HyperFormula library to be loaded.

```javascript
// define options
const options = {
    licenseKey: 'gpl-v3',
    precisionRounding: 9,
    nullDate: { year: 1900, month: 1, day: 1 },
    functionArgSeparator: '.'
};

// call the static method to build a new instance
const hfInstance = HyperFormula.buildEmpty(options);

```

--------------------------------

### Get Cell Dependents using BFS Algorithm

Source: https://hyperformula.handsontable.com/guide/dependency-graph

This pseudocode demonstrates how to find all dependent cells or ranges from a starting cell or range using a Breadth-First Search (BFS) algorithm. It utilizes a queue to manage cells to visit and a set to keep track of already found dependents. The `getCellDependents(cell)` function is assumed to be available.

```pseudocode
AllCellDependents={start}
let Q be an empty queue
Q.enqueue(start)
while Q is not empty do
  cell := Q.dequeue()
  S := getCellDependents(cell)
  for all cells c in S do:
    if c is not in AllCellDependents then:
      insert w to AllCellDependents
      Q.enqueue(c)
```

--------------------------------

### Package JSON for Hyperformula Demo

Source: https://hyperformula.handsontable.com/guide/basic-operations

This package.json file defines the metadata and dependencies for the HyperFormula demo project. It specifies the project name, version, main entry point, and lists 'hyperformula' and 'moment' as essential dependencies, indicating they are required for the demo to function.

```json
{
"name": "hyperformula-demo",
"version": "1.0.0",
"main": "index.html",
"dependencies": {
"hyperformula": "latest",
"moment": "latest"
}
}
```

--------------------------------

### Initialize HyperFormula and Data

Source: https://hyperformula.handsontable.com/guide/sorting-data

Initializes a HyperFormula instance with a GPL-v3 license key and populates a sheet named 'main' with initial table data. This setup is crucial for enabling calculations and subsequent data manipulation like sorting.

```javascript
import './styles.css'
import HyperFormula from 'hyperformula';

console.log(
  `%c Using HyperFormula ${HyperFormula.version}`,
  'color: blue; font-weight: bold',
);

/**
 * Initial table data.
 */
const tableData = [
  ['Greg Black', '100'],
  ['Anne Carpenter', '=SUM(100,100)'],
  ['Natalie Dem', '500'],
  ['John Sieg', '50'],
  ['Chris Aklips', '20'],
  ['Bart Hoopoe', '700'],
  ['Chris Site', '80'],
  ['Agnes Whitey', '90'],
];

// Create an empty HyperFormula instance.
const hf = HyperFormula.buildEmpty({
  licenseKey: 'gpl-v3',
});

// Add a new sheet and get its id.
const sheetName = hf.addSheet('main');
const sheetId = hf.getSheetId(sheetName);

// Fill the HyperFormula sheet with data.
hf.setCellContents(
  {
    row: 0,
    col: 0,
    sheet: sheetId,
  },
  tableData,
);
```

--------------------------------

### Initialize and Render Spreadsheet

Source: https://hyperformula.handsontable.com/guide/basic-operations

This section of code initializes the spreadsheet by binding UI events, rendering the table, and updating the sheet dropdown. It also ensures the message box is displayed.

```javascript
// // Bind the UI events.
bindEvents();
// Render the table.
renderTable();
// Refresh the sheet dropdown list
updateSheetDropdown();
document.querySelector('.example .message-box').style.display = 'block';
```

--------------------------------

### Extract Substring (MID)

Source: https://hyperformula.handsontable.com/guide/built-in-functions

Extracts a portion of a text string, starting at a specified position and continuing for a given length. Requires the text, starting position, and length.

```javascript
hyperformula.MID("Hello World", 7, 5) // Returns 'World'
```

--------------------------------

### Initial Table Rendering and Event Binding

Source: https://hyperformula.handsontable.com/guide/date-and-time-handling

Sets up the initial state of the application by defining whether animations are enabled, binding event listeners to UI elements, and rendering the table for the first time.

```javascript
const ANIMATION_ENABLED = true;
// Bind the button events.
bindEvents();
// Render the table.
renderTable();
```

--------------------------------

### Initialize HyperFormula Instance and Sheet

Source: https://hyperformula.handsontable.com/guide/basic-operations

Initializes a HyperFormula instance with a GPL v3 license and adds an initial sheet named 'InitialSheet'. It then fills this sheet with sample data generated by the `getSampleData` function.

```javascript
import HyperFormula from 'hyperformula';

// Create an empty HyperFormula instance.
const hf = HyperFormula.buildEmpty({
  licenseKey: 'gpl-v3',
});

// Add a new sheet and get its id.
state.currentSheet = 'InitialSheet';
const sheetName = hf.addSheet(state.currentSheet);
const sheetId = hf.getSheetId(sheetName);

// Fill the HyperFormula sheet with data.
hf.setSheetContent(sheetId, getSampleData(5, 5));
```

--------------------------------

### Require HyperFormula in Node.js

Source: https://hyperformula.handsontable.com/guide/server-side-installation

After installing HyperFormula, you can import it into your Node.js project using the `require` function. This makes the HyperFormula class available for use in your application.

```javascript
const { HyperFormula } = require('hyperformula');

// your code

```

--------------------------------

### Replace Substring (REPLACE)

Source: https://hyperformula.handsontable.com/guide/built-in-functions

Replaces a part of a text string with another string, based on a starting position and length. Requires the original text, start position, length to replace, and the new text.

```javascript
hyperformula.REPLACE("Hello World", 7, 5, "There") // Returns 'Hello There'
```

--------------------------------

### Date and Time Settings

Source: https://hyperformula.handsontable.com/api/interfaces/configparams

Configure date formats, leap year behavior, and the null date reference.

```APIDOC
## Date and Time Settings

### dateFormats

#### Description
Sets the date formats accepted by the date-parsing function. A format must be specified as a string consisting of tokens and separators.

### Method
(Not applicable - configuration parameter)

### Endpoint
(Not applicable - configuration parameter)

### Parameters
#### Query Parameters
- **dateFormats** (array of strings) - Optional - An array of date format strings. Defaults to ['DD/MM/YYYY', 'DD/MM/YY'].
  Supported tokens: DD (day), MM (month), YYYY (4-digit year), YY (2-digit year).
  Supported separators: / (slash), - (dash), . (dot), (empty space).

### Request Example
```json
{
  "dateFormats": ["DD/MM/YYYY", "DD-MM-YY"]
}
```

### Response
#### Success Response (200)
- **dateFormats** (array of strings) - The configured date formats.

#### Response Example
```json
{
  "dateFormats": ["DD/MM/YYYY", "DD-MM-YY"]
}
```

### leapYear1900

#### Description
Sets year 1900 as a leap year. For compatibility with Lotus 1-2-3 and Microsoft Excel, set this option to `true`.

### Method
(Not applicable - configuration parameter)

### Endpoint
(Not applicable - configuration parameter)

### Parameters
#### Query Parameters
- **leapYear1900** (boolean) - Optional - Whether to consider 1900 a leap year. Defaults to `false`.

### Request Example
```json
{
  "leapYear1900": true
}
```

### Response
#### Success Response (200)
- **leapYear1900** (boolean) - The configured leap year setting for 1900.

#### Response Example
```json
{
  "leapYear1900": true
}
```

### nullDate

#### Description
Internally, each date is represented as a number of days that passed since `nullDate`. This option sets a specific date from which that number of days is counted.

### Method
(Not applicable - configuration parameter)

### Endpoint
(Not applicable - configuration parameter)

### Parameters
#### Query Parameters
- **nullDate** (object) - Optional - The reference date for internal date calculations. Defaults to `{year: 1899, month: 12, day: 30}`.
  - **year** (number) - The year of the null date.
  - **month** (number) - The month of the null date (1-12).
  - **day** (number) - The day of the null date.

### Request Example
```json
{
  "nullDate": {"year": 1899, "month": 12, "day": 30}
}
```

### Response
#### Success Response (200)
- **nullDate** (object) - The configured null date.

#### Response Example
```json
{
  "nullDate": {"year": 1899, "month": 12, "day": 30}
}
```
```

--------------------------------

### Find Text Position (FIND)

Source: https://hyperformula.handsontable.com/guide/built-in-functions

Locates the starting position of one text string within another, performing a case-sensitive search. An optional starting position can be specified. Requires the text to find and the text to search within.

```javascript
hyperformula.FIND("World", "Hello World") // Returns 7
```

--------------------------------

### Function and Language Settings

Source: https://hyperformula.handsontable.com/api/interfaces/configparams

Configure function argument separators, available function plugins, whitespace handling, and language settings.

```APIDOC
## Function and Language Settings

### functionArgSeparator

#### Description
Sets a separator character that separates procedure arguments in formulas. Must be different from `decimalSeparator` and `thousandSeparator`.

### Method
(Not applicable - configuration parameter)

### Endpoint
(Not applicable - configuration parameter)

### Parameters
#### Query Parameters
- **functionArgSeparator** (string) - Optional - The character used to separate function arguments. Defaults to ','.

### Request Example
```json
{
  "functionArgSeparator": ","
}
```

### Response
#### Success Response (200)
- **functionArgSeparator** (string) - The configured function argument separator.

#### Response Example
```json
{
  "functionArgSeparator": ","
}
```

### functionPlugins

#### Description
Lists additional function plugins to be used by the formula interpreter.

### Method
(Not applicable - configuration parameter)

### Endpoint
(Not applicable - configuration parameter)

### Parameters
#### Query Parameters
- **functionPlugins** (array) - Optional - An array of function plugin identifiers. Defaults to [].

### Request Example
```json
{
  "functionPlugins": []
}
```

### Response
#### Success Response (200)
- **functionPlugins** (array) - The list of configured function plugins.

#### Response Example
```json
{
  "functionPlugins": []
}
```

### ignoreWhiteSpace

#### Description
Controls the set of whitespace characters that are allowed inside a formula.

### Method
(Not applicable - configuration parameter)

### Endpoint
(Not applicable - configuration parameter)

### Parameters
#### Query Parameters
- **ignoreWhiteSpace** (string) - Optional - Controls whitespace handling in formulas. Defaults to 'standard'. Allowed values: "standard" | "any".
  - 'standard': Allows SPACE, TAB, LINE FEED, and CARRIAGE RETURN.
  - 'any': Allows all characters matching the `\s` regex character class.

### Request Example
```json
{
  "ignoreWhiteSpace": "standard"
}
```

### Response
#### Success Response (200)
- **ignoreWhiteSpace** (string) - The configured whitespace handling mode.

#### Response Example
```json
{
  "ignoreWhiteSpace": "standard"
}
```

### language

#### Description
Sets a translation package for function and error names.

### Method
(Not applicable - configuration parameter)

### Endpoint
(Not applicable - configuration parameter)

### Parameters
#### Query Parameters
- **language** (string) - Optional - The language code for function and error name translations. Defaults to 'enGB'.

### Request Example
```json
{
  "language": "enGB"
}
```

### Response
#### Success Response (200)
- **language** (string) - The configured language code.

#### Response Example
```json
{
  "language": "enGB"
}
```
```

--------------------------------

### Search Text Position (Case-Insensitive) (SEARCH)

Source: https://hyperformula.handsontable.com/guide/built-in-functions

Finds the starting position of a search string within a text string, ignoring case and allowing wildcards. An optional starting position can be specified. Requires the search string and the text to search within.

```javascript
hyperformula.SEARCH("world", "Hello World") // Returns 7
```

--------------------------------

### GET /cellValueType

Source: https://hyperformula.handsontable.com/api/classes/hyperformula

Retrieves the general type of a cell's value.

```APIDOC
## GET /cellValueType

### Description
Retrieves the general type of a cell's value (e.g., 'NUMBER', 'STRING', 'BOOLEAN').

### Method
GET

### Endpoint
`/cellValueType`

### Parameters
#### Query Parameters
- **sheet** (number) - Required - The sheet ID.
- **col** (number) - Required - The column index.
- **row** (number) - Required - The row index.

### Request Example
```json
{
  "sheet": 0,
  "col": 1,
  "row": 0
}
```

### Response
#### Success Response (200)
- **type** (CellValueType) - The general type of the cell value.

#### Response Example
```json
{
  "type": "NUMBER"
}
```

### Errors
- **NoSheetWithIdError**: If the specified sheet ID does not exist.
- **EvaluationSuspendedError**: If the evaluation is suspended.
- **ExpectedValueOfTypeError**: If `cellAddress` is of wrong type.
```

--------------------------------

### Load HyperFormula Minimal Build and Dependencies from CDN

Source: https://hyperformula.handsontable.com/guide/client-side-installation

Use these script tags to load a minimal HyperFormula build along with its specific dependencies (Chevrotain and TinyEmitter) from jsDelivr. This is useful if you already have some dependencies or want to manage them separately.

```html
<script src="https://cdn.jsdelivr.net/npm/chevrotain@6/lib/chevrotain.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/tiny-emitter@2/dist/tinyemitter.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/hyperformula/dist/hyperformula.min.js"></script>

```

--------------------------------

### GET /cellValueFormat

Source: https://hyperformula.handsontable.com/api/classes/hyperformula

Retrieves auxiliary format information for a cell's value.

```APIDOC
## GET /cellValueFormat

### Description
Retrieves auxiliary format information for a cell's value, such as currency symbols or date formats.

### Method
GET

### Endpoint
`/cellValueFormat`

### Parameters
#### Query Parameters
- **sheet** (number) - Required - The sheet ID.
- **col** (number) - Required - The column index.
- **row** (number) - Required - The row index.

### Request Example
```json
{
  "sheet": 0,
  "col": 0,
  "row": 0
}
```

### Response
#### Success Response (200)
- **formatInfo** (FormatInfo) - Auxiliary format information for the cell value.

#### Response Example
```json
{
  "formatInfo": "$"
}
```

### Errors
- **NoSheetWithIdError**: If the specified sheet ID does not exist.
- **EvaluationSuspendedError**: If the evaluation is suspended.
- **ExpectedValueOfTypeError**: If `cellAddress` is of wrong type.
```

--------------------------------

### Get Cell Dependents

Source: https://hyperformula.handsontable.com/guide/dependency-graph

Retrieves the immediate dependents (out-neighbors) of a specified cell or range within the dependency graph.

```APIDOC
## GET /api/getCellDependents

### Description
Retrieves the immediate dependents (out-neighbors) of a specified cell or range within the dependency graph. This method helps identify which cells directly rely on the output of a given cell.

### Method
GET

### Endpoint
`/api/getCellDependents`

### Parameters
#### Query Parameters
- **sheet** (number) - Required - The sheet index of the cell or range.
- **col** (number) - Required - The column index of the cell or range.
- **row** (number) - Required - The row index of the cell or range.

### Request Example
```javascript
const hfInstance = HyperFormula.buildFromArray([[ '1', '=A1', '=A1+B1', '=B1+C1' ]])
hfInstance.getCellDependents({ sheet: 0, col: 0, row: 0 })
```

### Response
#### Success Response (200)
- **dependents** (array) - An array of objects, where each object represents a dependent cell with `sheet`, `col`, and `row` properties.

#### Response Example
```json
{
  "dependents": [
    { "sheet": 0, "col": 1, "row": 0 },
    { "sheet": 0, "col": 2, "row": 0 }
  ]
}
```
```

--------------------------------

### Get Cell Precedents

Source: https://hyperformula.handsontable.com/guide/dependency-graph

Retrieves the immediate precedents (in-neighbors) of a specified cell or range within the dependency graph.

```APIDOC
## GET /api/getCellPrecedents

### Description
Retrieves the immediate precedents (in-neighbors) of a specified cell or range within the dependency graph. This method is useful for understanding the direct upstream dependencies of a cell.

### Method
GET

### Endpoint
`/api/getCellPrecedents`

### Parameters
#### Query Parameters
- **sheet** (number) - Required - The sheet index of the cell or range.
- **col** (number) - Required - The column index of the cell or range.
- **row** (number) - Required - The row index of the cell or range.

### Request Example
```javascript
const hfInstance = HyperFormula.buildFromArray([[ '1', '2', '=A1', '=B1+C1' ]]);
hfInstance.getCellPrecedents({ sheet: 0, col: 3, row: 0 });
```

### Response
#### Success Response (200)
- **precedents** (array) - An array of objects, where each object represents a precedent cell with `sheet`, `col`, and `row` properties.

#### Response Example
```json
{
  "precedents": [
    { "sheet": 0, "col": 1, "row": 0 },
    { "sheet": 0, "col": 2, "row": 0 }
  ]
}
```
```

--------------------------------

### Display Information Message (JavaScript)

Source: https://hyperformula.handsontable.com/guide/undo-redo

Displays a given message in the information box. This function selects the '.example #info-box' element and sets its text content to the provided message string. It's useful for user feedback.

```javascript
function displayInfo(message) {
  const infoBoxDOM = document.querySelector('.example #info-box');
  infoBoxDOM.innerText = message;
}
```

--------------------------------

### Package JSON Configuration

Source: https://hyperformula.handsontable.com/guide/clipboard-operations

This JSON object represents the package.json file for the HyperFormula demo. It specifies the package name, version, main file, and dependencies, including 'hyperformula' and 'moment', both set to the latest versions.

```json
{
  "name": "hyperformula-demo",
  "version": "1.0.0",
  "main": "index.html",
  "dependencies": {
    "hyperformula": "latest",
    "moment": "latest"
  }
}
```

--------------------------------

### Get Cell Formula in HyperFormula

Source: https://hyperformula.handsontable.com/guide/basic-operations

Retrieves the formula from a specific cell. Requires the cell's coordinates as a SimpleCellAddress object.

```javascript
const A1Formula = hfInstance.getCellFormula({ sheet: 0, col: 0, row: 0 });
```

--------------------------------

### Update License Key Configuration (JavaScript)

Source: https://hyperformula.handsontable.com/guide/migration-from-0.6-to-1.0

Demonstrates how to update the license key in the HyperFormula configuration options. The open-source version now requires 'gpl-v3' instead of 'agpl-v3'.

```javascript
const options = {
  licenseKey: 'agpl-v3',
}
```

```javascript
const options = {
  // use `gpl-v3` instead of `agpl-v3`
  licenseKey: 'gpl-v3',
}
```

--------------------------------

### Get Cell Value in HyperFormula

Source: https://hyperformula.handsontable.com/guide/basic-operations

Retrieves the value of a specific cell. Requires the cell's coordinates as a SimpleCellAddress object.

```javascript
const B1Value = hfInstance.getCellValue({ sheet: 0, col: 1, row: 0 });
```

--------------------------------

### GET /cellValueDetailedType

Source: https://hyperformula.handsontable.com/api/classes/hyperformula

Retrieves the detailed type of a cell's value, including inferred formatting.

```APIDOC
## GET /cellValueDetailedType

### Description
Retrieves the detailed type of a cell's value, including inferred formatting such as currency or percentage.

### Method
GET

### Endpoint
`/cellValueDetailedType`

### Parameters
#### Query Parameters
- **sheet** (number) - Required - The sheet ID.
- **col** (number) - Required - The column index.
- **row** (number) - Required - The row index.

### Request Example
```json
{
  "sheet": 0,
  "col": 0,
  "row": 0
}
```

### Response
#### Success Response (200)
- **type** (CellValueDetailedType) - The detailed type of the cell value (e.g., 'NUMBER_PERCENT', 'NUMBER_CURRENCY').

#### Response Example
```json
{
  "type": "NUMBER_PERCENT"
}
```

### Errors
- **NoSheetWithIdError**: If the specified sheet ID does not exist.
- **EvaluationSuspendedError**: If the evaluation is suspended.
- **ExpectedValueOfTypeError**: If `cellAddress` is of wrong type.
```

--------------------------------

### Get Text Length (LEN)

Source: https://hyperformula.handsontable.com/guide/built-in-functions

Calculates and returns the total number of characters in a given text string. It takes a single text argument.

```javascript
hyperformula.LEN("Hello World") // Returns 11
```

--------------------------------

### Get Character Code (CODE)

Source: https://hyperformula.handsontable.com/guide/built-in-functions

Returns the numeric code of the first character in a given text string. This function requires a text input.

```javascript
hyperformula.CODE("A") // Returns 65
```

--------------------------------

### Initialize HyperFormula Engine

Source: https://hyperformula.handsontable.com/guide/advanced-usage

Initializes the HyperFormula engine with specified options, including a license key. This is a foundational step for using HyperFormula's calculation capabilities.

```javascript
import { HyperFormula } from 'hyperformula';

const options = {
    licenseKey: 'gpl-v3'
};

// initiate the engine with no data
const hfInstance = HyperFormula.buildEmpty(options);
```

--------------------------------

### GET /cellValue

Source: https://hyperformula.handsontable.com/api/classes/hyperformula

Retrieves the processed cell value for a given cell address. This includes applying rounding and post-processing.

```APIDOC
## GET /cellValue

### Description
Retrieves the processed cell value for a given cell address, including rounding and post-processing.

### Method
GET

### Endpoint
`/cellValue`

### Parameters
#### Query Parameters
- **sheet** (number) - Required - The sheet ID.
- **col** (number) - Required - The column index.
- **row** (number) - Required - The row index.

### Request Example
```json
{
  "sheet": 0,
  "col": 0,
  "row": 0
}
```

### Response
#### Success Response (200)
- **value** (CellValue) - The processed value of the cell.

#### Response Example
```json
{
  "value": "6"
}
```

### Errors
- **ExpectedValueOfTypeError**: If `cellAddress` is of incorrect type.
- **NoSheetWithIdError**: If the specified sheet ID does not exist.
- **EvaluationSuspendedError**: If the evaluation is suspended.
```

--------------------------------

### Initialize HyperFormula and Set Data (JavaScript)

Source: https://hyperformula.handsontable.com/guide/named-expressions

This snippet initializes a HyperFormula instance, adds a new sheet, and populates it with initial data. It also defines several named expressions for use in formulas. Dependencies include the 'hyperformula' library.

```javascript
import HyperFormula from 'hyperformula';

console.log(
  `%c Using HyperFormula ${HyperFormula.version}`,
  'color: blue; font-weight: bold',
);

const tableData = [
  [10, 20, 20, 30],
  [50, 60, 70, 80],
  [90, 100, 110, 120],
  ['=myOneCell', '=myTwoCells', '=myOneColumn', '=myTwoColumns'],
  ['=myFormula+myNumber+34', '=myText', '=myOneRow', '=myTwoRows'],
];

const hf = HyperFormula.buildEmpty({
  licenseKey: 'gpl-v3',
});

const sheetName = hf.addSheet('main');
const sheetId = hf.getSheetId(sheetName);

hf.setCellContents(
  {
    row: 0,
    col: 0,
    sheet: sheetId,
  },
  tableData,
);

hf.addNamedExpression('myOneCell', '=main!$A$1');
hf.addNamedExpression('myTwoCells', '=SUM(main!$A$1, main!$A$2)');
hf.addNamedExpression('myOneColumn', '=SUM(main!$A$1:main!$A$3)');
hf.addNamedExpression('myTwoColumns', '=SUM(main!$A$1:main!$B$3)');
hf.addNamedExpression('myOneRow', '=SUM(main!$A$1:main!$D$1)');
hf.addNamedExpression('myTwoRows', '=SUM(main!$A$1:main!$D$2)');
hf.addNamedExpression('myFormula', '=SUM(0, 1, 1, 2, 3, 5, 8, 13)');
hf.addNamedExpression('myNumber', '=21');
hf.addNamedExpression('myText', 'Apollo 11');

```

--------------------------------

### Undo and Redo Settings

Source: https://hyperformula.handsontable.com/api/interfaces/configparams

Configure the limit for the undo history.

```APIDOC
## Undo and Redo Settings

### undoLimit

#### Description
Sets the number of elements kept in the undo history.

### Method
(Not applicable - configuration parameter)

### Endpoint
(Not applicable - configuration parameter)

### Parameters
#### Query Parameters
- **undoLimit** (number) - Optional - The maximum number of undo steps to store. Defaults to 20.

### Request Example
```json
{
  "undoLimit": 20
}
```

### Response
#### Success Response (200)
- **undoLimit** (number) - The configured undo limit.

#### Response Example
```json
{
  "undoLimit": 20
}
```
```

--------------------------------

### Get HyperFormula Configuration

Source: https://hyperformula.handsontable.com/api/classes/hyperformula

Retrieves the current configuration parameters of the HyperFormula engine instance. The returned value is of type ConfigParams.

```typescript
const hfConfig = hfInstance.getConfig();
```

--------------------------------

### Get Character from Unicode Code Point (UNICHAR)

Source: https://hyperformula.handsontable.com/guide/built-in-functions

Returns the character corresponding to a given Unicode code point. Requires a numeric code point as input.

```javascript
hyperformula.UNICHAR(97) // Returns 'a'
```

--------------------------------

### Initialize and Populate HyperFormula Instance

Source: https://hyperformula.handsontable.com/guide/demo

This JavaScript code initializes a HyperFormula instance with a license key, adds a new sheet named 'main', and populates it with provided table data. It also defines named expressions for 'Year_1' and 'Year_2' to calculate the sum of respective columns.

```javascript
import './styles.css'
import HyperFormula from 'hyperformula';

console.log(
  `%c Using HyperFormula ${HyperFormula.version}`,
  'color: blue; font-weight: bold',
);

const tableData = [
  ['Greg Black', 4.66, '=B1*1.3', '=AVERAGE(B1:C1)', '=SUM(B1:C1)'],
  ['Anne Carpenter', 5.25, '=$B$2*30%', '=AVERAGE(B2:C2)', '=SUM(B2:C2)'],
  ['Natalie Dem', 3.59, '=B3*2.7+2+1', '=AVERAGE(B3:C3)', '=SUM(B3:C3)'],
  ['John Sieg', 12.51, '=B4*(1.22+1)', '=AVERAGE(B4:C4)', '=SUM(B4:C4)'],
  [
    'Chris Aklips',
    7.63,
    '=B5*1.1*SUM(10,20)+1',
    '=AVERAGE(B5:C5)',
    '=SUM(B5:C5)',
  ],
];

// Create an empty HyperFormula instance.
const hf = HyperFormula.buildEmpty({
  licenseKey: 'gpl-v3',
});

// Add a new sheet and get its id.
const sheetName = hf.addSheet('main');
const sheetId = hf.getSheetId(sheetName);

// Fill the HyperFormula sheet with data.
hf.setCellContents(
  {
    row: 0,
    col: 0,
    sheet: sheetId,
  },
  tableData,
);

// Add named expressions for the "TOTAL" row.
hf.addNamedExpression('Year_1', '=SUM(main!$B$1:main!$B$5)');
hf.addNamedExpression('Year_2', '=SUM(main!$C$1:main!$C$5)');

// Bind the events to the buttons.
function bindEvents() {

```

--------------------------------

### Get All Sheet Names - TypeScript

Source: https://hyperformula.handsontable.com/api/classes/hyperformula

Returns an array containing the names of all sheets currently present in the HyperFormula instance. Each name is a string.

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

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

--------------------------------

### Initialize HyperFormula Instance and Sheet

Source: https://hyperformula.handsontable.com/guide/undo-redo

This JavaScript code initializes a HyperFormula instance with a license key and adds a new sheet named 'main'. It then populates the sheet with initial data from `tableData`. The undo stack is cleared to prevent unintended undo operations during initialization.

```javascript
import './styles.css'
import HyperFormula from 'hyperformula';

console.log(
  `%c Using HyperFormula ${HyperFormula.version}`,
  'color: blue; font-weight: bold',
);

/**
 * Initial table data.
 */
const tableData = [
  ['Greg', '2'],
  ['Chris', '4'],
];

// Create an empty HyperFormula instance.
const hf = HyperFormula.buildEmpty({
  licenseKey: 'gpl-v3',
});

// Add a new sheet and get its id.
const sheetName = hf.addSheet('main');
const sheetId = hf.getSheetId(sheetName);

// Fill the HyperFormula sheet with data.
hf.setCellContents(
  {
    row: 0,
    col: 0,
    sheet: sheetId,
  },
  tableData,
);

// Clear the undo stack to prevent undoing the initialization steps.
hf.clearUndoStack();

```

--------------------------------

### Get Unicode Code Point of Character (UNICODE)

Source: https://hyperformula.handsontable.com/guide/built-in-functions

Returns the Unicode code point of the first character in a given text string. Accepts a single text argument.

```javascript
hyperformula.UNICODE("a") // Returns 97
```

--------------------------------

### Package JSON Configuration (JSON)

Source: https://hyperformula.handsontable.com/guide/localizing-functions

Defines the project's metadata and dependencies. It specifies the package name, version, main entry point, and lists 'hyperformula' and 'moment' as latest version dependencies, essential for the demo's functionality.

```json
{
"name": "hyperformula-demo",
"version": "1.0.0",
"main": "index.html",
"dependencies": {
"hyperformula": "latest",
"moment": "latest"
}
}

```

--------------------------------

### Move Rows within Hyperformula Sheet

Source: https://hyperformula.handsontable.com/guide/basic-operations

Demonstrates moving one or more rows within a sheet from a starting row to a target row. The method returns an array of changed cells.

```javascript
const changes = hfInstance.moveRows(0, 0, 1, 2);
```

--------------------------------

### HTML Structure for HyperFormula Demo

Source: https://hyperformula.handsontable.com/guide/demo

This HTML code sets up the basic structure for the HyperFormula demo page. It includes meta tags for character set and viewport, a title, and a div container for the example. Inside the container are buttons for 'Run calculations' and 'Reset', and a table element with predefined columns for Name, Year_1, Year_2, Average, and Sum.

```html
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<title>HyperFormula demo</title>
</head>
<body>
<div class="example">
<button id="run" class="run">
Run calculations
</button>
<button id="reset" class="outline reset">
Reset
</button>
<table>
<colgroup>
<col style="width:22%" />
<col style="width:15%" />
<col style="width:23%" />
<col style="width:20%" />
<col style="width:20%" />
</colgroup>
<thead>
<tr>
<th>Name</th>
<th>Year_1</th>
<th>Year_2</th>
<th>Average</th>
<th>Sum</th>
</tr>
</thead>
<tbody></tbody>
</table>
</div>
</body>
</html>
```

--------------------------------

### Remove Columns in HyperFormula

Source: https://hyperformula.handsontable.com/guide/basic-operations

Removes one or more columns from a specified sheet. It takes the sheet ID and a range (starting position and number of columns) as input. Returns an array of changed cells.

```javascript
const changes = hfInstance.removeColumns(0, [0, 2]);
```

--------------------------------

### HTML Structure for HyperFormula Demo

Source: https://hyperformula.handsontable.com/guide/i18n-features

This HTML code sets up the basic structure for the HyperFormula demo, including meta tags, a title, and a container for the Handsontable interface. It defines buttons for running and resetting calculations and a table structure with headers for Name, Lunch time, Date of Birth, Age, and Salary.

```html
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<title>HyperFormula demo</title>
</head>
<body>
<div class="example">
<button id="run" class="button run">
Run calculations
</button>
<button id="reset" class="button button-outline reset">
Reset
</button>
<table>
<colgroup>
<col style="width:22%" />
<col style="width:15%" />
<col style="width:23%" />
<col style="width:20%" />
<col style="width:20%" />
</colgroup>
<thead>
<tr>
<th>Name</th>
<th>Lunch time</th>
<th>Date of Birth</th>
<th>Age</th>
<th>Salary</th>
</tr>
</thead>
<tbody></tbody>
</table>
</div>
</body>
</html>
```

--------------------------------

### Import HyperFormula Library (JavaScript)

Source: https://hyperformula.handsontable.com/guide/basic-usage

This snippet shows how to import the HyperFormula library when using NPM or Yarn. Ensure the library is installed before using this import statement.

```javascript
import { HyperFormula } from 'hyperformula';

```

--------------------------------

### Load HyperFormula Full Build from CDN

Source: https://hyperformula.handsontable.com/guide/client-side-installation

Embed this script tag to load the complete HyperFormula build, including all necessary dependencies, directly from jsDelivr. This makes HyperFormula available as a global variable.

```html
<script src="https://cdn.jsdelivr.net/npm/hyperformula/dist/hyperformula.full.min.js"></script>

```
Tokens:
Raw