# Export Filtered Data to Excel in Grid with Dynamic Columns and Data Types

## Environment

<table>
 <tr>
  <td>Product</td>
  <td>Progress® Kendo UI® Grid for jQuery</td>
 </tr>
 <tr>
  <td>Operating System</td>
  <td>All</td>
 </tr>
 <tr>
  <td>Browser</td>
  <td>All</td>
 </tr>
 <tr>
  <td>Browser Version</td>
  <td>All</td>
 </tr>
</table>

## Description

How can I export to Excel filtered Grid data when columns and data fields information is retrieved during runtime?

> How to create a Grid with dynamic columns and data types you can learn at [this article](https://www.telerik.com/kendo-jquery-ui/documentation/knowledge-base/create-with-dynamic-columns-and-data-types)

## Solution

1. Generate the rows to be exported based on the generated columns for the Grid.
2. Create a new [`workbook`](https://www.telerik.com/kendo-jquery-ui/documentation/api/ooxml/workbook) object and add the generated rows there.
3. Use the [`saveAs`](https://www.telerik.com/kendo-jquery-ui/documentation/api/kendo/methods/saveas) method to save the generated excel file.

```dojo
    <script src="https://demos.telerik.com/kendo-ui/content/shared/js/products.js"></script>

    <button class="k-button" onclick="exportFilteredData()">Export filtered data</button>
    <div id="grid"></div>

    <script>
      var isDateField =[];
      generateGrid(products);

      function generateGrid(response) {
        var model = generateModel(response);
        var columns = generateColumns(response);

        var grid = $("#grid").kendoGrid({
          dataSource: {
            transport:{
              read:  function(options){
                options.success(response);
              }
            },
            pageSize: 5,
            schema: {
              model: model
            }
          },
          filterable: true,
          columns: columns,
          pageable: true,
          editable:true
        });
      }

      var columnNames;

      function generateColumns(response){
        columnNames = Object.keys(response[0]);
        return columnNames.map(function(name){
          return { field: name, format: (isDateField[name] ? "{0:D}" : "") };
        })
      }

      function generateModel(response) {

        var sampleDataItem = response[0];
        var model = {};
        var fields = {};
        for (var property in sampleDataItem) {
          if(property.indexOf("ID") !== -1){
            model["id"] = property;
          }
          var propType = typeof sampleDataItem[property];

          if (propType === "number" ) {
            fields[property] = {
              type: "number",
              validation: {
                required: true
              }
            };
            if(model.id === property){
              fields[property].editable = false;
              fields[property].validation.required = false;
            }
          } else if (propType === "boolean") {
            fields[property] = {
              type: "boolean"
            };
          } else if (propType === "string") {
            var parsedDate = kendo.parseDate(sampleDataItem[property]);
            if (parsedDate) {
              fields[property] = {
                type: "date",
                validation: {
                  required: true
                }
              };
              isDateField[property] = true;
            } else {
              fields[property] = {
                validation: {
                  required: true
                }
              };
            }
          } else {
            fields[property] = {
              validation: {
                required: true
              }
            };
          }
        }

        model.fields = fields;

        return model;
      }

      function generateRows() {
        var rows = [ {cells: [] }];
        for (let i = 0; i < columnNames.length; i++) {
          rows[0].cells.push({ value: columnNames[i] })
        }
        return rows;
      }

      function exportFilteredData() {
        var rows = generateRows();
        var data = $("#grid").data("kendoGrid").dataSource.view();

        for (var i = 0; i < data.length; i++){
          rows.push({
            cells: []
          })
          for (var c = 0; c < columnNames.length; c++){
            var colName = columnNames[c];
            rows[i+1].cells.push({ value: data[i][colName] });
          }
        }

        var workbook = new kendo.ooxml.Workbook({
          sheets: [
            {
              columns: [
                // Column settings (width).
                { autoWidth: true },
                { autoWidth: true },
                { autoWidth: true },
                { autoWidth: true },
                { autoWidth: true }
              ],
              // The title of the sheet.
              title: "Orders",
              // The rows of the sheet.
              rows: rows
            }
          ]
        });
        // Save the file as an Excel file with the xlsx extension.
        workbook.toDataURLAsync().then(function(dataURL) {
   		 kendo.saveAs({
          dataURI: dataURL,
     	  fileName: "Test.xlsx"
    	 });
	   });
      }
    </script>
```

## See Also

* [Create Grids with Dynamic Columns and Data Types](https://www.telerik.com/kendo-jquery-ui/documentation/knowledge-base/create-with-dynamic-columns-and-data-types)
* [Dynamic Aggregates in a Dynamic Grid](https://www.telerik.com/kendo-jquery-ui/documentation/knowledge-base/grid-dynamic-aggregates)
* [JavaScript API Reference of the Data Grid](https://www.telerik.com/kendo-jquery-ui/documentation/api/ui/grid)