Retrieve data after applying formula

Tags: #<Tag:0x00007efc615074b8>
#1

So, I’m initiating an table with this
hot = new Handsontable(document.getElementById(“table”), {
data: data,
columns: [{ type: “numeric” },{ type: “numeric” },{ type: “numeric” }],
rowHeaders: true,
colHeaders: true,
contextMenu: true,
formulas:true,
});
Lets say
data[0] = [1,2,3]

Now if I apply a formula in C1 cell say B1+10, and the I print the variable it shows as
data[0] = [1,2,"B1+10"]
But it should be
data[0] = [1,2,12]
and I want to do calculation with it. So, how do I get the actual number instead of the formula as string?

#2

Hey @koushik.naskar9

are you missing the = sign? It works here https://jsfiddle.net/handsoncode/7ahrn89z/

#3

Hi @aleksandra_budnik

In this example, https://jsfiddle.net/handsoncode/7ahrn89z/, double-clicking on column C shows “=B1+10”. But is it possible to show the actual value itself which is “12” instead of formula?

I am setting the formula dynamically to a set of columns using the setDataAtCell method, but after the table has rendered on double click I want the actual value to be shown in the cell instead of formula. Is there any way to achieve this?

Thanks in Advance.

#4

Sorry I didn’t put the = sign in my question. Let me modify your example to show, what I want. Lets start the table
data = [[1,2,"=B1+10"]] var hot = new Handsontable(document.getElementById("table"), { data: data, columns: [{ type: "numeric" }, { type: "numeric" }, { type: "numeric" }], rowHeaders: true, colHeaders: true, contextMenu: true, formulas: true, afterChange : function(){console.log(data)} });

Now in C1 instead of =B1+10 put =B1+13, it shows 15 in C1 but in console it prints [[1,2,"=B1+13"]], but I wanted [[1,2,15]], so that I can do further numerical calculation with the data. But instead of number it’s giving me a string.

#5

Hi @koushik.naskar9

In “console.log(data)” you are trying to print the same data that you have initialized at the beginning which is at line number one.

Instead of logging the data which is defined at line one. Just try to get the data from the hot table instance.

Please try the below snippet which prints the data as [1,2,12].

data = [[1,2,"=B1+10"]]
var hot = new Handsontable(document.getElementById(“table”), {
data: data,
columns: [{
type: “numeric”
}, {
type: “numeric”
}, {
type: “numeric”
}],
rowHeaders: true,
colHeaders: true,
contextMenu: true,
formulas: true,
});
console.log(hot.getData());

#6

Hey @rafi.rob231 @koushik.naskar9

I see that there’s more update since I visited this topic.

@rafi.rob231 I have updated the formula topic that you’ve created separately
@koushik.naskar9 what is your current progress?

#7

Well. I was using this example from here http://handsontable.github.io/handsontable-ruleJS/. There apparently you can drag and copy the formula and it works just as like excel sheet. But getData() method doesn’t work and doesn’t return the data array (returns formulas as string as in my question). I suppose that is some old example? But with the newer version the getData() method works but the drag and copy formula does’t

#8

We do not support RuleJS in the latest Handsontable.
That library has been made before formula-parser and there are no tests for using this two libraries together with newer versions.
the drag & drop is on our to-do list but it hasn’t been scheduled yet.

#9

Thanks @aleksandra_budnik. Will be waiting for the feature.

#10