WPF DataGrid copy to clipboard: how to influence data formats in the clipboard?

Question

There is good default feature in WPF DataGrid, which copies selected cells into Clipboard, so we can use it in other applications. But it seems, that copy process can be improved in terms of performance and also there is some kind of ... bug during paste into excel I assume, needs to be fixed.

Steps to replicate bug (incorrect excel usage)

A.1) copy cells from WPF DataGrid, containing text values with and without leading zeros (e.g. "001"; "002"; "1"; "12345"; "ABC"; "0ABC")

A.2) paste copied data into excel. It is expected, that leading zeroes will be kept, but if possible, excel will convert numeric values to numeric data format (by default cell data type is General, which does the thing).

Steps to replicate bug (correct excel usage)

B.1) copy cells from WPF DataGrid, containing text values with and without leading zeros (e.g. "001"; "002"; "1"; "12345"; "ABC"; "0ABC")

B.2) select all cells in target excel file and change cells data type to Text

B.3) paste copied data into excel. It is expected, that leading zeroes will be kept, but they are gone as well!

Steps to see expected result in excel (correct excel usage)

C.1) copy the same data from Notepad to clipboard

C.2) select all cells in target excel file and change cells data type to Text

C.3) paste copied data into excel. It is expected, that leading zeroes will be kept and now it is true. We can see identical to the source data. WYSIWYG

I started to look into Clipboard data to find the difference between B.1 and C.1 (I also copied the same data from excel and checked out it as well...). What I found out with help of C# Clipboard Viewer Sample from Microsoft:

Clipboard Data Formats for data, copied from Notepad++:

            - Text (native)

            - UnicodeText (native)

            - System.String (autoconvertable)

            - Locale (native)

            - OEMText (native)

Clipboard Data Formats for data, copied from WPF DataGrid:

            - HTML Format (native)

            - Csv (native)

            - Text (native)

            - UnicodeText (native)

            - System.String (autoconvertable)

Clipboard Data Formats for data, copied from Excel:

            - EnhancedMetafile (native)

            - System.Drawing.Imaging.Metafile (autoconvertable)

            - MetaFilePict (native)

            - Bitmap (native)

            - System.Drawing.Bitmap (autoconvertable)

            - System.Windows.Media.Imaging.BitmapSource (autoconvertable)

            - Biff12 (native)

            - Biff8 (native)

            - Biff5 (native)

            - SymbolicLink (native)

            - DataInterchangeFormat (native)

            - XML Spreadsheet (native)

            - HTML Format (native)

            - Text (native)

            - UnicodeText (native)

            - System.String (autoconvertable)

            - Csv (native)

            - Hyperlink (native)

            - Rich Text Format (native)

            - Embed Source (native)

            - Object Descriptor (native)

            - Link Source (native)

            - Link Source Descriptor (native)

            - Link (native)

            - Format129 (native)

So what would be the good\best way to solve problem on step B.3? I am wondering about 2 options for now:

Option #1

find out the way to explain to wpf datagrid, that it needs to set data into clipboard in a specific format.

Option #2

Second option is to handle Ctrl+C with my own function, which will run through selected cells, talk to view behind the datagrid, get correct data, append to string builder and set text to the clipboard.

And what do you think will be the best solution for the above B.3?

1. Please ask an actual clear question. 2. You have no control over how an object is pasted into Excel. 3. You can store data in whatever formats you want into the Clipboard... see the How to: Add Data to the Clipboard page on MSDN. — Sheridan
1. Clear question is "how to solve unexpected behaviour, which is described in B section. Item B.3" 2. Obviously I don't, but programmatically. There is difference between A and B section - you need to use excel properly. Use Text data type before we paste. So we have some control over this. 3. Thanks, this goes into Option #2 I have in my mind, please do not post any additional links, I know how to copy data into clipboard :-) — LOCKI
Clear answer: Unless you developed Excel, then as I just mentioned, you have no control over how an object is pasted into Excel... or any other application. Paste functionality is implemented in the application that receives the clipboard data, so the best that you can do is to find out exactly what type of data Excel can accept and then manually put your data into that format (using the link I provided in my last comment). — Sheridan
Dear Sheridan. I am not asking about excel. At all. I am asking about copy data from WPF DataGrid into clipboard in the same manner, as it is done by Notepad. Is it clearer enough for you now? I also would like to recommend you to replicate steps, described in section 3. You will see, that it is possible to copy and paste data into excel as expected, without loss of leading zeros = no need in changes in excel. — LOCKI
and one more thing: "Unless you developed Excel, then as I just mentioned, you have no control over how an object is pasted into Excel... or any other application." this is true, only in case you look into this from only one single point of view - programmer's point of view. From normal user point of view of course you have control over how data is pasted into excel. This control is limited to functions, exposed to end users use (one of them is to change target cells data type). — LOCKI

LOCKI LOCKI · Accepted Answer · 2014-05-14T16:36:12

Ok. So since I am talking about copy from WPF DataGrid into clipboard... I looked into the source code of the datagrid (I should come up with this idea before and not ask this question.)

so, part of handler for copy in the source is :

        // Supported default formats: Html, Text, UnicodeText and CSV
        Collection<string> formats = new Collection<string>(new string[] { DataFormats.Html, DataFormats.Text, DataFormats.UnicodeText, DataFormats.CommaSeparatedValue });

so simple answer is: Option #1 is not possible (in other words - sinse data formats for clipboard are hard coded - no possibility to influence that). Option #2 is ok. But now I think that is make sense to inherit datagrid and override method

        protected virtual void OnExecutedCopy(ExecutedRoutedEventArgs args)

this will give me good solution for all cases.

WPF DataGrid copy to clipboard: how to influence data formats in the clipboard?

1 Answers