Problem:
I have a handsontable instance using HyperFormula as the formula engine. Most of our cells are readonly and calculate values based on the values of other sheets/other cell of this table. However, for some cells, I want the user to be able to input anything. For these free input cells, we don’t want to allow the user to use any kind of formulas, not even simple ones like “=10+20“ (for example). Considering that, I want to disable formulas for some specific cells.
What I have tried:
beforeChange hook: One of the things I’ve tried is intercepting the change event with the beforeChange hook. If the value of the cell starts with “=”, I just return false. The problem with this is that, ideally, I’d want to keep the value, but as plain text. That is, HyperFormula shouldn’t calculate this at all.
Custom editor: I have tried to setValue manually from a custom editor, but it seems like HyperFormula still evaluates the formula.
Manually changing content: Another thing I’ve tried is manually changing the content. If the user inputs a formula, I’d add a ’character at the beginning of the text. This way, the formula would be “escaped”. The problem with this approach is, 1. we would be modifying the content and 2. the ’ would be shown on the editor.
Is there a way to disable formulas for specific cells?
Hi @douglaspigoulart
If you would like to visually show that the cell does not count the formula, you could use the custom cell renderer for those cells.
Here Handsontable example - JSFiddle - Code Playground is an example when in a custom rendered, I am setting up the value to be the cell’s getSourceDataAtCell() result.
Result
In general, there is no API to control whether a given cell should be a formula (so it will be calculated). If you could share a bit more details about the calculation that is taking place, maybe we can figure out an IF to remove those cells from the equation.
Interesting example @aleksandra_budnik, thanks for that. This kind of works for my case, but it would still evaluate the formula under the hood.
Evaluating the formula under the hood is not such a big problem for me because I’m going to send the source data to the back-end anyway. The problem is that, if we did something like =D1\*E1 (D1 being the cell that “shows no formula”), D1 here would still be a number calculated from the formula =C1*0.7, so =D1\*E1 would be: 4900 times 0.5 (4900 being the result of C1 times 0.7).
Is there any way to get around this?
Hm.. as there is no API to disable the formula for a given cell, I think the best workaround in this case would be to use beforeChange hook, Within that hook you can identify if a “restricted” cell is being referenced, and you can change the value before it is rendered.
@aleksandra_budnik Thanks for you response. We’ll use beforeChange + use handsontable separated from hyperformula to have more control over actions. This way, we can calculate formulas only for what we need.
1 Like
