Handle sharedFormula cells from exceljs

[eddiesholl]

I'm trying to set up a fairly straightforward workflow using exceljs to modify a sheet, then pull it into hyperformula to achieve recalculation of formula values. Basically following the example at https://hyperformula.handsontable.com/guide/file-import.html#overview

The content is failing to be parsed on cells with a sharedFormula present. Is it right that hyperformula can't process this syntax?

I haven't been able to find anything in the exceljs docs to prevent use of shared formulas.

I was messing around with a solution that uses a parser and an unparser to shift cell references based on the offset to the master. Given that's all using private APIs it felt like a risky way to do it, I was hoping there might be a better known solution available? I've seen mention of the idea of making those APIs public but nothing beyond that, ie #854

[sequba]

Hi @eddiesholl, thanks for reaching out to us 👋

According to the ExcelJS docs, a shared formula is an optimization used by Excel to keep the xlsx files smaller and less repetitive.
If your xlsx file contains a shared formula and you want to import it to HyperFormula, you need to expand the shared formula to the entire range. The easiest way to do that is by methods getFillRangeData and setCellContents. Here is a discussion on a similar topic.

Please, let me know if this solution works for you ;)

[AlexzanderManning]

Hi @sequba I am using the import example provided in the documentation, however I am getting this error when trying to import a file with a shared formula:

It appears that the parser its breaking because the shared formula is somehow being represented as an object instead of an array.

Here is a copy of the sheet that I am using: https://docs.google.com/spreadsheets/d/1qzxPERIpxncZeQ3JtSyWS1m-9E8nStryVKuSsqDPMSY/edit?usp=sharing

You can also find it attached here: safe_sheet.xlsx

I am interested to hear your thoughts in what could be causing shared formulas to be represented as objects during import, and the steps we can take to remedy the issue.

[sequba]

Hi @AlexzanderManning!

I am sorry, it seems that our xlsx file import demo misuses the exeljs API slightly. To fix it, just modify line 29. Replace:

const cellData = cell.value.formula ? `=${cell.value.formula}` : cell.value;

with

const cellData = cell.formula ? `=${cell.formula}` : cell.value;

This should be enough to make the script work with shared formulas. I will also modify it in our documentation.

Incidentally, I noticed two more issues with your xlsx file:

1. It uses the TRUE and FALSE constants, which need to be added to your HyperFormula setup as named expressions.
2. It uses the XLOOKUP function which is not available in the current HyperFormula version, but will be added with the upcoming release as our team is already working on the implementation.

[AlexzanderManning]

Hi @AlexzanderManning!

I am sorry, it seems that our xlsx file import demo misuses the exeljs API slightly. To fix it, just modify line 29. Replace:

const cellData = cell.value.formula ? `=${cell.value.formula}` : cell.value;

with

const cellData = cell.formula ? `=${cell.formula}` : cell.value;

This should be enough to make the script work with shared formulas. I will also modify it in our documentation.

Incidentally, I noticed two more issues with your xlsx file:

1. It uses the TRUE and FALSE constants, which need to be added to your HyperFormula setup as named expressions.
2. It uses the XLOOKUP function which is not available in the current HyperFormula version, but will be added with the upcoming release as our team is already working on the implementation.

Thanks for the update @sequba ! We are excited to to use XLOOKUPS in the future! Do you have a rough idea on when the next release is going out?

[sequba]

@AlexzanderManning we plan to release HyperFormula 3.0.0 in early January

[sequba]

[Note to self] Related: https://handsoncode.slack.com/archives/C031GBX62SE/p1703083619846689 (resource not available publically)

[sequba]

The File import guide and the example code will be updated by PR: #1470 and deployed during the upcoming HyperFormula release.