The spreadsheet at this point is nice and all, but the real power comes from being able to execute formulas: sum values, reference other cells, and so on.
I decided to use this pretty nice library that handles Excel formulas: https://github.com/handsontable/formula-parser so we can get full compatibility with the most popular formulas for free, without having to code them ourselves.
The library seems quite actively developed, and has a good test suite so we can run the test ourselves to check if something goes wrong.
We can run npm install hot-formula-parser and then restart our app with npm start.
We did the first app dissection from top to bottom, let’s now start from the bottom.
In the Cell component, when determining the value of an item we run the determineDisplay() method:
determineDisplay = ({ x, y }, value) => {
return value
}
It’s very simple, because it is missing the bulk of the functionality. Determining the value is simple if it’s just a value, but it’s more complicated if we need to calculate the value based on a formula. A formula (in our little spreadsheet) always starts with the equal sign =, so whenever we find it as the first char of a value, we run the formula computation on it, by calling the executeFormula() method passed as one of the props of Cell:
export default class Cell extends React.Component {
//...
determineDisplay = ({ x, y }, value) => {
if (value.slice(0, 1) === '=') {
const res = this.props.executeFormula({ x, y },
value.slice(1))
if (res.error !== null) {
return 'INVALID'
}
return res.result
}
return value
}
//...
}
We get executeFormula() from our parent component, so let’s see it in Row:
const Row = (props) => {
//...
cells.push(
<Cell
key={`${x}-${y}`}
y={y}
x={x}
onChangedValue={props.handleChangedCell}
updateCells={props.updateCells}
value={props.rowData[x] || ''}
executeFormula={props.executeFormula}
/>,
)
//...
}
We’re simply passing it down from the component props to its children. Nothing complicated here. The meat of the functionality is all moved up to Table then! This is because to do anything, we must know all the state of the table, we can’t just run a formula on a cell or on a row: any formula might reference any other cell. So here is how we’ll edit Table to fit formulas:
//...
import { Parser as FormulaParser } from 'hot-formula-parser'
//...
export default class Table extends React.Component {
constructor(props) {
//...
this.parser = new FormulaParser()
// When a formula contains a cell value, this event lets us
// hook and return an error value if necessary
this.parser.on('callCellValue', (cellCoord, done) => {
const x = cellCoord.column.index + 1
const y = cellCoord.row.index + 1
// Check if I have that coordinates tuple in the table range
if (x > this.props.x || y > this.props.y) {
throw this.parser.Error(this.parser.ERROR_NOT_AVAILABLE)
}
// Check that the cell is not self referencing
if (this.parser.cell.x === x && this.parser.cell.y === y) {
throw this.parser.Error(this.parser.ERROR_REF)
}
if (!this.state.data[y] || !this.state.data[y][x]) {
return done('')
}
// All fine
return done(this.state.data[y][x])
})
// When a formula contains a range value, this event lets us
// hook and return an error value if necessary
this.parser.on('callRangeValue',
(startCellCoord, endCellCoord, done) => {
const sx = startCellCoord.column.index + 1
const sy = startCellCoord.row.index + 1
const ex = endCellCoord.column.index + 1
const ey = endCellCoord.row.index + 1
const fragment = []
for (let y = sy; y <= ey; y += 1) {
const row = this.state.data[y]
if (!row) {
continue
}
const colFragment = []
for (let x = sx; x <= ex; x += 1) {
let value = row[x]
if (!value) {
value = ''
}
if (value.slice(0, 1) === '=') {
const res = this.executeFormula({ x, y },
value.slice(1))
if (res.error) {
throw this.parser.Error(res.error)
}
value = res.result
}
colFragment.push(value)
}
fragment.push(colFragment)
}
if (fragment) {
done(fragment)
}
})
}
//...
/**
* Executes the formula on the `value` usign the
* FormulaParser object
*/
executeFormula = (cell, value) => {
this.parser.cell = cell
let res = this.parser.parse(value)
if (res.error != null) {
return res // tip: returning `res.error` shows more details
}
if (res.result.toString() === '') {
return res
}
if (res.result.toString().slice(0, 1) === '=') {
// formula points to formula
res = this.executeFormula(cell, res.result.slice(1))
}
return res
}
render() {
//...
<Row
handleChangedCell={this.handleChangedCell}
executeFormula={this.executeFormula}
updateCells={this.updateCells}
key={y}
y={y}
x={this.props.x + 1}
rowData={rowData}
/>,
//...
}
}
In the constructor we initialize the formula parser. We pass the executeFormula() method down to each Row, and when called we call our parser. The parser emits 2 events that we use to hook our table state to determine the value of specific cells (callCellValue) and the values of a range of cells (callRangeValue), e.g. =SUM(A1:A5).
The Table.executeFormula() method is building a recursive call around the parser, because if a cell has an identity function pointing to another identity function, it will resolve the functions until it gets a plain value. In this way every cell of the table can be linked to each other, but will generate an INVALID value when a circular reference is determined, because the library has a callCellValue event that allows me to hook into the Table state and raise an error if
1) the formula reference a value out of the table
2) the cell is self-referencing
The inner working of each event responder is a bit tricky to get, but don’t worry about the details, focus on how it works overall.