Create JavaScript array from excel data using JavaScript and JSON

Question

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.

Maxím G. · Accepted Answer · 2019-08-13 20:12:58Z

2

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).

answered Aug 13, 2019 at 20:12

Maxím G.

1,03613 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Emiel Zuurbier · Accepted Answer · 2019-08-13 20:15:18Z

0

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.

answered Aug 13, 2019 at 20:15

Emiel Zuurbier

21.2k3 gold badges25 silver badges36 bronze badges

Comments

Eric Jones · Accepted Answer · 2019-08-13 20:19:29Z

0

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;
}

answered Aug 13, 2019 at 20:19

Eric Jones

768 bronze badges

Collectives™ on Stack Overflow

Create JavaScript array from excel data using JavaScript and JSON

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related