This is a migrated thread and some comments may be shown as answers.

Formatting dates in Excel Export

6 Answers 762 Views
Grid
This is a migrated thread and some comments may be shown as answers.
Laura
Top achievements
Rank 1
Laura asked on 06 Feb 2018, 05:35 PM

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

 

 

6 Answers, 1 is accepted

Sort by
0
Accepted
Viktor Tachev
Telerik team
answered on 07 Feb 2018, 10:16 AM
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.


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


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


Regards,
Viktor Tachev
Progress Telerik
Try our brand new, jQuery-free Angular components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
0
Laura
Top achievements
Rank 1
answered on 07 Feb 2018, 05:29 PM

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!

 

0
Viktor Tachev
Telerik team
answered on 08 Feb 2018, 01:31 PM
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
Try our brand new, jQuery-free Angular components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
0
Simon
Top achievements
Rank 1
answered on 19 Jan 2021, 03:53 PM

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

 

0
Viktor Tachev
Telerik team
answered on 21 Jan 2021, 11:47 AM

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

Virtual Classroom, the free self-paced technical training that gets you up to speed with Telerik and Kendo UI products quickly just got a fresh new look + new and improved content including a brand new Blazor course! Check it out at https://learn.telerik.com/.

0
Simon
Top achievements
Rank 1
answered on 21 Jan 2021, 12:51 PM

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

 

Simon

Tags
Grid
Asked by
Laura
Top achievements
Rank 1
Answers by
Viktor Tachev
Telerik team
Laura
Top achievements
Rank 1
Simon
Top achievements
Rank 1
Share this question
or