Javascript - Get excel data and post to server side using ajax

heyhej
September 19, 2023
165 views
1 vote
2 Answers

I am trying to gather the data from excel using the js and ajax

    var ExcelToJSON = function () {
    this.parseExcel = function (file) {
        var reader = new FileReader();
        reader.onload = function (e) {
            var data = e.target.result;
            var workbook = XLSX.read(data, {
                type: 'binary'
            });
            var dataArray = [];
            workbook.SheetNames.forEach(function (sheetName) {
                var XL_row_object = XLSX.utils.sheet_to_row_object_array(workbook.Sheets[sheetName]);
                var json_object = JSON.stringify(XL_row_object);
                dataArray.push(json_object);
                //jQuery('#xlx_json').val(json_object);

                $.ajax({
                    url: "test2.aspx/InsertExcelGeo",
                    type: "post",
                    data: JSON.stringify({ dataArray: dataArray }),
                    contentType: "application/json; charset=utf-8",
                    dataType: "json",
                    success: function (_data) {
                        alert(_data.d);
                    }
                })
            })
        };
        reader.onerror = function (ex) {
            console.log(ex);
        };
        reader.readAsBinaryString(file);
    };
};

this code is fully functioning, the file uploaded was a excel file with 3 sheets, with same content of column but different data, all data can be gather to the server side

but this 3 will run on the same time, i would like to gather the data first to the array then post it to ajax, but when i placed the ajax post outside to loop the ajax post will not work

i want to collect data all first using the loop and paste it on the array then after the loop i want the ajax post to run with the data of array collected

Answers

Use promises to manage asynchrony.

You can create function to envelope your ajax call and return the promise.

function ajaxCall(dataArray) => {
    return new Promise ((resolve, reject)=>{$.ajax({
      url: "test2.aspx/InsertExcelGeo",
      type: "post",
      data: JSON.stringify({ dataArray: dataArray }),
      contentType: "application/json; charset=utf-8",
      dataType: "json",
      success: function (_data) { resolve(_data.d) },
      error: function () {reject()}
      });
    })
}

Then you can call this function which returns a Promise and wait until all of the ajax calls finish to manage the answers:

var ExcelToJSON = async function () { //Your function must become asynchronous
    this.parseExcel = function (file) {
        const promises = []; //You will need an array to store the promises
        var reader = new FileReader();
        reader.onload = function (e) {
            var data = e.target.result;
            var workbook = XLSX.read(data, {
                type: 'binary'
            });
            var dataArray = [];
            workbook.SheetNames.forEach(function (sheetName) {
                var XL_row_object = XLSX.utils.sheet_to_row_object_array(workbook.Sheets[sheetName]);
                var json_object = JSON.stringify(XL_row_object);
                dataArray.push(json_object);
                //jQuery('#xlx_json').val(json_object);

                promises.push(ajaxCall(dataArray)); //Store all the promises within your for loop
            });
            //wait for all the promises to finish
            const results = await Promise.allSettled(promises);
            results.forEach((result) => {
                if(result.status === 'fulfilled'){
                    //Code when response was 200
                    console.log(result.value.data)
                } else {
                    //Code when response was other than 200
                }
            })
        };
        reader.onerror = function (ex) {
            console.log(ex);
        };
        reader.readAsBinaryString(file);
    };

This should do the trick.
Good luck!

In the original code, the AJAX request is made inside the forEach loop, which means you’re making an AJAX request for each sheet separately. If you want to collect all the data first and then make a single AJAX request, you can modify the code like this:

var ExcelToJSON = function () {
    this.parseExcel = function (file) {
        var reader = new FileReader();
        reader.onload = function (e) {
            var data = e.target.result;
            var workbook = XLSX.read(data, {
                type: 'binary'
            });
            var dataArray = [];

            workbook.SheetNames.forEach(function (sheetName) {
                var XL_row_object = XLSX.utils.sheet_to_row_object_array(workbook.Sheets[sheetName]);
                var json_object = JSON.stringify(XL_row_object);
                dataArray.push(json_object);
            });

            // Moved the AJAX request outside of the loop
            $.ajax({
                url: "test2.aspx/InsertExcelGeo",
                type: "post",
                data: JSON.stringify({ dataArray: dataArray }),
                contentType: "application/json; charset=utf-8",
                dataType: "json",
                success: function (_data) {
                    alert(_data.d);
                },
                error: function (error) {
                    console.log("Error:", error);
                }
            });
        };

        reader.onerror = function (ex) {
            console.log(ex);
        };

        reader.readAsBinaryString(file);
    };
};

Here’s what I changed:

Moved the AJAX call out of the forEach loop and placed it after the loop has finished executing.
The dataArray will collect all sheet data and will be sent in a single AJAX request after the loop.
Added an error callback to the AJAX request to catch and log errors.
This way, you’re making a single AJAX request with all the data from all the sheets.

Please signup or login to give your own answer.

Click here to cancel reply.

Javascript – Get excel data and post to server side using ajax

Answers