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

Question

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

@Kason Sorry, i didn't notice. I have included the code. Thank you — MRu
– MRu, Commented Jun 16, 2016 at 6:56
Enumerate through dtexcel.Columns() and get Name property : foreach (DataColumn col in dtexcel.Columns) — jdweng
– jdweng, Commented Jun 16, 2016 at 7:02
@Kason it's not necessarily A1. To be more specific the first row of excel a.k.a the header — MRu
– MRu, Commented Jun 16, 2016 at 7:06

Kason · Accepted Answer · 2016-06-16 07:05:47Z

1

 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.

answered Jun 16, 2016 at 7:05

Kason

7973 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MRu Over a year ago

Thank you. I manage to done something similar and get the value of the header. Many thanks again!

Dartek12 · Accepted Answer · 2016-06-16 07:44:31Z

0

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.

edited Jun 16, 2016 at 7:44

answered Jun 16, 2016 at 7:24

Dartek12

1721 gold badge2 silver badges12 bronze badges

2 Comments

Kason Over a year ago

table[i] dtexcel.Columns[i].ColumnName; ?

Dartek12 Over a year ago

equal sign was missed :)

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related