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

Date cells registered as Date in Excel ?

4 Answers 58 Views
Spreadsheet
This is a migrated thread and some comments may be shown as answers.
Barry
Top achievements
Rank 1
Barry asked on 21 Feb 2019, 07:19 PM

This is a bit hard to describe, but I have a RadGridView that I'm exporting to Excel.

 

The date and time fields register as "General" (under Right Click --> "Format Cells") when I need them to be either "Date" or "Time" based on what they are. The Excel sorting of DateTime fields doesn't work property otherwise - it seems to sorts on the textual representation as opposed to proper dates.

The attached picture clearly shows what I have in mind.

How do I get Excel to see them (checkboxed) as either "Date" (for date columns/cells) or "Time" (for time columns/cells)...instead of "General" ?

 

Barry

4 Answers, 1 is accepted

Sort by
0
Barry
Top achievements
Rank 1
answered on 21 Feb 2019, 07:24 PM

Forgot to add a snippet....this (for example) doesn't work:

  if (gridColumns[colindex].Tag.Equals("DateTime"))
  {
     worksheet.Columns[colindex].SetFormat(new CellValueFormat("MM/dd/yyyy hh:mm:ss"));                 
  }

...also not sure if I should be trying to format the Worksheet column, or individual cells.

0
Martin Ivanov
Telerik team
answered on 26 Feb 2019, 11:04 AM
Hello Barry,

Can you check the following article and let me know if it helps? - https://docs.telerik.com/devtools/wpf/controls/radgridview/export/how-to/export-datetime

If it doesn't help can you please share some code showing how your row model is structured and how exactly you export the control?

Regards,
Martin Ivanov
Progress Telerik
Get quickly onboarded and successful with your Telerik and/or Kendo UI products with the Virtual Classroom free technical training, available to all active customers. Learn More.
0
Barry
Top achievements
Rank 1
answered on 26 Feb 2019, 04:20 PM

Hi,

Does the code in the link you provided get implemented before my:

grid.ExportToXlsx(stream, new GridViewDocumentExportOptions() { AutoFitColumnsWidth = true, ExportDefaultStyles = false, ShowColumnHeaders = true, Items = items });

XlsxFormatProvider providerXlsx = new XlsxFormatProvider();
workbook = providerXlsx.Import(stream);

?...

Thanks,

Barry

0
Martin Ivanov
Telerik team
answered on 01 Mar 2019, 04:11 PM
Hi Barry,

Yes, the event handler should be attached before the call of the ExportToXlsx() method. 

Regards,
Martin Ivanov
Progress Telerik
Get quickly onboarded and successful with your Telerik and/or Kendo UI products with the Virtual Classroom free technical training, available to all active customers. Learn More.
Tags
Spreadsheet
Asked by
Barry
Top achievements
Rank 1
Answers by
Barry
Top achievements
Rank 1
Martin Ivanov
Telerik team
Share this question
or