locked
importing RowName and ColumnName from an excelfile to a datagridview ... RRS feed

  • Question

  • Hi All !
    I want to Import an Excel File to a DataGridView as FirstRow (Except A1 Cell that's null)will be Column HeaderCell in DataGridView and First Column in Excel file (Except Cell of A1=null) will be Row HeaderCell in DataGridView .Here is my code :
    string connectionString = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0;HDR=YES;IMEX=1;""", openFileDialog1.FileName);
    string query = String.Format("select * from [{0}$]", SheetName);
    OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, connectionString);
     
    DataSet ds = new DataSet();
     
    dataAdapter.Fill(ds);
    dataGridView1.DataSource = ds.Tables[0];
     
    int row = ds.Tables[0].Rows.Count;
    int col = ds.Tables[0].Columns.Count;
     
    for (int i = 0; i < row - 1; i++)
    {
      dataGridView1.Rows[i].HeaderCell.Value = ds.Tables[0].Rows[i].ToString(); ;
    }
     
    for (int j = 0; j < col - 1; j++)
    {
      dataGridView1.Columns[j].HeaderCell.Value = ds.Tables[0].Columns[j].ToString();
    }
    But I have some Problem for Header Column And Header Row .
    Thanks For Any Help .
    • Edited by s_mostafa_h Sunday, April 8, 2012 6:46 PM
    • Moved by CoolDadTx Monday, April 9, 2012 3:21 PM Winforms related (From:Visual C# General)
    Sunday, April 8, 2012 6:44 PM

Answers

  • Since you are using excel file with extension xlsx, I would go using (and retreiving data) with oledb provider (like you started).

    But do not bind data to dgv, since this way you will bind even the 1st column (which is something you dont want, because the data in 1st column are not interested to show). Create columns in dgv and populate it manually.

    One more thing tomention: you didnt use the correct connection string, the one provided is the right one.

    And some more things about connection string:

    using HDR=YES, it means that it will get column names provided in the 1st row of excel file (if set to NO, it will not create any column names inside dataTable (dataSet).

    Ok, here is what I did:

     string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Book1.xlsx;Extended Properties='Excel 12.0 xml;HDR=YES;'"; //change path to file!!!
                string SheetName = "SAW";
                string query = String.Format(@"select * from [{0}$]", SheetName);
                OleDbConnection conn = new OleDbConnection(connectionString);
                OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, conn);
                DataTable table = new DataTable("ExcelData");
                dataAdapter.Fill(table);
                //creating columns to dgv:
                for (int i = 0; i < table.Columns.Count; i++)
                    if (i > 0) //skipping 1st column (not creating in)
                        dataGridView1.Columns.Add(table.Columns[i].ColumnName, table.Columns[i].ColumnName);
                //populating dgv:
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    dataGridView1.Rows.Add(); //adding new row to dgv
                    for (int j = 0; j < table.Rows[i].ItemArray.Length; j++)
                    {
                        if (j > 0) //skipping 1st column
                            dataGridView1[j - 1, i].Value = table.Rows[i][j];
                    }
                }

    this code works, it was tested.


    Mitja

    Tuesday, April 10, 2012 5:51 AM

All replies

  • Does your excel file have column names (in 1st row)? If so, add do not bind it directly, since it will not be correct. You will have to populate dgv "manully" by code.

    From the 1st row of datatable create column names of dgv (use HeaderText property), and for the rest of the rows populate dgv.



    Mitja

    Sunday, April 8, 2012 9:45 PM
  • With Spire.XLS  

     you can use the following code to  importing RowName and ColumnName from an excelfile to a datagridview 


                 DataTable dt = new DataTable();

                //excel to dataTable
                dt = workbook.Worksheets[0].ExportDataTable();

    Good luck.

                                    
    Monday, April 9, 2012 2:44 AM
  • Thanks for your reply ,

    Here is my file , pl z check it .

    or : http://www.4shared.com/rar/6WxLLxtL/ReadFromExcelFile.html

    I've prob yet !

    Monday, April 9, 2012 2:12 PM
  • Since you are using excel file with extension xlsx, I would go using (and retreiving data) with oledb provider (like you started).

    But do not bind data to dgv, since this way you will bind even the 1st column (which is something you dont want, because the data in 1st column are not interested to show). Create columns in dgv and populate it manually.

    One more thing tomention: you didnt use the correct connection string, the one provided is the right one.

    And some more things about connection string:

    using HDR=YES, it means that it will get column names provided in the 1st row of excel file (if set to NO, it will not create any column names inside dataTable (dataSet).

    Ok, here is what I did:

     string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Book1.xlsx;Extended Properties='Excel 12.0 xml;HDR=YES;'"; //change path to file!!!
                string SheetName = "SAW";
                string query = String.Format(@"select * from [{0}$]", SheetName);
                OleDbConnection conn = new OleDbConnection(connectionString);
                OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, conn);
                DataTable table = new DataTable("ExcelData");
                dataAdapter.Fill(table);
                //creating columns to dgv:
                for (int i = 0; i < table.Columns.Count; i++)
                    if (i > 0) //skipping 1st column (not creating in)
                        dataGridView1.Columns.Add(table.Columns[i].ColumnName, table.Columns[i].ColumnName);
                //populating dgv:
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    dataGridView1.Rows.Add(); //adding new row to dgv
                    for (int j = 0; j < table.Rows[i].ItemArray.Length; j++)
                    {
                        if (j > 0) //skipping 1st column
                            dataGridView1[j - 1, i].Value = table.Rows[i][j];
                    }
                }

    this code works, it was tested.


    Mitja

    Tuesday, April 10, 2012 5:51 AM
  • my vote is 5!

    Thx , Very Nice !

    Tuesday, April 10, 2012 2:11 PM
  • :)

    Iam glad you like it.


    Mitja

    Tuesday, April 10, 2012 2:33 PM