CODEWITHSUNDEEP
javascripthtmljson
Prerona Kundu
Prerona Kundu

Reputation: 113

Create JavaScript array from excel data using JavaScript and JSON

I'm trying to read and parse an excel file and create a JavaScript array object with the data. The excel file will look like:

AA11 AA22 AN65
AB11 AB22 AN64
...

And I want the JavaScript array to look like:

[[AA11, AA22, AN65], [AB11, AB22, AN64],...]

So far I have the following code:

<input type="file" id="input" accept=".xls,.xlsx,.ods" />
<a id="result"></a>
<script type="text/javascript">
    $("#input").on("change", function(e) {
                var file = e.target.files[0];
                if (!file) return;

                var FR = new FileReader();
                function byDocument(e) {
                    var data = new Uint8Array(e.target.result);
                    var workbook = XLSX.read(data, { type: "array" });
                    var firstSheet = workbook.Sheets[workbook.SheetNames[0]];

                    var result = XLSX.utils.sheet_to_json(firstSheet, { header: 1 });
                    var output = document.getElementById("result");
                    output.innerHTML = JSON.stringify(result, null, 2);
                    window.alert(result);
                    window.alert(result.type);
                    var array;
                    array = output.innerHTML;
                    output.parentNode.removeChild(output);
                    return array;
                }
                FR.onload = function(e) {
                    array = byDocument(e);
                    window.alert(array);
                    array.type = Array;
                    window.alert(array.type);
                    window.alert(array.length);
                };
                FR.readAsArrayBuffer(file);
            });

</script>

This outputs text on the document that looks like an array, but when I try to store the data in a variable and index the array, it views the element array as some sort of string, or undefined. I'm wondering how to parse the excel file, or convert the JSON string, so that it behaves like a JavaScript array, which I could then index.

Upvotes: 0

Views: 1716

Answers (3)

Eric Jones
Eric Jones

Reputation: 76

The line output.innerHTML = JSON.stringify(result, null, 2); will turn the object into JSON and put it in the DOM.

const myJSObject = JSON.parse(myJSON) will return a JavaScript object and assign it to a variable.

I took a few of the DOM lines out of your function to make it less confusing, so you could try replacing your function with:

function byDocument(e) {
    var data = new Uint8Array(e.target.result);
    var workbook = XLSX.read(data, { type: "array" });
    var firstSheet = workbook.Sheets[workbook.SheetNames[0]];

    var result = XLSX.utils.sheet_to_json(firstSheet, { header: 1 });
    var array;
    array = result
    return array;
}

Upvotes: 0

Emiel Zuurbier
Emiel Zuurbier

Reputation: 20924

It seems like you are already turning your result into JSON here:

var result = XLSX.utils.sheet_to_json(firstSheet, { header: 1 });

According to the documents of xlsx this will convert your excel file into workable JSON. So result might be the value you need to return instead of array in your byDocument function.

Upvotes: 0

Max&#237;m G.
Max&#237;m G.

Reputation: 1026

I believe it should be array = result instead of array = output.innerHTML. Or, at least, array = JSON.parse(ourput.innerHTML), because innerHTML is not an array, it's a string (you got it by JSON.strigify before).

Upvotes: 2

Related Questions