I’m trying to use formulas here but when I start with ‘=SUM(’ and select any cell then the cell showing #ERROR! instead of ‘=SUM(A1)’ or value if A1.
Using React.JS with handsontable. here is code:JSFiddle
import React, { Component } from ‘react’;
import { HotTable } from ‘@handsontable-pro/react’;
import Handsontable from ‘handsontable-pro’;
import ‘handsontable-pro/dist/handsontable.full.css’;
class App extends Component {
constructor(props) {
super(props);
var editedRow = null;
var editedCol = null;
var firstRow = null;
var firstCol = null;
var lastRow = null;
var lastCol = null;
var result = null;
var toAdd = null;
var enteredData = null;
this.hotSettings = {
data: [[]],
minRows: 10,
minCols: 26,
colHeaders: true,
rowHeaders: true,
contextMenu: true,
minSpareRows: 1,
minSpareCols: 1,
manualColumnResize: true,
formulas: true,
selectionMode: 'multiple',
afterSelectionEnd: function (r, c, r2, c2) {
var selected = this.getSelected();
var cell = [];
for (var index = 0; index < selected.length; index += 1) {
var item = selected[index];
var startRow = Math.min(item[0], item[2]);
var endRow = Math.max(item[0], item[2]);
var startCol = Math.min(item[1], item[3]);
var endCol = Math.max(item[1], item[3]);
for (var rowIndex = startRow; rowIndex <= endRow; rowIndex += 1) {
for (var columnIndex = startCol; columnIndex <= endCol; columnIndex += 1) {
firstRow = this.getRowHeader(startRow);
firstCol = this.getColHeader(startCol);
lastRow = this.getRowHeader(endRow);
lastCol = this.getColHeader(endCol);
if ((startCol === endCol) && (startRow === endRow)) {
cell.push(firstCol + firstRow);
} else {
cell.push(firstCol + firstRow + ':' + lastCol + lastRow);
}
}
}
}
var filteredCells = cell.filter(function (item, pos) {
if(pos > 0){
return cell.indexOf(item) === pos;
}
});
toAdd = filteredCells.join();
if ((toAdd) && (enteredData && enteredData.indexOf('=') === 0 )) {
if (enteredData.indexOf('(') > -1){
enteredData = enteredData.split('(')[0]+'(';
result = enteredData+toAdd+')';
this.setDataAtCell(editedRow, editedCol, result);
}else{
enteredData = '=';
result = enteredData+toAdd;
this.setDataAtCell(editedRow, editedCol, result);
}
}
},
afterChange: function (changes, source) {
if (source === 'edit' || source === 'paste') {
enteredData = changes[0][3];
editedRow = changes[0][0];
editedCol = changes[0][1];
}
},
};
}
render() {
return (
<div>
<HotTable
id="hot"
licenseKey={"00000-00000-00000-00000-00000"}
settings={this.hotSettings}
width="100%"
height="600"
stretchH="all"
/>
</div>
);
}}
export default App;
