CODEWITHSUNDEEP
c#excelwinformsdatagridview
Latheesan
Latheesan

Reputation: 24116

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?

Upvotes: 13

Views: 37064

Answers (9)

GameMaster Greatee
GameMaster Greatee

Reputation: 33

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++;
                }
            }
        }
    }
}

Upvotes: 0

Mahmoud Fayez
Mahmoud Fayez

Reputation: 3459

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);
            }
        }

Upvotes: 0

Ophaedean Rhythm
Ophaedean Rhythm

Reputation: 103

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++;
            }
        }
    }

Upvotes: 5

Vishnu
Vishnu

Reputation: 1

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

Upvotes: -1

mrliioadin
mrliioadin

Reputation: 21

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.

Upvotes: 2

BALA s
BALA s

Reputation: 169

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.

Upvotes: 5

mcansozeri
mcansozeri

Reputation: 1

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

Upvotes: -1

Latheesan
Latheesan

Reputation: 24116

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...

Upvotes: 10

Pratik Bhoir
Pratik Bhoir

Reputation: 2144

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);
            }
        }
    }
}

Upvotes: 3

Related Questions