Set Date Cell Format during Excel Export in Reorderable Grid

Environment

Product	Progress® Kendo UI® Grid for jQuery
Product Version	Created with the 2017.3.913 version

Description

How can I change the format of a date column during the Excel export in the Kendo UI Grid?

Solution

1. Subscribe to the excelExport event of the Grid.
2. In the excelExport event handler, get all the fields and their models.
3. Based on the model type, push the index of the column in an array.
4. Loop through the rows and change the values and the format of the desired columns.

<div id="grid"></div>
<script>
    $("#grid").kendoGrid({
        toolbar: ["excel"],
        excelExport: function(e) {
            var sheet = e.workbook.sheets[0];
            var grid = e.sender;
            var fields = grid.dataSource.options.fields;
            var fieldsModels = grid.dataSource.options.schema.model.fields;
            var columns = grid.columns;
            var dateCells = [];

            for (var i = 0; i < fields.length; i++) {
                if (fields[i].hidden) { // required if grid contains hidden column/s or user hides column/s
                    fields.splice(i, 1);
                };

                var currentField = fields[i].field;
                var currentModel = fieldsModels[currentField];

                if (currentModel.type === "date") {
                    for (var j = 0; j < columns.length; j++) {
                        if (currentField === columns[j].field) {
                            dateCells.push(j);
                            break;
                        };
                    };
                };
            };
            for (var rowIndex = 1; rowIndex < sheet.rows.length; rowIndex++) {
                var row = sheet.rows[rowIndex];

                for (var q = 0; q < dateCells.length; q++) {
                    var cellIndex = dateCells[q];
                    var value = row.cells[cellIndex].value;
                    var newValue = new Date(value.getFullYear(), value.getMonth(), value.getDate());

                    row.cells[cellIndex].value = newValue;
                    row.cells[cellIndex].format = "yyyy-MM-dd";
                };
            };
        },
        columns: [{
                field: "name"
            },
            {
                field: "date2",
                width: 80,
                format: "{0: dd-MM-yy}"
            },
            {
                field: "age"
            },
            {
                field: "date1",
                width: 80,
                format: "{0: yy-MM-dd}"
            }
        ],
        reorderable: true,
        dataSource: {
            data: [{
                    name: "Jane Doe",
                    date2: new Date(),
                    age: 30,
                    date1: new Date()
                },
                {
                    name: "John Doe",
                    date2: new Date(),
                    age: 33,
                    date1: new Date()
                }
            ],
            schema: {
                model: {
                    fields: {
                        date1: {
                            type: "date"
                        },
                        name: {
                            type: "string"
                        },
                        date2: {
                            type: "date"
                        },
                        age: {
                            type: "number"
                        }
                    }
                }
            }
        }
    });
</script>

See Also

• Setting the Cell Format of the Grid during Excel Export