Edit this page

Excel Export

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

Enable Export to Excel

To enable Excel export, include the corresponding toolbar command and configure the export settings.

You need to include JSZip script on the page. For more information on the requirements to do this, refer to Requirements.

The following example demonstrates how to enable the Excel export functionality of a Kendo UI 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: "http://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>

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

Important

  • 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.

Features

Excel Export of All Data

By default, the Grid exports only the current page of data. To export all pages, set the allPages option to true.

Important

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, consider the implementation of server-side export.

The following example demonstrates how to export all the data from a Kendo UI Grid to Excel.

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: "http://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>

Excel Customization

The excelExport event allows customization of the generated Excel document.

The workbook event argument exposes the generated Excel workbook configuration.

For a better understanding about how Excel documents work, refer to the introductory article on Excel.

For more information on how to use the background option to set the background color of alternating rows while exporting the Grid to Excel, refer to this example.

Right-to-Left Support

The excelExport event allows reversing the cells and setting the text alignment to support right-to-left (RTL) languages.

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: "http://demos.telerik.com/kendo-ui/service/Northwind.svc/Products"
          },
          pageSize: 7
        },
        excelExport: function(e) {
          var sheet = e.workbook.sheets[0];
          for (var i = 0; i < sheet.rows.length; i++) {
            sheet.rows[i].cells.reverse();
            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>

Row Type

Each row has a type field that can be used to distinguish between the various row types in the Grid.

The possible values are:

  • "header"
  • "footer"
  • "groupHeader"
  • "groupFooter"
  • "data"

Multiple Grids

Each Grid is exported in a separate Excel sheet. For more information on how to export multiple Grids in a single Excel document, see this example.

Saving Files on Server

It might be useful sometimes to send the generated file to a remote service. To achieve this, prevent the default file saving and post the base64 encoded contents.

The following example demonstrates how to post files to the server.

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: "http://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>

Limitations

Column Templates

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.

Column Format

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.

Detail Templates

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.

Server-Side Processing

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

For examples on how to export Excel files, refer to the RadSpreadProcessing library.

For more information on the Grid server-side processing, refer to this article.

Troubleshooting

JavaScript Error Is Thrown That JSZip Is Not Found

Clicking Export to Excel or calling the saveAsExcel throws an exception if the JSZip JavaScript library is not found. To solve this issue, include JSZip in the page. For more information on this, see the introductory help topic about exporting to Excel.

Excel Export Is Not Working in Internet Explorer and Safari

Internet Explorer 9 and Safari do not support the option for saving a file and require the implementation of a server proxy. Set the proxyURL option to specify the server proxy URL, as shown below.

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",
            proxyURL: "/proxy"
        },
        dataSource: {
            type: "odata",
            transport: {
                read: "http://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>

Known Limitations

  • The maximum size of the exported file has a system-specific limit. For large data sets, it is highly recommended that you use the server-side solution the Document Processing Library provides. The RadSpreadStreamProcessing component is particularly suited for large data sets.
  • As discussed in the previous section, exporting 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 older, exporting requires a custom implementation and there are two ways to approach the task:
    • Use a server-side implementation to directly export the data that is otherwise displayed by the Grid.
    • Use a client-side implementation to export the table HTML markup or the dataSource items of the Grid.

Important

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.

Further Reading

See Also

For how-to examples on the Kendo UI Grid widget, browse its How To documentation folder.

Is this article helpful? Yes / No
Thank you for your feedback!

Give article feedback

Tell us how we can improve this article

close
Dummy