How can I parse an Excel file using SheetJS from an external link (Amazon S3)

Question

I am trying to parse an excel file that I already have the URL for. I keep getting different errors when trying to access the file so that it can be readable. Right now, here is my code:

  const input_file = doc.input_file;
  const extension = input_file.split('.').pop();



  let XMLHttpRequest = require("xmlhttprequest").XMLHttpRequest;
  let oReq = new XMLHttpRequest();
  oReq.open("GET", input_file, true);
  oReq.responseType = "arraybuffer";

  oReq.onload = function(e) {
    let arraybuffer = oReq.responseText;
    /* convert data to binary string */
    let data = new Uint8Array(arraybuffer);
    let arr = new Array();
    for(let i = 0; i != data.length; ++i) arr[i] = String.fromCharCode(data[i]);
    let bstr = arr.join("");

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

    /* DO SOMETHING WITH workbook HERE */
    let firstSheet = workbook.SheetNames[0];
    let parsed = XLSX.utils.sheet_to_csv(firstSheet);
    console.log(parsed);
  }

  oReq.send();

The current error I am getting is: Error: Unsupported file NaN at the when I try to read the file at: let workbook = XLSX.read(bstr, {type:"binary"});

I'm unsure on the easiest way to read that external link. Any ideas? If it helps, I am using Meteor.

Answer 1

This is a tried-and-true answer.

There are two problems with your code:

1. for binary files, it should be let arraybuffer = oReq.response;, not let arraybuffer = oReq.responseText;

2. You should enabled Cross-Origin Resource Sharing on your Amazon S3 instance. Just follow the official tutorial here.

Here is a working codepen:

http://codepen.io/KevinWang15/pen/GZXJKj

Are you using nodeJS?

note: The above code just uses the web browser's (chrome) XMLHttpRequest, I'm noticing that you are using

XMLHttpRequest = require("xmlhttprequest").XMLHttpRequest

Are you using something like nodejs? (Sorry I'm not familiar with Meteor)

More specifically, are you using driverdan/node-XMLHttpRequest ?

I experimented with it and your code, and it led to exactly the same error message. I think it's because this XMLHttpRequest still has compatibility problem with oReq.response and oReq.responseText

If you are using nodeJS, I recommend another library: ykzts/node-xmlhttprequest

Install it with

npm i w3c-xmlhttprequest

Change your XMLHttpRequest with

let XMLHttpRequest = require('w3c-xmlhttprequest').XMLHttpRequest;

And it instantly solves the problem!