Javascript read excel file into array

I'm trying to read an excel file and create a multidimensional array in javascript with it. The excel file will look like:

AA11 AA22 AN65
AB11 AB22 AN64
...

I need it to create an array that looks like:

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

So far, I've been able to bring up a file selection window, and I believe it's reading the file, I just think it might not be putting the data into the array correctly. This is what I have so far:

    <script type="text/javascript">
        $(function () {
            $("#input").on("change", function () {
            var excelFile,
            var array = [[],[]];
                fileReader = new FileReader();

            $("#result").hide();

            fileReader.onload = function (e) {
                var buffer = new Uint8Array(fileReader.result);

                $.ig.excel.Workbook.load(buffer, function (workbook) {
                    var column, row, newRow, cellValue, columnIndex, i,
                        worksheet = workbook.worksheets(0),
                        columnsNumber = 0,
                        gridColumns = [],
                        data = [],
                        worksheetRowsCount;
                    while (worksheet.rows(0).getCellValue(columnsNumber)) {
                        columnsNumber++;
                    }
                    for (columnIndex = 0; columnIndex < columnsNumber; columnIndex++) {
                        column = worksheet.rows(0).getCellText(columnIndex);
                        gridColumns.push({ headerText: column, key: column });
                    }
                    for (i = 1, worksheetRowsCount = worksheet.rows().count() ; i < worksheetRowsCount; i++) {
                        newRow = {};
                        row = worksheet.rows(i);

                        for (columnIndex = 0; columnIndex < columnsNumber; columnIndex++) {
                            cellValue = row.getCellText(columnIndex);
                            //newRow[gridColumns[columnIndex].key] = cellValue;
                            array[row,columnIndex] = cellValue;
                        }

                        window.alert(array[0][0]);
                        data.push(array);
                    }

    </script>

Any help would be greatly appreciated.

I need to enter large amount of data in an array, which will be in this form:

[
  [data1,data2,data3,...,data9],
  [data1,data2,data3,...,data9],
  [data1,data2,data3,...,data9],
  ...
]

As it stands, entering the data directly as JSON is a time consuming process that tends to have many errors. Alternatively, I would like to enter the data into an excel sheet and generate the JSON array from the sheet. How can I do this?

Initially, when I started working on this requirement, people suggested using any one of the backend libraries to read and convert the excel data into JSON objects. But it is difficult to send requests to the server every time. On the other hand, it was really easy to convert the excel file on the client into a JSON object by using pure JavaScript.

There is a JavaScript plugin XSLX that can be used to read excel files as binary strings and to convert them into JSON objects.

Plugins Required: xlsx.full.min.js

Installation

Include the CDN link in the head tag of an HTML file like this:

Now, in the HTML body tag, we need to write an input tag with the type as a file and need to restrict the user to select only files with the extension of .xls or .xlsx. Paste the below code into your HTML body tag.

Now we need to add event listeners for both input and button elements for handling change in input and button click events.

In JavaScript we can add the event listeners to elements like this:

We can read the data in the excel file by using a file reader as a binary string in JavaScript. Then we use XLSX which has a built-in facility of SheetJS to convert our binary string into a JSON object.

Now select the excel file and click on the convert button to display the JSON object in the browser. After that, we can use the JSON object for any manipulations and integrate the data into different UI layouts.

In Angular

For your convenience, I am adding the code sandbox link live demo here

To export JSON data as excel look into this video

Click here for source code

Thanks for your time :)

Level Up Coding

Thanks for being a part of our community! Level Up is transforming tech recruiting. Find your perfect job at the best companies.

How do I convert excel data to array?

Can we read excel file in JavaScript?

How fetch data from excel in JavaScript?

How do I convert an excel file to JSON?