Trying to get an Excel document through ajax, running into async issues

Question

I'm adding JavaScript to a SharePoint and I'm trying to write a function that gets an Excel document, or rather the data in it, from a library. I've already figured out how to do that, but the files in question are too large to be returned. I've considered manually setting the size limit for ajax, but I haven't found any way to do this, so Plan B is to send a query for each row

$("#button").click(function() {
            readFileRow(libraryUrl, "FileName.xlsx", "Sheet1", 1, "E", [])
            .done(function(cells) {
                console.log(xmlToJson(cells.documentElement));
            });
        });

/**
     * Reads a file in SharePoint Library via ExcelRest and returns the document
     *
     * @params string libraryUrl
     * @params string fileName
     * @params string sheetName
     * @params int rowNum
     * @params string lastColumn
     * @params string[][] finalResults
     *
     * @returns string[][]
    **/
    function readFileRow(libraryUrl, fileName, sheetName, rowNum, lastColumn, finalResults) {
        var fileUrl = libraryUrl + "/" + fileName + "/Model/Ranges('" + sheetName + "!A" + rowNum + "|" + lastColumn + rowNum + "')?$format=atom";

        return $.ajax({
            url: fileUrl,
            type: "GET",
            error: function (request, status, error) {
                console.log(error);
            },
        }).done(function(data) {
            jsonData = xmlToJson(data.documentElement);
            cells = serializeRange(jsonData);

            if(cells) {
                finalResults.push(cells);
                rowNum++;
                finalResults = readFileRow(libraryUrl, fileName, sheetName, rowNum, lastColumn, finalResults);
            }

            return $.when(finalResults);
        });
    }

As you can see, I'm trying to get around the async issue by calling the function recursively when the request comes back with data, meaning that it has to keep running. When I place a console.log() just before the return, I can see that it is compiling the results as I need it to. However, the return from calling readFileRow() is coming back before the process finishes and consists of the response from the very first ajax query, having apparently not been run through the .done() function. I've tried removing the return in front of the ajax call, but this only causes an exception, since the return is no longer thenable and thus doesn't have a .done() function.

Can anyone spot what's wrong with this function? Async functions aren't my forte and I'm pretty stumped.

Answer 1

In my previous project to read the data from excel which is placed in a library from the same site, I used ExcelPLus, its a free JavaScript plugin, include that in your SharePoint page and read the content of your excel file very easily. Below code snippet shows how to open and display the content of an excel sheet.

var ep=new ExcelPlus();
// we call openRemote()
ep.openRemote("http://my.server.com/path/file.xlsx", function(passed) {
  if (!passed) alert("Error: impossible to load the remote file");
  else alert(ep.selectSheet(1).readAll()) // show the content of the first sheet
})

and if you know the range from which you need to fetch the data from excel sheet, then you can use the read function, like below,

read(range, options)

range can be a range (e.g. "A1:D1"), or a single cell (e.g. "A1")

Answer 2

It looks like the answer was simply that I didn't fully understand what various methods related to async functions were doing. I restructured the function in the following way and got it to work as expected:

    $("#updateClusterDetails").click(function() {
        console.log("Update initiated");
        var baseFile = "AIF%20Test.xlsx";

        $.when(readFileRow(libraryUrl, baseFile, "Sheet1", 1, "E", new Array())).then(function(results) {
            console.log(results);
        });
    });

/**
 * Reads a file in SharePoint Library via ExcelRest and returns the document
 *
 * @params string libraryUrl
 * @params string fileLocation
 *
 * @returns Document/HTML
**/
function readFile(libraryUrl, fileLocation) {
    // The base url for the SharePoint document library
    var fileUrl = libraryUrl + fileLocation;

    return $.ajax({
        url: fileUrl,
        type: "GET",
        error: function (request, status, error) {
            console.log(error);
        },
        success: function(data) {
            return $.when(data);
        }
    });
}

    /**
 * Reads a series of rows in an Excel file in SharePoint Library via ExcelRest and returns the row data
 *
 * @params string libraryUrl
 * @params string fileName
 * @params string sheetName
 * @params int rowNum
 * @params string lastColumn
 * @params string[][] currentResults
 *
 * @returns string[][]
**/
function readFileRows(libraryUrl, fileName, sheetName, rowNum, lastColumn, currentResults) {
    var fileUrl = "/" + fileName + "/Model/Ranges('" + sheetName + "!A" + rowNum + "|" + lastColumn + rowNum + "')?$format=atom";

    return readFile(libraryUrl, fileUrl)
    .then(function(data) {
        jsonData = xmlToJson(data.documentElement);
        cells = serializeRange(jsonData);

        if(!cells) {
            return $.when(currentResults);
        }
        else {
            currentResults.push(cells);
            return $.when(readFileRow(libraryUrl, fileName, sheetName, rowNum + 1, lastColumn, currentResults));
        }

    });
}