Saving RadSpreadsheet freezes when amount of data is big

[Giorgos]

I have implement a custom provider for RadSpreadsheet as follows:

 

public class MySpreadsheetDocumentProvider : SpreadsheetProviderBase
{
public string FilePath { get; set; }

     public MySpreadsheetDocumentProvider(APD apd, out string errorMessage) : base()
{
  errorMessage = string.Empty;
  string folderPath = $"{ConfigurationManager.AppSettings["SpreadsheetFolder"]}";

 if (!Directory.Exists(folderPath))
 {
  Directory.CreateDirectory(folderPath);
 }

  FilePath = $"{folderPath}/{apd.APDId}.xlsx";

  if (!File.Exists(FilePath))
  {
    Workbook workbook = new Workbook();
    Worksheet worksheet = workbook.AddSheet();

    using (APD_BLL apd_bll = new APD_BLL())
    {
       var apd_data = apd_bll.Get(apd.APDId);
       FillWorksheet(worksheet, apd_data);
    }

    workbook.Save(FilePath);
  }
  }

public override void SaveWorkbook(Workbook workbook)
  {
  SpreadsheetDocumentProvider provider = new SpreadsheetDocumentProvider(FilePath);
  provider.SaveWorkbook(workbook);
  }

public override List<Worksheet> GetSheets()
  {
   SpreadsheetDocumentProvider provider = new SpreadsheetDocumentProvider(FilePath);
   var sheets = provider.GetSheets();

   return sheets;
 }

