Improve performance of slowly loading Spreadsheet
PROBLEM
Loading a large amount of data in the Spreadsheet might result in a slow page loading.
DESCRIPTION
The issue is caused by the rendering of the control itself and not the data. This can be confirmed by adding a breakpoint in the PageInit, PagePreRenderComplete and PageUnload events and the most of the processing time would be between PagePreRenderComplete and PageUnload.
SOLUTION
The solution is to save the data in JSON, pass it to the client and use the fromJSON() method of the underlying Kendo UI Spreadsheet. The following example with around 800,000 cells would be loaded for a few seconds while if the same amount of cells were loaded via a Provider or added directly to the Spreadsheet, the time would be a few minutes.
<asp:HiddenField runat="server" ID="HiddenField1" />
<telerik:RadSpreadsheet runat="server" ID="RadSpreadsheet1"></telerik:RadSpreadsheet>
<script>
function pageLoadHandler() {
var spreadsheet = $find("<%= RadSpreadsheet1.ClientID %>");
var value = $get("<%= HiddenField1.ClientID %>").value;
var valueAsJSON = JSON.parse(value);
spreadsheet.get_kendoWidget().fromJSON(valueAsJSON);
}
Sys.Application.add_load(pageLoadHandler);
</script>
protected void Page_Init(object sender, EventArgs e)
{
var workbook = new Telerik.Web.Spreadsheet.Workbook();
PopulateSheet(workbook.AddSheet(), "Workbook 1", 1598, 94);
PopulateSheet(workbook.AddSheet(), "Workbook 2", 19627, 28);
PopulateSheet(workbook.AddSheet(), "Workbook 3", 13325, 10);
PopulateSheet(workbook.AddSheet(), "Workbook 4", 170, 26);
PopulateSheet(workbook.AddSheet(), "Workbook 5", 44, 40);
PopulateSheet(workbook.AddSheet(), "Workbook 6", 312, 23);
PopulateSheet(workbook.AddSheet(), "Workbook 7", 297, 9);
var json = workbook.ToJson();
HiddenField1.Value = json;
}
private static void PopulateSheet(Worksheet sheet, string sheetName, int rowsCount, int columnsCount)
{
sheet.Name = sheetName;
sheet.Columns = new List<Column>();
var row = new Row() { Index = 0 };
// columns
int columnIndex = 0;
for (int i = 0; i < columnsCount; i++)
{
sheet.Columns.Add(new Column());
string cellValue = "header" + i;
var cell = new Cell() { Index = columnIndex++, Value = cellValue, Background = "#bfbfbf", Bold = true };
row.AddCell(cell);
}
sheet.AddRow(row);
// rows
int rowIndex = 1;
for (int r = 0; r < rowsCount; r++)
{
row = new Row() { Index = rowIndex++ };
columnIndex = 0;
for (int c = 0; c < columnsCount; c++)
{
string cellValue = "Row" + r + "; Col:" + c;
var cell = new Cell() { Index = columnIndex++, Value = cellValue };
row.AddCell(cell);
}
sheet.AddRow(row);
}
}
Protected Sub Page_Init(ByVal sender As Object, ByVal e As EventArgs)
Dim workbook = New Telerik.Web.Spreadsheet.Workbook()
PopulateSheet(workbook.AddSheet(), "Workbook 1", 1598, 94)
PopulateSheet(workbook.AddSheet(), "Workbook 2", 19627, 28)
PopulateSheet(workbook.AddSheet(), "Workbook 3", 13325, 10)
PopulateSheet(workbook.AddSheet(), "Workbook 4", 170, 26)
PopulateSheet(workbook.AddSheet(), "Workbook 5", 44, 40)
PopulateSheet(workbook.AddSheet(), "Workbook 6", 312, 23)
PopulateSheet(workbook.AddSheet(), "Workbook 7", 297, 9)
Dim json = workbook.ToJson()
HiddenField1.Value = json
End Sub
Private Shared Sub PopulateSheet(ByVal sheet As Worksheet, ByVal sheetName As String, ByVal rowsCount As Integer, ByVal columnsCount As Integer)
sheet.Name = sheetName
sheet.Columns = New List(Of Column)()
Dim row = New Row() With {
.Index = 0
}
Dim columnIndex As Integer = 0
For i As Integer = 0 To columnsCount - 1
sheet.Columns.Add(New Column())
Dim cellValue As String = "header" & i
Dim cell = New Cell() With {
.Index = Math.Min(System.Threading.Interlocked.Increment(columnIndex), columnIndex - 1),
.Value = cellValue,
.Background = "#bfbfbf",
.Bold = True
}
row.AddCell(cell)
Next
sheet.AddRow(row)
Dim rowIndex As Integer = 1
For r As Integer = 0 To rowsCount - 1
row = New Row() With {
.Index = Math.Min(System.Threading.Interlocked.Increment(rowIndex), rowIndex - 1)
}
columnIndex = 0
For c As Integer = 0 To columnsCount - 1
Dim cellValue As String = "Row" & r & "; Col:" & c
Dim cell = New Cell() With {
.Index = Math.Min(System.Threading.Interlocked.Increment(columnIndex), columnIndex - 1),
.Value = cellValue
}
row.AddCell(cell)
Next
sheet.AddRow(row)
Next
End Sub