What's the best way to migrate from hot-parser-formula to hyperformula?

[alvarovillafane]

Hi,

I'm migrating from using hot-parser-formula to hyperformula.
I found most of the things work fine, except dates which are a totally different API.

As my change needs to be backwards compatible, the only solution I found so far is to replace the date functions with custom functions that emulate the previous functionality.
This is a big headache, so I was wondering if there is a known path to do this migration.

Also, I would like to invoke in-built functions in custom functions to achieve what I mentioned above. Is this possible?
If so, can someone provide an example?

Thanks!

[sequba]

Hi @vmalvaro, thanks for reaching out to us.

Migrating from hot-parser-formula to HyperFormula

There is no official migration guide, but we do have a guide about customization of the date/time features which might be helpful to you. Also, if you had any specific "How to do X?" questions, I'd be happy to help. I believe some of the incompatibility issues might be solved by HyperFormula's configuration and do not require writing a custom function.

Calling a built-in function from custom functions

Technically it is possible but I wouldn't recommend that.

First, check out our Custom Function guide and the HyperFormula configuration options to see if there is no other way of achieving the desired behavior.

Then, you can look at our helper classes:

• DateTimeHelper
• ArithmeticHelper
• SearchStrategy

They are private and undocumented but they can be accessed from the FunctionPlugin class and their APIs are certainly more convenient to use than calling another built-in function.

If you decided you need to call a built-in function, you can do it according to this example. I created a custom function GREET() that internally calls a built-in function CONCATENATE(). But be warned that it is not an official feature. It has some important limitations and it uses private methods that may be changed anytime without prior notice.

[alvarovillafane]

Hi @sequba ,

