KendoUI Grid Excel Export Automatic DateTime Formatting

If you are working with tabular data, then chances are your users want to see it in a grid. And in my experience if you put the data in a grid, those users are going to export that data to Excel! Of course. Well, if you were lucky enough to be using a Kendo UI Grid, then the pain of exporting that grid data to an Excel spreadsheet is a lot less….painful.

If you haven’t already tried out the Kendo UI Grid Excel Export support, you really should.

So, how about a specific example, with my all-time favorite grid data type: dates. Just kidding, date columns are never my favorite, but they do end up being pretty darn important to users who have time-related data.

Exporting DateTime Columns

So, take your everyday date column in a Kendo grid. You’ve added some special formatting, because not only do we have a date, we also have time data. In fact, let’s say two columns, different formats.

And we can see our formats are working correctly:

2016-07-15 16_27_26-Kendo UI Grid Excel Export With Dates - JSFiddle

Great, so we wire up our handy Export to Excel functionality, which takes all of about 2 minutes, and export.

2016-07-15 16_13_50-Export.xlsx - Excel

What happened? That default column formatting in Excel just clobbered our time-inclusive formatting! Not cool!

Formatting using the Model Types

Ok, but in the link above, we can see how to format certain cells, and even add a date/time format. Perfect. Except I don’t want to handle it by column index, because what if users re-order the columns? Let’s add detection of the column type using the model of the Kendo DataSource. And, since we are referencing the column, we can include our own property for the export format. In case you are wondering, no, the same format string won’t work for both display and export – notice the difference in the AM/PM format specifiers.

Now, we can export the grid and use either the column-specified date/time format, or let a default format apply that will at least show the time when opened in Excel. No hard-coded column index needed.

2016-07-15 16_13_34-Export (2).xlsx - Excel
Here’s the full example you can try out for yourself:

The following two tabs change content below.