1

I have creates a react app using react CLI.

I have now created a folder called data now I want to read that data using the xlsx npm package. However, it is not working. I think it may have something to do with the way I may be referring to the excel file because I get a warning as "Property 'Sheets' does not exist on type 'string'" (Pic below)

I thought it would be easy to read an excel (that too a local one), but I am not able to figure it out. Can anyone help? I looked at a lot of SO questions related to this but no success.

My Code

import "./App.css";
import XLSX from "xlsx";

let fileName = "./data/menu.xlsx";
var first_worksheet = fileName.Sheets[fileName.SheetNames[0]];
var data = XLSX.utils.sheet_to_json(first_worksheet, { header: 1 });
console.log(fileName);

enter image description here

moys
  • 7,747
  • 2
  • 11
  • 42

2 Answers2

2

Your fileName is just a string with your path right now. You're not actually parsing it as an Excel sheet yet, so the property Sheets will not exist on it.

You need to actually read the file from the filesystem, and then use XLSX to parse it into a format you can use to programmatically interact with it. Since you're using react, you can look at the react examples in the XLSX repo for an idea of how to go about it. The included demos only cover react-native apps, however, so you may also want to start looking into ways to retrieve and parse files on the local filesystem from the browser, like FileReader and fetch. Then, once you have the file loaded into memory, you can call XLSX.read(file) to fully interpret it as an Excel worksheet.

toki
  • 936
  • 5
  • 18
  • Thanks for the answer but I am still able to figure out how to use `FileReader` on the file in the src folder. All the examples i find have an event (`input` element or `onClick` or `onChange`) that triggers the parsing of the excel. In my case I just want the excel to be read when the page is loaded & I cannot find any example for it. Up-voted your answer nonetheless for guidance. If you come across any example to suit my need, please let me know. – moys Dec 09 '21 at 06:23
1

I found the answer to my question in here Excel to JSON javascript code?

The answer that was useful for me was the answer from user7456320 (the code is copied below as well).

All that was needed was the XLSX library. I used the code below with useEffect hook in my react component to run once when the page loads.

<!doctype html>
<html>

<head>
    <title>Excel to JSON Demo</title>
    <script src="xlsx.full.min.js"></script>
</head>

<body>

    <script>
        /* set up XMLHttpRequest */
        var url = "http://myclassbook.org/wp-content/uploads/2017/12/Test.xlsx";
        var oReq = new XMLHttpRequest();
        oReq.open("GET", url, true);
        oReq.responseType = "arraybuffer";

        oReq.onload = function(e) {
            var arraybuffer = oReq.response;

            /* convert data to binary string */
            var data = new Uint8Array(arraybuffer);
            var arr = new Array();
            for (var i = 0; i != data.length; ++i) arr[i] = String.fromCharCode(data[i]);
            var bstr = arr.join("");

            /* Call XLSX */
            var workbook = XLSX.read(bstr, {
                type: "binary"
            });

            /* DO SOMETHING WITH workbook HERE */
            var first_sheet_name = workbook.SheetNames[0];
            /* Get worksheet */
            var worksheet = workbook.Sheets[first_sheet_name];
            console.log(XLSX.utils.sheet_to_json(worksheet, {
                raw: true
            }));
        }

        oReq.send();
    </script>
</body>
</html>
moys
  • 7,747
  • 2
  • 11
  • 42