将Excel数据导入Visual Studio 2010中的DataGridView

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

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

假设dataGridView1有2列，

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;
        dataGridView1.Rows.Add(worksheet.Cells[i + 1, 1].Value, worksheet.Cells[i + 1, 2].Value);
    }
}

假设dataGridView1有0列，

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;
    //Initializing Columns
    dataGridView1.ColumnCount = worksheet.UsedRange.Columns.Count;
    for(int x=0;x<dataGridView1.ColumnCount;x++)
    {
            dataGridView1.Columns[x].Name = "Column "+x.ToString();
    }
    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;
        dataGridView1.Rows.Add(worksheet.Cells[i + 1, 1].Value, worksheet.Cells[i + 1, 2].Value);
    }
}

您可以像下面的一样添加行

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

您正在做的是设置gridview的现有行的值。如果网格视图没有由索引给定的行，那么您将获得异常

但如果没有这些，你可以使用Ado.net从excel中读取数据并将其绑定到gridview。请查看以下KB文章的示例代码

// 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();

此错误意味着引发错误的索引在gridview中不存在，或者excel文件中缺少列。

datagridView中的索引数必须等于为datagridView发送的字段数。

尝试以下代码

 DialogResult dialogResult = MessageBox.Show("Sure", "Some  Title",MessageBoxButtons.YesNo);
if (dialogResult == DialogResult.Yes)
{
    dt = dsSource.Tables[Index];
    dt.Reset();
    Excel.Workbook workbook;
    Excel.Worksheet NwSheet;
    Excel.Range ShtRange;
    Microsoft.Office.Interop.Excel.Application ExcelObj = new                 Microsoft.Office.Interop.Excel.Application();
    OpenFileDialog filedlgExcel = new OpenFileDialog();
    filedlgExcel.Title = "Select file";
    filedlgExcel.InitialDirectory = @"c:'";
    //filedlgExcel.FileName = textBox1.Text;
    filedlgExcel.Filter = "Excel Sheet(*.xlsx)|*.xlsx|All Files(*.*)|*.*";
    filedlgExcel.FilterIndex = 1;
    filedlgExcel.RestoreDirectory = true;
    if (filedlgExcel.ShowDialog() == DialogResult.OK)
    {
        workbook = ExcelObj.Workbooks.Open(filedlgExcel.FileName, Missing.Value, Missing.Value,
             Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
             Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
        NwSheet = (Excel.Worksheet)workbook.Sheets.get_Item(1);
        ShtRange = NwSheet.UsedRange;
        for (int Cnum = 1; Cnum <= ShtRange.Columns.Count; Cnum++)
        {
            dt.Columns.Add(new DataColumn((ShtRange.Cells[1, Cnum] as Excel.Range).Value2.ToString()));
        }
        dt.AcceptChanges();
        string[] columnNames = new String[dt.Columns.Count];
        for (int i = 0; i < dt.Columns.Count; i++)
        {
            columnNames[0] = dt.Columns[i].ColumnName;
        }
        //string[] columnNames = (from dc in dt.Columns.Cast<DataColumn>() select dc.ColumnName).ToArray();

        for (int Rnum = 2; Rnum <= ShtRange.Rows.Count; Rnum++)
        {
            DataRow dr = dt.NewRow();
            for (int Cnum = 1; Cnum <= ShtRange.Columns.Count; Cnum++)
            {
                if ((ShtRange.Cells[Rnum, Cnum] as Excel.Range).Value2 != null)
                {
                    dr[Cnum - 1] = (ShtRange.Cells[Rnum, Cnum] as Excel.Range).Value2.ToString();
                }
            }
            dt.Rows.Add(dr);
            dt.AcceptChanges();
        }
        workbook.Close(true, Missing.Value, Missing.Value);
        ExcelObj.Quit();
        dataGridView1.DataSource = dt;