Dynamic adjustement of the sheet based on the loaded data

[Herr Walter Gartmann]

Hello, I am trying to make a function that can resize my spreadsheet sheet dynamically based on the data, which is load on the sheet.

I know that the Spreadsheet has a function called "resize". I pass the numbers of columns and rows with a ViewBag, from the controller to the View on JS.

Controller:

public ActionResult Menus_Read([DataSourceRequest] DataSourceRequest request)
{
    try
    {
        List<MenuViewModel> menus = db.GetMenus();
        DataSourceResult result = menus.ToDataSourceResult(request);
        ViewBag.RowCount = menus.Count;
        ViewBag.ColumnCount = typeof(MenuViewModel).GetProperties().Length;
        
        return Json(result, JsonRequestBehavior.AllowGet);
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.ToString());

        ModelState.AddModelError(string.Empty, ex.Message);
        return Json(new[] { new object() }.ToDataSourceResult(request, ModelState), JsonRequestBehavior.AllowGet); ;
    }
}

 

JS:

    $(document).ready(function() {
        var rowCount = @(ViewBag.RowCount);
        var columnCount = @(ViewBag.ColumnCount);

        var spreadsheet = $("#spreadsheet").data("kendoSpreadsheet");
        var sheet = spreadsheet.activeSheet();
        sheet.resize(rowCount, columnCount);
    });

To use the ViewBag content for the view I first need to get the data, to check the count of the columns and rows. Maybe this could be my problem, the timing.

Has anyone had a similar problem or can help me?

[Mihaela]

Hello Herr Walter Gartmann,

To resize the Spreadsheet based on the loaded data, I would suggest the following apptoach:

• Handle the Render event of the Spreadsheet only once and calculate the component's height based on the number of records in the DataSource:

<style>
 .k-spreadsheet-scroller .k-spreadsheet-view-size {
     height: 100%!important;
 }
</style>

@(Html.Kendo().Spreadsheet()
    .Name("spreadsheet")
    ...
)
<script>
    $( document ).ready(function() { //When the page is loaded
        var spreadSheet = $("#spreadsheet").data("kendoSpreadsheet"); //get a reference to the Spreadsheet
        spreadSheet.one("render", function(e) { //Handle the first triggering of the "render" event
            var rowsData = e.sender.sheets()[0].dataSource.data(); //Get the data of the 1st sheet
            var rowsNumber = rowsData.length; //g get the total number of records
            //Calculate the height of the Spreadsheet
           //The default row height is 20px (https://docs.telerik.com/kendo-ui/api/javascript/ui/spreadsheet/configuration/rowheight)
          //If there is a header in the sheet --> +40px;  +40px fo the Spreadsheet header; 
            ar height = (rowsNumber * 20) + 80 + $(".k-spreadsheet-action-bar").height(); 
            e.sender.element.innerHeight(height);
        });
    });
</script>

• Handle the DataBound event of the Spreadsheet and call the resize() method:

        .Events(e => e
            .DataBound("onDataBound")
        )

    function onDataBound(e) {
        e.sender.resize(true);
    }

Here is a REPL example that demonstrates this approach:

https://netcorerepl.telerik.com/coEeavOX170HHL4920

Let me know if the suggested approach works for you.

 

Regards,
Mihaela
Progress Telerik