Hello,
I would like to populate cells by autofill which I would like to change value to new one (for example multiply 2 times). My idea was to use getDataCell and then setDataCell but it populates with first value only.
Example at (e.g. please copy value in A1 by autofill to down):
https://jsfiddle.net/janzitniak/3ynpkg1q/9/
Can you give me advice please?
Regards,
Jan
Hey @jan.zitniak
our autofill only populates the same values, it does not change them anyhow. I was thinking about doing the same (with the multiplication) and I think that it should be done by using map to multiply the values in the beforeAutofill hook. However, there’s no option to use return to change those values in the beforeAutofill hook.
So the beforeChange callback’s the one which has to do the job. Here https://handsontable.com/docs/7.4.2/Hooks.html#event:beforeChange you can see that by returning
changes[0][3] = 10;
you change whatever was the new value to 10. To avoid calling your code for each change you should add IF for the source Autofill.fill as this is a result of the 2nd parameter of the beforeChange call.
I keep my fingers crossed, and would love to get some feedback on the progress.
Dear @aleksandra_budnik,
thank you for you quick response and ideas. I still have no idea how to do it. I added beforeAutofill hook but it changes only one cell not all in autofilled range.
Example at https://jsfiddle.net/janzitniak/3ynpkg1q/41/
Regards
Jan
I mentioned the beforeAutofill hook but, after some investigation I think that the beforeChange should be better, as it gives you an ability to change mutliple cells.
Example
Action
Result
In the following example you changed 2 cells on indexes [2, 0] and [3, 0], the changes array that is returned by the beforeChange hook, allows you to compare those values and converts them to something else.
If you use
changes[0][3] = 10;
changes[1][3] = 10;
in the beforeChange, the result is following
Thank you @aleksandra_budnik for your advice, now I understand. It works as I expected but how can I insert formula via changes because I used:
changes[0][3] = "=SUM(B3:E3)";
changes[1][3] = "=SUM(B4:E4)";
and it doesn’t pass as formula (it shows as text only).
https://jsfiddle.net/janzitniak/3ynpkg1q/50/
It looks like the formulas do not recalculate… I will investigate the subject, but surely will need to report it as a bug.
Hey @jan.zitniak
I wasn’t able to work around this issue so I needed to report it officially https://github.com/handsontable/handsontable/issues/6932
I will inform you once it’s done.
In the meanwhile, maybe you’d be able to check coordinates of newly changed cells. Then use them to call setDataAtCell. Those are two different operations so the renderer() is called more than once but that will happen in a split of a second so the user’s eye won’t notice a difference.
@aleksandra_budnik thank you for your help, willingness and next tip. I will wait for fixing that issue.
Dear @aleksandra_budnik,
I would like to work around this issue with afterChange.
I found your advice at Handsontable afterChange, do calculation, setdatacell stuck in loop where you mentioned your jsfiddle example at http://jsfiddle.net/fs2nspys/.
I would like to use same principle but if I use 2 or more setDataAtCell it also will invoke infinite loops. My example at http://jsfiddle.net/janzitniak/qetv0f9y/5/
Regards
Jan
Yes, you need to add the 3rd parameter to the setDataAtCell (the source of change).
Thank you @aleksandra_budnik for you advice, it helped me. For somebody can help right example available at http://jsfiddle.net/janzitniak/qetv0f9y/9/
Jan
I’m glad to hear it works for you.
I will update you once the https://github.com/handsontable/handsontable/issues/6932 is officially fixed.
Dear @aleksandra_budnik,
is it possible directly in afterChange hook to know which rows and columns are affected by autofill? And then to manipulate with them via setDataAtCell?
Kind regards
Jan
Great, thank you for update.
Hi @aleksandra_budnik, Have you fixed that issue ?
because i am still getting formulas in oldVal and newVal of changes array of afterChange hook.
Hi @sumit.baudh the https://github.com/handsontable/handsontable/issues/6932 is still open. I have requested to add it to the scope of the next release, but we have already closed the milestone. I will notify you once we solve it.
I am happy to announce that we fixed the following issue in v13.0.0.
Here Handsontable Release Notes you can read more about the changes.
