Storing data in Clipboard to past into Microsoft Excel
-
I am trying to find a way to take records from a ListView control and move them to the system clipboard so they can be pasted directly into Microsoft Excel. I have found the DataFormat class and see it supports a CommaSeparatedValue format, but I cannot get it to work correctly. I have tried this, just as a test... string s = "1,2,3,4,5,6,7,8,9"; DataObject d = new DataObject(); d.SetData(DataFormats.CommaSeparatedValue,s); Clipboard.SetDataObject(d,false); But when I paste it into Excel I get –§žý;pC¦yVk²ˆû Have any of you ever successfully formatted data, stored it in the Clipboard so it could be successfully pasted into Excel? Darryl Borden Principal IT Analyst darryl.borden@elpaso.com
-
I am trying to find a way to take records from a ListView control and move them to the system clipboard so they can be pasted directly into Microsoft Excel. I have found the DataFormat class and see it supports a CommaSeparatedValue format, but I cannot get it to work correctly. I have tried this, just as a test... string s = "1,2,3,4,5,6,7,8,9"; DataObject d = new DataObject(); d.SetData(DataFormats.CommaSeparatedValue,s); Clipboard.SetDataObject(d,false); But when I paste it into Excel I get –§žý;pC¦yVk²ˆû Have any of you ever successfully formatted data, stored it in the Clipboard so it could be successfully pasted into Excel? Darryl Borden Principal IT Analyst darryl.borden@elpaso.com
The Comma-Delimited Value isn't a string - which is the confusing part. The following code will do what you want. // Place comma-delimited on clipboard string commaText = "1,2,3,4,5,6,7,8,9"; byte[] bytes = System.Text.Encoding.UTF8.GetBytes(commaText); MemoryStream stream = new MemoryStream(bytes); DataObject dataObject = new DataObject(); dataObject.SetData(DataFormats.CommaSeparatedValue, stream); Clipboard.SetDataObject(dataObject, true); // Read comma-delimited from clipboard string values=string.Empty; IDataObject dataObject = Clipboard.GetDataObject(); if(dataObject.GetDataPresent(DataFormats.CommaSeparatedValue)) { StreamReader streamReader = new StreamReader((Stream) dataObject.GetData(DataFormats.CommaSeparatedValue)); values = streamReader.ReadToEnd(); streamReader.Close(); } Console.WriteLine( values );
-
The Comma-Delimited Value isn't a string - which is the confusing part. The following code will do what you want. // Place comma-delimited on clipboard string commaText = "1,2,3,4,5,6,7,8,9"; byte[] bytes = System.Text.Encoding.UTF8.GetBytes(commaText); MemoryStream stream = new MemoryStream(bytes); DataObject dataObject = new DataObject(); dataObject.SetData(DataFormats.CommaSeparatedValue, stream); Clipboard.SetDataObject(dataObject, true); // Read comma-delimited from clipboard string values=string.Empty; IDataObject dataObject = Clipboard.GetDataObject(); if(dataObject.GetDataPresent(DataFormats.CommaSeparatedValue)) { StreamReader streamReader = new StreamReader((Stream) dataObject.GetData(DataFormats.CommaSeparatedValue)); values = streamReader.ReadToEnd(); streamReader.Close(); } Console.WriteLine( values );
I tried out your code and it works great! I have just two questions... 1) where on earth did you dig this up - I have searched all over the place 2) This pastes a single row into Excel - how to I paste multiple rows? Thanks so much for your help! dpb Darryl Borden Principal IT Analyst darryl.borden@elpaso.com
-
The Comma-Delimited Value isn't a string - which is the confusing part. The following code will do what you want. // Place comma-delimited on clipboard string commaText = "1,2,3,4,5,6,7,8,9"; byte[] bytes = System.Text.Encoding.UTF8.GetBytes(commaText); MemoryStream stream = new MemoryStream(bytes); DataObject dataObject = new DataObject(); dataObject.SetData(DataFormats.CommaSeparatedValue, stream); Clipboard.SetDataObject(dataObject, true); // Read comma-delimited from clipboard string values=string.Empty; IDataObject dataObject = Clipboard.GetDataObject(); if(dataObject.GetDataPresent(DataFormats.CommaSeparatedValue)) { StreamReader streamReader = new StreamReader((Stream) dataObject.GetData(DataFormats.CommaSeparatedValue)); values = streamReader.ReadToEnd(); streamReader.Close(); } Console.WriteLine( values );
Never mind on the 2nd question. If I insert a "\n" character in the string then it wraps to the next line in Excel! "1,2,3,4,5\n6,7,8,9" Thanks again for your help. dpb Darryl Borden Principal IT Analyst darryl.borden@elpaso.com