Formatting dates in Excel Export

[Laura]

I'm able to format the dates the way I want in the grid, but they don't appear that way in my Excel export.

The code in my controller is:

1.[HttpPost]
2.public ActionResult Excel_Export_Save(string contentType, string base64, string fileName)
3.{
4.      var fileContents = Convert.FromBase64String(base64);
5.      return File(fileContents, contentType, fileName);
6.}

 

In my grid code below, lines 11 and 15 are the date fields in question.  They appear in the grid as MM/dd/yy hh:mm tt, but in the export they appear as MM/dd/yyyy.

01.@(Html.Kendo().Grid<ErmhsL2BudReqRawDto>()
02.    .Name("Grid")
03.    .Columns(columns =>
04.    {
05.        columns.Bound(c => c.Id).Hidden();
06.        columns.Bound(c => c.CharterId).Hidden();
07.        columns.Bound(c => c.CharterName).Width(400).Filterable(ftb => ftb.Cell(cell => cell.Operator("contains").SuggestionOperator(FilterType.Contains))).Locked(true);
08.        columns.Bound(c => c.EdcoeId).Width(100).Locked(true);
09.        columns.Bound(c => c.CdsCodeWithDashes).Locked(true).Width(150);
10.        columns.Bound(c => c.FiscalYear).Filterable(filterable => filterable.UI("fiscalYearFilter")).Locked(true).Width(100);
11.        columns.Bound(c => c.DateEntered).Format("{0:MM/dd/yy hh:mm tt}").HtmlAttributes(new { style = "text-align:right" }).Width(100);
12.        columns.Bound(c => c.ProgressMonitoringFrequency).Width(500);
13.        columns.Bound(c => c.MonitorNameAndTitle).Width(300);
14.        columns.Bound(c => c.IsAmhpCertified).Filterable(filterable => filterable.Messages(m => m.IsFalse("No")).Messages(m => m.IsTrue("Yes"))).ClientTemplate("#=IsAmhpCertified ? 'Yes': 'No'#").HtmlAttributes(new { style = "text-align:center" }).Width(100);
15.        columns.Bound(c => c.AmhpDateEntered).Format("{0:MM/dd/yy hh:mm tt}").HtmlAttributes(new { style = "text-align:right" }).Width(100);
16.    })
17.    .ToolBar(tools => tools.Excel())
18.    .Excel(excel => excel
19.        .AllPages(true)
20.        .FileName("ERMHS Level 2 Budget Requests.xlsx")
21.        .Filterable(true)
22.        .ProxyURL(Url.Action("Excel_Export_Save", "FiscalReport"))
23.    )
24.    .Filterable(filterable => filterable
25.        .Extra(false)
26.        .Operators(operators => operators
27.            .ForString(str => str.Clear()
28.                .StartsWith("Starts with")
29.                .IsEqualTo("Is equal to")
30.                .IsNotEqualTo("Is not equal to")
31.        ))
32.    )
33.    .Groupable()
34.    .Pageable(m => m.PageSizes(new[] { "25", "50", "100", "All" }))
35.    .Resizable(resizable => resizable.Columns(true))
36.    .Sortable()
37.    .Scrollable(s => s.Enabled(true))
38.    .HtmlAttributes(new { style = "height:700px;" })
39.    .DataSource(dataSource => dataSource
40.        .Ajax()
41.        .PageSize(25)
42.        .Events(events => events.Error("error_handler"))
43.        .Model(model =>
44.        {
45.            model.Id(p => p.Id);
46.            model.Field(p => p.Id).Editable(false);
47.            model.Field(p => p.CharterId).Editable(false);
48.        })
49.        .Sort(sort =>
50.        {
51.            sort.Add(p => p.CharterName);
52.        })
53.        .Read(read => read.Action("ErmhsL2BudgetRequests_Read", "FiscalReport"))
54.    )
55.)

[Viktor Tachev]

Hello Logan,

In order to customize the format of the values that will be displayed in the exported excel file you would need to handle the Grid excelExport event. For more information on specifying the format of Excel cells please refer to the following article.

• https://docs.telerik.com/kendo-ui/controls/data-management/grid/how-to/excel/cell-format

Furthermore, the example below illustrates formatting dates in the exported excel file:

• https://docs.telerik.com/kendo-ui/knowledge-base/date-format-excel-export-grid

Try to use similar approach and you would be able to implement the behavior you are looking for. 


Regards,
Viktor Tachev
Progress Telerik

[Laura]

Thank you for your response Viktor!

Those three links took me to kendo-ui examples, which didn't really help (I'm using UI for ASP.NET MVC), however I stumbled upon this https://docs.telerik.com/aspnet-mvc/helpers/grid/how-to/Export/cell-format which pointed me here https://github.com/telerik/ui-for-aspnet-mvc-examples/tree/master/grid/cell-format-excel.

