<-My Blog->

In this article, we gonna learn how to import and export excel and csv in react. 

1. Let's install create-react-app package.

npm i -g create-react-app

2. Create a new react project

create-react-app react-excel-csv

cd react-excel-csv

3. Install xlsx npm package.

npm i xlsx

xlsx package provides a bunch of functions for reading and writing CSV/Excel files.

Parsing functions:-

      XLSX.read(data, read_opts) attempts to parse data

      XLSX.readFile(filename, read_opts) attempts to read filename and parse.

Note:- you can pass raw option to false if you want formatted data. (example - formatted date)

XLSX.read(data, { raw: false })
XLSX.readFile(filename, { raw: false })

Writing functions:-

      XLSX.write(wb, write_opts) attempts to write the workbook

      XLSX.writeFile(wb, filename, write_opts) attempts to write workbook

Utility Functions:-

      Constructing:-

            book_new creates an empty workbook

            book_append_sheet adds a worksheet to a workbook

       Importing:

           aoa_to_sheet converts an array of arrays of JS data to a worksheet

           json_to_sheet converts an array of JS objects to a worksheet

          sheet_add_aoa adds an array of arrays of JS data to an existing worksheet.

          sheet_add_json adds an array of JS objects to an existing worksheet.

    Exporting:

          sheet_to_json converts a worksheet object to an array of JSON objects.

          sheet_to_csv generates delimiter-separated-values output.

Ref:- https://www.npmjs.com/package/xlsx

4. Create folder components inside the src folder. Inside this folder create home.component.jsx file.

import React, { useState } from "react";  
import { read, utils, writeFile } from 'xlsx';

const HomeComponent = () => {
    const [movies, setMovies] = useState([]);

    const handleImport = ($event) => {
        const files = $event.target.files;
        if (files.length) {
            const file = files[0];
            const reader = new FileReader();
            reader.onload = (event) => {
                const wb = read(event.target.result);
                const sheets = wb.SheetNames;

                if (sheets.length) {
                    const rows = utils.sheet_to_json(wb.Sheets[sheets[0]]);
                    setMovies(rows)
                }
            }
            reader.readAsArrayBuffer(file);
        }
    }

    const handleExport = () => {
        const headings = [[
            'Movie',
            'Category',
            'Director',
            'Rating'
        ]];
        const wb = utils.book_new();
        const ws = utils.json_to_sheet([]);
        utils.sheet_add_aoa(ws, headings);
        utils.sheet_add_json(ws, movies, { origin: 'A2', skipHeader: true });
        utils.book_append_sheet(wb, ws, 'Report');
        writeFile(wb, 'Movie Report.xlsx');
    }

    return (
        <>
            <div className="row mb-2 mt-5">
                <div className="col-sm-6 offset-3">
                    <div className="row">
                        <div className="col-md-6">
                            <div className="input-group">
                                <div className="custom-file">
                                    <input type="file" name="file" className="custom-file-input" id="inputGroupFile" required onChange={handleImport}
                                        accept=".csv, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, application/vnd.ms-excel"/>
                                    <label className="custom-file-label" htmlFor="inputGroupFile">Choose file</label>
                                </div>
                            </div>
                        </div>
                        <div className="col-md-6">
                            <button onClick={handleExport} className="btn btn-primary float-right">
                                Export <i className="fa fa-download"></i>
                            </button>
                        </div>
                    </div>
                </div>
            </div>
            <div className="row">
                <div className="col-sm-6 offset-3">
                    <table className="table">
                        <thead>
                            <tr>
                                <th scope="col">Id</th>
                                <th scope="col">Movie</th>
                                <th scope="col">Category</th>
                                <th scope="col">Director</th>
                                <th scope="col">Rating</th>
                            </tr>
                        </thead>
                        <tbody> 
                                {
                                    movies.length
                                    ?
                                    movies.map((movie, index) => (
                                        <tr key={index}>
                                            <th scope="row">{ index + 1 }</th>
                                            <td>{ movie.Movie }</td>
                                            <td>{ movie.Category }</td>
                                            <td>{ movie.Director }</td>
                                            <td><span className="badge bg-warning text-dark">{ movie.Rating }</span></td>
                                        </tr> 
                                    ))
                                    :
                                    <tr>
                                        <td colSpan="5" className="text-center">No Movies Found.</td>
                                    </tr> 
                                }
                        </tbody>
                    </table>
                </div>
            </div>
        </>

    );
};

export default HomeComponent;

In the above code, you can change the extension based on the file you want to import or export. (.xlsx, .xls, .csv)

5. Open the App.js file and make the following changes.

import React from "react";
import "./App.css";
import HomeComponent from "./components/home.component";

function App() {
  return (
    <main> 
        <HomeComponent /> 
    </main>
  );
}

export default App;

6. Finally start the project.

npm start

open http://localhost:3000 in the browser.

Sample Excel CSV Files:- https://github.com/ultimateakash/react-excel-csv/tree/master/sample%20files

Checkout my full react-excel-csv example. https://github.com/ultimateakash/react-excel-csv

If you facing any issues. don't hesitate to comment below. I will be happy to help you.

Thanks.