Read xlsx file from local-CodePudding

I am having xlsx file under the path src/excel/data.xlsx .

So I am trying to get the data from this excel and form array.

Code tried:

import * as XLSX from "xlsx";

export function App() {
  fetch("./excel/data.xlsx")
    .then((res) => res.arrayBuffer())
    .then((ab) => {
      const wb = XLSX.read(ab, { type: "array" });
      console.log("html ", wb);
    });

  return <div>Hello World</div>;
}

Error:

Invalid HTML: could not find < table >

If I give correct path ./excel/data.xlsx or wrong path notvalid.xlsx in this line fetch("./excel/data.xlsx") , then I get the same error like above, so I think it may be error due to path setting but I am not sure.

Requirement:

I am in the need to read the data from excel/data.xlsx file and get the data as array.

Working Example:

Please help me to achieve the expected result.

CodePudding user response：

After reading this SheetJS issue on GitHub (Invalid HTML: could not find #1110), I get the idea that it could be an issue with your fetch-request receiving a HTML-page rather than an XLSM-file. Perhaps you're simply referencing the file with the wrong URL which is giving a 404 response, which is a HTML file, which starts with '<', which is interpreted as HTML, which will give an error not knowing what <table> is.

All of this is speculations derived from the answers in the GitHub issue.

Make sure that you can download your XLSM-file by going to yourwebsiteurl.com/path/to/app/folder/excel/data.xlsm. You could also provide us with the file structure on your web server regarding your index.html file and your data.xlsm file so that we can make sure that the URL is correct.

CodePudding user response：

Your solution here for React.js. The excel sheet should be under the public folder.

import * as XLSX from "xlsx";

export function App() {
  function Upload() {
    var url = "excel/data.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();
    console.log("Read!@");
  }
  return <button onClick={Upload}>Click me</button>;
}

This approach works on the server side:

import * as XLSX from "xlsx";

export function App() {
  let workbook = XLSX.readFile('./src/excel/data.xlsx');
  let sheet_name_list = workbook.SheetNames;
  let xlData = XLSX.utils.sheet_to_json(workbook.Sheets[sheet_name_list[0]]);
  console.log(xlData);

  return <div>Hello World</div>;
}

If you are trying on the client side, you'll need to upload the excel file by input and then process it accordingly.

function Upload() {
const fileUpload = (document.getElementById('fileUpload'));
const regex = /^([a-zA-Z0-9\s_\\.\-:]) (.xls|.xlsx)$/;
if (regex.test(fileUpload.value.toLowerCase())) {
    let fileName = fileUpload.files[0].name;
    if (typeof (FileReader) !== 'undefined') {
        const reader = new FileReader();
        if (reader.readAsBinaryString) {
            reader.onload = (e) => {
                processExcel(reader.result);
            };
            reader.readAsBinaryString(fileUpload.files[0]);
        }
    } else {
        console.log("This browser does not support HTML5.");
    }
} else {
    console.log("Please upload a valid Excel file.");
}
}

function processExcel(data) {
    const workbook = XLSX.read(data, {type: 'binary'});
    const firstSheet = workbook.SheetNames[0];
    const excelRows = XLSX.utils.sheet_to_row_object_array(workbook.Sheets[firstSheet]);

    console.log(excelRows);
}

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Process Excel File</title>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.16.0/xlsx.full.min.js"></script>
</head>
<body>
<input  type="file" id="fileUpload" onchange="Upload()"/>
</body>
</html>

CodePudding user response：

It is an issue with the path you are passing to fetch. If you want to use fetch, then put the excel file in your public folder. If it is a project generated with create-react-app. Place your excel file in public/excel/data.xlsx and change the path you are passing to fetch ie fetch('excel/data.xlsx'). If you are using any other bundler or framework to generate your project, just make sure that the excel file is in the folder that is served by your application if you want to use fetch with it.

Here is it working on codesandbox