Copy from Excel and paste on DataGrid

[Ruchi Gupta]

Hi All, Has anybody implemented copying from excel and pasting on to Datagrid. Thanks Ruchi

[Nick Parker]

This should work, I just threw it together so you will need to test it, but you should be able to get the idea.

IDataObject obj = Clipboard.GetDataObject();
DataTable table = new DataTable();
string data = string.Empty;
Char delim = ',';
if(obj.GetDataPresent(DataFormats.CommaSeparatedValue))
{
StreamReader reader = new StreamReader((Stream)obj.GetData(DataFormats.CommaSeparatedValue));
while(reader.Peek() > 0)
{
data = reader.ReadLine();
string[] arr = data.Split(delim);
if(table.Columns.Count <= 0)
{
for(int i = 0; i < arr.GetUpperBound(0);i++)
table.Columns.Add();
}
DataRow row = table.NewRow();
for(int j = 0; j < arr.GetUpperBound(0);j++)
{
row[j] = arr.GetValue(j);
}
table.Row.Add(row);
}
reader.Close();
dataGrid.DataSource = table.DefaultView();
}

- Nick Parker
My Blog | My Articles

[Heath Stewart]

When you copy data in Excel, it uses several clipboard formats to store data (actually, quite a few more than "several"). Several of these will work, but your best bet is to use the "Csv" clipboard format):

IDataObject data = Clipboard.GetDataObject();
if (data != null)
{
string csv = data.GetData("Csv") as string;
if (csv != null)
{
using (StringReader reader = new StringReader(csv))
{
string line = null;
while ((line = reader.ReadLine()) != null)
// Continue processing...
}
}
}

Where I've placed the comment you can either take the cheap way out and use line.Split(',').Trim('\"'), or parse it correctly by using a token reader. While there's no standard CSV format, the agreed-upon convention is to quote a field when the field value contains a comma (since the file is comma-delimited). So, when you encounter a quote set a flag (or increment a variable, whatever). When you encounter a comma, if the flag is set, then don't split the line. When you enounter the next quote, reset the flag. As long as that flag isn't set, you can split the field at the comma when you hit one. There's other formats you can use as well. Excel 2003 copies the new standard Excel XML format to the clipboard, as well as many other formats (both text and binary formats). You can use Mike Dunn's ClipSpy[^] to view the different formats, as well as the data those formats contain. The real trick here is that you have to have some way to paste into the DataGrid. Once one of the cells is in edit mode, the DataGrid receives little to no notification; the control being hosted in the current cell is receiving notifications. You could either use a paste button or jump through hoops to support Ctrl+V (or the mouse) to paste into the DataGrid. From there, it's as simple as the getting the data source (taking both DataSource and DataMember into account) and adding a new row/object for each line and, if applicable, each field in that row for each imported field from the CSV clipboard format. And easy way is to use the CurrencyManager like so:

CurrencyManager cm = (CurrencyManager)dataGrid1.BindingContext[
dataGrid1.DataSource, dataGrid1.DataMember];
if (cm != null)
{
IList list = cm.List;
if (list is