Batch Highlighting Cells

[Marc]

I'd like to highlight cells, in a range.forEachCell method, but each cell has to have a different highlight (background colour) based on some rules. It's quite slow. Is there a way to do this quicker?

Thanks,

Marc

[Stefan]

Hello Marc,

I can suggest checking the background method of the Spreadsheet range. This is a faster option to set the background:

http://docs.telerik.com/kendo-ui/api/javascript/spreadsheet/range#methods-background

I made an example to demonstrate this with a bigger range:

http://dojo.telerik.com/iQAYA

If this is not an option, please send a runnable example demonstrating the current implementation, I and will investigate if it can be optimised. 

Regards,
Stefan
Telerik by Progress

[Marc]

Hi Stefan,

To highlight the cell, I am using the .background method. However, my problem is that I am selecting a massive range (5000 cells for instance), and each cell has to have a check to determine which background colour is applied. Is there any other way around this?

Marc

[Marc]

Hi, 

Sorry I did not read your request for an example. I have provided one below, I believe you should be able to access it, although it's the first time I've used the Dojo site so hopefully you can see it.

http://dojo.telerik.com/@marclazell/AkOwi

Thanks

Marc

[Stefan]

Hello Marc,

Thank you for the provided example.

I observed the same behaviour on my end. As this is calling the background method on 10 000 cells, the operations are slow as they require many DOM manipulations.

I can suggest based on the expected data, to set only the first colour on the whole range, and then based on the data to only change it on the cells that are different as this will greatly reduce the numbers of times of background method is called and also the loading time:

http://dojo.telerik.com/oHuXe

I hope this will help speed up the process.

Regards,
Stefan
Telerik by Progress

[Marc]

Hi Stefan,

Thanks for the investigation - you came to the same kind of conclusion I did. Although - your suggestion is genius. Thank you!

I will check this, and as long as the first colour applied to all cells is the one which is statistically applied more often than not with the logic, then this should help.

Thanks again

Marc

 

[Gabriel]

please ignore this comment

[Gabriel]

This solution is still slow (+increase CPU usage) for example, 255 color ranges on a sheet describing 31x24 values. 

Do you have a workaround similar with setting range values ( values - API Reference - Kendo UI Range - Kendo UI for jQuery (telerik.com)) to set formula, background and format?

Maybe this article helps you - nice workaround described here: Spreadsheet set background color of formula cells - Kendo UI for jQuery (telerik.com)

Thank you.

[Angel Petrov]

Hello,

The last linked article does show how to set the background. Here are examples one can use to set a formula and format in a similar fashion. Hope the provided information proves helpful.

Regards,
Angel Petrov
Progress Telerik

[Gabriel]

Hello Angel,

I continued this topic because you offered a workaround to the slow execution of the range.forEachCell function.

My issue is similar, unable to use this function at all because it is too slow.

Just try the following and let me know:

1. Go to the link mentioned above (http://dojo.telerik.com/oHuXe) and replace the script with this one:

  <script type="text/javascript" charset="utf-8">
      $("#spreadsheet").kendoSpreadsheet();
      var spreadsheet = $("#spreadsheet").data("kendoSpreadsheet");

      var sheet = spreadsheet.activeSheet();

      var range = sheet.range("A1:A255");
      range.background("blue")

var start = performance.now();
      range.forEachCell(function (row, column, value) {
        var thisCell = "R" + row + "C" + column;    
          sheet.range(thisCell+ ":" + thisCell).background("red");
      })
var end = performance.now();
console.log(`Performance = ${end - start}`);
    </script>

2. Execute the script using the 2017 KENDO UI version and check the console logs for the performance value. My laptop shows Performance = 546.6999999880791 

3. Execute the script using the latest version of  KENDO UI and check the console logs for the performance value. Here it shows 1724.0999999940395

Looks like something is broken here, what could be the issue?

[Angel Petrov]

Hello,

The problem looks really strange. If the entire range gets styles there is no performance problem as you can check from this dojo. But if the cells are traversed and re-styled this takes additional time. As for why it takes more compared to the older versions it would be difficult to say. When adding features/improvements to the component probably some part of the logic does something additional. To find the exact culprit we should check all changes from 2017 till now. 

Can you please elaborate what is the idea behind obtaining a range and then traversing each cell, creating a new range and re-styling it? Is using the initial range an option?

Regards,
Angel Petrov
Progress Telerik

[Gabriel]

Hi,

before spreadsheet we have a data filtering area, therefore any initial visualization is replaced with new data dynamically.

The sheet is showing monthly activity (24 x 31) with different background colors for headers, data cells, totals and averages rows.

The workaround today is to prepare the data in advance, decrease the color ranges from 255 to 30 and apply the background in a loop to avoid traversing cells. 

 

something similiar with these:

<script type="text/javascript" charset="utf-8">
      $("#spreadsheet").kendoSpreadsheet();
      var spreadsheet = $("#spreadsheet").data("kendoSpreadsheet");

      var sheet = spreadsheet.activeSheet();

      var ranges = [];

const hr=['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE'];


      let color = 0;
      for(let r=1;r<=24;r++)
      {
        for(let c=1;c<=31;c++)
        {
          if(color>255) color=0;
//let cell = "R"+r+"C"+c;
          let cell = hr[c-1]+r;
          if (color>=ranges.length) ranges.push(cell);
          else ranges[color] = ranges[color] + ","+cell;

          color++;
        }
      }

    var start = performance.now();      
        for(color = 0;color<255;color++)
          sheet.range(ranges[color]).background("RGB(255,"+color+","+color+")");
    var end = performance.now();
    console.log(`Performance = ${end - start}`);

    </script>

 

 

[Angel Petrov]

Hello,

The described approach does seem like a possible solution. As long as it does the job needed I do not see any problems in integrating it into the real application.

Regards,
Angel Petrov
Progress Telerik

[Gabriel]

Hi Angel,

This workaround decreases the visualization quality which might be an issue.

Decreasing 3 times the speed of the sheet.range function is a quality issue and need further investigation.

What will happen in 2 years, will run 6 times slower? Is this a general issue or it is only on this control? Should I create a ticket on this issue? 

Thank you.

[Angel Petrov]

Hi,

We have contacted our developers in regards of the decreased performance. We will update this thread with more info as soon as possible.

Regards,
Angel Petrov
Progress Telerik

[Angel Petrov]

Hello,

As promised I am writing back with more info. The described approach although correct will trigger redrawing multiple times. This can be avoiding by executing the script in batch mode. Here is a modified dojo that demonstrates this approach.

Regards,
Angel Petrov
Progress Telerik

[Gabriel]

Great solution!