How to import and append an excel file in kendo grid using JQuery?

Question

I have a kendo grid and I want to import and append an excel file to my kendo grid. For example, if I have 3 rows in my kendo grid, then after importing the excel file containing 2 rows, the grid should have total of 5 rows data. I have tried importing the excel file but unable to append the file to the grid. Is that possible to do so? Any help will be really appreciated.

I tried to read the excel file and load rows in the grid one by one and then in the last did this.

              var grid = $("#grid").data("kendoGrid");
              var griddataSource = grid.dataSource._data;
              // Assuming the structure of Excel file matches the grid's schema
              for (var i = 1; i < rows.length; i++) {
                var row = rows[i];
                var dataItem = {
                  ProductID: row[0],
                  ProductName: row[1],
                  Price: row[2],
                };
                
                // Add the new data item to the grid's data source
                grid.dataSource.add(dataItem);
              }
              );
              $("#grid").data("kendoGrid").dataSource.data([]);
              $("#grid").data("kendoGrid").dataSource.data([griddataSource]);
              grid.refresh();`
            
I also tried another way using HTML table, with the help of below link but it also didnt work and I also didnt understand how they are using table.

[enter link description here][1]
https://www.aspsnippets.com/Articles/2499/Read-Parse-Excel-File-XLS-and-XLSX-using-jQuery/

Answer 1

You need to use grid method setDataSource() in order to have the grid rebind to the new data. Please try the following.

var grid = $("#grid").data("kendoGrid");
var griddataSource = grid.dataSource;  // reference to the dataSource itself rather than its data
// Assuming the structure of Excel file matches the grid's schema
for (var i = 1; i < rows.length; i++) {
  var row = rows[i];
  var dataItem = {
    ProductID: row[0],
    ProductName: row[1],
    Price: row[2],
  };
            
  // Add the new data item to the grid's data source
  griddataSource.add(dataItem);
} 

grid.setDataSource(griddataSource);  // Replace the grid's datasource and rebind it

Answer 2

Make sure that the excel file contains the same columns as the grid:-

$("#upload").on("click", function () {
    var fileUpload = $("#fileUpload")[0];

    //Validate whether File is valid Excel file.
    var regex = /^([a-zA-Z0-9\s_\\.\-:])+(.xls|.xlsx)$/;
    if (regex.test(fileUpload.value.toLowerCase())) {
        if (typeof FileReader != "undefined") {
            var reader = new FileReader();

            //For Browsers other than IE.
            if (reader.readAsBinaryString) {
                reader.onload = function (e) {
                    ProcessExcel(e.target.result);
                };
                reader.readAsBinaryString(fileUpload.files[0]);
            } else {
                //For IE Browser.
                reader.onload = function (e) {
                    var data = "";
                    var bytes = new Uint8Array(e.target.result);
                    for (var i = 0; i < bytes.byteLength; i++) {
                        data += String.fromCharCode(bytes[i]);
                    }
                    ProcessExcel(data);
                };
                reader.readAsArrayBuffer(fileUpload.files[0]);
            }
        } else {
            alert("Browser does not support HTML5.");
        }
    } else {
        alert("Upload a valid Excel file.");
    }
});
function ProcessExcel(data) {
    var displayedData = $("#grid").data().kendoGrid.dataSource.view();

    //Read the Excel File data.
    var workbook = XLSX.read(data, {
        type: "binary",
    });

    var firstSheet = workbook.SheetNames[0];

    //Read all rows from First Sheet into an JSON array.
    var excelRows = XLSX.utils.sheet_to_row_object_array(
        workbook.Sheets[firstSheet]
    );

    // To remove the unwanted row appearing in excel "__rowNum"
    var modifiedArr = excelRows.map(({ __rowNum__, ...rest }) => ({ ...rest }))


    for (var i = 0; i < modifiedArr.length; i++) {
        $("#grid").data("kendoGrid").dataSource.add(modifiedArr[i]);
    }

    $("#grid").data("kendoGrid").refresh();
}

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<input type="file" id="fileUpload" />
<input type="button" id="upload" value="Upload" />
<div id="dvExcel"></div>
<div id="grid"></div>