RadGrid Export: Date Column Format Not Respected

[Michelle]

When I export my RadGrid to an ExcelML file, the date format isn't respected.  My dates are displayed via {0:MM/dd/yyyy}, but the entire instant shows up in my Excel file.

It looks like a cell format can be specified somehow...I've found the following class

   Telerik.Web.UI.GridExcelBuilder.NumberFormatType.ShortDate

but I can't figure out how to manually set cell formats.

Any thoughts?

Thanks!
Michelle

[Michelle]

I figured it out.  It's a bit of a workaround, and I'm not sure I'm doing it the "proper" way, but the solution works...

In grid_ExcelMLExportRowCreated, I set a custom style for my date cells. 

      e.Row.Cells.GetCellByName("ExpirationDate").StyleValue = "dateStyle"

Then, in grid_ExcelMLExportStylesCreated, I create the custom style.

      Dim dateStyle As New StyleElement("dateStyle")

      dateStyle.FontStyle.FontName = "Tahoma"

      dateStyle.InteriorStyle.Pattern = InteriorPatternType.Solid

      Dim numberFormatElement As New Telerik.Web.UI.GridExcelBuilder.NumberFormatStyleElement

      numberFormatElement.FormatType = GridExcelBuilder.NumberFormatType.ShortDate

      dateStyle.InnerElements.Add(numberFormatElement)

      'this adds a <NumberFormat> element; but one already exists; be sure to remove the 

      'duplicate in RadGridProtocols_GridExporting

      e.Styles.Add(dateStyle)

Finally, in grid_GridExporting, I remove the duplicate <NumberFormat> elements, as follows:

      e.ExportOutput = e.ExportOutput.Replace("<NumberFormat></NumberFormat><NumberFormat", "<NumberFormat")

It's a bit of a hack, but it works to produce dates of the format MM/dd/yyyy.

What is the "proper" way to do this?

Michelle

[Rosen]

Hi Michelle,

You can modify the existing style for values of DateTime type by modifying the alternatingDateItemStyle and dateItemStyle styles, similar to this:

 Protected Sub RadGrid1_ExcelMLExportStylesCreated(ByVal source As Object, ByVal e As GridExcelBuilder.GridExportExcelMLStyleCreatedArgs)
        For Each styleElement As GridExcelBuilder.StyleElement In e.Styles
            If styleElement.Id = "alternatingDateItemStyle" OrElse styleElement.Id = "dateItemStyle" Then
                styleElement.NumberFormat.FormatType = GridExcelBuilder.NumberFormatType.ShortDate
            End If
        Next
    End Sub

Sincerely yours,
Rosen
the Telerik team

Check out Telerik Trainer, the state of the art learning tool for Telerik products.

[Michelle]

Excellent!  Works perfectly!

I had a hard time figuring this out on my own.  Could the online documentation be updated to include this information?

Thanks,
Michelle

[Sebastian]

Hello Michelle,

Thank you for the suggestion - this information will be included for the next version of the documentation. I updated your Telerik points for the feedback.

Best regards,
Stephen
the Telerik team

Check out Telerik Trainer, the state of the art learning tool for Telerik products.

[Mark Galbreath]

What documentation?  And what's with the Telerik points?  What feedback???  What's going on here???

[Sebastian]

Hello Mark,

Review the communication in this public forum thread:
http://www.telerik.com/community/forums/aspnet-ajax/grid/exportsettings-api.aspx

Best regards,
Sebastian
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Watch a video on how to optimize your support resource searches and check out more tips on the blogs.

[Mark Galbreath]

Hi Sebastian,
Did you notice that I started that thread?

[Dhamodharan]

Hi,

i am using same code what you gave. but i want to get date in this format ("MM-dd-yyyy"). is this possible?

Please let me know?

Thanks in advance,
Dhamu

[JD]

Hi,

Same question as the original, but formatting the date column(s) in a Word or PDF export.  I can't figure out a way to do this using ItemCreated because I can't get at any information about the column to find out if it's the date column as I loop through the cells.

Thanks,
JD

[Kostadin]

Hi JD,

The formatting of Word is set a little bit different than the ExcelML Excel format. More information could be found at the following help article. Basically you have to hook OnExportCellFormatting event handler and assign a format on each cell which you want to be formatted. As to Pdf you could review the following help topic. You could unify both methods by using the ItemDataBound event handler to set a custom format. At this event you will be able to extract the needed information from the grid cells.

Regards,
Kostadin
Telerik