1

I have a weird issue when copying rows from a Telerik's RadGridView into excel, the problem has to do with a DateTime column.

The column in RadGridView has the correct format:

06/17/2016 02:30 PM

But when the user copies the rows and pastes them into excel the format gets screwed up:

2016-06-16 14:30 PM //24hr format with PM period lol

I have noticed that the problem happens only with values whose hour is greater than 12, for example if the value is 06/17/2016 08:54 AM it will get pasted into excel with the correct format.

So thinking that it has to do with the format i manually set the format string of the column with the following code:

dgv_history.Columns["Repair Date"].FormatString = "{0:MM/dd/yyyy HH:mm}";

I also tried

dgv_history.Columns["Repair Date"].FormatString = "{0:MM/dd/yyyy hh:mm tt}";

But these lines of code only change how the date is displayed in the RadGridView, if i try to copy/paste them into excel the problem persists.

This is how it looks like once pasted into excel:

2016-06-16 13:57 PM
2016-06-16 13:31 PM
6/16/2016 12:55
6/16/2016 12:33
6/16/2016 12:22
6/16/2016 11:48
6/16/2016 11:19

What can i do to solve this problem?, is it a bug?, i have been searching over their documentation but i still can't find anything related.

Thank you

Update 06/20/2016 12:15

Seems that this is a normal functionality of the RadGridView, as seen in this demo page http://docs.telerik.com/devtools/winforms/gridview/copy-paste-cut the result is the same as the one described in this question.

ArturoAP
  • 432
  • 2
  • 13

5 Answers5

1

I don't have a real answer to the question, but have a look to:

http://docs.telerik.com/devtools/wpf/controls/radgridview/clipboard/copying.html

You have the possibility to override the copied values (and you can check with the debugger what telerik is writing to the clipboard).

Sorry for adding this as answer, but I'm not yet allowed to post a comment.

Cheers

Thomas

Thomas Voß
  • 1,145
  • 8
  • 20
  • Don't worry any kind of information is valuable, thank you – ArturoAP Jun 20 '16 at 19:15
  • By the way i already checked the debugger and my DataTable has the correct date format, but the values from the copied rows of the RadGridView are formatted differently, if i apply a FormatString it will only change the way its displayed, but internally it will keep this weird 24Hr+Period format. – ArturoAP Jun 21 '16 at 22:25
  • Have you seen this post (first answer) in teleriks forum?: http://www.telerik.com/forums/radgrid-export-to-excel-date-format Maybe you can set your dateformat in the clipboard event like this as well – Thomas Voß Jun 22 '16 at 08:22
0

I experienced a similar problem when creating time cards years ago - no body likes to use that 24 hour time format conversion - and the 12 hour am/pm can throw people who aren't paying attention especially when copy/pasting from a 24 hour to double 12 hour format - converting data entry time into calculate-able formula in a basic formula example - once I incorporated a 12- into the formula where I started the formula: =IF(C8>D8,12-C8+D8,D8-C8 and then multiplied it back to a negative 1 later it solved itself.

Kimberly Stansell
  • 11
  • 2
0

Check if the Datetime settings on the client pc's regional settings are correct. Just as a suggestion if nothing else works

Carodice
  • 256
  • 4
  • 6
  • I tried your suggestion by changing the PC's regional settings, it worked for the displayed data in the grid view but not for the copied values into excel, i also tried setting the culture info of the column manually but got the same results, thank you for the suggestion. – ArturoAP Jun 22 '16 at 17:45
  • maybe try changing the cell in excel's display format to TEXT.Just a long shot. :) You can also maybe see if the display is correct under Registry> HKEY_CURRENT_USER> Control Panel> International> sShortDate/sLongdate – Carodice Jun 22 '16 at 20:03
0

I think the datetime that RadGridView generated is always like "2016-06-16 13:57 PM" however excel formats it by its builtin functionality. It is possibly caused by missing "PM" so that Excel added it and also convert it to the default datetime format. You can just change the cell property to "Text" before copying that should always treat your data as a string.

Ye Peng
  • 183
  • 6
  • Yes, i can do that but the final users might not be aware, and will have to go trough the hassle of doing this extra step, either way thanks for the answer. – ArturoAP Jun 22 '16 at 21:33
  • I see you have solved this problem, well done. However, I am just thinking why not build a "Export" feature to save data from Grid to other file formats such as Excel? – Ye Peng Jun 23 '16 at 05:53
  • I will definitely build that function later on, the reason i wanted to have the copy to clipboard functionality is that most of the users have their own excel sheets to wish they paste the selected data from the application, this way its faster for them to just Select->Copy->SwitchToUserSheet->Paste – ArturoAP Jun 23 '16 at 14:32
0

I was able to solve the problem, but i had to manipulate the data object being added to the clipboard.

As it turns the only problem is the period being appended at the end of the DateTime value, so in order to fix it i removed all periods in all datetime values using the following code.

DataObject dataobject = dgv_history.GetClipboardContent();

//Gets the data object as a CSV string
string dos = dataobject.GetText(TextDataFormat.CommaSeparatedValue);

//Replaces all the occurrences of AM & PM with an empty string 
dos = dos.Replace(" AM", string.Empty);
dos = dos.Replace(" PM", string.Empty);

//Sets the modified CSV string as the DataObject
dataobject = new DataObject(dos);

//Adds the DataObject to the clipboard
Clipboard.SetDataObject(dataobject, true);

By doing this, the date time gets pasted as a nicely formatted 24 hr format, and gets detected as such by excel.

There was another temporary solution i was able to create, but involves our SSRS server. I created a simple report with the same parameters used for the RadGridView, the report output can be easily downloaded as an excel spreadsheet without having to worry of the data being manipulated or translated by a WinFroms control.

Thanks everyone for your time and answers.

ArturoAP
  • 432
  • 2
  • 13