Formatting Cell Height to scale dynamically when Exporting the Grid to Excel

Environment

Product Version	2023.2.829
Product	Telerik UI for ASP.NET MVC Grid

Description

How can I format the cell height to scale dynamically during the export of the Telerik UI for ASP.NET MVC Grid to Excel?

Solution

To achieve the desired scenario:

Handle the ExcelExport event of the Grid.
Convert all the Grid items to a sanitized JSON format by using the toJSON client-side method.
Parse the model from the Excel Export's event data to a JSON object.
Generate the column cells by using the previously obtained property names in a valid workbook format.
Iterate through the JSON data and manually construct each of the row cells in a valid workbook format.
Use the rowHeight formula to calculate the resizing.
The rowHeight formula checks if the length of the contentToWrap string is bigger than a given number (27 in this case), and if it is, rowHeight is set to a number that we get from rounding up (contentToWrap.length / 27) * 20 in order to get a dynamic resizing value, otherwise set the rowHeight to 20.
Create a Workbook and add the already created data to its sheets.
Export.

    @(Html.Kendo().Grid<wrap_text_in_Export_excel.Models.OrderViewModel>()
        .Name("grid")
        .Columns(columns =>
        {
            columns.Bound(p => p.ProductID);
            columns.Bound(p => p.ProductName);
        })
        .ToolBar(tb => tb.Excel())
        .Events(ev => ev.ExcelExport("onExcelExport")) // Bind to the onExcelExport() function.
        .Pageable()
        .Sortable()
        .Scrollable()
        .Filterable()
        .HtmlAttributes(new { style = "height:550px;" })
        .DataSource(dataSource => dataSource
            .Ajax()
            .PageSize(20)
            .Read(read => read.Action("Read", "Grid"))
        )
    )

    namespace wrap_text_in_Export_excel.Models
    {
        public class OrderViewModel
        {
            public int ProductID { get; set; }
            public string ProductName { get; set; }   
        }
    }

    namespace wrap_text_in_Export_excel.Controllers
    {
        public partial class GridController : Controller
        {
            public ActionResult Read([DataSourceRequest]DataSourceRequest request)
            {
                var result = Enumerable.Range(0, 50).Select(i => new OrderViewModel
                {
                    ProductID = i,
                    ProductName = $"Lorem ipsum dolor sit amet, consectetur adipiscing elit. Morbi ut lacus mattis, congue nunc sed, " +
                    $"accumsan erat. Duis ac finibus massa. Duis eleifend venenatis nibh, quis consectetur lacus laoreet nec. Nunc " +
                    $"posuere nisl sem, eu maximus velit pulvinar eget. Aliquam gravida ipsum vitae interdum sodales. Quisque dictum " +
                    $"ac lectus auctor porttitor. Proin viverra lacus eget tortor iaculis, id bibendum lectus lobortis. {i}"
                });

                return Json(result.ToDataSourceResult(request));
            }
        }
    }

<script>
    function onExcelExport(e) {
        e.preventDefault();

        var sanitizedJsonData = e.data.map(function (dataItem) {
            return dataItem.toJSON(); // Get the sanitized JSON data.
        })

        var modelProps = generateModel(sanitizedJsonData); // Generate the model.

        var columns = modelProps.map(function (name) {
            return {
                value: name // Get the columns.
            };
        });

        var rows = [{
            cells: columns // Get the rows.
        }];

        for (var i = 0; i < sanitizedJsonData.length; i++) {
            var rowCells = [];
            var contentToWrap = "";

            for (var j = 0; j < modelProps.length; j++) {
                var cellValue = sanitizedJsonData[i][modelProps[j]];

                if (modelProps[j] === "ProductName") { // When we are in the ProductName column, get all the text and wrap it. 
                    contentToWrap = cellValue;
                    rowCells.push({
                        value: cellValue,
                        wrap: true
                    });
                } else {
                    rowCells.push({
                        value: cellValue
                    });
                }
            }

            var rowHeight = contentToWrap.length > 27 ? Math.ceil(contentToWrap.length / 27) * 20 : 20 // This formula is used for calculating the resizing for auto height.

            rows.push({
                cells: rowCells, // Push the data into the rows and add the auto height (rowHeight) property we've created. 
                height: rowHeight 
            });
        }

        var columnSettings = modelProps.map(function () {
            return {
                width: 200 // Static width of each column
            };
        });

        var workbook = new kendo.ooxml.Workbook({ // Create the Woorkbook and add the rows and columns.
            sheets: [{
                columns: columnSettings,
                title: "Products",
                rows: rows
            }]
        });
        kendo.saveAs({ // Save it as a .xlsx file.
            dataURI: workbook.toDataURL(),
            fileName: "Test.xlsx"
        }); 
    }

    function generateModel(data) { // Generate the model that we'll use. 
        var sampleDataItem = data[0];
        var model = Object.keys(sampleDataItem);
        return model;
    }
</script>

Formatting Cell Height to scale dynamically when Exporting the Grid to Excel

Environment

Description

Solution

More ASP.NET MVC Grid Resources

See Also

Getting Started

Support Resources

Community