Excel compatibility issues

[dabreegster]

Page URL

https://hyperformula.handsontable.com/guide/compatibility-with-microsoft-excel.html

Improvement description

Hi, I'm using Hyperformula along with https://www.npmjs.com/package/exceljs to automate the use of some Excel models. My script sets input cells, then scrapes some output cells. I started from https://github.com/handsontable/hyperformula-demos/tree/develop/read-excel-file, but to get my particular XLSX working, I wound up with some changes: https://gist.github.com/dabreegster/253ab5f6e489bfbb55584d21fe428081. Along the way, I hit a few confusing differences between HF and Excel:

• INDEX with two arguments works differently. In HF, if you do INDEX(range, 123) with a 2D range (not just a column or a row), the result is an error. In Excel, seemingly it will default to using the first column or row for the unspecified dimension. So my hack was to use regex on formulas, x.replaceAll(/INDEX\(([^,]+),([^)]+)\)/g, "INDEX($1,1,$2)") to force row 1, or x.replaceAll(/INDEX\(([^,]+),([^)]+)\)/g, "INDEX($1,$2,1)") to force column 1.
• In JS, if you raise a negative number to a fractional power, you get NaN. Excel appears to ignore the negative base. So for example, Math.pow(-2, 5.1) in JS vs =(-2)^5.2. I manually hacked around this with ABS around the base, but also still preserving the negative for the final result, to match the behavior I was seeing from Excel.
• My XLSX had a named range with a key mecc2, which is an illegal name according to HF's a1CellRefRegexp. I haven't looked up the Excel spec, just a real-world case I hit.
• I had to replace uses of _xlfn.IFNA with IFNA in formulas

I would imagine fixes in HF would need to be gated on new config options. I'm not likely to have time to work on fixes, but I just wanted to report these and the workarounds, in case they're helpful to anyone else. Thanks so much for this great library!

[AMBudnik]

Thank you for bringing this up. That is a valuable feedback.

We will check it out internally and update this thread.

[sequba]

@dabreegster

• I checked the INDEX issue and it seems to work. Unless I misunderstand your point.

  it('should assume first column if no last argument', () => {
    const engine = HyperFormula.buildFromArray([
      [1, 2],
      [3, 4],
      ['=INDEX(A1:B2, 2)'],
    ])

    expect(engine.getCellValue(adr('A3'))).toEqual(3)
  })

• Excel for Windows (Version 2403, 64-bit) also gives #NUM for formula =(-2)^5.5
• Our naming rules for the named expressions follow the Open Document Standard and are already mentioned in the List of differences with other software

[dabreegster]

Thanks for confirming! I've been using LibreOffice 7.3 to work with this particular xlsx (I don't have access to Excel on Linux), so maybe the differences are actually there. It was quite a convoluted debugging process to figure out which parts of huge formulas was differing, and it's possible I didn't isolate the root causes properly.