Import Spreadsheets or Excel in your React Component

05 Jul, 2019

Working with Excel in React can be a pain or a pleasure. We’ll be importing Excel Sheet into React, display the data in an editable, so the user can preview or make changes, then, we submit to the server.

Tools

We will be using Create React App, AntD and react-excel-renderer

Run the following commands one after the other to create a new App, Install dependencies and start your App

npx create-react-app excel-app
cd excel-app
npm install antd react-excel-renderer
npm start

In src directory, locate App.js and edit the code as follows

import React from "react"
import ReactDOM from "react-dom"
import "antd/dist/antd.css"
import ExcelPage from "./components/excelPage"

function App() {
  return (
    <>
      <ExcelPage />
    </>
  )
}
const rootElement = document.getElementById("root")
ReactDOM.render(<App />, rootElement)

In the above code, we import antd’s styling, and the component we are about to create, ExcelPage. Then, we render it.

This code will show up as error currently because we are yet to create the component. Let’s fix that.

Create a folder components in the src directory. Create a file excelPage.js in src/components

Open the file excelPage.js and edit as follows:

import React, { Component } from "react"
import { Table, Button, Popconfirm, Row, Col, Icon, Upload } from "antd"
import { ExcelRenderer } from "react-excel-renderer"

export default class ExcelPage extends Component {
  render() {
    return (
      <>
        <h1>Importing Excel Component</h1>
      </>
    )
  }
}

In the above code, we create the component, ExcelPage, import antd components to be used and the ExcelRenderer from react-excel-renderer

Next, we’ll create editable components from antd.

Create a folder utils in src directory. Create editable.js in src/utils

Type in (or copy/paste) the following code in editable.js

import React from "react"
import { Form, Input } from "antd"

const EditableContext = React.createContext()

const EditableRow = ({ form, index, ...props }) => (
  <EditableContext.Provider value={form}>
    <tr {...props} />
  </EditableContext.Provider>
)

export const EditableFormRow = Form.create()(EditableRow)

export class EditableCell extends React.Component {
  state = {
    editing: false,
  }

  toggleEdit = () => {
    const editing = !this.state.editing
    this.setState({ editing }, () => {
      if (editing) {
        this.input.focus()
      }
    })
  }

  save = e => {
    const { record, handleSave } = this.props
    this.form.validateFields((error, values) => {
      if (error && error[e.currentTarget.id]) {
        return
      }
      this.toggleEdit()
      handleSave({ ...record, ...values })
    })
  }

  renderCell = form => {
    this.form = form
    const { children, dataIndex, record, title } = this.props
    const { editing } = this.state
    return editing ? (
      <Form.Item style={{ margin: 0 }}>
        {form.getFieldDecorator(dataIndex, {
          rules: [
            {
              required: true,
              message: `${title} is required.`,
            },
          ],
          initialValue: record[dataIndex],
        })(
          <Input
            ref={node => (this.input = node)}
            onPressEnter={this.save}
            onBlur={this.save}
          />
        )}
      </Form.Item>
    ) : (
      <div
        className="editable-cell-value-wrap"
        style={{ paddingRight: 24, minHeight: 32 }}
        onClick={this.toggleEdit}
      >
        {children}
      </div>
    )
  }

  render() {
    const {
      editable,
      dataIndex,
      title,
      record,
      index,
      handleSave,
      children,
      ...restProps
    } = this.props
    return (
      <td {...restProps}>
        {editable ? (
          <EditableContext.Consumer>{this.renderCell}</EditableContext.Consumer>
        ) : (
          children
        )}
      </td>
    )
  }
}

It’s a utility class for having editable table cells and rows. Code and documentation can be found on Antd’s documentation

Next, we go to excelPage.js in src/components Import the editable cell and editable row utilities:

//...
import { EditableFormRow, EditableCell } from "../utils/editable"
//...

Add state object for table header information inside src/components/excelPage.js before the render function:

constructor(props) {
    super(props);
    this.state = {
      cols: [],
      rows: [],
      errorMessage: null,
      columns: [
        {
          title: "NAME",
          dataIndex: "name",
          editable: true
        },
        {
          title: "AGE",
          dataIndex: "age",
          editable: true
        },
        {
          title: "GENDER",
          dataIndex: "gender",
          editable: true
        },
        {
          title: "Action",
          dataIndex: "action",
          render: (text, record) =>
            this.state.rows.length >= 1 ? (
              <Popconfirm
                title="Sure to delete?"
                onConfirm={() => this.handleDelete(record.key)}
              >
                <Icon
                  type="delete"
                  theme="filled"
                  style={{ color: "red", fontSize: "20px" }}
                />
              </Popconfirm>
            ) : null
        }
      ]
    };
  }

The above code adds the table header and action to state

Next, we add a function to handle saving when editing the rows:

handleSave = row => {
  const newData = [...this.state.rows]
  const index = newData.findIndex(item => row.key === item.key)
  const item = newData[index]
  newData.splice(index, 1, {
    ...item,
    ...row,
  })
  this.setState({ rows: newData })
}

