How to read data from a local XLSX file and save it into an Object/Array

Question

I need to read data from a local XLSX file and save it to an object (or array).

The data looks like this:

and I need the data like this:

so I can work with it (compare it to other objects/display it...)

How is this possible?

I tried an AJAX call with the following code:

/* set up XMLHttpRequest */
var url = "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];
  var test = XLSX.utils.sheet_to_json(worksheet,{raw:true})
  console.log(test)
}

oReq.send();

console.log(test) //I can not access it here..

It worked and I got the object as I needed but the problem is I can not access the object outside of the function. I made a post a few hours ago: How to get an object from an async call (xlsx file) to global scope

I edited the post and added my attempt – House97_ May 10 '19 at 13:14 — House97_, May 10 '19 at 13:14

score 0 · Answer 1 · answered Jun 19 '21 at 08:37

<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.8.0/jszip.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.8.0/xlsx.js"></script>
<script>
    var ExcelToJSON = function () {

        this.parseExcel = function (file) {
            var reader = new FileReader();

            reader.onload = function (e) {
                var data = e.target.result;
                var workbook = XLSX.read(data, {
                    type: 'binary'
                });
                workbook.SheetNames.forEach(function (sheetName) {
                    // Here is your object
                    var XL_row_object = XLSX.utils.sheet_to_row_object_array(workbook.Sheets[
                        sheetName]);
                    var json_object = JSON.stringify(XL_row_object);
                    console.log(JSON.parse(json_object));
                    jQuery('#xlx_json').val(json_object);
                })
            };

            reader.onerror = function (ex) {
                console.log(ex);
            };

            reader.readAsBinaryString(file);
        };
    };

    function handleFileSelect() {

        fetch(
                'CSV_DATASET.xlsx')
            .then(res => res.blob()) // Gets the response and returns it as a blob
            .then(blob => {
                // Like calling ref().put(blob)
                let file = new File([blob], {
                    type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
                });
                console.log(file)
                var xl2json = new ExcelToJSON();
                console.log(file)
                xl2json.parseExcel(file);
            });

    }
</script>

just call function onload – Bilal Khursheed Jun 19 '21 at 08:38 — Bilal Khursheed, Jun 19 '21 at 08:38

Endless · Answer 2 · 2021-06-20T09:49:22.713

Don't read it as a binarystring, use

XLSX.read(new Uint8Array(arrayBuffer), {
  type: 'array'
})

You could use async/await to avoid all callbacks

<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.17.0/jszip.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.17.0/xlsx.js"></script>
<script type="module"> // Run as type=module if you want top level await
  const sampleUrl = 'https://file-examples-com.github.io/uploads/2017/02/file_example_XLSX_10.xlsx'
  const res = await fetch(sampleUrl)
  const arrayBuffer = await res.arrayBuffer()
  const uint8 = new Uint8Array(arrayBuffer)

  /* Call XLSX */
  const workbook = XLSX.read(uint8, { type: 'array' })

  /* DO SOMETHING WITH workbook HERE */
  const firstSheetName = workbook.SheetNames[0]
  /* Get worksheet */
  const worksheet = workbook.Sheets[firstSheetName]
  const test = XLSX.utils.sheet_to_json(worksheet, { raw: true })
  console.log(test)
</script>

If you are not using top-level await then you have to put the code inside a async function

async function loadUrl (url) {
  const res = await fetch(url)
  ...
}

How to read data from a local XLSX file and save it into an Object/Array

2 Answers2