Create Excel compatibility page

[wojciechczerniak]

Description

Would be cool to create a page that would explain which options should be used, and which values to configure full Excel compatibility. Explain each option and why.

Numerical precision

Numerical precision, different for Gnumeric, different for Excel

precisionEpsilon
precisionRounding
smartRounding

Wildcards and RegularExpressions

Wildcards are on by default, but RegularExpressions?

matchWholeCell
useRegularExpressions
useWildcards

BLANK references

Excel evaluates references to BLANK cells as zero (0).

evaluateNullToZero: true

Leap year bug

Excel replicates Lotus 1-2-3 leap year bug, if you need this compatibility, you need to set correctly three options:

leapYear1900
nullDate
nullYear

Named Expressions

Excel has two aliases/variables: TRUE and FALSE. Those can be added to HyperFormula with Named Expressions.

hfInstance.addNamedExpression('TRUE', '=TRUE()');
hfInstance.addNamedExpression('FALSE', '=FALSE()');

Culture

There are multiple options that are based on the culture. We should propose a valid configuration for the default enGB Excel configuration.

language: 'enGB',
functionArgSeparator: ???
decimalSeparator
thousandSeparator
accentSensitive
caseFirst
caseSensitive
ignorePunctuation
localeLang: 'en',

Date and time formats

Not all date and time formats are supported out of the box. There are so many formats and they're dependant on locales that we choosed to not implement them all (bundle size). Instead we provide options to add support for whatever you need.

dateFormats
timeFormats
parseDateTime
stringifyDateTime
stringifyDuration

Spreadsheet size

Optional, this has an influence on performance and is not always necessary for full compatibility.

maxColumns
maxRows

Full config

Ready made config for copy & paste. Note that not all functions or dates may be compatible.

Links

Page does not exist yet.

Type of issue

• [x] Missing documentation
• [ ] Error in the documentation
• [ ] Request for a demo
• [x] Request for a tutorial, guide etc.
• [ ] Other issues, suggestions or ideas

[warpech]

Also, I would love to see in the documentation what wildcards are supported and exactly in what functions and expressions. Currently, wildcards are only mentioned in the documentation home page:

Support for wildcard characters

And the changelog:

support for wildcards and regex inside criterion functions like SUMIF, COUNTIF

The best way to dig for more info is by reading the tests in https://github.com/handsontable/hyperformula/pull/303/files

[izulin]

Also, I would love to see in the documentation what wildcards are supported and exactly in what functions and expressions. Currently, wildcards are only mentioned in the documentation home page:

Support for wildcard characters

And the changelog:

support for wildcards and regex inside criterion functions like SUMIF, COUNTIF

The best way to dig for more info is by reading the tests in https://github.com/handsontable/hyperformula/pull/303/files

Let me comment on it here then (since I was the one responsible for implementing wildcards)

In the wildcard mode, we're supporting only '*' as a many (possibly 0) characters, and '?' as exactly one character. In the regexp mode, we're supporting exactly JS regexp's.

[warpech]

Optionally, this could a page that includes information about compatibility with other popular spreadsheet software (Google Sheets, etc), but Excel is the most important to begin with.

The idea is to fill this page with what we know already. not about conducting a full new research.

[sequba]

AFAIK Excel also has:

useArrayArithmetic: true
ignoreWhiteSpace: 'any'

[sequba]

Closing as released in version 2.1.0.