Import Excel data to DataGridView in Visual Studio 2010

Question

Please help to fix importing data from Excel document to DataGridView control with following code:

private void button5_Click(object sender, EventArgs e)
{
    Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
    Excel.Workbook workbook =app.Workbooks.Open(@"C:\Users\Admin\Desktop\Dropbox\Vandit's Folder\Internship\test.xlsx");
    Excel.Worksheet worksheet = workbook.ActiveSheet;

    rcount = worksheet.UsedRange.Rows.Count;

    int i = 0;

    for(;i<rcount;i++)
    {
        dataGridView1.Rows[i].Cells["Column1"].Value = worksheet.Cells[i + 1, 1].Value;
        dataGridView1.Rows[i].Cells["Column2"].Value = worksheet.Cells[i + 1, 2].Value;
    }
}

when i run this code, I always get an exception saying

"Index was out of range. Must be non-negative and less than the size of the collection."
"Parameter name: index."

Answer 1

you can add rows like below

for(int i=0;i<rcount;i++)
{
 dataGridView1.Rows.Add(orksheet.Cells[i + 1, 1].Value,  worksheet.Cells[i + 1, 2].Value);
}

What you are doing is set the values of existing rows of gridview. if gridview not having rows given by index then you will get exception

but without all these you can use Ado.net and get read the data from excel and bind it to gridview. check below sample code from this KB article

// Create connection string variable. Modify the "Data Source"
// parameter as appropriate for your environment.
String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
    "Data Source=" + Server.MapPath("../ExcelData.xls") + ";" +
    "Extended Properties=Excel 8.0;";

// Create connection object by using the preceding connection string.
OleDbConnection objConn = new OleDbConnection(sConnectionString);

// Open connection with the database.
objConn.Open();

// The code to follow uses a SQL SELECT command to display the data from the worksheet.

// Create new OleDbCommand to return data from worksheet.
OleDbCommand objCmdSelect =new OleDbCommand("SELECT * FROM myRange1", objConn);

// Create new OleDbDataAdapter that is used to build a DataSet
// based on the preceding SQL SELECT statement.
OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();

// Pass the Select command to the adapter.
objAdapter1.SelectCommand = objCmdSelect;

// Create new DataSet to hold information from the worksheet.
DataSet objDataset1 = new DataSet();

// Fill the DataSet with the information from the worksheet.
objAdapter1.Fill(objDataset1, "XLData");

// Bind data to DataGrid control.
DataGrid1.DataSource = objDataset1.Tables[0].DefaultView;
DataGrid1.DataBind();

// Clean up objects.
objConn.Close();