Hi,
we're evaluating the Spreadsheet component for our solution and while we love most of it, we are having a blocking issue in the hide/unhide feature
In our case, we have a kind of "control panel with button" around the spreadsheet, which allows the user to hide/unhide some blocks of (potentially non contiguous rows or columns in one click). E.g. let's say you click a button and you hide rows 1, 7, 15, and 50 and columns E, F and Z.
The Spreadsheet client side API exposes single col/row hide methods and the overall time is very high
(e.g. 3 seconds to hide like some 40-60 rows)
Do you think there is or will be a workaround/fix on this?
thanks in advance
Sebastian
9 Answers, 1 is accepted
Rank 1
adding more info on point above.
Please find attached our reference XLS file (please note you might have a problem opening it, since SUMPRODUCT seem to throw an exception - we workedaround it by creating a custom SUMPRODUCT kendo JS implementation)
If we try, e.g. to hide all ODD rows in sheets 2, 3, 4 with one click.. it'll take a lot of time (6 seconds or more).
On the other hand, trying to hide a similar number of rows in the same xls file in e.g. Google Sheets is accomplished in no time.
Could there be a workaround for this?
thanks a lot,
Sebastian
Rank 1
Hello,
I'd like to report that we managed to achieve GREAT results on multiple row hiding by using the "filter" functionality.
So, row-wise, we're now ok. Hiding multiple columns (e.g. 100 adjacent cols) by invoking hideColumn repeatedly still proves very slow though.
kind regards,
Sebastian
Hello Sebastian,
The performance for hiding columns is hurt by the recalculation of the spreadsheet layout after each method call.
The sheet object of the spreadsheet has a batch method that allows multiple operations to trigger a change event once. You can use it like this:
var sheet = spreadsheet.activeSheet();
sheet.batch(function() {
for (var i = 0; i < 50; i++) {
sheet.hideColumn(i);
sheet.hideRow(i); /* if hiding rows is cleaner, no need to filter them */
}
}, { layout: true });
It appears that the method is not documented, although it is a public API -- we'll address this shortly.
Regards,Alex Gyoshev
Telerik
Rank 1
Veteran
i am coping data in kendo spreadsheet cell but it is taking too much time as because evry time firing change event. I have used batch method but still not getting any improvement.
function OnPeriodChange(e) {
var period = $("#ddlPeriodEnding").val();
var cellVal = $("#ddlcells").val();
var cellText = $("#ddlcells").data("kendoDropDownList").text();
var spreadsheet = $("#spreadsheet").data("kendoSpreadsheet");
var sheet = spreadsheet.activeSheet();
var range = sheet.selection();
var values = range.values();
sheet.batch(function () {
FillBalanceSheet(period, cellVal, cellText, sheet);
//sheet.range(cellVal + "1" + ":" + cellText + "200").select();
}, { layout: false });
//e.preventDefault();
}
even I am also using change metod in batch
sheet.batch(function () {
sheet.bind("change", function (e) {
var range = sheet.selection();
var values = range.values();
if (range._ref.bottomRight.row == 0) {
if (values.length == 1 && values[0][0] != null) {
$("#ddlPeriodEnding").data("kendoDropDownList").value(values[0][1]);
$("#ddlPeriodEnding").data("kendoDropDownList").text(values[0][0]);
}
var selCell = cols[range._ref.bottomRight.col];
var colPos = getKey(selCell, colText);
if (colPos != undefined) {
$("#ddlcells").data("kendoDropDownList").text(colPos);
//var x = $('#ddlcells').data("kendoDropDownList").dataItem(colPos);
// $("#ddlcells").data("kendoDropDownList").value(x.Value);
}
else {
var selNextCell = cols[range._ref.bottomRight.col + 1];
var colPosnext = getKey(selNextCell, colText);
if (colPosnext != undefined)
{
$("#ddlcells").data("kendoDropDownList").text(colPosnext);
//var x = $('#ddlcells').data("kendoDropDownList").dataItem(colPosnext);
// $("#ddlcells").data("kendoDropDownList").value(x.Value);
}
}
}
});
}, { layout: false });
please suggest.
Would you please open a separate support ticket or forum thread and include a Dojo example and detailed steps to reproduce the issue? We would also appreciate if you add some clarification on the FillBalanceSheet function and also why you subscribe to the change handler in the batch method. Thank you in advance.
As for the batch method from the original post I would like to add the link to the documentation article for it.
Regards,
Peter Milchev
Telerik by Progress
Rank 1
Veteran
Hello Peter,
Every time when I ask any question, I did not get any good and valuable answer. I got answer like please open separate post. What is this. Is it really matter to ask question in separate post. I will only say, Please provide the answer of question.
My problem is, I am getting data from database in dropdown change and want to show this data in kendo UI spreadsheet.
On Dropdown Change event, One function is called and in this function through ajax call I am calling MVC controller method and in success on ajax call putting data in Kendo UI spreadsheet. It is about 60 60 rows and its taking 10 second to show sheet with data.
Here is the function. on dropdown change.
function OnPeriodChange(e) {
var period = $("#ddlPeriodEnding").val();
var cellVal = $("#ddlcells").val();
var cellText = $("#ddlcells").data("kendoDropDownList").text();
var spreadsheet = $("#spreadsheet").data("kendoSpreadsheet");
var sheet = spreadsheet.activeSheet();
var range = sheet.selection();
var values = range.values();
FillBalanceSheet(period, cellVal, cellText, sheet);
e.preventDefault();
}
and in FillBalanceSheet function
function FillBalanceSheet(period, cellVal, cellText, sheet)
{
var periodText = $("#ddlPeriodEnding").data("kendoDropDownList").text();
$("#divLoading").show();
$.ajax({
url: CreateBalanceSheet,
data: { Period: period },
success: function (data) {
if (data != null && data.length > 0) {
sheet.range(cellVal + "1" + ":" + cellText + "1").merge();
sheet.range(cellText + "1").value(period);
sheet.range(cellVal + "1").value(periodText);
sheet.range(cellVal + "1" + ":" + cellText + "1").background("rgb(255, 0, 102)");
sheet.range(cellVal + "1" + ":" + cellText + "1").color("rgb(255, 255, 255)");
sheet.range(cellVal + "1" + ":" + cellText + "1").textAlign("center");
sheet.range(cellText + "2").value(data[0].CashAndLiquidAssets);
sheet.range(cellText + "2").background("rgb(255,255,255)");
sheet.range(cellText + "2").color("rgb(0,62,117)");
sheet.range(cellText + "2").textAlign("right");
sheet.range(cellText + "3").value(data[0].TradeAR);
sheet.range(cellText + "3").background("rgb(229,243,255)");
sheet.range(cellText + "3").color("rgb(0,62,117)");
sheet.range(cellText + "3").textAlign("right");
sheet.range(cellText + "4").value(data[0].OtherAR);
sheet.range(cellText + "4").background("rgb(255,255,255)");
sheet.range(cellText + "4").color("rgb(0,62,117)");
sheet.range(cellText + "4").textAlign("right");
sheet.range(cellText + "5").value(data[0].Inventory);
sheet.range(cellText + "5").background("rgb(229,243,255)");
sheet.range(cellText + "5").color("rgb(0,62,117)");
sheet.range(cellText + "5").textAlign("right");
....
....
sheet.range(cellText + "57").value(data[0].NonRecurringItems);
sheet.range(cellText + "57").background("rgb(255,255,255)");
sheet.range(cellText + "57").color("rgb(0,62,117)");
sheet.range(cellText + "57").textAlign("right");
}
}
});
}
I hope, I have explain all the steps clearly. and if you still want separate post. Can you please send me steps?
Thanks
Pankaj
....
I would suggest you to use the .batch function inside the success callback of the ajax request of the FillBalanceSheet function:
function FillBalanceSheet(period, cellVal, cellText, sheet) { var periodText = $("#ddlPeriodEnding").data("kendoDropDownList").text(); $("#divLoading").show(); $.ajax({ url: CreateBalanceSheet, data: { Period: period }, success: function (data) { if (data != null && data.length > 0) { sheet.batch(function () { sheet.range(cellVal + "1" + ":" + cellText + "1").merge(); // the other cell manipulations }, { layout: false }); } } });}I would also suggest you to chain the setter method in order to prevent the excessive calling of the range method:
sheet.range(cellVal + "1" + ":" + cellText + "1").merge();sheet.range(cellVal + "1" + ":" + cellText + "1").background("rgb(255, 0, 102)");sheet.range(cellVal + "1" + ":" + cellText + "1").color("rgb(255, 255, 255)");sheet.range(cellVal + "1" + ":" + cellText + "1").textAlign("center");could become:
sheet.range(cellVal + "1" + ":" + cellText + "1").merge() .background("rgb(255, 0, 102)") .color("rgb(255, 255, 255)") .textAlign("center");If that does not help your case you could open an official support ticket. To submit a support ticket, you can use the top navigation Support -> Get support link. Then you have to select a product and search for already existing issues using the search functionality. If there are no relevant options for you, you can then click "Submit support ticket" button at the bottom of the page, which allows you to submit a support ticket, bug or feature request.
You can also do the same through the Products & Subscriptions menu, selecting a product you purchased and choosing "Submit support ticket" from the right hand menu.
Please note that the submit support ticket button appears after you make a detailed search and look through the results (at the bottom of the page). Here is a screenshot.
Regards,
Peter Milchev
Telerik by Progress
Rank 1
Veteran
Hello Peter,
It is working fine and I am thankful to you for your wonderful support.
Thanks & Regards,
Pankaj
We are glad that we were able to help solving the issue and now everything is working fine.
Regards,
Peter Milchev
Telerik by Progress