I have handsonTable which is getting data as array of array. I applied some formulas and saved the data. now I want to load that data directly into handsonTable component in ReactJs. problem is its not showing value instead it shows formula as plain text (=sum(A:B)).
Load Data(Array of arrays) into handsontable from database also keep the formulas applied before saving
You can try use this method:
hfInstance.rebuildAndRecalculate();
In afterLoadData() hook and then additionally re-render the table.
I am unable to find rebuildAndRecalculate() in hfInstance. also there is afterLoadData prop in
{allScenarioData.map((d, index) => {
return (
<TabPanel key={index}>
<HotTable
width="100%"
height="auto"
data={d}
ref={(el) => (hotRef.current[index] = el!)}
formulas={{
engine: hyperformulaInstance,
}}
afterLoadData={()=>{hotRef.current[activeTab].hotInstance.rebuildAndRecalculate()}}
// afterFormulasValuesUpdate={afterFormulasValuesUpdate}
rowHeaders={true}
colHeaders={true}
minSpareCols={36}
maxCols={40}
fixedColumnsStart={4}
manualColumnResize={true}
manualRowMove={true}
colWidths={(index: number) => {
return index === 0
? 200
: index >= 1 && index <= 3
? 120
: 70;
}}
// prettier-ignore
columns={[
{ data: 1 }, { data: 2 }, { data: 3 }, { data: 4 }, { data: 5 }, { data: 6 }, { data: 7 }, { data: 8 }, { data: 9 }, { data: 10 },
{ data: 11 }, { data: 12 }, { data: 13 }, { data: 14 }, { data: 15 }, { data: 16 }, { data: 17 }, { data: 18 }, { data: 19 },
{ data: 20 }, { data: 21 }, { data: 22 }, { data: 23 }, { data: 24 }, { data: 25 }, { data: 26 }, { data: 27 }, { data: 28 },
{ data: 29 }, { data: 30 }, { data: 31 }, { data: 32 }, { data: 33 }, { data: 34 }, { data: 35 }, { data: 36 }, { data: 37 },
{ data: 38 }, { data: 39 }, { data: 40 }, { data: 41 },
]}
cells={(row: number, column: number) => {
if (row === 0) {
return {
readOnly: true,
className: "header-cell",
};
} else if (row >= 1 && column <= 3) {
return {
readOnly: true,
className: "nodes-data-cell",
};
} else return { readOnly: false };
}}
contextMenu={["remove_row"]}
licenseKey="non-commercial-and-evaluation" // for non-commercial use only
/>
</TabPanel>
);
})}In order for me to do a code review I would need to confirm your current support plan. Can you please send me your license ID at support@handsontable.com?
I am using non-commercial copy right now which will later be upgraded if all the requirements are met. I need a tutorial or any direction if you can give…
Can you confirm if this is the issue you are experiencing?
https://jsfiddle.net/aszymanski/y5xft4rh/
Also, can you tell me how exactly you are loading the new data?
setData([[3,5,'=sum(A1:B1)'],['=A1']])
this will not be calculated if I click the button instead formula will be recalculated if I double click cell C1 and then click somewhere else… this step calculates the value but still it will give wrong answer (i.e. C1=13), I will get correct answer only if I double click and unFocus both cells (i.e A1,B1)…
I am simply passing array of array to and when I have to reload, I fetch data from server, mix some new data on frontEnd and set the Global State (Zustand).
Thank you for the confirmation. Unfortunately, it’s a regression that comes with Handsontable 12.0 where we changed the methods responsible for loading data. It works fine with version 11.1. I already reported this issue here:
But for the time being, if that’s ok with you, I can suggest downgrading to version 11.1.
Oky, so if I downgrade to V11.1, I hope I don’t have to change code much?
There shouldn’t be any issues regarding this. However, if you’ll encounter any problems, let me know.
@adrian.szymanski @aleksandra_budnik
for me donwgrading also not working… I guess something is wrong with how I am assigning data.
"@handsontable/react": "^11.1.0", "handsontable": "^11.1.0",
on cell G2 I added formula =sum(E2:F2) . now this is working here…
When I click on New Scenario button what I am doing is copying data in scenario 2 and appending it in state as new scenario.
setAllScenarioData([
...allScenarioData,
JSON.parse(JSON.stringify(allScenarioData[allScenarioData.length - 1])),
]);
This is the data from Scenario am trying to append in state but after setting state
this is the result I get for both Scenario 2 and Scenario 2… when i double click on formula and leave the cell. then it update the cell with calculated value… i have to do this for all cells.
Even if I add Formula and change alreaady created tab and get back to the tab, it changes the data like shown in screenshot.
Can you compare your data loading method to the one I presented in my example? That would help to specify if the problem lies there.
@adrian.szymanski , It is same as yours, just setting state on button click, and looping through allScenariosData and creating new instance of HotTable for each scenario…
Above comment creating hotTable
even did
hotRef.current[activeTab].hotInstance?.updateData([
[“2017”, 10, 11, 12, 15, “=sum(C2:D2)”, “3”],
[“2018”, 10, 11, 12, 13, 15, 16],
[“2019”, 10, 11, 12, 13, 15, 16],
[“2020”, 10, 11, 12, 13, 15, 16],
[“2021”, 10, 11, 12, 13, 15, 16],
])
still its putting formula as value… even updated state with above data… thats too not working.
updateData won’t work, but loadData should. Can you try this solution if possible?
@adrian.szymanski I think I have found the issue. Its related to visibility of column.
if I skip column I face the issue I am facing… even in your working example, if I put
columns={[
{ data: 1 }, { data: 2 }, { data: 3 }, { data: 4 }, { data: 5 }, { data: 6 }, { data: 7 }, { data: 8 }, { data: 9 }, { data: 10 },
{ data: 11 }, { data: 12 }, { data: 13 }, { data: 14 }, { data: 15 }, { data: 16 }, { data: 17 }, { data: 18 }, { data: 19 },
{ data: 20 }, { data: 21 }, { data: 22 }, { data: 23 }, { data: 24 }, { data: 25 }, { data: 26 }, { data: 27 }, { data: 28 },
{ data: 29 }, { data: 30 }, { data: 31 }, { data: 32 }, { data: 33 }, { data: 34 }, { data: 35 }, { data: 36 }, { data: 37 },
{ data: 38 }, { data: 39 }, { data: 40 }, { data: 41 },
]}
notice I am skipping column 0 which contains IDs and I need them in data but not in view. Using hiddenColumns plugin is alternative but it hides full Column A and columns start from B which i don’t want…
you can check my earlier comment to see data I have (having Ids at column 0) . Can you please guide me how can I hide the column 0 but keep the data in same datasource. and grid should start from A.
Can you show me your data object? It’s not visible in the previous code snippet you send. Maybe I’ll be able to replicate it then.
Just add
columns={[
{ data: 1 }, { data: 2 }, { data: 3 }, { data: 4 }, { data: 5 }, { data: 6 }, { data: 7 }
]}
in your own example. https://jsfiddle.net/aszymanski/y5xft4rh/1/
<HotTable
data={data}
ref={hotTableComponent}
startRows={5}
startCols={5}
formulas={{engine: HyperFormula}}
height="auto"
width="auto"
columns={[
{ data: 1 }, { data: 2 }, { data: 3 }, { data: 4 }, { data: 5 }, { data: 6 }, { data: 7 }
]}
colHeaders={true}
minSpareRows={1}
licenseKey="non-commercial-and-evaluation"
/>
Rerun the example you will see formula will become value…
Ok, I got it. I think this workaround would work in this situation. You can still hide the first column by hiddenColumns and still have proper column names by defining them as a custom array and the formulas will work correctly:
No it hides the first column but formula will be calculated including hidden column…
for example: if you give this data to instance on click, =A1 will give 3 in return, which is from hidden column…
setData = [[3,5,6],[33,’=A1’]]
this workaround is just hiding column in view… formulas will consider hidden column as A.