Hopefully the code sample below will help someone as much as it helped me:

01.@(Html.Kendo().Grid<KendoUIMVC5.Models.Order>()
02.    .Name("grid")
03.    .ToolBar(tools => tools.Excel())
04.    .Events(e => e.ExcelExport("excelExport"))
05.    .Columns(columns => {
06.        columns.Bound(p => p.OrderID).Filterable(false).Width(100);
07.        columns.Bound(p => p.Freight).Width(100);
08.        columns.Bound(p => p.OrderDate).Format("{0:MM/dd/yyyy}").Width(140);
09.        columns.Bound(p => p.ShipName);
10.        columns.Bound(p => p.ShipCity).Width(150);
11.    })
12.    .Pageable()
13.    .DataSource(dataSource => dataSource
14.        .Ajax()
15.        .PageSize(20)
16.        .Read(read => read.Action("Orders_Read", "Home"))
17.     )
18.)
19.<script>
20.    function excelExport(e) {
21.        var sheet = e.workbook.sheets[0];
22.        for (var rowIndex = 1; rowIndex < sheet.rows.length; rowIndex++) {
23.            var sheet = e.workbook.sheets[0];
24.
25.            for (var rowIndex = 1; rowIndex < sheet.rows.length; rowIndex++) {
26.                var row = sheet.rows[rowIndex];
27.                row.cells[1].format = "[Blue]#,##0.0_);[Red](#,##0.0);0.0;"
28.            }
29.        }
30.    }
31.</script>

 

I found line 23 to be unnecessary and I updated line 27 to read:

row.cells[4].format = "MM/dd/yy hh:mm"

where 4 is the 5th column on my spreadsheet (the date column).

 

Thank you again!

 

[Viktor Tachev]

Hello Logan,

Thank you for sharing your approach with the community. This can help someone with similar scenario.

I pointed to these examples because the code used for specifying format in the exported cells was JavaScript. The relevant logic in both the jQuery widgets and in the MVC wrappers is executed client-side in the excelExport event handler. 


Regards,
Viktor Tachev
Progress Telerik

[Simon]

I have a similar question - formatting dates when exporting to Excel. We do our exporting in a controller method - there is no grid displayed in the View, just a number of buttons to launch export to excel functionality. 

I followed/modified the code in this sample: https://demos.telerik.com/aspnet-mvc/grid/server-export

All is well except I can't seem to format date values on exporting. here's part of the code in the controller method:

 

                var entities = _repository.GetSchedulesByAcademicYear(acadYear);
                var models = FeesMapper.MapSchedulesForExport(entities);

                var columnsData = new List<ExportColumnSettings>();

I then manually add a number of ExportColumnSettings objects to columnsData, for our date properties I use:

 

                var col = new ExportColumnSettings
                {
                    Field = "StartDate",
                    Title = "Start Date",
                    Format = "{0:MM/dd/yyyy}",
                    Width = 150
                };

The format is not applied in the resulting Excel document however, I just get an integer  such as 44216 for these date values. I can alter the format in Excel and it does render a date as expected but I can't ask our users to do that. I've tried all sorts of format strings for the Format property - what am I doing wrong?

Thank you.

Simon

 

[Viktor Tachev]

Hi Simon,

 

By default the cells in Excel will treat the values as numbers and because of this you are seeing these values for dates. In order to show the expected result the format of the cells should be configured.

In the server export example the code that handles the format of the cells is the following:

private void ChangeCellStyle(ExportCellStyle e)
{
	bool isHeader = e.Row == 0;
	SpreadCellFormat format = new SpreadCellFormat
	{
		ForeColor = isHeader ? SpreadThemableColor.FromRgb(50, 54, 58) : SpreadThemableColor.FromRgb(214, 214, 217),
		IsItalic = true,
		VerticalAlignment = SpreadVerticalAlignment.Center,
		WrapText = true,
		Fill = SpreadPatternFill.CreateSolidFill(isHeader ? new SpreadColor(93, 227, 0) : new SpreadColor(50, 54, 58))
	};
	e.Cell.SetFormat(format);
}

 

In addition to these options there is a NumberFormat property that can be used to specify the format of the cells. You can use it to show dates like so:

 SpreadCellFormat format = new SpreadCellFormat() 
    { 
        NumberFormat = "dd/mm/yyyy", 
        IsBold = true 
    }; 
    cell.SetFormat(format);

 

That said, the server export functionality uses the Telerik Document Processing Library underneath. If you would like more information on it and on formatting the cells you would find the article below interesting:

https://docs.telerik.com/devtools/document-processing/libraries/radspreadstreamprocessing/model/cells#set-a-format

 

Regards,
Viktor Tachev
Progress Telerik

[Simon]

Viktor, that's exactly what I needed, thank you very much.

 

Simon