Let’s add a function after handleSave that validates that we are only adding excel file

checkFile(file) {
    let errorMessage = "";
    if (!file || !file[0]) {
      return;
    }
    const isExcel =
      file[0].type === "application/vnd.ms-excel" ||
      file[0].type ===
        "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    if (!isExcel) {
      errorMessage = "You can only upload Excel file!";
    }
    console.log("file", file[0].type);
    const isLt2M = file[0].size / 1024 / 1024 < 2;
    if (!isLt2M) {
      errorMessage = "File must be smaller than 2MB!";
    }
    console.log("errorMessage", errorMessage);
    return errorMessage;
  }

Now, let’s add the function that handles the actual excel file processing, after checkFile

fileHandler = fileList => {
  console.log("fileList", fileList)
  let fileObj = fileList
  if (!fileObj) {
    this.setState({
      errorMessage: "No file uploaded!",
    })
    return false
  }
  console.log("fileObj.type:", fileObj.type)
  if (
    !(
      fileObj.type === "application/vnd.ms-excel" ||
      fileObj.type ===
        "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
    )
  ) {
    this.setState({
      errorMessage: "Unknown file format. Only Excel files are uploaded!",
    })
    return false
  }
  //just pass the fileObj as parameter
  ExcelRenderer(fileObj, (err, resp) => {
    if (err) {
      console.log(err)
    } else {
      let newRows = []
      resp.rows.slice(1).map((row, index) => {
        if (row && row !== "undefined") {
          newRows.push({
            key: index,
            name: row[0],
            age: row[1],
            gender: row[2],
          })
        }
      })
      if (newRows.length === 0) {
        this.setState({
          errorMessage: "No data found in file!",
        })
        return false
      } else {
        this.setState({
          cols: resp.cols,
          rows: newRows,
          errorMessage: null,
        })
      }
    }
  })
  return false
}

In case you’re willing to send this to a server on submission, the handleSubmit method can be added after fileHandler

handleSubmit = async () => {
  console.log("submitting: ", this.state.rows)
  //submit to API
  //if successful, banigate and clear the data
  //this.setState({ rows: [] })
}

Next, we need functions that can remove rows and add new rows. We’ll implement that below:

handleDelete = key => {
  const rows = [...this.state.rows]
  this.setState({ rows: rows.filter(item => item.key !== key) })
}
handleAdd = () => {
  const { count, rows } = this.state
  const newData = {
    key: count,
    name: "User's name",
    age: "22",
    gender: "Female",
  }
  this.setState({
    rows: [newData, ...rows],
    count: count + 1,
  })
}

Within the render function, before the return function, let’s add:

const components = {
  body: {
    row: EditableFormRow,
    cell: EditableCell,
  },
}
const columns = this.state.columns.map(col => {
  if (!col.editable) {
    return col
  }
  return {
    ...col,
    onCell: record => ({
      record,
      editable: col.editable,
      dataIndex: col.dataIndex,
      title: col.title,
      handleSave: this.handleSave,
    }),
  }
})

Next, add the Upload button after the h1 within return inside render function:

<Row gutter={16} justify="space-between">
  <Col
    span={8}
    style={{
      display: "flex",
      justifyContent: "space-between",
      alignItems: "center",
      marginBottom: "5%",
    }}
  >
    <div style={{ display: "flex", alignItems: "center" }}>
      <div className="page-title">Upload Farmer Data</div>
    </div>
  </Col>
  <Col span={8}>
    <a
      href="https://res.cloudinary.com/bryta/raw/upload/v1562751445/Sample_Excel_Sheet_muxx6s.xlsx"
      target="_blank"
      rel="noopener noreferrer"
      download
    >
      Sample excel sheet
    </a>
  </Col>
  <Col
    span={8}
    align="right"
    style={{ display: "flex", justifyContent: "space-between" }}
  >
    {this.state.rows.length > 0 && (
      <>
        <Button
          onClick={this.handleAdd}
          size="large"
          type="info"
          style={{ marginBottom: 16 }}
        >
          <Icon type="plus" />
          Add a row
        </Button>{" "}
        <Button
          onClick={this.handleSubmit}
          size="large"
          type="primary"
          style={{ marginBottom: 16, marginLeft: 10 }}
        >
          Submit Data
        </Button>
      </>
    )}
  </Col>
</Row>

The above code simply shows the page title and a Submit button that only shows on condition that there’s data already imported.

Next, we add the actual upload button after the Row component:

<div>
  <Upload
    name="file"
    beforeUpload={this.fileHandler}
    onRemove={() => this.setState({ rows: [] })}
    multiple={false}
  >
    <Button>
      <Icon type="upload" /> Click to Upload Excel File
    </Button>
  </Upload>
</div>

Next, we add the editable Table component right after the previously added div:

<div style={{ marginTop: 20 }}>
  <Table
    components={components}
    rowClassName={() => "editable-row"}
    dataSource={this.state.rows}
    columns={columns}
  />
</div>