Tutorial: How to Create a Spreadsheet using React

Learn how to build a simple Google Sheets or Excel clone using React in this tutorial. By the end of this tutorial, you’ll have a working, configurable, and reusable spreadsheet React component for all your calculations.

This tutorial covers the following topics for which I wrote dedicated guides:

React
JSX
ES6

You might want to check them out to get an introduction to these topics if you’re new to them.

First Steps

To begin with, let’s outline what we’re going to build. We’ll create a Table component that will consist of a fixed number of rows. Each row will have the same number of columns, and each column will contain a Cell component.

We’ll be able to select any cell, type any value into it, and execute formulas on those cells, effectively creating a working spreadsheet.

Build a Simple Spreadsheet

If you don’t have create-react-app installed already, install it by running the following command in your terminal:

1	npm install -g create-react-app

Then, create a new React app and start the development server:

1
2
3

npx create-react-app spreadsheet
cd spreadsheet
npm start

This will create a new React app named “spreadsheet” and start the development server on localhost:3000.

Next, replace the existing code in App.js with the following code:

import React from 'react'
import Table from './components/Table'

const App = () =>
  (<div style={{ width: 'max-content' }}>
    <Table x={4} y={4} />
  </div>)

export default App

This code sets up the basic structure of our app, including the Table component.

To create the Table component, create a new file called Table.js in the src/components directory and add the following code:

import React from 'react'
import PropTypes from 'prop-types'
import Row from './Row'

export default class Table extends React.Component {
  constructor(props) {
    super(props)

    this.state = {
      data: {},
    }
  }

  handleChangedCell = ({ x, y }, value) => {
    const modifiedData = Object.assign({}, this.state.data)
    if (!modifiedData[y]) modifiedData[y] = {}
    modifiedData[y][x] = value
    this.setState({ data: modifiedData })
  }

  updateCells = () => {
    this.forceUpdate()
  }

  render() {
    const rows = []

    for (let y = 0; y < this.props.y + 1; y += 1) {
      const rowData = this.state.data[y] || {}
      rows.push(
        <Row
          handleChangedCell={this.handleChangedCell}
          updateCells={this.updateCells}
          key={y}
          y={y}
          x={this.props.x + 1}
          rowData={rowData}
        />,
      )
    }

    return (
      <div>
        {rows}
      </div>
    )
  }
}

Table.propTypes = {
  x: PropTypes.number.isRequired,
  y: PropTypes.number.isRequired,
}

This code sets up the basic structure of the Table component and handles the changing of cell values.

Next, create a new file called Row.js in the src/components directory and add the following code:

import React from 'react'
import PropTypes from 'prop-types'
import Cell from './Cell'

const Row = (props) => {
  const cells = []
  const y = props.y
  for (let x = 0; x < props.x; x += 1) {
    cells.push(
      <Cell
        key={`${x}-${y}`}
        y={y}
        x={x}
        onChangedValue={props.handleChangedCell}
        updateCells={props.updateCells}
        value={props.rowData[x] || ''}
      />,
    )
  }
  return (
    <div>
      {cells}
    </div>
  )
}

Row.propTypes = {
  handleChangedCell: PropTypes.func.isRequired,
  updateCells: PropTypes.func.isRequired,
  x: PropTypes.number.isRequired,
  y: PropTypes.number.isRequired,
}

export default Row

This code sets up the basic structure of the Row component, which is responsible for rendering the cells in a row.

Finally, create a new file called Cell.js in the src/components directory and add the following code:

import React from 'react'
import PropTypes from 'prop-types'

export default class Cell extends React.Component {
  constructor(props) {
    super(props)
    this.state = {
      editing: false,
      value: props.value,
    }
  }

  onChange = (e) => {
    this.setState({ value: e.target.value })
  }

  onBlur = (e) => {
    this.props.onChangedValue({ x: this.props.x, y: this.props.y }, e.target.value)
    this.setState({ editing: false })
  }

  onDoubleClick = () => {
    this.setState({ editing: true })
  }

  render() {
    return (
      <div>
        {this.state.editing
          ? <input
              type="text"
              value={this.state.value}
              onChange={this.onChange}
              onBlur={this.onBlur}
            />
          : <span onDoubleClick={this.onDoubleClick}>{this.state.value}</span>}
      </div>
    )
  }
}

