Handling Events In Kendo SpreadSheet Using jQuery

Introduction

Basically, JavaScript interacts with HTML though events, and different types of events will be fired when the HTML page loads. Similarly, when the user interacts with Kendo spreadsheets different types of events will be fired. We will see mostly used events in Kendo spreadsheets.

This article is in continuation of my previous article, where I have explained how to do remote data binding in Kendo spreadsheets.

Kendo SpreadSheet.html

<!DOCTYPE html>
<html>
<head>
<style>
html {
font-size: 14px;
font-family: Arial, Helvetica, sans-serif;
}
</style>
<title></title>
<link rel="stylesheet" href="https://kendo.cdn.telerik.com/2018.3.1017/styles/kendo.common-material.min.css" />
<link rel="stylesheet" href="https://kendo.cdn.telerik.com/2018.3.1017/styles/kendo.material.min.css" />
<link rel="stylesheet" href="https://kendo.cdn.telerik.com/2018.3.1017/styles/kendo.material.mobile.min.css" />
<script src="https://kendo.cdn.telerik.com/2018.3.1017/js/jquery.min.js"></script>
<script src="https://kendo.cdn.telerik.com/2018.3.1017/js/jszip.min.js"></script>
<script src="https://kendo.cdn.telerik.com/2018.3.1017/js/kendo.all.min.js"></script>
</head>
<body>
<div id="example">
<div class="box wide">
<div class="box-col">
<h4>Save data changes</h4>
<ul class="options">
<li>
<button class="k-button" id="save">Save changes</button>
<button class="k-button" id="cancel">Cancel changes</button>
</li>
</ul>
</div>
</div>
<div id="spreadsheet" style="width: 100%"></div>
<script>
$(function () {
var dataSource = new kendo.data.DataSource({
transport: {
read: onRead,
},
schema: {
model: {
id: "ProductID",
fields: {
productID: { type: "number" },
productName: { type: "string" },
price: { type: "number" },
tax: { type: "number" }
}
}
}
});
$("#spreadsheet").kendoSpreadsheet({
columns: 20,
rows: 100,
toolbar: false,
sheetsbar: false,
sheets: [{
name: "Products",
dataSource: dataSource,
rows: [{
height: 40,
cells: [
{
bold: "true",
background: "#9c27b0",
textAlign: "center",
color: "white",
title: "ID"
}, {
bold: "true",
background: "#9c27b0",
textAlign: "center",
color: "white",
title:"Product Name"
}, {
bold: "true",
background: "#9c27b0",
textAlign: "center",
color: "white",
title:"Price"
}, {
bold: "true",
background: "#9c27b0",
textAlign: "center",
color: "white"
}, ]
}],
columns: [
{ width: 100 },
{ width: 415 },
{ width: 145 },
{ width: 145 },
{ width: 145 }
]
}]
});
function onRead(options) {
$.ajax({
url: "http://localhost:11207/api/Products/ProductDetails",
dataType: "json",
success: function (result) {
options.success(result);
},
error: function (result) {
options.error(result);
}
});
}
</script>
</div>
</body>
</html>

SpreadSheet in browser

Events

render

This event will be fired when the spreadsheet is completely rendered in the page.

$("#spreadsheet").kendoSpreadsheet({
render: onRender,
columns: 20,
rows: 100,
toolbar: false,
sheetsbar: false,
sheets: [{
name: "Products",
dataSource: dataSource,
filter: {
ref: "A1:D3",
columns: []
},
rows: [{
height: 40,
cells: [
{
bold: "true",
background: "#9c27b0",
textAlign: "center",
color: "white",
title: "ID"
}, {
bold: "true",
background: "#9c27b0",
textAlign: "center",
color: "white",
title: "Product Name"
}, {
bold: "true",
background: "#9c27b0",
textAlign: "center",
color: "white",
title: "Price"
}, {
bold: "true",
background: "#9c27b0",
textAlign: "center",
color: "white"
},]
}],
columns: [
{ width: 100 },
{ width: 415 },
{ width: 145 },
{ width: 145 },
{ width: 145 }
]
}]
});

function onRender(e) {
console.log("Render Event")
}

From the above code it is obvious the onRender function will be fired, when the spreadsheet renders in the page.

select

This event will be fired when there is a selection change in the spreadsheet.

$("#spreadsheet").kendoSpreadsheet({
select: onSelect,
columns: 20,
rows: 100,
toolbar: false,
sheetsbar: false,
sheets: [{
name: "Products",
dataSource: dataSource,
filter: {
ref: "A1:D3",
columns: []
},
rows: [{
height: 40,
cells: [
{
bold: "true",
background: "#9c27b0",
textAlign: "center",
color: "white",
title: "ID"
}, {
bold: "true",
background: "#9c27b0",
textAlign: "center",
color: "white",
title: "Product Name"
}, {
bold: "true",
background: "#9c27b0",
textAlign: "center",
color: "white",
title: "Price"
}, {
bold: "true",
background: "#9c27b0",
textAlign: "center",
color: "white"
},]
}],
columns: [
{ width: 100 },
{ width: 415 },
{ width: 145 },
{ width: 145 },
{ width: 145 }
]
}]
});

function onSelect(e) {
console.log("on select event the cell value ", e.range.value())
}

onSelect function will be fired when there is a selection change in the spreadsheet.

change

This event will be fired when there is any change in the spreadsheet from its initial state.

$("#spreadsheet").kendoSpreadsheet({
change: onChange,
columns: 20,
rows: 100,
toolbar: false,
sheetsbar: false,
sheets: [{
name: "Products",
dataSource: dataSource,
filter: {
ref: "A1:D3",
columns: []
},
rows: [{
height: 40,
cells: [
{
bold: "true",
background: "#9c27b0",
textAlign: "center",
color: "white",
title: "ID"
}, {
bold: "true",
background: "#9c27b0",
textAlign: "center",
color: "white",
title: "Product Name"
}, {
bold: "true",
background: "#9c27b0",
textAlign: "center",
color: "white",
title: "Price"
}, {
bold: "true",
background: "#9c27b0",
textAlign: "center",
color: "white"
},]
}],
columns: [
{ width: 100 },
{ width: 415 },
{ width: 145 },
{ width: 145 },
{ width: 145 }
]
}]
});

function onChange(e) {
console.log("on change event the cell value ", e.range.value())
}

onChange() will be fired when there is a change in cell data; we are passing the updated event object to the function to get an updated value when there is a change.

Column Events

a. hideColumn – This event will be fired when the column is hidden using the context menu in the spreadsheet.

b. deleteColumn – This event will be fired when column is deleted using the context menu in the spreadsheet.

$("#spreadsheet").kendoSpreadsheet({
hideColumn: onHideColumn,
deleteColumn: onDeleteColumn,
columns: 20,
rows: 100,
toolbar: false,
sheetsbar: false,
sheets: [{
name: "Products",
dataSource: dataSource,
filter: {
ref: "A1:D3",
columns: []
},
rows: [{
height: 40,
cells: [
{
bold: "true",
background: "#9c27b0",
textAlign: "center",
color: "white",
title: "ID"
}, {
bold: "true",
background: "#9c27b0",
textAlign: "center",
color: "white",
title: "Product Name"
}, {
bold: "true",
background: "#9c27b0",
textAlign: "center",
color: "white",
title: "Price"
}, {
bold: "true",
background: "#9c27b0",
textAlign: "center",
color: "white"
},]
}],
columns: [
{ width: 100 },
{ width: 415 },
{ width: 145 },
{ width: 145 },
{ width: 145 }
]
}]
});

function onHideColumn(e) {
console.log("Hide column event")
}
function onDeleteColumn(e) {
console.log("Delete column event")
}

onHideColumn() and onDeleteColumn() will be fired when we use hide and delete the column from the context menu in the spreadsheet.

Row events

Similar to column we have row events,

a. hideRow – This event will be fired when the row is hidden using the context menu in the spreadsheet.

b. deleteRow – This event will be fired when row is deleted using the context menu in the spreadsheet.

$("#spreadsheet").kendoSpreadsheet({
hideRow: onHideRow,
deleteRow: onDeleteRow,
columns: 20,
rows: 100,
toolbar: false,
sheetsbar: false,
sheets: [{
name: "Products",
dataSource: dataSource,
filter: {
ref: "A1:D3",
columns: []
},
rows: [{
height: 40,
cells: [
{
bold: "true",
background: "#9c27b0",
textAlign: "center",
color: "white",
title: "ID"
}, {
bold: "true",
background: "#9c27b0",
textAlign: "center",
color: "white",
title: "Product Name"
}, {
bold: "true",
background: "#9c27b0",
textAlign: "center",
color: "white",
title: "Price"
}, {
bold: "true",
background: "#9c27b0",
textAlign: "center",
color: "white"
},]
}],
columns: [
{ width: 100 },
{ width: 415 },
{ width: 145 },
{ width: 145 },
{ width: 145 }
]
}]
});

function onHideRow(e) {
console.log("Hide Row event")
}
function onDeleteRow(e) {
console.log("Delete Row event")
}

onHideRow() and onDeleteRow() will be fired when the user hides or deletes the row using the context menu in the spreadsheet as shown in the below figure.

pdfExport

This event will be fired when the user initiates the PDF export

$("#spreadsheet").kendoSpreadsheet({
pdfExport: onPdfExport,
columns: 20,
rows: 100,
toolbar: false,
sheetsbar: false,
sheets: [{
name: "Products",
dataSource: dataSource,
filter: {
ref: "A1:D3",
columns: []
},
rows: [{
height: 40,
cells: [
{
bold: "true",
background: "#9c27b0",
textAlign: "center",
color: "white",
title: "ID"
}, {
bold: "true",
background: "#9c27b0",
textAlign: "center",
color: "white",
title: "Product Name"
}, {
bold: "true",
background: "#9c27b0",
textAlign: "center",
color: "white",
title: "Price"
}, {
bold: "true",
background: "#9c27b0",
textAlign: "center",
color: "white"
},]
}],
columns: [
{ width: 100 },
{ width: 415 },
{ width: 145 },
{ width: 145 },
{ width: 145 }
]
}]
});

function onPdfExport(e) {
console.log("Export to PDF event, file name ", e.options.pdf.fileName)
}

onPdfExport() will be fired when the pdf export happens from the spreadsheet, from the event object we can get all the spreadsheet details, from the above which we used to print pdf filename in console.

Complete Code

<!DOCTYPE html>
<html>
<head>
<style>
html {
font-size: 14px;
font-family: Arial, Helvetica, sans-serif;
}
</style>
<title></title>
<link rel="stylesheet" href="https://kendo.cdn.telerik.com/2018.3.1017/styles/kendo.common-material.min.css" />
<link rel="stylesheet" href="https://kendo.cdn.telerik.com/2018.3.1017/styles/kendo.material.min.css" />
<link rel="stylesheet" href="https://kendo.cdn.telerik.com/2018.3.1017/styles/kendo.material.mobile.min.css" />
<script src="https://kendo.cdn.telerik.com/2018.3.1017/js/jquery.min.js"></script>
<script src="https://kendo.cdn.telerik.com/2018.3.1017/js/jszip.min.js"></script>
<script src="https://kendo.cdn.telerik.com/2018.3.1017/js/kendo.all.min.js"></script>
</head>
<body>
<div id="example">
<button id="exportPDF" class="k-button">Export to PDF</button>
<div id="spreadsheet" style="width: 100%"></div>
<script>
function onRender(e) {
console.log("Render Event")
}
function onSelect(e) {
console.log("on select event the cell value ", e.range.value())
}
function onChange(e) {
console.log("on change event the cell value ", e.range.value())
}
function onPdfExport(e) {
console.log("Export to PDF event, file name ", e.options.pdf.fileName)
}
function onHideColumn(e) {
console.log("Hide column event")
}
function onDeleteColumn(e) {
console.log("Delete column event")
}
function onHideRow(e) {
console.log("Hide Row event")
}
function onDeleteRow(e) {
console.log("Delete Row event")
}
$(function () {
var dataSource = new kendo.data.DataSource({
transport: {
read: onRead,
},
schema: {
model: {
id: "ProductID",
fields: {
productID: { type: "number" },
productName: { type: "string" },
price: { type: "number" },
tax: { type: "number" }
}
}
}
});
$("#spreadsheet").kendoSpreadsheet({
render: onRender,
select: onSelect,
change: onChange,
pdfExport: onPdfExport,
hideColumn: onHideColumn,
deleteColumn: onDeleteColumn,
unhideRow: onUnhideRow,
hideRow: onHideRow,
deleteRow: onDeleteRow,
pdfExport: onPdfExport,
columns: 20,
rows: 100,
toolbar: false,
sheetsbar: false,
sheets: [{
name: "Products",
dataSource: dataSource,
filter: {
ref: "A1:D3",
columns: []
},
rows: [{
height: 40,
cells: [
{
bold: "true",
background: "#9c27b0",
textAlign: "center",
color: "white",
title: "ID"
}, {
bold: "true",
background: "#9c27b0",
textAlign: "center",
color: "white",
title: "Product Name"
}, {
bold: "true",
background: "#9c27b0",
textAlign: "center",
color: "white",
title: "Price"
}, {
bold: "true",
background: "#9c27b0",
textAlign: "center",
color: "white"
},]
}],
columns: [
{ width: 100 },
{ width: 415 },
{ width: 145 },
{ width: 145 },
{ width: 145 }
]
}]
});
function onRead(options) {
$.ajax({
url: "http://localhost:11207/api/Products/ProductDetails",
dataType: "json",
success: function (result) {
options.success(result);
},
error: function (result) {
options.error(result);
}
});
}
});
$("#exportPDF").on('click', function (e) {
exportPDF(e);
})
function exportPDF(e) {
var spreadsheet = $("#spreadsheet").data("kendoSpreadsheet");
spreadsheet.saveAsPDF({ area: "selection" });
}
//
</script>
</div>
</body>
</html>

Summary

We have seen how to initialize Kendo spreadsheet control and handle the events in the control.

I hope you have enjoyed this article. Your valuable feedback, questions, or comments about this article are always welcomed.