Internationalization features

Configure HyperFormula to match the languages and regions of your users.

Function names and errors

Each of HyperFormula’s built-in functions and errors is available in 18 languages.

You can easily switch between languages (language).

When adding a custom function, you can define the function’s name in every language that you support.

To support more languages, add a custom language pack.

Date and time formats

To match a region’s calendar conventions, you can set multiple date formats (dateFormats) and time formats (timeFormats).

By default, HyperFormula uses the European date and time formats. You can easily change them.

You can also add custom ways of handling dates and times.

Number format

To match a region’s number format, configure HyperFormula’s decimal separator (decimalSeparator) and thousands separator (thousandSeparator).

By default, HyperFormula uses the European number format (1000000.00):

1
decimalSeparator: '.', // set by default
2
thousandSeparator: '', // set by default

To use the US number format (1,000,000.00), set:

1
decimalSeparator: '.', // set by default
2
thousandSeparator: ',',

Currency symbol

To match your users’ currency, configure recognized currency symbols and (optionally) custom TEXT output formatting. Both sides are covered in the dedicated Currency handling guide.

String comparison rules

To make sure that language-sensitive strings are compared in line with your users’ language (e.g., Préservation vs. Preservation), set HyperFormula’s string comparison rules (localeLang).

The value of localeLang is processed by Intl.Collator, a JavaScript standard object.

The default setting is:

1
localeLang: 'en', // set by default

To set the en-US string comparison rules, set:

1
localeLang: 'en-US',

To further customize string comparison rules, use these options:

Compatibility with other spreadsheet software

For information on compatibility with locale-dependent syntax in other spreadsheet software, see:

`en-US` configuration

This configuration aligns HyperFormula with the en-US locale. Due to the configuration of separators, it might not be fully compatible with formulas coming from other spreadsheet software.

1
language: 'enUS',
2
dateFormats: ['MM/DD/YYYY', 'MM/DD/YY', 'YYYY/MM/DD'],
3
timeFormats: ['hh:mm', 'hh:mm:ss.sss'], // set by default
4
decimalSeparator: '.', // set by default
5
thousandSeparator: ',',
6
functionArgSeparator: ';', // might cause incompatibility with other spreadsheets
7
currencySymbol: ['$', 'USD'],
8
localeLang: 'en-US',

`en-US` demo

This demo shows HyperFormula configured for the en-US locale.

Name	Lunch time	Date of Birth	Age	Salary

{
  "name": "hyperformula-demo",
  "version": "1.0.0",
  "main": "index.html",
  "dependencies": {
    "hyperformula": "latest",
    "moment": "latest"
  }
}

<!DOCTYPE html>
<html>
  <head>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <title>HyperFormula demo</title>
  </head>

  <body>
    <div class="example">
  <button id="run" class="button run">
    Run calculations
  </button>
  <button id="reset" class="button button-outline reset">
    Reset
  </button>
  <table>
    <colgroup>
      <col style="width:22%" />
      <col style="width:15%" />
      <col style="width:23%" />
      <col style="width:20%" />
      <col style="width:20%" />
    </colgroup>
    <thead>
    <tr>
      <th>Name</th>
      <th>Lunch time</th>
      <th>Date of Birth</th>
      <th>Age</th>
      <th>Salary</th>
    </tr>
    </thead>
    <tbody></tbody>
  </table>
</div>
  </body>
</html>

/* general */
.example {
  color: #606c76;
  font-family: sans-serif;
  font-size: 14px;
  font-weight: 400;
  letter-spacing: .01em;
  line-height: 1.6;
  -webkit-box-sizing: border-box;
  box-sizing: border-box;
}

.example *,
.example *::before,
.example *::after {
  -webkit-box-sizing: border-box;
  box-sizing: border-box;
}

/* buttons */

