Internationalization features

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

Contents:

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):

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

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

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

Tip

In HyperFormula, both decimalSeparator and thousandSeparator must be different from functionArgSeparator. In some cases it might cause compatibility issues with other spreadsheets, e.g., Microsoft Excel or Google Sheets.

Currency symbol

To match your users’ currency, you can configure multiple currency symbols (currencySymbol).

The default currency symbol is $. To add USD as an alternative, set:

currencySymbol: ['$', 'USD'],

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:

localeLang: 'en', // set by default

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

localeLang: 'en-US',

To further customize string comparison rules, use these options:

• caseSensitive
• accentSensitive
• caseFirst
• ignorePunctuation

Compatibility with other spreadsheet software

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

• Compatibility with Microsoft Excel
• Compatibility with Google Sheets

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.

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

en-US demo

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

Run calculations Reset

Name	Lunch time	Date of Birth	Age	Salary

Source code

/* start:skip-in-sandbox */
const enUS = HyperFormula.languages.enUS;
/* end:skip-in-sandbox */
/**
 * 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();