# 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](/docs/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`](/docs/api/classes/hyperformula#clearsheet),
[`setSheetContent`](/docs/api/classes/hyperformula#setsheetcontent), [`setCellContents`](/docs/api/classes/hyperformula#setcellcontents), [`addNamedExpression`](/docs/api/classes/hyperformula#addnamedexpression),
[`changeNamedExpression`](/docs/api/classes/hyperformula#changenamedexpression), and [`removeNamedExpression`](/docs/api/classes/hyperformula#removenamedexpression). It is advised
to [batch](/docs/guide/batch-operations) them.
## Sheets
### Adding a sheet
A sheet can be added by using the [`addSheet`](/docs/api/classes/hyperformula#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.
```javascript
// 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`](/docs/api/classes/hyperformula#countsheets) method. This
method does not require any parameters.
```javascript
// count the number of sheets you added
const sheetsCount = hfInstance.countSheets();
```
### Removing a sheet
A sheet can be removed by using the [`removeSheet`](/docs/api/classes/hyperformula#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](#changes-array).
```javascript
// 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`](/docs/api/classes/hyperformula#renamesheet) method. You need to
pass the ID of a sheet you want to rename (you can get it with the
[`getSheetId`](/docs/api/classes/hyperformula#getsheetid) method only if you know its name) along with a new name
as the first and second parameters, respectively.
```javascript
// 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`](/docs/api/classes/hyperformula#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](#changes-array).
```javascript
// 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`](/docs/api/classes/hyperformula#setsheetcontent), in which you can pass
the sheet ID and its new values.
This method returns [an array of changed cells](#changes-array).
```javascript
// 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`](/docs/api/classes/hyperformula#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](#changes-array).
```javascript
// 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`](/docs/api/classes/hyperformula#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](#changes-array).
```javascript
// 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`](/docs/api/classes/hyperformula#moverows) method. You need
to pass the following parameters:
* Sheet ID
* Starting row
* Number of rows to be moved
* [Target row](/docs/api/classes/hyperformula#moverows)
This method returns [an array of changed cells](#changes-array).
```javascript
// 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`](/docs/api/classes/hyperformula#setroworder) method. You need to pass the following parameters:
* Sheet ID
* [New row order](/docs/api/classes/hyperformula#setroworder)
This method returns [an array of changed cells](#changes-array).
```javascript
// 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`](/docs/api/classes/hyperformula#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](#changes-array).
```javascript
// 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`](/docs/api/classes/hyperformula#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](#changes-array).
```javascript
// 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`](/docs/api/classes/hyperformula#movecolumns) method.
You need to pass the following parameters:
* Sheet ID
* Starting column
* Number of columns to be moved
* [Target column](/docs/api/classes/hyperformula#movecolumns)
This method returns [an array of changed cells](#changes-array).
```javascript
// 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`](/docs/api/classes/hyperformula#setcolumnorder) method. You need to pass the following parameters:
* Sheet ID
* [New column order](/docs/api/classes/hyperformula#setcolumnorder)
This method returns [an array of changed cells](#changes-array).
```javascript
// 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`](/docs/api/interfaces/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`](/docs/api/classes/hyperformula#simplecelladdressfromstring) which you can use to retrieve
the [`SimpleCellAddress`](/docs/api/interfaces/simplecelladdress) .
:::
### Moving cells
You can move one or more cells using the [`moveCells`](/docs/api/classes/hyperformula#movecells) method. You need
to pass the following parameters:
* Source range ([SimpleCellRange](/docs/api/interfaces/simplecellrange))
* Top left corner of the destination range ([SimpleCellAddress](/docs/api/interfaces/simplecelladdress))
This method returns [an array of changed cells](#changes-array).
```javascript
// 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`](/docs/api/classes/hyperformula#setcellcontents) method. You need to pass the top left corner address
of a block as a [`SimpleCellAddress`](/docs/api/interfaces/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](#changes-array).
```javascript
// 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`](/docs/api/classes/hyperformula#getcellvalue) . Remember to
pass the coordinates as a [`SimpleCellAddress`](/docs/api/interfaces/simplecelladdress) .
```javascript
// 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`](/docs/api/classes/hyperformula#getcellformula).
Remember to pass the coordinates as a [`SimpleCellAddress`](/docs/api/interfaces/simplecelladdress) .
```javascript
// 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.
```javascript
// 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.
```javascript
// 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`](/docs/api/globals#exportedchange).
This is a collection of cells whose **values** were affected by an operation,
together with their absolute addresses and new values.
```javascript
[{
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:
```js
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.
Source code
```js
const ANIMATION_ENABLED = true;
/**
* Return sample data for the provided number of rows and columns.
*
* @param {number} rows Amount of rows to create.
* @param {number} columns Amount of columns to create.
* @returns {string[][]}
*/
function getSampleData(rows, columns) {
const data = [];
for (let r = 0; r < rows; r++) {
data.push([]);
for (let c = 0; c < columns; c++) {
data[r].push(`${Math.floor(Math.random() * 999) + 1}`);
}
}
return data;
}
/**
* A simple state object for the demo.
*
* @type {object}
*/
const state = {
currentSheet: null,
};
/**
* Input configuration and definition.
*
* @type {object}
*/
const inputConfig = {
'add-sheet': {
inputs: [
{
type: 'text',
placeholder: 'Sheet name',
},
],
buttonText: 'Add Sheet',
disclaimer:
'For the sake of this demo, the new sheets will be filled with random data.',
},
'remove-sheet': {
inputs: [
{
type: 'text',
placeholder: 'Sheet name',
},
],
buttonText: 'Remove Sheet',
},
'add-rows': {
inputs: [
{
type: 'number',
placeholder: 'Index',
},
{
type: 'number',
placeholder: 'Amount',
},
],
buttonText: 'Add Rows',
},
'add-columns': {
inputs: [
{
type: 'number',
placeholder: 'Index',
},
{
type: 'number',
placeholder: 'Amount',
},
],
buttonText: 'Add Columns',
},
'remove-rows': {
inputs: [
{
type: 'number',
placeholder: 'Index',
},
{
type: 'number',
placeholder: 'Amount',
},
],
buttonText: 'Remove Rows',
},
'remove-columns': {
inputs: [
{
type: 'number',
placeholder: 'Index',
},
{
type: 'number',
placeholder: 'Amount',
},
],
buttonText: 'Remove Columns',
},
'get-value': {
inputs: [
{
type: 'text',
placeholder: 'Cell Address',
},
{
type: 'text',
disabled: 'disabled',
placeholder: '',
},
],
disclaimer: 'Cell addresses format examples: A1, B4, C6.',
buttonText: 'Get Value',
},
'set-value': {
inputs: [
{
type: 'text',
placeholder: 'Cell Address',
},
{
type: 'text',
placeholder: 'Value',
},
],
disclaimer: 'Cell addresses format examples: A1, B4, C6.',
buttonText: 'Set Value',
},
};
// 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));
/**
* Fill the HTML table with data.
*/
function renderTable() {
const sheetId = hf.getSheetId(state.currentSheet);
const tbodyDOM = document.querySelector('.example tbody');
const updatedCellClass = ANIMATION_ENABLED ? 'updated-cell' : '';
const { height, width } = hf.getSheetDimensions(sheetId);
let newTbodyHTML = '';
for (let row = 0; row < height; row++) {
for (let col = 0; col < width; col++) {
const cellAddress = { sheet: sheetId, col, row };
const isEmpty = hf.isCellEmpty(cellAddress);
const cellHasFormula = hf.doesCellHaveFormula(cellAddress);
const showFormula = cellHasFormula;
let cellValue = '';
if (isEmpty) {
cellValue = '';
} else if (!showFormula) {
cellValue = hf.getCellValue(cellAddress);
} else {
cellValue = hf.getCellFormula(cellAddress);
}
newTbodyHTML += `