How to upload an Excel sheet file using react.js and display data to a table

Question

I am new to react JS. I am trying to perform uploading Excel sheet file using react.js and display data to a table. I got partial reference from the link but, it is not complete. Please help with it. Importing data from excel and displaying in a react component

Eachern

see this link #46909760 – Blackberry

Answer 1

You can use the library like https://react-dropzone.js.org/ to upload files and then use https://github.com/felixrieseberg/React-Spreadsheet-Component library to display it.

Haugen

but how exactly do we "read" and save data using the react dropzone library? #68070200 – Eure

Answer 2

react-excel-renderer

There's a perfect library exactly for this ! It converts the excel data to JSON first, then renders it into a HTML table. Its called react-excel-renderer

• Install it npm install react-excel-renderer --save

• Import both components ExcelRenderer and OutTable

  import {ExcelRenderer, OutTable} from 'react-excel-renderer';

• Provide the file object to the ExcelRenderer function in your event handler

    fileHandler = (event) => {
  let fileObj = event.target.files[0];
  
  //just pass the fileObj as parameter
  ExcelRenderer(fileObj, (err, resp) => {
    if(err){
      console.log(err);            
    }
    else{
      this.setState({
        cols: resp.cols,
        rows: resp.rows
      });
    }
  });               
  
  }
  

• Once JSON is obtained, provide it to the OutTable component
  <OutTable data={this.state.rows} columns={this.state.cols} tableClassName="ExcelTable2007" tableHeaderRowClass="heading" />

Thats it ! Its done !

A demo for the same can be found here

Bayly

This is great! Dose it support reading data from multiple sheets in the document? – Frontal

Is there any way if I get a excel file from backend api call and then render on frontend. In your sample I checked that it is for when you select file from file browser. – Hominy

@tejesh: so long as you can get the file blob, whether from file browser or a backend api, it's possible. – Bayly

@sachin : if you mean workbooks, then no, currently, it only supports a single workbook. – Bayly

@AshishDeshpande thanks ! I did some workaround and modified your example. Now it is working fine. I created file Object instead of blob and then I passed that object directly to filerender function. – Hominy

Answer 3

I've had success with this using xlsx to read excel file sheets. Just do something like this:

import excel from 'xlsx';
let fileName = "newData.xlsx";
let workbook = excel.readFile(fileName);
console.log(workbook) //should print an array with the excel file data

assuming a spreadsheet with name 'newData.xlsx' in your root folder. Then its just a matter of figuring out how to access the data you want. This should also be helpful.

Sanguine

I get this error in the console: Uncaught TypeError: _fs.readFileSync is not a function – Cookstove

Looking back, I think this only works in Node or some other server side deployment. If you want to use XLSX in the browser you'll have to use a FileReader – Sanguine

Answer 4

I am extending @AshishDeshpande answer, I used the same library react-excel-rendere If you have file coming from backend api call, the using axios, you can do below:

if we check demo code from @AshishDeshpande ,

https://github.com/ashishd751/excel-renderer-demo/blob/master/src/App.js

I added fileHandler() function in openFileBrowser(),

openFileBrowser = () => {
        
      axios({
        url:'http://127.0.0.1:5000/display',
        method:'GET',
        responseType: 'blob'
      })
      .then((response) => {
                  const url = (new File([response.data], "file.xlsx", {type:"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", lastModified:new Date().getTime()}));

                  console.log(url);
                 
                  let fileName = url.name;
                  console.log(url);
                  
                  //check for file extension and pass only if it is .xlsx and display error message otherwise
                  if(fileName.slice(fileName.lastIndexOf('.')+1) === "xlsx"){
                    this.setState({
                      uploadedFileName: fileName,
                      isFormInvalid: false
                    });
                    this.renderFile(url)
                  }    
                  else{
                    this.setState({
                      isFormInvalid: true,
                      uploadedFileName: ""
                    })
                  }
                 
                  
    })
    }

Hominy

can update to: axios.get("/display", { //url:'http://127.0.0.1:5000/display', method:'GET', responseType: 'blob' }) then, need to add "proxy": "127.0.0.1:5000", in package.json – Hominy

Answer 5

Make react app, then add this code to app.js, just change column name(i.e. InputA and InputB) these were for me and if want to add other columns you can do that. Hope this might help you. Feel free to comment (This is my first answer on stackoverflow).

import React, { useState } from "react";
import "./App.css";
import * as XLSX from "xlsx";
function App() {
  const [items, setItems] = useState([]);

  const readExcel = (file) => {
    const promise = new Promise((resolve, reject) => {
      const fileReader = new FileReader();
      fileReader.readAsArrayBuffer(file);

      fileReader.onload = (e) => {
        const bufferArray = e.target.result;

        const wb = XLSX.read(bufferArray, { type: "buffer" });

        const wsname = wb.SheetNames[0];

        const ws = wb.Sheets[wsname];

        const data = XLSX.utils.sheet_to_json(ws);

        resolve(data);
      };

      fileReader.onerror = (error) => {
        reject(error);
      };
    });

    promise.then((d) => {
      setItems(d);
    });
  };

  return (
    <div>
      <input
        type="file"
        onChange={(e) => {
          const file = e.target.files[0];
          readExcel(file);
        }}
      />

      <table class="table container">
        <thead>
          <tr>
            <th scope="col">InputA</th>
            <th scope="col">InputB</th>
          </tr>
        </thead>
        <tbody>
          {items.map((d) => (
            <tr key={d.InputA}>
              <th>{d.InputA}</th>
              <td>{d.InputB}</td>
            </tr>
          ))}
        </tbody>
      </table>
    </div>
  );
}

export default App;

Solent

Answer 6

datetime need for this

const parseDate = v => {
  const d = v - 1;
  const t = Math.round((d - Math.floor(d)) * 24 * 60 * 60);
  console.log('parseDate d ', d, 't', t);
  return moment(new Date(1900, 0, d, 0, 0, t)).format('YYYY-MM-DD');
};

const dateStr = parseDate(43523);
console.log('dateStr ', dateStr)

<script src="https://momentjs.com/downloads/moment.js"></script>

Millburn