Errors should be parsed from string and propagated

[wojciechczerniak]

Description

Errors entered by the user or included in the data source are not parsed as Error. They are interpreted as a string value.

• We may refactor error.type to use enum as numbers (see CellError does not provide localized error value that should be displayed in a cell #48)
• We may add error.value to return translated string as value (see CellError does not provide localized error value that should be displayed in a cell #48)
• Should we parse only the error cells that start with =? No

Error syntax

Error ::= '#' [A-Z0-9]+ ([!?] | ('/' ([A-Z] | ([0-9] [!?]))))

Error types

Name	Comments
#DIV/0!	Attempt to divide by zero, including division by an empty cell. ERROR.TYPE of 2 6.13.11
#NAME?	Unrecognized/deleted name. ERROR.TYPE of 5.
#N/A	Not available. ISNA() applied to this value will return TRUE. Lookup functions that failed, and NA(), return this value. ERROR.TYPE of 7.
#NULL!	Intersection of ranges produced zero cells. ERROR.TYPE of 1.
#NUM!	Failed to meet domain constraints (e.g., input was too large or too small). ERROR.TYPE of 6.
#REF!	Reference to invalid cell (e.g., beyond the application’s abilities). ERROR.TYPE of 4.
#VALUE!	Parameter is wrong type. ERROR.TYPE of 3.

ODFF 1.3 spec

Spec says it's optional:

Evaluators may allow entry of errors directly, parse them and recognize them as Errors. Functions shall propagate Errors unless stated otherwise.

Although, both XL and GS parse them according to spec. LC does it if = is present.

Example test

it('Errors should be parsed and propagated', () => {
  const engine = HyperFormula.buildFromArray([
    ['#DIV/0!', '=A1', '=ISERROR(B1)', '=ISERROR(#DIV/0!)', '=#DIV/0!'],
  ])

  expect(engine.getCellValue(adr('A1'))).toBe(new CellError(ErrorType.DIV_BY_ZERO))
  expect(engine.getCellValue(adr('B1'))).toBe(new CellError(ErrorType.DIV_BY_ZERO))
  expect(engine.getCellValue(adr('C1'))).toBe(true)
  expect(engine.getCellValue(adr('D1'))).toBe(true)
  expect(engine.getCellValue(adr('E1'))).toBe(new CellError(ErrorType.DIV_BY_ZERO))
})

Other requirements

An unknown Error name shall be mapped into an Error supported by the evaluator when read (e.g., the application's equivalent of #NAME?), though an evaluator may warn the user if this has or will take place. It is desirable to preserve the original specific Error name when writing an Error back out, where possible, but for Errors other than #N/A evaluators may write a different Error for a formula than they did when reading it. Whitespace shall not be included in an Error name.

Evaluators should use a human-comprehensible name, not a numeric id, for Error names they write.

[wojciechczerniak]

Might be a bug because it is already supported:

hyperformula/test/parser/error-literals.spec.ts

Lines 7 to 35 in b3752ae

	describe('Parsing error literals', () => {
	it('should parse error literals', () => {
	const parser = new ParserWithCaching(new Config(), new SheetMapping(enGB).get)
	const ast = parser.parse('=#VALUE!', CellAddress.absolute(0, 0, 0)).ast as ErrorAst
	expect(ast.type).toBe(AstNodeType.ERROR)
	expect(ast.error!.type).toEqual(ErrorType.VALUE)
	})
	it('should parse error literals with ?', () => {
	const parser = new ParserWithCaching(new Config(), new SheetMapping(enGB).get)
	const ast = parser.parse('=#NAME?', CellAddress.absolute(0, 0, 0)).ast as ErrorAst
	expect(ast.type).toBe(AstNodeType.ERROR)
	expect(ast.error!.type).toEqual(ErrorType.NAME)
	})
	it('should parse error literals with slashes', () => {
	const parser = new ParserWithCaching(new Config(), new SheetMapping(enGB).get)
	const ast = parser.parse('=#N/A', CellAddress.absolute(0, 0, 0)).ast as ErrorAst
	expect(ast.type).toBe(AstNodeType.ERROR)
	expect(ast.error!.type).toEqual(ErrorType.NA)
	})
	it('should parse error in other languages', () => {
	const parser = new ParserWithCaching(new Config({language: plPL}), new SheetMapping(plPL).get)
	const ast = parser.parse('=#ARG!', CellAddress.absolute(0, 0, 0)).ast as ErrorAst
	expect(ast.type).toBe(AstNodeType.ERROR)
	expect(ast.error!.type).toEqual(ErrorType.VALUE)
	})
	})

For some reason #DIV/0! is parsed as #NAME? which would be correct for an unknown error. On the other hand unknown error type ie. #UNKNOWN! crashes the engine with Cannot read property 'type' of undefined error.

[voodoo11]

We've parsed error literals that occurred only in formulas. Now it is done also in CellContentParser. Unknown errors are returned as #NAME? now. We can change it later, when dealing with detailed error messages.

[wojciechczerniak]

Thanks! #NAME? is the default recommended by ODFF1.3.