C# 在 Visual Studio 2010 中将 Excel 数据导入到 DataGridView

Import Excel data to DataGridView in Visual Studio 2010

提问by Vandit Agarwal

Please help to fix importing data from Exceldocument to DataGridViewcontrol with following code:

请帮助修复从Excel文档导入数据以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;
    }
}

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

采纳答案by Viral Jain

Assuming dataGridView1has 2 columns,

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

Assuming dataGridView1has 0 columns,

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

回答by Damith

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

您正在做的是设置gridview 现有行的值。如果 gridview 没有由索引给出的行，那么你会得到异常

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

但是没有所有这些，您可以使用 Ado.net 并从 excel 读取数据并将其绑定到 gridview。检查此知识库文章中的以下示例代码

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

回答by mck

This error means that the index which throw error is either not exists in gridview or there is column missing in your excel file.

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

Number of index in datagridView must be equal to number of fields you send for datagridview.

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

回答by RAMARAJ.M

Try Below code

试试下面的代码

 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;