.example button {
  border: 0.1em solid #1c49e4;
  border-radius: .3em;
  color: #fff;
  cursor: pointer;
  display: inline-block;
  font-size: .85em;
  font-family: inherit;
  font-weight: 700;
  height: 3em;
  letter-spacing: .1em;
  line-height: 3em;
  padding: 0 3em;
  text-align: center;
  text-decoration: none;
  text-transform: uppercase;
  white-space: nowrap;
  margin-bottom: 20px;
  background-color: #1c49e4;
}

.example button:hover {
  background-color: #2350ea;
}

.example button.outline {
  background-color: transparent;
  color: #1c49e4;
}

/* labels */

.example label {
  display: inline-block;
  margin-left: 5px;
}

/* inputs */

.example input:not([type='checkbox']), .example select, .example textarea, .example fieldset {
  margin-bottom: 1.5em;
  border: 0.1em solid #d1d1d1;
  border-radius: .4em;
  height: 3.8em;
  width: 12em;
  padding: 0 .5em;
}

.example input:focus,
.example select:focus {
  outline: none;
  border-color: #1c49e4;
}

/* message */

.example .message-box {
  border: 1px solid #1c49e433;
  background-color: #1c49e405;
  border-radius: 0.2em;
  padding: 10px;
}

.example .message-box span {
  animation-name: cell-appear;
  animation-duration: 0.2s;
  margin: 0;
}

/* table */

.example table {
  table-layout: fixed;
  border-spacing: 0;
  overflow-x: auto;
  text-align: left;
  width: 100%;
  counter-reset: row-counter col-counter;
}

.example table tr:nth-child(2n) {
  background-color: #f6f8fa;
}

.example table tr td,
.example table tr th {
  overflow: hidden;
  text-overflow: ellipsis;
  border-bottom: 0.1em solid #e1e1e1;
  padding: 0 1em;
  height: 3.5em;
}

/* table: header row */

.example table thead tr th span::before {
  display: inline-block;
  width: 20px;
}

.example table.spreadsheet thead tr th span::before {
  content: counter(col-counter, upper-alpha);
}

.example table.spreadsheet thead tr th {
  counter-increment: col-counter;
}

/* table: first column */

.example table tbody tr td:first-child {
  text-align: center;
  padding: 0;
}

.example table thead tr th:first-child {
  padding-left: 40px;
}

.example table tbody tr td:first-child span {
  width: 100%;
  display: inline-block;
  text-align: left;
  padding-left: 15px;
  margin-left: 0;
}

.example table tbody tr td:first-child span::before {
  content: counter(row-counter);
  display: inline-block;
  width: 20px;
  position: relative;
  left: -10px;
}

.example table tbody tr {
  counter-increment: row-counter;
}

/* table: summary row */

.example table tbody tr.summary {
  font-weight: 600;
}

/* updated-cell animation */

.example table tr td.updated-cell span {
  animation-name: cell-appear;
  animation-duration: 0.6s;
}

@keyframes cell-appear {
  from {
    opacity: 0;
  }
  to {
    opacity: 1;
  }
}

import './styles.css'
import HyperFormula from 'hyperformula';
import enUS from 'hyperformula/es/i18n/languages/enUS';
import moment from 'moment';

console.log(
  `%c Using HyperFormula ${HyperFormula.version}`,
  'color: blue; font-weight: bold',
);


/**
 * Initial table data.
 */
const tableData = [
  [
    'Greg Black',
    '11:45 AM',
    '05/23/1989',
    '=YEAR(NOW())-YEAR(C1)',
    '$80,000.00',
  ],
  [
    'Anne Carpenter',
    '12:30 PM',
    '01/01/1980',
    '=YEAR(NOW())-YEAR(C2)',
    '$95,000.00',
  ],
  [
    'Natalie Dem',
    '1:30 PM',
    '12/13/1973',
    '=YEAR(NOW())-YEAR(C3)',
    '$78,500.00',
  ],
  [
    'John Sieg',
    '2:00 PM',
    '10/31/1995',
    '=YEAR(NOW())-YEAR(C4)',
    '$114,000.00',
  ],
  [
    'Chris Aklips',
    '11:30 AM',
    '08/18/1987',
    '=YEAR(NOW())-YEAR(C5)',
    '$71,900.00',
  ],
  ['AVERAGE', null, null, '=AVERAGE(D1:D5)', '=AVERAGE(E1:E5)'],
];

