This website uses Google cookies to provide its services and analyze your traffic. Your IP address and user-agent are shared with Google, along with performance and security metrics, to ensure quality of service, generate usage statistics and detect and address abuses.More information

Ver sitio en español Go to homepage Contact me
sábado, 25 de junio de 2016

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.

Share this article: Share in Twitter Share in Facebook Share in Google Plus Share in LinkedIn
Comments (0):
* (Your comment will be published after revision)

E-Mail


Name


Web


Message


CAPTCHA
Change the CAPTCHA codeSpeak the CAPTCHA code