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.
Related Content
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:
npm install -g create-react-app
Then, create a new React app and start the development server:
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:
npm install hot-formula-parser
Then, import the FormulaParser
class from the library in your Table.js
file:
import { Parser as FormulaParser } from 'hot-formula-parser'
Next, initialize the formula parser in the Table
component’s constructor:
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:
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