HyperFormula (v3.1.0)

# Basic operations

HyperFormula can perform efficient CRUD operations on the workbook. You can apply these operations to various workbook elements, such as:

  • Cells
  • Rows / Columns
  • Sheets

Check the API for a full reference of methods available for CRUD operations.

HyperFormula automatically updates all references, both relative and absolute, in all sheets affected by the change.

Operations affecting only the dependency graph should not decrease performance. However, multiple operations that have an impact on calculation results may affect performance; these are clearSheet, setSheetContent, setCellContents, addNamedExpression, changeNamedExpression, and removeNamedExpression. It is advised to batch them.

# Sheets

# Adding a sheet

A sheet can be added by using the addSheet method. You can pass a name for it or leave it without a parameter. In the latter case the method will create an autogenerated name for it. That name can then be returned for further use.

// the autogenerated sheet name can be assigned to a variable const myNewSheet = hfInstance.addSheet(); // create a sheet with a specific name hfInstance.addSheet('SheetName');

You can also count sheets by using the countSheets method. This method does not require any parameters.

// count the number of sheets you added const sheetsCount = hfInstance.countSheets();

# Removing a sheet

A sheet can be removed by using the removeSheet method. To do that you need to pass a mandatory parameter: the ID of a sheet to be removed. This method returns an array of changed cells.

// track the changes triggered by removing the sheet 0 const changes = hfInstance.removeSheet(0);

# Renaming a sheet

A sheet can be renamed by using the renameSheet method. You need to pass the ID of a sheet you want to rename (you can get it with the getSheetId method only if you know its name) along with a new name as the first and second parameters, respectively.

// rename the first sheet hfInstance.renameSheet(0, 'NewSheetName'); // you can retrieve the sheet ID if you know its name const sheetID = hfInstance.getSheetId('SheetName'); // use the retrieved sheet ID in the method hfInstance.renameSheet(sheetID, 'AnotherNewName');

# Clearing a sheet

A sheet's content can be cleared with the clearSheet method. You need to provide the ID of a sheet whose content you want to clear. This method returns an array of changed cells.

// clear the content of sheet 0 const changes = hfInstance.clearSheet(0);

# Replacing sheet content

Instead of removing and adding the content of a sheet you can replace it right away. To do so use setSheetContent, in which you can pass the sheet ID and its new values. This method returns an array of changed cells.

// set new values for sheet 0 const changes = hfInstance.setSheetContent(0, [['50'], ['60']]);

# Rows

# Adding rows

You can add one or more rows by using the addRows method. The first parameter you need to pass is a sheet ID, and the second parameter represents the position and the size of a block of rows to be added. This method returns an array of changed cells.

// track the changes triggered by adding // two rows at position 0 inside the first sheet const changes = hfInstance.addRows(0, [0, 2]);

# Removing rows

You can remove one or more rows by using the removeRows method. The first parameter you need to pass is a sheet ID, and the second parameter represents the position and the size of a block of rows to be removed. This method returns an array of changed cells.

// track the changes triggered by removing // two rows at position 0 inside the first sheet const changes = hfInstance.removeRows(0, [0, 2]);

# Moving rows

You can move one or more rows by using the moveRows method. You need to pass the following parameters:

  • Sheet ID
  • Starting row
  • Number of rows to be moved
  • Target row

This method returns an array of changed cells.

// track the changes triggered by moving // the first row in the first sheet into row 2 const changes = hfInstance.moveRows(0, 0, 1, 2);

# Reordering rows

You can change the order of rows by using the setRowOrder method. You need to pass the following parameters:

This method returns an array of changed cells.

// row 0 and row 2 swap places const changes = hfInstance.setRowOrder(0, [2, 1, 0]);

# Columns

# Adding columns

You can add one or more columns by using the addColumns method. The first parameter you need to pass is a sheet ID, and the second parameter represents the position and the size of a block of columns to be added. This method returns an array of changed cells.

// track the changes triggered by adding // two columns at position 0 inside the first sheet const changes = hfInstance.addColumns(0, [0, 2]);

# Removing columns

You can remove one or more columns by using the removeColumns method. The first parameter you need to pass is a sheet ID, and the second parameter represents the position and the size of a block of columns to be removed. This method returns an array of changed cells.

// track the changes triggered by removing // two columns at position 0 inside the first sheet const changes = hfInstance.removeColumns(0, [0, 2]);

# Moving columns

