I'm using kendo spreadsheet in my project and have a lot of customization so first I'm having some code to prepare grid data and in the end using the below line to set the spread sheet data
kendo.jQuery(this.el.nativeElement).kendoSpreadsheet(gridData);
If i have 3000 rows and 100 columns it takes around ~1 min.
I got a suggestion to use batch but I'm not finding any syntax. Please advice.
Replication steps:
<!DOCTYPE html>
<html>
<head>
<base href="https://demos.telerik.com/kendo-ui/spreadsheet/index">
<style>html { font-size: 14px; font-family: Arial, Helvetica, sans-serif; }</style>
<title></title>
<script src="https://code.jquery.com/jquery-3.4.1.min.js"></script>
<script src="https://kendo.cdn.telerik.com/2023.2.718/js/jszip.min.js"></script>
<script src="https://kendo.cdn.telerik.com/2023.2.718/js/kendo.all.min.js"></script>
<link rel="stylesheet" href="https://kendo.cdn.telerik.com/themes/6.6.0/default/default-ocean-blue.css">
<script src="https://kendo.cdn.telerik.com/2023.2.718/js/angular.min.js"></script>
</head>
<body>
<div id="example">
<div id="spreadsheet" style="width: 100%;"></div>
<script>
$(function() {
function getData(){
var rows = [];
var cells = []
for(var i = 0; i< 250; i++){
var cell = {
index:i,
value: `Cell #${i}`,
bold: "true",
borderBottom:{size:2,color:"#a9a9a9"}
}
cells.push(cell);
}
for(var i = 0; i < 3500; i++){
var row = {cells:cells}
rows.push(row);
}
return {
sheets:[
{
name: "Food Order",
rows: rows
}
]
}
}
var data = getData();
console.log("SpreaddSheet start", new Date())
var spread = $("#spreadsheet").kendoSpreadsheet().data("kendoSpreadsheet");
spread.fromJSON(data);
var sheet = spread.activeSheet();
//sheet.range("A1:IP3500").background("green");
console.log("SpreaddSheet end", new Date())
});
</script>
</div>
</body>
</html>
This is a show stopper for us to keep using Kendo UI Spreadsheet. We need help from the Telerik experts on this. We may need to do a live session to figure this part out as this is a big hold up for us. Please advise, is there a faster way to format cells rather than running trough them one at a time in for loop.
Please advise.
Hi Janaki,
Below you will find an example of how the sytles can be applied using batch:
var spread = $("#spreadsheet").kendoSpreadsheet().data("kendoSpreadsheet"); spread.fromJSON(data); var sheet = spread.activeSheet(); sheet.batch(function() { sheet.range("A1:IP3500").bold(true); sheet.range("A1:IP3500").borderBottom({size:2,color:"#a9a9a9"}); }, { layout: true });Here you will find a Dojo example based on the code provided in your second reply. As you will see when the styles are applied using batch the Spreadsheet is loaded in a second.
I hope this helps.
Regards,
This code takes more than ~3 min . Each cell has a diff color based on business logic
with range its takes ~10 seconds
Hi Janaki,
Manipulating the cells in such a huge range especially when iterating cell by cell could slow down the performance.
In a scenario where you are first creating the array with the cells and then using the fromJSON method to create the Spreadsheet with the data you can try add the styles to the cells in the data array. This way you will add the colors based on the requirements and will add the data in the Spreadsheet afterwards:
function getData(){ var rows = []; var cells = [] for(var i = 0; i< 250; i++){ var cell = { index:i, value: `Cell #${i}`, color: 'red', borderTop: {size: 2, color: '#006400'}, borderLeft: {size: 2, color: '#006400'}, borderBottom: {size: 2, color: '#006400'}, borderRight: {size: 2, color: '#006400'}, bold: true, comment: "This is a comment", fontFamily: "monospace", fontsize: 16 } cells.push(cell); } for(var i = 0; i < 3500; i++){ var row = {cells:cells} rows.push(row); } return { sheets:[ { name: "Food Order", rows: rows } ] } }I tested such approach and on my side creating a Spreadsheet with 3500 rows and 250 column with different styles applied takes less than 7s . Here is a Dojo example where this is demonstrated - https://dojo.telerik.com/ChuuIlGv.
Regards,
Neli