Description
HyperFormula fails to parse formulas containing Excel's internal function prefixes. These prefixes are present in the raw XML of every .xlsx file for any function added after the original OOXML spec (~Excel 2007) and affect 180+ functions including commonly used ones like IFS, SWITCH, XLOOKUP, CONCAT, TEXTJOIN, MAXIFS, and FILTER.
Users who parse .xlsx files with libraries like SheetJS or ExcelJS get the raw prefixed formula strings (e.g., =_xlfn.IFS(C5<C7,"Pass",C5>=C7,"Fail")). Passing these to HyperFormula produces:
Parsing error. Redundant input, expecting EOF but found: (
Every major spreadsheet library (SheetJS, ExcelJS, openpyxl, Apache POI, LibreOffice) strips these prefixes on import. HyperFormula currently does not.
Root cause
The ProcedureName token in src/parser/LexerConfig.ts requires the first character to be a letter:
pattern: new RegExp(`([${UNICODE_LETTER_PATTERN}][${NON_RESERVED_CHARACTER_PATTERN}]*)\\(`)
// UNICODE_LETTER_PATTERN = 'A-Za-z\u00C0-\u02AF' — no underscore
Since every Excel internal prefix starts with _, none of them match as a function name. The lexer tokenizes the prefix as something else, then reports "expecting EOF but found (".
Reproduction
const hf = HyperFormula.buildFromSheets({
Sheet1: { cells: [[{ cellValue: 10 }, {}, { cellValue: 5 }]] } // A1=10, C1=5
});
// Works
hf.setCellContents({ sheet: 0, row: 0, col: 3 }, [['=IFS(A1>C1,"Pass",A1<=C1,"Fail")']]);
hf.getCellValue({ sheet: 0, row: 0, col: 3 }); // "Pass"
// Fails — same formula with Excel's internal prefix
hf.setCellContents({ sheet: 0, row: 0, col: 3 }, [['=_xlfn.IFS(A1>C1,"Pass",A1<=C1,"Fail")']]);
hf.getCellValue({ sheet: 0, row: 0, col: 3 }); // CellError: Parsing error
Verified against HyperFormula 3.2.0.
Prefixes to strip
All of these produce the same parse error and should be stripped before lexing:
Prefix Introduced Meaning Example
_xlfn. Excel 2010 Future functions _xlfn.IFS(...), _xlfn.XLOOKUP(...)
_xlfn._xlws. Excel 365 Future + worksheet-scoped _xlfn._xlws.FILTER(...)
_xlws. Excel 365 Worksheet-scoped _xlws.SORT(...)
_xlpm. Excel 365 LAMBDA/LET parameter names _xlpm.x inside a LAMBDA body
_xludf. Excel 365 User-defined (LAMBDA in Name Manager) _xludf.MyCustomFn(...)
All verified to fail against HyperFormula 3.2.0 with the same "Parsing error. Redundant input, expecting EOF but found: (" error.
Suggested fix
A formula preprocessing step that strips the prefixes before they reach the lexer:
formula.replace(/_xlfn\._xlws\.|_xlfn\.|_xlws\.|_xlpm\.|_xludf\./g, '')
(Longer prefixes ordered first so _xlfn._xlws. matches fully rather than being partially consumed by _xlfn..)
This is low risk — these prefixes are serialization artifacts, not real function names, and stripping them is the standard approach used by every other spreadsheet engine.
Raised in passing in #1392 but never addressed. Also reported on the Handsontable forum by users importing .xlsx files.
Video or screenshots
No response
Demo
https://forum.handsontable.com/t/error-message-when-using-ifs-function/9057
HyperFormula version
3.2
Your framework
No response
Your environment
Chrome on MacOS
Description
HyperFormula fails to parse formulas containing Excel's internal function prefixes. These prefixes are present in the raw XML of every .xlsx file for any function added after the original OOXML spec (~Excel 2007) and affect 180+ functions including commonly used ones like IFS, SWITCH, XLOOKUP, CONCAT, TEXTJOIN, MAXIFS, and FILTER.
Users who parse .xlsx files with libraries like SheetJS or ExcelJS get the raw prefixed formula strings (e.g., =_xlfn.IFS(C5<C7,"Pass",C5>=C7,"Fail")). Passing these to HyperFormula produces:
Parsing error. Redundant input, expecting EOF but found: (
Every major spreadsheet library (SheetJS, ExcelJS, openpyxl, Apache POI, LibreOffice) strips these prefixes on import. HyperFormula currently does not.
Root cause
The
ProcedureNametoken insrc/parser/LexerConfig.tsrequires the first character to be a letter:Since every Excel internal prefix starts with
_, none of them match as a function name. The lexer tokenizes the prefix as something else, then reports "expecting EOF but found (".Reproduction
Verified against HyperFormula 3.2.0.
Prefixes to strip
All of these produce the same parse error and should be stripped before lexing:
Prefix Introduced Meaning Example
_xlfn. Excel 2010 Future functions _xlfn.IFS(...), _xlfn.XLOOKUP(...)
_xlfn._xlws. Excel 365 Future + worksheet-scoped _xlfn._xlws.FILTER(...)
_xlws. Excel 365 Worksheet-scoped _xlws.SORT(...)
_xlpm. Excel 365 LAMBDA/LET parameter names _xlpm.x inside a LAMBDA body
_xludf. Excel 365 User-defined (LAMBDA in Name Manager) _xludf.MyCustomFn(...)
All verified to fail against HyperFormula 3.2.0 with the same "Parsing error. Redundant input, expecting EOF but found: (" error.
Suggested fix
A formula preprocessing step that strips the prefixes before they reach the lexer:
formula.replace(/_xlfn\._xlws\.|_xlfn\.|_xlws\.|_xlpm\.|_xludf\./g, '')(Longer prefixes ordered first so _xlfn._xlws. matches fully rather than being partially consumed by _xlfn..)
This is low risk — these prefixes are serialization artifacts, not real function names, and stripping them is the standard approach used by every other spreadsheet engine.
Raised in passing in #1392 but never addressed. Also reported on the Handsontable forum by users importing .xlsx files.
Video or screenshots
No response
Demo
https://forum.handsontable.com/t/error-message-when-using-ifs-function/9057
HyperFormula version
3.2
Your framework
No response
Your environment
Chrome on MacOS