HyperFormula (handsontable/hyperformula)

HyperFormula

https://github.com/handsontable/hyperformula
Admin
HyperFormula is an open-source headless spreadsheet built in TypeScript that parses and evaluates...

Tokens:73,603
Snippets:657
Trust Score:9.4
Update:2 months ago
Show doc for...
Context Summary (auto-generated)
Raw
# HyperFormula

HyperFormula is an open-source, headless spreadsheet engine built in TypeScript that serves as both a parser and evaluator of Excel-compatible formulas. It can be integrated into browser applications or used as a server-side service with Node.js, providing high-speed formula calculation with a library of approximately 400 built-in functions compatible with Microsoft Excel and Google Sheets syntax.

The engine operates without assuming any existing user interface, making it a general-purpose library suitable for various business applications including CRM/ERP calculated fields, custom spreadsheet apps, business logic builders, form builders, educational apps, and online calculators. HyperFormula features support for CRUD operations, undo/redo functionality, clipboard operations, named expressions, data sorting, and formula localization with 17 built-in languages.

## Installation

Install the library from npm:

```bash
npm install hyperformula
```

## Creating HyperFormula Instances

### buildFromArray - Create from 2D Array

Creates a HyperFormula instance with a single sheet from a two-dimensional array.

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

const data = [
  ['10', '20', '30', '=SUM(A1:C1)'],
  ['5', '15', '25', '=AVERAGE(A1:C2)'],
  ['=A1*2', '=B1+B2', '=C1-C2', '=A3+B3+C3']
];

const hfInstance = HyperFormula.buildFromArray(data, { licenseKey: 'gpl-v3' });

// Get calculated values
console.log(hfInstance.getCellValue({ sheet: 0, col: 3, row: 0 })); // 60
console.log(hfInstance.getCellValue({ sheet: 0, col: 3, row: 1 })); // 17.5
console.log(hfInstance.getCellValue({ sheet: 0, col: 3, row: 2 })); // 75
```

### buildFromSheets - Create from Multiple Sheets

Creates a HyperFormula instance with multiple named sheets from an object containing sheet definitions.

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

const sheets = {
  'Revenue': [
    ['Q1', 'Q2', 'Q3', 'Q4', 'Total'],
    [1000, 1200, 1500, 1800, '=SUM(A2:D2)']
  ],
  'Expenses': [
    ['Q1', 'Q2', 'Q3', 'Q4', 'Total'],
    [500, 600, 700, 800, '=SUM(A2:D2)']
  ],
  'Profit': [
    ['Q1', 'Q2', 'Q3', 'Q4', 'Total'],
    ['=Revenue!A2-Expenses!A2', '=Revenue!B2-Expenses!B2',
     '=Revenue!C2-Expenses!C2', '=Revenue!D2-Expenses!D2',
     '=SUM(A2:D2)']
  ]
};

const hfInstance = HyperFormula.buildFromSheets(sheets, { licenseKey: 'gpl-v3' });

// Get profit totals
const profitSheetId = hfInstance.getSheetId('Profit');
console.log(hfInstance.getCellValue({ sheet: profitSheetId, col: 4, row: 1 })); // 2900
```

### buildEmpty - Create Empty Instance

Creates an empty HyperFormula instance that can be populated later.

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

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

// Add sheets and data programmatically
const sheetName = hfInstance.addSheet('MySheet');
const sheetId = hfInstance.getSheetId(sheetName);

hfInstance.setCellContents({ sheet: sheetId, col: 0, row: 0 }, [[100, 200, '=A1+B1']]);

console.log(hfInstance.getCellValue({ sheet: sheetId, col: 2, row: 0 })); // 300
```

## Cell Operations

### setCellContents - Set Cell Values

Sets content for a block of cells starting from a given address. Returns an array of cells whose values changed.

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

const hfInstance = HyperFormula.buildFromArray([
  ['1', '2', '3'],
  ['4', '5', '6']
], { licenseKey: 'gpl-v3' });

// Set a single cell
const changes1 = hfInstance.setCellContents({ sheet: 0, col: 0, row: 0 }, [[100]]);

// Set multiple cells at once
const changes2 = hfInstance.setCellContents({ sheet: 0, col: 0, row: 1 }, [
  ['=A1*2', '=B1*2', '=C1*2'],
  ['=SUM(A1:C1)', '=AVERAGE(A1:C1)', '=MAX(A1:C1)']
]);

console.log(hfInstance.getSheetValues(0));
// [[100, 2, 3], [200, 4, 6], [105, 35, 100]]
```

