Reading excel file into array using javascript

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.

Answer

Not sure what you’re using to parse the Excel, is it IgniteUI ? For what it’s worth, the free (community edition) of SheetJS, js-xlsx provides a few functions that produce exactly the output you needed, given the spreadsheet you provided.

The docs are a bit messy, but they are complete, the most interesting sections for this use-case are: Browser file upload form element under Parsing workbooks and XLSX.utils.sheet_to_json. You can run a test with the type of spreadsheet you provided in the code sample below:

$("#input").on("change", function (e) {
   var file = e.target.files[0];
   // input canceled, return
   if (!file) return;
   
   var FR = new FileReader();
   FR.onload = function(e) {
     var data = new Uint8Array(e.target.result);
     var workbook = XLSX.read(data, {type: 'array'});
     var firstSheet = workbook.Sheets[workbook.SheetNames[0]];
     
     // header: 1 instructs xlsx to create an 'array of arrays'
     var result = XLSX.utils.sheet_to_json(firstSheet, { header: 1 });
     
     // data preview
     var output = document.getElementById('result');
     output.innerHTML = JSON.stringify(result, null, 2);
   };
   FR.readAsArrayBuffer(file);
});

<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.14.5/xlsx.full.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<input type="file" id="input" accept=".xls,.xlsx,.ods">
<pre id="result"></pre>