Formatting dates in Excel Export

4 posts, 1 answers
  1. Laura
    Laura avatar
    7 posts
    Member since:
    Dec 2016

    Posted 06 Feb 2018 Link to this post

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

     

     

  2.
  3. Answer
    Viktor Tachev
    Admin
    Viktor Tachev avatar
    2480 posts

    Posted 07 Feb 2018 Link to this post

    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.
  5. Laura
    Laura avatar
    7 posts
    Member since:
    Dec 2016

    Posted 07 Feb 2018 in reply to Viktor Tachev Link to this post

    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!

     

  7. Viktor Tachev
    Admin
    Viktor Tachev avatar
    2480 posts

    Posted 08 Feb 2018 Link to this post

    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.
Back to Top