Skip to content
v3.3.0
—

Advanced usage

The following example shows how to use formulas to find out which of the two Teams (A or B) is the winning one. You will do that by comparing the average scores of players in each team.

The initial steps are the same as in the basic example. First, import HyperFormula and choose the configuration options:

import { HyperFormula } from 'hyperformula';


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

This time you will use the buildFromEmpty static method to initialize the engine:

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

Now, let’s prepare some data. The first column will be players’ IDs and the second column will be their scores. Then, you will define the formulas responsible for calculating the average scores.

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


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


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

Now prepare sheets and insert the data into them:

// add 'TeamA' sheet
const sheetNameA = hfInstance.addSheet('TeamA');
// get the new sheet ID for further API calls
const sheetIdA = hfInstance.getSheetId(sheetNameA);
// insert playersA content into targeted 'TeamA' sheet
hfInstance.setSheetContent(sheetIdA, playersA);


// add 'TeamB' sheet
const sheetNameB = hfInstance.addSheet('TeamB');
// get the new sheet ID for further API calls
const sheetIdB = hfInstance.getSheetId(sheetNameB);
// insert playersB content into targeted 'TeamB' sheet
hfInstance.setSheetContent(sheetIdB, playersB);


// check the content in the console output
console.log(hfInstance.getAllSheetsValues());

After setting everything up, you can add formulas:

// add a sheet named 'Formulas'
const sheetNameC = hfInstance.addSheet('Formulas');
// get the new sheet ID for further API calls
const sheetIdC = hfInstance.getSheetId(sheetNameC);
// add formulas to that sheet
hfInstance.setSheetContent(sheetIdC, formulas);

Almost done! Now, you can use the getSheetValues method to get all values including the calculated ones. Alternatively, you can use getCellValueto get the value from a specific cell.

// get all sheet values
const sheetValues = hfInstance.getSheetValues(sheetIdC);


// get the simple cell address of 'A1' from that sheet
const simpleCellAddress = hfInstance.simpleCellAddressFromString('A1', sheetIdC);


// check the winning team 🎉
const winningTeam = hfInstance.getCellValue(simpleCellAddress);


// print the result to the console
console.log(winningTeam)

Demo

Section titled “Demo”
🏆

Team A

ID Score

Team B

ID Score

Formulas

Source code 
// 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',
});


const sheetInfo = {
  teamA: { sheetName: 'TeamA' },
  teamB: { sheetName: 'TeamB' },
  formulas: { sheetName: 'Formulas' },
};


// add 'TeamA' sheet
hf.addSheet(sheetInfo.teamA.sheetName);
// insert playersA content into targeted 'TeamA' sheet
hf.setSheetContent(hf.getSheetId(sheetInfo.teamA.sheetName), playersAData);
// add 'TeamB' sheet
hf.addSheet(sheetInfo.teamB.sheetName);
// insert playersB content into targeted 'TeamB' sheet
hf.setSheetContent(hf.getSheetId(sheetInfo.teamB.sheetName), playersBData);
// add a sheet named 'Formulas'
hf.addSheet(sheetInfo.formulas.sheetName);
// add formulas to that sheet
hf.setSheetContent(hf.getSheetId(sheetInfo.formulas.sheetName), formulasData);


/**
 * Fill the HTML table with data.
 *
 * @param {string} sheetName Sheet name.
 */
function renderTable(sheetName) {
  const sheetId = hf.getSheetId(sheetName);
  const tbodyDOM = document.querySelector(
    `.example #${sheetName}-container tbody`,
  );


  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 cellHasFormula = hf.doesCellHaveFormula(cellAddress);
      let cellValue = '';


      if (!hf.isCellEmpty(cellAddress) && !cellHasFormula) {
        cellValue = hf.getCellValue(cellAddress);
      } else {
        cellValue = hf.getCellFormula(cellAddress);
      }


      newTbodyHTML += `<td><span>${cellValue}</span></td>`;
    }


    newTbodyHTML += '</tr>';
  }


  tbodyDOM.innerHTML = newTbodyHTML;
}


/**
 * Render the result block
 */
function renderResult() {
  const resultOutputDOM = document.querySelector('.example #output');
  const cellAddress = hf.simpleCellAddressFromString(
    `${sheetInfo.formulas.sheetName}!A1`,
    hf.getSheetId(sheetInfo.formulas.sheetName),
  );


  resultOutputDOM.innerHTML = `<span>
  <strong>${hf.getCellValue(cellAddress)}</strong> won!
  </span>`;
}


/**
 * Bind the events to the buttons.
 */
function bindEvents() {
  const runButton = document.querySelector('.example #run');


  runButton.addEventListener('click', () => {
    renderResult();
  });
}


// Bind the button events.
bindEvents();


// Render the preview tables.
for (const [_, tableInfo] of Object.entries(sheetInfo)) {
  renderTable(tableInfo.sheetName);
}