You can move one or more columns by using the moveColumns method. You need to pass the following parameters:

  • Sheet ID
  • Starting column
  • Number of columns to be moved
  • Target column

This method returns an array of changed cells.

// track the changes triggered by moving // the first column in the first sheet into column 2 const changes = hfInstance.moveColumns(0, 0, 1, 2);

# Reordering columns

You can change the order of columns by using the setColumnOrder method. You need to pass the following parameters:

This method returns an array of changed cells.

// column 0 and column 2 swap places const changes = hfInstance.setColumnOrder(0, [2, 1, 0]);

# Cells

TIP

By default, cells are identified using a SimpleCellAddress which consists of a sheet ID, column ID, and row ID, like this: { sheet: 0, col: 0, row: 0 }

Alternatively, you can work with the A1 notation known from spreadsheets like Excel or Google Sheets. The API provides the helper function simpleCellAddressFromString which you can use to retrieve the SimpleCellAddress .

# Moving cells

You can move one or more cells using the moveCells method. You need to pass the following parameters:

This method returns an array of changed cells.

// choose the source cells const source = { sheet: 0, col: 1, row: 0 }; // choose the target cells const destination = { sheet: 0, col: 3, row: 0 }; // track the changes triggered by moving // one cell from source to target location const changes = hfInstance.moveCells({ start: source, end: source }, destination);

# Updating cells

You can set the content of a block of cells by using the setCellContents method. You need to pass the top left corner address of a block as a SimpleCellAddress, along with the content to be set. It can be content for either a single cell or a set of cells in an array. This method returns an array of changed cells.

// track the changes triggered by setting // a block of cells with content '=B1' const changes = hfInstance.setCellContents({ col: 3, row: 0, sheet: 0 }, [['=B1']]);

# Getting cell value

You can get the value of a cell by using getCellValue . Remember to pass the coordinates as a SimpleCellAddress .

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

# Getting cell formula

You can retrieve the formula from a cell by using getCellFormula. Remember to pass the coordinates as a SimpleCellAddress .

// get the formula from the A1 cell const A1Formula = hfInstance.getCellFormula({ sheet: 0, col: 0, row: 0 });

# Handling an error

Each time you call a method, HyperFormula will perform the corresponding operation. If there is an issue, it will throw an error. Methods available in the HyperFormula's API might throw different errors, but all of them follow the same pattern. Thus, the errors can be handled in a similar manner.

For example, imagine you let users rename their sheets in an application but by mistake they choose a sheet ID that does not exist. It would be nice to display the error to the user, so they are aware of this fact.

// variable used to carry the message for the user let messageUsedInUI; // attempt to rename a sheet try { hfInstance.renameSheet(5, "Payroll"); // whoops! there is no sheet with an ID of 5 } catch (e) { // notify the user that a sheet with an ID of 5 does not exist if (e instanceof NoSheetWithIdError) { messageUsedInUI = "Sheet with provided ID does not exist"; } // a generic error message, just in case else { messageUsedInUI = "Something went wrong"; } }

# isItPossibleTo* methods

There are also methods that you may find useful to call in pair with the above-mentioned operations. These methods are prefixed with isItPossibleTo* whose sole purpose is to check if the desired operation is possible. They all return a simple boolean value. You will find it handy when you want to give the user a more generic message and you don't want to react to specific errors.

This can be particularly useful for interaction with the UI of the application you work on. For example, you can allow the user to add new sheets by typing a new sheet name inside an input field. You can easily check if that action is allowed, and if it is not, throw an error.

// an instance with some example data const hfInstance = HyperFormula.buildFromArray([ ['1', '2'], ]); // a variable used to carry the message for the user let messageUsedInUI; // use this method to check the possibility to remove columns const isRemovable = hfInstance.isItPossibleToRemoveColumns(0, [1, 1]); // check if there is a possibility to remove columns if (!isRemovable) { messageUsedInUI = 'Sorry, you cannot perform a remove action' }

# Changes array

All data modification methods return an array of ExportedChange. This is a collection of cells whose values were affected by an operation, together with their absolute addresses and new values.

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

This gives you information about where the change happened, what the new value of a cell is, and even what type it is - in this case, an error.

The array of changes includes only cells that have different values after performing the operation. See the example:

const hf = HyperFormula.buildFromArray([ [0], [1], ['=SUM(A1:A2)'], ['=COUNTBLANK(A1:A3)'], ]); // insert an empty row between the row 0 and the row 1 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, // }]

# Demo

This demo presents several basic operations integrated with a sample UI.

← Batch operations →