Formula with push

Tags: #<Tag:0x00007f8b1dda6f38>
#1

HI,

We are binding json object to the spreadsheet.

var myObj= ‘[{“table”:“10”,“col”:“11”,“age”:“20”,“address”:“test”,“city”:“tvm”,“class”:“1”},{“table”:“20”,“col”:“22”}]’;

We were trying to push another object with a formula for one of the cell.

var json = ‘[{“table”:"=B1",“col”:“11”,“age”:“20”,“address”:“test”,“city”:“tvm”,“class”:“1”},{“table”:“20”,“col”:“22”}]’;

myObj.push(json)

The formula is showing as text =B1 in the spreadhseet. However if i set the formula using setDataAtCell(x,x,’=B1’), the formula is applied to the cell.

Is this a known issue or something wrong with the way i’m binding the data?

Thank you…

#2

Hey @prasanth.sivakumar

That is not as issue. We have created our own ecosystem of methods and hooks to allow users to get a full support for the CRUD operations. If you want to change the data in a cell you should always use loadData() (for more cells( or setDataAtCell for a single change.

#3

Alexandra, but if I bind an object array to the table and then add a new object with push , that will get added to the table… The issue is that if I give a formula in the object and then push it, the formula is appearing as text rather than as a formula in the table… Push still inserts a new row to the table.

Is this an expected behavior?

#4

Can you share a demo with your recent progress? I got a bit lost, and a demo would definitely help.

#5

Hi Aleksandra,

Please try this fiddle,http://jsfiddle.net/g9pjhksx/214/

To start, please click on Add Resource button. This will add three resources in the table. We have to add multiple projects under a resource. Also, all the edits to the table should be highlighted with yellow background. A user can delete projects by clicking the Delete button in each row.

I run into some issues,

  1. As you can see, i have formulas in num3 field in the source data (=B1’). The formula is appearing as text in the table. However if i uncomment the loadData() in line 74 and line 108, formula will appear properly in the table.
  2. My edits will be highlighted in yellow. But if i uncomment the loadData() in line 74 and line 108, the metadata in the cells are lost (which i know is an expected feature).
  3. Instead of using =B2 for each row, can i specify =Bn in my source data where n is the index of the current row? I can set the formula after inserting the row using setDataAtCell(), just wnated to see if i can somehow do this in the source data itself. I have many rows, setDataAtCell is slowing down the table in a big way.

How can i retain the coloring as well as the formulas in the table?

I thought of keeping the index of changed cells in an array, in the afterCreateCol and afterRemoveRow event and then add background color for the changed cells after loadData(). Is this a possible solution? Or can you recommend a better solution?

#6

Thank you for sharing the demo @prasanth.sivakumar

the data should not be manipulated by native methods like splice, splice or pop/push for arrays. Handsontable has its own ecosystem of methods and hooks. If you want to add/remove rows or columns you should use alter method.

#7

Thanks Alexandra. My data is represented as objects. In case i use alter method to insert a row, do i need to set the data using ‘setCellData’? This is killing the performance. And we have seen many examples where you push objects to the data source… what will be your recommendation to insert an object to the table?

Could you please also share your comments on the other points in my last post?

#8

Yes, if you want to fill a new row you should use setDataAtCell after the alter or you can create a mockup of data (there do anything you want, native pop/push methods) and then load it via loadData as a complete data source.

#9

Alexandra, please confirm if this is what you meany by mockup option,

If i want to add a new row (object),

  • get the current data using getSourceData to an object array, say myobjarr
  • push/splice a new object to this array
  • call loadData with myobjarr

I think setCellData will not work for us, it is taking too much time… we have 50-100 rows of data each having 70 columns. Initially we tried the alter/setCellData option, it is taking too much time…

Also, please help with the other issues mentioned in my post.

#10

Here’s a demo that should make it clear https://jsfiddle.net/handsoncode/dzjvcsxg/
It’s set to handle arrays but it works the same for objects.

I think setCellData will not work for us, it is taking too much time.

yes, if you have a lot of data to change it is better to use loadData instead.

#11

Thank you Aleksandra, could you please advise on the other points in my post (see below)

I run into some issues,

  1. As you can see, i have formulas in num3 field in the source data (=B1’). The formula is appearing as text in the table. However if i uncomment the loadData() in line 74 and line 108, formula will appear properly in the table.
  2. My edits will be highlighted in yellow. But if i uncomment the loadData() in line 74 and line 108, the metadata in the cells are lost (which i know is an expected feature).
  3. Instead of using =B2 for each row, can i specify =Bn in my source data where n is the index of the current row? I can set the formula after inserting the row using setDataAtCell(), just wnated to see if i can somehow do this in the source data itself. I have many rows, setDataAtCell is slowing down the table in a big way.

How can i retain the coloring as well as the formulas in the table?

I thought of keeping the index of changed cells in an array, in the afterCreateCol and afterRemoveRow event and then add background color for the changed cells after loadData(). Is this a possible solution? Or can you recommend a better solution?

#12

In the project, I can see hot, hot1 and hot3. Please check that and update the JSFiddle with the resent changes (without the slice method)

#13

Hi Aleksandra, updated the fiddle with the changes you suggested.

http://jsfiddle.net/a809h45r/10/

Adding the relevant questions from my previous post for your expert advise

  1. My edits will be highlighted in yellow. But if i uncomment the loadData() in line 74 and line 108, the metadata in the cells are lost (which i know is an expected feature).
  2. Instead of using =B2 for each row, can i specify =Bn in my source data where n is the index of the current row? I have many rows, setDataAtCell is slowing down the table in a big way. Right now, we are adding formula to a cell with setCellData(x,y,’=SUM(A1:A3’)’

To reapply the background color of edited cells (setmeta), we thought of keeping the index of changed cells in an array, in the afterCreateCol and afterRemoveRow event and then add background color for the changed cells after calling loadData(). Is this a good solution? Or can you recommend a different solution?

#14

Sorry, but I would need to spend more time than an hour to make it work properly, but that is a commercial service - code review.

I have simplified the code a bit http://jsfiddle.net/handsoncode/pk7gwnm8/ (use this in the afterChange hook) and fixed formatting but I cannot do anything more than that.

If you think that there is a bug the Handsontable logic please simplify the demo

#15

Hi Alexandra, I’m not looking for any code snippet. I was expecting some guidance from the support team on an efficient way of handling my requirement. Just to simplify my issues,

I insert the records and then change the background colour on each edit using setcellMeta(). However when we call LoadData to load additional data received from server, all the metadata will be lost. What is the best way to reapply the metadata?
Also, i have inserted some rows specifying the ‘source’. When i use loaddata, even the source property is lost.

#16

I cannot share any tips for a custom solution that I haven’t tried before without testing it in the first place. Without the tests and debugging I would say that.

  1. You should keep the coordinates for changes rows (to make them yellow) to retrieve the list and run the setCellMeta each time you call loadData().
  2. As mentioned before use loadData instead of setDataAtCell for larger datasets. Here http://jsfiddle.net/handsoncode/pk7gwnm8/ you can use setDataAtCell as you start with 0 rows and just keep adding one by one.
  3. Make sure that you have the same width of records for each row if you use formulas, as here https://github.com/handsontable/handsontable/issues/4430 we can see that the length of the data matters and it can produce unsolved formulas visible as strings.
#17

Thank you Aleksandra,

For the last point in your post,
Make sure that you have the same width of records for each row if you use formulas, as here https://github.com/handsontable/handsontable/issues/4430 we can see that the length of the data matters and it can produce unsolved formulas visible as strings.

when you say that the rows should have same width of records and the length of data matters, does it mean that if the data in the cellsis more, the formulas may not render properly?

#18

It is the same as mentioned here https://github.com/handsontable/handsontable/issues/4430

so it means that

A B C D
x x x
x x x =SUM(A1*2)

in the above example the formula will not appear as we dor not have data in the D column for the first row.

#19

Thank you Aleksandra… you may please close this topic. Appreciate your support.

#20

You’re welcome.

Thank you for a prompt reply.