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.
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>
Check out the Github repo
Working sample on Codesandbox
Subscribe to my newsletter and stay updated.