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

Maybe you could be a bit more specific about a) what your spreadsheet look like (does it have headers), b) what you expect and c) what actually happens. — H H, May 06 '09 at 22:28
a) My spreadsheet contains 3 columns and does contain a header. It looks something like this: RowNo A B 1 10 20 2 20 40 b) I want to read the headers (RowNo, A, B) because in the program i dont have the idea about the headers. c) Using this code i directly get the data, but i dont get the headers. — , May 06 '09 at 22:35

score 6 · Answer 1 · answered May 06 '09 at 23:10

6

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

http://connectionstrings.com/excel

answered May 06 '09 at 23:10

Alan McBee

4,202
3
33
38

Yes. Initially I had tried HDR=Yes. But still getting wrong answer-'3' in both the cases – May 06 '09 at 23:23
1

Sam, use HDR=YES and get the column-name from dr.GetFieldName (or similar) – H H May 07 '09 at 09:17
Close. Not dr.GetFieldName, but dr.GetName(ordinal) where ordinal = dr.GetOrdinal("RowNo") and so on. – Alan McBee May 07 '09 at 19:24

score 2 · Answer 2 · answered May 18 '11 at 15:04

I've always used the built in functions of GetSchema to enumerate Sheets and Headers. It's really quite slick and no non-sense. Good Luck!

OleDbConnection xl = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=filename.xlsx;Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\"");
xl.Open();

//Get columns
DataTable dtColumns = xl.GetSchema("Columns", new string[] { null, null, sheetName, null });
List<string> columns = new List<string>();
foreach (DataRow dr in dtColumns.Rows)
   columns.Add(dr[3].ToString());

xl.Close();

score 2 · Answer 3 · answered May 07 '09 at 00:12

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.

answered May 07 '09 at 00:12

corey broderick

185
2
7

Perfect. This fixed it for me. Thank you. – bjpelcdev Oct 12 '16 at 08:14

score 0 · Answer 4 · edited Sep 11 '12 at 06:43

string xlPath = @"D:\Temparary.xlsx";    //location of xlsx file

string constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + xlPath + ";Extended Properties=\"Excel 12.0 Xml; HDR=YES; IMEX=1;\"";

OleDbConnection con = new OleDbConnection(constr);

OleDbCommand cmd = new OleDbCommand("select * from [Sheet1$]",con);

con.Open();

OleDbDataReader dreader = cmd.ExecuteReader();

if (dreader.HasRows)
{
    dreader.Read();
    Label2.Text = dreader.GetValue(0).ToString();

}

dreader.Close();

con.Close();

score 0 · Answer 5 · edited Sep 15 '11 at 16:35

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

score 0 · Answer 6 · edited May 23 '17 at 12:08

I had a different problem, but was able to access the first row of Excel data and remove it via this OleDBAdapter Excel QA I posted via stack overflow.

If you are trying to access first row, first column, just populate a dataset per the post I mentioned and add to the bottom:

    // DataSet:          
    Object o = ds.Tables["xlsImport"].Rows[0]["LocationID"];
    Object oa = ds.Tables["xlsImport"].Rows[0]["PartID"];            
    Object row0Col3 = ds.Tables["xlsImport"].Rows[0][3];

    string valLocationID = o.ToString();
    string valPartID = oa.ToString();
    string rowZeroColumn3 = row0Col3.ToString();

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

6 Answers6

Linked