Excel Export

The Grid enables you to export its content to Excel.

For a runnable example, refer to the demo on Excel export by the Grid.

Getting Started

To enable the Excel export option of the Grid:

  1. Include the corresponding toolbar command and set the export settings.
  2. To take full advantage of the Excel export feature, download the JSZip library and include the file before the Kendo UI JavaScript files in the Layout.cshtml. For more information, refer to the article with the requirements.

    <script src="http://code.jquery.com/jquery-1.9.1.min.js"></script>
    <script src="http://cdnjs.cloudflare.com/ajax/libs/jszip/2.4.0/jszip.js"></script>
    <script src="http://kendo.cdn.telerik.com/2019.1.115/js/kendo.all.min.js"></script>
    
    @(Html.Kendo().Grid<ProductViewModel>()
        .Name("grid")
        .ToolBar(tools => tools.Excel())
        .Excel(excel => excel
            .FileName("Products.xlsx")
        .DataSource(dataSource => dataSource
            .Ajax()
            .Read(read => read.Action("Products_Read", "Home"))
        )
    )
    

To initiate the Excel export, press the Toolbar button or use the Grid client-side API and call the saveAsExcel method.

Browser versions, such as Internet Explorer 9 and earlier and Safari, require the implementation of a server proxy.

    [HttpPost]
    public ActionResult Pdf_Export_Save(string contentType, string base64, string fileName)
    {
        var fileContents = Convert.FromBase64String(base64);

        return File(fileContents, contentType, fileName);
    }

Outputting the Result

Through its default configuration, the Telerik UI Grid for ASP.NET Core exports the current page of the data with sorting, filtering, grouping, and aggregates applied. To export all pages, refer to this section.

The Grid uses the current column order, visibility, and dimensions to generate the Excel file. It does not export the current CSS theme in the Excel file. For more information on changing the visual appearance of the Excel document, refer to this section.

  • The Grid exports only data-bound columns. Template and command columns are ignored.
  • The Format option is not used during export. For more information on this, refer to this section.
  • The ClientTemplate option is not used during export. For more information on this, refer to this section.

Exporting All Data

By default, the Telerik UI Grid for ASP.NET Core exports only the current page of data. To export all pages, set the AllPages option to true.

When the AllPages option is set to true, the Grid makes a read request for all data. If the data items are too many, the browser may become unresponsive. For such scenarios, implement a server-side export.

@(Html.Kendo().Grid<ProductViewModel>()
    .Name("grid")
    .ToolBar(tools => tools.Excel())
    .Excel(excel => excel
        .AllPages(true)
    )
    .DataSource(dataSource => dataSource
        .Ajax()
        .Read(read => read.Action("Products_Read", "Home"))
    )
)

Customizing Excel Documents

The ExcelExport() event allows the customization of the generated Excel document. The workbook event argument exposes the generated Excel workbook configuration. For more information on how the Excel documents work, refer to the article on Excel export in Kendo UI for jQuery.

  1. Attach an excel export handler.

    @(Html.Kendo().Grid<OrderViewModel>()    
        .Name("grid")
        .ToolBar(tools => tools.Excel())
        .Events(e => e.ExcelExport("excelExport"))
        /* Other configuration. */
    )
    
  2. In the handler, manipulate the generated workbook. The example alternates the background color of the rows cells.

    <script>
        function excelExport(e) {
            var sheet = e.workbook.sheets[0];
            for (var rowIndex = 1; rowIndex < sheet.rows.length; rowIndex++) {
                if (rowIndex % 2 == 0) {
                    var row = sheet.rows[rowIndex];
                    for (var cellIndex = 0; cellIndex < row.cells.length; cellIndex++) {
                        row.cells[cellIndex].background = "#aabbcc";
                    }
                }
            }
        }
    </script>
    

Exporting Right-to-Left Content

The ExcelExport() event allows you to reverse the cells and set the text alignment to support right-to-left (RTL) languages. To render the document in the right-to-left flow in Excel, enable the rtl option of the workbook.

Each row has a type field that can be used to distinguish between the various row types in the Grid. The supported values are:

  • "header"
  • "footer"
  • "group-header"
  • "group-footer"
  • "data"

Setting the Column Templates

The Telerik UI Grid for ASP.NET Core does not use the ClientTemplate during Excel export. It exports only the data. The reason is that a column template may contain arbitrary HTML which cannot be converted to Excel column values. Here is how to use a column template that does not contain HTML:

  1. Attach an excel export handler.

    @(Html.Kendo().Grid<OrderViewModel>()    
        .Name("grid")
        .ToolBar(tools => tools.Excel())
        .Events(e => e.ExcelExport("excelExport"))
        .Columns(columns => {
            columns.Bound(p => p.OrderID).Filterable(false).Width(100);
            columns.Bound(p => p.Freight).ClientTemplate("Freight: #: kendo.format('{0:c}', Freight) #");
        })
        /* Other configuration. */
    )
    
  2. In the handler, manipulate the generated workbook. The example applies the column template to the cell by assigning it to the rows.cells.value.

    <script>
        function excelExport(e) {
            var sheet = e.workbook.sheets[0];
            var template = kendo.template(this.columns[1].template);
            var data = this.dataSource.view();
            for (var i = 0; i < data.length; i++) {
                sheet.rows[i + 1].cells[1].value = template(data[i]);
            }
        }
    </script>
    

Defining the Column Format

The Telerik UI Grid for ASP.NET Core does not use the Format option during Excel export because some formats are incompatible with Excel. To format the cell values, set the Kendo UI for jQuery format option of the cells.

The page on creating a custom number format describes the formats that are supported by Excel.

  1. Attach an excel export handler.

    @(Html.Kendo().Grid<OrderViewModel>()    
        .Name("grid")
        .ToolBar(tools => tools.Excel())
        .Events(e => e.ExcelExport("excelExport"))
        .Columns(columns => {
            columns.Bound(p => p.OrderID).Filterable(false).Width(100);
            columns.Bound(p => p.Freight).ClientTemplate("Freight: #: kendo.format('{0:c}', Freight) #");
        })
        /* Other configuration. */
    )
    
  2. In the handler, manipulate the generated workbook. The example applies a format to the cell by assigning it to the rows.cells.format

    <script>
        function excelExport(e) {
            var sheet = e.workbook.sheets[0];
            for (var rowIndex = 1; rowIndex < sheet.rows.length; rowIndex++) {
                var sheet = e.workbook.sheets[0];
                for (var rowIndex = 1; rowIndex < sheet.rows.length; rowIndex++) {
                    var row = sheet.rows[rowIndex];
                    row.cells[1].format = "[Blue]#,##0.0_);[Red](#,##0.0);0.0;"
                }
            }
        }
    </script>
    

Server-Side Processing

To export huge datasets to Excel, use the RadSpreadStreamProcessing library which is part of Telerik Document Processing (TDP) by Progress.

The ASP.NET Core version is in development. For updates, check this feature request.

Known Limitations

  • The Grid and its DataSource contain only the data items from the current page during client-side export. As a result, either make the export in chunks, or disable the paging feature.
  • The maximum size of the exported file has a system-specific limit. For large data sets, use the server-side solution which is provided by the RadSpreadStreamProcessing as part of the Document Processing Library.
  • Exporting the Grid to Excel in older browsers, such as Internet Explorer 9 and Safari, requires the implementation of a server proxy. For more information, refer to the ProxyURL configuration section.
  • The Grid does not use the column ClientTemplate during the Excel export—it exports only the data. The reason for this behavior is that a column template might contain arbitrary HTML which cannot be converted to Excel column values. For more information on how to use a column template that does not contain HTML, refer to this column template example.
  • The Grid does not export its ClientDetailTemplateId for the same reason as it does not export its column templates.
  • The Grid does not use column formats during the Excel export because some Kendo UI formats are incompatible with Excel. To format the cell values, set the cell format option of the cells.

For more information on the formats that are supported by Excel, refer to this page. For more information on how to format cell values, refer to this UI for ASP.NET MVC example.

See Also

In this article
Not finding the help you need? Improve this article