How to copy and paste to Excel from a DataGridView

When multiple rows are selected in a DataGridView control and Ctrl+C is pressed to copy its content, the default behavior copies the cell values in plain text format separated by tabulators. Just with this you can paste the data into an Excel spreadsheet, but you can implement this behavior in a more sophisticated way, applying a custom format to the columns depending on the data type of each cell.

The data in an Excel spreadsheet can be imported and exported in XML format according to a predefined scheme document, but it is also able to interpret and convert HTML tables into Excel tables. This is what we will do to export the selected rows from the DataGridView.

First, you have to write a function to transform the selected rows in an HTML table:

private void CopyToExcel(bool hdr)
{
if (dataGrid.SelectedRows.Count > 0)
{
string scopy = "<table>{0}<tbody>";
string sheaders = "";
foreach (DataGridViewRow row in dataGrid.SelectedRows)
{
if (hdr && string.IsNullOrEmpty(sheaders))
{
sheaders = "<theader><tr>";
foreach (DataGridViewCell cell in row.Cells)
{
sheaders += "<th>" + cell.OwningColumn.HeaderText +
"</th>";
}
sheaders += "</tr></theader>";
}
scopy += "<tr>";
foreach (DataGridViewCell cell in row.Cells)
{
if (cell.Value != null)
{
if (cell.ValueType == typeof(DateTime))
{
scopy +=
"<td style=mso-number-format:\"dd/MM/yyyy HH:mm\">"
+ cell.Value.ToString() + "</td>";
}
else if (cell.ValueType == typeof(bool))
{
scopy += "<td style=mso-number-format:\"\\@\">" +
(Convert.ToBoolean(cell.Value) ? "Yes" : "No") +
"</td>";
}
else if (cell.ValueType == typeof(int))
{
scopy += "<td style=mso-number-format:\"0\">" +
cell.Value.ToString() + "</td>";
}
else if (cell.ValueType == typeof(double))
{
scopy += "<td style=mso-number-format:\"0.00\">" +
cell.Value.ToString() + "</td>";
}
else
{
scopy += "<td style=mso-number-format:\"\\@\">" +
cell.Value.ToString() + "</td>";
}
}
else
{
scopy += "<td style=mso-number-format:\"\\@\"/>";
}
}
scopy += "</tr>";
}
scopy += "</tbody></table>";
Clipboard.SetData(DataFormats.Text, string.Format(scopy, sheaders));
}
}

The hdr function parameter indicates whether to also copy the column headers, and, in order to apply the correct format to each cell, you can use the mso-number-format element to define the cell style, in this case, based on the data type of it value.

Now we just have to call this function in a keyboard event, when the user types the Ctl+C key combination, for example in KeyDown:

private void dataGrid_KeyDown(object sender, KeyEventArgs e)
{
if ((e.KeyCode == Keys.C) && (e.Modifiers == Keys.Control))
{
CopyToExcel(true);
e.Handled = true;
}
}

It is important to change to true the e.Handled property, because, otherwise, the default behavior of the control will overwrite the clipboard data.