File import

Import XLSX and CSV files into HyperFormula.

Overview

HyperFormula has no built-in file import functionality. But its factory methods use standard JavaScript data types, for easy integration with any way of importing data.

Import CSV files

To import CSV files, use a third-party CSV parser (opens new window) (e.g., PapaParse (opens new window) or csv-parse (opens new window)). Then pass the result to HyperFormula as a JavaScript array.

Import XLSX files

To import XLSX files, use a third-party XLSX parser (opens new window) (e.g., ExcelJS (opens new window) or xlsx (opens new window)). Then pass the result to HyperFormula as a JavaScript array.

Excel's internal function prefixes

Excel stores some function names with an internal prefix. It marks every function added after Excel 2007 this way, so a file can contain _xlfn.IFS(...) where the user typed IFS(...). The prefixes are an artifact of how Excel saves the file, not part of the function name.

Whether the prefixes reach your code depends on the parser you use: ExcelJS, for example, passes them through.

HyperFormula ignores these prefixes, so you can pass the formula straight to the engine:

Prefix	Example
_xlfn.	=_xlfn.IFS(A1>B1,"Pass",A1<=B1,"Fail")
_xlfn._xlws.	=_xlfn._xlws.FILTER(A1:A9,B1:B9>1)
_xlws.	=_xlws.SORT(A1:A9)
_xludf.	=_xludf.MY_FUNCTION()

The _xlpm. prefix marks a LAMBDA/LET parameter name. HyperFormula does not support LAMBDA or LET, with or without the prefixes: a LET formula returns #NAME?, and a LAMBDA called in place, such as =_xlfn.LAMBDA(_xlpm.x,_xlpm.x+1)(1), is a parsing error.

getCellFormula() returns the formula without the prefix, so =_xlfn.IFS(A1>B1,"Pass",A1<=B1,"Fail") reads back as =IFS(A1>B1,"Pass",A1<=B1,"Fail").

A prefix does not add a function. If HyperFormula does not support the function itself, the cell holds a #NAME? error. See the list of supported functions.

Example: Import XLSX files in Node

This example uses ExcelJS (opens new window) to import XLSX files into HyperFormula.

See full example on GitHub (opens new window).

const ExcelJS = require('exceljs');
const { HyperFormula } = require('hyperformula');

async function run(filename) {
  const xlsxWorkbook = await readXlsxWorkbookFromFile(filename);
  const sheetsAsJavascriptArrays = convertXlsxWorkbookToJavascriptArrays(xlsxWorkbook)
  const hf = HyperFormula.buildFromSheets(sheetsAsJavascriptArrays, { licenseKey: 'gpl-v3' });

  console.log('Formulas:', hf.getSheetSerialized(0));
  console.log('Values:  ', hf.getSheetValues(0));
}

async function readXlsxWorkbookFromFile(filename) {
  const workbook = new ExcelJS.Workbook();
  await workbook.xlsx.readFile(filename);
  return workbook;
}

function convertXlsxWorkbookToJavascriptArrays(workbook) {
  const workbookData = {};

  workbook.eachSheet((worksheet) => {
    const sheetDimensions = worksheet.dimensions
    const sheetData = [];

    for (let rowNum = sheetDimensions.top; rowNum <= sheetDimensions.bottom; rowNum++) {
      const rowData = [];

      for (let colNum = sheetDimensions.left; colNum <= sheetDimensions.right; colNum++) {
        const cell = worksheet.getCell(rowNum, colNum)

        const cellData = cell.formula ? `=${cell.formula}` : cell.value;
        rowData.push(cellData);
      }

      sheetData.push(rowData);
    }

    workbookData[worksheet.name] = sheetData;
  })

  return workbookData;
}

run('sample_file.xlsx');