### getCellValue - Get Calculated Cell Value

Returns the calculated value of a cell, with rounding and post-processing applied.

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

const hfInstance = HyperFormula.buildFromArray([
  ['=PI()', '=TODAY()', '=IF(A1>3, "Yes", "No")'],
  ['=SUM(1,2,3,4,5)', '=CONCATENATE("Hello", " ", "World")', '=SQRT(144)']
], { licenseKey: 'gpl-v3' });

console.log(hfInstance.getCellValue({ sheet: 0, col: 0, row: 0 })); // 3.14159265359
console.log(hfInstance.getCellValue({ sheet: 0, col: 2, row: 0 })); // "Yes"
console.log(hfInstance.getCellValue({ sheet: 0, col: 0, row: 1 })); // 15
console.log(hfInstance.getCellValue({ sheet: 0, col: 1, row: 1 })); // "Hello World"
console.log(hfInstance.getCellValue({ sheet: 0, col: 2, row: 1 })); // 12
```

### getCellFormula - Get Cell Formula

Returns the normalized formula string from a cell, or undefined if the cell doesn't contain a formula.

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

const hfInstance = HyperFormula.buildFromArray([
  ['100', '=A1+50', '=SUM(A1:B1)']
], { licenseKey: 'gpl-v3' });

console.log(hfInstance.getCellFormula({ sheet: 0, col: 0, row: 0 })); // undefined
console.log(hfInstance.getCellFormula({ sheet: 0, col: 1, row: 0 })); // "=A1+50"
console.log(hfInstance.getCellFormula({ sheet: 0, col: 2, row: 0 })); // "=SUM(A1:B1)"
```

## Sheet Operations

### addSheet - Add New Sheet

Adds a new sheet to the workbook. Returns the sheet name (auto-generated if not provided).

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

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

// Add sheet with custom name
const name1 = hfInstance.addSheet('Sales');
console.log(name1); // "Sales"

// Add sheet with auto-generated name
const name2 = hfInstance.addSheet();
console.log(name2); // "Sheet2"

// Populate the sheets
hfInstance.setSheetContent(hfInstance.getSheetId('Sales'), [
  ['Product', 'Price', 'Quantity', 'Total'],
  ['Widget', 10, 5, '=B2*C2'],
  ['Gadget', 25, 3, '=B3*C3']
]);

console.log(hfInstance.getSheetNames()); // ["Sales", "Sheet2"]
```

### setSheetContent - Replace Sheet Content

Replaces the entire content of a sheet with new values.

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

const hfInstance = HyperFormula.buildFromArray([
  ['old', 'data', 'here']
], { licenseKey: 'gpl-v3' });

// Replace with new content
const changes = hfInstance.setSheetContent(0, [
  ['Product', 'Price'],
  ['Apple', 1.50],
  ['Banana', 0.75],
  ['Total', '=SUM(B2:B3)']
]);

console.log(hfInstance.getCellValue({ sheet: 0, col: 1, row: 3 })); // 2.25
```

### getSheetValues - Get All Sheet Values

Returns all calculated cell values from a sheet as a 2D array.

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

const hfInstance = HyperFormula.buildFromArray([
  ['=1+1', '=2+2', '=3+3'],
  ['=A1*2', '=B1*2', '=C1*2'],
  ['=SUM(A1:C1)', '=SUM(A2:C2)', '=SUM(A1:C2)']
], { licenseKey: 'gpl-v3' });

const values = hfInstance.getSheetValues(0);
console.log(values);
// [[2, 4, 6], [4, 8, 12], [12, 24, 36]]
```

## Row and Column Operations

### addRows - Insert Rows

Adds rows at specified positions. Formulas are automatically updated to reflect the new structure.

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

const hfInstance = HyperFormula.buildFromArray([
  ['Item', 'Price'],
  ['Apple', 1.00],
  ['Orange', 1.50],
  ['Total', '=SUM(B2:B3)']
], { licenseKey: 'gpl-v3' });

// Add 2 rows before row index 2 (before "Orange")
const changes = hfInstance.addRows(0, [2, 2]);

// Set content for new rows
hfInstance.setCellContents({ sheet: 0, col: 0, row: 2 }, [
  ['Banana', 0.75],
  ['Grape', 2.00]
]);

console.log(hfInstance.getCellValue({ sheet: 0, col: 1, row: 5 })); // Total now includes new items
```

