Formatting dates in Excel Export

5 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
    2495 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
    2495 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.
  9. Simon
    Simon avatar
    42 posts
    Member since:
    Mar 2016

    Posted 1 day and 13 hours ago Link to this post

    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

     

Back to Top