CODEWITHSUNDEEP
c#visual-studio-2010datagridview
MRu
MRu

Reputation: 1395

C# - How to get the header from excel into datagridview?

I have the code below to import value from excel to datagridview. But i want only the first row which is the header to be arranged vertically down in one column only. Here is the code. I have tried using something like dtExcel.Rows[0][0].ToString(); but it doesn't work. Can someone tell me why and how can i achieve this?

        private void button1_Click(object sender, EventArgs e)
    {
        string filePath = string.Empty;
        string fileExt = string.Empty;
        OpenFileDialog file = new OpenFileDialog(); //open dialog to choose file  
        if (file.ShowDialog() == System.Windows.Forms.DialogResult.OK) //if there is a file choosen by the user  
        {
            filePath = file.FileName; //get the path of the file  
            fileExt = Path.GetExtension(filePath); //get the file extension  
            if (fileExt.CompareTo(".xls") == 0 || fileExt.CompareTo(".xlsx") == 0)
            {
                try
                {
                    DataTable dtExcel = new DataTable();
                    dtExcel = ReadExcel(filePath, fileExt); //read excel file  
                    dataGridView1.Visible = true;
                    dataGridView1.DataSource = dtExcel;
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message.ToString());
                }
            }
            else
            {
                MessageBox.Show("Please choose .xls or .xlsx file only.", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Error); //custom messageBox to show error  
            }
        }  
    }

    public DataTable ReadExcel(string fileName, string fileExt)
    {
        string conn = string.Empty;
        DataTable dtexcel = new DataTable();
        if (fileExt.CompareTo(".xls") == 0)
            conn = @"provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties='Excel 8.0;HRD=Yes;IMEX=1';"; //for below excel 2007  
        else
            conn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties='Excel 12.0;HDR=NO';"; //for above excel 2007  
        using (OleDbConnection con = new OleDbConnection(conn))
        {
            try
            {
                OleDbDataAdapter oleAdpt = new OleDbDataAdapter("select * from [Sheet1$]", con); //here we read data from sheet1  
                oleAdpt.Fill(dtexcel); //fill excel data into dataTable  
            }
            catch { }
        }
        return dtexcel;
    }

Upvotes: 0

Views: 1239

Answers (2)

Dartek12
Dartek12

Reputation: 172

The header row you can get iterating through:

string[] columnName = new string[dtexcel.Columns.Count);
for (int i = 0; i < dtexcel.Columns.Count; i++)
     columnName[i] = dtexcel.Columns[i].ColumnName;

If the field is null but the column has items then its name will be "F{i}", where i is a number of column.

Upvotes: 0

Kason
Kason

Reputation: 797

 foreach (DataRow row in dtexcel.Rows) {
    for (int i = 0; i < dtexcel.Columns.Count;i++ )
       MessageBox.Show(row[i].ToString()); // row[i] is what you want.
    }

This code can help you check all the records in the table.

Upvotes: 1

Related Questions