### removeRows - Delete Rows

Removes rows from specified positions. Returns affected cells.

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

const hfInstance = HyperFormula.buildFromArray([
  ['A', '=ROW()'],
  ['B', '=ROW()'],
  ['C', '=ROW()'],
  ['D', '=ROW()']
], { licenseKey: 'gpl-v3' });

// Remove row at index 1 (row containing "B")
const changes = hfInstance.removeRows(0, [1, 1]);

console.log(hfInstance.getSheetValues(0));
// [['A', 1], ['C', 2], ['D', 3]]
```

### addColumns - Insert Columns

Adds columns at specified positions.

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

const hfInstance = HyperFormula.buildFromArray([
  ['Name', 'Price', 'Total'],
  ['Item1', 100, '=B2*1.1'],
  ['Item2', 200, '=B3*1.1']
], { licenseKey: 'gpl-v3' });

// Add 1 column before column index 2 (before "Total")
hfInstance.addColumns(0, [2, 1]);

// Set quantity in new column
hfInstance.setCellContents({ sheet: 0, col: 2, row: 0 }, [['Qty']]);
hfInstance.setCellContents({ sheet: 0, col: 2, row: 1 }, [[5]]);
hfInstance.setCellContents({ sheet: 0, col: 2, row: 2 }, [[3]]);

// Update total formula
hfInstance.setCellContents({ sheet: 0, col: 3, row: 1 }, [['=B2*C2']]);
hfInstance.setCellContents({ sheet: 0, col: 3, row: 2 }, [['=B3*C3']]);

console.log(hfInstance.getSheetValues(0));
```

## Named Expressions

### addNamedExpression - Create Named Expression

Adds a named expression that can be referenced across the workbook.

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

const hfInstance = HyperFormula.buildFromArray([
  ['Subtotal', 1000],
  ['Tax', '=Subtotal*TaxRate'],
  ['Total', '=Subtotal+Tax']
], { licenseKey: 'gpl-v3' });

// Add global named expressions
hfInstance.addNamedExpression('Subtotal', '=Sheet1!$B$1');
hfInstance.addNamedExpression('TaxRate', '=0.08');

console.log(hfInstance.getCellValue({ sheet: 0, col: 1, row: 1 })); // 80
console.log(hfInstance.getCellValue({ sheet: 0, col: 1, row: 2 })); // 1080

// Add scoped named expression (local to sheet 0)
hfInstance.addNamedExpression('LocalDiscount', '=0.10', 0);
```

### changeNamedExpression - Modify Named Expression

Changes an existing named expression to a new value.

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

const hfInstance = HyperFormula.buildFromArray([
  ['Price', 100],
  ['Discounted', '=Price*DiscountRate']
], { licenseKey: 'gpl-v3' });

hfInstance.addNamedExpression('Price', '=Sheet1!$B$1');
hfInstance.addNamedExpression('DiscountRate', '=0.9');

console.log(hfInstance.getCellValue({ sheet: 0, col: 1, row: 1 })); // 90

// Change discount rate
hfInstance.changeNamedExpression('DiscountRate', '=0.8');

console.log(hfInstance.getCellValue({ sheet: 0, col: 1, row: 1 })); // 80
```

## Clipboard Operations

### copy, cut, paste - Clipboard Management

Manages clipboard operations with an internal clipboard store.

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

const hfInstance = HyperFormula.buildFromArray([
  ['100', '=A1*2', '=A1*3'],
  ['', '', ''],
  ['', '', '']
], { licenseKey: 'gpl-v3' });

// Copy a range
const copiedValues = hfInstance.copy({
  start: { sheet: 0, col: 0, row: 0 },
  end: { sheet: 0, col: 2, row: 0 }
});
console.log(copiedValues); // [[100, 200, 300]]

// Paste to new location (formulas adjust references)
hfInstance.paste({ sheet: 0, col: 0, row: 1 });
console.log(hfInstance.getCellFormula({ sheet: 0, col: 1, row: 1 })); // "=A2*2"

// Cut and paste (moves cells)
hfInstance.cut({
  start: { sheet: 0, col: 0, row: 0 },
  end: { sheet: 0, col: 2, row: 0 }
});
hfInstance.paste({ sheet: 0, col: 0, row: 2 });