Thanks for your comment. I reviewed the docs but still not sure how to achieve this via configuration.
There is no official migration guide, but we do have a [guide about customization of the date/time features which might be helpful to you](https://hyperformula.handsontable.com/guide/date-and-time-handling.html). Also, if you had any specific "How to do X?" questions, I'd be happy to help. I believe some of the incompatibility issues might be solved by HyperFormula's configuration and do not require writing a custom function.

Would you mind giving me some examples of how to do the conversion of the formulas below? this would give me a better idea and probably can figure out the rest. I need hyperformula to return what was returning and accepting hot-parse-formula

TODAY()

• hot-parse-formula returns a Date
• hyperformula returns a number

Expected return: Date

DATE(year, month, day)

• hot-parse-formula returns a Date
• hyperformula returns a number

Expected return: Date

DAYS, EDATE, YEAR, MONTH or any Function that accepts a date as a parameter

• hot-parse-formula I can have a DATE in ISOstring format (2023-10-19T13:00:00.000Z) as a cell value and it calculates fine
• hyperformula It throws #VALUE when a cell has a date as a ISOString value

To fix this I create a custom function and use the hot-formula-parser to return the value.
Not sure what I'm missing here. Or do you know an alternative to make the formula work when passing a date in a ISOString format as a parameter?

Let me know if you need further clarification.

Thanks for your help!

[sequba]

1. Return a date value from the function

HyperFormula is designed to follow the OpenFormula standard, which specifies that date should be represented as a number of days since nullDate. Currently, it is not possible to customize this behavior.

Depending on your use case and the architecture of your application, you may:

• Convert numbers to Date objects outside HyperFormula after running the calculations. You may detect the cells that contain dates by calling getCellValueDetailedType on them.
• Create custom functions that return a Date object instead of a number. You may use DateTimeHelper in your implementations. Note, that you wouldn't be able to combine such functions with other built-in functions as they cannot operate on Date objects.
• If returning dates as strings is an option for you, you may combine functions like NOW() and DATE() with function TEXT() to obtain the calculation results as formatted strings.

2. Passing a date value as a parameter

By default, HyperFormula does not interpret ISO strings as dates, but it can be configured to support any date/time format by providing a custom parseDateTime method. All functions that accept dates as parameters use this method internally, so you wouldn't need to overwrite them with custom functions. I recommend implementing parseDateTime using Moment.js lib, as described in our guide.

@vmalvaro, please, let me know if these ideas were helpful.

[DmitryB11]

Hi @sequba ,

I'm trying to migrate too but I don't understand where to start?
I have in old project this import

var FormulaParser = require('hot-formula-parser').Parser;
var parser = new FormulaParser(); 

After I set variables like this:
parser.setVariable('V', V1);

And after I replace this variable in my formula:

let res1 = this.state.Formula1;
res1 = res1.replace(/V1/g, "V");

How I can do it now with hyperformula?

[sequba]

Hi @DmitryB11, thanks for reaching out. Unfortunately, I do not know the API of hot-formula-parser. Can you ask your question in the form of "How to do X using HyperFormula"?

[DmitryB11]

Hi @sequba I will try to ask my question in your template:
How to use a formula in a string like "A+B" and replace A with var A1 and B with var B1 in HyperFormula?

[DmitryB11]

Hi Kuba, I will write here and also repit it on GitHub so others can benefit from this too. I will try to ask my question in your template: How to use a formula in a string like "A+B" and replace A with var A1 and B with var B1 in HyperFormula? Thank you, Dmitry.

…

On Mon, Jan 23, 2023 at 4:25 PM Kuba Sekowski ***@***.***> wrote: Hi @DmitryB11 <https://github.com/DmitryB11>, thanks for reaching out. Unfortunately, I do not know the API of hot-formula-parser. Can you ask your question in the form of *"How to do X in HyperFormula"*? — Reply to this email directly, view it on GitHub <#1142 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AMWDQBIZX57AYMPTWVXHYY3WTZFAJANCNFSM6AAAAAATZ7WK2A> . You are receiving this because you were mentioned.Message ID: ***@***.*** com>

[sequba]

@DmitryB11 I'm not sure what exactly you mean by "replace". In HyperFormula we have a feature called named expressions which work similarly to variables.

You can define named expressions A and B, and whenever you use them in a formula they will be substituted for their values:

const hf = HyperFormula.buildFromArray(
  [[0, "=A+B"]],
  { licenseKey: "gpl-v3" },
);

hf.addNamedExpression("A", "=42");
hf.addNamedExpression("B", "=Sheet1!$A$1");

console.log(hf.getCellValue({ sheet: 0, row: 0, col: 1 }));

hf.changeNamedExpression("A", "=4242");

console.log(hf.getCellValue({ sheet: 0, row: 0, col: 1 }));

[DmitryB11]

Hi, Thank you for the answer, I'll try. Best regards, Dmitry.

…

On Tue, Jan 24, 2023 at 3:16 PM Kuba Sekowski ***@***.***> wrote: @DmitryB11 <https://github.com/DmitryB11> I'm not sure what exactly you mean by "replace". In HyperFormula we have a feature called named expressions <https://hyperformula.handsontable.com/guide/named-expressions.html#demo> which work similarly to variables. You can define named expressions A and B, and whenever you use them in a formula they will be substituted for their values: const hf = HyperFormula.buildFromArray( [[0, "=A+B"]], { licenseKey: "gpl-v3" },); hf.addNamedExpression("A", "=42");hf.addNamedExpression("B", "=Sheet1!$A$1"); console.log(hf.getCellValue({ sheet: 0, row: 0, col: 1 })); hf.changeNamedExpression("A", "=4242"); console.log(hf.getCellValue({ sheet: 0, row: 0, col: 1 })); — Reply to this email directly, view it on GitHub <#1142 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AMWDQBL6CBLE7HRWCXZYL3LWT6FUXANCNFSM6AAAAAATZ7WK2A> . You are receiving this because you were mentioned.Message ID: ***@***.*** com>

[DmitryB11]

Hi, I'm trying to understand how the HyperFormula is working. I need it for my react native app and I don't understand what sheet: 0, row: 0, col: 1 means? console.log(hf.getCellValue({ sheet: 0, row: 0, col: 1 })); I don't have any tables in my app. How can I get the value? Thank you, Dmitry.

…

On Tue, Jan 24, 2023 at 3:16 PM Kuba Sekowski ***@***.***> wrote: @DmitryB11 <https://github.com/DmitryB11> I'm not sure what exactly you mean by "replace". In HyperFormula we have a feature called named expressions <https://hyperformula.handsontable.com/guide/named-expressions.html#demo> which work similarly to variables. You can define named expressions A and B, and whenever you use them in a formula they will be substituted for their values: const hf = HyperFormula.buildFromArray( [[0, "=A+B"]], { licenseKey: "gpl-v3" },); hf.addNamedExpression("A", "=42");hf.addNamedExpression("B", "=Sheet1!$A$1"); console.log(hf.getCellValue({ sheet: 0, row: 0, col: 1 })); hf.changeNamedExpression("A", "=4242"); console.log(hf.getCellValue({ sheet: 0, row: 0, col: 1 })); — Reply to this email directly, view it on GitHub <#1142 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AMWDQBL6CBLE7HRWCXZYL3LWT6FUXANCNFSM6AAAAAATZ7WK2A> . You are receiving this because you were mentioned.Message ID: ***@***.*** com>

[sequba]

@DmitryB11 HyperFormula is a spreadsheet calculation engine. Its internal state is essentially a data table similar to a Microsoft Excel document.

I encourage you to check out our basic guides:

• Intro
• Basic usage
• Cell references

[DmitryB11]

Thank you

…

On Thu, Jan 26, 2023 at 6:23 PM Kuba Sekowski ***@***.***> wrote: @DmitryB11 <https://github.com/DmitryB11> HyperFormula is a *spreadsheet* calculation engine. Its internal state is essentially a data table similar to a Microsoft Excel document. I encourage you to check out our basic guides: - Intro <https://hyperformula.handsontable.com/> - Basic usage <https://hyperformula.handsontable.com/guide/basic-usage.html> - Cell references <https://hyperformula.handsontable.com/guide/cell-references.html#relative-references> — Reply to this email directly, view it on GitHub <#1142 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AMWDQBI62LJ2SBJ2OAEIELLWUJNEPANCNFSM6AAAAAATZ7WK2A> . You are receiving this because you were mentioned.Message ID: ***@***.*** com>