const config = {
  language: 'enUS',
  dateFormats: ['MM/DD/YYYY', 'MM/DD/YY', 'YYYY/MM/DD'],
  timeFormats: ['hh:mm', 'hh:mm:ss.sss'],
  decimalSeparator: '.',
  thousandSeparator: ',',
  functionArgSeparator: ';',
  currencySymbol: ['$', 'USD'],
  localeLang: 'en-US',
  licenseKey: 'gpl-v3',
};

if (!HyperFormula.getRegisteredLanguagesCodes().includes('enUS')) {
  HyperFormula.registerLanguage('enUS', enUS);
}

// Create an empty HyperFormula instance.
const hf = HyperFormula.buildEmpty(config);

// Add a new sheet and get its id.
const sheetName = hf.addSheet('main');
const sheetId = hf.getSheetId(sheetName);

// Fill the HyperFormula sheet with data.
hf.setCellContents(
  {
    row: 0,
    col: 0,
    sheet: sheetId,
  },
  tableData,
);

const columnTypes = ['string', 'time', 'date', 'number', 'currency'];

/**
 * Display value in human-readable format
 *
 * @param {SimpleCellAddress} cellAddress Cell address.
 */
function formatCellValue(cellAddress) {
  if (hf.isCellEmpty(cellAddress)) {
    return '';
  }

  if (columnTypes[cellAddress.col] === 'time') {
    return formatTime(hf.numberToTime(hf.getCellValue(cellAddress)));
  }

  if (columnTypes[cellAddress.col] === 'date') {
    return formatDate(hf.numberToDate(hf.getCellValue(cellAddress)));
  }

  if (columnTypes[cellAddress.col] === 'currency') {
    return formatCurrency(hf.getCellValue(cellAddress));
  }

  return hf.getCellValue(cellAddress);
}

/**
 * Date formatting function.
 *
 * @param {{month: *, year: *, day: *}} dateObject Object with date-related information.
 */
function formatDate(dateObject) {
  dateObject.month -= 1;

  return moment(dateObject).format('MM/DD/YYYY');
}

/**
 * Time formatting function.
 *
 * @param dateTimeObject Object with date and time information.
 */
function formatTime(dateTimeObject) {
  return moment(dateTimeObject).format('h:mm A');
}

/**
 * Currency formatting function.
 *
 * @param value Number representing the currency value
 */
function formatCurrency(value) {
  return value.toLocaleString('en-US', {
    style: 'currency',
    currency: 'USD',
  });
}

/**
 * Fill the HTML table with data.
 *
 * @param {boolean} calculated `true` if it should render calculated values, `false` otherwise.
 */
function renderTable(calculated = false) {
  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++) {
    newTbodyHTML += `<tr class="${row === height - 1 ? 'summary' : ''}">`;

    for (let col = 0; col < width; col++) {
      const cellAddress = { sheet: sheetId, col, row };
      const cellHasFormula = hf.doesCellHaveFormula(cellAddress);
      const showFormula = cellHasFormula && !calculated;
      const displayValue = showFormula
        ? hf.getCellFormula(cellAddress)
        : formatCellValue(cellAddress);

      newTbodyHTML += `<td class="${
        cellHasFormula ? updatedCellClass : ''
      }"><span>${displayValue}</span></td>`;
    }

    newTbodyHTML += '</tr>';
  }

  tbodyDOM.innerHTML = newTbodyHTML;
}

/**
 * Replace formulas with their results.
 */
function runCalculations() {
  renderTable(true);
}

/**
 * Replace the values in the table with initial data.
 */
function resetTable() {
  renderTable();
}

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

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

  resetButton.addEventListener('click', () => {
    resetTable();
  });
}

const ANIMATION_ENABLED = true;

// Bind the button events.
bindEvents();

// Render the table.
renderTable();