Conditional formatting cell (Color and Background Color) with custom plugin

[asananddevsingh]

Hi,

I am working on a feature POC where I want to allow the user to insert a column to the handsontable and fill that with data from any existing column with some conditional formatting.

Handontable used with "non-commercial-and-evaluation" license with below versions

• "handsontable": "13.1.0",
• "@handsontable/react": "13.1.0",
• "hyperformula": "2.6.0"

I've created a custom plugin for hyperformula called as "FORMAT" whick takes 3 arguments,
e.g. =FORMAT(B1 > 100, "white", "red")

1. Condition
2. Cell Text color
3. Cell Background color

Now, I want to parse this formaula and format the cell based on it. I am unabele to understand how to control the Handsontable from Plugin. Grid example is as below:

Here is Sandbox of the my code:

e.g.

Please suggest, if it is possible to allow user to add column and apply formatting formula to it.

Thanks,
Anand Dev

[thilgen]

@asananddevsingh - I don't think you will be able to do this with a custom HyperFormula function plugin. HyperFormula is "headless" and doesn't know anything about how a host application (like Handsontable) elects to render/draw a cell or its contents.

If you are interested in getting into some internals - I worked on a feature earlier this year that allowed HyperFormula to pass a "hint" to the host application that a cell should be rendered as an HTML Link that you might find useful. The HyperFormula part has been accepted but the Handsontable code is still pending.

Issue: #1215
HyperFormula: #1216 (merged)
Handsontable: handsontable/handsontable#10314 (open)

Note You can see a bit of this in your sandbox/sample if you add a console.log(ast) - the hyperlink property on the ast object is the "hint" passed to the host application that the cell contains an HTML link and that (if the host application chooses) it can be rendered with a different color and link-click semantics.

[asananddevsingh]

Hi @thilgen,

Thank you for swift response. I appreciate your effort.

I've looked at the "Note" you suggested about "ast" and I can see its value as undefined. Now, could you please help me with --From where can I set the value to "hyperlink" flag? or how to set to set its value as a string as hit for my handsontable?

Also, is there any way available with Handsontable (without using custom plugin/hyperformula) by using which I can give the user an ability to insert column dynamically and set some color based on some condition.

Something like this example but conditionally: https://jsfiddle.net/d1ye98u7/

I am ready to buy the license too, if it's available in pro version.

[AMBudnik]

Hi @asananddevsingh

Is there any particular need to have a custom formula? If the cell color should change based on the value of the 2nd column you can use IF and some cell metadata conditionals.

Here's an example https://jsfiddle.net/9wpamzxu/

I set it up to show nothing if the condition is not met.

Edition

Outcome

[asananddevsingh]

Yes, it would be having other columns too with formulas to calculate the values.

[AMBudnik]

OK, so you're right - the custom function might be needed to extend that check from the line 13 from = to =FORMAT.

[asananddevsingh]

Thanks for response @AMBudnik.

I think the same way. Is there any callback or hook available to refresh the cell/table once custom plugin updates the cell data?

I am planning to get the details from user and set everything as string in cell and then parse that string and refresh the data in cell at handsontable level (React component/rendered level).

Also, is there any way to use HotColumn component (in react) and also allow the user to insert column?

[AMBudnik]

There's a hook called afterSetCellMeta() it is triggered once we use setCellMeta() method. Now refreshing cells could be done via render() method. you need it if you want the changes to apply visually to the table once you use setCellMeta().

[asananddevsingh]

I've tried these methods but no luck. Thanks for replies. I apprceate it.

[sequba]

In my opinion, conditional formatting should be the responsibility of the UI (hansontable or a custom one), so HyperFormula does not need any changes to make it possible.

I believe this discussion is more about handsontable API now.

[AMBudnik]

Yes, @sequba you're right.

Can you update me on the latest changes at support@handsontable.com? We'd like to close the issue here as it is not strictly Hyperformula-related

[asananddevsingh]

Yes @AMBudnik , please close this.

I am dealing with this need using Handsontable and React Js ways.

I appreciate your time and effort.