Strange preferences of Named Expression names

[YuriKirilin]

Description

Adding Named Expression by hfInstance.addNamedExpression raises a error when the name ends with a number and there are no digits in the middle.

Steps to reproduce

E.g. adding named expression "Product1" raises the error, while "Line2Product1" does not. From an end-user point of view, these names are similar, and Excel supports both of them.

Your environment

• HyperFormula version: 1.3.0

Hint

See the strange regular expression in NamedExpressions.js line 243 (isNameValid function)

[MartinDawson]

Here is the code

  public isNameValid(expressionName: string): boolean {
    if (/^[A-Za-z]+[0-9]+$/.test(expressionName)) {
      return false
    }
    return /^[A-Za-z\u00C0-\u02AF_][A-Za-z0-9\u00C0-\u02AF._]*$/.test(expressionName)
  }

In this commit: e1d91aa74ccda49b94219c4d1617e3f4c35032f7

This regex: /^[A-Za-z]+[0-9]+$/.test(expressionName)

It's been taken from here: https://docs.oasis-open.org/office/OpenDocument/v1.3/os/part4-formula/OpenDocument-v1.3-os-part4-formula.html#__RefHeading__1017964_715980110

Identifier ::= ( LetterXML

  (LetterXML | DigitXML | '_' | CombiningCharXML)* )

- ( [A-Za-z]+[0-9]+ )

- ([Tt][Rr][Uu][Ee]) - ([Ff][Aa][Ll][Ss][Ee])

The spec says this:

Expressions should limit the names of their identifiers to only ([UNICODE]) letters, underscores, and digits, not including patterns that look like cell references or the words True or False.

[A-Za-z]+[0-9]+ matches patterns such as A1, AA1, i.e cell references which are invalid according to the oasis open source spec.

Excel is actually wrong here and hyperformula is correct I think. However for compatibility reasons this probably should be fixed? Or made a config option even to allow it.

The spec seems silly in this regard though because Product1 is never mistaken for a cell reference. The regex is far too generic imo... but :shrug:

[YuriKirilin]

For me, the regex from the spec looks too inaccurate as well, and it may be amended in the next spec versions.

I guess, this is not the only point where Excel diverges from various specifications. And as a HyperFormula user, I'd prefer to have an option specifying strictness/compatibility mode to be able to use my existing datasheets with as few modifications as possible. At least, this discrepancy should be accented somewhere in the HF documentation.

[warpech]

Thanks for your points of view. We will investigate the options. Maybe we can apply some form of Postel's law here. Or at least explain the discrepancy better, as @YuriKirilin notes.

[sequba]

This bug is closely related to #1058. Both issues will be addressed in pull request #1130

[sequba]

@YuriKirilin,

I'm more than happy to announce that we just released HyperFormula 2.3.1 where this issue is fixed. We are closing this issue as solved. If there is anything that won't work for you after updating, please leave a comment.