 private static void FillWorksheet(Worksheet sheet, IEnumerable<APDYpallhlou> data)
 {

 

So the provider looks for an excel file in a predefined location. If the file exists then it is bound to the RadSpreadsheet, otherwise a new file is created. This part of the provider works well with small as well as with bigger files.

The problem is when I try to Save the RadSpreadsheet by clicking the Save button on the toolbar. Save works ok for a few hundred rows. But it fails for a larger number of rows, e.g. 15000 rows. When the Save button is clicked the page freezes and method SaveWorkbook(Workbook workbook) is never invoked.

[Peter Milchev]

Hello Giorgios,

Can you please check the browser's console for any JavaScript errors and the Network tab of the browser's DevTools(12) for any failing requests? 

I guess that the JSON passed via the Callback is too long and exceeds the maximum JSON length allowed.

Meanwhile, you can check the following article:

• https://www.telerik.com/support/kb/aspnet-ajax/spreadsheet/details/improve-performance-of-slowly-loading-spreadsheet

Regards,
Peter Milchev
Progress Telerik

[Giorgos]

Hi Peter, 

thanks for your reply. My problem is in saving not loading the spreadsheet. The spreadsheet, containing about 15500 x 7 = 108500 cells, loads in about 18 seconds (using the code I already posted). But if, after load is completed, the Save button is pressed the page freezes and SaveWorkbook(Workbook workbook) is never invoked.

Pressing F12 I get no javascript error on the browser's console.

But if I delete 1000 rows from the excel file that the spreadsheet is bound to, then saving works ok. It is actually faster than loading taking about 10 seconds (for 14500 rows).

[Peter Milchev]

Hello Giorgos,

I am sorry for the late answer, I have replicated the issue and researched various approaches to overcome it. 

Generally, the issue is the amount of data that needs to be serialized/transferred.

Even if loading the spreadsheet is not an issue, the save operation actually serialized the data to a JSON string and sends it to the server via a Callback. Unfortunately, that is where a framework limitation is hit, the string is too big to be sent in a single request.

That is why I have implemented a chunking mechanism for saving the big file to the server. Attached is the sample project that loads the file with 17 columns and 100k rows and sends the file to the server.

Also, the code uses the client-side file import that works faster than the server-side loading.

<telerik:RadScriptManager ID="RadScriptManager1" EnablePageMethods="true" runat="server">
    <Scripts>
        <asp:ScriptReference Assembly="Telerik.Web.UI" Name="Telerik.Web.UI.Common.Core.js" />
        <asp:ScriptReference Assembly="Telerik.Web.UI" Name="Telerik.Web.UI.Common.jQuery.js" />
        <asp:ScriptReference Assembly="Telerik.Web.UI" Name="Telerik.Web.UI.Common.jQueryInclude.js" />
    </Scripts>
</telerik:RadScriptManager>
<telerik:RadCodeBlock runat="server">
    <script src="https://code.jquery.com/jquery-3.3.1.js" integrity="sha256-2Kok7MbOyxpgUVvAk/HJ2jigOSYS2auK4Pfzbm7uH60=" crossorigin="anonymous"></script>

    <script src="https://kendo.cdn.telerik.com/2020.2.513/js/jszip.min.js"></script>

    <script>
        function OnClientClicked(sender, args) {
            var spreadsheet = $find("<%= RadSpreadsheet1.ClientID %>");
            save(spreadsheet);
        }

        function showLoadingPanel() {
            var currentLoadingPanel = $find("<%= RadAjaxLoadingPanel1.ClientID %>");
            currentLoadingPanel.show("<%= RadSpreadsheet1.ClientID %>");
        }
        function hideLoadingPanel() {
            var currentLoadingPanel = $find("<%= RadAjaxLoadingPanel1.ClientID %>");
            currentLoadingPanel.hide("<%= RadSpreadsheet1.ClientID %>");
        }

        function LoadHeavyFile() {
            showLoadingPanel();
            $.ajax({
                url: '/TestSource.xlsx',
                method: 'GET',
                xhrFields: {
                    responseType: 'blob'
                },
                success: function (data, second, third) {
                    console.log("data received")
                    var res = $find("<%= RadSpreadsheet1.ClientID%>").get_kendoWidget().fromFile(data);
                    console.log(res)
                    res.done(function () {
                        hideLoadingPanel();
                    })
                }
            });
        }

        function sendDataInChunks(chunks, index, callback) {
            if (index >= chunks.length) {
                setTimeout(callback)
                return;
            }

            var data = chunks[index];
            console.log("sending chunk " + index + " out of " + chunks.length);

            $.post({
                url: '/Default.aspx/GetDetails',
                method: 'post',
                data: JSON.stringify(data),
                contentType: "application/json; charset=utf-8",
                dataType: "json",
                success: function (result) {
                    console.log("chunk " + index + " sent successfully");
                    sendDataInChunks(chunks, index + 1, callback)
                },
                error: function (result) { console.log("something went wrong") },
            });
        }

        function splitIntoChunks(jsonString) {
            var chunkSize = 2000000; // about 1.4MB per chunk
            var requestsCount = Math.ceil(jsonString.length / chunkSize);
            var index = 0;
            var chunks = [];
            console.log("start chunking");

            for (var i = 0; i < requestsCount - 1; i++) {
                var end = index + chunkSize;
                var data = { chunkNumber: i, chunkData: jsonString.substring(index, end), totalChunks: requestsCount };
                index = end;
                chunks.push(data)
            }

            // add the rest in the last chunk
            chunks.push({
                chunkNumber: (requestsCount - 1),
                chunkData: jsonString.substr(index),
                totalChunks: requestsCount
            })

            console.log("Chunking completed");
            return chunks;
        }

        function save(spread) {
            //owLoadingPanel()
            setTimeout(function () {
                console.log("start converting to JSON")
                var json = spread.get_kendoWidget().toJSON();
                console.log("converting to JSON completed")

                console.log("start Stringifying JSON")
                var jsonString = JSON.stringify(json);
                console.log("Stringifying JSON completed")

                var chunks = splitIntoChunks(jsonString)

                console.log("start sending chunks, Total of " + chunks.length);
                sendDataInChunks(chunks, 0, function () {
                    $find("<%= RadButton1.ClientID %>").click();
                });
            })
        }
    </script>
</telerik:RadCodeBlock>
<telerik:RadAjaxLoadingPanel runat="server" Skin="Default" ID="RadAjaxLoadingPanel1"></telerik:RadAjaxLoadingPanel>

<telerik:RadButton runat="server" Style="display: none" ID="RadButton1" Text="Postback to process file" AutoPostBack="true" OnClick="RadButton1_Click" />
<telerik:RadButton runat="server" ID="RadButton2" Text="Send Workbook to Server" OnClientClicked="OnClientClicked" AutoPostBack="false" />
<telerik:RadButton runat="server" ID="RadButton3" Text="Load heavy file" OnClientClicked="LoadHeavyFile" AutoPostBack="false" />

<telerik:RadSpreadsheet runat="server" ID="RadSpreadsheet1" />

using System;
using Telerik.Web.UI;
using Telerik.Web.Spreadsheet;
using System.Web.Script.Services;

[ScriptService]
public partial class Default : System.Web.UI.Page
{
    private const string FileJsonChunkSessionKey = "FileJsonChunks";


    [System.Web.Services.WebMethod(EnableSession = true)]
    public static void GetDetails(string chunkData, int chunkNumber, int totalChunks)
    {
        if (chunkNumber == 0)
        {
            System.Web.HttpContext.Current.Session[FileJsonChunkSessionKey] = chunkData;
            //System.Web.HttpContext.Current.Session[FileJsonChunkSessionKey] = new List<string>(totalChunks+5) { chunkData };
        }
        else
        {
            System.Web.HttpContext.Current.Session[FileJsonChunkSessionKey] += chunkData;
            //(System.Web.HttpContext.Current.Session[FileJsonChunkSessionKey] as List<string>).Insert(chunkNumber,chunkData);
        }
    }

    protected void Page_Load(object sender, EventArgs e)
    {

    }

    protected void RadButton1_Click(object sender, EventArgs e)
    {
        if (Session[FileJsonChunkSessionKey] != null && !string.IsNullOrWhiteSpace(Session[FileJsonChunkSessionKey].ToString()))
        {
            var data = Session[FileJsonChunkSessionKey].ToString();
            var workbook = Workbook.FromJson(data);
            var path = Server.MapPath("~/App_Data/testSave.xlsx");

            // clear the file from the session
            Session.Remove("FileJsonChunks");

            // default Load/Save from Excel file
            SpreadsheetDocumentProvider provider = new SpreadsheetDocumentProvider(path);
            provider.SaveWorkbook(workbook);
        }
    }
}

Regards,
Peter Milchev
Progress Telerik