Pasting excel data into a blank DataGridView - Index out of range exception

Question

I have an excel sheet with the following:

So, what I am trying to achieve is copy this from Excel and paste it into a blank DataGridView view.

This is the code I have so far:

private void PasteClipboard(DataGridView myDataGridView)
{
    DataObject o = (DataObject)Clipboard.GetDataObject();
    if (o.GetDataPresent(DataFormats.Text))
    {
        string[] pastedRows = Regex.Split(o.GetData(DataFormats.Text).ToString().TrimEnd("\r\n".ToCharArray()), "\r\n");
        foreach (string pastedRow in pastedRows)
        {
            string[] pastedRowCells = pastedRow.Split(new char[] { '\t' });
            using (DataGridViewRow myDataGridViewRow = new DataGridViewRow())
            {
                for (int i = 0; i < pastedRowCells.Length; i++)
                    myDataGridViewRow.Cells[i].Value = pastedRowCells[i];

                myDataGridView.Rows.Add(myDataGridViewRow);
            }
        }
    }
}

When the code runs, I am getting the following error:

Am I approaching this task at hand incorrectly?

Answer 1

After some digging around, I found that I have to add columns first, then add a new row, get the row index of the newly created row, and then set the cell values.

Here's the updated code:

DataObject o = (DataObject)Clipboard.GetDataObject();
if (o.GetDataPresent(DataFormats.Text))
{
    if (myDataGridView.RowCount > 0)
        myDataGridView.Rows.Clear();

    if (myDataGridView.ColumnCount > 0)
        myDataGridView.Columns.Clear();

    bool columnsAdded = false;
    string[] pastedRows = Regex.Split(o.GetData(DataFormats.Text).ToString().TrimEnd("\r\n".ToCharArray()), "\r\n");
    foreach (string pastedRow in pastedRows)
    {
        string[] pastedRowCells = pastedRow.Split(new char[] { '\t' });

        if (!columnsAdded)
        {
            for (int i = 0; i < pastedRowCells.Length; i++)
                myDataGridView.Columns.Add("col" + i, pastedRowCells[i]);

            columnsAdded = true;
            continue;
        }

        myDataGridView.Rows.Add();
        int myRowIndex = myDataGridView.Rows.Count - 1;

        using (DataGridViewRow myDataGridViewRow = myDataGridView.Rows[myRowIndex])
        {
            for (int i = 0; i < pastedRowCells.Length; i++)
                myDataGridViewRow.Cells[i].Value = pastedRowCells[i];
        }
    }
}

}

And here it is working:

Happy to accept criticisms and useful tips on improving this. This code is quite slow...

Answer 2

I know it's some years later, but I was looking for a solution for this problem and found BASA's modification of Latheesan's code. It only worked partially, so modifying it, I would like to add this solution for future browsers:

private void Paste(DataGridView d)
    {
        DataObject o = (DataObject)Clipboard.GetDataObject();
        if (o.GetDataPresent(DataFormats.StringFormat))
        {
            string[] pastedRows = Regex.Split(o.GetData(DataFormats.StringFormat).ToString().TrimEnd("\r\n".ToCharArray()), "\r");
            int j = 0;
            try { j = d.CurrentRow.Index; } catch { }
            foreach (string pastedRow in pastedRows)
            {
                DataGridViewRow r = new DataGridViewRow();
                r.CreateCells(d, pastedRow.Split(new char[] { '\t' }));
                d.Rows.Insert(j, r);
                j++;
            }
        }
    }

Answer 3

Perfect Code here: (write in button)

DataObject o = (DataObject)Clipboard.GetDataObject();
if (o.GetDataPresent(DataFormats.Text))
{
    if (myDataGridView.RowCount > 0)
        myDataGridView.Rows.Clear();

    if (myDataGridView.ColumnCount > 0)
        myDataGridView.Columns.Clear();

    bool columnsAdded = false;
    string[] pastedRows = Regex.Split(o.GetData(DataFormats.Text).ToString().TrimEnd("\r\n".ToCharArray()), "\r\n");
    int j=0;
    foreach (string pastedRow in pastedRows)
    {
        string[] pastedRowCells = pastedRow.Split(new char[] { '\t' });

        if (!columnsAdded)
        {
            for (int i = 0; i < pastedRowCells.Length; i++)
                myDataGridView.Columns.Add("col" + i, pastedRowCells[i]);

            columnsAdded = true;
            continue;
        }

        myDataGridView.Rows.Add();
        int myRowIndex = myDataGridView.Rows.Count - 1;

        using (DataGridViewRow myDataGridViewRow = myDataGridView.Rows[j])
        {
            for (int i = 0; i < pastedRowCells.Length; i++)
                myDataGridViewRow.Cells[i].Value = pastedRowCells[i];
        }
        j++;
    }
}

Modified from Latheesan's code.