Load Spreadsheet data based on conditional criteria
Environment
| Product Version | 2021.1.224 |
| Product | Progress® Kendo UI® for jQuery |
Description
How to populate Spreadsheet remote dataSource conditionally by dynamically changing the URL?
Solution
- Initialize the DropDown Widget
- Set up the DropDown's
dataSourceto match the different remote dataSource options of the Spreadsheet - Subscribe to the
selectevent of the DropDown and set 'url' variable to be equal to the name of the selecteddataItem, then pass it to the loadData() function - Initialize the Speadsheet
- Define the loadData() function and in it
- set the parameter to be equal to a local variable url
- define a new
kendo.data.DataSource - get the
activeSheet, then get its_rows._countand_columns._count - use the
sheet.range(0,0,rowsCount,columnsCount).clear()to clear any leftover data from the Spreadsheet - pass the local url variable to the Speadsheet's transport.read.url
- finally in the loadData() use setDataSource to set the dataSource of the activeSheet to be equal to the
kendo.data.DataSource
- set the parameter to be equal to a local variable url
<div id="dropdownlist" style="margin-bottom: 5px"></div>
<div id="spreadsheet" style="width: 100%"></div>
<script>
$(document).ready(function(){
$("#dropdownlist").kendoDropDownList({
dataTextField: "name",
dataValueField: "id",
optionLabel: "Select dataSource...",
dataSource:[
{ id: "1", name:"Products"},
{ id: "2", name:"Customers"}
],
select:function(e){
if(e.dataItem){
var url = `/${e.dataItem.name}`;
loadData(url);
}
}
});
$("#spreadsheet").kendoSpreadsheet({
toolbar:false,
columns:15,
sheets: [{
rows: [{
height: 40,
cells: [
{
bold: "true",
background: "#9c27b0",
textAlign: "center",
color: "white"
},{
bold: "true",
background: "#9c27b0",
textAlign: "center",
color: "white"
},{
bold: "true",
background: "#9c27b0",
textAlign: "center",
color: "white"
},{
bold: "true",
background: "#9c27b0",
textAlign: "center",
color: "white"
},{
bold: "true",
background: "#9c27b0",
textAlign: "center",
color: "white"
}]
}],
columns: [
{ width: 100 },
{ width: 415 },
{ width: 145 },
{ width: 145 },
{ width: 145 }
]
}]
});
function loadData(path){
var url = path;
var spreadsheet = $("#spreadsheet").data("kendoSpreadsheet");
var sheet = spreadsheet.activeSheet();
var rowsCount = sheet._rows._count;
var columnsCount = sheet._columns._count;
sheet.range(0,0,rowsCount,columnsCount).clear();
var dataSource = new kendo.data.DataSource({
transport: {
read: function(options) {
$.ajax({
url: "https://demos.telerik.com/kendo-ui/service"+url,
dataType: "jsonp",
success: function(result) {
options.success(result);
},
error: function(result) {
options.error(result);
}
});
}
},
batch: true
});
sheet.setDataSource(dataSource);
}
});
</script>