PikeBishop PikeBishop - 1 month ago 25
TypeScript Question

XMLHTTPRequest.response is null when trying to read an excel file

Hi new to alot of this i'm trying to use the below typescript to grab an excel file before converting it to json to use as a data source for charts i'm using. it's bugging out in the onload function because i think the .response = null - readystate = 1 (OPENED) and the status = 0 (Open or Unsent). not too sure wjy it's null.. I've read the readstate needs to be = 4 and status = 200 (finishing opening it) before it can work. but i don't know how i can do that?

import { Injectable } from '@angular/core';
import { read, IWorkBook, IWorkSheet, utils } from 'ts-xlsx';

@Injectable()
export class OperationDataService {
oUrl: string;
xhr: any;
wb: IWorkBook;
wbSheet: IWorkSheet;
arraybuffer: any;
data: any;
arr: any;
bstr: any;

getData() {
this.oUrl = './assets/operations.xlsx';
this.xhr = new XMLHttpRequest();
this.xhr.open('GET', this.oUrl, true);
this.xhr.responseType = 'arraybuffer';

console.log('Service Constructor ' + this.xhr.open + ' - ' + this.xhr.readyState + ' - ' + this.xhr.response + ' - ' + this.xhr.status);

if (this.xhr.readyState === 4) {

if (this.xhr.status === 200) {

this.xhr.onload = function (e) {
console.log('in side function');

this.arraybuffer = this.xhr.response;

console.log('ArrayBuffer ' + this.arraybuffer);

/* convert data to binary string */
this.data = new Uint8Array(this.arraybuffer);
this.arr = new Array();

for (let item of this.data) {
this.arr[item] = String.fromCharCode(this.data[item]);
}

this.bstr = this.arr.join('');
console.log(this.xhr.responseText);

this.wb = read(this.bstr, { type: 'binary' });
this.wbSheet = this.wb.Sheets[0];

this.objAr = utils.sheet_to_json(this.wbSheet, { header: 1 });
console.log(utils.sheet_to_json(this.wbSheet, { header: 1 }));
console.log('get data set');

return this.objAr;
};
}
}

this.xhr.send();
}

}





UPDATE

Thanks the below has got me up to the javescript lib to convert the excel to json - seems to be a issue with a JSzip version in the dependencies of ts-xlsx but the XMLHtpRequest GET has worked i can display the binary data in the browser.

import { Injectable } from '@angular/core';
import { read, IWorkBook, IWorkSheet, utils } from 'ts-xlsx';

@Injectable()
export class OperationDataService {
oUrl: string;
xhr: any;
wb: IWorkBook;
wbSheet: IWorkSheet;
arraybuffer: any;
data: any;
arr: any;
bstr: any;

getData() {
this.oUrl = './assets/operations.xlsx';
this.xhr = new XMLHttpRequest();
this.xhr.open('GET', this.oUrl, true);
this.xhr.responseType = 'arraybuffer';

this.xhr.addEventListener('load', function() {

console.log('in side function');

this.arraybuffer = this.xhr.response;

// /* convert data to binary string */
this.data = new Uint8Array(this.arraybuffer);
this.arr = new Array();

// console.log('Data ' + this.data);

for (let i = 0; i !== this.data.length; ++i) {
this.arr[i] = String.fromCharCode(this.data[i]);
}
this.bstr = this.arr.join('');
// console.log('bstr ' + this.bstr);

this.wb = read(this.bstr, { type: 'binary' });
console.log('wb ' + this.wb);

this.wbSheet = this.wb.Sheets[0];
this.objAr = utils.sheet_to_json(this.wbSheet, { header: 1 });

console.log(this);

return this.objAr;

}.bind(this));

this.xhr.send(null);
}

}

Answer

Loading a file using XMLHttpRequest can be simpler than that:

this.oUrl = './assets/operations.xlsx';
this.xhr = new XMLHttpRequest();
this.xhr.addEventListener("load", function() {
    var arraybuffer = this.response;
    ...
});
this.xhr.open('GET', this.oUrl, true);
this.xhr.responseType = 'arraybuffer';

You can do it your way as well, but then it should be like:

this.xhr.onreadystatechange =() => {
    if (this.xhr.readyState === 4 && this.xhr.status === 200) {
        this.arraybuffer = this.xhr.response;

    }
}

If you are using the onload event then you don't need to check the readyState.
In any case you never really added the event listener because you adding it only if readyState === 4 and status === 200 which will never happen before you actually sent the request.

As for the response, it looks like that because xlsx files are zipped.


Edit

If you're assigning an event listener function as an anonymous one then the this in the body of that function won't be your object but the XMLHttpRequest object so it should be:

this.xhr.addEventListener("load", function() {
    var arraybuffer = this.response;
    ...
});

Unless you bind it:

this.xhr.addEventListener("load", function() {
    var arraybuffer = this.xhr.response;
    ...
}.bind(this));

If you're using an arrow function then this will be your object:

this.xhr.addEventListener("load",() => {
    var arraybuffer = this.xhr.response;
    ...
});