Cell.propTypes = {
  onChangedValue: PropTypes.func.isRequired,
  x: PropTypes.number.isRequired,
  y: PropTypes.number.isRequired,
  value: PropTypes.string.isRequired,
}

This code sets up the basic structure of the Cell component, which is responsible for rendering individual cells in a row.

Introducing Formulas

To add formula functionality to our spreadsheet, we’ll use the hot-formula-parser library. Start by installing the library by running the following command in your terminal:

1	npm install hot-formula-parser

Then, import the FormulaParser class from the library in your Table.js file:

1	import { Parser as FormulaParser } from 'hot-formula-parser'

Next, initialize the formula parser in the Table component’s constructor:

1	this.parser = new FormulaParser()

We’ll also need to add some event listeners to the formula parser to handle cell value and range value calculations. Add the following code in the Table component’s constructor:

// 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, y } = cellCoord
  const cell = this.state.data[y] && this.state.data[y][x]

  if (!cell) {
    return done("")
  }

  return done(cell)
})

// 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 { x: startX, y: startY } = startCellCoord
  const { x: endX, y: endY } = endCellCoord
  const fragment = []

  for (let y = startY; y <= endY; y += 1) {
    const row = this.state.data[y]
    if (!row) {
      continue
    }
    const colFragment = []

    for (let x = startX; x <= endX; x += 1) {
      let value = row[x] || ""
      colFragment.push(value)
    }
    fragment.push(colFragment)
  }

  return done(fragment)
})

These event listeners will allow us to handle cell value and range value calculations in formulas.

Next, update the executeFormula method in the Table component to use the formula parser and return the result of the formula calculation:

executeFormula = (cell, value) => {
  this.parser.setCell(cell)
  const res = this.parser.parse(value)

  if (res.error) {
    return "INVALID"
  }

  if (res.result.toString().match(/^=/)) {
    return this.executeFormula(cell, res.result.slice(1))
  }

  return res.result
}

This updated method uses the formula parser to parse the formula and return the result.

Finally, update the handleChangedCell method in the Table component to evaluate the cell value as a formula if it starts with an equals sign:

handleChangedCell = ({ x, y }, value) => {
  const modifiedData = Object.assign({}, this.state.data)
  if (!modifiedData[y]) modifiedData[y] = {}
  modifiedData[y][x] = value
  this.setState({ data: modifiedData })

  if (value.slice(0, 1) === "=") {
    const newValue = this.executeFormula({ x, y }, value.slice(1))
    modifiedData[y][x] = newValue
    this.setState({ data: modifiedData })
  }
}

This updated method checks if the cell value starts with an equals sign, and if it does, it evaluates the value as a formula and replaces the cell value with the result.

Improve Performance

To improve the performance of our spreadsheet, we’ll implement the shouldComponentUpdate method in the Cell component. This will prevent unnecessary re-rendering of cells that haven’t changed.

Update the Cell component as follows:

shouldComponentUpdate(nextProps, nextState) {
  if (this.props.value !== nextProps.value) {
    return true
  }

  if (this.state.editing !== nextState.editing) {
    return true
  }

  if (this.state.value !== nextState.value) {
    return true
  }

  return false
}

This updated shouldComponentUpdate method compares the current props and state with the next props and state, and returns true if any of the values have changed. Otherwise, it returns false to prevent unnecessary re-rendering.

Saving the Content of the Table

To save the content of the table in localStorage, we’ll add a componentWillUnmount method to the Table component. This method will be called when the component is about to be removed from the DOM, giving us an opportunity to save the state.

Update the Table component as follows:

1
2
3

componentWillUnmount() {
  window.localStorage.setItem("tableData", JSON.stringify(this.state.data))
}

This updated componentWillUnmount method saves the state object as JSON in localStorage using the key “tableData”.

To load the saved state when the component is mounted, add a componentDidMount method to the Table component:

componentDidMount() {
  const savedData = window.localStorage.getItem("tableData")
  if (savedData) {
    this.setState({ data: JSON.parse(savedData) })
  }
}

This componentDidMount method retrieves the saved state from localStorage and parses it from JSON into an object, which is then used to update the component’s state.

Wrapping Up

That’s it for this tutorial! You’ve learned how to create a simple spreadsheet using React. Feel free to explore more advanced functionality and customization options based on what you’ve learned here.

Don’t forget to check out my in-depth guides on React, JSX, and ES6 for more detailed information on these topics.

Tags: React, JSX, ES6, Spreadsheet, Formulas