Slow to hide/unhide columns/rows

10 posts, 0 answers
  1. Sebastian
    Sebastian avatar
    4 posts
    Member since:
    Jan 2016

    Posted 01 Feb Link to this post

    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

  2. Sebastian
    Sebastian avatar
    4 posts
    Member since:
    Jan 2016

    Posted 01 Feb in reply to Sebastian Link to this post

    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

  3. UI for ASP.NET MVC is VS 2017 Ready
  4. Sebastian
    Sebastian avatar
    4 posts
    Member since:
    Jan 2016

    Posted 02 Feb in reply to Sebastian Link to this post

    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

  5. Alex Gyoshev
    Admin
    Alex Gyoshev avatar
    2500 posts

    Posted 03 Feb Link to this post

    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
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
  6. Pankaj
    Pankaj avatar
    16 posts
    Member since:
    Mar 2015

    Posted 26 Jul in reply to Alex Gyoshev Link to this post

    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.

     

  7. Peter Milchev
    Admin
    Peter Milchev avatar
    139 posts

    Posted 28 Jul Link to this post

    Hello Pankaj,

    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
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
  8. Pankaj
    Pankaj avatar
    16 posts
    Member since:
    Mar 2015

    Posted 28 Jul in reply to Peter Milchev Link to this post

    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

     

     

     

     

     

     

     

     

    ....

     

     

     

     

     

     

                   

  9. Peter Milchev
    Admin
    Peter Milchev avatar
    139 posts

    Posted 01 Aug Link to this post

    Hello 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
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
  10. Pankaj
    Pankaj avatar
    16 posts
    Member since:
    Mar 2015

    Posted 01 Aug in reply to Peter Milchev Link to this post

    Hello Peter,

                       It is working fine and I am thankful to you for your wonderful support.

    Thanks & Regards,

    Pankaj

                      

  11. Peter Milchev
    Admin
    Peter Milchev avatar
    139 posts

    Posted 02 Aug Link to this post

    Hello 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
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
Back to Top
UI for ASP.NET MVC is VS 2017 Ready