Excel Export

As of the Kendo UI Q3 2014 (2014.3.1119) release, the Grid widget provides built-in Excel export functionality.

For runnable examples, refer to:

Getting Started

To enable the Excel export option of the Grid:

  1. Include the corresponding toolbar command and set the export settings.
  2. Include the JSZip script on the page. For more information, refer to the article with the requirements.

To initiate Excel export through code, call the saveAsExcel method.

  • By default, the Grid exports the current page of the data with sorting, filtering, grouping, and aggregates applied.
  • The Grid uses the current column order, visibility, and dimensions to generate the Excel file.
  • The Grid does not export the current CSS theme in the Excel file. For more information on how to change the visual appearance of the Excel document, refer to the below section about customization of the Excel document.
  • The Grid exports only data-bound columns. All columns that do not have their field option set are ignored.
  • The format option is not used during export. For more information, refer to the section on column formats.
  • The template option is not used during export. For more information, refer to the section on column templates.
  • The detailTemplate option is not used during export. For more information, refer to the section on detail templates.

For more information, refer to the online demo on Excel export.

The following example demonstrates how to enable the Excel export functionality of the Grid.

Example
    <script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/2.4.0/jszip.min.js"></script>

    <div id="grid"></div>
    <script>
        $("#grid").kendoGrid({
            toolbar: ["excel"],
            excel: {
                fileName: "Kendo UI Grid Export.xlsx"
            },
            dataSource: {
                type: "odata",
                transport: {
                    read: "https://demos.telerik.com/kendo-ui/service/Northwind.svc/Products"
                },
                pageSize: 7
            },
            sortable: true,
            pageable: true,
            columns: [
                { width: 300, field: "ProductName", title: "Product Name" },
                { field: "UnitsOnOrder", title: "Units On Order" },
                { field: "UnitsInStock", title: "Units In Stock" }
            ]
        });
    </script>

Configuration

With regard to its Excel export, the Grid enables you to:

Exporting All Pages

By default, the Grid 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 and serverPaging is enabled, the Grid will make a "read" request for all data. If the data items are too many, the browser may become unresponsive. In such cases, use server-side export.

Example
    <script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/2.4.0/jszip.min.js"></script>

    <div id="grid"></div>
    <script>
        $("#grid").kendoGrid({
            toolbar: ["excel"],
            excel: {
                allPages: true
            },
            dataSource: {
                type: "odata",
                transport: {
                    read: "https://demos.telerik.com/kendo-ui/service/Northwind.svc/Products"
                },
                pageSize: 7
            },
            pageable: true,
            columns: [
                { width: 300, field: "ProductName", title: "Product Name" },
                { field: "UnitsOnOrder", title: "Units On Order" },
                { field: "UnitsInStock", title: "Units In Stock" }
            ]
        });
    </script>

Customizing Exported Files

To customize the generated Excel file, use the excelExport event. The workbook event argument exposes the generated Excel workbook configuration.

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"
Example
    <script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/2.4.0/jszip.min.js"></script>

    <div class="k-rtl">
      <div id="grid" ></div>
    </div>
    <script>
      $("#grid").kendoGrid({
        toolbar: ["excel"],
        excel: {
          allPages: true
        },
        dataSource: {
          type: "odata",
          transport: {
            read: "https://demos.telerik.com/kendo-ui/service/Northwind.svc/Products"
          },
          pageSize: 7
        },
        excelExport: function(e) {
          var workbook = e.workbook;
          var sheet = workbook.sheets[0];

          workbook.rtl = true;
          for (var i = 0; i < sheet.rows.length; i++) {
            for (var ci = 0; ci < sheet.rows[i].cells.length; ci++) {
              sheet.rows[i].cells[ci].hAlign = "right";
            }
          }
        },
        pageable: true,
        columns: [
          { width: 300, field: "ProductName", title: "Product Name" },
          { field: "UnitsOnOrder", title: "Units On Order" },
          { field: "UnitsInStock", title: "Units In Stock" }
        ]
      });
    </script>

Exporting Multiple Grids

By default, each Grid exports its content in a separate Excel sheet. For more information, refer to the example on exporting multiple Grids in a single Excel document.

Saving Files on the Server

To send the generated file to a remote service, prevent the default file saving and post the base64 encoded contents.

Example
<script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/2.4.0/jszip.min.js"></script>

<div id="grid"></div>
<script>
    $("#grid").kendoGrid({
        toolbar: ["excel"],
        dataSource: {
            type: "odata",
            transport: {
                read: "https://demos.telerik.com/kendo-ui/service/Northwind.svc/Products"
            },
            pageSize: 7
        },
        pageable: true,
        columns: [
            { width: 300, field: "ProductName", title: "Product Name" },
            { field: "UnitsOnOrder", title: "Units On Order" },
            { field: "UnitsInStock", title: "Units In Stock" }
        ],
        excelExport: function(e) {
            // Prevent the default behavior which will prompt the user to save the generated file.
            e.preventDefault();
            // Get the Excel file as a data URL.
            var dataURL = new kendo.ooxml.Workbook(e.workbook).toDataURL();
            // Strip the data URL prologue.
            var base64 = dataURL.split(";base64,")[1];
            // Post the base64 encoded content to the server which can save it.
            $.post("/server/save", {
                base64: base64,
                fileName: "ExcelExport.xlsx"
            });
        }
    });
</script>

Server-Side Processing

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

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.
  • If you use Kendo UI Q2 2014 SP2 (2014.2.1008) or earlier, the export requires a custom implementation. To achieve that task, either:
    • Use a server-side implementation to directly export the data that is otherwise displayed by the Grid, or
    • Use a client-side implementation to export the table HTML markup or the dataSource items of the Grid.
  • The Grid does not use column templates 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 detail template for the same reason as it does not export its column templates. If the detail template contains another Grid, follow the example on the exporting a detail Grid.
  • 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 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 example.

Further Reading

See Also

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