// Check clipboard status
console.log(hfInstance.isClipboardEmpty()); // true (after paste from cut)
```

## Batch Operations

### batch - Execute Multiple Operations

Combines multiple operations into a single transaction for better performance.

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

const hfInstance = HyperFormula.buildFromArray([
  ['=SUM(A2:A100)'],
  ...Array(99).fill([0])
], { licenseKey: 'gpl-v3' });

// Batch multiple cell updates for performance
const changes = hfInstance.batch(() => {
  for (let i = 1; i < 100; i++) {
    hfInstance.setCellContents({ sheet: 0, col: 0, row: i }, [[i]]);
  }
});

// Recalculation happens only once at the end
console.log(hfInstance.getCellValue({ sheet: 0, col: 0, row: 0 })); // 4950
console.log(changes.length); // Returns all changed cells
```

### suspendEvaluation / resumeEvaluation - Manual Control

Provides manual control over when recalculation occurs.

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

const hfInstance = HyperFormula.buildFromArray([
  ['=SUM(B1:D1)', 0, 0, 0]
], { licenseKey: 'gpl-v3' });

// Suspend evaluation
hfInstance.suspendEvaluation();

// Make multiple changes without triggering recalculation
hfInstance.setCellContents({ sheet: 0, col: 1, row: 0 }, [[10]]);
hfInstance.setCellContents({ sheet: 0, col: 2, row: 0 }, [[20]]);
hfInstance.setCellContents({ sheet: 0, col: 3, row: 0 }, [[30]]);

console.log(hfInstance.isEvaluationSuspended()); // true

// Resume and get all changes at once
const changes = hfInstance.resumeEvaluation();
console.log(hfInstance.getCellValue({ sheet: 0, col: 0, row: 0 })); // 60
```

## Undo/Redo Operations

### undo / redo - History Management

Supports undoing and redoing operations with full history tracking.

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

const hfInstance = HyperFormula.buildFromArray([
  ['Original', 100]
], { licenseKey: 'gpl-v3' });

// Make changes
hfInstance.setCellContents({ sheet: 0, col: 1, row: 0 }, [[200]]);
hfInstance.setCellContents({ sheet: 0, col: 1, row: 0 }, [[300]]);

console.log(hfInstance.getCellValue({ sheet: 0, col: 1, row: 0 })); // 300

// Undo last change
hfInstance.undo();
console.log(hfInstance.getCellValue({ sheet: 0, col: 1, row: 0 })); // 200

// Undo again
hfInstance.undo();
console.log(hfInstance.getCellValue({ sheet: 0, col: 1, row: 0 })); // 100

// Redo
hfInstance.redo();
console.log(hfInstance.getCellValue({ sheet: 0, col: 1, row: 0 })); // 200

// Check availability
console.log(hfInstance.isThereSomethingToUndo()); // true
console.log(hfInstance.isThereSomethingToRedo()); // true
```

## Custom Functions

### registerFunctionPlugin - Add Custom Functions

Registers custom function plugins to extend HyperFormula's capabilities.

```javascript
import { HyperFormula, FunctionPlugin, FunctionArgumentType } from 'hyperformula';

// Create a custom function plugin
class MyPlugin extends FunctionPlugin {
  greet(ast, state) {
    return this.runFunction(ast.args, state, this.metadata('GREET'), (name) => {
      return `Hello, ${name}!`;
    });
  }

  double(ast, state) {
    return this.runFunction(ast.args, state, this.metadata('DOUBLE'), (num) => {
      return num * 2;
    });
  }
}

MyPlugin.implementedFunctions = {
  GREET: {
    method: 'greet',
    parameters: [{ argumentType: FunctionArgumentType.STRING }]
  },
  DOUBLE: {
    method: 'double',
    parameters: [{ argumentType: FunctionArgumentType.NUMBER }]
  }
};

const translations = {
  enGB: { GREET: 'GREET', DOUBLE: 'DOUBLE' },
  enUS: { GREET: 'GREET', DOUBLE: 'DOUBLE' }
};

// Register before creating instance
HyperFormula.registerFunctionPlugin(MyPlugin, translations);

const hfInstance = HyperFormula.buildFromArray([
  ['World', '=GREET(A1)'],
  [50, '=DOUBLE(A2)']
], { licenseKey: 'gpl-v3' });

console.log(hfInstance.getCellValue({ sheet: 0, col: 1, row: 0 })); // "Hello, World!"
console.log(hfInstance.getCellValue({ sheet: 0, col: 1, row: 1 })); // 100
```

