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

I have an excel sheet with the following:

enter image description here

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:

enter image description here

Am I approaching this task at hand incorrectly?

c++sharpexcelwinformsdatagridview


Solution 1:[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:

enter image description here

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

Solution 2:[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++;
            }
        }
    }

Solution 3:[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.

Solution 4:[4]

Have your gridview columns defined, if not you have to define the columns first.

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())
            {
                myDataGridViewRow = (DataGridViewRow) myDataGridView.RowTemplate.Clone();
                for (int i = 0; i < pastedRowCells.Length; i++)
                    myDataGridViewRow.Cells[i].Value = pastedRowCells[i];

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

IF not definced columns

private void PasteClipboard(DataGridView myDataGridView)
{
    //Create COlumns in datagridView
    myDataGridView = new DataGridView();
    myDataGridView.Columns.Add("col1", "Col1");
    myDataGridView.Columns.Add("col2", "Col2");
    myDataGridView.Columns.Add("col3", "Col3");
    myDataGridView.Columns.Add("col4", "Col4");

    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())
            {
                myDataGridViewRow = (DataGridViewRow) myDataGridView.RowTemplate.Clone();
                for (int i = 0; i < pastedRowCells.Length; i++)
                    myDataGridViewRow.Cells[i].Value = pastedRowCells[i];

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

Solution 5:[5]

A really nice solution was posted here:

But, one line needs to be changed:

 if (dgv.Rows.Count < (r + rowsInClipboard.Length))
            dgv.Rows.Add(r + rowsInClipboard.Length - dgv.Rows.Count);

needs to be changed to:

 if (dgv.Rows.Count < (r + rowsInClipboard.Length))
            dgv.Rows.Add(r + rowsInClipboard.Length+1 - dgv.Rows.Count);

If this line isn't changes, the last row pasted will not be passed to SQL.

Solution 6:[6]

In case you are dealing with Unicode here is the code to paste to a DataTable that is binded to a the DataGridView

        DataObject o = (DataObject)Clipboard.GetDataObject();
        if (o.GetDataPresent(DataFormats.Text))
        {
            string[] pastedRows = Regex.Split(o.GetText().TrimEnd("\r\n".ToCharArray()), "\r\n");
            foreach (string pastedRow in pastedRows)
            {
                string[] pastedRowCells = pastedRow.Split(new char[] { '\t' });
                var temp = dt1.NewRow();
                for (int i = 0; i < pastedRowCells.Length; i++)
                    temp[i] = pastedRowCells[i];
                dt1.Rows.Add(temp);
            }
        }

Solution 7:[7]

The code below allows to paste clipboard data starting from the selected cell, ignores if data exceeds the current column count and adds subsequent rows if required to accommodate for extra pasted rows

private void dataBatch_KeyUp(object sender, KeyEventArgs e)
{
    if (e.Modifiers == Keys.Control && e.KeyCode == Keys.V)
    {
        var gridView = (DataGridView)sender;
        DataObject o = (DataObject)Clipboard.GetDataObject();
        if (o.GetDataPresent(DataFormats.StringFormat))
        {
            string[] pastedRows = Regex.Split(o.GetData(DataFormats.StringFormat).ToString().TrimEnd(Environment.NewLine.ToCharArray()), Environment.NewLine);
            DataGridViewCell firstSelectedCell;
            try
            {
                firstSelectedCell = gridView.SelectedCells.Count > 0 ? gridView.SelectedCells[0] : (gridView.Rows.Count > 0 ? ((DataGridViewRow)(gridView.Rows[0])).Cells[0] : throw new Exception(""));
            }
            catch { return; }
            int initialColumnIndex = firstSelectedCell.ColumnIndex;

            var rowCounter = 1;
            var rowCount = pastedRows.Count();

            foreach (string pastedRow in pastedRows)
            {
                var cellData = pastedRow.Split('\t');
                foreach (var cd in cellData)
                {
                    firstSelectedCell.Value = cd;
                    firstSelectedCell.Selected = true;

                    if (firstSelectedCell.ColumnIndex < gridView.Columns.Count - 1)
                    {
                        firstSelectedCell = gridView[firstSelectedCell.ColumnIndex + 1, firstSelectedCell.RowIndex];
                    }
                    else
                    {
                        break;
                    }
                }
                if (rowCounter < rowCount)
                {
                    if (firstSelectedCell.RowIndex >= gridView.Rows.Count - 1)
                    {
                        gridView.Rows.Add();
                    }
                    firstSelectedCell = gridView[initialColumnIndex, firstSelectedCell.RowIndex + 1];
                    rowCounter++;
                }
            }
        }
    }
}

Solution 8:[8]

I've just modified @Latheesan's code as below which is the shortest version.

DataObject o = (DataObject)Clipboard.GetDataObject();

if (o.GetDataPresent(DataFormats.Text))
{
    if (myDataGridView.Rows.Count > 0)
        myDataGridView.Rows.Clear();
    if (myDataGridView.Columns.Count > 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(pastedRowCells);

        //***You don't need following lines, use just above line. ***

        //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];
        //}
    }
}

Solution 9:[9]

//Column Count Bug Propered:://

using System.Linq;

DataTable xDataTable = new DataTable();
DataObject XClipboardDat = (DataObject)Clipboard.GetDataObject();

if (XClipboardDat.GetDataPresent(DataFormats.Text))
{
    string[] XClipboardRows = Regex.Split(XClipboardDat.GetData(DataFormats.Text).ToString(), @"[\r\n]+").Where(y => !string.IsNullOrEmpty(y.ToString())).ToArray();

    IEnumerable<string[]> XDatRowCol = XClipboardRows.Select(xRow => Regex.Split(xRow, @"[\t]+").Where(y => !string.IsNullOrEmpty(y.ToString())).ToArray());

    int ColNum = XDatRowCol.Select(XDatRow => XDatRow.Length).ToArray().Max<int>();

    for (int i = 0; i < ColNum; i++) { xDataTable.Columns.Add(); }

    foreach(string[] XDatRow in XDatRowCol) { xDataTable.Rows.Add(XDatRow); }

    dataGridView2.DataSource = xDataTable;
 }

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Ophaedean Rhythm
Solution 3
Solution 4 Pratik Bhoir
Solution 5 takrl
Solution 6 Mahmoud Fayez
Solution 7
Solution 8 Nathan Tuggy
Solution 9

Related Questions