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:

• Sheet ID
• New row order

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:

• Sheet ID
• New column order

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:

• Source range (SimpleCellRange)
• Top left corner of the destination range (SimpleCellAddress)

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.

SheetName Select action Add sheet Remove sheet Add row(s) Add column(s) Remove row(s) Remove column(s) Get value Set value

Run

Source code

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 += `<td class="${cellHasFormula ? updatedCellClass : ''}"><span>
      ${cellValue}
      </span></td>`;
    }

    newTbodyHTML += '</tr>';
  }

  tbodyDOM.innerHTML = newTbodyHTML;
}

/**
 * Updates the sheet dropdown.
 */
function updateSheetDropdown() {
  const sheetNames = hf.getSheetNames();
  const sheetDropdownDOM = document.querySelector('.example #sheet-select');
  let dropdownContent = '';

  sheetDropdownDOM.innerHTML = '';
  sheetNames.forEach((sheetName) => {
    const isCurrent = sheetName === state.currentSheet;

    dropdownContent += `<option value="${sheetName}" ${isCurrent ? 'selected' : ''}>${sheetName}</option>`;
  });
  sheetDropdownDOM.innerHTML = dropdownContent;
}

/**
 * Update the form to the provided action.
 *
 * @param {string} action Action chosen from the dropdown.
 */
function updateForm(action) {
  const inputsDOM = document.querySelector('.example #inputs');
  const submitButtonDOM = document.querySelector('.example #inputs button');
  const allInputsDOM = document.querySelectorAll('.example #inputs input');
  const disclaimerDOM = document.querySelector('.example #disclaimer');

  // Hide all inputs
  allInputsDOM.forEach((input) => {
    input.style.display = 'none';
    input.value = '';
    input.disabled = false;
  });
  inputConfig[action].inputs.forEach((inputCfg, index) => {
    const inputDOM = document.querySelector(`.example #input-${index + 1}`);

    // Show only those needed
    inputDOM.style.display = 'block';

    for (const [attribute, value] of Object.entries(inputCfg)) {
      inputDOM.setAttribute(attribute, value);
    }
  });
  submitButtonDOM.innerText = inputConfig[action].buttonText;

  if (inputConfig[action].disclaimer) {
    disclaimerDOM.innerHTML = inputConfig[action].disclaimer;
    disclaimerDOM.parentElement.style.display = 'block';
  } else {
    disclaimerDOM.innerHTML = '&nbsp;';
  }

  inputsDOM.style.display = 'block';
}

/**
 * Add the error overlay.
 *
 * @param {string} message Error message.
 */
function renderError(message) {
  const inputsDOM = document.querySelector('.example #inputs');
  const errorDOM = document.querySelector('.example #error-message');

  if (inputsDOM.className.indexOf('error') === -1) {
    inputsDOM.className += ' error';
  }

  errorDOM.innerText = message;
  errorDOM.parentElement.style.display = 'block';
}

/**
 * Clear the error overlay.
 */
function clearError() {
  const inputsDOM = document.querySelector('.example #inputs');
  const errorDOM = document.querySelector('.example #error-message');

  inputsDOM.className = inputsDOM.className.replace(' error', '');
  errorDOM.innerText = '';
  errorDOM.parentElement.style.display = 'none';
}

/**
 * Bind the events to the buttons.
 */
function bindEvents() {
  const sheetDropdown = document.querySelector('.example #sheet-select');
  const actionDropdown = document.querySelector('.example #action-select');
  const submitButton = document.querySelector('.example #inputs button');

  sheetDropdown.addEventListener('change', (event) => {
    state.currentSheet = event.target.value;
    clearError();
    renderTable();
  });
  actionDropdown.addEventListener('change', (event) => {
    clearError();
    updateForm(event.target.value);
  });
  submitButton.addEventListener('click', (event) => {
    const action = document.querySelector('.example #action-select').value;

    doAction(action);
  });
}

/**
 * Perform the wanted action.
 *
 * @param {string} action Action to perform.
 */
function doAction(action) {
  let cellAddress = null;
  const inputValues = [
    document.querySelector('.example #input-1').value || void 0,
    document.querySelector('.example #input-2').value || void 0,
  ];

  clearError();

  switch (action) {
    case 'add-sheet':
      state.currentSheet = hf.addSheet(inputValues[0]);
      handleError(() => {
        hf.setSheetContent(
          hf.getSheetId(state.currentSheet),
          getSampleData(5, 5),
        );
      });
      updateSheetDropdown();
      renderTable();

      break;
    case 'remove-sheet':
      handleError(() => {
        hf.removeSheet(hf.getSheetId(inputValues[0]));
      });

      if (state.currentSheet === inputValues[0]) {
        state.currentSheet = hf.getSheetNames()[0];
        renderTable();
      }

      updateSheetDropdown();

      break;
    case 'add-rows':
      handleError(() => {
        hf.addRows(hf.getSheetId(state.currentSheet), [
          parseInt(inputValues[0], 10),
          parseInt(inputValues[1], 10),
        ]);
      });
      renderTable();

      break;
    case 'add-columns':
      handleError(() => {
        hf.addColumns(hf.getSheetId(state.currentSheet), [
          parseInt(inputValues[0], 10),
          parseInt(inputValues[1], 10),
        ]);
      });
      renderTable();

      break;
    case 'remove-rows':
      handleError(() => {
        hf.removeRows(hf.getSheetId(state.currentSheet), [
          parseInt(inputValues[0], 10),
          parseInt(inputValues[1], 10),
        ]);
      });
      renderTable();

      break;
    case 'remove-columns':
      handleError(() => {
        hf.removeColumns(hf.getSheetId(state.currentSheet), [
          parseInt(inputValues[0], 10),
          parseInt(inputValues[1], 10),
        ]);
      });
      renderTable();

      break;
    case 'get-value':
      const resultDOM = document.querySelector('.example #input-2');

      cellAddress = handleError(() => {
        return hf.simpleCellAddressFromString(
          inputValues[0],
          hf.getSheetId(state.currentSheet),
        );
      }, 'Invalid cell address format.');

      if (cellAddress !== null) {
        resultDOM.value = handleError(() => {
          return hf.getCellValue(cellAddress);
        });
      }

      break;
    case 'set-value':
      cellAddress = handleError(() => {
        return hf.simpleCellAddressFromString(
          inputValues[0],
          hf.getSheetId(state.currentSheet),
        );
      }, 'Invalid cell address format.');

      if (cellAddress !== null) {
        handleError(() => {
          hf.setCellContents(cellAddress, inputValues[1]);
        });
      }

      renderTable();

      break;
    default:
  }
}

/**
 * Handle the HF errors.
 *
 * @param {Function} tryFunc Function to handle.
 * @param {string} [message] Optional forced error message.
 */
function handleError(tryFunc, message = null) {
  let result = null;

  try {
    result = tryFunc();
  } catch (e) {
    if (e instanceof Error) {
      renderError(message || e.message);
    } else {
      renderError('Something went wrong');
    }
  }

  return result;
}

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