## Event Handling

### on / off - Event Subscription

Subscribe to events for reactive updates when data changes.

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

const hfInstance = HyperFormula.buildFromArray([
  ['=A2+A3', 10, 20]
], { licenseKey: 'gpl-v3' });

// Subscribe to value updates
hfInstance.on('valuesUpdated', (changes) => {
  console.log('Values changed:', changes.map(c => ({
    address: c.address,
    newValue: c.newValue
  })));
});

// Subscribe to sheet events
hfInstance.on('sheetAdded', (sheetName) => {
  console.log('Sheet added:', sheetName);
});

hfInstance.on('sheetRemoved', (sheetName, changes) => {
  console.log('Sheet removed:', sheetName);
});

// Trigger events
hfInstance.setCellContents({ sheet: 0, col: 1, row: 0 }, [[100]]);
// Logs: Values changed: [{ address: { sheet: 0, col: 0, row: 0 }, newValue: 120 }]

hfInstance.addSheet('NewSheet');
// Logs: Sheet added: NewSheet
```

## Formula Helpers

### calculateFormula - Fire-and-Forget Calculation

Calculates a formula without adding it to any cell.

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

const hfInstance = HyperFormula.buildFromArray([
  [10, 20, 30, 40, 50]
], { licenseKey: 'gpl-v3' });

// Calculate formula in context of sheet 0
const result1 = hfInstance.calculateFormula('=SUM(A1:E1)', 0);
console.log(result1); // 150

const result2 = hfInstance.calculateFormula('=AVERAGE(A1:E1)*2', 0);
console.log(result2); // 60

const result3 = hfInstance.calculateFormula('=IF(SUM(A1:E1)>100, "High", "Low")', 0);
console.log(result3); // "High"
```

### validateFormula - Check Formula Syntax

Validates if a string is a valid formula.

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

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

console.log(hfInstance.validateFormula('=SUM(1,2,3)')); // true
console.log(hfInstance.validateFormula('=IF(A1>0, "Yes", "No")')); // true
console.log(hfInstance.validateFormula('=INVALID(((')); // false
console.log(hfInstance.validateFormula('Not a formula')); // false
```

## Date and Time Helpers

### numberToDate / numberToDateTime / numberToTime

Convert serial numbers to date/time objects.

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

const hfInstance = HyperFormula.buildFromArray([
  ['=DATE(2024,6,15)', '=NOW()']
], { licenseKey: 'gpl-v3' });

// Get the serial number from a date cell
const dateSerial = hfInstance.getCellValue({ sheet: 0, col: 0, row: 0 });

// Convert serial number to date object
const dateObj = hfInstance.numberToDate(dateSerial);
console.log(dateObj); // { year: 2024, month: 6, day: 15 }

// Convert to datetime (includes time component)
const dateTimeObj = hfInstance.numberToDateTime(dateSerial + 0.5); // noon
console.log(dateTimeObj); // { year: 2024, month: 6, day: 15, hours: 12, minutes: 0, seconds: 0 }

// Convert to time only
const timeObj = hfInstance.numberToTime(0.75); // 6pm
console.log(timeObj); // { hours: 18, minutes: 0, seconds: 0 }
```

## Summary

HyperFormula excels as a computation engine for applications requiring spreadsheet-like functionality without a visual interface. Its primary use cases include building business logic layers for enterprise applications, creating calculated fields in CRM/ERP systems, implementing form builders with dynamic calculations, developing financial modeling tools, and serving as a deterministic compute layer for AI and LLM applications. The engine's ability to handle complex formula dependencies, cross-sheet references, and named expressions makes it suitable for sophisticated data processing scenarios.

Integration patterns typically involve creating a HyperFormula instance during application initialization, populating it with data from external sources (databases, APIs, user input), and using event subscriptions to react to calculated value changes. For high-performance scenarios, batch operations should be used when making multiple changes, and the suspend/resume evaluation pattern provides fine-grained control over when recalculations occur. The library's TypeScript foundation ensures type safety in modern development environments, and its compatibility with React, Angular, Vue, and Svelte enables seamless integration with popular frontend frameworks.