In C# how to access excel headers using OLEDB (without Automation)?

Question

This is my code where I am trying to access first row, first column

     string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;
                                      Data Source=" + fileName + @";Extended Properties=""Excel 8.0;HDR=NO;""";
            string CreateCommand = "SELECT * FROM [Sheet1$]";
            OleDbConnection conn = new OleDbConnection(connectionString);

              conn.Open();
              OleDbCommand cmd = new OleDbCommand(CreateCommand, conn);
             //   cmd.ExecuteNonQuery();
               DbDataReader dr= cmd.ExecuteReader();

              int i = 0;

               while (dr.Read())
               {

                   string ab = dr.GetValue(i).ToString();
                   MessageBox.Show(ab);
                   i++;
               }

Answer 1

Did you try HDR=YES ? That's what tells the OLEDB provider that you do have a header row.

http://connectionstrings.com/excel

Answer 2

Wouldn't you want to set the HDR=No?

Telling the OLEDB provider that the first row contains headers will cause the provider to use the headers as the names for the fields. (I'm thinking about dumping the info into a datatable, after which you get the information @ DataTable.Columns["[HEADER]"].Row....)

Since you're using a simple data reader, and you want the "header" fields to be read as data, specify that these are not headers.

Answer 3

// CODE TO SET UP THE CONNECTION BETWEEN EXCEL AND VS2005 // IN EXTENDED PROPERTIES SET HDR = YES FOR READING FIRST ROW AND HEADERS. // IN EXTENDED PROPERTIES SET IMEX = 1 TO READ INTERMIXED DATA.

            excelCon = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\ExcelDBtrial.xls;Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'");
            excelCon.Open();
            exDA = new OleDbDataAdapter("Select * from [Sheet1$]", excelCon);
            exDA.Fill(exDT);

            //CODE TO ADD TABLE HEADERS INTO THE HEADERS COMBOBOX
            foreach (DataColumn dc in exDT.Columns)
                headerCB.Items.Add(dc.ToString());