Copy from excel to DataGridView c#

Question

I am trying to copy multiple excel rows to my datagridview, to then be send to my DB. I copy the excel rows using Ctrl + C then i try pasting them using a button click event in the program, but i get an error:

My code as follows :

private void btnPaste_Click(object sender, EventArgs e)
    {

        try
        { 
        dgvPM.Focus();
        dgvPM.CurrentCell = dgvPM[1,1];
        string s = Clipboard.GetText();
        string[] lines = s.Replace("\n", "").Split('\r');
        string[] fields;
        int row = 0;
        int column = 0;
            foreach (string l in lines)
                {
                    fields = l.Split('\t');
                    foreach (string f in fields)
                        dgvPM[column++, row].Value = f;
                    row++;
                    column = 0;
                }
        }
        catch(SqlException ex)
        {
            MessageBox.Show(ex.Message);
        }
        catch(System.Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
    }

I don't know how else i could go about achieving this task. Any help would be appreciated !

UPDATE :

this is how my datagridview looks :

Sample of excel data that I'm pasting :

I assume one of the columns in your DataGridView contains numbers and you are trying to add a string value that can not be converted. — Tamás Szabó, Apr 05 '17 at 07:37
No, I have even tried formating the excel rows as to fit the datagridview perfectly and still this pops up. — Adi Mohan, Apr 05 '17 at 07:40
Could you post example data from the clipboard, and the designer part of your DataGridView? — Tamás Szabó, Apr 05 '17 at 07:59
Using the code you supplied and assuming the clipboard contents contained a tab-delimited string…One error you could get is if the string contained in the clipboard, has more columns or rows than the grid. This will throw an index out of range error. The other could be a formatting problem, which would possibly throw a Data Error if the column was looking for a number and got something else. **What is the error?** — JohnG, Apr 06 '17 at 09:26

score 3 · Answer 1 · answered Apr 23 '18 at 20:02

I had the same situation and your code helped me a lot. Here is mine which is working based on yours:

 private void button1_Click(object sender, EventArgs e)
    {
        string s = Clipboard.GetText();

        string[] lines = s.Replace("\n", "").Split('\r');

        dgPositions.Rows.Add(lines.Length-1); 
        string[] fields;
        int row = 0;
        int col = 0;

        foreach (string item in lines)
        {
            fields = item.Split('\t');
            foreach (string f in fields)
            {
                Console.WriteLine(f);
                dgPositions[col, row].Value = f ;                   
                col++;
            }
            row++;
            col = 0;
        }
    }

the problemwas the same.. I needed to copy from excel into datagridview.

To append instead of resetting the grid use > int row = dgPositions.Rows.Count - 1; — Corrpside, Jul 29 '21 at 16:00
In the case Lines = 1, it raise a exception for dgPositions.Rows.Add(0) — Dominique Mathieu, Nov 04 '22 at 13:44

Copy from excel to DataGridView c#

1 Answers1